study

A diary of some sort. An interesting one.

Lesson 2: 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:

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

BETWEEN

filter result within a certain range.

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

Imporant Notes:

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:

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:

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.