EMPLOYEE TABLE
So On. . . . .
Resulting EMPLOYEE table :-
DEPARTMENT TABLE
DEPARTMENT LOCATIONS TABLE
PROJECT TABLE
WORKS_ON TABLE
So On. . . . .
Resulting works_on Table:-
DEPENDENT TABLE
So On. . . . .
1). Write an SQL query to determine the 5th highest salary from employee table
without using TOP or limit method.
2). Retrieve the first and last names of employees with the same salary.
3). Retrieve department number of departments that have less than five
employees in it.
4). Retrieve the names of employees who make at least $10,000 more than the
employee who is paid the least in the company.
5). Retrieve the number of male employees in each department.
6). Retrieve the first and last names and department number and name of all
employees directly supervised by James Borg. Show results in ascending alpha
order (by last name and then first name).
7). Retrieve the name and number of departments which have employees who do
not work on at least one project. Show results in ascending alpha order. (NOTE:
a department should appear on this list if it has an employee who does not work
on any project at all.)
8). For each department, list the department name and the total number of hours
assigned to projects controlled by the department (irrespective of the employee to
whom they are assigned) and the total number of hours assigned to employees of
the department (irrespective of the project involved). Show results in ascending
alpha order.
9). Retrieve the names of departments which have at least one project which
employs every one of the employees of the department that controls the project.
Also show the name of the project. Show results in ascending alpha order.
10). Retrieve the first and last names of employees who work on projects which
are not controlled by their departments. Also show the names of the projects, the
employee's department number, and the number of the project's controlling
department. (All of this should be shown in the same result table.) Show results
in ascending alpha order (by last name and then first name and then project
name).
11. Retrieve the first and last names of employees who work on more than the
average number of projects. (Note: employees who do not work on any project
are to be included in the average.) Display their names, the number of projects
they work on, and the average number of projects. (The same average should be
repeated in each row.) Show results in ascending alpha order (by last name and
then first name). [The average number of projects is the average number of
projects worked on per employee.]
12. Retrieve the name and number of the project, which uses the most employees.
Also show the total number of employees for that project. If there is more than
one project that has attained that maximum, list them all. Show results in
ascending alpha order.
13. Do any departments have a location in which they have no projects? Retrieve
the names of departments which have at least one location which is not the same
as any of the locations of the department's projects. Show results in ascending
alpha order. [This means that one department location is different from every
location of every project of that department.]
14. List the names of dependents that have the same first name as an employee
of whom they are not the dependent. Also show the ssn of the employee with the
same first name and the ssn of the employee on whom the dependent is dependent
(dependent.essn). (All of this should be shown in the same table.) Show results in
ascending alpha order.
15. Retrieve the first and last names of employees whose supervisor works on any
project outside the employee's department. Show results in ascending alpha order
(by last name and then first name). [Note that you are to retrieve the employee's
name, not the supervisor's.]
