Discussion 1

SQL

Agenda

1. Logistics
2. SQL

Logistics

Course website: http://www.cs186berkeley.net/

​

Projects are involved coding assignments

• Project 0 (setup) due Friday, 9/4 at 11:59PM
• Project 1 (SQL) due Thursday, 9/10 at 11:59PM

​

Vitamins are required weekly quizzes that keep you up to date with lectures

• Pre-Semester Quiz due Friday, 9/4 at 11:59PM
• Vitamin 1 due Monday 9/7 at 11:59PM

​

Midterm 1 will be on Oct 1st, 8-10PM

Midterm 2 will be on Nov 5th, 8-10PM

Logistics - Access Check

EdStem: https://edstem.org/us/join/xDrU5s

Gradescope: https://www.gradescope.com/courses/1225642

GitHub/Classroom50: See Project 0 for setup

• Post on Ed / come to OH if you have any issues!

Public Course Drive: https://drive.google.com/drive/folders/1O3AL0ld4Riq8H0f3-eBHUzq4SCNq6bKp

Questions?

Motivation

This week: How do I use SQL to fetch data? (covered much more extensively in data science classes)

​

The rest of the semester: How does SQL fetch data for me?

• Data storage
• Data manipulation
• Query optimization
• Additional features (persistence/concurrency/etc)

SQL

SQL for single tables queries

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

• SQL is declarative - you describe what you want in the output, and the DBMS decides how it’s fetched.

Logical Processing Order

1. FROM <table1> - which table are we drawing data from
2. [WHERE <predicate>] - only keep rows where <predicate> is satisfied
3. [GROUP BY <column list>] - group together rows by value of columns in <column list>
4. [HAVING <predicate>] - only keep groups having <predicate> satisfied
5. SELECT <column list> - select columns in <column list> to keep
1. [DISTINCT] - keep only distinct rows (filter out duplicates)
6. [ORDER BY <column list> [DESC/ASC]] - order the output by value of the columns in <column list>, ASCending by default
7. [LIMIT <amount>] - limit the output to just the first <amount> rows

​

• DBMS may execute a query in an equivalent but different order
• For multi-table queries: perform joins with FROM

Logical Processing Order

​

test_table

�FROM test_table�

​

Take data FROM test_table.

Logical Processing Order

​

test_table

�FROM test_table�WHERE a > 2�

​

Take data FROM test_table and keep rows WHERE a > 2.

Logical Processing Order

​

�FROM test_table�WHERE a > 2�GROUP BY b�

We GROUP BY b, but there may be multiple values of a per group, so we can’t use a directly anymore. We can, however, use it with aggregate functions (MIN, MAX, SUM, AVERAGE, COUNT).

Using aggregate functions without a GROUP BY clause = everything in one group.

​

Logical Processing Order

​

�FROM test_table�WHERE a > 2�GROUP BY b

HAVING COUNT(*) >= 2�

Throw away groups that have fewer than 2 rows in them.

​

Note: COUNT(*) includes NULL values, and COUNT(column) does not include null values.

Logical Processing Order

​

SELECT b AS c�FROM test_table�WHERE a > 2�GROUP BY b

HAVING COUNT(*) >= 2�

We use an alias here: b AS c selects b, but then calls it c afterwards. We can use this alias in any step after this one (so not in SELECT, WHERE, GROUP BY, HAVING).

Logical Processing Order

​

SELECT DISTINCT b AS c�FROM test_table�WHERE a > 2�GROUP BY b

HAVING COUNT(*) >= 2�

There are no duplicate rows here so DISTINCT isn’t necessary, but if there were any, DISTINCT would remove them. Duplicates are removed by exact match on the entire row.

Logical Processing Order

​

SELECT DISTINCT b AS c�FROM test_table�WHERE a > 2�GROUP BY b

HAVING COUNT(*) >= 2�ORDER BY c DESC

Sort the output by the columns (just c here) (numerically for integers, lexicographically for strings) in either ASCending (low to high) or DESCending (high to low) order. Default is ASC.

​

Note: order of output is not guaranteed unless you have an ORDER BY clause.

Logical Processing Order

​

