CS 157 EXERCISE KEY Database DB2 and DB1BI. Give the output of the following SQL query.(Due 11/29: 1-8)** 1. SELECT EMPLOYEE.SSN, EMPLOYEE.LNAME FROM DEPARTMENT, EMPLOYEE WHERE DEPARTMENT.DNAME = 'ADMINISTRATION' AND EMPLOYEE.DNO = DEPARTMENT.DNUMBER;** 2. SELECT EMPLOYEE.FNAME, EMPLOYEE.LNAME FROM EMPLOYEE WHERE EXISTS (SELECT * FROM WORKS_ON WHERE WORKS_ON.WSSN = EMPLOYEE.SSNAND WORKS_ON.PNO <> 1);** 3. SELECT EMPLOYEE.SSN, EMPLOYEE.LNAME, EMPLOYEE.BDATE FROM EMPLOYEE WHERE EMPLOYEE.ADDRESS LIKE '%BELLAIRE%';*** 4. SELECT EMPLOYEE.SSN, EMPLOYEE.FNAME, EMPLOYEE.LNAME FROM EMPLOYEE WHERE EXISTS (SELECT * FROM DEPENDENT WHERE EMPLOYEE.SSN =DEPENDENT.DEPSSN) AND EXISTS (SELECT * FROM DEPARTMENT WHERE EMPLOYEE.SSN = DEPARTMENT.MGRSSN);(delete) 5. SELECT EMPLOYEE.SSN, EMPLOYEE.FNAME, EMPLOYEE.LNAME FROM EMPLOYEE WHERE EXISTS (SELECT * FROM DEPENDENT WHERE EMPLOYEE.SSN = DEPENDENT.DEPSSN) AND EXISTS (SELECT * FROM DEPARTMENTWHERE EMPLOYEE.SSN = DEPARTMENT.MGRSSN);*** 6. SELECT EMPLOYEE.LNAME, EMPLOYEE.FNAME FROM EMPLOYEE WHERE (SELECT COUNT(*) FROM WORKS_ON WHERE EMPLOYEE.SSN = WORKS_ON.WSSN) >= 2; *** 7. SELECT LNAME, SALARY FROM EMPLOYEE ORDER BY SALARY DESC, LNAME ASC;*** 8. SELECT DEPARTMENT.DNAME, COUNT(*) FROM DEPARTMENT, EMPLOYEE WHERE DEPARTMENT.DNUMBER = EMPLOYEE.DNO GROUP BY DEPARTMENT.DNAME HAVING COUNT(*) > 2;(please create your own table) II. Give SQL syntax for the following English descriptions. (USE DB1b). *** 9. Give names of projects which have at least one part supplied bySmith.(> 1 solutions: in, exist, equal join)*** 10. Give supplier names and total quantity of parts distributed byeach supplier. List the names in alphabetic order.*** 3. Retrieve name and color of each part supplied to the project'Terminal'.(> 1 solutions: in, exist, equal join)*** 11. Get supplier names who provide parts to a project located inParis.(> 1 solutions: in, exist, equal join)*** 12. Give supplier number for each supplier who provides more than100 red screws to project #3.(> 1 solutions: in, exist, equal join)II. Give SQL syntax for the following English descriptions.(Due 11/30 13-15) * 13. Get name of each project located in Stafford.* 14. Get project location of the 'ProductX'.* 15. Get the social security number of each employee who worksover 15 hours on at least one project.(Due 12/1 15-18)* 16. Get the social security number & last name of each femaleemployee who works in department #4.* 17. Increase the salary of each employee working in department #5by 8.5%.** 18. For each employee, get his/her social security number and total# of hours he/she works.(Due 12/2 18-21)** 19. For each employee, list his/her ssn & number of projects (notproject #) that he/she is working on.(> 1 solutions: in, exists, equal join)** 20. For each supervisor, retrieve his/her social security number, andthe maximun salary among those employees supervised byhim/her.** 21. For each department, get its department # & an average salaryof all employee working there.(Due 12/3 21-24) ** 22. Get the first & last names of employees who work for 20 hours per week or more on project number less than 15.(use “in” “exists” join)select fname, lnamefrom employeewhere ssn in (select wssn from works_on where pno < 15 and hours >= 20);ORselect fname, lnamefrom employeewhere exists (select * from works_on where ssn = wssn and pno < 15 and hours >= 20);ORselect fname, lnamefrom employee, works_onwhere ssn = wssn and pno < 15 and hours >= 20;** 23. Get the first & last names of employees who have at least one son.SELECT FNAME, LNAMEFROM EMPLOYEE, DEPENDENTWHERE SSN = DEPSSN AND DEPENDENT.RELATIONSHIP = 'SON';SELECT FNAME, LNAMEFROM EMPLOYEEWHERE SSN IN (SELECT DEPSSN FROM DEPENDENT WHERE RELATIONSHIP = 'SON');SELECT FNAME, LNAMEFROM EMPLOYEEWHERE EXISTS (SELECT * FROM DEPENDENT WHERE SSN = DEPSSN AND RELATIONSHIP = 'SON');** 24. Get last name of each employee who does not work on project #1. Assume every employee works on at leastone project.select lnamefrom employeewhere not exists(select * from works_on where wssn = ssn and pno = 1);select lnamefrom employeewhere ssn not in (select wssn from works_on where pno = 1);select lnamefrom employee, works_onwhere pno <> 1 and ssn = wssn;(Due 12/4 24-27)** 25. For each employee, list the first and last name of his/herdependent(s).(> 1 solutions: in, exists, equal join)select dependent_name, lnamefrom employee, dependentwhere ssn = depssn;** 26. Get name of each department which has a location in Houston. (> 1 solutions: exists, in, equal join)SELECT DEPARTMENT.DNAMEFROM DEPARTMENT, DEPT_LOCATIONWHERE DEPARTMENT.DNUMBER = DEPT_LOCATION.DNUMBERAND DLOCATION = 'HOUSTON';** 27. Get first & last name of the manager in the Researchdepartment. ( > 1 solutions: exists, in, equal join)SELECT FNAME, LNAMEFROMEMPLOYEE, DEPARTMENTWHERE SSN = MGRSSN AND DNAME = 'RESEARCH';(Due 12/5 28-31)** 28. For each employee, list his/her ssn & names of projects thathe/she is working on.(> 1 solutions: in, exists, equal join)select wssn, pnamefrom works_on, projectwhere pno = pnumber;** 29. Get department name of the 'Reorganization' project.(> 1 solutions: in, exists, equal join)SELECT DNAMEFROM DEPARTMENT, PROJECTWHERE DNUMBER = DNUM AND PNAME = 'REORGANIZATION';** 30. For the 'Research' department, get its department number & lastnames of its employees.(> 1 solutions: in, exists, equal join)SELECT DNO, LNAMEFROM DEPARTMENT, EMPLOYEEWHERE DNUMBER = DNO AND DNAME = 'RESEARCH';(Due 12/6 31-34)** 31. Get first & last name of each employee who has a dependentwith the same first name as the employee.( > 1 solutions: exists, in, equal join)select fname, lnamefrom employeewhere ssn in (select depssn from dependent where depssn = ssn and fname = dependent_name);select fname, lnamefrom employee, dependentwhere ssn = depssn and fname = dependent_name;*** 32. Get department name & the average salary of all employees ineach department.select dname, avg(salary)from department, employeewhere dnumber = dnogroup by dname;*** 33. Get the first & last
View Full Document
Unlocking...