What are the basic elements of a database?

Data must remain highly available since every minute of downtime can be prohibitively expensive. If you’re serving thousands to hundreds of thousands of operations per second, the cost of a minute of downtime could easily run into several hundreds of thousands of dollars. There are six features critical to ensuring high availability and safeguard against every type of failure or outage event: 1) in-memory replication; 2) multi-rack/zone/datacenter replication; 3) instant auto-failover; 4) AOF data persistence; 5) backup; and 6) multi-region/cloud replication. These features protect your data from process failures, node failures, multi-node failures, rack/zone/datacenter failures, network split events, and complete region or cloud failures.

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

titleyeartypeThe Mona Lisa1506paintingJean-Baptiste Poquelin (Moliere)1787sculptureThe Wedding Feast at Cana1563paintingThe Lacemaker1670paintingA 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

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 Caffieri17251792

JOIN

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

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:

typeoldest_work_frompainting1506sculpture1759

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_frompainting1506

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_fromPaolo Veronese1563Johannes 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.

What are the 4 elements of database?

What are three elements of a database?

What are the elements of database class 10?

What are the main elements of a database diagram?