南京大学：《计算机程序的构造和解释 Structure and Interpretation of Computer Programs》课程教学资源（PPT课件讲稿）24-SQL-II

Lecture 24-SQL:The Sequel

Lecture 24 - SQL: The Sequel

Review of Tables and Join

Review of Tables and Join

Table A table stores data.It consists of... a fixed number of columns. data entries stored in rows. To make a table in SQL,use a CREATE TABLE statement: CREATE TABLE[name]AS··.; To create rows of data,UNION together SELECT statements: SELECT [expr]AS [name],[expr]AS [name],.. UNION SELECT [expr]AS [name],[expr]AS [name],... UNION SELECT [expr]AS [name],[expr]AS [name],... To create rows of data from existing tables,use a SELECT statement with a FROM clause: SELECT [columns]FROM [table]WHERE [condition] ORDER BY [order][ASC/DESC]LIMIT [number];

Table A table stores data. It consists of... ● a fixed number of columns. ● data entries stored in rows. SELECT [expr] AS [name], [expr] AS [name], ... UNION SELECT [expr] AS [name], [expr] AS [name], ... UNION SELECT [expr] AS [name], [expr] AS [name], ...; To create rows of data from existing tables, use a SELECT statement with a FROM clause: CREATE TABLE [name] AS ...; To create rows of data, UNION together SELECT statements: SELECT [columns] FROM [table] WHERE [condition] ORDER BY [order] [ASC/DESC] LIMIT [number]; To make a table in SQL, use a CREATE TABLE statement:

Join Demo Given multiple tables,we can join them together by specifying their names, separated by commas,in the FROM clause of a SELECT statement. SELECT FROM table1,table2; When we join two tables,we get a new table with one row for each combination of rows from the original tables. parent child name fur name fur abraham barack abraham long parent child abraham long abraham barack barack short abraham barack barack short abraham barack clinton long abraham clinton clinton long abraham clinton abraham long abraham clinton barack short abraham clinton clinton long

Join Given multiple tables, we can join them together by specifying their names, separated by commas, in the FROM clause of a SELECT statement. SELECT * FROM table1, table2; When we join two tables, we get a new table with one row for each combination of rows from the original tables. parent child abraham barack abraham clinton name fur abraham long barack short clinton long parent child name fur abraham barack abraham long abraham barack barack short abraham barack clinton long abraham clinton abraham long abraham clinton barack short abraham clinton clinton long Demo

Check Your Understanding Table songs: Table albums: name artist album name artist release_year Table artists: name first_year_active 1. Write an SQL query that outputs the first 10 artists who became active after 2015. SELECT name FROM artists WHERE first_year_active 2015 LIMIT 10; 1.Write an SQL query that outputs the names and artists of songs that were released in 2010 ordered by the first year active of the artist. SELECT s.name,s.artist FROM songs AS s,artists AS ar,albums AS al WHERE album al.name AND s.artist ar.name AND release_year 2010 ORDER BY first_year_active;

Check Your Understanding 1. Write an SQL query that outputs the first 10 artists who became active after 2015. 1. Write an SQL query that outputs the names and artists of songs that were released in 2010 ordered by the first year active of the artist. Table songs: name | artist | album Table artists: name | first_year_active Table albums: name | artist | release_year SELECT name FROM artists WHERE first_year_active > 2015 LIMIT 10; SELECT s.name, s.artist FROM songs AS s, artists AS ar, albums AS al WHERE album = al.name AND s.artist = ar.name AND release_year = 2010 ORDER BY first_year_active;

Aggregation

Aggregation

Single Row Operations:Single-Table Queries So far,our SQL statements have referred to the values in a single row at a time. table dogs name fur Write a query that outputs the name of dogs that either have long fur or are named Grover. abraham long barack short SELECT name FROM dogs clinton long WHERE fur ='long'OR name ='grover' delano long output: name eisenhower short abraham fillmore curly clinton grover short delano herbert curly grover

Single Row Operations: Single-Table Queries So far, our SQL statements have referred to the values in a single row at a time. name fur abraham long barack short clinton long delano long eisenhower short fillmore curly grover short herbert curly table dogs SELECT name FROM dogs WHERE fur = 'long' OR name = 'grover'; name abraham clinton delano grover Write a query that outputs the name of dogs that either have long fur or are named Grover. output:

Single Row Operations:Join table dogs table parents name fur parent child result of cross product: delano long delano herbert name fur parent child herbert curly fillmore delano delano long delano herbert grover short fillmore grover delano long fillmore delano Write a query that outputs the names and delano long fillmore grover fur types of all of Fillmore's children. herbert curly delano herbert SELECT name,fur FROM dogs,parents herbert curly fillmore delano WHERE parent ='fillmore'AND herbert name child; curly fillmore grover grover short delano herbert output: name fur → grover short fillmore delano delano long → grover short fillmore grover grover short

Single Row Operations: Join Write a query that outputs the names and fur types of all of Fillmore's children. parent child delano herbert fillmore delano fillmore grover name fur delano long herbert curly grover short table dogs table parents SELECT name, fur FROM dogs, parents name fur parent child delano long delano herbert delano long fillmore delano delano long fillmore grover herbert curly delano herbert herbert curly fillmore delano herbert curly fillmore grover grover short delano herbert grover short fillmore delano grover short fillmore grover name fur WHERE parent = 'fillmore' AND name = child; delano long grover short result of cross product: output:

Aggregation Aggregation is the process of doing operations on groups of rows instead of just a single row. SQL provides aggregate functions whose return values can be used as entries in a column. output the average age of all dogs: table dogs SELECT AVG(age)AS avg_age FROM dogs; name fur age output: avg_age delano long 10 6.2 eisenhowe short 7 output the total number of rows: fillmore curly 8 SELECT COUNT(*)AS count FROM dogs; grover short 2 output: count herbert curly 4 5

Aggregation Aggregation is the process of doing operations on groups of rows instead of just a single row. name fur age delano long 10 eisenhowe r short 7 fillmore curly 8 grover short 2 herbert curly 4 table dogs count 5 avg_age 6.2 output: output: SELECT AVG(age) AS avg_age FROM dogs; output the average age of all dogs: SELECT COUNT(*) AS count FROM dogs; output the total number of rows: SQL provides aggregate functions whose return values can be used as entries in a column

Aggregate Function Demo Aggregation function Return value MAX([columns]) The maximum value in the given column(s) MIN([columns]) The minimum value in the given column(s) AVG([column]) The average value in the given column COUNT([column]) The number of values in the given column SUM([column]) The sum of the values in the given column table dogs output the sum of ages of all dogs: name fur age SELECT SUM(age)AS sum_age FROM dogs; eisenhower short 7 delano long 10 output the name that comes first alphabetically. grover short 2 SELECT MIN(name)AS min_name FROM dogs;

Aggregate Function Aggregation function Return value MAX([columns]) The maximum value in the given column(s) MIN([columns]) The minimum value in the given column(s) AVG([column]) The average value in the given column COUNT([column]) The number of values in the given column SUM([column]) The sum of the values in the given column SELECT SUM(age) AS sum_age FROM dogs; output the sum of ages of all dogs: SELECT MIN(name) AS min_name FROM dogs; output the name that comes first alphabetically: name fur age eisenhower short 7 delano long 10 grover short 2 table dogs Demo