Enumerate and Explain All the Basic Elements of an SQL Query

“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 artists.

Our first task is to get some basic information on the artworks we exhibit – specifically, the title, 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 FROM 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 artists table:

SELECT name, year_birth, year_death
FROM artists;

Here’s the output of this SQL query:

JOIN

In many cases, you’ll need to join data from several tables to get the output you want. The JOIN 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 artists 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 FROM statement and another table in the JOIN statement. We also use the ON keyword to tell the database which column values should be matched to join the tables.

Here’s the result:

To 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 WHERE 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 WHERE statement, you simply specify that the type of artwork should be ‘painting’. Thus, you get this result:

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 ORDER BY 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 ORDER BY clause and add the DESC keyword to put the results in descending order:

SELECT title, year
FROM artworks
WHERE type = ‘painting’
ORDER BY year DESC;

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 ORDER BY 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 GROUP BY clause comes into play.

With GROUP BY, 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 GROUP BY in this  article. Also, get more examples with GROUP BY here.

HAVING

Like the WHERE clause, HAVING filters a query’s output. However, WHERE filters at the record level, while HAVING 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:

Get 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:

Were 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.

Thanks for reading, and happy learning!