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...