Assignment Instruction
1. Using MySQL Workbench, create a new schema for this assignment and import assignment2.sql.
2. Reverse-engineer the database to produce the ER diagram for this database. Rely on the ERD to perform remaining tasks.
3. Write and execute SQL commands to get information from the database:
i. Display a single number showing the total number of customers in the database.
ii. Display a single number showing how many products categories are available (do not include duplicates).
iii. Display a single number showing how many shipping addresses are from Ontario.
iv. Join the payment_info and payments table together based on PK/FK. Display the count of records for each payment type.
v. Display a single number showing how many invoices were issued in 2013.
vi. Join the customers and shipping_details tables together based on PK/FK. Display the province code and the number of records per province. Data should be sorted in descending order of the number of records.
vii. Display the lowest, highest, and median amounts on the invoice table?
viii. Join the customers and customer_product tables together based on PK/FK. The SQL should display the customer’s first and last names, the email (in uppercase) and the ordered amount. Only records where the ordered_amount is greater or equal to 5 should be displayed.
ix. Referring to the customers and shipping_details tables on the ER, display all data from the customers table but only where the province on the shipping_details table is equal to NS. Do not use a join for this requirement.
4.
Copy and paste each of the SQL statements from #3 into a single Word document. Make sure to specify the requirement number ahead of the SQL, i.e., 3i, 3ii, etc. If the file does not include the requirement number before the SQL, the submission will not be graded.