Quiz Solutions

Quiz: Normalization

Normalize the following table:

Student Name	Quest ID	Course	Description	Section


Quiz: Relational Algebra

Q1: What kind of operation is performed on the following table?


• Projection: Projection is returning a subset of the columns in a table

Q2: What kind of operation is performed on the following table?


• Selection: Selection is a returning a subset of the rows in a table.

Q3: What kinds of joins can performed on tables?

• Natural Join: Join based on common columns (names and datatypes have to be the same)
• Θ-Join: Join based on a comparison operator between two columns
  • Equijoin: Join based on equality between columns

Quiz: SELECT Clause

Calculate the average of 2 random numbers.

SELECT (RANDOM() + RANDOM())/2.;

	(RANDOM() + RANDOM())/2.
0	3.993871e+17

Q: What is the upper and lower case for the string 'UPPER or lower'?

SELECT UPPER('UPPER or lower'), LOWER('UPPER or lower')

	UPPER('UPPER or lower')	LOWER('UPPER or lower')
0	UPPER OR LOWER	upper or lower

Quiz: FROM Clause

Q: Who are the execs for Stats Club, and what are their positions, and emails?

• Projection of name, position, and email from the table exec

SELECT name, position, email
FROM exec;

	name	position	email
0	Darrell Aucoin	President	darrell.aucoin@gmail.com
1	Judith B. Gibbons	Events	judith@uwaterloo.ca
2	Lamar Roy	Finance	lamar@uwaterloo.ca
3	Gilberto Cross	Events	gilberto@uwaterloo.ca
4	Melba Lane	President	melba@uwaterloo.ca
5	Ruben Lamb	Technology	ruben@uwaterloo.ca
6	Hannah Mason	SeniorAdvisor	hannah@uwaterloo.ca
7	Patrick Robertson	Events	patrick@uwaterloo.ca
8	Dominick Byrd	Events	dominick@uwaterloo.ca

Quiz: DISTINCT

Q: What are the distinct majors of Stats Club members?

SELECT DISTINCT major
FROM member;

	major
0	Stats
1	Pure Math
2	CS
3	Applied Math
4	Act Sci
5	C & O
6	Econ

Q: How many distinct majors of Stats Club members are there?

• DISTINCT can be within aggregate functions

SELECT COUNT(DISTINCT major)
FROM member;

	COUNT(DISTINCT major)
0	7

Quiz: Aliases

Perform a query using a table alias, and use this table alias when referencing the column i.e. table_alias.column_name

• This will be really important for bigger, more complicated queries

SELECT e.name, e.start_time, e.location, e.Description
FROM event AS e;

	name	start_time	location	Description
0	BOT	2015-01-28 19:00:00	C & D	Come and play games with your fellow stats Clu...
1	EOT	None	None	End of Term social at a local Pub. A joint eve...
2	Intro to Hadoop	None	None	Hadoop is a distributed computing system desig...
3	Intro to SQL	2015-02-05 18:00:00	MC-3003	SQL is a relational database language and alon...
4	Prof Talk	None	None	None
5	Intro to SQL 2	None	None	SQL is a relational database language and alon...
6	Prof Talk 2	None	None	None
7	Prof Talk 3	None	None	None

Presentation 2

Quiz: Filtering (WHERE Clause)

Q: What events for Stats Club are introductory talks?

• Introductory talk names start with 'Intro'

SELECT name
FROM event
WHERE name LIKE 'Intro%';

	name
0	Intro to Hadoop
1	Intro to SQL
2	Intro to SQL 2

Q: What Stats Club members have their first name starting with a letter BETWEEN A and G?

SELECT name
FROM member
WHERE name BETWEEN 'A' AND 'G';

	name
0	Darrell Aucoin
1	Fred E. Finch
2	Billy L. Hunter
3	Austin K. Gilliard
4	Elaine S. Ott
5	Daniel J. Moore
6	Frances A. Miller
7	Deborah D. Helfrich
8	Bobbie D. Mathews
9	Arnold J. Fuller
10	Ann W. McLaughlin
11	Dominick Byrd

Quiz: Practice Questions

Q: What events have dates specified?

SELECT name, start_time
FROM event
WHERE start_time IS NOT NULL;

	name	start_time
0	BOT	2015-01-28 19:00:00
1	Intro to SQL	2015-02-05 18:00:00

Q: What events don't have dates specified?

