Question 1. Download the database bookstore.mdb. This database keeps track of th

Question

Question 1. Download the database bookstore.mdb. This database keeps track of the inventory of books, orders, and customers on a bookstore. Again, for each of the queries specified below, write the corresponding SQL code in a Word file or PDF u can test your SQL code in Access to make suret turns the correct data, but you are not required Only need questions 5-8 answered! Thank you! 1. Show the isbn, title of the books authored by Mark Twain. 2. Show the total number of different books offered by the bookstore. 3. Show the number of orders from customer 11 that have not yet been filled (order filled is of Yes/No type, to compare simply do order filled Yes or order filled-No). 4. For each author, show the author's name and the total number of books from that author sold. Sort the results in order of the number of books, with the largest number of books appearing first. The number of books column should have the heading "number_of_books". 5. For each order, show the order number, order date, the customer first and last name, customer phone, and total cost of that order and whether it was filled or not. 6. Repeat the query 6, but only show orders placed on or after July 1, 2016. 7. Show the list of authors corresponding to books ordered on or after May 2016. The query should show each author only once 8. For each customer show the first name, last name, and total amount spent in books. Sort the results alphabetically by last name, with customers having the same last name sorted alphabetically by first name. 9. Modify the query above to show customers only from NY and that have spent at least $100 in books

Explanation / Answer

Solution:

Query:

5)

SELECT order_number, order_date, first_name, last_name, phone, SUM(cost) FROM Customer, Order GROUP BY order_number

6)

SELECT order_number, order_date, first_name, last_name, phone, SUM(cost) FROM Customer, Order WHERE order_date= 'July 1, 2016' GROUP BY order_number

7)

SELECT DISTINCT(author) FROM Book, order WHERE order_date> 'may 2016'

8)

SELECT first_name, last_name, SUM(quantity*price) FROM Book, Customer, Order WHERE order_number IS NOT NULL ORDER BY last_name, first_name.

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.