Additional SQL queries
Additional SQL queries
ClassicModelsClassicModels is a fictitious company. Use the is a fictitious company. Use the ClassicModels databaseClassicModels database to answer the following to answer the following requests (
requests (SQL to create the databaseSQL to create the database). Y). Your inour instructor has tstructor has the answers the answers to alo all queries.l queries.
Single entity
Single entity
11. . Prepare a liPrepare a list of st of ofoffifices soces sorterted by cod by countryuntry, state, c, state, city.ity. 2.
2. HHow mow many emploany employyees are there in the cees are there in the compompany?any? 3.
3. WhWhat is at is the total the total of of payments receivpayments received?ed? 4.
4. List the product lList the product lines ines that contain that contain 'Cars'.'Cars'. 5.
5. Report total Report total payments for Octopayments for October 28, ber 28, 2004.2004. 6.
6. Report Report those those payments greater than payments greater than $100,000.$100,000. 7.
7. List the products iList the products in n each produceach product lit line.ne. 8.
8. HoHow many products in each w many products in each product liproduct line?ne? 9.
9. What What is is the mithe mininimum mum paypayment receivement received?d?
110. 0. List aList all ll payments greater than payments greater than twice twice the average payment.the average payment.
1111. . WhWhat is at is the averthe average percentage age percentage markup omarkup of the f the MSRP oMSRP on bun buyyPrice?Price? 112. 2. HoHow many distinct w many distinct products does Clproducts does ClassicModels assicModels sell?sell?
113. 3. Report the name aReport the name and cnd city of custoity of customers who domers who don'n't have st have sales representatives?ales representatives?
114. 4. WhWhat are the names of at are the names of exexecutives with VP or Manecutives with VP or Manager in ager in their title? Use the CONtheir title? Use the CONCATCAT function to combine the employee's first name and last name into a single field for
function to combine the employee's first name and last name into a single field for reporting.
reporting.
115. 5. WhWhicich h orders haorders havve a e a vvalualue greater than e greater than $5,000?$5,000?
One to
One to m
many relationship
any relationship
11. . Report the aReport the accoccount representative for eacunt representative for each ch customer.ustomer. 2. Repor
2. Report total payments for A t total payments for A telier graphique.telier graphique. 3.
3. Report Report the tothe total tal payments by datepayments by date 4
4. . Report Report the the proproducts that havducts that have not been solde not been sold.. 5
5. . List List the the amounamount paid by t paid by each custoeach customer.mer. 6
6. . HoHow w many omany ordrders have been placed by Herkku Gifts?ers have been placed by Herkku Gifts? 7.
7. Who Who are the employeare the employees in es in BostonBoston?? 8
8. . Report thoReport those payments greater tse payments greater than han $100,000. Sort the repo$100,000. Sort the report so the rt so the cucu stomer who madestomer who made the high
the highest payest pay ment appears first.ment appears first. 9.
9. List the vList the value oalue of 'f 'On HOn Holold' d' orderorders.s.
110. 0. Report the number of Report the number of orders 'On Horders 'On Holold' d' for each for each customercustomer..
Many to many relationship
Many to many relationship
11. . List pList producroducts sots sold ld by order date.by order date. 2.
2. List alList all l the orders fothe orders for the 1940 Ford Picr the 1940 Ford Pickup Trkup Truck.uck. 3.
3. List the names of List the names of customercustomers and s and the corresthe correspondponding ing order numberorder numbers where a particulars where a particular
H
Hoomme e EExxeerrcciissees s SSkkiilll l bbuuiillddeerrs s SSuuppppoorrtt
Instructor
order from that customer has a value greater than $25,000? 4. Are there any products that appear on all orders?
5. List those orders containing items sold at less than the MSRP.
6. Reports those products that have been sold with a markup of 100% or more (i.e., the priceEach is at least twice the buy Price)
7. List the products ordered on a Monday.
8. What is the quantity on hand for products listed on 'On Hold' orders?
Regular expressions
1. Find products containing the name 'Ford'. 2. List products ending in 'ship'.
3. Report the number of customers in Denmark, Norway , and Sweden.
4. What are the products with a product code in the range S700_1000 to S700_1499? 5. Which customers have a digit in their name?
6. List the names of employees called Dianne or Diane.
7. List the products containing ship or boat in their product name. 8. List the products with a product code beginning with S700. 9. List the names of employees called Larry or Barry .
10. List the names of employees with non-alphabetic characters in their names. 11. List the vendors whose name ends in Diecast
General queries
1. Who is at the top of the organization (i.e., reports to no one). 2. Who reports to William Patterson?
3. List all the products purchased by Herkku Gifts.
4. Compute the commission for each sales representative, assuming the commission is 5% of the cost of an order. Sort by employee last name and first name.
5. What is the difference in days between the most recent and oldest order date in the Orders file?
6. Compute the average time between order date and ship date for each customer ordered by the largest difference.
7. What is the value of orders shipped in August 2004? (Hint).
8. Compute the total value ordered, total amount paid, and their difference for each customer for orders placed in 2004 and payments received in 2004 (Hint; Create v iews for the total paid and total ordered).
9. List the employees who report to those employees who report to Diane Murphy. Use the CONCAT function to combine the employee's first name and last name into a single field for reporting.
10. What is the percentage value of each product in inventory sorted by the highest percentage first (Hint: Create a view first).
11. Write a function to convert miles per gallon to liters per 1 00 kilometers.
12. Write a procedure to increase the price of a specified product category by a given percentage. You will need to create a product table with appropriate data to test your
procedure. Alternatively, load the ClassicModels database on your personal machine so you have complete access. You have to change the DELIMITER prior to creating the
procedure.
13. Write a procedure to report the amount ordered in a specific month and year for customers containing a specified character string in their name.
specified percentage.
15. What is the value of orders shipped in August 2004? ( Hint).
16. What is the ratio the value of payments made to orders received for each month of 2004. (i.e., divide the value of payments made by the orders received)?
17. Who are the customers who have placed more than three orders each exceeding $10,000 in value?
Correlated subqueries
1. Who reports to Mary Patterson?
2. Which payments in any month and year are more than twice the average for that month and year (i.e. compare all payments in Oct 2004 with the average payment for Oct 2004)? Order the results by the date of the payment. You will need to use the date functions. 3. Report for each product, the percentage v alue of its stock on hand as a percentage of the
stock on hand for product line to which it belongs. Order the report by product line and percentage value within product line descending. Show percentages with two decimal places.
4. For orders containing more than two products, report those products that constitute more than 50% of the value of the order.
Spatial data
The Offices and Customers tables contain the latitude and longitude of each office and customer in officeLocation and customerLocation, respectively, in POINT format. Conventionally, latitude and longitude and reported as a pair of points, with latitude first.
1. Which customers are in the Southern Hemisphere?
2. Which US customers are south west of the New York office?
3. Which customers are closest to the Tokyo office (i.e., closer to Tokyo than any other office)?
4. Which French customer is furthest from the Paris office? 5. Who is the northernmost customer?
6. What is the distance between the Paris and Boston offices?
To be precise for long distances, the distance in kilometers, as the crow flies, between two points when you have latitude and longitude, is
(ACOS(SIN(lat1*PI()/1 80)*SIN(lat2*PI()/1 80)+COS(lat1*PI()/180)*COS(lat2*PI()/180)* COS((lon1-lon2)*PI()/1 80))*180/PI())*60*1.8532
Data visualization
For each problem, use R to run an SQL query and then visualize the results. T he first step in all cases is to load the MySQL library and access the database.
1. Visualize in blue the number of items for each product scale.
2. Prepare a line plot with appropriate labels for total payments for each month in 2004.
3. Create a histogram with appropriate labels for the value of orders received from the Nordic countries (Denmark,Finland, Norway,Sweden).
4. Create a heatmap for product lines and Norwegian cities.
5. Create a parallel coordinates plot for product scale, quantity in stock, and MSRP in the Products table.
Workbench file for the following image.
This page is part of the promotional and support material for Data Management (fifth edition) by Richard T. Watson
