Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
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.

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.

A Guide to SQL
A Guide to SQL
9th Edition
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Philip J. Pratt
Chapter8: Sql Functions And Procedures
Section: Chapter Questions
Problem 9TD
See similar textbooks
icon
Related questions
Question
  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:
  4. Display a single number showing the total number of customers in the database.
  5. Display a single number showing how many products categories are available (do not include duplicates).
  6. Display a single number showing how many shipping addresses are from Ontario.
  7. Join the payment_info and payments table together based on PK/FK. Display the count of records for each payment type.
  8. Display a single number showing how many invoices were issued in 2013.
  9. 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.
  10. Display the lowest, highest, and mean amounts on the invoice table?
  11. 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. 
  12. 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
steps

Step by step

Solved in 3 steps

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Knowledge Booster
SQL Query
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.
Recommended textbooks for you
A Guide to SQL
A Guide to SQL
Computer Science
ISBN:
9781111527273
Author:
Philip J. Pratt
Publisher:
Course Technology Ptr
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781285196145
Author:
Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:
Cengage Learning
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781305627482
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Oracle 12c: SQL
Oracle 12c: SQL
Computer Science
ISBN:
9781305251038
Author:
Joan Casteel
Publisher:
Cengage Learning
SEE MORE TEXTBOOKS