“What are the basic elements of an SQL query?” is a popular SQL job interview question. In this article, we review the basic syntax of an SQL query.
An SQL query, which requests information from a relational database, consists of several elements. These allow you to select specific columns from specific tables and to filter and sort this information if necessary. In this article, I’ll briefly review these elements by explaining what each element does and how to use it.
Basic Elements of an SQL Query
SELECT
The SELECT
statement is the first thing you’ll encounter when you start learning SQL. With the SELECT
statement, you choose the columns to be displayed in the output.
For example, let’s imagine we are data analysts at the Louvre. We have several tables in our database, including ones about artworks
and
SELECT name, year_birth, year_death FROM artists;0.artworksidtitleartist_idyeartype111The Mona Lisa121506painting112Jean-Baptiste Poquelin [Moliere]141787sculpture113The Wedding Feast at Cana111563painting114The Lacemaker131670painting115A River141759sculpture
artistsidnameyear_birthyear_death11Paolo Veronese1528158812Leonardo da Vinci1452151913Johannes Vermeer1632167514Jean-Jacques Caffieri17251792
Our first task is to get some basic information on the artworks we exhibit – specifically, the
SELECT name, year_birth, year_death FROM artists;1, year of creation, and the type of artwork. Here’s the query for requesting this information:
SELECT title, year, type FROM artworks;
In the SELECT
statement, we simply list the columns we want to see. Here’s the output:
You can get more examples of SELECT
in this article on writing a SELECT statement. Also, check out our SQL Basics course to start learning SQL interactively.
FROM
As you saw in our first example, when choosing the columns to be displayed, you also need to specify the table where this data is stored. This is done with the
SELECT name, year_birth, year_death FROM artists;4 keyword.
In the example above, we requested information about various works of art, which is [as expected] stored in the artworks
table.
Now, let’s get some basic information about the artists featured in our museum. To this end, we’ll request artists’ names, birth years, and death years from the
SELECT name, year_birth, year_death FROM artists;0 table:
SELECT name, year_birth, year_death FROM artists;
Here’s the output of this SQL query:
nameyear_birthyear_deathPaolo Veronese15281588Leonardo da Vinci14521519Johannes Vermeer16321675Jean-Jacques Caffieri17251792JOIN
In many cases, you’ll need to join data from several tables to get the output you want. The
SELECT name, year_birth, year_death FROM artists;7 statement allows you to get information from two or more tables in one SQL query.
For example, let’s say you want to see artists’ names along with their artwork. This information is not available in one table: you have the name of the artwork in the artworks
tables, while the name of the artist is stored in the
SELECT name, year_birth, year_death FROM artists;0 table. However, you can easily join these two pieces of information using the artist’s ID number, which is stored in both tables:
SELECT artworks.title, artists.name FROM artworks JOIN artists ON artworks.artist_id = artists.id;
We specify one table in the
SELECT name, year_birth, year_death FROM artists;4 statement and another table in the
SELECT name, year_birth, year_death FROM artists;7 statement. We also use the
SELECT artworks.title, artists.name FROM artworks JOIN artists ON artworks.artist_id = artists.id;2 keyword to tell the database which column values should be matched to join the tables.
Here’s the result:
titlenameThe Mona LisaLeonardo da VinciJean-Baptiste Poquelin [Moliere]Jean-Jacques CaffieriThe Wedding Feast at CanaPaolo VeroneseThe LacemakerJohannes VermeerA RiverJean-Jacques CaffieriTo understand the different types of joins available in SQL, refer to this introductory guide. Also, make sure to check our top 10 interview questions on SQL Joins. They are great for practicing [even if you are not preparing for a job interview].
WHERE
The
SELECT artworks.title, artists.name FROM artworks JOIN artists ON artworks.artist_id = artists.id;3 clause is used to filter the output of a query. For example, let’s say your next assignment is to list the titles of paintings available in the museum and their year of creation. You don’t want information on sculptures and other artwork types in the result set.
Here’s the SQL query to use:
SELECT title, year FROM artworks WHERE type = ‘painting’;
With the
SELECT artworks.title, artists.name FROM artworks JOIN artists ON artworks.artist_id = artists.id;3 statement, you simply specify that the type of artwork should be ‘painting’. Thus, you get this result:titleyearThe Mona Lisa1506The Wedding Feast at Cana1563The Lacemaker1670
For more details on the WHERE clause, check out this article.
ORDER BY
You often want the query output to be displayed in a specific order. In SQL, you can sort the result set with the
SELECT artworks.title, artists.name FROM artworks JOIN artists ON artworks.artist_id = artists.id;5 clause.
For example, let’s order the result of the last SQL query by the year of creation, starting with the most recent artworks. To do this, we simply specify the year column in the
SELECT artworks.title, artists.name FROM artworks JOIN artists ON artworks.artist_id = artists.id;5 clause and add the
SELECT artworks.title, artists.name FROM artworks JOIN artists ON artworks.artist_id = artists.id;7 keyword to put the results in descending order:
SELECT title, year FROM artworks WHERE type = ‘painting’ ORDER BY year DESC;titleyearThe Lacemaker1670The Wedding Feast at Cana1563The Mona Lisa1506
Learn more about ordering rows by one or more columns in this introductory article. For more advanced use cases of ORDER BY, check out this in-depth guide on the
SELECT artworks.title, artists.name FROM artworks JOIN artists ON artworks.artist_id = artists.id;5 statement.
GROUP BY
By default, all the results we obtain are oriented to records. However, in some cases we may want to calculate metrics for groups of records [e.g. the number of paintings by each artist, the oldest masterpiece for each artwork type, etc.]. Here’s where the
SELECT artworks.title, artists.name FROM artworks JOIN artists ON artworks.artist_id = artists.id;9 clause comes into play.
With
SELECT artworks.title, artists.name FROM artworks JOIN artists ON artworks.artist_id = artists.id;9, you can create groups of records [rows] and calculate metrics on each group. For example, let’s see the year of creation for the oldest masterpiece of each artwork type:
SELECT type, MIN [year] AS oldest_work_from FROM artworks GROUP BY type;
Here, we calculate the earliest [minimum] year for each type of artwork. We get the following output:
Learn more on the syntax of
SELECT artworks.title, artists.name FROM artworks JOIN artists ON artworks.artist_id = artists.id;9 in this article. Also, get more examples with
SELECT artworks.title, artists.name FROM artworks JOIN artists ON artworks.artist_id = artists.id;9 here.
HAVING
Like the
SELECT artworks.title, artists.name FROM artworks JOIN artists ON artworks.artist_id = artists.id;3 clause,
SELECT title, year FROM artworks WHERE type = ‘painting’;4 filters a query’s output. However,
SELECT artworks.title, artists.name FROM artworks JOIN artists ON artworks.artist_id = artists.id;3 filters at the record level, while
SELECT title, year FROM artworks WHERE type = ‘painting’;4 filters at the group level. Read more about the difference between HAVING and WHERE here.
Suppose that we want to get the artwork types where the oldest work is dated earlier than 1600. As this is the condition for an aggregated value, we put it in the HAVING clause:
SELECT type, MIN [year] AS oldest_work_from FROM artworks GROUP BY type HAVING MIN [year] < 1600;
In our case, only painting satisfy this condition:
typeoldest_work_frompainting1506Get more details on when and how to use HAVING in this guide.
SQL Query Syntax: Let’s Sum Up
As we’ve reviewed the basic elements of an SQL query, you could probably see that where and how to use these elements is usually straightforward. However, there are many details not covered in this overview. To help you review your knowledge of SQL syntax, we have prepared a two-page SQL Basics Cheat Sheet. Feel free to download it, print it out, and stick it to your desk.
Now, let’s try to use all basic elements in one SQL query. Let’s get the names of artists together with the year of their oldest work that we have in the museum. We also want [1] to exclude the Mona Lisa, as it’s too famous; [2] to include only artists whose oldest work was created before 1700, and [3] to order the output by the year of the oldest work, starting with the earliest.
Here’s the query to use:
The output of this query is:
nameoldest_work_fromPaolo Veronese1563Johannes Vermeer1670Were you able to follow the logic of this SQL query? If you don’t feel confident about using different query elements, be sure to check out our SQL Basics course. In 129 interactive exercises, it covers SQL’s fundamental applications, including building basic reports, working with multiple tables, creating reports with aggregate functions, and writing subqueries and complex instructions.
Would you like even more practice? LearnSQL.com has developed an SQL Practice Set with 88 interactive exercises to test your basic SQL knowledge.