1.a. SELECT Name FROM STUDENT WHERE Major='CS'b. SELECT course_name FROM COURSE as c, SECTION as s WHERE c.course_number=s.course_numberAND instructor='King' AND (year='07' OR year='08')c. SELECT course_number, semester, year, COUNT(*) as noof_students FROM SECTION as s, GRADE_REPORT as g WHERE instructor='King' AND s.section_identifier=g.section_identifierd. SELECT s.name, c.course_name, c.course_number, c.credit_hours, se.semester, se.year, g.grade FROM STUDENT s, COURSE c, SECTION se, GRADE_REPORT g WHERE Class=4 AND Major='CS' AND s.student_number=g.student_number AND g.section_identifier =se.section_identifier AND se.course_number =c.course_number2.a. when this query is run, then where ever employees with super_ssn referring to 888665555 will also get deleted.b. so to avoid deleting of other employees from employee table it is better to set EMPSUPERFK to null.3.a. select d.dname, count(e.ssn) as employee_count from department d, employee e where d.dnumber=e.dno group by e.dno having avg(e.salary)>30000;b.yes it can be done in sql. select dname, dnumber, COUNT(ssn) as males_count from EMPLOYEE, department where dno=dnumber and sex='m' group by dname having avg(salary)>30000;4.a. SELECT name, major FROM STUDENT s WHERE NOT EXISTS ( SELECT * FROM GRADE_REPORT g WHERE g.student_number= s.student_number AND NOT(g.grade='A'))b. SELECT name, major FROM STUDENT s where NOT EXISTS ( SELECT * FROM GRADE_REPORT g WHERE g.student_number= s.student_number AND g.grade='A' ) 5.a. select fname, lname from employee e where e.dno= (select dno from employee x where x.salary=(select max(salary) from employee));b. select fname, lname from employee where super_ssn in (select ssn from employee where super_ssn='888665555' );6.a. create view dept as select d.dname, e.fname,e.salary from department as d, employee as e where e.ssn=d.mgr_ssn group by dnumber;b. create view empResearch as select e.fname, s.fname as mgrName, e.salary from employee e, employee s where e.super_ssn=s.ssn and e.dno=(select dnumber from department where dname='research');c. create view projectemp as select p.pname, d.dname, count(essn), sum(hours) from project p, department d, works_on w where p.dnum=d.dnumber and p.pnumber=w.pno group by w.pnod. create view projemp as select p.pname, d.dname, count(essn), sum(hours) from project p, department d, works_on w where p.dnum=d.dnumber and p.pnumber=w.pno group by w.pno having
View Full Document
Unlocking...