Skip to content

Introduction to SQL

Here you can access the books table used in the course.

Take Notes

Add notes about the concepts you've learned and SQL cells with queries you want to keep.

--Using AND and OR with the WHERE clause--

-- Select the title and release_year for all German-language films released before 2000 SELECT title, release_year FROM films WHERE release_year <2000 AND language = 'German';

-- Update the query to see all German-language films released after 2000 SELECT title, release_year FROM films WHERE release_year > 2000 AND language = 'German';

-- Select all records for German-language films released after 2000 and before 2010 SELECT * FROM films WHERE (release_year >2000 AND release_year <2010) AND (language = 'German');

-- Find the title and year of films from the 1990 or 1999 SELECT title, release_year FROM films WHERE (release_year = 1990 OR release_year = 1999);

-- Add a filter to see only English or Spanish-language 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 SELECT title, release_year FROM films WHERE (release_year = 1990 OR release_year = 1999) AND (language = 'English' OR language = 'Spanish') AND gross >2000000;

-- Narrow down your query to films with budgets > $100 million SELECT title, release_year FROM films WHERE release_year BETWEEN 1990 AND 2000 AND budget >100000000;

-- Restrict the query to only Spanish-language films SELECT title, release_year FROM films WHERE release_year BETWEEN 1990 AND 2000 AND budget > 100000000 AND language = 'Spanish';

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

-- Find the title and release_year for all films over two hours in length released in 1990 and 2000 SELECT title, release_year FROM films WHERE release_year IN (1990, 2000) AND duration > 120;

-- Find the title and language of all films in English, Spanish, and French SELECT title, language FROM films WHERE language IN ('English', 'Spanish', 'French');

-- Find the title, certification, and language all films certified NC-17 or R that are in English, Italian, or Greek SELECT title, certification, language FROM films WHERE certification IN ('NC-17', 'R') AND language IN ('English', 'Italian', 'Greek');

-- Count the unique titles SELECT COUNT (DISTINCT title) AS nineties_english_films_for_teens FROM films -- Filter to release_years to between 1990 and 1999 WHERE release_year IN (1990, 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');

Add your notes here

Spinner
DataFrameas
df
variable
-- Find the number of decades in the films table
SELECT (MAX(release_year)-MIN(release_year)) / 10.0 AS number_of_decades
FROM films;

//Which release_year had the most language diversity?
//Take your time to translate this question into code. We'll get you started then it's up to you to test your queries in the console.
//"Most language diversity" can be interpreted as COUNT(DISTINCT ___). Now over to you.
//Hint You'll need to GROUP BY release_year but ORDER BY the language count; however, you won't be ordering in the default order.

SELECT COUNT (DISTINCT language) AS language_count, release_year
FROM films
GROUP BY  release_year
ORDER BY language_count DESC;

Explore Datasets

Use the books table to explore the data and practice your skills!

  • Select only the title column.
  • Alias the title column as book_title.
  • Select the distinct author names from the author column.
  • Select all records from the table and limit your results to 10.