90.2K

Verified Solution

Link Copied!

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.

1. How many rows are in the Orders table? (Hint: Look up COUNT function)

2. 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.

3. What are the earliest and latest order dates in the Orders table? (Hint: Lookup MAX and MIN functions)

4. 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.)

5. What orders do not have a status of Cancelled? Sort by order number. (Hint: Use the not equals operator.)

6. 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.)

7. 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.)

8. 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.)

9. What is the average payment $ amount?

10. 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

11. 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)

Answer & Explanation Solved by verified expert

See Answer