15 lines
816 B
SQL
15 lines
816 B
SQL
-- get library id
|
|
select libraryid from "library" where city = 'Alexandria';
|
|
|
|
-- get author id
|
|
select authorid from author where familyname = 'Tolkien';
|
|
|
|
-- get all books by the author
|
|
select * from book where authorid = (select authorid from author where familyname = 'Tolkien');
|
|
|
|
-- get all available copies of the books from the above author in the above library
|
|
select book.title, book.publisher, book.genre, book.rrp, count(*) as "number_available" from bookcopy join book on bookcopy.bookid = book.bookid where
|
|
libraryid = (select libraryid from "library" where city = 'Alexandria') and
|
|
bookcopy.bookid in (select bookid from book where authorid = (select authorid from author where familyname = 'Tolkien')) and
|
|
(onloan = false or onloan is null)
|
|
group by book.title, book.publisher, book.genre, book.rrp;
|