Company Database

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) )

SELECT BDATE, ADDRESSFROM EMPLOYEEWHERE FNAME='John' AND MINIT='B’AND LNAME='Smith

Query 1: Retrieve the birthdate and address of the employee whose name is 'John B. Smith'.

SELECT FNAME, LNAME, ADDRESSFROM EMPLOYEE, DEPARTMENTWHERE DNAME='Research' AND DNUMBER=DNO

Query 2: Retrieve the name and address of all employees who work for the 'Research' department.

SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESSFROM PROJECT, DEPARTMENT, EMPLOYEEWHERE DNUM=DNUMBER AND MGRSSN=SSNAND PLOCATION='Stafford'

Query 3: For every project located in 'Stafford', list the project number, the controlling department number, and the department manager's last name, address, and birthdate.

(SELECT PNAMEFROM PROJECT, DEPARTMENT, EMPLOYEEWHERE DNUM=DNUMBER ANDMGRSSN=SSN AND LNAME='Smith')UNION(SELECT PNAMEFROM PROJECT, WORKS_ON, EMPLOYEEWHERE PNUMBER=PNO ANDESSN=SSN AND NAME='Smith')

Query 4: Make a list of all project numbers for projects that involve an employee whose last name is 'Smith' as a worker or as a manager of the department that controls the project.

SELECT E.FNAME, E.LNAMEFROM EMPLOYEE AS EWHERE E.SSN IN

(SELECT ESSNFROM DEPENDENTWHERE ESSN=E.SSN ANDE.FNAME=DEPENDENT_NAME)

Query 5: Retrieve the name of each employee who has a dependent with the same first name as the employee.

SELECT E.FNAME, E.LNAMEFROM EMPLOYEE E, DEPENDENT DWHERE E.SSN=D.ESSN ANDE.FNAME=D.DEPENDENT_NAME

Query 6: Retrieve the name of each employee who has a dependent with the same first name as the employee.

SELECT DNAME, LNAME, FNAME,PNAMEFROM DEPARTMENT, EMPLOYEE,WORKS_ON, PROJECTWHERE DNUMBER=DNO AND SSN=ESSNAND PNO=PNUMBERORDER BY DNAME, LNAME

Query 7: Retrieve a list of employees and the projectseach works in, ordered by the employee's department,and within each department ordered alphabetically byemployee last name.

SELECT FNAME, LNAMEFROM EMPLOYEEWHERE ADDRESS LIKE'%Houston,TX%'

Query 8: Retrieve all employees whose address is in Houston, Texas. Here, the value of the ADDRESS attribute must contain the substring 'Houston,TX‘ in it.

SELECT FNAME, LNAMEFROM EMPLOYEEWHERE ( (SELECT PNO

FROM WORKS_ONWHERE SSN=ESSN) CONTAINS(SELECT PNUMBERFROM PROJECTWHERE DNUM=5))

Query 9: Retrieve the name of each employee who works on all the projects controlled by department number 5.

SELECT FNAME, LNAMEFROM EMPLOYEEWHERE NOT EXISTS (SELECT *

FROM DEPENDENTWHERE SSN=ESSN)

Query 10: Retrieve the names of employees who have no dependents.

SELECT MAX(SALARY),MIN(SALARY), AVG(SALARY)FROM EMPLOYEE, DEPARTMENTWHERE DNO=DNUMBER ANDDNAME='Research'

Query 11: Find the maximum salary, the minimum salary, and the average salary among employees who work for the 'Research‘ department.

SELECT DNO, COUNT (*),AVG (SALARY)FROM EMPLOYEEGROUP BY DNO

Query 12: For each department, retrieve the department number, the number of employees in the department, and their average salary.

SELECT PNUMBER, PNAME, COUNT (*)FROM PROJECT, WORKS_ONWHERE PNUMBER=PNOGROUP BY PNUMBER, PNAME

Query 13: For each project, retrieve the project number,project name, and the number of employees who work on that project.

SELECT PNUMBER, PNAME,COUNT(*)FROM PROJECT, WORKS_ONWHERE PNUMBER=PNOGROUP BY PNUMBER, PNAMEHAVING COUNT (*) > 2

