CS377: Database Design - SQL Joins (3 Points)

Developed by Professor Tralie and Professor Mongan.

Exercise Goals

The goals of this exercise are:

1. To write a SQL Join statement

Modify the MyFirstStatement.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.

MyJoins.sql

-- TODO: list all people that live in the 19426 zip code -- HINT: The answer is [{"columns":["FirstName","LastName","Address"],"values":[["Alex","Smith","123 Main Street"],["Susan","Smith","123 Main Street"],["Samantha","Lee","234 Main Street"],["Alex","Lee","234 Main Street"],["Steph","Lee","234 Main Street"]]}] -- TODO: List the average household salary of the people living in each house (call this column HouseholdSalary), sorted in descending order by salary -- HINT: The answer is [{"columns":["Address","HouseholdSalary"],"values":[["234 Main Street",70000],["12 Third Street",40000],["123 Main Street",25000]]}] -- TODO: List the average total household salary (call this column HouseholdSalary) by zip code, in descending order by salary -- HINT: The answer is [{"columns":["Zip","HouseholdSalary"],"values":[["19426",130000],["19406",40000]]}] -- HINT: You will need a sum of salaries for the inner subquery, and then take the average of those by zip code!

Main.sql

CREATE TABLE PERSON ( ID int, FirstName text, LastName text, Age int, Salary float ); CREATE TABLE HOUSE ( ID int, Address text, City text, State text, ZIP text ); CREATE TABLE HOUSEHOLDMEMBER ( ID int, HouseID int, PersonID int ); INSERT INTO PERSON VALUES (1, "Alex", "Smith", 20, 20000); INSERT INTO PERSON VALUES (2, "Susan", "Smith", 22, 30000); INSERT INTO PERSON VALUES (3, "Samantha", "Lee", 50, 100000); INSERT INTO PERSON VALUES (4, "Alex", "Lee", 49, 110000); INSERT INTO PERSON VALUES (5, "Steph", "Lee", 15, 0); INSERT INTO PERSON VALUES (6, "Stephen", "Johnson", 29, 40000); INSERT INTO HOUSE VALUES (1, "123 Main Street", "Collegeville", "PA", "19426"); INSERT INTO HOUSE VALUES (2, "234 Main Street", "Collegeville", "PA", "19426"); INSERT INTO HOUSE VALUES (3, "12 Third Street", "King of Prussia", "PA", "19406"); INSERT INTO HOUSEHOLDMEMBER VALUES (1, 1, 1); INSERT INTO HOUSEHOLDMEMBER VALUES (2, 1, 2); INSERT INTO HOUSEHOLDMEMBER VALUES (3, 2, 3); INSERT INTO HOUSEHOLDMEMBER VALUES (4, 2, 4); INSERT INTO HOUSEHOLDMEMBER VALUES (5, 2, 5); INSERT INTO HOUSEHOLDMEMBER VALUES (6, 3, 6);

Output