For each department whose average employee salary is more than $30,000, retrieve the department name and the number of employees working for that department. select department.dname, count(*) as number_of_emp from department join employee on employee.DNO = department.DNO where (select avg(salary) from employee where employee.dno=department.dno group by employee.dno)>30000 group by department.dname; Same as a, except output the number of male employees instead of the number of employees. select department.dname, count(*) from department join employee on employee.DNO = department.DNO where (select avg(salary) from employee)>30000 and employee.sex = 'M' group by department.dname; Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees. select fname||' '||lname as Full_name from employee where dno= (select dno from employee where salary = (select max(salary) from employee)); Retrieve the names of employees who make at least $10,000 more than the employee who is paid the least in the company. select fname||' '||lname as Full_name from employee where salary>= (select min(salary)+10000 from employee); Retrieve the names of employees who is making least in their departments and have more than one dependent. select x.fname||' '||x.lname as Full_name from (select e.ssn, e.fname, e.lname from employee e where salary<= (select min(salary) from employee e) group by e.dno,e.ssn, e.fname,e.lname) x, (select d.essn, count(*) from dependent d, employee e where d.ESSN = e.SSN group by d.essn having count(*)>1) y where x.ssn=y.essn; A view that has the department name, manager name and manager salary for every department. create view view1 as select d.dname as Department_Name, e.fname||' '|| e.lname as Manager_Name, e.salary as Manager_salary from employee e join department d on e.ssn=d.mgrssn; A view that has the department name, its manager's name, number of employees working in that department, and the number of projects controlled by that department (for each department). create view view2 as select m.dname, m.manager_name, n.number_of_emp, t.number_of_projects from (select d.dno, d.dname as dname, e.fname||' '||e.lname as manager_name from department d, employee e where d.mgrssn=e.ssn) m left outer join (select d.dno, count(*) as number_of_emp from employee e, department d where d.dno=e.DNO group by d.dno) n on m.dno=n.dno left outer join (select p.dno,count(p.pno) as number_of_projects from department d, project p where p.dno=d.dno group by p.dno) t on n.dno=t.dno; A view that has the project name, controlling department name, number of employees working on the project, and the total hours per week they work on the project (for each project). create view view3 as select proj_dept.project_name, proj_dept.controlling_dept_name, num_emp.num_of_emp_working,num_emp.number_of_working_hours from (select p.pno, p.pname as project_name, d.dname as controlling_dept_name from department d, project p where p.DNO = d.DNO) proj_dept natural join (select w.pno, count(*) as num_of_emp_working, sum(w.hours) as number_of_working_hours from WORKS_ON w, PROJECT p where w.PNO = p.PNO group by w.pno) num_emp; A view that has the project name, controlling department name, number of employees, and total hours worked per week on the project for each project with more than one employee working on it. create view view4 as select proj_dept.project_name, proj_dept.controlling_dept_name, num_emp.num_of_emp_working,num_emp.number_of_working_hours from (select p.pno, p.pname as project_name, d.dname as controlling_dept_name from department d, project p where p.DNO = d.DNO) proj_dept natural join (select w.pno, count(*) as num_of_emp_working, sum(w.hours) as number_of_working_hours from WORKS_ON w, PROJECT p where w.PNO = p.PNO group by w.pno having count(*)>1) num_emp; A view that has the employee name, employee salary, department that the employee works in, department manager name, manager salary, and average salary for the department. create view view5 as select x.fname, x.salary, x.dname, y.fname as Manager_name, z.aver as Average_salary from (select e.dno,e.fname, e.salary, d.dname from employee e, department d where e.dno=d.dno) x, (select d.dno, e.fname, e.salary from employee e, department d where e.ssn=d.mgrssn) y, (select e.dno, avg(salary) as aver from employee e group by e.dno) z where x.dno=y.dno and y.dno=z.dno;
View Full Document
Unlocking...