SQLBolt, Learn SQL with simple, interactive exercises.

advanced.sql

Expressions

SELECT particle_speed / 2.0 AS half_particle_speed
FROM physics_data
WHERE ABS(particle_position) * 10.0 > 500;


List all movies and their combined sales in millions of dollars ✓
  SELECT title, (b.domestic_sales + b.international_sales) / 1000000 AS 'sales in millions' 
  FROM movies AS m 
    INNER JOIN boxoffice AS b ON m.id = b.movie_id;

List all movies and their ratings in percent ✓
  SELECT title, rating * 10 AS 'Rating %'  
  FROM movies AS m 
    INNER JOIN boxoffice AS b ON m.id = b.movie_id;
    
List all movies that were released on even number years ✓
  SELECT title, year FROM movies WHERE year % 2 = 0;


Aggregates

COUNT(*), COUNT(column)	  A common function used to counts the number of rows in the group if no column name is specified. Otherwise, count the number of rows in the group with non-NULL values in the specified column.
MIN(column)	              Finds the smallest numerical value in the specified column for all rows in the group.
MAX(column)	              Finds the largest numerical value in the specified column for all rows in the group.
AVG(column)	              Finds the average numerical value in the specified column for all rows in the group.
SUM(column)	              Finds the sum of all numerical values in the specified column for the rows in the group.

Postgresql aggreate functions: https://www.postgresql.org/docs/9.4/functions-aggregate.html


Find the longest time that an employee has been at the studio ✓
  SELECT name, MAX(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_years_employed
  FROM employees
  GROUP BY role;

Find the total number of employee years worked in each building ✓
  SELECT building, SUM(years_employed) FROM employees GROUP BY building


HAVING

SELECT group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias, …
FROM mytable
WHERE condition
GROUP BY column
HAVING group_condition;


Find the number of Artists in the studio (without a HAVING clause) ✓
  SELECT COUNT(*) FROM employees WHERE role LIKE 'artist';
  
Find the number of Employees of each role in the studio ✓
  SELECT role, COUNT(name) FROM employees GROUP BY role;
 
Find the total number of years employed by all Engineers ✓
  SELECT role, SUM(years_employed) FROM employees 
  GROUP BY role HAVING role LIKE 'engineer';


ORDER OF EXECUTION

https://pbs.twimg.com/media/EF5FYNIXYAAuyxV?format=jpg&name=medium


EXERCISES

Find the number of movies each director has directed ✓
  SELECT director, COUNT(*) FROM movies GROUP BY director;
  
Find the total domestic and international sales that can be attributed to each director ✓
  SELECT director, SUM(domestic_sales) + SUM(international_sales) AS Total FROM movies 
    LEFT JOIN boxoffice ON movies.id = boxoffice.movie_id 
  GROUP BY director;

insert.sql

INSERT

INSERT INTO mytable
VALUES (value_or_expr, another_value_or_expr, …),
       (value_or_expr_2, another_value_or_expr_2, …),
       …;
       
OR specify column name

INSERT INTO mytable
(column, another_column, …)
VALUES (value_or_expr, another_value_or_expr, …),
      (value_or_expr_2, another_value_or_expr_2, …),
      …;

Add the studio's new production, Toy Story 4 to the list of movies (you can use any director) ✓
  INSERT INTO movies (title, director, year, length_minutes) 
  VALUES ('Toy Story 4', 'John Lasseter', 2019, 123);
  
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 (movie_id, rating, domestic_sales, international_sales) 
  VALUES (15, 8.7, 340000000, 270000000);
  
  

sql.sql

Common Operators

=, !=, < <=, >, >=	      Standard numerical operators	                        col_name != 4
BETWEEN … AND …	          Number is within range of two values (inclusive)	    col_name BETWEEN 1.5 AND 10.5
NOT BETWEEN … AND …	      Number is not within range of two values (inclusive)	col_name NOT BETWEEN 1 AND 10
IN (…)	                  Number exists in a list	                              col_name IN (2, 4, 6)
NOT IN (…)	              Number does not exist in a list	                      col_name NOT IN (1, 3, 5)


Find the movies released in the years between 2000 and 2010 ✓
  SELECT * FROM movies WHERE year BETWEEN 2000 AND 2010;

Find the movies not released in the years between 2000 and 2010 ✓
  SELECT * FROM movies WHERE year NOT BETWEEN 2000 AND 2010;
  
  
Text Operators

=	          Case sensitive exact string comparison (notice the single equals)	                                    col_name = "abc"
!= or <>	  Case sensitive exact string inequality comparison	                                                    col_name != "abcd"
LIKE	      Case insensitive exact string comparison	                                                            col_name LIKE "ABC"
NOT LIKE	  Case insensitive exact string inequality comparison	                                                  col_name NOT LIKE "ABCD"
%	          Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE)	col_name LIKE "%AT%" (matches "AT", "ATTIC", "CAT" or even "BATS")
_	          Used anywhere in a string to match a single character (only with LIKE or NOT LIKE)	                  col_name LIKE "AN_" (matches "AND", but not "AN")
IN (…)	    String exists in a list	                                                                              col_name IN ("A", "B", "C")
NOT IN (…)	String does not exist in a list	                                                                      col_name NOT IN ("D", "E", "F")


