CS377: Database Design - SQL Aggregation (3 Points)

Developed by Professor Tralie and Professor Mongan.

Exercise Goals

The goals of this exercise are:

1. To write a SQL statement performing an aggregated query

Modify the Aggregation.sql file to answer the database questions below.

Enter your Ursinus netid before clicking run. This is not your ID number or your email. For example, my netid is wmongan (non Ursinus students can simply enter their name to get this to run, but they won't get an e-mail record or any form of credit).

Netid

Clicking Run below will check your work and, if it passes, will submit your work automatically. You must be connected to the VPN for submission to be successful! You will receive a copy of your code via e-mail, so you'll know that it was submitted if you receive that e-mail! VPN access requires Multi-Factor Authentication, which sends you a code when you log into the network. Instructions on configuring these for your account can be found here.

Aggregation.sql

-- TODO: How many grocery items are there (where type is Grocery)? Call the column NumGrocery. -- HINT: The answer is [{"columns":["NumGrocery"],"values":[[7]]}] -- TODO: How many of each item is there (use only one query!)? Call the column NumItems, and sort in increasing order by the number of items. Don't forget to include the ProductType column so you know which quantity goes with which type of item! -- HINT: The answer is [{"columns":["ProductType","NumItems"],"values":[["Grocery",7],["Produce",15],["Household",20]]}] -- TODO: What is the average cost of produce items? Call the column AvgCostProduce. -- HINT: The answer is [{"columns":["AvgCostProduce"],"values":[[0.4]]}] -- TODO: What is the average cost of each type of item (use only one query!)? Call the column AvgCost and sort in decreasing order by the average cost. Don't forget to include the ProductType column so you know which quantity goes with which type of item! -- HINT: The answer is [{"columns":["ProductType","AvgCost"],"values":[["Grocery",3.25],["Household",1.75],["Produce",0.4]]}]

Main.sql

CREATE TABLE INVENTORY ( ID int, ProductType text, ProductName text, Price float, Quantity int ); INSERT INTO INVENTORY VALUES (1, "Produce", "Banana", 0.30, 5); INSERT INTO INVENTORY VALUES (2, "Household", "Bleach", 2.50, 10); INSERT INTO INVENTORY VALUES (3, "Grocery", "Milk", 2.50, 3); INSERT INTO INVENTORY VALUES (4, "Grocery", "Orange Juice", 4, 4); INSERT INTO INVENTORY VALUES (5, "Produce", "Apple", 0.5, 10); INSERT INTO INVENTORY VALUES (6, "Household", "Legal Pad", 1.00, 10);

Output