You will need to add a CREATE DATABASE and USE database statement at the beginning of the script in the file of the Sample Database in order for SQL Script in the Sample Database to execute/run correctly
If you discover any ambiguity between the written Metadata and the actual table definitions, the path that I recommend is to use the SHOW TABLES Statement and the DESCRIBE Table Statement to eliminate any ambiguity between the written Metadata and the actual Metadata.
CustOrders.sql download
Aggregation Part of the Exercise
. Select the maximum price of any orders in the items_ordered table. Hint: Select the maximum price only.
. Select the average price of all of the orders that were purchased in the month of Dec.
. Return the total number of rows in the orders table?
. For all of the tents that were ordered in the orders table, what is the price of the lowest tent? Hint: Your query should return the price only.
Group By Exercises
. How many people are in each unique state on the customer table? Select the state and display the number of people in each state. Hint:count is used to counting rows in a column,sum works on numeric data only.
. From the items_ordered table, select the item, maximum price, and minimum price for each specific item in the table. Hint: The items will need to be broken up into separate groups.
. How many orders did each customer make? Use the items_ordered table. Select the customer identification, the number of orders they made, and the sum of their orders.
Having Clause Exercise
. How many people are in each unique state in the customer table that has more than one person in the state? Select the state and display the number of how many people are in each if it's greater than 1.
. From the order table, select the item, maximum price, and minimum price for each specific item in the table. Only display the results if the maximum price for one of the items is greater than 190.00.
. How many orders did each customer make? Use the orders table. Select the customer identification number, the number of orders they made, and the sum of their orders if they purchased more than 1 item.
Ordered By Exercises
. Select the last name, first name, and city for all customers in the customer table. Display the results in Ascending Order based on the last name.
. Same thing as exercise #1, but display the results in descending order.
. Select the item and price for all of the items in the items_ordered table that the price is greater than 10.00. Display the results in Ascending order based on the price.
IN a BETWEEN Exercises
Select the date, item, and price from the orders table for all of the rows that have a price value ranging from 10.00 to 80.00.
Select the first name, city, and state from the customer table for all of the rows where the state value is either: Arizona, Washington, Oklahoma, Colorado, or Hawaii.
Submit an SQL Text File for the SQL portion of this assignment. An SQL Text File is a text file with a ".SQL" extension on the file name.
- The script should be well documented with comments
- All the script should be placed in one file
- The script should run continuously without errors