SQL – an introduction to basic SELECT queries

SQL stands for Structured Query Language, which is a domain-specific language used for operating with data in a relational database system. As per its name, the most important function of SQL is to help users query the data they need in an efficient way.

This article is written with the intention to give readers an introduction to SQL with some of its most widely-used queries.

Table of content
SELECT … FROM …
WHERE …
ORDER BY …
LIMIT …
… AS …
SELECT DISTINCT … FROM …

For illustrative purposes, we will be using the Chinook database, which is a sample database we have when installing DBeaver (a GUI tool to deal with SQL). You may also obtain the database here.

SELECT … FROM …

By using the SELECT (data) FROM (table) query, we ask the system to return the data we need from the specified table.

Example 1:

SELECT Name, ArtistId 
FROM  Artist;

Name	ArtistId
AC/DC	1
Accept	2
Aerosmith	3
Alanis Morissette	4
Alice In Chains	5

Note that the result we show here is trimmed to save space (in fact, there are 275 rows outputted for this query).

Example 2:

We may also use SELECT *, which means select all columns.

SELECT *
FROM  Artist;

ArtistId	Name
1	AC/DC
2	Accept
3	Aerosmith
4	Alanis Morissette
5	Alice In Chains

Example 3:

Get some data from the table Track.

SELECT Name, AlbumId, GenreId, Milliseconds
FROM Track;

Name	AlbumId	GenreId	Milliseconds
For Those About To Rock (We Salute You)	1	1	343719
Balls to the Wall	2	1	342562
Fast As a Shark	3	1	230619
Restless and Wild	3	1	252051
Princess of the Dawn	3	1	375418

WHERE …

The WHERE clause allows us to filter the returned rows by some conditions.

Example 1:

Let’s list all tracks in the album with id 4.

SELECT Name, AlbumId, GenreId, Milliseconds
FROM Track
WHERE AlbumId=4;

Name	AlbumId	GenreId	Milliseconds
Go Down	4	1	331180
Dog Eat Dog	4	1	215196
Let There Be Rock	4	1	366654
Bad Boy Boogie	4	1	267728
Problem Child	4	1	325041
Overdose	4	1	369319
Hell Ain’t A Bad Place To Be	4	1	254380
Whole Lotta Rosie	4	1	323761

Example 2:

We can also state multiple conditions. The logic for more than 1 condition is:

Let’s get the tracks composed by Jerry Cantrell whose duration is longer than 200 seconds. Note that as Jerry Cantrell is a string, we need to cover it with quotes ('')

SELECT Name, AlbumId, GenreId, Milliseconds
FROM Track
WHERE Composer='Jerry Cantrell'
  AND Milliseconds > 200000;

Name	AlbumId	GenreId	Milliseconds
Sea Of Sorrow	7	1	349831
Bleed The Freak	7	1	241946
Sunshine	7	1	284969
I Know Somethin (Bout You)	7	1	261955

Example 3:

There is the keyword NOT that helps inverse the verdict appears after it.

We now collect the tracks composed by Jerry Cantrell, however, only those that are not longer than 200 seconds.

SELECT Name, AlbumId, GenreId, Milliseconds
FROM Track
WHERE Composer='Jerry Cantrell'
  AND NOT Milliseconds > 200000;

Name	AlbumId	GenreId	Milliseconds
We Die Young	7	1	152084
Put You Down	7	1	196231

Of course, we can, instead, translate the second condition into that the duration has to be shorter than or equal to 200 seconds, as in the query below, which gives the same result.

SELECT Name, AlbumId, GenreId, Milliseconds
FROM Track
WHERE Composer='Jerry Cantrell'
  AND Milliseconds <= 200000;

ORDER BY …

When fetching the data, we may want to see the resulted rows in some specific order, in which case we should use the ORDER BY clause.

Example 1:

Get the list of employees, sorted by their birthdates.

SELECT EmployeeId, FirstName, LastName, BirthDate
FROM Employee
ORDER BY BirthDate;

EmployeeId	FirstName	LastName	BirthDate
4	Margaret	Park	1947-09-19 00:00:00
2	Nancy	Edwards	1958-12-08 00:00:00
1	Andrew	Adams	1962-02-18 00:00:00
5	Steve	Johnson	1965-03-03 00:00:00
8	Laura	Callahan	1968-01-09 00:00:00
7	Robert	King	1970-05-29 00:00:00
6	Michael	Mitchell	1973-07-01 00:00:00
3	Jane	Peacock	1973-08-29 00:00:00

Example 2:

By default, the system will order the result in increasing order (from small to large). If we want to reverse to decreasing order, just add DESC at the end of the command.

For example, let’s get the invoice list, the records with the highest total values go first.

