I'm currently working on this subqueries homework assignment, but I'm having a little difficulty. I was able to figure out 1 and 3. and most of two but I'm having the hardest time with 4 and 5. any tips? I'm using the JUSTLEE books database
- Write a query that displays the title, ISBN, and wholesale cost of books that cost more than the average of all books. Format the cost with dollars and cents.
Select title,isbn,to_char(cost($'999.99'))
FROM books
WHERE retail>(SELECT AVG(retail)
FROM books);
2. Write a query that displays the title and publication date of the oldest book in the BOOKS table. Format the date with the complete name of the month and a comma after the day of the month, like "January 3, 2011."
SELECT title, isbn
FROM books
WHERE pubdate=
(SELECT MIN(pubdate)
FROM books);
3. Write a query that shows the title(s) of the book or books most frequently purchased by the customers in the database.
Select title
FROM books
WHERE isbn=
(Select isbn
FROM orderitems
HAVING SUM(quantity)=(SELECT MAX(SUM(quantity))
FROM orderitems GROUP BY isbn)
GROUP BY isbn);
CAN ANYONE HELP WITH THESE TWO??
4.Write a query that displays the names of the customers who purchased the book with the highest retail price in the database. Capitalize the first and last names.
5. Write a query that displays the first name and last name of each author along with the number of books he or she has written. Capitalize the first and last names.