Question
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)
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started