SELECT InvoiceId, CustomerId, Total
FROM Invoice
ORDER BY Total DESC;

InvoiceId	CustomerId	Total
404	6	25.86
299	26	23.86
96	45	21.86
194	46	21.86
89	7	18.86
201	25	18.86
88	57	17.91
306	5	16.86
313	43	16.86
103	24	15.86

Note that the result is trimmed to save space (there are 412 invoices in totals).

LIMIT …

Here we describe a formal method to limit the number of rows returned by the system. Sometimes, when the database is big, our queries may result in thousands or even millions of rows, which pushes pressure on the transfer-bandwidth and memory. In those cases, put a LIMIT clause at the end of the query may help solve the issue.

Example 1:

Let’s execute the above query with a LIMIT.

SELECT InvoiceId, CustomerId, Total
FROM Invoice
ORDER BY Total DESC
LIMIT 5;

InvoiceId	CustomerId	Total
404	6	25.86
299	26	23.86
96	45	21.86
194	46	21.86
89	7	18.86

Example 2:

If we place LIMIT 1 after an ORDER BY, we will get the result with a minimum value (or maximum if we use ORDER BY ... DESC).

For instance, over the tracks whose unit price is less than 1, get the one that has the longest duration.

SELECT TrackId, Name, UnitPrice, Milliseconds
FROM Track
WHERE UnitPrice < 1
ORDER BY Milliseconds DESC
LIMIT 1;

TrackId	Name	UnitPrice	Milliseconds
1666	Dazed And Confused	0.99	1612329

Example 3:

What if we want to get some rows in the middle of the result? The accompany of OFFSET may make that feasible. When we use LIMIT n OFFSET i, we command the system to skip i first rows and return the next n rows. See how the below output is different from in example 1:

SELECT InvoiceId, CustomerId, Total
FROM Invoice
ORDER BY Total DESC
LIMIT 5 OFFSET 2;

InvoiceId	CustomerId	Total
96	45	21.86
194	46	21.86
89	7	18.86
201	25	18.86
88	57	17.91

… AS …

The alias is another name we give to a column or a table, denoted by the AS term. Note that this is just an alternative name for an existing object (column or table), we do NOT create a new object.

Example 1:

Get the list of tracks jointly composed by Angus Young, Malcolm Young, and Brian Johnson. Rename the column Milliseconds so that it is more clear, like Duration in milliseconds.

SELECT Name, Composer, Milliseconds AS 'Duration in milliseconds'
FROM Track
WHERE Composer='Angus Young, Malcolm Young, Brian Johnson';

Name	Composer	Duration in milliseconds
For Those About To Rock (We Salute You)	Angus Young, Malcolm Young, Brian Johnson	343719
Put The Finger On You	Angus Young, Malcolm Young, Brian Johnson	205662
Let’s Get It Up	Angus Young, Malcolm Young, Brian Johnson	233926
Inject The Venom	Angus Young, Malcolm Young, Brian Johnson	210834
Snowballed	Angus Young, Malcolm Young, Brian Johnson	203102
Evil Walks	Angus Young, Malcolm Young, Brian Johnson	263497
C.O.D.	Angus Young, Malcolm Young, Brian Johnson	199836
Breaking The Rules	Angus Young, Malcolm Young, Brian Johnson	263288
Night Of The Long Knives	Angus Young, Malcolm Young, Brian Johnson	205688
Spellbound	Angus Young, Malcolm Young, Brian Johnson	270863

SELECT DISTINCT … FROM …

SELECT DISTINCT is similar to SELECT except for the fact that the returned rows are non-duplicated. That says, only 1 of the duplicated rows is kept, while the others are removed.

Example 1:

Let’s compare the results taken from using SELECT and SELECT DISTINCT in the below queries.

SELECT

SELECT UnitPrice
FROM Track;

UnitPrice
0.99
0.99
0.99
…
1.99
1.99
1.99
…

There are 3503 rows in total.

SELECT DISTINCT

SELECT DISTINCT UnitPrice
FROM Track;

UnitPrice
0.99
1.99

There are 2 rows in total.

Conclusion

In summary, in this article, we have covered:

SELECT … FROM …	Query which data from which table
WHERE …	Conditions for selection
=, !=, <>, >, <, >=, <=	Comparison operators
AND, OR, XOR	Combination logic for multiple conditions
NOT	Reverse the verdict of a condition
ORDER BY … (DESC)	Sort the outputted rows
LIMIT …	The maximum number of rows returned
… AS …	Set another name (alias) for a table or column
SELECT DISTINCT … FROM …	Select only the unique rows

References:

• Wiki’s page about SQL: link
• W3schools’s SQL: link
• Sqlzoo’s tutorial: link
• An introduction to Chinook by sqlitetutorial: link