SQL exercises from sqlbolt.com.
- SELECT Queries 101
- Queries With Constraints (Pt. 1)
- Queries With Constraints (Pt. 2)
- Filtering and Sorting Query Results
- Simple SELECT Queries
- Multi-table Queries With JOINs
- OUTER JOINs
- A Short Note on NULLs
- Queries With Expressions
- Queries With Aggregates (Pt. 1)
- Queries With Aggregates (Pt. 2)
- Order of Execution of a Query
- Inserting Rows
- Updating Rows
- Deleting Rows
- Creating Tables
- Altering Tables
- Dropping Tables
- Find the title of each film.
SELECT title
FROM movies;- Find the director of each film.
SELECT director
FROM movies;- Find the title and director of each film.
SELECT title, director
FROM movies;- Find the title and year of each film.
SELECT title, year
FROM movies;- Find all the information about each film.
SELECT *
FROM movies;- Find the movie with a row id of 6.
SELECT title
FROM movies
WHERE id = 6;- Find the movies released in the years between 2000 and 2010.
SELECT title
FROM movies
WHERE year BETWEEN 2000 AND 2010;- Find the movies not released in the years between 2000 and 2010.
SELECT title
FROM movie
WHERE year NOT BETWEEN 2000 and 2010;- Find the first 5 Pixar movies and their release year.
SELECT title, year
FROM movies
WHERE year <= 2003;- Find all the Toy Story movies.
SELECT title
FROM movies
WHERE title LIKE 'Toy Story%';- Find all the movies directed by John Lasseter.
SELECT title
FROM movies
WHERE director = 'John Lasseter';- Find all the movies (and director) not directed by John Lasseter.
SELECT title
FROM movies
WHERE director != 'John Lasseter';- Find all the WALL-* movies.
SELECT title
FROM movies
WHERE title LIKE 'WALL-%';- List all directors of Pixar movies (alphabetically), without duplicates.
SELECT DISTINCT director
FROM movies
ORDER BY director;- List the last four Pixar movies released (ordered from most recent to least).
SELECT title
FROM movies
ORDER BY year DESC
LIMIT 4;- List the first five Pixar movies sorted alphabetically.
SELECT title
FROM movies
ORDER BY title
LIMIT 5;- List the next five Pixar movies sorted alphabetically.
SELECT title
FROM movies
ORDER BY title
LIMIT 5 OFFSET 5;- List all the Canadian cities and their populations.
SELECT city, population
FROM north_american_cities
WHERE country = 'Canada';- Order all the cities in the United States by their latitude from north to south.
SELECT city
FROM north_american_cities
WHERE country = 'United States'
ORDER BY latitude DESC;- List all the cities west of Chicago, ordered from west to east.
SELECT city
FROM north_american_cities
WHERE longitude < -87.629798
ORDER BY longitude;- List the two largest cities in Mexico (by population).
SELECT city
FROM north_american_cities
WHERE country = 'Mexico'
ORDER BY population DESC
LIMIT 2;- List the third and fourth largest cities (by population) in the United States and their population.
SELECT city, population
FROM north_american_cities
WHERE country = 'United States'
ORDER BY population DESC
LIMIT 2 OFFSET 2;- Find the domestic and international sales for each movie.
SELECT title, domestic_sales, international_sales
FROM boxoffice
JOIN movies ON movie_id = movies.id;- Show the sales numbers for each movie that did better internationally rather than domestically.
SELECT title, domestic_sales, international_sales
FROM boxoffice
JOIN movies ON movie_id = movies.id
WHERE international_sales > domestic_sales;- List all the movies by their ratings in descending order.
SELECT title, rating
FROM movies
JOIN boxoffice ON movies.id = movie_id
ORDER BY rating DESC;- Find the list of all buildings that have employees.
SELECT DISTINCT building
FROM employees
LEFT JOIN buildings ON building = building_name;- Find the list of all buildings and their capacity.
SELECT building_name, capacity
FROM buildings;- List all buildings and the distinct employee roles in each building (including empty buildings).
SELECT DISTINCT building_name, role
FROM buildings
LEFT JOIN employees ON building = building_name;- 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;- Find the names of the buildings that hold no employees.
SELECT DISTINCT building_name
FROM buildings
LEFT JOIN employees ON building_name = building
WHERE name IS NULL;- List all movies and their combined sales in millions of dollars.
SELECT title,
(domestic_sales + international_sales) / 1000000 AS gross_sales
FROM movies
JOIN boxoffice ON movies.id = boxoffice.movie_id;- List all movies and their ratings in percent.
SELECT title,
rating * 10 AS rating_percent
FROM movies
JOIN boxoffice ON movies.id = boxoffice.movie_id;- List all movies that were released on even number years.
SELECT title
FROM movies
WHERE year % 2 = 0;- Find the longest time that an employee has been at the studio.
SELECT MAX(years_employed) AS maximum_years_employed
FROM employees;- For each role, find the average number of years employed by employees in that role.
SELECT role,
AVG(years_employed) AS average_number_of_years
FROM employees
GROUP BY role;- Find the total number of employee years worked in each building.
SELECT building,
SUM(years_employed) AS total_years
FROM employees
GROUP BY building;- Find the number of Artists in the studio (without a HAVING clause).
SELECT COUNT(name) AS number_of_artists
FROM employees
WHERE role = 'Artist';- Find the number of Employees of each role in the studio.
SELECT role,
COUNT(name) AS employees
FROM employees
GROUP by role;- Find the total number of years employed by all Engineers.
SELECT role,
SUM(years_employed) AS total_years_employed
FROM employees
GROUP BY role
HAVING role = 'Engineer';1. FROM and JOINs
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT / OFFSET
- Find the number of movies each director has directed.
SELECT director,
COUNT(title) AS number_of_movies
FROM movies
GROUP BY director;- Find the total domestic and international sales that can be attributed to each director.
SELECT director,
SUM(domestic_sales + international_sales) AS total_sales
FROM movies
JOIN boxoffice ON movies.id = boxoffice.movie_id
GROUP BY director;- 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', 'John Lasseter', 2017, 90);- 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 boxoffice
VALUES (4, 8.7, 340000000, 270000000);- 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;- The year that Toy Story 2 was released is incorrect, it was actually released in 1999.
UPDATE movies
SET year = 1999
WHERE title = 'Toy Story 2';- 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 title = 'Toy Story 8'
AND director = 'El Directore';- This database is getting too big, lets remove all movies that were released before 2005.
DELETE FROM movies
WHERE year < 2005;- Andrew Stanton has also left the studio, so please remove all movies directed by him.
DELETE FROM movies
WHERE director = 'Andrew Stanton';-
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
CREATE TABLE IF NOT EXISTS database (
name STRING,
version FLOAT,
download_count INTEGER);- 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;- 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';- We've sadly reached the end of our lessons, lets clean up by removing the Movies table.
DROP TABLE IF EXISTS movies;- And drop the BoxOffice table as well.
DROP TABLE IF EXISTS boxoffice;