CS2102 Exercise I. (25 marks) Consider the following self-describing schema with

Question

CS2102 Exercise I. (25 marks) Consider the following self-describing schema with the underlined primary key and the corresponding foreign keys person pid, name) read pid, isbn) bookisbn, title) Consider primary and foreign key constraints to simplify your answers. Note that two different books may have the same title but different ISBN. The database does not contain NULL values. Question 11. (4 marks) (sQL) Print the titles of books and the names of persons who read them. Print the resut in alphabetical order of titles and persons. Question 12. (4 marks) (SQL) Print the titles of books read by 100 different persons or more. Question 13. (4 marks) (Algebra) Print the titles of books read by more one person.

Explanation / Answer

Question 11)

select p.name,b.title from
person p inner join read r on p.pid=r.pid
inner join book b on r.isbn=b.isbn
order by b.title,p.name

Question 12)

select title from (select b.title as title , count(*) as count from book b inner join read r
on b.isbn=r.isbn group by b.isbn) where count>100

Question 13)

select title from (select b.title as title , count(*) as count from book b inner join read r
on b.isbn=r.isbn group by b.isbn) where count>1

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---