Skip to content

Queries

One of the core purposes of the SQL language is to retrieve information stored in a database. This is commonly referred to as querying.

Queries allow us to communicate with the database by asking questions and returning a result set with data relevant to the question.

We will be querying a database with one table named movies.

Fun fact

IBM started out SQL as SEQUEL (Structured English QUEry Language) in the 1970’s to query databases.

  • Click on the following link to open the Online SQL Editor
  • Click on File>Open DB and select the file movie.db in ClassesICT.
  • In the editor, type the following:
SQL
SELECT * FROM movie;
What are the column names?
  • id
  • name
  • genre
  • year
  • imdb_rating
  • Type the following commands in the SQL editor to try and find out what they do:

SELECT

SQL
SELECT name, genre 
FROM movie ;

AS

SQL
SELECT name AS 'Titles'
FROM movie;

DISTINCT

SQL
SELECT DISTINCT genre 
FROM movie;

WHERE

SQL
SELECT *
FROM movie
WHERE imdb_rating > 8;

LIKE

SQL
SELECT * 
FROM movie
WHERE name LIKE 'Se_en';
SQL
SELECT * 
FROM movie
WHERE name LIKE 'A%';

IS NULL

SQL
SELECT name
FROM movie 
WHERE imdb_rating IS NOT NULL;

BETWEEN

SQL
SELECT *
FROM movie
WHERE year BETWEEN 1990 AND 1999;

AND

SQL
SELECT * 
FROM movie
WHERE year BETWEEN 1990 AND 1999
   AND genre = 'romance';

OR

SQL
SELECT *
FROM movie
WHERE year > 2014
   OR genre = 'action';

ORDER BY

SQL
SELECT *
FROM movie
ORDER BY name DESC;

LIMIT

SQL
SELECT *
FROM movie
LIMIT 10;

CASE

SQL
SELECT name,
CASE
  WHEN imdb_rating > 8 THEN 'Fantastic'
  WHEN imdb_rating > 6 THEN 'Poorly Received'
  ELSE 'Avoid at All Costs'
END AS 'Review'
FROM movie;