SQL BOLT

-- to select specific columns 
SELECT column, another_column, ...
FROM mytable
 
-- to get every thing we can use *
 
SELECT *
FROM mytable

-- 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 Director, Title FROM movies
 
-- Find the title and year of each film
SELECT year, Title FROM movies
 
 
-- Find all the information about each film
SELECT * FROM movies
 

SELECT column, another_column, …
FROM mytable
WHERE condition
    AND/OR another_condition
    AND/OR …;

-- Find the movie with a row id of 6
SELECT id, title FROM movies
WHERE id == 6 
 
 
-- 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 
 
-- Find the first 5 Pixar movies and their release year
SELECT * FROM movies
WHERE id BETWEEN 0 AND 5

-- Find all the Toy Story movies
SELECT title FROM movies
WHERE title  
    LIKE "%Toy%"
 
-- Find all the movies directed by John Lasseter
SELECT * FROM movies
WHERE Director = "John Lasseter"
 
-- Find all the movies (and director) not directed by John Lasseter
SELECT * FROM movies
WHERE Director != "John Lasseter"
 
-- Find all the WALL-* movies
SELECT * FROM movies
WHERE Title LIKE "WALL%"    

SELECT DISTINCT column, another_column, …
FROM mytable
WHERE condition(s);

SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC;

SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC
LIMIT num_limit OFFSET num_offset;

-- List all directors of Pixar movies (alphabetically), without duplicates ✓
SELECT DISTINCT Director FROM movies
ORDER BY Director ASC
 
-- 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

-- 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 
 
-- I Directly copy pasted this one becase i dont want to put energy on learning Longitude and Latidude
-- List all the cities west of Chicago, ordered from west to east
SELECT city, longitude 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 * FROM North_american_cities
WHERE Country LIKE "United States"
ORDER BY Population DESC
LIMIT 2 OFFSET 2

SELECT column, another_table_column, …
FROM mytable
INNER JOIN another_table 
    ON mytable.id = another_table.id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

-- 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
WHERE international_sales >= 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 rating DESC