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 number AND 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 identifier d 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 number 2 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 pno d 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 count essn 1
View Full Document
Unlocking...