SQL Assignment: Summarizing Data
Create SQL commands to answer the following questions using the tables shown below from the classicmodelsdatabase. Paste your SQL code below each question along with a screen shot of the first few rows of the results grid.
Submit the completed document in Moodle.
For all problems, use a single word alias (no quote marks) for any calculated fields. Limit long output to the first 5 rows.
Example: SELECT AVG(amount) AS Avg_Payment
The following questions use aggregate functions and all will require a GROUP BY clause. Some of them will require a HAVING clause if you are making selection criteria based on results from aggregated functions.
1. Show the count of orders by status in the orders table. Use the alias N_Status for the counts.
2. Show the count of orders by customer number in the orders table. Use the alias N_Status for the counts. Order by counts from highest to lowest.
3. Write a query to show the customer number, the count of checks, and the total payments made by customer. Use the aliases N_Checks and Total_Paid. Order by the total paid.
4. Show the minimum, maximum, and average credit limit by city. Use the aliases Min_Limit, Max_Limit, and Average_Limit. Sort by city in alphabetical order.
5. Show the number of customers and average credit limit for customers in zip code (postalCode) 94217. Use appropriate aliases and round the average credit limit to 0 decimal places. (HINT: use a WHERE clause for the zip code.)
6. Show the number of payments received each month in 2003.
7. Show the months in 2003 that had more than 10 payments. (HINT: You need a HAVING clause.)
8. Show the average credit limit by city for all cities that have anaverage limit between $99,000 and $150,000. Use the alias Average_Limit for the averages. Sort by the average credit limits, largest to smallest. (HINT: You need a HAVING clause.)
9. Show the count of customers by state for all states that have more than 2 customers. Use the alias N_State for the counts. Sort by state in alphabetical order. Make sure you do not count customers that have Null values in the state field. (HINT: You need a HAVING clause.)
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also includes a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.