SELECT DISTINCT b AS c�FROM test_table�WHERE a > 2�GROUP BY b

HAVING COUNT(*) >= 2�ORDER BY c DESC

LIMIT 1

Return just the first row.

A Note On GROUP BY

• Consider the following Classes table and
• Applying the query: SELECT <TBD> FROM Classes GROUP BY Dept;
• This is what the table looks like once we perform the GROUP BY

​

A Note On GROUP BY

• The role of the SELECT statement is to squash each group into a single row
• In general, SELECT clauses are applied to each row, but when grouping is involved they are applied to each group

SELECT Dept, Course �FROM Classes GROUP BY Dept;

Are the following valid?

NO

SELECT Dept, SUM(Capacity) FROM Classes GROUP BY Dept;

YES

The Group By Rules

• Columns can only be selected if they are a part of the group by clause or the selected column is an aggregate (i.e. MAX, MIN, AVG, etc.)

Given the table Students(sid, age, gpa, name, major), which of the following are valid?

SELECT age�FROM Students

GROUP BY major;

SELECT age�FROM Students

GROUP BY major, age;

SELECT MAX(age)�FROM Students

GROUP BY major;

SELECT age, MIN(gpa)�FROM Students

GROUP BY name;

NO

YES

YES

NO

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)

Practice: Single-Table Queries

Question 1a

Find the names of the 5 songs that spent the least weeks in the top 40, ordered from least to most. Break ties by song name in alphabetical order.

​

Tables

Songs �(song_id, song_name, album_id, weeks_in_top_40)

Artists �(artist_id, artist_name, first_yr_active)

Albums �(album_id, album_name, artist_id, yr_released, genre)

Question 1a

Find the names of the 5 songs that spent the least weeks in the top 40, ordered from least to most. Break ties by song name in alphabetical order.

Tables

Songs �(song_id, song_name, album_id, weeks_in_top_40)

Artists �(artist_id, artist_name, first_yr_active)

Albums �(album_id, album_name, artist_id, yr_released, genre)

SELECT song_name

FROM Songs

ORDER BY weeks_in_top_40 ASC, song_name ASC

LIMIT 5

​

Question 1b

Find the name and first year active of every artist whose name starts with the letter ‘B’.

​

Tables

Songs �(song_id, song_name, album_id, weeks_in_top_40)

Artists �(artist_id, artist_name, first_yr_active)

Albums �(album_id, album_name, artist_id, yr_released, genre)

Question 1b

Find the name and first year active of every artist whose name starts with the letter ‘B’.

​

Tables

Songs �(song_id, song_name, album_id, weeks_in_top_40)

Artists �(artist_id, artist_name, first_yr_active)

Albums �(album_id, album_name, artist_id, yr_released, genre)

SELECT artist_name, first_yr_active�FROM artists�WHERE artist_name LIKE 'B%'

​

​

Question 1b

Find the name and first year active of every artist whose name starts with the letter ‘B’.

​

Tables

Songs �(song_id, song_name, album_id, weeks_in_top_40)

Artists �(artist_id, artist_name, first_yr_active)

Albums �(album_id, album_name, artist_id, yr_released, genre)

SELECT artist_name, first_yr_active�FROM artists�WHERE artist_name ~ '^B.*'

​

Question 1c

Find the total number of albums released per genre.

Tables

Songs �(song_id, song_name, album_id, weeks_in_top_40)

Artists �(artist_id, artist_name, first_yr_active)

Albums �(album_id, album_name, artist_id, yr_released, genre)

Question 1c

Find the total number of albums released per genre.

Tables

Songs �(song_id, song_name, album_id, weeks_in_top_40)

Artists �(artist_id, artist_name, first_yr_active)

Albums �(album_id, album_name, artist_id, yr_released, genre)

SELECT genre, COUNT(album_id)

FROM Albums

GROUP BY genre;

​

Question 1d

Find the total number of albums released per genre. Don’t include genres with a count less than 10.

Tables

Songs �(song_id, song_name, album_id, weeks_in_top_40)

Artists �(artist_id, artist_name, first_yr_active)

Albums �(album_id, album_name, artist_id, yr_released, genre)

Question 1d