SELECT name
FROM event
WHERE start_time IS NULL;

	name	start_time
0	EOT	None
1	Intro to Hadoop	None
2	Prof Talk	None
3	Intro to SQL 2	None
4	Prof Talk 2	None
5	Prof Talk 3	None

Q: What Stats Club members are in Stats, Act Sci, or CS?

• Recall the IN predicate operator

SELECT name
FROM member
WHERE major in ('Stats', 'Act Sci', 'CS');

	name
0	Darrell Aucoin
1	Ryan T. Luby
2	Billy L. Hunter
3	Stephanie R. Matthews
4	Robert B. Williams
5	Austin K. Gilliard
6	James M. Eddings
7	Elaine S. Ott
8	James A. Foxt
9	Daniel J. Moore
10	Kelly S. Ferguson
11	Vivian R. Donley
12	Frances A. Miller
13	Mina W. Lawrence
14	Phillip C. Mascarenas
15	Jeff M. Wright
16	Deborah D. Helfrich
17	Nancy P. Jackson
18	Bobbie D. Mathews
19	Arnold J. Fuller
20	Ralph L. Waldrop
21	Tameika M. McMaster
22	Melissa R. Anderson
23	Ann W. McLaughlin
24	Judith B. Gibbons
25	Ruben Lamb
26	Patrick Robertson

Q: What are the Stats Club exec positions?

• Avoid duplication of positions

SELECT DISTINCT position
FROM exec_position;

	position
0	Events
1	Finance
2	President
3	Senior Advisor
4	Technology

Q: How many different Stats Club exec positions are there?

• Note that we can use DISTINCT within aggregate functions
• There is a one-to-many relationship between exec and exec_positions

SELECT COUNT(DISTINCT position) AS num_exec_positions
FROM exec_position;

	num_exec_positions
0	5

Quiz: Aggregation (GROUP BY Clause)

Q: What is the attendance for each Stats Club event?

SELECT event, COUNT(*) AS attendance
FROM attendance
GROUP BY event;

	event	attendance
0	BOT	12
1	EOT	18
2	Intro to Hadoop	17
3	Intro to SQL	22
4	Intro to SQL 2	23
5	Prof Talk	20
6	Prof Talk 2	16

Quiz: Joining Tables

Q: What are the email addresses and phone numbers of stats club execs who are in change or organizing at least one event?


SELECT DISTINCT exec.name, exec.email, exec.phone
FROM exec INNER JOIN event ON exec.questid = event.organizer;

	name	email	phone
0	Judith B. Gibbons	judith@uwaterloo.ca	519-555-2343
1	Dominick Byrd	dominick@uwaterloo.ca	519-555-2325
2	Darrell Aucoin	darrell.aucoin@gmail.com	519-555-1424
3	Patrick Robertson	patrick@uwaterloo.ca	519-555-2312

Quiz: Subqueries

Q: Where can a Non-Correlated subquery can be placed?

A: in the FROM, WHERE, and HAVING clauses

Q: Where can a Correlated subquery can be placed?

A: in the SELECT, WHERE, and HAVING clauses

Q: Using a non-correlated subquery, what are the names, locations, and descriptions of events that served pizza?

1. Break the problem into smaller pieces: What are the events that served pizza?
2. Only retrieve values from the table event that event.name matches those values

SELECT event
FROM expenses
WHERE expense = 'pizza';

	event
0	BOT
1	Intro to SQL
2	Prof Talk
3	Prof Talk 2
4	Prof Talk 3

• Second, only retrieve values from the table event that event.name matches those values

SELECT name, location, description
FROM event
WHERE name IN (SELECT event FROM expenses WHERE expense = 'pizza');

	name	location	Description
0	BOT	C & D	Come and play games with your fellow stats Clu...
1	Intro to SQL	MC-3003	SQL is a relational database language and alon...
2	Prof Talk	None	None
3	Prof Talk 2	None	None
4	Prof Talk 3	None	None

Or alternatively, using a join:

SELECT e.name, e.location, e.description
FROM (SELECT event FROM expenses WHERE expense = 'pizza') AS exp INNER JOIN
event AS e
  ON exp.event = e.name;

	e.name	e.location	e.description
0	BOT	C & D	Come and play games with your fellow stats Clu...
1	Intro to SQL	MC-3003	SQL is a relational database language and alon...
2	Prof Talk	None	None
3	Prof Talk 2	None	None
4	Prof Talk 3	None	None

