Question

Do these codes look right for the following 5 statements. This is using Oracle SQL:

1. Write a query that displays the title, ISBN, and wholesale cost of books whose wholesale cost is more than the average of all books. Format the retail price with dollars and cents.
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."
3. Write a query that shows the title(s) of the book most frequently purchased by the customers in the database. Use the quantity column from the orderitems table to find the book most frequently purchased.
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.

--1

SELECT title, isbn, cost, TO_CHAR(retail, '$999.99') AS retail

FROM books

WHERE cost <

(SELECT AVG (cost)

FROM books);

--2

SELECT title

TO_CHAR(pubdate, 'Month DD, YYYY') "Publication Date"

FROM books;

--3

SELECT title

FROM books

WHERE isbn = (select isbn from orderitems HAVING SUM (quantity) = (select MAX(SUM(quantity))

FROM orderitems

GROUP BY isbn);

--4

SELECT INITCAP (firstname) AS "First Name", INITCAP (lastname) AS "Lastname"

FROM customers

JOIN orders Using (customer#)

JOIN orderitems Using (Order#)

JOIN books USING (isbn)

WHERE retail =

(SELECT MAX(retail) FROM books;

--5

SELECT fname, lname, booknum AS "Number of Books"

FROM author join

(SELECT COUNT (isbn), booknum, authorid

FROM bookauthor

GROUP BY authorid)

USING (authorid);

Answer 1

Yes, the SQL queries look fine to me. Please run these in the database one by one and check if you are getting the desired output. You might see 2 issues:

1. issues with some syntax or any other error before it generates any output.

In this case please post the error message in the comment section, I can take a look and let you know for the corrections that may need. Looking at the data, it looks like fine and , you should not receive any error.

2. You may not get desired output as needed:

If so, please post actual output you need and the output you are getting. I can take a look and suggest you any changed you need.

best of luck!