Populate these tables of this schema with sample data Write

Populate these tables of this schema with sample data Write & execute SQL statements to obtain the following information from the database Find total number of employees Find total number of employees in \'Research\' department Find the number of projects controlled by each department (get department #, department name & # of projects controlled) Find departments which control more than 5 projects Get Name & SSN of Employees with more than 2 dependents Increase salary of employees working in \'HR\' department by 10% Delete project whose project number = 12345

Solution

1) select count(ssn) as total from Employee

2) select count(ssn) as total_research from Employee where dno in(select dnumber from Department where dname=\'Research\')

3) select d.dnumber,d.dname,count(p.dnum) from Department d,Project p where d.dnumber=p.dnum group by p.dnum

4) select dnum,dname from Department where dnum in (select dnum from Project where count(pnum)>=5 group by dnum)

5) select fname,ssn from Employee where ssn in(select essn from Dependent where count(dependent_name)>2 group by essn)

6) update Employee set salary=salary+(0.1*salary) where dno in(select dnumber from Department where dname=\'HR\')

7) delete from Project where pnumber=12345