Find all the Toy Story movies ✓
  SELECT * FROM movies WHERE title LIKE "%toy story%";

Find all the movies directed by John Lasseter ✓
  SELECT * FROM movies WHERE director LIKE "%john lasseter%";

Find all the movies (and director) not directed by John Lasseter ✓
  SELECT * FROM movies WHERE director NOT LIKE "%john lasseter%";

Find all the WALL-* movies ✓
  SELECT * 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 * FROM movies ORDER BY year DESC LIMIT 4;
  
List the first five Pixar movies sorted alphabetically ✓
  SELECT * FROM movies ORDER BY title ASC LIMIT 5;

List the next five Pixar movies sorted alphabetically ✓
  SELECT * FROM movies ORDER BY title ASC LIMIT 5 OFFSET 5;
  
  
REVIEW

List all the Canadian cities and their populations ✓
  SELECT * FROM north_american_cities WHERE country LIKE 'Canada';

Order all the cities in the United States by their latitude from north to south ✓
  SELECT * FROM north_american_cities 
  WHERE country LIKE 'United States' 
  ORDER BY latitude DESC;

List all the cities west of Chicago (which has longitude of -87.629798), ordered from west to east ✓
  SELECT * FROM north_american_cities
  WHERE longitude < -87.629798
  ORDER BY longitude ASC;
 
List the two largest cities in Mexico (by population) ✓
  SELECT * FROM north_american_cities 
  WHERE country LIKE '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 LIKE 'United States' 
  ORDER BY population desc 
  LIMIT 2 OFFSET 2


JOINs

Find the domestic and international sales for each movie ✓
  SELECT * FROM movies 
    INNER JOIN boxoffice 
        ON movies.id = boxoffice.movie_id;

Show the sales numbers for each movie that did better internationally rather than domestically ✓
  SELECT * FROM movies 
    INNER JOIN boxoffice 
        ON movies.id = boxoffice.movie_id 
        AND boxoffice.international_sales > boxoffice.domestic_sales;
      
List all the movies by their ratings in descending order ✓
  SELECT * FROM movies 
    INNER JOIN boxoffice 
        ON movies.id = boxoffice.movie_id 
  ORDER BY boxoffice.rating DESC;

Find the list of all buildings that have employees ✓
  SELECT DISTINCT building_name FROM buildings
    INNER JOIN employees ON buildings.building_name = employees.building;

List all buildings and the distinct employee roles in each building (including empty buildings) ✓
  SELECT DISTINCT buildings.building_name, employees.role FROM buildings
    LEFT JOIN employees ON buildings.building_name = employees.building;

Find the name and role of all employees who have not been assigned to a building ✓
  SELECT name, role, building FROM employees
    LEFT JOIN buildings ON employees.building = buildings.building_name 
  WHERE building IS NULL;
  
Find the names of the buildings that hold no employees ✓
  SELECT building_name, name FROM buildings 
    LEFT JOIN employees ON employees.building = buildings.building_name 
  WHERE name IS NULL;


    

update.sql

UPDATE