Higher Institute of Applied Sciences and Technology of Sousse

Database Engineering

Bachelor's in Computer Science

Imen MOALLA
[Link]@[Link]

Academic year: 2020-2021

 The SQL Language
Structured Query Language

2
Presentation of SQL

SQL is a declarative language intended for the

database manipulation within the
RDBMS which addresses both the issues
database object creation
(model), data manipulation, management
of security and local data processing
It is used by the leading RDBMS on the market:
Oracle, SQL Server, MySQL, Access, DB2,…

3
Presentation of SQL

Data definition language (schema, DDL)

. Table creation (relation): CREATE TABLE
. Table modification (relation): ALTER TABLE
. Table deletion (relation): DROP TABLE
. Views: CREATE VIEW

Data manipulation language (schema,

LMD)
. Tuple insertion: INSERT
. Update of tuples: UPDATE
. Tuple deletion: DELETE

4
Introduction to SQL

Data Control Language (DCL)

. To manage the rights on the database objects (creation
of users and assignment of their rights)
. GRANT and REVOKE

Data Control Language (DCL)

. For
the management of transactions (validation or cancellation
data modifications in the database
. BEGIN and END TRANSACTION
. COMMIT and ROLLBACK

5
Creation of tables

Creating a table involves defining its name,

the columns that make it up and their types
. Syntax
To create a table, we use the instruction
next:
CREATE TABLE table_name
(Attribute_name 1 type1,
Attribute_name 2 type2,
……,
PRIMARY KEY(attribute_key_name)
FOREIGN KEY(foreign_key_attribute_name)
REFERENCES nom_table ( foreign_key_attribute );

6
Table creation
Data type
NUMBER( N ): Integer with N digits
-NUMBER(N, M): Real number with N digits in total, M after the
comma.
DATE: Full date (date and/or time)
VARCHAR( N ), VARCHAR2( N ): string of N characters
(variable length).
-CHAR( N ): String of N characters (fixed length).

7
Integrity constraints
An integrity constraint is a rule that defines the
consistency of a data or a set of data
from the BD

Constraint on a column (constraint-column)

. NULL/NOTNULL: Prohibits a column from containing a null value
. UNIQUE: Prohibits two tuples of the relation from having the same value for
the attribute.

. PRIMARY KEY: defines the attribute as the primary key

8
Integrity constraints
Constraint on a table (table-constraint)
.UNIQUE (attribute list): Prohibits two tuples from the
relations have the same values for all attributes
from the list.
.PRIMARY KEY(list of attributes): Defines the attributes of the list
like the primary key
.FOREIGN KEY (list of attributes) REFERENCES table-name
Control the referential integrity between the
attributes of the list and the table and its specified columns

9
Example of table creation
Etudiant (NumEtud, NomEtud, PrenomEtud, #NumClass)
Classe (NumClass, NomClass)
CREATE TABLE CLASS
NUMCLASSENUMBER(5)
NAMECLASSVARCHAR2(10)
PRIMARY KEY(NUMCLASSE);

CREATE TABLE STUDENT (

NumEtudNUMBER(5)PRIMARY KEY,
NOMVARCHAR2(10)
FIRSTNAME VARCHAR2(10)
NUMCLASSEVARCHAR2(10),
FOREIGN KEY(NUMCLASSE) REFERENCES CLASSE(NUMCLASSE);

10
Deletion / Modification of a table
Deletion of tables
. Syntax
To delete the content of a table as well as its schema, we
utilise l’instruction qui suit :
DROP TABLE table_name;
Note: Be careful, deleting a table leads to
loss of data it contains.
. Example
To delete the STUDENT table and its contents, we do
recourse to instruction:
DROP TABLE STUDENT;

11
Suppression / Modification of a table
Modification of tables
There are several modifications that can be made to a table.
data
. Adding attributes
Après avoir créé la base de données, des tâches de maintenance
seem to be sometimes necessary. Hence the addition of a new attribute:
ALTER TABLE table_name
ADD(attribute type);
. Example
Adding the Phone Number field to the Student table amounts to
to write
ALTER TABLE Student
ADD(Phone_num varchar(10));

12
Deletion / Modification of a table
. Modification of attributes
After creating the database, we can change the type.
of an attribute using the following statement:
ALTER TABLE table_name
MODIFY(attribute type);

. Example
Change the number of digits of the field Num_telde in the table
Student requires the use of instruction:
ALTER TABLE Student
MODIFY(Phone_num varchar(8));

13
Data manipulation
. Data addition
To add a row (tuple) to a table, we proceed
as follows:
INSERT INTO nom_table
VALUES(attribute_value1, attribute_value2,…);
. Example
Given the Student table (NumEtud, last name, first name, city). If
we want to insert the information of a new student
having the following information (1234, Ben Salah, Salah,
Djerba), it is written:
INSERT INTO STUDENT
VALUES(1234, BEN SALAH, SALAH, DJERBA) ;

14
 Data manipulation
. Data modification
To modify the value of an attribute related to one or more
to extract tuples from a table, we proceed as follows:
UPDATE table_name
SETattribut1 = valeur1, attribut2 = valeur2
[WHEREcondition];
. Example
Change the city of the student Salah Ben Salah who lives
now in Tunis, we write in this case:
UPDATE STUDENT
SETVILLE=TUNIS
WHERENumEtud=1234;

15
Data manipulation
.Data suppression
It involves deleting one or more tuples from a table.
To do this, we write:
DELETE FROM nom_table
[WHEREcondition];

. Example
Delete the student with NumEtd 1234 from the table
Student, we write:
DELETE FROM STUDENT
WHERENumEtd=1234 ;

16
 Data interrogation
. Generalities
It involves searching for one or more tuples in the database.
Syntax:
. A name of an attribute (column)
SELECT A1, …., A2 . Rjun relationship name (of table)
. FROM defines from which
FROM R1,…….,Rm tables the result is calculated
[WHERE[condition] . WHERE defines the predicates of
selection of the result
Group by<expression>; . Group by groups the lines for the
equal values in the columns
Having<expression>; mentioned
. Having selected the groups of
Order by [DESC/ASC]; lines satisfying the condition
. Order by specify if the sorting is done from
in ascending order (by default) or
decreasing 17
Data interrogation
. Projection
All the attributes of a table:
SELECT*
FROM nom_table;
.Example
Display the list of all students
SELECT*FROMETUDIANT;
Some attributes:
SELECT attribute1, attribute2,…
FROM nom_table;
. Example
Display the list of student names
SELECT NAME
FROMSTUDENT;
18
Data interrogation
. Restriction
Restrictions are expressed in SQL using the 'WHERE' predicate.
as follows:
SELECT attribute1, attribute2 FROM table_name
WHERE conditions;
A simple condition is the comparison between two
expressions or more using a logical operator (=, !=, <,
<=, >, >=)
. Example 1
Liste des étudiants qui s’appellent Ali
SELECT * FROM STUDENT
WHEREFIRSTNAME ='ALI';

19
Data interrogation
. Example 3
Liste des étudiants qui ne s’appellent pas Ali
SELECT * FROM STUDENT
WHERE FIRSTNAME != 'ALI';
. Example 4
List of students with NumEtd less than 100
SELECT * FROM STUDENT
WHERENumEtd < 100;
. Example 5
List of students with NumEtd between 100 and 200
SELECT * FROM STUDENT
WHERE NumEtd BETWEEN 100 AND 200;

20
Data interrogation
. Joints
. The join allows you to combine two or more tables in order to
extract data.
. The join is performed using a common column from the tables.
which is usually the primary key
. When a projected column is found in both tables at
join, the name of the projected column must be preceded by the name
from the table where the data will be extracted
. When a projected column is found in only one of the
tables to join, it is not necessary to indicate the name of the
table

21
Data interrogation
. Example of a simple join query:
TableA (idTableA,colonne1,colonne2)
TableB (idTableB, colonne1, colonne2, #idtableA)

SELECT TableA.column1, column 2

FROM TableA, TableB
WHERE [Link] = [Link]

. Table aliases are used to temporarily name tables.

to join to shorten the SQL statement and make it more readable.

SELECT TA.colonne1, colonne2, colonne3

FROM TableA TA, TableB TB
WHERE [Link] = [Link]
22
Data interrogation
Example of a join on the same table:

List pairs of movies from the same year.

SELECT [Link], [Link]
FROM Film f1, Film f2
WHERE [Link] <> [Link] AND [Link] = [Link]

23
Data interrogation
Example of a join between two tables:

List the cinemas showing films of

2005
SELECT [Link]
FROM Film f, Theater t
WHERE [Link] = [Link]
AND [Link] = [Link]
AND [Link] = 2005; 24
Data interrogation
Example of a join between two tables:
STUDENTS [StudNb, Name, Address]
REGISTRATION [CourseCode, Year, Time, #StudNb]

List the names of the students enrolled in the course

BD1-2019
SELECT [Link]
FROM Students S, INSCRIPTION I
WHERE ([Link]=[Link]) and ([Link] ='BD1-
2019')

25
Aggregation functions
. An aggregation function calculates a value from a
list of values in a column.
. SQL also allows grouping tuples of a relation according to
certain criteria, such as the value of another column.
. These operators are applied in a SELECT clause

SUM: sum of the values of selected tuples

AVG: average of values
MIN:valeur Minimum
MAX: Maximum value
COUNT:nombre de tuples sélectionnées

26
Aggregation functions
. Example:

STUDENTS [Number, Name, Address, Age]

INSCRIPTION [#StudNb, #CourseCode, Year, Time]
COURSE [CourseCode, CourseT itle, CreditNb, HoursNb]

Find the average age of students living in Blois

SELECT AVG(Age) FROM STUDENTS

WHERE Address= 'Blois';
How many students do we have?

SELECT count(*)
FROM STUDENTS ;
27
Aggregation functions
. Example:

STUDENTS [Number, Name, Address, Age]

INSCRIPTION [StudNb, CourseCode, Year, Time]
COURSE [CourseCode, CourseT itle, CreditNb, HoursNb]

Donner le nom et l'Agede l’étudiant le plusâgé

SELECT [Link], [Link]

FROM STUDENTS S
WHERE [Link] = (SELECT MAX ([Link]) FROM STUDENTS S2);

28