Queries

Question 1

SELECT

Answer 1

SELECT column1, column2

FROM table_name;

Question 2

AS

Answer 2

SELECT name AS ‘Titles’

FROM movies;

Question 3

DISTINCT

Answer 3

SELECT DISTINCT tools
FROM inventory;

will return unique values in the output, and filters out all duplicate values in the specified column(s).

Question 4

WHERE

Answer 4

SELECT *
FROM movies
WHERE imdb_rating > 8;

used to obtain only the info we want from a table.

Comparison Operators:

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

Question 5

LIKE

Answer 5

SELECT *
FROM movies
WHERE name LIKE ‘Se_en’;

used to compare similar values (this example returns both movies Seven and Se7en).

SELECT *
FROM movies
WHERE name LIKE ‘A%’;

will return all movies that begin with the letter ‘A’.

SELECT *
FROM movies
WHERE name LIKE ‘%a’;

will return all movies that end with ‘a’.

Question 6

IS NULL

Answer 6

unknown values are indicated by NULL. You can use IS NULL or IS NOT NULL.

SELECT name
FROM movies
WHERE imdb_rating IS NOT NULL;

will return all movies with an imdb rating.

Question 7

BETWEEN

Answer 7

The BETWEEN operator is used in a WHERE clause to filter the result set within a certain range. It accepts two values that are either numbers, text or dates.

SELECT *
FROM movies
WHERE year BETWEEN 1990 AND 1999;

will return movies from 1990 up to, and including 1999.

SELECT *
FROM movies
WHERE name BETWEEN ‘A’ AND ‘J’;

will return movies that begin with the letter ‘A’ up to, but not including one’s that begin with ‘J’.

Question 8

AND

Answer 8

Used to combine multiple conditions in a WHERE clause to make the result set more specific.

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

Question 9

OR

Answer 9

Used to combine multiple conditions in WHERE if any condition is true (for AND all conditions must be true).

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

Question 10

ORDER BY

Answer 10

Used to sort the results either alphabetically or numerically.

SELECT *
FROM movies
ORDER BY name;

You can specify the order by using DESC (high to low or Z-A) or ASC (low to high or A-Z).

SELECT *
FROM movies
WHERE imdb_rating > 8
ORDER BY year DESC;

Question 11

LIMIT

Answer 11

Used to specify the max number of rows in the result set.

SELECT *
FROM movies
ORDER BY imdb_rating DESC
LIMIT 20;

Question 12

CASE

Answer 12

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

ELSE gives us the string if all conditions are false.

The CASE statement must end with END.

SELECT name,
CASE
WHEN imdb_rating > 8 THEN ‘Fantastic’
WHEN imdb_rating > 5 THEN ‘Poorly Received’
ELSE ‘Avoid at All Costs’
END
FROM movies;

You can add AS to END to rename the CASE statement.

SELECT name,
CASE
WHEN imdb_rating > 8 THEN ‘Fantastic’
WHEN imdb_rating > 5 THEN ‘Poorly Received’
ELSE ‘Avoid at All Costs’
END AS ‘Review’
FROM movies;