Database Question Relational Schema: Book(bookid, title, author, category, year,

Question

Database Question

Relational Schema: Book(bookid, title, author, category, year, liblocation) Student(studentid, name, major, gender) Borrow(studentid, bookid, borrowdate) Library(liblocation,name, address) The schema above is a segment of a library database. It contains some basic information about books in different libraries and the students who borrow them. For each book, the database stores its unique ID, the book title, the author, the category it belongs to (e.g., Computer Science, Medicine), the year of publication, and the location of the library where the book is located. The student table records the student’s unique ID, the name and some other information. Similarly, the database keeps record of the borrowing transactions, and library address. There are a few important things that you should know about this schema. (1) You should consider Book.year as integers. (2) You should consider Borrowdate as date field. Except for the two attributes mentioned above, all other attributes are strings.

(a) Write an SQL query that outputs the names of all students who have borrowed books from the library at ‘100 Washington Street’.
(b) Write an SQL query that outputs for each book category the number of times books have been borrowed so far.
(c) Write a correlated SQL subquery that outputs for each category the title of books published at least 20 years after the first book was published in its category. Output the category, title and year in each tuple.
(d) Write an SQL query that outputs the names of students who have borrowed more books than the average number of books borrowed by all students.
(e) Write an SQL query that outputs the names of students who have borrowed the same book more than once.

Explanation / Answer

a) answer
select distinct s.name from Borrow as b
inner join Student as s on s.studentid = b.studentid
inner join Book as boo on boo.bookid=b.bookid
inner join Library as l on l.liblocation =boo.loblocation
where l.address='100 Washington Street';

b) answer
select b.category, count(b.bookid) from Book as boo
inner join Borrow as b on boo.bookid=b.bookid
group by b.category;
We can you "group by 1" instead of above group by line

c) answer
select category, title,year from Book as b where year =
(select min(b1.year) from book as b1 where b1.category=b.category)
order by 1

d) answer
select distinct s.studentid, s.name from Borrow as b
inner join Student as s on s.studentid = b.studentid
group by 1
having count(s.studentid)>((select count(studentid) from Borrow)/(select count

(studentid) from Student))

e) answer
select name from student where studentid in (
select t.studentid from (select studentid, bookid, count(bookid) as c from borrow
group by 1,2) as t where t.c>1)

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.