SQL练习（更新中...）

SQL练习

地址：https://sqlbolt.com/

SQL Lesson 1: SELECT queries 101

Table: Movies

Id	Title	Director	Year	Length_minutes
1	Toy Story	John Lasseter	1995	81
2	A Bug’s Life	John Lasseter	1998	95
3	Toy Story 2	John Lasseter	1999	93
4	Monsters, Inc.	Pete Docter	2001	92
5	Finding Nemo	Andrew Stanton	2003	107
6	The Incredibles	Brad Bird	2004	116
7	Cars	John Lasseter	2006	117
8	Ratatouille	Brad Bird	2007	115
9	WALL-E	Andrew Stanton	2008	104
10	Up	Pete Docter	2009	101
11	Toy Story 3	Lee Unkrich	2010	103
12	Cars 2	John Lasseter	2011	120
13	Brave	Brenda Chapman	2012	102
14	Monsters University	Dan Scanlon	2013	110

1. Find the title of each film

   select title from movies;
   

2. Find the director of each film

   select director from movies;
   

3. Find the title and director of each film

   select title, director from movies;
   

4. Find the title and year of each film

   select title, year from movies;
   

5. Find all the information about each film

   select * from movies;
   

SQL Lesson 2: Queries with constraints (Pt. 1)

Table: Movies

Id	Title	Director	Year	Length_minutes
1	Toy Story	John Lasseter	1995	81
2	A Bug’s Life	John Lasseter	1998	95
3	Toy Story 2	John Lasseter	1999	93
4	Monsters, Inc.	Pete Docter	2001	92
5	Finding Nemo	Andrew Stanton	2003	107
6	The Incredibles	Brad Bird	2004	116
7	Cars	John Lasseter	2006	117
8	Ratatouille	Brad Bird	2007	115
9	WALL-E	Andrew Stanton	2008	104
10	Up	Pete Docter	2009	101
11	Toy Story 3	Lee Unkrich	2010	103
12	Cars 2	John Lasseter	2011	120
13	Brave	Brenda Chapman	2012	102
14	Monsters University	Dan Scanlon	2013	110

1. Find the movie with a row id of 6

   SELECT * FROM movies 
   where id = 6;
   

2. Find the movies released in the years between 2000 and 2010

   SELECT * FROM movies 
   where year between 2000 and 2010;
   

3. Find the movies not released in the years between 2000 and 2010

   SELECT * FROM movies 
   where year not between 2000 and 2010;
   

4. Find the first 5 Pixar movies and their release year

   SELECT * FROM movies 
   limit 5;
   

SQL Lesson 3: Queries with constraints (Pt. 2)

Table: Movies

Id	Title	Director	Year	Length_minutes
1	Toy Story	John Lasseter	1995	81
2	A Bug’s Life	John Lasseter	1998	95
3	Toy Story 2	John Lasseter	1999	93
4	Monsters, Inc.	Pete Docter	2001	92
5	Finding Nemo	Andrew Stanton	2003	107
6	The Incredibles	Brad Bird	2004	116
7	Cars	John Lasseter	2006	117
8	Ratatouille	Brad Bird	2007	115
9	WALL-E	Andrew Stanton	2008	104
10	Up	Pete Docter	2009	101
11	Toy Story 3	Lee Unkrich	2010	103
12	Cars 2	John Lasseter	2011	120
13	Brave	Brenda Chapman	2012	102
14	Monsters University	Dan Scanlon	2013	110
87	WALL-G	Brenda Chapman	2042	97

1. Find all the Toy Story movies

   SELECT * FROM movies 
   where title like "%Toy Story%";
   

2. Find all the movies directed by John Lasseter

   SELECT * FROM movies 
   where director = "John Lasseter";
   

3. Find all the movies (and director) not directed by John Lasseter

   SELECT * FROM movies 
   where director != "John Lasseter";
   

4. Find all the WALL-* movies

   SELECT * FROM movies 
   where title like "WALL%";
   

SQL Lesson 4: Filtering and sorting Query results

Table: Movies

Id	Title	Director	Year	Length_minutes
1	Cars 2	John Lasseter	2011	120
2	Ratatouille	Brad Bird	2007	115
3	A Bug’s Life	John Lasseter	1998	95
4	Finding Nemo	Andrew Stanton	2003	107
5	WALL-E	Andrew Stanton	2008	104
6	Cars	John Lasseter	2006	117
7	The Incredibles	Brad Bird	2004	116
8	Toy Story 3	Lee Unkrich	2010	103
9	Monsters University	Dan Scanlon	2013	110
10	Toy Story	John Lasseter	1995	81
11	Monsters, Inc.	Pete Docter	2001	92
12	Toy Story 2	John Lasseter	1999	93
13	Up	Pete Docter	2009	101
14	Brave	Brenda Chapman	2012	102

1. List all directors of Pixar movies (alphabetically), without duplicates

   select distinct director 
   from Movies 
   order by director asc;
   

2. List the last four Pixar movies released (ordered from most recent to least)

   SELECT * FROM movies 
   order by year desc 
   limit 4;
   

