SQL Questions

PART-A

Employee(EmpId, Lname, Fname, PosId, Supervisor, HireDate, Salary, Commission, DeptId, QualId) PosId: REFERENCES Position (PositionId)

DeptId: REFERENCES Dept (DeptId)QualId: REFERENCES Qualification (QualId) Supervisor: REFERENCES Employee(EmpId)

Position(PositionId,PosDesc) Qualification(QualId, QualDesc) Dept(DeptId, DeptName, Location, EmpId)

EmpId: REFERENCES employee (EmpId)

Dependent(EmpId, DependentId, DepDOB, Relation) EmpId: REFERENCES Employee (EmpId)

a)Display all employee names and their department names.

b) Find all employees full names (lastname, firstname format) with salary, and their supervisors name with salary.

c) Find number of employees for each department and position. Display position description, department name and number of employees in the output.

d) Display each employees name, department name, position description, and qualification description.

e) Display employee names and dependent information. Include those employees who has no dependent.

f) Find out the names and number of years worked along with their department names in descending order by number of years worked.

g) Who works in the same department in which John Smith works? Display first and last names of the employees in the output. John Smith should not appear in the output. h) Find name of the supervisor for employee with lname SHAH. i) Find all employees in the SALES department. Display first name, last name and department name in the output.

j) Display departments (name) that their employees have average salary less than the average salary of department 100. k) Display the position (description) of the employee(s) with the highest average salary.