The classic model's database consists of eight tables Offices sales offices Employees All employees, including sales reps who work with customers Customers Orders Orders placed by customers Order Details Line items within an order Payments Payments made by customers against their account Products The list of scale model cars Product Lines The list of product line classification The following is an entity relationship attribute model for the database Use this ERA diagram to determine what tables and columns you will use to answer each of the questions How many rows are in the Orders table (Hint Look up COUNT function) List all columns in the Orders table in reverse numerical order (from largest to smallest) by order number Limit the output to the first 5 rows What are the earliest and latest order dates in the Orders table (Hint Lookup MAX and MIN functions) List employee first names in alphabetical order along with a count of employees that have each first name (Hint you will need GROUP BY and ORDER BY clauses ) What orders do not have a status of Cancelled Sort by order number (Hint Use the not equals operator ) Update Steve Pattersons first name to Steven in the employees table Ensure that your update will not change a person whose first name is Steve but whose last name is not Patterson and will not change a person whose last name is Patterson but whose first name is not Steve (There will be no results grid for this question ) List customer name, contact last name, contact first name, phone, city, and credit limit for customers in the cities of Paris or Lyon in France and who have a credit limit between 100000 and 130000 Do not assume that city names are unique There can be cities with the same name in different countries (Hint Remember to use parentheses in the WHERE clause if you have both ANDs and ORs ) List the year, month and total $ amount of payments for each month Order by year and month (Hint Make sure you get payments, not orders Also make sure to get total payments, not individual payments ) What is the average payment $ amount Add a new table called Dates to the classicmodels schema with the following columns Column Primary Key Data Type Optional FullDate Yes Date No MonthName No 3 characters No YearNumber No Integer No QuarterNumber No Integer No Notes No Variable, up to 150 characters Yes Which month(s) in what year(s) from question 8 have the highest payment total amount Your query may not use a LIMIT clause It must work if there are any number of months tied for the highest payment total amount It must not produce any output for a month that is not the highest payment total or tied for the highest payment total w Customers WEGO olare CARD WA phone VARSO) INTEGER ODATETIME DATORE com WARS prowerode VARNARSO INTEGER icelach DOUBLE retumber SMALLINT VARNARSO MORE) INTOOR ROARES) phone) with WC) VORS) WARS de RS VARKARS WINTEGER DURE IN aber INGER MOMO) WORSO) WOWE) VAROR de VMC) INTEGER MORN de VMRS) d VMWAN) WOWO VROURI) der WARS con Text INTEGER deler VROUSO) Die DATETIME at DOUBLE bu DOUBLE SP DOUBLE re) w Customers WEGO olare CARD WA phone VARSO) INTEGER ODATETIME DATORE com WARS prowerode VARNARSO INTEGER icelach DOUBLE retumber SMALLINT VARNARSO MORE) INTOOR ROARES) phone) with WC) VORS) WARS de RS VARKARS WINTEGER DURE IN aber INGER MOMO) WORSO) WOWE) VAROR de VMC) INTEGER MORN de VMRS) d VMWAN) WOWO VROURI) der WARS con Text INTEGER deler VROUSO) Die DATETIME at DOUBLE bu DOUBLE SP DOUBLE re)

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 07, 2024

The classic model's database consists of eight tables: Offices: sales offices Employees: All employees, including sales reps who work with customers. Customers Orders: Orders placed

The classic model's database consists of eight tables:

Offices: sales offices
Employees: All employees, including sales reps who work with customers.
Customers
Orders: Orders placed by customers
Order Details: Line items within an order.
Payments: Payments made by customers against their account
Products: The list of scale model cars
Product Lines: The list of product line classification

The following is an entity/relationship/attribute model for the database. Use this ERA diagram to determine what tables and columns you will use to answer each of the questions.

How many rows are in the Orders table? (Hint: Look up COUNT function)
List all columns in the Orders table in reverse numerical order (from largest to smallest) by order number. Limit the output to the first 5 rows.
What are the earliest and latest order dates in the Orders table? (Hint: Lookup MAX and MIN functions)
List employee first names in alphabetical order along with a count of employees that have each first name. (Hint: you will need GROUP BY and ORDER BY clauses.)
What orders do not have a status of Cancelled? Sort by order number. (Hint: Use the not equals operator.)
Update Steve Pattersons first name to Steven in the employees table. Ensure that your update will not change a person whose first name is Steve but whose last name is not Patterson and will not change a person whose last name is Patterson but whose first name is not Steve. (There will be no results grid for this question.)
List customer name, contact last name, contact first name, phone, city, and credit limit for customers in the cities of Paris or Lyon in France and who have a credit limit between 100000 and 130000. Do not assume that city names are unique. There can be cities with the same name in different countries. (Hint: Remember to use parentheses in the WHERE clause if you have both ANDs and ORs.)
List the year, month and total $ amount of payments for each month. Order by year and month. (Hint: Make sure you get payments, not orders. Also make sure to get total payments, not individual payments.)
What is the average payment $ amount?
Add a new table called Dates to the classicmodels schema with the following columns:

Column	Primary Key	Data Type	Optional
FullDate	Yes	Date	No
MonthName	No	3 characters	No
YearNumber	No	Integer	No
QuarterNumber	No	Integer	No
Notes	No	Variable, up to 150 characters	Yes

Which month(s) in what year(s) from question 8 have the highest payment total amount? Your query may not use a LIMIT clause. It must work if there are any number of months tied for the highest payment total amount. It must not produce any output for a month that is not the highest payment total or tied for the highest payment total.

w Customers WEGO olare CARD WA phone VARSO) INTEGER ODATETIME DATORE com WARS prowerode VARNARSO INTEGER icelach. DOUBLE retumber SMALLINT VARNARSO MORE) INTOOR ROARES) phone) with WC) VORS) WARS de RS VARKARS WINTEGER DURE IN aber INGER MOMO) WORSO) WOWE) VAROR de VMC) INTEGER MORN de VMRS) d. VMWAN) WOWO VROURI) der WARS con Text INTEGER deler VROUSO) Die DATETIME at DOUBLE bu DOUBLE SP DOUBLE re) w Customers WEGO olare CARD WA phone VARSO) INTEGER ODATETIME DATORE com WARS prowerode VARNARSO INTEGER icelach. DOUBLE retumber SMALLINT VARNARSO MORE) INTOOR ROARES) phone) with WC) VORS) WARS de RS VARKARS WINTEGER DURE IN aber INGER MOMO) WORSO) WOWE) VAROR de VMC) INTEGER MORN de VMRS) d. VMWAN) WOWO VROURI) der WARS con Text INTEGER deler VROUSO) Die DATETIME at DOUBLE bu DOUBLE SP DOUBLE re)