Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
SQL data manipulation and Subqueries
Submission instructions
What to submit:
· One PDF file with the SQL queries code.
Questions
Download bookstoreHW.sql (do not edit this file). This is the same database of Exercise 5.
If you have not done so already, start by importing the database to MySQL Workbench (make sure to follow exactly the instructions on how to import/export databases in MySQL workbench). Then, create the entity-relationship of the database and export it to a pdf. It is important to have the diagram handy when creating the queries.
Now, create SQL code for each of the questions below. First, run the code on My SQL Workbench, then copy the code to a Word file, and finally, after you have all codes, convert the Word file to a PDF and submit it.
Data manipulation queries:
1. Think of your favorite book. Add it to the books table. You can leave number_on_hand NULL or make up some number.
2. Change the price of the book titled 'Chimera' authored by John Barth to 21.49.
3. Change all orders from customer 11 placed before February 1st 2016 to filled (you need to set order_filled to 1).
4. Delete customer named Jerry Brown from New Jersey.
Subqueries:
The queries below are meant to be solved with the subqueries technique, which automatically means they are not easy :) So make sure to think in a problem-solving way about how to use SQL to achieve the result. You must use a single query to answer the questions below, just like I did in class (a single SQL query may have multiple SELECT statements).
5. Find the average book retail price, and then use it to retrieve the books that have a retail price above the average.
You need to first create a query that finds the average book retal price. This query will return a single number corresponding to the average. Then you will use this first query in a second query that retrieves the books with retail price above the average.
6. Each order is placed by a single customer. Show the maximum number of orders placed by a single customer. Your query should show a single cell with the number corresponding to the maximum number of orders.
For example, suppose you have three customers: customer 1 placed 5 orders; customer 2 placed 10 orders; customer 3 placed 20 orders. In this case, the maximum number of orders placed by a customer is 20. It is number 20 that the query is supposed to return.
First, you need to create a query that calculates the number of orders per customer. You will then use this query in a second query that calculates the maximum.
7. Find the average number of units sold per book.
First, you need to create a query that calculates the quantity sold per book. You will then use this query in a second query that calculates the average