11th Feb 2021 7 minutes read Enumerate and Explain All the Basic Elements of an SQL Query Kateryna Koidan sql learn sql 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. artworks idtitleartist_idyeartype 111The Mona Lisa121506painting 112Jean-Baptiste Poquelin (Moliere)141787sculpture 113The Wedding Feast at Cana111563painting 114The Lacemaker131670painting 115A River141759sculpture artists idnameyear_birthyear_death 11Paolo Veronese15281588 12Leonardo da Vinci14521519 13Johannes Vermeer16321675 14Jean-Jacques Caffieri17251792 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: titleyeartype The Mona Lisa1506painting Jean-Baptiste Poquelin (Moliere)1787sculpture The Wedding Feast at Cana1563painting The Lacemaker1670painting A River1759sculpture 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: nameyear_birthyear_death Paolo Veronese15281588 Leonardo da Vinci14521519 Johannes Vermeer16321675 Jean-Jacques Caffieri17251792 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: titlename The Mona LisaLeonardo da Vinci Jean-Baptiste Poquelin (Moliere)Jean-Jacques Caffieri The Wedding Feast at CanaPaolo Veronese The LacemakerJohannes Vermeer A RiverJean-Jacques Caffieri 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: titleyear The Mona Lisa1506 The Wedding Feast at Cana1563 The 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 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; titleyear The Lacemaker1670 The Wedding Feast at Cana1563 The 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 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: typeoldest_work_from painting1506 sculpture1759 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: typeoldest_work_from painting1506 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: nameoldest_work_from Paolo Veronese1563 Johannes Vermeer1670 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! Tags: sql learn sql sql query