Quiz: WITH Clause (HARD)

Q: MathSoc only provides a maximum cap on social events based on the formula below. What is the max cap for social expenses and is Stats Club over this limit?

• Membership fee for Stats Club is 2 dollars

Social expenses = max {250, (MathSoc Members) * (5 + (Member fee) )} Social expenses = max {250, (MathSoc Members) * 7}

Break the problem into smaller problems:

• What are the total expenses for social events?
• What is the max budget for social events?

Smaller Problems:

• What are the total expenses for social events?

  • What events are social events?

• What is the max cap for social events?

  • What is the result of the formula
  (MathSoc Members) * 7 - What are the number of Stats Club members who are in the Math Faculty

(aka MathSoc members)? - How do we find the max value between this number and 250?

Think about how to solve this for 5 mins

For total social expenses, we need to find the sum of expenses filtering out non-social expenses.

Part 1: What events are social?

SELECT name
FROM event
WHERE type = 'social';

	name
0	BOT
1	EOT

Part 2: What are the total social expenses?

SELECT SUM(price)
FROM expenses
WHERE event IN (SELECT name FROM event WHERE type = 'social');

	SUM(price)
0	219.22

Or alternatively, we can join tables event and expenses and filter out non- social events:

SELECT SUM(exp.price)
FROM event AS e INNER JOIN expenses AS exp
  ON e.name = exp.event
WHERE e.type = 'social';

	SUM(exp.price)
0	219.22

At this point we have one half of the problem solved, the final result would look a bit like:

WITH soc_cost (expense) AS
    (SELECT SUM(exp.price)
     FROM event AS e INNER JOIN expenses AS exp
       ON e.name = exp.event
     WHERE type = 'social'),
soc_budget (budget) AS
     (SELECT statement)
SELECT statement;

Part 3: We need to calculate the formula

(MathSoc Members) * 7 - First, what is the number of MathSoc Members (aka number of members who are in the Math Faculty)?

SELECT COUNT(*)
FROM member
WHERE faculty = 'Math';

	COUNT(*)
0	33

Part 4: What is the result of the formula

(MathSoc Members) * 7

SELECT COUNT(*) * 7
FROM member
WHERE faculty = 'Math';

	COUNT(*) * 7
0	231

Part 5: What is the max of this number and 250?

SELECT MAX(COUNT(*) * 7, 250) 
FROM member 
WHERE faculty = 'Math';

	MAX(COUNT(*) * 7, 250)
0	250

Joining all of this up together:

WITH soc_cost (expense) AS 
    (SELECT SUM(exp.price) 
     FROM event AS e INNER JOIN expenses AS exp 
       ON e.name = exp.event 
     WHERE e.type = 'social'),
soc_budget (budget) AS 
    (SELECT MAX(COUNT(*) * 7, 250) 
     FROM member 
     WHERE faculty = 'Math')
SELECT expense, budget, budget - expense
FROM soc_cost CROSS JOIN soc_budget;

	expense	budget	budget - expense
0	219.22	250	30.78

Quiz: CASE Expressions

Q: Suppose we are interested in the healthiness of our food options at Stats Club events. A score of various foods was given below. What is the average 'healthiness' of Stats Club events?

• The average of the score values for all of the foods at Stats Club events.

Food	Score
donuts	-2
pop	-2
fries	-2
pizza	-1
cookies	-1
coffee	0
water	2
meals	2
veggie platter	3

SELECT AVG(CASE
    WHEN expense IN ('donuts', 'pop', 'fries') THEN -2
    WHEN expense IN ('pizza', 'cookies') THEN -1
    WHEN expense IN ('coffee') THEN 0
    WHEN expense IN ('water', 'meals') THEN 2
    WHEN expense IN ('veggie platter') THEN 3
END) AS avg_score
FROM expenses;

	avg_score
0	-0.818182

Quiz: ORDER BY and LIMIT Clause

Q: What are the top 10 highest priced items in expenses?

SELECT expense, price
FROM expenses
ORDER BY price
LIMIT 10;

	expense	price
0	water	10.23
1	cookies	10.23
2	cookies	10.23
3	cookies	10.23
4	water	10.52
5	water	10.52
6	pop	13.23
7	pop	13.23
8	pop	15.34
9	pop	15.65