SQL basics

At this point it is legitimate to ask the following question: which language to use to formulate queries to a database? We need a query language that is a language used to create queries on databases and information systems by users. It is used to make it possible to extract information from the database, through the relevant DBMS, querying the database, and then interfacing with the user and their service requests. The universally used query language is SQL.

SQL is a language for defining and manipulating data. As a manipulation language, SQL allows to select data of interest from the base and to update its content. The queries are used both in the SQL constructs of data definition and in those of updating the database.

SQL is a declarative language: it allows you to specify what to look for without saying how. When a query is executed by the query processor, it is translated into a procedural language inside the system which allows specifying how to access the data. There are generally several translations of a SQL query into the procedural language. The task of the query optimizer is to choose the most efficient execution plan.

A SQL query is performed on a database, then on a set of tables connected to one another by the mechanism of foreign keys. The result of a query is a table. We will introduce SQL by example, that is, showing increasingly rich and complex examples of interrogation.

To understand how SQL works, we analyze the table containing the number of visitors to Italian museums in the two years, as shown in the following screenshot:

This table in named Museum; the simplest interrogation that can be written is the following:

select *
from museum

The result is the entire museum table. The first line of the query is called select statement and is used to retrieves data from a database. The * operator allows us to select all columns. The second line of the query is called from statement and serves to indicate which tables to use. The select statement and that from are mandatory in a query. Pay close attention to the case, spacing, and logical separation of the components of each query by SQL keywords.

 If we are interested only in the name and in the city of museums, we can select them in this way:

select Museum, City
from museum

The result is the following table:

 

To clarify the difference between database tables and SQL tables, we see a simple query that generates a table with two columns with the same name:

select Museum, Museum
from museum

The result is the following table:

Museum	Museum
Colosseo e Foro Romano	Colosseo e Foro Romano
Scavi di Pompei	Scavi di Pompei
Galleria degli Uffizi	Galleria degli Uffizi
Galleria dell'Accademia di Firenze	Galleria dell'Accademia di Firenze
Castel Sant'Angelo	Castel Sant'Angelo
Venaria Reale	Venaria Reale
Museo Egizio di Torino	Museo Egizio di Torino
Circuito Museale Boboli …	Circuito Museale Boboli …
Reggia di Caserta	Reggia di Caserta
Galleria Borghese	Galleria Borghese

 

A SQL table can contain duplicate rows and columns of the same name. The columns are uniquely identified by their position. This is true for the tables resulting from the queries. The database tables, that is, those that are part of the database, cannot have columns of the same name.

Furthermore, we show a simple query that generates a table with equal rows:

select city
from museum

The result is the following table:

Also, you can specify the distinct keyword after the select keyword to eliminate duplicates:

select distinct city
from museum

The result is the following table:

City

ROMA

POMPEI

FIRENZE

VENARIA R.

TORINO

CASERTA

Now let's introduce the where clause:

select Museum,City
from museum
where City = 'Rome'

The result is the following table:

If you ever want to find a particular item or group of items in your database, you need one or more conditions. Conditions are contained in the where clause. For example, to find museums that in 2016 registered a number of visitors over one million, we will have to write:

select Museum, City, Visitors_2016
from museum
where Visitors_2016 >= 1000000

The result is the following table:

Museum	City	Visitors_2016
Colosseo e Foro Romano	ROMA	6408852
Scavi di Pompei	POMPEI	3283740
Galleria degli Uffizi	FIRENZE	2010631
Galleria dell'Accademia di Firenze	FIRENZE	1461185
Castel Sant'Angelo	ROMA	1234443
Venaria Reale	VENARIA R.	1012033

 

In this series of examples we have learned to correctly formulate queries to a database using the SQL language. As we have seen, using SQL is extremely simple. In the following table the some of the most important SQL commands are listed:

Command	Brief description
SELECT	Extracts data from a database
UPDATE	Updates data in a database
DELETE	Deletes data from a database
INSERT INTO	Inserts new data into a database
CREATE DATABASE	Creates a new database
ALTER DATABASE	Modifies a database
CREATE TABLE	Creates a new table
ALTER TABLE	Modifies a table
DROP TABLE	Deletes a table
CREATE INDEX	Creates an index (search key)
DROP INDEX	Deletes an index

 

We will now address other SQL queries so we can acquire further skills.