SQL - Queries

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

SELECT

SELECT name, genre, year
FROM movies;

AS

AS is a keyword in SQL that allows you to rename a column or table using an alias.

SELECT name AS 'Titles'
FROM movies;

DISTINCT(중복 제거)

DISTINCT is used to return unique values in the output. It filters out all duplicate values in the specified column(s).

SELECT DISTINCT genre 
FROM movies;

WHERE(조건)

WHERE clause filters the result set to only include rows where the following condition is true.

Comparison operators used with the WHERE clause are:

• = equal to
• != not equal to
• > greater than
• < less than
• >= greater than or equal to
• <= less than or equal to

SELECT * 
FROM movies
WHERE year > 2014;

LIKE

LIKE is a special operator used with the WHERE clause to search for a specific pattern in a column.

_ means you can substitute any individual character here without breaking the pattern.

% is a wildcard character that matches zero or more missing letters in the pattern.

• A% matches all movies with names that begin with letter ‘A’
• %a matches all movies that end with ‘a’

SELECT * 
FROM movies 
WHERE name LIKE '%man%';

IS NULL

Unknown values are indicated by NULL.

• IS NULL
• IS NOT NULL

SELECT name
FROM movies 
WHERE imdb_rating IS NOT NULL;

BETWEEN

The BETWEEN operator is used in a WHERE clause to filter the result set within a certain range.

이 기능은 주의가 필요!

Remember, BETWEEN two alphabets is exclusive of the second alphabet.

SELECT *
FROM movies
WHERE name BETWEEN 'D' AND 'G';

• D, E, F 이름의 영화를 검색

Remember, BETWEEN two numbers is inclusive of the second number.

SELECT *
FROM movies
WHERE year BETWEEN 1970 AND 1979;

• 1970년부터 1979년까지의 영화를 검색

AND(교집합)

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

Or(합집합)

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

Order by

We can sort the results using ORDER BY, either alphabetically or numerically.

SELECT *
FROM movies
ORDER BY imdb_rating DESC
LIMIT 3;

CASE

A CASE statement allows us to create different outputs (usually in the SELECT statement). It is SQL’s way of handling if-then logic.

SELECT name,
 CASE
  WHEN genre = 'romance' THEN 'Chill'
  WHEN genre = 'comedy' THEN 'Chill'
  ELSE 'Intense'
 END AS 'Mood'
FROM movies;

• Each WHEN tests a condition and the following THEN gives us the string if the condition is true.
• The ELSE gives us the string if all the above conditions are false.
• The CASE statement must end with END.
• The AS rename the column to shorten it.