SLIDE 1
CS61A Discussion 12
SQL
Slides: albertxu.xyz/teaching/cs61a/
Why SQL?
a declarative language - instead of implementing a specific procedure to make a table, we give SQL queries that are handled for us.
CS61A Discussion 12 SQL Albert Xu Slides: - - PowerPoint PPT Presentation
CS61A Discussion 12 SQL Albert Xu Slides: - - PowerPoint PPT Presentation
CS61A Discussion 12 SQL Albert Xu Slides: albertxu.xyz/teaching/cs61a/ Why SQL? a declarative language - instead of implementing a specific procedure to make a table, we give SQL queries that are handled for us. Why SQL? a declarative language
SLIDE 2
SLIDE 3
Why SQL?
a declarative language - instead of implementing a specific procedure to make a table, we give SQL queries that are handled for us. …this is implemented for us in the SQL engine we’re using, and something that we don’t have to worry about.
SLIDE 4
Why SQL?
a declarative language - instead of implementing a specific procedure to make a table, we give SQL queries that are handled for us. …this is implemented for us in the SQL engine we’re using, and something that we don’t have to worry about.
SLIDE 5
Why SQL?
a declarative language - instead of implementing a specific procedure to make a table, we give SQL queries that are handled for us. …this is implemented for us in the SQL engine we’re using, and something that we don’t have to worry about. SQL ver.
SLIDE 6
Why SQL?
a declarative language - instead of implementing a specific procedure to make a table, we give SQL queries that are handled for us. …this is implemented for us in the SQL engine we’re using, and something that we don’t have to worry about. When it comes to storing data, we need tables! One of the most popular ways of working with tables is SQL. SQL ver.
SLIDE 7
SQLite
- The specific implementation we’re using is called
SQLite
- It’s case insensitive for keywords and column names!
- White space is also fine, just like in Scheme!
- Boolean operators (AND, NOT, OR) are the same,
equality is mostly the same except = vs. ==
SELECT * FROM records WHERE TITLE = “Programmer” select * from records where title = “Programmer” SeLeCt * FrOm records WhErE tItLe = “Programmer”
these are all ok! why?
SeLeCt * FrOm records WhErE tItLe == “Programmer”
this is not
SLIDE 8
Creating Tables
SLIDE 9
Creating Tables
CREATE TABLE records AS SELECT “Ben Bitdiddle” AS Name, “Computer” AS Division, “Wizard” AS Title, 60000 AS Salary, “Oliver Warbucks” AS Supervisor UNION SELECT “Alyssa P Hacker”, “Computer” , “Programmer”, 40000, “Ben Bitdiddle” UNION SELECT “Cy D Fect” , “Computer” , “Programmer”, 35000, “Ben Bitdiddle” UNION SELECT “Lem E Tweakit” , “Computer” , “Technician”, 25000, “Ben Bitdiddle” UNION . . . SELECT “Robert Cratchet”, “Accounting”, “Scrivener” , 18000, “Eben Scrooge”;
SLIDE 10
Understanding SELECT
SELECT name, division FROM records WHERE supervisor = “Ben Bitdiddle”;
SLIDE 11
SELECT name, division FROM records WHERE supervisor = “Ben Bitdiddle”;
Understanding SELECT
SLIDE 12
SELECT name, division FROM records WHERE supervisor = “Ben Bitdiddle”;
Understanding SELECT
SLIDE 13
SELECT name, division FROM records WHERE supervisor = “Ben Bitdiddle”;
Understanding SELECT
SLIDE 14
SELECT name, division FROM records WHERE supervisor = “Ben Bitdiddle”;
so we get this!
Understanding SELECT
SLIDE 15
Joins
table1 table2
SLIDE 16
Joins
table1 table2
SELECT * FROM table1, table2;
*implicitly performs an inner join
SLIDE 17
Joins
table1 table2
SELECT * FROM table1, table2;
*implicitly performs an inner join
SLIDE 18
Thinking with Joins
SELECT <cols> FROM <tables> WHERE <condition>; (1) Where is my data coming from?
SLIDE 19
Thinking with Joins
SELECT <cols> FROM <tables> WHERE <condition>; (1) Where is my data coming from?
SLIDE 20
Thinking with Joins
SELECT <cols> FROM <tables> WHERE <condition>; (1) Where is my data coming from? (2) Which joins make sense?
SLIDE 21
Thinking with Joins
SELECT <cols> FROM <tables> WHERE <condition>; (1) Where is my data coming from? (2) Which joins make sense? (3) Anything else to filter on?
SLIDE 22
Thinking with Joins
SELECT <cols> FROM <tables> WHERE <condition>; (1) Where is my data coming from? (4) Which columns to keep? (2) Which joins make sense? (3) Anything else to filter on?
SLIDE 23
Aggregation
…allows us to combine rows to get a single value, in some manner.
SLIDE 24
Aggregation
…allows us to combine rows to get a single value, in some manner.
Useful aggregation functions: MAX MIN COUNT SUM
SLIDE 25
Aggregation
…allows us to combine rows to get a single value, in some manner.
SELECT COUNT(*) FROM records SELECT MAX(Salary), MIN(Division) FROM records
Useful aggregation functions: MAX MIN COUNT SUM
SLIDE 26
Aggregation (cont.)
SELECT COUNT(*) FROM records
SLIDE 27
Aggregation (cont.)
SELECT COUNT(*) FROM records
8
SLIDE 28
Aggregation (cont.)
SELECT MAX(Salary), MIN(Division) FROM records
SLIDE 29
Aggregation (cont.)
150000 Accounting
SELECT MAX(Salary), MIN(Division) FROM records
SLIDE 30
Now With Groups
GROUP BY is an optional keyword we can add to our SELECT statement, which creates groups. When aggregation is performed on a grouped table, we get an aggregate value for each group.
SLIDE 31
SELECT COUNT(*) FROM records GROUP BY Division
GROUP BY is an optional keyword we can add to our SELECT statement, which creates groups. When aggregation is performed on a grouped table, we get an aggregate value for each group.
SELECT MAX(Salary), MIN(Division) FROM records GROUP BY Supervisor HAVING COUNT(*) >= 2
Now With Groups
SLIDE 32
Now With Groups (cont.)
SELECT COUNT(*) FROM records GROUP BY Division
SLIDE 33
Now With Groups (cont.)
SELECT COUNT(*) FROM records GROUP BY Division
accounting administration computer
SLIDE 34
Now With Groups (cont.)
SELECT COUNT(*) FROM records GROUP BY Division
2 1 5
accounting administration computer
SLIDE 35
Now With Groups (cont.)
SELECT MAX(Salary), MIN(Division) FROM records GROUP BY Supervisor HAVING COUNT(*) >= 2
SLIDE 36
Now With Groups (cont.)
SELECT MAX(Salary), MIN(Division) FROM records GROUP BY Supervisor HAVING COUNT(*) >= 2
alyssa ben eben
- liver
SLIDE 37
Now With Groups (cont.)
SELECT MAX(Salary), MIN(Division) FROM records GROUP BY Supervisor HAVING COUNT(*) >= 2
30000 Computer 40000 Computer 18000 Accounting 150000 Administration
alyssa ben eben
- liver
SLIDE 38
Now With Groups (cont.)
HAVING is a keyword we can attach to GROUP BY which filters out groups!
SELECT MAX(Salary), MIN(Division) FROM records GROUP BY Supervisor HAVING COUNT(*) >= 2
30000 Computer 40000 Computer 18000 Accounting 150000 Administration
alyssa ben eben
- liver
SLIDE 39
Now With Groups (cont.)
HAVING is a keyword we can attach to GROUP BY which filters out groups!
SELECT MAX(Salary), MIN(Division) FROM records GROUP BY Supervisor HAVING COUNT(*) >= 2
30000 Computer 40000 Computer 18000 Accounting 150000 Administration
alyssa ben eben
- liver
SLIDE 40
Now With Groups (cont.)
HAVING is a keyword we can attach to GROUP BY which filters out groups!
SELECT MAX(Salary), MIN(Division) FROM records GROUP BY Supervisor HAVING COUNT(*) >= 2
30000 Computer 40000 Computer 150000 Administration
alyssa ben
- liver
SLIDE 41
Now With Groups (cont.)
HAVING is a keyword we can attach to GROUP BY which filters out groups!
SELECT MAX(Salary), MIN(Division) FROM records GROUP BY Supervisor HAVING COUNT(*) >= 2
30000 Computer 40000 Computer 150000 Administration
alyssa ben
- liver
Discuss: Why can’t we just use WHERE?
SLIDE 42
An Example of Aggregation with Groups
…allows us to combine rows to get a single value, in some manner.
SELECT COUNT(*) FROM records SELECT MAX(Salary), MIN(Division) FROM records
Useful aggregation functions: MAX MIN COUNT SUM
SLIDE 43
thanks for coming!
Attendance: links.cs61a.org/albert-disc Slides: albertxu.xyz/teaching/cs61a/