Nerd For Tech
Published in

Nerd For Tech

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:

English or SQL?

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.

Results of query

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 criteria
NOT —returns all records where condition is not true
Null / 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

--

--

--

NFT is an Educational Media House. Our mission is to bring the invaluable knowledge and experiences of experts from all over the world to the novice. To know more about us, visit https://www.nerdfortech.org/.

Recommended from Medium

Aligning product and customer-facing teams (Product Tank panel)

Beginner’s guide — Object Oriented Programming

Ballerina REST API service with MySQL connector

Web Development Trends 2019

Drupal 8/9: Altering Entities BaseField data type

Vehicle Entity

Thinkium consensus Protocol 1

Solving Sparse Matrix Systems in Rust

Benchmarking Infrastructure for the JVM

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Lauren Gifford

Lauren Gifford

More from Medium

SQL Server checklist for better performance

ALL ABOUT EMBEDDED AND DYNAMIC SQL

To import spreadsheet or excel data into a db file in SQLite format