Please use SQL to answer queries. STUDENT(Ssn, Name, Major, Bdate) COURSE(Course
ID: 3715029 • Letter: P
Question
Please use SQL to answer queries.
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)
1.Retrieve the names of courses in which the textbook has been changed at least once.
2.Retrieve the names of departments that adopt textbooks published by Addison-Wesley only.
3. Retrieve the names of students who have never used a book (in a course) written by Navathe and published by Addison-Wesley.
Explanation / Answer
2. SELECT Dept FROM COURSE WHERE Course# in (select course# from BOOK_ADOPTION WHERE BOOK_ISBN IN (SELECT BOOK_ISBN FROM TEXT WHERE PUBLISHER ='ADDISON-WESLEY'))
3. SELECT Name from STUDENT where Ssn not in (select ssn from ENROLL where course# in (select course# from book_adoption where book_isbn in (select book_isbn from text where author='NAVATHE' and Publisher='ADDISON-WESLEY')) )