1 of 42

Exam Prep 1

SQL

2 of 42

Announcements

assignment

Project 1 (SQL)

Assignment Deadline

Thursday, 9/10

at 11:59 PM

lightbulb

Vitamin 2

Assignment Deadline

Monday, 9/14

at 11:59 PM

3 of 42

SQL

4 of 42

Single-Table SQL

SELECT [DISTINCT] <column list>�FROM <table1>�[WHERE <predicate>]�[GROUP BY <column list>]�[HAVING <predicate>]�[ORDER BY <column list> [DESC/ASC]]�[LIMIT <amount>];

5 of 42

Single-Table SQL: Logical Processing Order

FROM <table1> - which table are we drawing data from
[WHERE <predicate>] - only keep rows where <predicate> is satisfied
[GROUP BY <column list>] - group together rows by value of columns in <column list>
[HAVING <predicate>] - only keep groups having <predicate> satisfied
SELECT <column list> - select columns in <column list> to keep

[DISTINCT] - keep only distinct rows (filter out duplicates)

[ORDER BY <column list> [DESC/ASC]] - order the output by value of the columns in <column list>, ASCending by default
[LIMIT <amount>] - limit the output to just the first <amount> rows

But humans don’t think the same way a SQL interpreter would. Instead, I present to you the logical processing order for SQL.

When you’re given a query problem, the first thing you should ask it, “where” is this data stored? From specifies which table we are drawing from.
Secondly, what rows do we care about? Where will take in a boolean predicate and return only the rows that meet the predicate condition.
Next, are we grouping any rows? Group by will take in a list of columns and group rows by the value of those columns.
Fourth, what groups do we care about? Having will take in a boolean predicate and return only the groups that meet the predicate condition.
Fifth, we what columns do we care about? Select will take in a column list and return only the columns in that list. We can also use `distinct` to filter out duplicates.
Next, we take a look at the resulting table and ask ourselves, is there any order we want our results in? Order by takes in a column list and orders the output by the value in those columns. You can specify whether to order in ascending mode or descending mode with ASC or DESC
Finally, how many rows do we care about? Limit takes in a number and outputs the number of rows off the top.

If there’s any takeaway you all can have from this topic on single-table SQL queries, it’s encapsulated in this slide.

Anytime you’re given a SQL problem, this is my recommended way of approaching it!
If you’re ever unsure of whether a query is legal or not, write down the states of the table changing in this order.

6 of 42

Join Variants

The different types of joins determine what we do with rows that don’t ever match the “join condition”

SELECT * FROM

T1 INNER JOIN T2

ON T1.a = T2.a;�

Fill entries with no matching data with NULL
If no join condition, return cartesian product (i.e. FROM t1, t2)

Join Condition

7 of 42

String Comparison

LIKE: following expression follows SQL specified format

_: Any single character
%: Zero, one, or more characters
Looks for a perfect string match

Examples:

LIKE ‘z%’ starts with z
LIKE ‘z_’ exactly 2 letters, 1st is z
LIKE ‘_z%’ 2nd letter is a z

~ : following expression follows regex format

. : Any single character
* : Zero, one, or more of the character preceding the symbol
^: Match at start of string (If used outside [])
Looks for any pattern in the string that fits

Examples:

~ ‘z.*’ contains z
~ ‘^z.*’ starts with z

Note: ~ cannot be used in SQLite (which Project 1 will be using)

8 of 42

More SQL Things

Sets - a collection with no duplicates

UNION - all items in either set, INTERSECTION - all items in both sets.
In SQL, these don’t have to be used on sets (there can be duplicates in the inputs). No duplicates in the final set!
Must add ALL to include duplicates - UNION ALL, INTERSECTION ALL
Used between two queries (SELECT … FROM … UNION SELECT … FROM …)

Correlated Query

Subquery depends on values from the outer query, subquery is recalculated for every row of the outer query’s table
SELECT S.sname FROM Sailors S WHERE EXISTS � (SELECT * FROM Reserves R WHERE R.bid=102 AND� S.sid=R.sid)

If you attended the first week of discussion or went to lectures during the first week, most of that should’ve been review. Here are a couple points that may have not been emphasized now but will sometimes occur in exams

First, we have sets/

A union is all items in either set, and intersection matches items in both sets
First point, if you want to include duplicates in your answer, you must include the ALL keyword (like union all or intersect all)
The UNION or INTERSECTION keywords are typically used between two different queries. For example, we may union two queries with select blank from table 1 union select blank from table 2

Next we have correlated subqueries!

A subquery depends on values from the outer query
An example is if we select name from sailors where the row exists in the following subquery

Select all from reserves where the boat ID is 102 and the sailor ID in Sailors matches the sailor ID in Reserves.
In this case, we are accessing the sailors table from the outer query to compute our subquery!

9 of 42

More SQL Things

Aggregations - COUNT, SUM, AVG, etc.

Using an aggregate in WHERE is not allowed! WHERE count(*) > 500 is an invalid query!
NULL column values are ignored by aggregate functions

Don’t use HAVING without GROUP BY - Just use WHERE instead!
DISTINCT removes all duplicate rows

10 of 42

Worksheet: Q1

11 of 42

Question 1a

Return the bid and genre of each book that has ever been checked out. Remove any duplicate rows with the same bid and genre.

12 of 42

Question 1a

Return the bid and genre of each book that has ever been checked out. Remove any duplicate rows with the same bid and genre.

SELECT DISTINCT b.bid, b.genre

FROM Books b, Checkouts c

WHERE b.bid = c.book

Need to join Books and Checkouts to get genre and the fact that a book is checked out
INNER JOIN would work as well!

