Course 8: A first example SQL

Oct 2, 2021
Course 8: A first example SQL

1. A first example



This first part proposes to quickly build a first simple table, to record a few lines in it and to write a few queries to extract them. In the following sections, we will review the detailed syntax of each of the commands used here.

The goal of this part is to have a first overview of the SQL language, its main functionalities, which are also the most used.

1.1. Construction of a first table

Let’s create a table Marinsintended to receive the names of sailors from the 15th century to the present day, with their date of birth and possibly death. This table will contain the following data.

Table 1. Contents of the Sailors table

name First name year of birth Year of death
Auguin Christophe 1959
Bart Jeans 1650 1702
Cartier Jacques 1491 1557
Dumont d’Urville Jules 1790 1842
Dupleix Joseph 1697 1763
Moitessier Bernard 1925 1994
Montcalm Louis 1712 1759
Surcouf Robert 1773 1827
Tabarly Eric 1931 1998

We need four fields for this table: two text fields and two numeric fields. It is of course possible to enter the years of birth and death as dates, but we will limit ourselves in this example to considering them as integers, for the sake of simplification. We will see the problem of storing dates later.

A possible syntax for creating such a table is as follows:

Example 1. Creation of a first table

create   table Sailors (
	name   varchar (30),
	firstname   varchar (30),int 
	ddbirth   ,
	ddmort   int );

This example should not be taken for more than what it is: a first introductory example. To be clearer: this table does not include, among other things, a primary key declaration, no index, this is really not an example to follow!

There are two parts to this statement:

  • The instruction create table, immediately followed by the name of the table to be created;
  • A series of statements separated by commas, which specify the columns of the table. Each column declaration is made up of the name of the column, followed by its type. The type varchar(30)is a 30 character string type, and intan integer type. These statements are placed in parentheses.

1.2. Save some data

The recording of data in a table is done row by row. Let’s see an example right away.

Example 2. A first row insertion

insert   into Marins (name, first name, birth, ddmort)
     values ( 'Bart' ,   'Jean' , 1650, 1702);

The command insert intois immediately followed by the name of the table in which the data is inserted. Then follows, in parentheses, the list of fields and the order in which they will be specified. Then follows the keyword values, and finally the values ​​of the fields, in the order of the declaration. Note that the values ​​of character string type fields are declared between quotes. The use of quotes or quotes varies from server to server. To insert a quote in a string of characters, it must be preceded by another quote:

Example 3. Character string with quotes

insert   into Marins (surname, first name, birth, ddmort) 
     values ( 'Dumont d' 'Urville' ,   'Jules' , 1790, 1842);

You can insert data by not specifying all the fields in the table:

Example 4. Inserting a partial line

insert   into Marins (name, first name, birth) 
     values ( 'Auguin' ,   'Auguin' , 1959);

You can also insert several lines at the same time, separating the elements with commas:

Example 5. Inserting a partial line

insert   into Marins (name, first name, ddnaissance, ddmort) 
     values ( 'Bart' ,   'Jean' , 1650, 1702),
          ( 'Cartier' ,   'Jacques' , 1491, 1557);

It is also possible to insert data without specifying the name of the columns to be inserted. This way of doing things is dangerous, for many reasons, and absolutely to be avoided.

1.3. Extract data

The keyword to extract data from a table is select. The easiest way to use this command is as follows:

Example 6. A first selection of data

select last name, first name   from Sailors;

The list of fields that we want to display follows the keyword select, followed by the keyword from, then the name of the table in which we want to make the selection.

The result of this command is as follows:

------------------------------ -------------------- ----------
Bart Jean
Cartier Jacques
Dumont d'Urville Jules
Dupleix                        Joseph
Montcalm Louis
Surcouf Robert
Tabarly Eric
Moitessier Bernard
Auguin Auguin

Leave a Reply

Your email address will not be published. Required fields are marked *