1) a) SELECT Name FROM student WHERE Major = "CS"; b) Select course_name from course where course_number in (select course_number from Section where Instructor="king" AND year ="07" OR "08"; (or) SELECT c.Course_name FROM section s JOIN course c ON s.Course_number = c.Course_number WHERE s.Instructor = "King" AND (s.Year = "08" OR s.Year = "07"); For each section taught by Professor King, retrieve the course number, semester,year, and number of students who took the section.c) SELECT s.Course_number, s.Semester, s.Year, COUNT(g.Student_number) AS Num_Students FROM section s JOIN grade_report g ON s.Section_identifier = g.Section_identifier WHERE s.Instructor = "King" ;Retrieve the name and transcript of each senior student (Class = 4) majoring in CS. A transcript includes course name, course number, credit hours, semester, year, and grade for each course completed by the student.d) SELECT st.name, c.Course_name, c.Course_number, c.Credit_hours, s.Semester, s.Year, g.Grade FROM student st, course c, grade_report g, section s WHERE st.Student_number = g.Student_number AND g.Section_identifier = s.Section_identifier AND s.Course_number = c.Course_number AND st.Class = "4";2) a) When we delete the employee record where Lname = "Borg", The super_ssn records which is same of the SSN will also get deleted.b) It is better to have SET NULL with ON DELETE, since instead of deleting the entire row of SUPER_SSN which is same as SSN we can SET the value to NULL. SO, we can save the records for the other employess ( in our case, the record which belongs to the super_ssn same as ssn)3) a) select Dname, COunt(*) as number of employees from DEPARTMENT , EMPLOYEE where DEPARTMENT.Dnumber=EMPLOYEE.Dno GROUP BY Dname,Dno HAVING AVG(SALARY)>30000;b) 4)a) SELECT Name, Major FROM STUDENTWHERE EXISTS ( SELECT *FROM GRADE_REPORTWHERE StudentNumber= STUDENT.StudentNumber AND Grade='A');b) SELECT Name, Major FROM STUDENTWHERE NOT EXISTS ( SELECT *FROM GRADE_REPORTWHERE StudentNumber= STUDENT.StudentNumber AND Grade='A');5)a) select fname, lname from employee where dno = (select dnofrom employee where salary = (select max(salary) from employee;b) Select e1.lname from employee e1, employee e2 where e1.superssn = e2.ssn and e2.superssn = '888665555';6) a) CREATE VIEW QA (Department_name,manager_name,manager_salary) as Select Dname,mgr_ssn,salary from DEPARTMENT,EMPLOYEE where mgr_ssn=ssn;b) CREATE VIEW QB (employee_name , supervisor_name,employee_salary)as Select E1.Fname , E1.Lname , E2.Fname , E2.Lname , E1.salaryfrom EMPLOYEE E1 E2where E1.super_ssn = E2.ssn and EMPLOYEE.Dno=DEPARTMENT.Dnumber and DEPARTMENT.Dname="Research";c) Create VIEW QC (Project_name,COntrolling_depatrment,number_of_employees,total_hours) AS Select Pname,Dname,Count(essn),sum(hours) from project,DEPARTMENT,WORKS_ON PROJECT.Dnum=DEPARTMENT.Dnumber and PROJECT.Pnumber=WORKS_ON.pno GROUP BY Pname,Dname;d) c) Create VIEW QC (Project_name,COntrolling_depatrment,number_of_employees,total_hours) AS Select Pname,Dname,Count(essn),sum(hours) from project,DEPARTMENT,WORKS_ON PROJECT.Dnum=DEPARTMENT.Dnumber and PROJECT.Pnumber=WORKS_ON.pno GROUP BY Pname,Dname having
View Full Document