Skip to content

Intermediate SQL

COUNT(*) tells you how many records are in a table. However, if you want to count the number of non-missing values in a particular field, you can call COUNT() on just that field.

Take Notes

#Count the total number of records in the people table, aliasing the result as count_records

SELECT COUNT(*) AS count_birthdate FROM people;

#Count the number of records with a birthdate in the people table, aliasing the result as count_birthdate.

SELECT COUNT(birthdate) AS count_birthdate FROM people;

#Count the records for languages and countries in the films table; alias as count_languages and count_countries.

SELECT COUNT(language) AS count_languages, COUNT(country) AS count_countries FROM films;

# use the DISTINCT keyword to select the unique values from a field.

SELECT DISTINCT(country) FROM films;

#Return the number of unique countries represented in the films table, aliased as count_distinct_countries.

SELECT COUNT(DISTINCT country) AS count_distinct_countries FROM films;

SELECT title, release_year FROM films WHERE (release_year = 1990 OR release_year = 1999) AND (language = 'English' OR language = 'Spanish') -- Filter films with more than $2,000,000 gross AND gross > 2000000;

SELECT title, release_year FROM films WHERE release_year BETWEEN 1990 AND 2000 AND budget > 100000000 -- Amend the query to include Spanish or French-language films AND (language ='Spanish' OR language ='French');

Spinner
DataFrameas
df
variable
-- Add your own queries here
SELECT *
FROM cinema.reviews
LIMIT 5

-- Count the unique titles SELECT title AS nineties_english_films_for_teens FROM films -- Filter to release_years to between 1990 and 1999 WHERE release_year BETWEEN 1990 AND 1999 -- Filter to English-language films AND language ='English' -- Narrow it down to G, PG, and PG-13 certifications AND certification IN ('G','PG','PG-13');

Numerical data: - AVG() - SUM()

Various data types: - COUNT() - MAX() - MIN()

1 hidden cell