Question

Introduction to Oracle 12c SQL and DB concepts:

books ( book_id, title, author_last_name, author_first_name, rating)

patrons (patron_id, last_name, first_name, street_address, city_state_zip, location)

transactions (transaction_id, patron_id, book_id, transaction_date, transaction_type)

*Book_Id, Patron_id and Transaction_id are primary keys. Patron_id and book_id in transactions table are foreign keys.

Possible values for the transaction type are 1 = checking out , 2 = returning 3 = placing a hold.

1. Write SQL to list all patrons and their number of transactions for each transaction type. Include all patrons even if they do not have transactions.
2. Write SQL to list all the books (book ids and first 10 characters of the title) and their total number of transactions in 2013. Include all books even if they have no transactions (count should be 0). Sort the results by the most “popular” book (the book with the most transactions).

Answer 1

Answer 1:

select a.last_name,b.type1,(select count(*) from patrons left join transactions on patrons.patron_id=transactions.patron_id where patrons.patron_id=a.patron_id) as count from patrons a,(select distinct(transaction_type) as type1 from transactions) b order by a.last_name;

Output:

Answer 2:

select substr(Title,0,10),(select count(*) from transactions where book_id=books.BOOK_ID and to_char(to_date(TRANSACTION_DATE,'YYYY-MM-DD') , 'YYYY')='2013')as count from books;