This assignment will bring together everything we’ve learned in this session. It is a challenge, so feel free to work together and ask questions!
Step 1: Run these two scripts in order. They will create database objects and then fill the tables. To run each of the scripts, open one file at a time, highlight everything (or hit Control + A on Windows, Command + A on Mac) and then click RUN.
BikeStores Sample Database - create objects.sql
BikeStores Sample Database - load data.sql
These files create a new sample database for you to play around with. This database will be used for the below questions, but is also a great way to see how databases and tables are created!
Step 2: Write queries that answer the following questions in a file called CTA_LastName.sql. Make sure to run each query to ensure it runs. Questions are color-coded from easiest (green) to most difficult (red).
How many orders were shipped after the due (required) date?
Name all customers who live in New York and provided a phone number.
List all staff member names (no duplicates) who had a discount greater than 5% (0.05)
Rank each of the customers by number of orders. Make sure to list the customer name and number of orders.
List all customers who ordered from multiple stores.
Name all stores (with store name, city, and state), how many unique customers have ordered from each (including zeros), and total number of orders.
For customers with more than 1 order, calculate the minimum, maximum, and average number of dates between orders. Note: I am asking for one minimum, one maximum, and one average. I specify for customers with more than 1 order so you don't average in any 0s. (For a walkthrough of how to solve this, please see the video under Resources).