Consider the following relations for a database that keeps track of student enro
ID: 3681028 • Letter: C
Question
Consider the following relations for a database that keeps track of student enrollment in courses and the books adopted for each course:
STUDENT (Ssn, Name, Major, Bdate)
COURSE (Course#, Cname, Dept)
ENROLL (Ssn, Course#, Quarter, Grade)
BOOK_ADOPTION (Course#, Quarter, Book_isbn)
TEXT (Book_isbn, Book_title, Publisher, Author)
Retrieve the names of departments that adopt textbooks written by Navathe and published by Addison-Wesley.
Select major from student where
Retrieve the names of students who have enrolled in a course that uses a textbook published by Addison-Wesley.
List the number of courses taken by all students named John Smith in Winter 2009 (i.e., Quarter=W09).
Explanation / Answer
1) Answer:
select Dept from ENROLL where ENROLL.Course# = ( select Course# from BOOK_ADOPTION where Book_isbn = (select Book_isbn from TEXT where (TEXT.Author = 'Navathe' and Publisher = 'Addison-Wesley')));
2) Answer:
select Major from STUDENT where Name = 'Robert';
3) Answer:
select Name from STUDENT where Ssn = (select Ssn from ENROLL where Course# = ( select Course# from BOOK_ADOPTION where Book_isbn = ( select Book_isbn from TEXT where Publisher = 'Addision-Wesley')));
4) Answer:
select count(*) from STUDENT where Name = 'John Smith' and Ssn = (select Ssn from ENROLL where Quarter = 'W09');