1.a)SELECT Name FROM student WHERE Major = 'CS';b)SELECT c.Course_name FROM section s JOIN course c ON s.Course_number = c.Course_numberWHERE s.Instructor = 'King' AND (s.Year = '08' OR s.Year = '07');c)SELECT s.course_number, s.semester , s.year, count(g.student_number) from section s JOIN grade_report g ON s.section_identifier = g.section_identifier where s.instructor = 'KING';d)SELECT st.name, c.Course_name, c.Course_number, c.Credit_hours, s.Semester, s.Year, g.Grade FROM course cINNER JOIN section s ON c.Course_number = s.Course_number INNER JOIN grade_report g ON s.Section_identifier = g.Section_identifierINNER JOIN student st ON g.Student_number = st.Student_number WHERE st.Class = '2';2.a.If ON DELETE CASCADE is executed, when the record with Lname = 'Borg' is deleted, the rows corresponding to the employees with super_ssn who have same value of ssn as Borg will also deleted (Records with Lname Wong and Wallace willget deleted)b.It is always better to SET NULL, When we delete an employee, it is not necessaryfor people reporting under him to be deleted. 3.a.select d.dname, count(e.fname) from employee e join department d on e.dno = d.dnumber group by e.dno having avg(e.salary)>30000;b.select d.dname ,count(e.fname) from department d join employee e on e.dno = d.dnumber where e.salary>30000 and sex='M' group by d.dnumber ;4.a.select s.name, s.major from student s join grade_report g on s.student_number=g.student_numberwhere g.grade = 'A' group by g.student_number having count(g.student_number) = (select count(student_number) from grade_report where student_number = g.student_number);b.select s.name, s.major from student s join grade_report g on s.student_number=g.student_numberwhere g.grade <> 'A' group by g.student_number having count(g.student_number) = (select count(student_number) from grade_report where student_number = g.student_number);5.a.select e.fname, e.minit,e.lname from employee e where e.dno=(select dno from employee where salary=(select max(salary) from employee));b.select e.fname, e.minit,e.lname from employee e, employee s where e.super_ssn=s.SSN and s.super_ssn=888665555;6.a.create view Dept_Manager_View asselect d.dname , e.fname as employee_Fname, e.minit as employee_Mname,e.lname asemployee_Lname, e.salaryfrom department d,employee ewhere d.mgr_ssn=e.ssn ;b.create view Research_Employee_View asselect e.fname as employee_Fname, e.minit as employee_Mname,e.lname as employee_Lname,s.fname as supervisor_Fname, s.minit as supervisor_Mname,s.lname as supervisor_Lname,e.salary from employee e,employee s,department dwhere d.dnumber=e.dno and d.Dname='Research' and e.super_ssn=s.SSN;c.create view proj_hours_view asselect p.pname as Project , d.dname as Department, sum(h.hours) as Total_Hours, count(h.essn) as Emp_Countfrom project p join works_on h on p.pnumber = h.pno join department d on p.dnum = d.dnumbergroup by p.pnumberd.create view proj_hours_view_emp_gt1 asselect p.pname as Project , d.dname as Department, sum(h.hours) as Total_Hours, count(h.essn) as Emp_Countfrom project p join works_on h on p.pnumber = h.pno join department d on p.dnum = d.dnumbergroup by p.pnumber having
View Full Document
Unlocking...