An Introduction to SQL

7 min readJul 7, 2022

Structured Query Language (SQL) is a programming language used to manage relational databases. Databases store information and then allow this information to be accessed and manipulated.

Relational Databases

A relational database organizes information into tables. The structure of a relational database is similar to that of an Excel spreadsheet. The tables in a database can be thought of like individual sheets within one big Excel file, which would be the database. Each table contains data which is organized into columns and rows. Each column holds a particular type of value.

For example, if we had a table of information about pets, it could have columns for a pet’s name, species, and age. The table would also have an id column. This is a very important column that should appear in every table. Each row in a table has an id which identifies that piece of information and can be used to extract data from this row for use.

Writing SQL Queries

SQL is used by writing declarative statements, often referred to as queries. There are many SQL clauses that are used to write SQL statements. Some of the most common clauses are as follows.

SELECT — extracts data from a database

UPDATE — updates data in a database

DELETE — deletes data from a database

INSERT INTO — inserts new data into a database

CREATE DATABASE — creates a new database

ALTER DATABASE — modifies a database

CREATE TABLE — creates a new table within a database

ALTER TABLE — modifies a table

DROP TABLE — deletes a table

CREATE INDEX — creates an index (search key)

DROP INDEX — deletes an index

It is important to note that these clauses are written in all capital letters. Each individual SQL query must end with a semicolon. Although not a command, another important part of SQL queries is the * symbol. The * symbol is used to represent “all”. So, if we had a table of pets, the following SQL query would extract all of the data in the pet table.

SELECT * FROM pets;

Creating a Table

A new table can be created using the following format.

CREATE TABLE table_name (column_1_name data_type,column_2_name data_type,column_3_name data_type);

To create our pet table, our SQL statement would be written as follows.

CREATE TABLE pets (id INTEGER,name TEXT,species TEXT);

This gives us a table with a name of “pets” and with three columns, “id”, “name”, and “species”. To add data to this table, we use the INSERT INTO command. The format for adding data to a table is as follows.

INSERT INTO table_name (column_name_1, column_name_2, column_name_3)VALUES (value_1, value_2, value_3);

The SQL statement for adding a pet to our table would be written as follows. When writing text in the values, single quotes must be used. The statement will not work if double quotes are used.

INSERT INTO pets (id, name, species)VALUES (1, ‘Baby’, ‘Dog’);

As we saw before, using the SELECT clause will extract data from a table.

SELECT * FROM pets;

The statement above would extract all of the information for every pet in the table. If we only want to see the names of our pets though, we can use the statement below.

SELECT name FROM pets;

Now let’s say we also want to keep track of our pets’ ages. We can add a column to the table using the ALTER TABLE clause. The statement to do so is as follows. We first write ALTER TABLE, then specify the table, then clarify how we want to alter the table.

ALTER TABLE petsADD COLUMN age INTEGER;

Now that we have a new column, we need to update the information for Baby in our table. To do this, we use the UPDATE clause, as shown below.

UPDATE petsSET age = 7WHERE id = 1;

The WHERE clause is a very important and powerful tool in SQL. It is used to filter information so we only see data that matches certain criteria. For example, if we wanted to see information for all of the dogs in our table, we would use the following statement.

SELECT * FROM pets WHERE species = “Dog”;

Let’s say we added something to the table that we actually don’t want there. To remove a record from our table, we use a DELETE FROM statement, which would be formatted as follows.

DELETE FROM pets

WHERE condition describing unwanted data;

Constraints

Constraints can be added to columns and can be used to add information about the data that can be housed in a column or how a column can behave. This is done while creating a new table. Constraints are added to columns after their data type is specified. A constraint can cause a database to reject data that does not meet the constraint. We could place constraints on the pets table using the following statement.

CREATE TABLE pets (id INTEGER PRIMARY KEY,name TEXT,species TEXT DEFAULT ‘Dog’,age INTEGER NOT NULL);

The PRIMARY KEY clause tells the table that the id column can be used to identify a row. This also ensures that each id that is entered in the table will be unique. There is also a UNIQUE clause that would ensure that each value in a column would be unique. The NOT NULL clause ensures that a value will be submitted for each column when a new record is added to the table. Essentially, this ensures that no information is missing. The DEFAULT clause sets a value to be the assumed value for a piece of information added to the table unless another value is specified. If someone tried to add a new pet to the table, the species for the pet would be “Dog” unless a different species were specified.

Useful SQL Keywords

AS

AS allows columns or tables to be renamed using an alias. The following statement would rename “age” to “years old”.

SELECT age AS yearsOldFROM pets;

DISTINCT

DISTINCT filters out all duplicate values in a column and returns unique values when extracting information from a table.

SELECT DISTINCT nameFROM pets;

WHERE

As we learned before, WHERE is a very powerful tool that can filter information so we only see data that matches certain criteria. Comparison operators can be used when using WHERE to filter information. For example, the following would extract information for all pets older than 5 years old.

SELECT *FROM petsWHERE age > 5;

LIKE

LIKE can be used to compare similar values.

SELECT *FROM petsWHERE species LIKE ‘_at’;

In the statement above, the underscore acts as a wildcard. This means that the statement will return records where the species has three letters and ends in “at”, no matter what the first letter is. So, this statement can return cats, bats, and rats.

SELECT *FROM petsWHERE species LIKE ‘%og’;

In the statement above, the percentage also acts as a wildcard. This statement will return records where the species ends in “og”, no matter what letters come before it. So, this statement can return dogs and frogs.

IS NULL and IS NOT NULL

IS NULL and IS NOT NULL can be used to extract records where information is or is not missing, respectively. The following would return all records for pets that do not have an age assigned to them.

SELECT *FROM petsWHERE age IS NULL;

BETWEEN

BETWEEN is used with a WHERE clause to filter data within a certain range. BETWEEN accepts two values which can be numbers, text, or dates. The following would return records for every pet between the ages of five and ten.

SELECT *FROM petsWHERE age BETWEEN 5 AND 10;

When used with numbers, BETWEEN is inclusive and when used with text, it is exclusive. This means that the statement above would return information for every pet that is five years old, every pet that is ten years old, and every pet in between. However, if we wanted to return pets whose names are between A and E, a pet whose name starts with A would be included but a pet whose name starts with E would not be included in the results.

AND

AND allows multiple criteria to be added to a query. The following query returns information for pets who meet the age criteria and the species criteria. A record will be returned only if it meets both criteria.

SELECT *FROM petsWHERE age BETWEEN 5 AND 10AND species = ‘Dog’;

OR

Like AND, OR allows multiple criteria to be added to a query. However, unlike AND, it will return records that match either criteria.

ORDER BY

ORDER BY sorts the results returned by a query by one of its properties. By default records are sorted in ascending order either alphabetically or numerically. The results of an ORDER BY query can be reversed using the DESC keyword. The first statement would list all pets from youngest to oldest while the second would list them from oldest to youngest.

SELECT *FROM petsORDER BY age;
SELECT *FROM petsORDER BY age DESC;

LIMIT

If everyone we know added all of their pets to the database, each query could return thousands of pets! That might be more information than we want to look at. We could choose to see only 10 of these pets using the LIMIT keyword, as below.