Create the SQL queries as requested for each question. Be certain to follow the formatting illustrated in each question, and use column aliases as shown. Note that for questions that return a large number of rows only a few rows are shown below as an illustration. Also note that you are provided with sample data only. The data you are using is static – it is not being updated by the users as deposits and purchases are made.
Recycling centers from around the region periodically bring collected materials to Williamson Official Recycled Material Sales (WORMS) for bulk sale to companies and individuals that use recycled material for a variety of tasks. A sample of data from WORMS is described by the data model below, and a few rows of data from each table are being made available to you as you design new reports for use by WORMS employees.
Explanation of the data model:
The SITE table contains basic data on the various collection sites that supply the materials to WORMS. The MATERIAL table contains data on the different materials that WORMS collects from the sites for sale to BUYERS. Collection sites bring recyclable materials to WORMS whenever they want – it is normally based on the site’s storage capacity for each type of material, but that is just a generalization and not a rule. Whenever a collection site brings materials to WORMS it is recorded as a DEPOSIT. Whenever a BUYER purchases materials from WORMS that is recorded as a PURCHASE. All material quantities are rounded to the nearest ton and stored in ton units. Therefore, if the quantity on hand for a given material is shown as 100, that means that WORMS has 100 tons of that material on hand. This is true for quantity on hand (mat_qty), deposit quantity (dep_qty), and purchase quantity (purch_qty). All prices are prices per ton of the material. Note on Buyers: WORMS sells to individuals and to companies. If the buyer is an individual (such as a rancher that uses old newspapers for animal bedding), then the buyer’s first and last names are stored in the BUYER table. If the buyer is a company, then the company name is stored in the BUYER table. The database designer could have made subtypes out of these different kinds of buyers, but chose not to. We must work with the data model as it was implemented in the system – we cannot change the structure of the database to incorporate subtypes because all of the existing front-end applications would have to be modified.
Instructions: This is individual effort only – you may not collaborate with any other student (past or present), faculty member, or other person or service (including posting requests for help on website forums). You do not need any SQL techniques or functions beyond the ones that were covered in class to complete this assignment -- the purpose of the assignment is for you to demonstrate your mastery of the material covered in class, not to make you research code beyond what was covered.
Create the tables shown in the ERD above using CTAS commands to copy the structure and data from the WORMS schema. Do NOT make up your own tables or make up your own data – only use the table structures and data that already exist on our server. Note that you must use the exact same table names in your account as are given in the ERD above.
For each of the following questions, write the SELECT query that would return the results requested. Be certain to use the column aliases and formats illustrated in the sample output shown for each problem.
1. Write a query to display the purchase number, material, purchase date, quantity purchased, purchase price, and total for that sale for each purchase. The total for the sale is the purchase price times the quantity sold. The material is the material name and material type, with the material type in parentheses and separated from the name by one space. The material type should be standardized to all lowercase letters. Sort the output by the sale total in descending order, then within matching sale totals sort by purchase date in ascending order.
Result: (52 rows returned)
802 scrap iron (metal) 885 scrap iron (metal) 784 Aluminum (metal)
874 Aluminum (metal)
734 Aluminum (metal)
833 Safety windows (glass) 834 Aluminum (metal)
733 Safety windows (glass) 754 HDPE (plastic)
PURCH_DATE TONS SOLD PRICE PER TON TOTAL SALE
22-APR-17 2010 11-MAY-17 1432 21-APR-17 570 08-MAY-17 550 10-APR-17 490 01-MAY-17 1005 02-MAY-17 480 07-APR-17 957 19-APR-17 680
235 $472,350.00 230 $329,360.00 510 $290,700.00 520 $286,000.00 520 $254,800.00 250 $251,250.00 520 $249,600.00 230 $220,110.00 320 $217,600.00
2. Write a query to display each city and state that contains at least one collection site. Only show each city and state once in your output. Sort the output by city name.
HARPERSVILLE TN HASTBURG TN MICKLIN TN PORTSMITH TN RICKLE TN WILSONVILLE TN WINSTON TN WYNN TN
3. Write a query to display the material ID, material name, and the number of deposits that have been made of each material. Include materials that have never been deposited, but limit the results to only materials that have a price greater than 50. Also, restrict the results to only materials that have a current quantity that is more than 100 tons. Sort the output by number of deposits, then by material name.
258 Fauxglass plexi 422 Steel Cut
289 scrap iron
213 Green glass 211 PET
0 1 3 3 4 4
4. Write a query to display the site name, site city, deposit quantity, and material name of all deposits of paper or metal types of materials. Limit the results to only deposits of a quantity that is greater than the largest purchase made by any buyer from the 901 area code. Sort the output by site city and then by site name. Be certain that the sorting is case insensitive.
Harpersville Recycling Center
Elm Street Center
Elm Street Center
1127 Corrugated cardboard 2350 scrap iron
2526 scrap iron 2450 scrap iron
5. Write a query to display the material ID, name, number of deposits and number of purchases of each material. Include materials that have never been deposited or have never been purchased. Sort the output by the number of purchases in descending order, then by number of deposits in descending order, then by material name in ascending order.
NUM DEPOSITS NUM PURCHASES
289 scrap iron 3 10 280 CLEAR GLASS 4 6 211 PET 4 6 220 Newspaper 6 4 233 Corrugated cardboard 4 4 268 Aluminum 3 4 283 Brown glass 3 4 215 HDPE 3 4 213 Green glass 4 2 234 Safety windows 3 2 299 Wood glassine 2 2 298 Lexiumluminate 1 2 422 Steel Cut 1 2 294 PVC 1 0 343 Blubberous Distillate 0 0 258 Fauxglass plexi 0 0 284Tires 0 0 350 Wax resin baseboard 0 0
6. Write a query to display the material ID, material name, and the average deposit quantity for each material that has been deposited. Include the unit “tons” as part of the average deposit quantity output. Sort the output by the average deposit amount in descending order.
289 scrap iron
233 Corrugated cardboard 280 CLEAR GLASS
213 Green glass
283 Brown glass
234 Safety windows
299 Wood glassine
422 Steel Cut
7. Write a query to display the buyer number, company name, and the total of all purchases by each buyer. The total of purchases for each buyer is the purchase quantity times the purchase price for all purchases made by that buyer. Limit the output to only buyers that are companies (not individuals), and that have made more than one purchase. Sort the output in descending order by the total purchases.
480 Smithson Manufacturing 510 Ropersty Rushings
496 Reclaim Life
502 Sandusky Green Earth 500 The Bin Store
612 Alabama Reclaimation Disbursements
8. Write a query to display the site name and city for all sites that have never deposited a paper type of material. Sort the output by city and then by site name.
Rotter Street Collections
9. Write a query to display the deposit ID, date, quantity, material name, and site name for the single largest deposit ever made by any site.
DEP_ID DEP_DATE DEP_QTY MAT_NAME SITE_NAME
2507 06-MAR-17 2526 scrap iron Green Center
10. The management is curious about how certain changes in the purchase process at WORMS may have affected sales. For all purchases made on or after May 1, 2017, management wants to see how the quantity of each buyer’s purchase compares with the average quantity of that buyer’s purchases from before May 1, 2017. Write a query to display the buyer number, purchase date, purchase quantity, the pre-May average purchase quantity, and the amount the current purchase differs from the pre-May average quantity for purchase made on or after May 1, 2017 by each buyer. The pre- May average is the average of the purchase quantities for all purchases made by that buyer with a purchase date that is before May 1, 2017. Sort the output by the buyer number and then by the purchase date.
BUYER_NUM PURCH_DATE PURCH_QTY BEFORE MAY AVG CHANGE FROM AVERAGE
334 11-MAY-17 480 04-MAY-17 480 08-MAY-17 480 11-MAY-17 496 01-MAY-17 499 04-MAY-17 500 05-MAY-17 500 09-MAY-17 502 05-MAY-17 502 09-MAY-17 506 04-MAY-17 510 03-MAY-17 510 04-MAY-17 510 05-MAY-17 510 08-MAY-17 511 05-MAY-17 511 11-MAY-17 515 02-MAY-17 612 10-MAY-17
17 47 250 741.14 550 741.14
1432 741.14 1005 735.67 620 670 1100 623.5 35 623.5 200 302.5 20 302.5 190 157.67 1100 590.33 700 590.33 620 590.33 350 590.33 460 430 150 430 480 490 32 22
• Does your output match the result output exactly (column aliases, rounding, sorting, etc.)?
• Avoid these common mistakes:
o To_Char to produce a date in native format o Using “LIKE” when all you need is “=”
o Using “LIKE” without a wildcard
o Using a subquery when it is not needed
you put your name in a comment at the top of your file?
you number your answers using comments?
you remove the CTAS commands from your file?
you remove all extra commands (there should be only ten SELECT queries in the file)?
you remember to save your file as .sql in SQL Tools?
you remember to upload your submission to the “Take Home Test 3” dropbox?
you logout of D2L then log back in and go to the dropbox to open your submission and verify
that you uploaded the correct file?
These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction
of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice.
Unethical use is strictly forbidden.
-- 1. Write a query to display the purchase number, material, purchase date, quantity purchased,
-- purchase price, and total for that sale for each purchase. The total for the sale is the purchase price
-- times the quantity sold. The material is the material name and material type, with the material type
-- in parentheses and separated from the name by one space. The material type should be
-- standardized to all lowercase letters. Sort the output by the sale total in descending order, then
-- within matching sale totals sort by purchase date in ascending order.
m.MAT_NAME || ' (' || m.MAT_TYPE || ')' as "MATERIAL",
p.PURCH_DATE, p.PURCH_QTY as "TON SOLD",
p.PURCH_PRICE as "PRICE PER TON",
TO_CHAR(p.PURCH_QTY * p.PURCH_PRICE, '$99,999,999.99') as "TOTAL SALE"
FROM PURCHASE p INNER JOIN MATERIAL m on p.MAT_ID = m.MAT_ID
ORDER BY "TOTAL SALE" DESC, p.PURCH_DATE ASC
-- 2 Write a query to display each city and state that contains at least one collection site. Only show
-- each city and state once in your output. Sort the output by city name.
select DISTINCT CITY, "STATE" from
SELECT Upper(SITE_CITY) as "CITY", SITE_STATE as "STATE"
ORDER BY CITY