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.

Complete the following Updating Data Activities using SQL. After completing the required SQL activity, use a Select clause after each activity to display the fields changed to prove the results is correct. 1. Change the description of part BT04 to Oven. 2. Add $100 to the credit limit of every customer represented by sales rep number 06. 3. Add order 12600 (order date: 09/06/2018, customer number: 311) to the database. Add two order lines to this order. On the first line, the part number is AX12, the number ordered is 5, and the quoted price is $13.95. On the second line, the part number is BA74, the number ordered is 3, and the quoted price is $4.50. 4. Write the command to delete every customer whose balance is $0 and who is represented by sales rep 12. Do not execute the command. 5. Create a new table named SPGOOD to contain the columns PART_NUMBER, DESCRIPTION, and UNIT_PRICE. Then insert into this new table the part number, part description, and unit price from the PART table…

Task 5: The InstantRide Management team considers creating a new team for Car Maintenance. The new team needs to find/list the cars that are used more than average with the usage count. Collect the information of all rides and consolidate over the Car IDs. You need to create a three level SQL statement. Firstly, you need to COUNT the number of rows in TRAVELS and GROUP_BY the CAR ID field. Then you need to calculate the AVG of the data to find the average usage of the cars. Finally, you need to return CAR_ID and the TRAVELS count (as the Usages column) filtered to only values greater than the calculated average. Task Query all cars used more than the average.

Given the table PRODUCT(ProductID, ProductName, ProductClass, ProductWeight, ProductStyle, ProductColor, ProductPrice). Note: ProductWeight and ProductPrice are numeric fields, and the other attributes are character fields, i.e., store text. Write a SQL query to display the product class whose average price is at least 50