Queries

This lesson mainly touches on frequently used clauses for querying a database.

AS

Rename a column by providing an Alias. e.g.

SELECT xxx AS 'yyy' FROM zzz;

where:

• xxx => column to be selected
• ‘yyy’ => new alias
• zzz => table being selected from

DISTINCT

Get all distinct values in the column (no dupes would be in the result set).

SELECT DISTINCT xxx FROM yyy;

WHERE

Filters the result to only include where the condition which follows in the clause is true.

SELECT * FROM xxx WHERE yyy > zzz;

LIKE operator

compares similar values.

SELECT * FROM xxx WHERE yyy LIKE 'a_c';

The _ in the above query is a wildcard. See SQL Wildcards and examples here

Comparison operators in SQL

• =
• !=
• >
• >=
• <
• <=
• IS NULL
• IS NOT NULL

BETWEEN

filter result within a certain range.

SELECT * FROM xxx WHERE yyy BETWEEN 'a' AND 'j';

Imporant Notes:

• BETWEEN two letters is not inclusive of the 2nd letter.
• BETWEEN two numbers is inclusive of the 2nd number.

AND operator

combines multiple conditions for the WHERE clause (all conditions must be true for the overall statement to be true).

FROM aaa WHERE bbb BETWEEN 11 AND 22 AND ccc = 'ddd';

OR Operator

similar to AND but works if just one condition from the many is true.

ORDER BY

for sort results.

SELECT * FROM xxx ORDER BY yyy DESC;

Note:

• DESC => sorts result in descending order
• ASC => sorts result in ascending order

LIMIT

specifies maximum number of rows result will have. The clause always goes at the end of the query.

SELECT * FROM xxx LIMIT 10;

CASE

Usually SQL’s way of including if-then logic in queries. Important operators to note:

• WHEN
• THEN
• ELSE
• END

An example:

SELECT name
  CASE
    WHEN x > y THEN 'a'
    WHEN x < y THEN 'b'
    ELSE 'c'
  END AS 'review'
FROM movies;

Final note

Use the SELECT clause everytime you want to query a database.