3 rectangles stacked on top of 1 another.

A beginner's guide to SQL.

Last TendedNov 2021
PlantedOct 2021
StatusSeed
TagsData
The letter 'A' in upper and lower case.

Definition

SQL stands for Structured Query Language. Its purpose is to allow users to interact with relational databases. Data is structured in tables. Each table has:

  • 1 column for each field &
  • 1 row for each record.
A table of data.

An SQL statement is composed of:

  • an ordered list of clauses (example: SELECT, FROM, WHERE, …),
  • each clause has its own syntax &
  • each statement ends in ';'.
Code.
3 rectangles stacked on top of 1 another decorated with measurement guides.

Creating

schema.sql

output

schema.sql

fleet

idnameclasslength
1a-wingfighter7
2cruisercorvette127
3x-wingfighter13
4y-wingbomber23

Allow SQL to create IDs

schema.sql

output

schema.sql

fleet

idnameclasslength
1a-wingfighter7
2cruisercorvette127
3x-wingfighter13
4y-wingbomber23
Source
Squares, triangles & circles scattered on 1 side of a line, squares in a line on the other.

Filtering Data

query.sql

output

query.sql

fleet

idnameclasslength
1a-wingfighter7
2cruisercorvette127
3x-wingfighter13
4y-wingbomber23

query.sql

output

query.sql

fleet

idnameclasslength
1a-wingfighter7
2cruisercorvette127
3x-wingfighter13
4y-wingbomber23

query.sql

output

query.sql

fleet

idnameclasslength
1a-wingfighter7
2cruisercorvette127
3x-wingfighter13
4y-wingbomber23

query.sql

output

query.sql

fleet

idnameclasslength
2cruisercorvette127
4y-wingbomber23
3x-wingfighter13
1a-wingfighter7

query.sql

output

query.sql

fleet

idnameclasslength
4y-wingbomber23
2cruisercorvette127
1a-wingfighter7
3x-wingfighter13

query.sql

output

query.sql

fleet

idnameclasslength
1a-wingfighter7
2cruisercorvette127
3x-wingfighter13
4y-wingbomber23

query.sql

output

query.sql

fleet

idnameclasslength
4y-wingbomber23
2cruisercorvette127
1a-wingfighter7
3x-wingfighter13

Conditions

Comparison Operators

=equals
<less-than
<=less-than or equals
>greater-than
>=greater-than or equals
!=does not equal

query.sql

output

query.sql

fleet

idnameclasslength
1a-wingfighter7
2cruisercorvette127
3x-wingfighter13
4y-wingbomber23

query.sql

output

query.sql

fleet

idnameclasslength
1a-wingfighter7
2cruisercorvette127
3x-wingfighter13
4y-wingbomber23

query.sql

output

query.sql

fleet

idnameclasslength
1a-wingfighter7
2cruisercorvette127
3x-wingfighter13
4y-wingbomber23

query.sql

output

query.sql

fleet

idnameclasslength
1a-wingfighter7
2cruisercorvette127
3x-wingfighter13
4y-wingbomber23

query.sql

output

query.sql

fleet

idnameclasslength
1a-wingfighter7
2cruisercorvette127
3x-wingfighter13
4y-wingbomber23

query.sql

output

query.sql

fleet

idnameclasslength
1a-wingfighter7
2cruisercorvette127
3x-wingfighter13
4y-wingbomber23

query.sql

output

query.sql

fleet

idnameclasslength
1a-wingfighter7
2cruisercorvette127
3x-wingfighter13
4y-wingbomber23

Multiple Conditions

query.sql

output

query.sql

fleet

idnameclasslength
1a-wingfighter7
2cruisercorvette127
3x-wingfighter13
4y-wingbomber23

query.sql

output

query.sql

fleet

idnameclasslength
1a-wingfighter7
2cruisercorvette127
3x-wingfighter13
4y-wingbomber23

query.sql

output

query.sql

fleet

idnameclasslength
1a-wingfighter7
2cruisercorvette127
3x-wingfighter13
4y-wingbomber23

query.sql

output

query.sql

fleet

idnameclasslength
1a-wingfighter7
2cruisercorvette127
3x-wingfighter13
4y-wingbomber23
Squares, triangles & circles scattered on 1 side of a line, all shapes in oredered lines on the other.

Aggregating Data

Aggregate functions

COUNTnumber of rows in a column
SUMadd values in a column
MINlowest value in a column
MAXhighest value in a column
AVGaverage of a group of selected values

query.sql

output

query.sql

Query Result

SUM(length)
170

/* In previous examples, the original table with highlighted cells was displayed. In this example, the table resulting from the query is displayed. */

query.sql

output

query.sql

Query Result

classSUM(length)
cruiser170
fighter20
bomber23

query.sql

output

query.sql

Query Result

classcombined_length
cruiser170
fighter20
bomber23

query.sql

output

query.sql

fleet

classcombined_length
cruiser170
fighter20
bomber23

query.sql

output

query.sql

Query Result

idnameclasslengthlength_category
1a-wingfighter7small
2cruisercorvette127large
3x-wingfighter13medium
4y-wingbomber23medium
Source
A line from 1 square to another, then to 3 smaller squares.

Subqueries

index.sql

output

index.sql

fleet

idnameclasslength
1a-wingfighter7
2cruisercorvette127
3x-wingfighter13
4y-wingbomber23

personnel

idnamestarshiprelated_to
1Luke33
2Obi-Wan1null
3Leianull1
A line connecting to squares, then leading to 3 smaller squares.

Joins

An SQL database commonly contains data that has been split up into multiple related tables. You can bring that data back together using joins.

The following examples will be referring to this data:

schema.sql

output

schema.sql

fleet

idnameclasslength
1a-wingfighter7
2cruisercorvette127
3x-wingfighter13
4y-wingbomber23

personnel

idnamestarshiprelated_to
1Luke33
2Obi-Wan1null
3Leianull1

teams

idmember_amember_b
113

Types of Joins

Explicit Inner Join

query.sql

output

query.sql

Query Result

idnameclasslengthnamestarshiprelated_to
3x-wingfighter13Luke33
1a-wingfighter7Obi-Wan1null

Explicit Inner Join (with other filtering techniques)

Because the fleet & personnel tables both have a column with heading 'name', we need to specify which table we referring to these columns.

query.sql

output

query.sql

Query Result

nameclasspilot
a-wingfighterObi-Wan

Outer Join

This join will list all rows from the primary table, even if there is no data for that row in the secondary table.

query.sql

output

query.sql

Query Result

namepilot
x-wingLuke
a-wingObi-Wan
cruisernull
y-wingnull

Self Join

A table joined to itself.

query.sql

output

query.sql

Query Result

namerelated_to
LukeLeia
LeiaLuke

Multiple Joins

query.sql

output

query.sql

Query Result

namename
LukeLeia
A computer screen with a blinking cursor.

Playground

A library with book cases 30 meters tall and a globe in the middle of them.

Learning Resources