Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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.

  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

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

image text in transcribed

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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Auditing The Master A Tax Collector Report

Authors: B. Cobbey Crisler

1st Edition

1912297108, 978-1912297108

More Books

Students also viewed these Accounting questions

Question

Explain the causes of indiscipline.

Answered: 1 week ago