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 (you can test your SQL code in Access to make sure it returns the correct data, but you are not required to submit the Access file with the queries). 1. Show the isbn, title of the books authored by Mark Twain. 2. Show the total number of different books offered by the bookstore. 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). 3. 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:

1)

SELECT isbn, title FROM Book WHERE author= 'Mark Twain'

2)

SELECT DISTINCT(COUNT(isbn)) FROM Book

3)

SELECT COUNT(order_number) FROM Orders WHERE customer_number= 11 AND ordered_filled= No

4)

SELECT author, SUM (quantity) FROM Book, OrderDetails GROUP BY author

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.

9)

SELECT first_name, last_name, SUM(quantity*price) FROM Book, Customer, Order WHERE order_number IS NOT NULL AND state= 'NY' AND SUM(qty*price)>= 100 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.