Ch. 3 — The Single Entity
Ch. 3 Exercises
Exercise 1
Prepare a list of offices sorted by country, state, city.
Exercise 2
How many employees are there in the company?
Exercise 3
What is the total of payments received?
Exercise 4
List the product lines that contain 'Cars'.
Exercise 5
Report total payments for October 28, 2004.
Exercise 6
Report those payments greater than $100,000.
Exercise 7
List the products in each product line.
Exercise 8
How many products in each product line?
Exercise 9
What is the minimum payment received?
Exercise 10
List all payments greater than twice the average payment.
Exercise 11
What is the average percentage markup of the MSRP on buy price?
Exercise 12
How many distinct products does ClassicModels sell?
Exercise 13
Report the name and city of customers who don't have a sales representative.
Exercise 14
What are the names of executives with VP or Manager in their title?
Exercise 15
List the orders with a total value greater than $5,000, sorted by order value.
ClassicModels Schema
customers
customerNumbercustomerNamecontactLastNamecontactFirstNamephoneaddressLine1addressLine2citystatepostalCodecountrysalesRepEmployeeNumbercreditLimitcustomerLocation 122 rows
employees
employeeNumberlastNamefirstNameextensionemailreportsTojobTitleofficeCode 23 rows
offices
officeCodecityphoneaddressLine1addressLine2statecountrypostalCodeterritoryofficeLocation 7 rows
orderdetails
orderNumberproductCodequantityOrderedpriceEachorderLineNumber 2996 rows
orders
orderNumberorderDaterequiredDateshippedDatestatuscommentscustomerNumber 326 rows
payments
checkNumberpaymentDateamountcustomerNumber 273 rows
productlines
productLinetextDescriptionhtmlDescriptionimage 7 rows
products
productCodeproductNameproductScaleproductVendorproductDescriptionquantityInStockbuyPriceMSRPproductLine 110 rows
Exercise 15
List the orders with a total value greater than $5,000, sorted by order value.
Write your SQL above and click Run Query.