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!
An Example
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.
The Formula
We can CREATE TABLE, ALTER TABLE, and DROP TABLE which all do pretty much what they sound like. We can also INSERT INTO, UPDATE, and DELETE data entries in tables using SQL. However, the star player, and the one with the most possible qualifiers is SELECT. For SELECT we can use the following formula:
Action → Action Modifier → Location → Filter → Sort
Action command
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.
By adding DISTINCT directly after SELECT, we can get only unique values.
Action Modifiers
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 MIN and MAX to get a single value or COUNT, AVG, or SUM to aggregate values.
Location Command
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.
Filter Commands
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: BETWEEN, IN, and LIKE. Additional optional filters that can be combined with WHERE for added specificity: AND / OR — returns records which may meet multiple criteriaNOT —returns all records where condition is not trueNull / NOT NULL — blank or not blank field
Sort Commands
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 COUNT, AVG, or SUM to indicate ways of grouping.
Then…
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.
Finally…
ASC (ascending) or DESC (descending) options allow for values to be ordered in your preferred direction.
Joins
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.
INNER JOIN
Returns records that have matching values in both tables
LEFT JOIN
Returns all records from the left table, and the matched records from the right table
RIGHT JOIN
Returns all records from the right table, and the matched records from the left table
FULL JOIN
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
