Company Database

1. Company Database

2. CREATE TABLE DEPARMENT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9), PRIMARY KEY (DNUMBER), UNIQUE (DNAME), FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SNN) )

3. Query 1: Retrieve the birthdate and address of theemployee whose name is 'John B. Smith'. SELECT BDATE, ADDRESS FROM EMPLOYEE WHERE FNAME='John' AND MINIT='B’ AND LNAME='Smith

4. Query 2: Retrieve the name and address of all employeeswho work for the 'Research' department. SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE, DEPARTMENT WHERE DNAME='Research' AND DNUMBER=DNO

5. Query 3: For every project located in 'Stafford', list the projectnumber, the controlling department number, and the departmentmanager's last name, address, and birthdate. SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND PLOCATION='Stafford'

6. Query 4: Make a list of all project numbers for projects that involve anemployee whose last name is 'Smith' as a worker or as a manager ofthe department that controls the project. (SELECT PNAME FROM PROJECT, DEPARTMENT,EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME='Smith') UNION (SELECT PNAME FROM PROJECT, WORKS_ON, EMPLOYEE WHERE PNUMBER=PNO AND ESSN=SSN AND NAME='Smith')

7. Query 5: Retrieve the name of each employee who has a dependentwith the same first name as the employee. SELECT E.FNAME, E.LNAME FROM EMPLOYEE AS E WHERE E.SSN IN (SELECT ESSN FROM DEPENDENT WHERE ESSN=E.SSN AND E.FNAME=DEPENDENT_NAME)

8. Query 6: Retrieve the name of each employee who has a dependentwith the same first name as the employee. SELECT E.FNAME, E.LNAME FROM EMPLOYEE E, DEPENDENT D WHERE E.SSN=D.ESSN AND E.FNAME=D.DEPENDENT_NAME

9. Query 7: Retrieve a list of employees and the projects each works in, ordered by the employee's department, and within each department ordered alphabetically by employee last name. SELECT DNAME, LNAME, FNAME,PNAME FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT WHERE DNUMBER=DNO AND SSN=ESSN AND PNO=PNUMBER ORDER BY DNAME, LNAME

10. Query 8: Retrieveallemployeeswhoseaddress is in Houston, Texas. Here, the value ofthe ADDRESS attribute must contain thesubstring'Houston,TX‘ in it. SELECT FNAME, LNAME FROM EMPLOYEE WHERE ADDRESS LIKE'%Houston,TX%'

11. Query 9: Retrieve the name of each employee who workson all the projects controlled by department number 5. SELECT FNAME, LNAME FROM EMPLOYEE WHERE ( (SELECT PNO FROM WORKS_ON WHERE SSN=ESSN) CONTAINS (SELECT PNUMBER FROM PROJECT WHERE DNUM=5))

12. Query 10: Retrieve the names of employees who have nodependents. SELECT FNAME, LNAME FROM EMPLOYEE WHERE NOT EXISTS (SELECT * FROM DEPENDENT WHERE SSN=ESSN)

13. Query 11: Find the maximum salary, theminimum salary, and the average salary amongemployees who work for the 'Research‘department. SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY) FROM EMPLOYEE, DEPARTMENT WHERE DNO=DNUMBER AND DNAME='Research'

14. Query 12: For each department, retrieve the departmentnumber, the number of employees in the department, andtheiraveragesalary. SELECT DNO, COUNT (*),AVG (SALARY) FROM EMPLOYEE GROUP BY DNO

15. Query 13: For each project, retrieve the project number, project name, and the number of employees who work onthatproject. SELECT PNUMBER, PNAME, COUNT (*) FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER, PNAME

16. Query 14: For each project on which more thantwo employees work, retrieve the project number,project name, and the number of employees whoworkon thatproject. SELECT PNUMBER, PNAME,COUNT(*) FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER, PNAME HAVING COUNT (*) > 2

17. Query 15: Specifytheconstraintthatthesalary of an employee must not greaterthanthesalary of themanager of thedepartmentthatthe employee worksfor. CREATE ASSERTION SALARY_CONSTRAINT CHECK ( NOT EXISTS ( SELECT * FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D WHERE E.Salary>M.Salary AND E.Dno=D.Dnumber AND D.Mgr_ssn=M.Ssn ) )