Query 14: For each project on which more than two employees work, retrieve the project number, project name, and the number of employees who work on that project.

CREATE ASSERTION SALARY_CONSTRAINTCHECK ( NOT EXISTS ( SELECT *FROM EMPLOYEE E, EMPLOYEE M,DEPARTMENT DWHERE E.Salary>M.SalaryAND E.Dno=D.DnumberAND D.Mgr_ssn=M.Ssn ) )

Query 15: Specify the constraint that the salary of an employee must not greater than the salary of the manager of the department that the employee works for.

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

Query 17: Find all courses that are offered at least once every year.SELECT C.CnameFROM Course AS CWHERE NOT EXISTS(SELECT distinct(O1.Year)

FROM Offer AS O1 WHERE O1.Year NOT IN

(SELECT O2.YearFROM Offer AS O2WHERE (O2.CID = C.CID))

Query 18: Find all faculties who taught a course in which the average grade for CS students was lower than the other students.

SELECT F.FnameFROM Faculty AS FWHERE F.FID IN (SELECT T1.FID

FROM Take AS T1, Student AS S1WHERE (T1.SID=S1.SID) AND (S1.Curriculum="CS")GROUP BY T1.FIDHAVING Avg(T1.Grade) <all

(SELECT Avg(T2.Grade)FROM Take AS T2, Student AS S2WHERE (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)

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_idFROM COURSE CWHERE NOT EXISTS

(SELECT *FROM TRANSCRIPT T, SECTION SWHERE (T.grade = ’F’) AND (T.section_id =S.section_id)

AND (S.course_number =C.course_number)AND (S.dept_id = C.dept_id)

Query 20: Find the id of all sections of courses offered by department ”Computer Science” in the ”Fall99” semester.

SELECT S.section_idFROM section S, department DWHERE S.dept_id = D.dept_id AND D.department_name = "Computer Science"

AND S.semester = "Fall99"

Query 21: Find the id of all sections that a student named ”Kenny” is taking in ”Fall99” semester.

SELECT T.section_idFROM section S, transcript T, student St WHERE St.sid = T.sid AND S.section_id = T.section_id AND St.firstname = "Kenny" AND S.semester "Fall99"

Query 22: Find the first name, last name and the department name for all instructors who are teaching at least three sections of a single course in semester ’Fall 99’.SELECT I.firstname, I.lastname, D.department_nameFROM INSTRUCTOR I, DEPARTMENT DWHERE (I.dept_id = D.dept_id) ANDEXISTS

(SELECT S.course_number, S.dept_idFROM SECTION SWHERE (S.semester = ’Fall 99’) AND

(S.instructor_id = I.instructor_id)GROUP BY S.course_number, S.dept_idHAVING count(S.section_id) >= 3)

Query 23: For all different courses in the curriculum, find the total number of students in that class for each different semester (regardless of their sections).

SELECT SC.course_number, SC.dept_id, SC.semester, count(TR.sid)FROM SECTION SC, TRANSCRIPT TRWHERE SC.section_id = TR.section_idGROUP BY SC.course_number, SC.dept_id, SC.semester

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

Query 25: Set the grade of student named ”Kenny” for course number 111, dept id 15 offered in ”Fall99” to ”F”.UPDATE transcriptSET grade = ’F’WHERE sid IN

(SELECT S.sidFROM student SWHERE S.firstname = ’Kenny’) AND section_id IN

(SELECT Sc.section_id FROM section ScWHERE Sc.course_number = 111 AND Sc.dept_id = 15 AND Sc.semester = ’Fall99’)

Query 26: Insert a tuple into transcript indicating that ”Kenny” is taking course number 111, dept id 15 in ”Fall99”.

INSERT INTO transcript(sid, section_number)SELECT DISTINCT S.sid, Sc.section_numberFROM student S, section ScWHERE S.firstname = ’Kenny’ AND Sc.dept_id = 15

AND Sc.course_number = 111 AND Sc.semester = ’Fall99’