Hi, I'm new using SQL Developer and I'm working on a query were I need help.
This is the schema of the database I'm working on:
I'm trying to show all customers who have checked more books than DVDs, and I want to have customer name, total book checkouts and total DVD checkouts.
So far I have this on my query but I'm still short to get what I want and I'm stock in here:
Select Customer_Firstname,Customer_Lastname,
(Select Count (Transaction\_ID) From Transaction Where Book.Catalog\_Item\_ID = Physical\_Item.Catalog\_Item\_ID
And Physical\_Item.Physical\_Item\_ID = Transaction.Physical\_item\_ID) As Books
From Customer
Inner Join Library\_card On Customer.Customer\_ID = Library\_card.Customer\_ID
Inner Join Transaction On Library\_card.CUSTOMER\_ID = Transaction.LIBRARY\_CARD\_ID
Inner Join Physical\_item On Transaction.PHYSICAL\_ITEM\_ID = Physical\_item.PHYSICAL\_ITEM\_ID
Inner Join CATALOG\_ITEM On CATALOG\_ITEM.CATALOG\_ITEM\_ID = Physical\_item.CATALOG\_ITEM\_ID
Inner Join Book on Book.CATALOG\_ITEM\_ID = CATALOG\_ITEM.CATALOG\_ITEM\_ID;
With this query I can get the Customers that have checkout books and qty of books, but now I don't know how to do the same for the DVDs and compare both to just display those Customers with more books than DVDs.
Thanks for any help or guidance!