Breaking It Down — SQL Syntax
At the intersection of human language and computer language.
Ah SQL… do you pronounce it “ess-cue-ell” or “see-qual”? Confusing and controversial pronunciation aside, this programming language is awesome because it allows us to ask for some data from an unseen database and receive information back that is reminiscent of and excel file. That is, organized in a human readable format. The programming language which, in my opinion, is closest in look and words to English, can be deceptively difficult to write.
The most difficult part of writing SQL query for me is remembering the different commands I have access to and the order in which they should be written. It helps to have a formula, a grammar and syntax to follow. This post started as my personal notes to help with remembering and I hope they help you too!
Given the following statement:
Looks somewhat simple, no? It translates in English to “Select the data of columns aisle and summed quantity from table groceries. The returned results should be grouped according to the aisle and then ordered by the summed quantity.
Let’s break it down.
ALTER TABLE, and
DROP TABLE which all do pretty much what they sound like. We can also
DELETE data entries in tables using SQL. However, the star player, and the one with the most possible qualifiers is
SELECT we can use the following formula:
Action → Action Modifier → Location → Filter → Sort
Like verbs in English, these statements say what needs to be done.
SELECT extracts data from a database. We can specify columns to be returned or use the splat operator (*) to indicate that we want all the columns.
DISTINCT directly after
SELECT, we can get only unique values.
These commands used in the
SELECT portion indicate that we want some operation done on the data before we see it and before other manipulations are performed. We can use
MAX to get a single value or
SUM to aggregate values.
The most standard and straightforward portion. We need to specify which database table contains the columns we want returned. The action is followed by
FROM and the table name on which to execute the command.
We now enter optional territory. Here on out, all commands indicate further organization of the results and there are a lot of options. Next in the statement order are filtering commands. There indicate criteria to apply to filter results.
The main guy here is
WHERE followed by column name and a comparator operator like:
<, or preposition such as:
LIKE. Additional optional filters that can be combined with
WHERE for added specificity:
OR — returns records which may meet multiple criteria
NOT —returns all records where condition is not true
NOT NULL — blank or not blank field
Once we have exactly the fields which should be returned, we can specify the order in which they should appear.
GROUP BY groups rows that have the same values into summary rows. Here we can also add aggregate functions like
SUM to indicate ways of grouping.
ORDER BY column name(s). If multiple columns are indicated, results will be sorted by first then if there are two of the same, sorted by the next column.
ASC (ascending) or
DESC (descending) options allow for values to be ordered in your preferred direction.
To further complicate things, it is possible to query multiple tables at once and determine if there are shared values between the two There are several ways to do this outlined below which depend on the desired relationship between the tables.
Returns records that have matching values in both tables
Returns all records from the left table, and the matched records from the right table
Returns all records from the right table, and the matched records from the left table
Returns all records when there is a match in either left or right table
Hopefully SQL is now a bit less mystic and you feel confidently able to ask the database for its contents!
The final tidbit I’ll leave here is this
SQL GROUP BY Statement
The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in…