Find the total number of albums released per genre. Don’t include genres with a count less than 10.

Tables

Songs �(song_id, song_name, album_id, weeks_in_top_40)

Artists �(artist_id, artist_name, first_yr_active)

Albums �(album_id, album_name, artist_id, yr_released, genre)

SELECT genre, COUNT(*)

FROM Albums

GROUP BY genre

HAVING COUNT(*) >= 10;

​

Question 1e

Find the genre for which the most albums were released in the year 2000. Assume there are no ties.

​

Tables

Songs �(song_id, song_name, album_id, weeks_in_top_40)

Artists �(artist_id, artist_name, first_yr_active)

Albums �(album_id, album_name, artist_id, yr_released, genre)

Question 1e

Find the genre for which the most albums were released in the year 2000. Assume there are no ties.

​

Tables

Songs �(song_id, song_name, album_id, weeks_in_top_40)

Artists �(artist_id, artist_name, first_yr_active)

Albums �(album_id, album_name, artist_id, yr_released, genre)

SELECT genre

FROM Albums

WHERE yr_released = 2000

GROUP BY genre

ORDER BY COUNT(*) DESC

LIMIT 1;

​

SQL Joins

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;

​

Join Condition

Inner Join, Example

Ages

Standing

SELECT ages.Name, ages.Age, standing.Year

FROM ages INNER JOIN standing

ON ages.Name = standing.Name;

​

Inner Join, Example

Ages

Standing

SELECT ages.Name, ages.Age, standing.Year

FROM ages INNER JOIN standing

ON ages.Name = standing.Name;

​

Result

Inner Join, Example

Ages

Standing

SELECT ages.Name, ages.Age, standing.Year

FROM ages INNER JOIN standing

ON ages.Name = standing.Name;

​

Result

Inner Join, Example

Ages

Standing

SELECT ages.Name, ages.Age, standing.Year

FROM ages INNER JOIN standing

ON ages.Name = standing.Name;

​

Result

Inner Join, Example

Ages

Standing

SELECT ages.Name, ages.Age, standing.Year

FROM ages INNER JOIN standing

ON ages.Name = standing.Name;

​

Result

Inner Join, Example

Ages

Standing

SELECT ages.Name, ages.Age, standing.Year

FROM ages INNER JOIN standing

ON ages.Name = standing.Name;

​

Result

Inner Join, Example

Ages

Standing

SELECT ages.Name, ages.Age, standing.Year

FROM ages INNER JOIN standing

ON ages.Name = standing.Name;

​

Result

Inner Join, Example

Ages

Standing

SELECT ages.Name, ages.Age, standing.Year

FROM ages INNER JOIN standing

ON ages.Name = standing.Name;

​

Result

Inner Join, Example

Ages

Standing

SELECT ages.Name, ages.Age, standing.Year

FROM ages INNER JOIN standing

ON ages.Name = standing.Name;

​

Result

Inner Join, Example

Ages

Standing

SELECT ages.Name, ages.Age, standing.Year

FROM ages INNER JOIN standing

ON ages.Name = standing.Name;

​

Result

Inner Join, Example

Ages

Standing

SELECT ages.Name, ages.Age, standing.Year

FROM ages INNER JOIN standing

ON ages.Name = standing.Name;

​

Result

Inner Join, Example

Ages

Standing

SELECT ages.Name, ages.Age, standing.Year

FROM ages INNER JOIN standing

ON ages.Name = standing.Name;

​

Result

Inner Join, Example

Ages

Standing

SELECT ages.Name, ages.Age, standing.Year

FROM ages INNER JOIN standing

ON ages.Name = standing.Name;

​

Result

Inner Join, Example

Ages

Standing

SELECT ages.Name, ages.Age, standing.Year

FROM ages INNER JOIN standing

ON ages.Name = standing.Name;

​

Result

Left Join, Example

Ages

Standing

SELECT ages.Name, ages.Age, standing.Year

FROM ages LEFT JOIN standing

ON ages.Name = standing.Name;

​

Left Join, Example

Ages

Standing

SELECT ages.Name, ages.Age, standing.Year

FROM ages LEFT JOIN standing

ON ages.Name = standing.Name;

​

