Q1) List the SSN of all employees who work on exactly same set of projects as employee with SSN '123456789'
*
select essn from works_on
where pno in(select pno from works_on where essn='123456789')
having count(*)=(select count(*) from works_on where essn='123456789')
group by essn
minus
select essn from works_on where essn='123456789'
Q2) Produce a view name DEPT_INFO that lists for each department, its name , the number of employee working for the department, and the total salary of the employee working in the department.
*
create or replace view DEPT_INFO_v1 as
select count(dname)as total_emp ,dname,sum(salary) sal from employee e, department d
where e.dno = d.dnumber
group by dname
Q3) List Fname,Lname,Salary of each employee who works on all project controlled by Research department.
*
select fname,lname,e.salary from employee e where
e.ssn in(
select essn from works_on w
where
w.pno in (select p.pnumber from project p where p.dnum in(select dnum from department where dname ='Research'))
group by essn having count(*)=(select count(p.pnumber) from project p where p.dnum in(select dnum from department where dname ='Research')))
Tags:
SQL practice
