SQL

Selecting columns, filtering rows
SELECT name, birthdate FROM people;
SELECT * FROM people LIMIT 10;
SELECT DISTINCT language FROM films;

#COUNT(*) tells you how many rows are in a table
SELECT COUNT(*) FROM people;
SELECT COUNT(birthdate)
FROM people;

#count the number of non-missing values in a particular column
SELECT COUNT(birthdate) FROM people;
SELECT COUNT(DISTINCT birthdate) FROM people;

SELECT title FROM films WHERE title = 'Metropolis'; #equal
SELECT title FROM films WHERE title <> 'Metropolis'; #not equal

SELECT title, release_year FROM films 
WHERE language='Spanish' and release_year < 2000; SELECT title FROM films WHERE release_year >= 1994
AND release_year <= 2000;

SELECT title
FROM films
WHERE release_year
BETWEEN 1994 AND 2000;

SELECT title, language
FROM films
WHERE language IN ('English', 'Spanish', 'French') ;

#count the number of missing birth dates 
SELECT COUNT(*)
FROM people
WHERE birthdate IS NULL;

SELECT name
FROM people
WHERE birthdate IS NOT NULL;
The LIKE (NOT LIKE) operator can be used in a WHERE clause to search for a pattern in a column
SELECT name FROM people WHERE name LIKE 'B%';
SELECT name FROM people WHERE name LIKE '_r%';
SELECT name FROM people WHERE name NOT LIKE 'A%';
Aggregate function – AVG(), MAX(), SUM()..
SELECT SUM(duration) FROM films;
SELECT SUM(gross) FROM films WHERE release_year >= 2000;
SELECT AVG(gross) FROM films WHERE title LIKE 'A%';
AS
SELECT (4.0 / 3.0) AS result;
SELECT MAX(budget) AS max_budget, MAX(duration) AS max_duration FROM films;
SELECT title, (gross-budget) AS net_profit FROM films; 
SELECT MAX(release_year)-MIN(release_year) AS difference 
FROM films;
ORDER BY
SELECT title
FROM films
ORDER BY release_year DESC;
Sorting multiple columns
SELECT birthdate, name
FROM people
ORDER BY birthdate, name;
GROUP BY
SELECT sex, count(*)
FROM employees
GROUP BY sex;

SELECT sex, count(*)
FROM employees
GROUP BY sex
ORDER BY count DESC;

select release_year, count(*) from films  group by release_year;

select release_year, avg(duration) from films  group by release_year;
HAVING
SELECT release_year
FROM films
GROUP BY release_year
HAVING COUNT(title) > 10;
SELECT release_year, AVG(budget) AS avg_budget, AVG(gross) AS avg_gross 
FROM films 
WHERE release_year > 1990 
GROUP BY release_year 
HAVING AVG(budget) > 60000000
ORDER BY AVG(gross) DESC; 
SELECT title, imdb_score
FROM films
JOIN reviews
ON films.id = reviews.film_id
WHERE title = 'To Kill a Mockingbird';