University:
Massachusetts Institute of Technology
Course:
15.992 | S-Lab: Laboratory for Sustainable Business
Academic year:

2023
Views:

291

Pages:

1
Author:

Zachery Copeland

Information Technology I SQL Query Exercises • Find First Name and Last Name for all employees who work for the ‘Research’ department SELECT FNAME, LNAME FROM EMPLOYEES, DEPARTMENT WHERE DNAME = “Research” AND DNUMBER=DNO; • For each employee, retrieve the employee’s First and Last name and the first and last name of his/her immediate supervisor SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM EMPLOYEES AS E, EMPLOYEES AS S WHERE E.SUPERSSN = S.SSN; • How many employees work in the ‘Research’ department? SELECT COUNT(*) FROM EMPLOYEE, DEPARTMENT WHERE DNO=DNUMBER AND DNAME=”Research”; • What is the maximum, minimum, and average salary in the ‘Research’ department? SELECT MIN(SALARY), MAX(SALARY), AVG(SALARY) FROM EMPLOYEE, DEPARTMENT WHERE DNO=DNUMBER AND DNAME=”Research”; • For each project on which more than two employees work, retrieve the project number, project name, and number of employees who work on that project SELECT PNUMBER, PNAME, COUNT(*) FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER HAVING COUNT(*)>2; • For each project, retrieve the project number, project name, and number of employees from department 5 who work on this project SELECT PNUMBER, PNAME, COUNT(*) FROM PROJECT, WORKS_ON, EMPLOYEE WHERE PNUMBER=PNO AND SSN=ESSN AND DNO=5 GROUP BY PNUMBER;