1 of 20

CS W186 Exam Prep Section 1:

SQL

2 of 20

SQL

3 of 20

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>];

4 of 20

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.

5 of 20

Join Variants

SELECT * FROM

T1 INNER JOIN T2

ON T1.a = T2.a;

The different types of joins determine what we do with rows that don’t ever match the “join condition”
Default is INNER JOIN === FROM t1, t2 WHERE t1.id = t2.id
Fill entries with no matching data with NULL
If no join condition, return cartesian product (i.e. FROM t1, t2)

Join Condition

There are four types of joins that you’ll most frequently see in this class.

Inner join, left join, right join, and full join
In any join query, you will have two components

First, specify the type of join
Second, specify the join condition, which specifies which columns from each table we want to join on

These venn diagrams illustrate how the joins work from an abstract perspective

Some additional points

By default, SQL uses inner join, which is equivalent to joining two tables and matching up their Ids.
Null is used to fill in the entries with no matching data points.

Inner join is the only join that wouldn’t have any null values by definition, since the results must match on both sides.

If there is no join condition, SQL performs a cartesian product

Cartesian product is all possible pairs of rows, where each pair has one row from the first set and one row from the second set

6 of 20

String Comparison

LIKE: following expression follows SQL specified format

_: Any single character
%: Zero, one, or more characters

Examples:

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

Note: ~ cannot be used in SQLite

7 of 20

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!

8 of 20

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

9 of 20

Worksheet

10 of 20

Question 1

11 of 20

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.

Need to join Books and Checkouts to get genre and the fact that a book is checked out

INNER JOIN would work as well!

12 of 20

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)).

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

13 of 20

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

Want title from Books table, # of checked out from aggregating on Checkouts. Group by title, not bid --- bid is unique for each “instance” of a book!

14 of 20

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

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

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

15 of 20

Question 2

16 of 20

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.

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

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

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

17 of 20

Question 2b

Select the bid of all Bikes that have never been ridden

Incorrect: Finds all bikes without an owner --- not what we want!

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

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

18 of 20

Question 2c

Select the name of the rider and the city_name of the src and dest locations of all their journeys for all rides. Even if a rider has not ridden a bike, we still want to output their name.

None of these are correct!

The INNER JOINs and WHERE clauses will filter out rows with NULL values produced by the OUTER JOIN.

19 of 20

Question 2c

Select the name of the rider and the city_name of the src and dest locations of all their journeys for all rides. Even if a rider has not ridden a bike, we still want to output their name.

How would we construct a correct answer?

Need to do a JOIN on Locations to get the city_name, but need to do this before the join onto riders

20 of 20

Attendance Link

https://cs186berkeley.net/attendance