Using MySQL Workbench, create a new schema for this assignment and import assignment2.sql. Reverse-engineer the database to produce the ER diagram for this database. Rely on the ERD to perform remaining tasks. Write and execute SQL commands to get information from the database: Display a single number showing the total number of customers in the database. Display a single number showing how many products categories are available (do not include duplicates). Display a single number showing how many shipping addresses are from Ontario. Join the payment_info and payments table together based on PK/FK. Display the count of records for each payment type. Display a single number showing how many invoices were issued in 2013. 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. Display the lowest, highest, and mean amounts on the invoice table? 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. 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.

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

Using MySQL Workbench, create a new schema for this assignment and import assignment2.sql.
Reverse-engineer the database to produce the ER diagram for this database. Rely on the ERD to perform remaining tasks.
Write and execute SQL commands to get information from the database:
Display a single number showing the total number of customers in the database.
Display a single number showing how many products categories are available (do not include duplicates).
Display a single number showing how many shipping addresses are from Ontario.
Join the payment_info and payments table together based on PK/FK. Display the count of records for each payment type.
Display a single number showing how many invoices were issued in 2013.
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.
Display the lowest, highest, and mean amounts on the invoice table?
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.
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.

Expert Solution

Step by step

Solved in 3 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions