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 - 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.
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.
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.
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.
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. == why? SELECT * FROM records WHERE TITLE = “Programmer” these are all ok! select * from records where title = “Programmer” SeLeCt * FrOm records WhErE tItLe = “Programmer” this is not SeLeCt * FrOm records WhErE tItLe == “Programmer”
Creating Tables
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”;
Understanding SELECT SELECT name, division FROM records WHERE supervisor = “Ben Bitdiddle”;
Understanding SELECT SELECT name, division FROM records WHERE supervisor = “Ben Bitdiddle”;
Understanding SELECT SELECT name, division FROM records WHERE supervisor = “Ben Bitdiddle”;
Understanding SELECT SELECT name, division FROM records WHERE supervisor = “Ben Bitdiddle”;
Understanding SELECT SELECT name, division FROM records WHERE supervisor = “Ben Bitdiddle”; so we get this!
Joins table1 table2
Joins table1 table2 SELECT * FROM table1, table2; *implicitly performs an inner join
Joins table1 table2 SELECT * FROM table1, table2; *implicitly performs an inner join
Thinking with Joins (1) Where is my data coming from? SELECT <cols> FROM <tables> WHERE <condition>;
Thinking with Joins (1) Where is my data coming from? SELECT <cols> FROM <tables> WHERE <condition>;
Thinking with Joins (1) Where is my data coming from? SELECT <cols> FROM <tables> WHERE <condition>; (2) Which joins make sense?
Thinking with Joins (1) Where is my data coming from? SELECT <cols> FROM <tables> WHERE <condition>; (2) Which joins make sense? (3) Anything else to filter on?
Thinking with Joins (1) Where is my data coming from? SELECT <cols> FROM <tables> WHERE <condition>; (2) Which joins make sense? (4) Which columns to keep? (3) Anything else to filter on?
Aggregation …allows us to combine rows to get a single value, in some manner.
Aggregation …allows us to combine rows to get a single value, in some manner. Useful aggregation functions: MAX MIN COUNT SUM
Aggregation …allows us to combine rows to get a single value, in some manner. Useful aggregation functions: MAX MIN COUNT SUM SELECT COUNT(*) FROM records SELECT MAX(Salary), MIN(Division) FROM records
Aggregation (cont.) SELECT COUNT(*) FROM records
Aggregation (cont.) SELECT COUNT(*) FROM records 8
Aggregation (cont.) SELECT MAX(Salary), MIN(Division) FROM records
Aggregation (cont.) SELECT MAX(Salary), MIN(Division) FROM records 150000 Accounting
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 .
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 . SELECT COUNT(*) FROM records GROUP BY Division SELECT MAX(Salary), MIN(Division) FROM records GROUP BY Supervisor HAVING COUNT(*) >= 2
Now With Groups (cont.) SELECT COUNT(*) FROM records GROUP BY Division
Now With Groups (cont.) SELECT COUNT(*) FROM records GROUP BY Division accounting administration computer
Now With Groups (cont.) SELECT COUNT(*) FROM records GROUP BY Division 2 accounting administration 1 computer 5
Now With Groups (cont.) SELECT MAX(Salary), MIN(Division) FROM records GROUP BY Supervisor HAVING COUNT(*) >= 2
Now With Groups (cont.) SELECT MAX(Salary), MIN(Division) FROM records GROUP BY Supervisor HAVING COUNT(*) >= 2 alyssa ben eben oliver
Now With Groups (cont.) SELECT MAX(Salary), MIN(Division) FROM records GROUP BY Supervisor HAVING COUNT(*) >= 2 30000 Computer alyssa ben 40000 Computer eben 18000 Accounting oliver 150000 Administration
Now With Groups (cont.) SELECT MAX(Salary), MIN(Division) FROM records GROUP BY Supervisor HAVING COUNT(*) >= 2 30000 Computer alyssa HAVING is a keyword we can ben 40000 Computer attach to GROUP BY which eben 18000 Accounting filters out groups! oliver 150000 Administration
Now With Groups (cont.) SELECT MAX(Salary), MIN(Division) FROM records GROUP BY Supervisor HAVING COUNT(*) >= 2 30000 Computer alyssa HAVING is a keyword we can ben 40000 Computer attach to GROUP BY which eben 18000 Accounting filters out groups! oliver 150000 Administration
Now With Groups (cont.) SELECT MAX(Salary), MIN(Division) FROM records GROUP BY Supervisor HAVING COUNT(*) >= 2 30000 Computer alyssa HAVING is a keyword we can 40000 Computer ben attach to GROUP BY which oliver 150000 Administration filters out groups!
Now With Groups (cont.) SELECT MAX(Salary), MIN(Division) FROM records GROUP BY Supervisor HAVING COUNT(*) >= 2 30000 Computer alyssa HAVING is a keyword we can 40000 Computer ben attach to GROUP BY which oliver 150000 Administration filters out groups! Discuss: Why can’t we just use WHERE ?
An Example of Aggregation with Groups …allows us to combine rows to get a single value, in some manner. Useful aggregation functions: MAX MIN COUNT SUM SELECT COUNT(*) FROM records SELECT MAX(Salary), MIN(Division) FROM records
thanks for coming! have a good week :) Attendance: links.cs61a.org/albert-disc Slides: albertxu.xyz/teaching/cs61a/
Recommend
More recommend
