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
   

SQL Lesson 8: A short note on NULLs

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
Engineer	Yancy I.		0
Artist	Oliver P.		0

1. Find the name and role of all employees who have not been assigned to a building

   SELECT name, role 
   FROM employees 
   where building is null;
   

2. Find the names of the buildings that hold no employees

   SELECT b.building_name 
   FROM buildings b 
   left join employees e
   on b.building_name = e.building
   where role is null;
   

SQL Lesson 9: Queries with expressions

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. List all movies and their combined sales in millions of dollars

   SELECT title, (Domestic_sales + International_sales)/1000000 as sales 
   FROM movies m 
   left join boxoffice b
   on m.id = b.movie_id;
   

2. List all movies and their ratings in percent

   SELECT title, rating * 10 as rating
   FROM movies m 
   left join boxoffice b
   on m.id = b.movie_id;
   

3. List all movies that were released on even number years

   SELECT * FROM movies
   where year % 2= 0;
   

SQL Lesson 10: Queries with aggregates (Pt. 1)

Table: Employees

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 longest time that an employee has been at the studio

   SELECT MAX(years_employed) as max_years_employed
   FROM employees;
   

2. For each role, find the average number of years employed by employees in that role

   SELECT role, avg(years_employed) as avg_years_employed
   FROM employees
   group by role;
   

3. Find the total number of employee years worked in each building

   SELECT building, sum(years_employed) as sum_years_employed
   FROM employees
   group by building;
   

SQL Lesson 11: Queries with aggregates (Pt. 2)

Table: Employees

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 number of Artists in the studio (without a HAVING clause)

   SELECT role, count(*) as Number_of_artists
   FROM employees
   where role = "Artist";	
   

2. Find the number of Employees of each role in the studio

   SELECT building, role, count(*) as count 
   FROM employees
   group by role;
   

3. Find the total number of years employed by all Engineers

   SELECT role, sum(years_employed) as sum_years_employed 
   FROM employees 
   where role = "Engineer";
   

SQL Lesson 12: Order of execution of a Query

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 number of movies each director has directed

   SELECT director, count(*) as Count
   FROM movies 
   group by director;
   

2. Find the total domestic and international sales that can be attributed to each director

   SELECT director, sum(Domestic_sales+International_sales) as sum_sales
   FROM movies m 
   left join Boxoffice b
   on m.id = b.movie_id
   group by director;
   

SQL Lesson 13: Inserting rows

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

Table: Boxoffice (Read-Only)

Movie_id	Rating	Domestic_sales	International_sales
3	7.9	245852179	239163000
1	8.3	191796233	170162503
2	7.2	162798565	200600000

1. Add the studio’s new production, Toy Story 4 to the list of movies (you can use any director)

   INSERT INTO movies VALUES (4, "Toy Story 4", "El Directore", 2015, 90);
   

2. Toy Story 4 has been released to critical acclaim! It had a rating of 8.7, and made 340 million domestically and 270 million internationally. Add the record to the BoxOffice table.

   INSERT INTO movies VALUES (4, "Toy Story 4", "El Directore", 2015, 90);
   

SQL Lesson 14: Updating rows

Table: Movies

Id	Title	Director	Year	Length_minutes
1	Toy Story	John Lasseter	1995	81
2	A Bug’s Life	El Directore	1998	95
3	Toy Story 2	John Lasseter	1899	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 8	El Directore	2010	103
12	Cars 2	John Lasseter	2011	120
13	Brave	Brenda Chapman	2012	102
14	Monsters University	Dan Scanlon	2013	110

1. The director for A Bug’s Life is incorrect, it was actually directed by John Lasseter

   update movies
   set director = "John Lasseter"
   where id = 2;
   

2. The year that Toy Story 2 was released is incorrect, it was actually released in 1999

   update movies
   set year = 1999
   where id = 3;
   

3. Both the title and director for Toy Story 8 is incorrect! The title should be “Toy Story 3” and it was directed by Lee Unkrich

   update movies
   set title = "Toy Story 3",director = "Lee Unkrich"
   where id = 11;
   

SQL Lesson 15: Deleting rows

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. This database is getting too big, lets remove all movies that were released before 2005.

   delete from movies where year < 2005;
   

2. Andrew Stanton has also left the studio, so please remove all movies directed by him.

   delete from movies where director = "Andrew Stanton";
   

SQL Lesson 16: Creating tables

1. Create a new table named Database with the following columns:

   – Name A string (text) describing the name of the database
   – Version A number (floating point) of the latest version of this database
   – Download_count An integer count of the number of times this database was downloaded

   This table has no constraints.

   create table Database (
       Name text,
       Version float,
       Download_count integer
   );
   

SQL Lesson 17: Altering tables

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. Add a column named Aspect_ratio with a FLOAT data type to store the aspect-ratio each movie was released in.

   alter table movies
   add Aspect_ratio float;
   

2. Add another column named Language with a TEXT data type to store the language that the movie was released in. Ensure that the default for this language is English.

   alter table movies 
   add Language text default English;
   

SQL Lesson 18: Dropping tables

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. We’ve sadly reached the end of our lessons, lets clean up by removing the Movies table

   drop table movies;
   

2. And drop the BoxOffice table as well

   drop table Boxoffice;