13 of 42

Question 1b

Find all of the fantasy book titles that have been checked out and the date when they were checked out. Even if a book hasn’t been checked out, we still want to output the title (i.e. the row should look like (title, NULL)).

14 of 42

Question 1b

Find all of the fantasy book titles that have been checked out and the date when they were checked out. Even if a book hasn’t been checked out, we still want to output the title (i.e. the row should look like (title, NULL)).

SELECT title, day

FROM Books b LEFT OUTER JOIN Checkouts c � ON c.book = b.bid

WHERE b.genre = Fantasy

Wanting NULL in the output is a good sign of a LEFT OUTER or RIGHT OUTER join
LEFT JOIN to preserve all books
Use WHERE to filter genre of the joined table

15 of 42

Question 1c

Select the name of the book that has been checked out the most times and the corresponding checked out count. You can assume that each book was checked out a unique number of times, and that the titles of the books are all unique. (Note: bid is unique for each “instance” of a book)

16 of 42

Question 1c

Select the name of the book that has been checked out the most times and the corresponding checked out count. You can assume that each book was checked out a unique number of times, and that the titles of the books are all unique. (Note: bid is unique for each “instance” of a book)

SELECT title, count(*) as cnt

FROM Books b, Checkouts c

WHERE b.bid = c.book

GROUP BY b.title

ORDER BY cnt DESC

LIMIT 1

Want title from Books table, # of checked out from aggregating on Checkouts. Group by title, not bid!

17 of 42

Question 1d

Select the name of all of the pairs of libraries that have books with matching titles. Include the name of both libraries and the title of the book. There should be no duplicate rows, and no two rows that are the same except the libraries are in opposite order. To ensure this, the first library name should be alphabetically less than the second library name. There may be zero, one, or more than one correct answer.

18 of 42

Question 1d

Select the name of all of the pairs of libraries that have books with matching titles. Include the name of both libraries and the title of the book. There should be no duplicate rows, and no two rows that are the same except the libraries are in opposite order. To ensure this, the first library name should be alphabetically less than the second library name. There may be zero, one, or more than one correct answer.

Incorrect: does not return books with matching titles. Also incorrectly uses lname to compare to Books.library

19 of 42

Question 1d

Select the name of all of the pairs of libraries that have books with matching titles. Include the name of both libraries and the title of the book. There should be no duplicate rows, and no two rows that are the same except the libraries are in opposite order. To ensure this, the first library name should be alphabetically less than the second library name. There may be zero, one, or more than one correct answer.

20 of 42

Question 1d

Select the name of all of the pairs of libraries that have books with matching titles. Include the name of both libraries and the title of the book. There should be no duplicate rows, and no two rows that are the same except the libraries are in opposite order. To ensure this, the first library name should be alphabetically less than the second library name. There may be zero, one, or more than one correct answer.

Correct: Filters rows in cross join where the alphabetic order is respected and book titles are the same

21 of 42

Question 1d

Select the name of all of the pairs of libraries that have books with matching titles. Include the name of both libraries and the title of the book. There should be no duplicate rows, and no two rows that are the same except the libraries are in opposite order. To ensure this, the first library name should be alphabetically less than the second library name. There may be zero, one, or more than one correct answer.

22 of 42

Question 1d

Select the name of all of the pairs of libraries that have books with matching titles. Include the name of both libraries and the title of the book. There should be no duplicate rows, and no two rows that are the same except the libraries are in opposite order. To ensure this, the first library name should be alphabetically less than the second library name. There may be zero, one, or more than one correct answer.

Correct: Finds book-library pairs as inner subqueries. Outer query does the cross join of these pairs such that the titles are the same and l1 is alphabetically “less than” l2

23 of 42

Worksheet: Q2

24 of 42

Question 2a

Select all of the following queries which return the rid of the rider with the most bikes. Assume all riders have a unique number of bikes.

25 of 42

Question 2a

Correct: owner references rid so we don’t need to join on another table. Just aggregate!

Select all of the following queries which return the rid of the rider with the most bikes. Assume all riders have a unique number of bikes.

26 of 42

Question 2a

Select all of the following queries which return the rid of the rider with the most bikes. Assume all riders have a unique number of bikes.

27 of 42

Question 2a

Correct: returns “all” owners that have at least as many bikes as all owners. Because all riders have a unique # of bikes, this returns the 1 rider with the most bikes

Select all of the following queries which return the rid of the rider with the most bikes. Assume all riders have a unique number of bikes.

28 of 42

Question 2a

Select all of the following queries which return the rid of the rider with the most bikes. Assume all riders have a unique number of bikes.

29 of 42

Question 2a

Select all of the following queries which return the rid of the rider with the most bikes. Assume all riders have a unique number of bikes.

Incorrect: using MAX on the table bikes is nonsensical (what even would be aggregated?)

30 of 42

Question 2b

Select the bid of all bikes that have never been ridden.

31 of 42

Question 2b

Select the bid of all bikes that have never been ridden.

Incorrect: The subquery returns the rows in bikes with the same bid as the current row. NOT EXISTS always evaluates to false, so no rows are returned by the query.

32 of 42

Question 2b

Select the bid of all bikes that have never been ridden.

33 of 42

Question 2b

Select the bid of all bikes that have never been ridden.

Correct: Finds all bikes in the Bikes table for which there are no entries in the Rides table

34 of 42

Question 2b

Select the bid of all bikes that have never been ridden.

35 of 42

Question 2b

Select the bid of all bikes that have never been ridden.

Correct: Finds all bikes in the Bikes table that do not exist in the join of Rides and Bikes

36 of 42

Question 2c