3. List the first five Pixar movies sorted alphabetically

   SELECT * FROM movies 
   order by title asc 
   limit 5;
   

4. List the next five Pixar movies sorted alphabetically

   SELECT * FROM movies 
   order by title asc 
   limit 5 offset 5;
   

SQL Review: Simple SELECT Queries

Table: North_american_cities

City	Country	Population	Latitude	Longitude
Guadalajara	Mexico	1500800	20.659699	-103.349609
Toronto	Canada	2795060	43.653226	-79.383184
Houston	United States	2195914	29.760427	-95.369803
New York	United States	8405837	40.712784	-74.005941
Philadelphia	United States	1553165	39.952584	-75.165222
Havana	Cuba	2106146	23.05407	-82.345189
Mexico City	Mexico	8555500	19.432608	-99.133208
Phoenix	United States	1513367	33.448377	-112.074037
Los Angeles	United States	3884307	34.052234	-118.243685
Ecatepec de Morelos	Mexico	1742000	19.601841	-99.050674
Montreal	Canada	1717767	45.501689	-73.567256
Chicago	United States	2718782	41.878114	-87.629798

1. List all the Canadian cities and their populations

   SELECT city, population FROM north_american_cities 
   where country = "Canada";
   

2. Order all the cities in the United States by their latitude from north to south

   SELECT * FROM north_american_cities 
   where country = "United States" 
   order by latitude desc;
   

3. List all the cities west of Chicago, ordered from west to east

   SELECT city FROM north_american_cities 
   where Longitude < (SELECT Longitude FROM north_american_cities where city = "Chicago") 
   order by longitude desc;
   

4. List the two largest cities in Mexico (by population)

   SELECT * FROM north_american_cities 
   where country = "Mexico" 
   order by population desc
   limit 2;
   

5. List the third and fourth largest cities (by population) in the United States and their population

   SELECT * FROM north_american_cities 
   where country = "United States" 
   order by population desc
   limit 2 offset 2;
   

SQL Lesson 6: Multi-table queries with JOINs

Table: Movies (Read-Only)

Id	Title	Director	Year	Length_minutes
1	Toy Story	John Lasseter	1995	81
2	A Bug’s Life	John Lasseter	1998	95
3	Toy Story 2	John Lasseter	1999	93
4	Monsters, Inc.	Pete Docter	2001	92
5	Finding Nemo	Andrew Stanton	2003	107
6	The Incredibles	Brad Bird	2004	116
7	Cars	John Lasseter	2006	117
8	Ratatouille	Brad Bird	2007	115
9	WALL-E	Andrew Stanton	2008	104
10	Up	Pete Docter	2009	101
11	Toy Story 3	Lee Unkrich	2010	103
12	Cars 2	John Lasseter	2011	120
13	Brave	Brenda Chapman	2012	102
14	Monsters University	Dan Scanlon	2013	110

Table: Boxoffice (Read-Only)

Movie_id	Rating	Domestic_sales	International_sales
5	8.2	380843261	555900000
14	7.4	268492764	475066843
8	8	206445654	417277164
12	6.4	191452396	368400000
3	7.9	245852179	239163000
6	8	261441092	370001000
9	8.5	223808164	297503696
11	8.4	415004880	648167031
1	8.3	191796233	170162503
7	7.2	244082982	217900167
10	8.3	293004164	438338580
4	8.1	289916256	272900000
2	7.2	162798565	200600000
13	7.2	237283207	301700000

1. Find the domestic and international sales for each movie

   SELECT * FROM movies m left join Boxoffice b 
   where m.id = b.movie_id;
   

2. Show the sales numbers for each movie that did better internationally rather than domestically

   SELECT title, domestic_sales, international_sales
   FROM movies
   JOIN boxoffice
   ON movies.id = boxoffice.movie_id
   WHERE international_sales > domestic_sales;
   

3. List all the movies by their ratings in descending order

   SELECT title, domestic_sales, international_sales
   FROM movies
   JOIN boxoffice
   ON movies.id = boxoffice.movie_id
   order by rating desc;
   

SQL Lesson 7: OUTER JOINs

Table: Buildings (Read-Only)

Building_name	Capacity
1e	24
1w	32
2e	16
2w	20

Table: Employees (Read-Only)

Role	Name	Building	Years_employed
Engineer	Becky A.	1e	4
Engineer	Dan B.	1e	2
Engineer	Sharon F.	1e	6
Engineer	Dan M.	1e	4
Engineer	Malcom S.	1e	1
Artist	Tylar S.	2w	2
Artist	Sherman D.	2w	8
Artist	Jakob J.	2w	6
Artist	Lillia A.	2w	7
Artist	Brandon J.	2w	7
Manager	Scott K.	1e	9
Manager	Shirlee M.	1e	3
Manager	Daria O.	2w	6

1. Find the list of all buildings that have employees

   SELECT distinct building FROM employees 
   where building is not null;
   

2. Find the list of all buildings and their capacity

   SELECT * FROM buildings;
   

3. List all buildings and the distinct employee roles in each building (including empty buildings)

   select distinct building_name, role 
   from building 
   left join employees
   on building = building_name