Result

Right Join, Example

Ages

Standing

SELECT ages.Name, ages.Age, standing.Year

FROM ages RIGHT JOIN standing

ON ages.Name = standing.Name;

​

Right Join, Example

SELECT ages.Name, ages.Age, standing.Year

FROM ages RIGHT JOIN standing

ON ages.Name = standing.Name;

​

Intermediate Table (Before SELECT)

Right Join, Example

Ages

Standing

SELECT ages.Name, ages.Age, standing.Year

FROM ages RIGHT JOIN standing

ON ages.Name = standing.Name;

​

Result

Full Outer Join, Example

Ages

Standing

SELECT ages.Name, ages.Age, standing.Year

FROM ages FULL JOIN standing

ON ages.Name = standing.Name;

​

Full Outer Join, Example

Ages

Standing

SELECT ages.Name, ages.Age, standing.Year

FROM ages FULL JOIN standing

ON ages.Name = standing.Name;

​

Result

Practice: Multi-Table Joins

Question 2a

Find the names of all artists who released a ‘country’ genre album in 2020.

Tables

Songs �(song_id, song_name, album_id, weeks_in_top_40)

Artists �(artist_id, artist_name, first_yr_active)

Albums �(album_id, album_name, artist_id, yr_released, genre)

Question 2a

Find the names of all artists who released a ‘country’ genre album in 2020.

Tables

Songs �(song_id, song_name, album_id, weeks_in_top_40)

Artists �(artist_id, artist_name, first_yr_active)

Albums �(album_id, album_name, artist_id, yr_released, genre)

SELECT artist_name

FROM Artists AS A

INNER JOIN Albums AS B

ON A.artist_id = B.artist_id

WHERE genre = ‘country’ AND yr_released = 2020

GROUP BY A.artist_id, artist_name;

​

​

Question 2b

Find the name of the album with the song that spend the most weeks in the top 40. Assume there is only one such song.

Tables

Songs �(song_id, song_name, album_id, weeks_in_top_40)

Artists �(artist_id, artist_name, first_yr_active)

Albums �(album_id, album_name, artist_id, yr_released, genre)

Question 2b

Find the name of the album with the song that spend the most weeks in the top 40. Assume there is only one such song.

Tables

Songs �(song_id, song_name, album_id, weeks_in_top_40)

Artists �(artist_id, artist_name, first_yr_active)

Albums �(album_id, album_name, artist_id, yr_released, genre)

SELECT album_name

FROM Albums AS A INNER JOIN Songs AS S �ON A.album_id = S.album_id

ORDER BY weeks_in_top_40 DESC LIMIT 1;

​

Question 2c

Find the artist name and the most weeks one of their songs spent in the top 40 for each artist. Include artists that have not released an album.

Tables

Songs �(song_id, song_name, album_id, weeks_in_top_40)

Artists �(artist_id, artist_name, first_yr_active)

Albums �(album_id, album_name, artist_id, yr_released, genre)

Question 2c

Find the artist name and the most weeks one of their songs spent in the top 40 for each artist. Include artists that have not released an album.

Tables

Songs �(song_id, song_name, album_id, weeks_in_top_40)

Artists �(artist_id, artist_name, first_yr_active)

Albums �(album_id, album_name, artist_id, yr_released, genre)

SELECT artist_name, MAX(weeks_in_top_40)

FROM Artists LEFT JOIN

(Songs INNER JOIN Albums ON Songs.album_id = Albums.album_id)

ON Artists.artist_id = Albums.artist_id

GROUP BY Artists.artist_id, artist_name;

​

Appendix (Tips and Tricks)

• Using an aggregate in WHERE is not allowed
• WHERE COUNT(*) > 500 is an invalid query!
• Don’t use HAVING without GROUP BY
• Just use WHERE instead!
• If GROUP BY is used, you can only SELECT columns that are aggregates OR are columns used to group by
• DISTINCT removes all duplicate rows
• Watch out for order of operators + logical processing order!
• If you want to see how many distinct values there are of a column, DISTINCT COUNT(X) will not work; use COUNT(DISTINCT X)

​

​

Attendance Link

​

https://cs186berkeley.net/attendance