Question

Given the following relations about students borrow books from a university library.

Student (StudentID: integer, Name: string, EnrollDate: date)

Book(Title: string, Author: string, Quantity: integer)

BorrowRecord(StudentID: integer, BookTitle: string, BookAuthor: string, BorrowDate: date, DueDate: date, Returned: integer)

Note that there are no duplicate records in the three relations, and there can be duplicates of book titles. For Returned, 0 means not returned and 1 indicates the books have been returned.

Write the following queries in SQL.

(a) Find the StudentIDs of students who don’t return the books on time (Assume today is “23-OCT-20”). (4 points)

(b) Find the Names of students who have borrowed the book "SQL theory" written by “Paul”.  (4 points)

(c) Find the Titles and corresponding Authors of books which have never been borrowed. (4 points)

(d) Find the Names of students who have borrowed every book written by ‘Paul’. (4 points)

Answer 1