An Introduction to SQL

The following article is designed to help people who are new to SQL. It aims to be a practical guide that explains the basic concepts and gets the reader using SQL as quickly as possible.

This is the first in a series of articles. Each article will come with an associated demonstration video.

Topics covered in future articles will be determined by user feedback.

Please contact us at [email protected] if you have any questions or suggestions about this tutorial.

1.0 – Video Tutorial

1.1 Databases, SQL, Tables, Columns and Rows and SQL

A database is a software program that enables the storage and retrieval of information. Typically it is used to store numbers and text.

SQL

(Structured Query Language)
(often referred to by its initials, pronounced “sequel” or as letters S Q L)
is a language of instructions that can be used to tell a database to retrieve, add, remove or update information in a database.

Information in a database is stored in tables. The data in tables are arranged in rows/records. A row/record contains one or more columns

Unlike in spreadsheets, such as Microsoft Excel, databases do not provide a direct visual representation of the data stored. The image below provides a diagram of how the information in a database is arranged.

Information in a database is stored in tables. The data in tables are arranged in rows/records. A row/record contains one or more columns

1.2 Getting information From a Table : SELECT

If we wanted to retrieve all the information from the PERSON table we could run the SQL statement:

fig 1.2

SELECT
FIRST_NAME,
LAST_NAME,
EMPLOYER_NAME
FROM
PERSON;

The keyword SELECT tells the database that you wish to retrieve information.

Next we have a list of column names identifying which columns we would like to retrieve. In this instance we have listed all the columns in the table. We separate the column names with a comma.

FROM PERSON means that we would like information from the table called PERSON.

The ; symbol at the end informs the database that this is the end of our instruction.

If we ran the SQL contained in fig 1.2 against a database containing a PERSON table as described in fig 1.1 the following data would be returned.

fig 1.3

Sam Smith S&P Mowing
Joanna Eiger S&P Mowing
Pradeep Swarrup Rehold Roofing

There is a shortcut that makes it easier to select all the columns from a table. Instead of listing them all you can use the * symbol. So we could re-write out SQL query as:

fig 1.4

SELECT
*
FROM
PERSON;

We did not have to select all the columns from the table, if we were only interested in the LAST_NAME and EMPLOYER_NAME columns we could have only asked for those columns:

fig 1.5

SELECT
LAST_NAME,
EMPLOYER_NAME
FROM
PERSON;

1.3 The WHERE Clause

We just looked at how we can retrieve information from a table and how we can specify which columns we wish to retrieve.

We can also specify which rows we wish to retrieve. We do not do this by specifying specifically which rows we are interested in, instead we specify a criteria that the row must match.

fig 1.6

SELECT
*
FROM
PERSON
WHERE
LAST_NAME=’Smith’;

In the query above we use a WHERE clause to specify that we are only interested in rows where the LAST_NAME column contains SMITH.

Looking at the query above and the PERSON table pictured in fig 1.1, how many rows would the above query return ?

If you said 1 you are correct, the PERSON table only contains a single row where the LAST_NAME column contains Smith, row 1.

If we ran the SQL query above in fig 1.6 on a database containing a PERSON table as pictured in fig 1.1 the following data would be returned :

fig 1.7

Sam Smith S&P Mowing

1.4 Selecting from multiple tables – Joining

It is possible to select data from multiple tables at the same time. It’s also possible to specify a WHERE clause criteria that references multiple tables.

Take another look at the PERSON and COMPANY tables pictured in fig 1.1 at the top of the page and image we wanted to see the first name and last name of each person along with the email address of the company they work for.

The following SQL query will help :

fig 1.8

SELECT
PERSON.FIRST_NAME,
PERSON.LAST_NAME,
COMPANY.EMAIL_ADDRESS
FROM
PERSON, COMPANY
WHERE
COMPANY.COMPANY_NAME=PERSON.EMPLOYER_NAME;

It will return the following data :

fig 1.9

Sam Smith [email protected]
Joanna Eiger [email protected]
Pradeep Swarrup [email protected]

Note that in our SQL query we have two tables listed after the FROM. Just as we separated multiple columns with commas here we have seperated multiple tables with commas.

Note that in our WHERE clause we have specified that the COMPANY_NAME column in the COMPANY table must equal the EMPLOYER_NAME column in the EMPLOYER table.

There are many ways in SQL to join data from multiple tables, this is perhaps the simplest.

We will go on to cover more sophisticated SQL techniques in future tutorials. Let us know what you would like us to cover by emailing us at [email protected]

Don’t forget to check out the accompanying video.

Comments are closed.