18. Query 16: Find the average grade of all ”CS” curriculum students with respect to differentsemesters. SELECT T.Semester, AVG(T.Grade) FROM Take AS T, Student AS S WHERE (S.SID = T.SID) AND (S.Curriculum = "CS") GROUP BY T.Semester

19. Query 17: Find all courses that are offered at least once every year. SELECT C.Cname FROM Course AS C WHERE NOT EXISTS(SELECT distinct(O1.Year) FROM Offer AS O1 WHERE O1.Year NOT IN (SELECT O2.Year FROM Offer AS O2 WHERE (O2.CID = C.CID))

20. Query 18: Find all facultieswho taught a course in which the average grade for CS studentswas lower than the other students.

21. SELECT F.Fname FROM Faculty AS F WHERE F.FID IN (SELECT T1.FID FROM Take AS T1, Student AS S1 WHERE (T1.SID=S1.SID) AND (S1.Curriculum="CS") GROUP BY T1.FID HAVING Avg(T1.Grade) <all (SELECT Avg(T2.Grade) FROM Take AS T2, Student AS S2 WHERE (T2.SID=S2.SID) AND (S2.Curriculum <>"CS") AND (T1.FID = T2.FID) AND (T1.CID = T2.CID) AND (T1.Semester = T2.Semester) AND (T1.Year = T2.Year) GROUP BY T2.FID)

22. Query 19: Find the number and department identifier of all courses in which no student ever got an ’F’. SELECT C.course_number, C.dept_id FROM COURSE C WHERE NOT EXISTS (SELECT * FROM TRANSCRIPT T, SECTION S WHERE (T.grade = ’F’) AND (T.section_id=S.section_id) AND (S.course_number=C.course_number) AND (S.dept_id = C.dept_id)

23. Query 20: Find the id of all sections of courses offered by department ”Computer Science” in the ”Fall99” semester. SELECT S.section_id FROM section S, department D WHERE S.dept_id = D.dept_id AND D.department_name= "Computer Science" ANDS.semester= "Fall99"

24. Query 21: Find the id of all sections that a student named ”Kenny” is taking in ”Fall99” semester. SELECT T.section_id FROM section S, transcript T, student StWHERE St.sid = T.sid AND S.section_id = T.section_id ANDSt.firstname= "Kenny" AND S.semester "Fall99"

25. Query 22: Find the first name, last name and the department name for all instructors who are teaching atleast three sections of a single course in semester ’Fall 99’. SELECT I.firstname, I.lastname, D.department_name FROM INSTRUCTOR I, DEPARTMENT D WHERE (I.dept_id = D.dept_id) AND EXISTS (SELECT S.course_number, S.dept_id FROM SECTION S WHERE (S.semester = ’Fall 99’) AND (S.instructor_id= I.instructor_id) GROUP BY S.course_number, S.dept_id HAVING count(S.section_id) >= 3)

26. Query 23: For all different courses in the curriculum, find the total number of students in that class foreach different semester (regardless of their sections). SELECT SC.course_number, SC.dept_id, SC.semester, count(TR.sid) FROM SECTION SC, TRANSCRIPT TR WHERE SC.section_id = TR.section_id GROUP BY SC.course_number, SC.dept_id, SC.semester

27. Query 24: Find the average number of students in each different course (identified by course numberand dept id), average over different semesters. Find for each course number of students for differentsemesters and then take the average. SELECT S.course_number, S.dept_id, count(S.sid)/count(DISTINCT S.semester) FROM section S, transcript T WHERE S.section_id = T.section_id GROUP BY S.course_number, S.dept_id

28. Query 25: Set the grade of student named ”Kenny” for course number 111, dept id 15 offeredin ”Fall99” to ”F”. UPDATE transcript SET grade = ’F’ WHERE sidIN (SELECT S.sid FROM student S WHERE S.firstname = ’Kenny’) ANDsection_idIN (SELECT Sc.section_id FROM section Sc WHERE Sc.course_number = 111 AND Sc.dept_id = 15 ANDSc.semester= ’Fall99’)

29. Query 26: Insert a tuple into transcript indicating that ”Kenny” is taking course number 111,deptid 15 in ”Fall99”. INSERT INTO transcript(sid, section_number) SELECT DISTINCT S.sid, Sc.section_number FROM student S, section Sc WHERE S.firstname = ’Kenny’ AND Sc.dept_id = 15 ANDSc.course_number= 111 AND Sc.semester= ’Fall99’