The following tables form a Library database held in an RDBMS: Borrower (card_no
ID: 3902214 • Letter: T
Question
The following tables form a Library database held in an RDBMS: Borrower (card_no , last_name , first_name , address, city , state, zip Books (ISBN, title, pub_date , pub_id , list_price, category-id, pub_id) Categories Ccategory.id, category_desc) Author (author_id, last_name, first_name) Bookauthor (ISBN, author_id) Publisher (pub_id, name, contact, phone) Bookloans (ISBN, branch_id, card_no , date_out, due_date) Bookcopies (ISBN, branch id , no_of copies) Branch (branch_id, branch_name, city) Write SQL statements to perform the following queries: 1. Display the last names and first names of all borrower who do not have any books checked out. Use a subquery. written or co-written by William White. USE a subquery Systems-7th Ed" USE a subquery. Display the title column. borrowed a book that was supplied by a publisher with a publisher Name 2. Determine the number of different borrowers who have borrowed books 3. Determine which books were published by the publisher of "Intro to DB 4. Display the last name, first name, and address of borrowers who have that begins with the letter P. Use a subquery. 5. Determine which books have a list price that is less than or equal the average list price of other books in the same category. Display the title, category id, and list price columns.Explanation / Answer
If you have any doubts, please give me comment...
-- 1)
SELECT last_name, first_name
FROM Borrower
WHERE card_no IN(
SELECT card_no
FROM Bookloans
);
--2)
SELECT DISTINCT last_name, first_name
FROM Borrower
WHERE card_no IN(
SELECT card_no
FROM Bookloans
WHERE ISBN IN(
SELECT ISBN
FROM Bookauthor
WHERE author_id IN(
SELECT author_id
FROM Author
WHERE last_name ='White' AND first_name="William"
)
)
);
-- 3)
SELECT title
FROM Books
WHERE pub_id IN(
SELECT pub_id
FROM Publisher
WHERE name = 'Intro to DB Systems-7th Ed'
);
--4)
SELECT last_name, first_name, address
FROM Borrower
WHERE card_no IN(
SELECT card_no
FROM Bookloans
WHERE ISBN IN(
SELECT ISBN
FROM Books
WHERE pub_id IN(
SELECT pub_id
FROM Publisher
WHERE name LIKE 'P%'
)
)
);
-- 5)
SELECT title, category_id, list_price
FROM Books B
WHERE list_pirce <=(
SELECT AVG(list_price)
FROM Books
WHERE B.ISBN != ISBN AND category_id = B.category_id
);
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.