“Using the ssh, Oracle database 11g” Group Functions 1. Determine how many books
ID: 3573610 • Letter: #
Question
“Using the ssh, Oracle database 11g” Group Functions
1.Determine how many books are in the Cooking category
2.Display the number of books with a retail price of more than $30.00
3.Display the most recent publication date of all books sold by JustLee Books
4.Determine how many orders have been placed by each customer. Do not include in the results any customer who hasn’t recently placed an order with JustLee books.
5.List the retail price of the least expensive book in the computer category
6.Determine the average retail price of books by publisher name and category. Include only the categories children and Computer and the groups with an average retail price greater than $50.
Explanation / Answer
Answer :
1.Determine how many books are in the Cooking category
Answer :
SELECT COUNT(*) FROM books WHERE category = 'COOKING';
........
2.Display the number of books with a retail price of more than $30.00
Answer :
SELECT COUNT(*) FROM books WHERE retail > 30;
...........
3.Display the most recent publication date of all books sold by JustLee Books
Answer :
SELECT MAX(pubdate) FROM books;
........
4.Determine how many orders have been placed by each customer. Do not include in the results any customer who hasn’t recently placed an order with JustLee books.
Answer :
SELECT customer,COUNT(*) FROM orders GROUP BY customer;
........
5.List the retail price of the least expensive book in the computer category
Answer :
SELECT MIN(retail) FROM books WHERE category = 'COMPUTER';
...........
6.Determine the average retail price of books by publisher name and category. Include only the categories children and Computer and the groups with an average retail price greater than $50.
Answer :
SELECT name,category,AVG(retail) FROM books JOIN publisher USING(pubid)
WHERE category IN('COMPUTER' , 'CHILDREN') GROUP BY name,category;
.....................
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.