Consider a relational database schema that consists of the 3 relation schemes gi
ID: 3632315 • Letter: C
Question
Consider a relational database schema that consists of the 3 relation schemes given below; where the attributes S, C, P and Secrefer to student, course, professor and section, respectively. enrolls (S, C, Sec) teaches (P, C, Sec) likes (S, C) Express each query below in SQL. (Use SQL-standard as much as possible as was covered in class.) List each student enrolling in any class with professor Dan. List each student enrolling in at least one course he or she likes. List each student enrolling only in courses he/she does not like. Find the number of students enrolling in any class with professor Dan.Explanation / Answer
Ans for Q. a) select e.* from enrolls e inner join teaches t on e.C==t.C where t.P='Dan'; Ans for Q. b) select e.* from enrolls e inner join likes l on e.C==l.C ; Ans for Q. c) select e.* from enrolls e left join likes l on e.S=l.S where e.C not in (select C from likes l2 where l2.S=e.S); Ans for Q. d) select count(distinct(e.S)) from enrolls e inner join teaches t on e.C==t.C where t.P='Dan'; Hope the above information helps you. Do let me know, if you need anything further in detail. Thanks and Regards, Deepu