Write the following queries in SQL a Retrieve the maximum sa

Write the following queries in SQL:

a) Retrieve the maximum salary of each employee in each department.
b) Retrieve the Ssn of all employees who work for the Finance and Research departments.
c) Retrieve the Ssn of all employees who work for any department except the Research department.
d) Retrieve the names of controlling departments of employees who work on more than two projects.
e) Make a list of the distinct names of all employees who are working in department number 4 and employees who are working on projects that are located in Sharjah.

Figure 3.6 One possible database state for the COMPANY relational database schema. EMPLOYEE Frame Minit Lname SSn Bdate Address Sex Salary Super ssn Dno B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 300000 333445555 5 John Franklin T Wong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 888665555 5 F 43000 888665555 4 Jennifer S Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 333445555 5 Joyce A English 453453453 1972-07-31 5631 Rice, Houston, TX F 25000 333445555 5 Ahmad V Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX M 25000 987654321 4 James Borg 888665555 1937-11-10 450 Stone, Houston, TX 55000 NULL DEPT LOCATIONS DEPARTMENT Dnumber Dname Dnumber Mgr ssn Mgr start date Dlocation 5 3334 1988-05-22 Research Houston Administration 4 987654321 1995-01-01 Stafford 888665555 Headquarters Bellaire 5 Sugarland Houston WORKS ON PROJECT Essn Pro Hours Pname Pnumber Plocation Dnum 1 32.5 Productx 1 Bellaire 5 123456789 ProductY 2 Sugarland 5 2 75 123456789 3 40.0 Productz Houston 666884444 1 20.0 10 Stafford 4 453453453 Computerization 2 20.0 20 Houston 1 Reorganization 453453453 2 10.0 30 Stafford 4 333445555 Newbenefits 3 10.0 333445555 10 10.0 333445555 DEPENDENT 20 10.0 Essn Dependent name Sex Bdate 333445555 Relationship 30 30.0 333445555 Alice 999887777 1986-04-05 Daughter 10 100 333445555 Theodore M 1983.10.25 Son 999887777 10 350 333445555 Joy F 1958-05-03 spouse 987987987 30 5.0 987654321 Abner M 1942.02.28 spouse 987987987 30 20.0 123456789 Michael M 1988-01-04 Son 987654321 20 15.0 123456789 Alice 1988.12.30 Daughter 987654321 888665555 123456789

Solution

1) select e.fname,e.ssn,e.salary,d.dname from Employee e inner join Department d where e.Dno=d.Dnumber AND e.salary in(select max(salary) from Employee where Dno=d.Dnumber)

3)

select ssn from Employee where Dno in(select Dnumber from Department where dname<>\'Research\')

2)

select ssn from Employee where Dno in(select Dnumber from Department where dname in(\'finance\',\'Research\'))

4)

select Dname from Department where Dnumber in(select Dno from Employee where ssn in(select Essn from works_on where count(Essn)>2 group by Essn))

5)

select distinct Fname,distinct Lname from Employee where Dno=4 OR ssn in(select Essn from works_on where Pno in(select Pnumber from Project where Plocation=\'Sharjah\'))