Universit de Lausanne Ecole des Hautes Etudes Commerciales (HEC) Cours de Bases de Donnes Avances
TP2 - SQL
BFSH1 - 1015 Lausanne - Switzerland - Tel. +41 21 692.3420 - [Link]@[Link] - [Link]
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
Agenda
Introduction Types de donnes Oprateurs et fonctions Langage SQL
Definition de Donnes Manipulation de Donnes Requtes
Exercice
Bases de Donnes Avances TP2
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
Introduction
SQL
Structured Query Language Un langage normalis
SQL99: ANSI X3.135-1999, ISO/IEC 9075:1999 "Database Language SQL"
Standard d'accs aux bases de donnes relationnelles
Support par les principaux systmes de bases de donnes relationnelles Portabilit (en principe, peut ncessiter quelques petites modifications)
Bases de Donnes Avances TP2
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL Types de Donnes
Types de donnes principaux (Oracle)
Chanes de caractres:
CHAR (taille) VARCHAR (taille)
Donnes brutes:
RAW BLOB (Large Object Datatype) BFILE
Nombres:
NUMBER (chiffres,decimales) INT FLOAT DOUBLE
Types personnalises (objets) Types XML Types spatiales Types Mdia
Date
DATE TIMESTAMP INTERVAL
Bases de Donnes Avances TP2
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL Oprateurs et fonctions
Quelques oprateurs et fonctions utiles
Sur nombres
+, -, *, /, POWER, SQRT ABS, MOD, EXP, LOG SIN, COS, TAN ROUND, TRUNC CONCAT, REPLACE SUBSTR, LENGHT TRIM, LTRIM, RTRIM, UPPER, LOWER ...
Comparison
>, <, =, != ANY, SOME, ALL IS NULL, LIKE, IN, EXISTS NOT, AND, OR
Sur chanes de caractres
Date
CURRENT_DATE CURRENT_TIMESTAMP
Fonction daggrgation
COUNT, SUM, MAX, MIN AVG, STDEV, VARIANCE
Sur ensembles
UNION, INTERSECT, MINUS
Et beaucoup dautres
Conversion
TO_CHAR, TO_DATE, TO_NUMBER
Bases de Donnes Avances TP2
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL 1. Langage de dfinition de donnes
Langage de dfinition de donnes (LDD / DDL)
Commandes pour crer, modifier et supprimer les lments du schma relationnel tel que relations, vues, indexes, contraintes, database CREATE | ALTER | DROP DATABASE CREATE | ALTER | DROP TABLE CREATE | ALTER | DROP VIEW CREATE | ALTER | DROP TRIGGER CREATE | ALTER | DROP INDEX CREATE | ALTER | DROP CONSTRAINT CREATE | ALTER | DROP ...
Bases de Donnes Avances TP2
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL CREATE DATABASE
CREATE DATABASE
Syntaxe: CREATE DATABASE <nom> Effets: Cre une base de donne vide et prte lemploi Efface les ventuelles donnes prsentes
ALTER DATABASE
Syntaxe: ALTER DATABASE <nom> Effets: Permet deffectuer des oprations de maintenance
DROP DATABASE <nom>
Syntaxe: DROP DATABASE <nom> Effets: Efface la base de donnes entire
Example:
CREATE DATABASE cd
Bases de Donnes Avances TP2
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL CREATE TABLE
CREATE TABLE nom_table ( nom-col type-col [ [CONSTRAINT] contrainte-col]* [CONSTRAINT contrainte-table]* );
Effets: Cre une table vide en spcifiant son nom, ses attributs et ses contraintes Lgnde: [] = optionnel, CAPITALE = mot cl, * = rptition possible (typiquement separs par des virgule)
CREATE TABLE AS requte-SQL
Effets: Permet de crer et peupler une table partir dune requte SQL
Bases de Donnes Avances TP2
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL CREATE TABLE - Exercices
Crer une table Artiste contenant un Nom (identifiant), un genre et une nationalit. Insrer un jeu de donnes et afficher la table.
Solution: CREATE TABLE Artiste ( Nom varchar(64) , Genre varchar(16) , Nationalite varchar(16) , CONSTRAINT ID_Artiste PRIMARY KEY (Nom) );
Insrer un jeux de donnes et afficher la table
Solution: INSERT INTO Artiste VALUES ('Joe Satriani','Rock','USA'); INSERT INTO Artiste VALUES ('Gordon Haskell','Jazz','GB'); SELECT * FROM Artiste;
Bases de Donnes Avances TP2
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL CREATE TABLE Contraintes
NOT NULL
Oblige lattribut avoir des valeur non nulle Contrainte sur colonne:
spcifier NOT NULL aprs la definition de lattribut
DEFAULT
Attribue une valeur par dfaut lattribut la place dune valeur nulle. Contrainte sur colonne:
spcifier DEFAULT valeur aprs la definition de lattribut
CHECK (condition)
Spcifie une condition que chaque ligne de la table doit vrifier. La condition peut porter sur plusieurs attributs et contenir des connecteurs logiques, mais ne peut pas contenir de requtes SQL, porter sur dautres lignes de la table ou dautres tables Contrainte sur colonne:
spcifier CHECK (condition) aprs la definition de lattribut (1 seul attribut)
Contrainte sur table:
spcifier en fin de requte CONSTRAINT nom CHECK (condition)
Bases de Donnes Avances TP2
10
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL CREATE TABLE Contraintes
PRIMARY KEY
Dfinit la cl primaire dune table: deux lignes ne peuvent pas avoir le mme ensemble de valeurs pour les attributs dfinis par la cl primaire. Ne permet pas de valeur nulles. Contrainte sur colonne:
spcifier PRIMARY KEY aprs la definition de lattribut (limit 1 seul attribut)
Contrainte sur table:
spcifier en fin de requte CONSTRAINT nom PRIMARY KEY (attr1, attr2,...,attrN)
UNIQUE
Dfinit une cl secondaire dune table: deux lignes ne peuvent pas avoir la mme ensemble de valeurs pour les attributs dfinis par la contrainte. Permet les valeurs nulles, mais NOT NULL peut aussi tre spcifi. Contrainte sur colonne:
spcifier UNIQUE aprs la definition de lattribut (limit 1 seul attribut)
Contrainte sur table:
spcifier en fin de requten CONSTRAINT nom UNIQUE (attr1, attr2,...,attrN)
Bases de Donnes Avances TP2
11
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL CREATE TABLE Contraintes
FOREIGN KEY
Permet de garantir lintgrit rfrentielle, cest dire que les valeurs des attributs spcifis comme cl trangre rfrencent des tuples dune autre table qui existent. Permet les valeurs nulles, mais NOT NULL peut aussi tre spcifi. Contrainte sur colonne:
spcifier REFERENCES nom-table aprs la definition de lattribut (limit 1 seul attribut)
Contrainte sur table:
Spcifier en fin de requte CONSTRAINT nom FOREIGN KEY (attr1, attr2,...,attrN) REFERENCES table [(attr1, attr2,...,attrN)] [action]
Note: rfrence par dfaut la cl primaire de la table cible, ou une cl secondaire prciser travers REFERENCES table (attr1, attr2,...,attrN) Note: action permet de spcifier une action excuter
Condition: ON DELETE : permet de spcifier l'action raliser en cas d'effacement ON UPDATE : permet de spcifier l'action raliser en cas de modification Action: SET NULL : remplace les valeurs par NULL SET DEFAULT : remplace par la valeur par dfaut si dfini, sinon NULL CASCADE : la mise jour / dstruction est rpercute RESTRICT : indique une erreur (ne permet pas laction)
Bases de Donnes Avances TP2
12
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL CREATE TABLE Contraintes Exercices
Crer une table Album avec un Titre (identifiant), une Anne (obligatoire, par dfaut 2004, comprise entre 1900 et 2100) et un Artiste (cl trangre sur table Artiste).
Solution: CREATE TABLE Album ( Titre varchar(64) PRIMARY KEY, Annee numeric(4) DEFAULT 2004 NOT NULL, Artiste varchar(64), CONSTRAINT CKAnnee CHECK (Annee BETWEEN 1900 AND 2100), CONSTRAINT REF_Album_Artiste FOREIGN KEY (Artiste) REFERENCES Artiste(Nom) );
Insrer un jeux de donnes et afficher la table
Solution: INSERT INTO Album VALUES('Surfing WTA',1987,'Joe Satriani'); INSERT INTO Album VALUES('Harry''s Bar',2002,'Gordon Haskell'); SELECT * FROM Album;
Bases de Donnes Avances TP2
13
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL CREATE TABLE Contraintes Exercices
Crer une table Chanson avec un Titre (identifiant) un Album (cl trangre rfrenant la table Album), un Numero (facultatif) et une dure. Le couple Album et Numero doit tre unique dans toute la table.
Solution: CREATE TABLE Chanson ( Titre varchar(64), Album varchar(64), Numero numeric(2) , Duree numeric(4,2) , CONSTRAINT PK_Chanson PRIMARY KEY (Titre), CONSTRAINT SID_Chanson UNIQUE (Album, Numero) );
Insrer un jeux de donnes et afficher la table
Solution: INSERT INTO Chanson VALUES('How wonderful','Harry''s Bar',1,3.07); INSERT INTO Chanson VALUES('Always with me','Surfing WTA',4,6.4); SELECT * FROM Chanson;
Bases de Donnes Avances TP2
14
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL CONTRAINTES - CREATE TRIGGER
CREATE [OR REPLACE] TRIGGER nom-trigger BEFORE|AFTER|INSTEAD OF INSERT [OR] UPDATE [OF column] [OR] DELETE ON nom-table [FOR EACH ROW] [WHEN (condition)] instructions pl/sql
Permet de spcifier un traitement complexe pour garantir une contrainte dintgrit Trait au TP suivant
Example:
CREATE TRIGGER TR_InsertAlbum AFTER INSERT ON album FOR EACH ROW DECLARE nb_artiste int; BEGIN select count(*) into nb_artiste from artiste where nom= :[Link]; if (nb_artiste < 1) then INSERT into artiste values(:[Link], :[Link], null); end if; end;
Bases de Donnes Avances TP2
15
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL CREATE VIEW
CREATE [OR REPLACE] VIEW nom_vue AS requte SQL
Permet de crer une vue, soit une table virtuelle produite par une requte SQL. La vue ne contient pas de donnes propres. Elle est construite au moment de la requte avec les donnes des tables sous-jacentes. Aprs sa cration, une vue peut tre utilise de la mme faon qu'une table. Des requtes de slection ou de manipulation peuvent y tre appliques. OR REPLACE: crase lancienne vue de mme nom si elle existait
Bases de Donnes Avances TP2
16
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL CREATE VIEW - Exercices
Exercice: Crer une table ChansonsUSA qui contient toutes les noms et dures des chansons dont les artistes sont de nationalit USA.
Solution: CREATE TABLE ChansonsUSA AS SELECT [Link], [Link], duree FROM chanson, album, artiste WHERE [Link] = [Link] AND [Link] = [Link] AND [Link] = 'USA';
Exercice: Crer une vue partir de la mme requte.
Solution: CREATE VIEW VueChansonsUSA AS SELECT [Link], [Link], duree FROM chanson, album, artiste WHERE [Link] = [Link] AND [Link] = [Link] AND [Link] = 'USA';
Exercice: Modifiez les donnes dans la table originale. Affichez table et vue et notez les diffrences entre eux.
Bases de Donnes Avances TP2
17
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL ALTER TABLE
ALTER TABLE nom-table action
action: permet de spcifier la modification de la table apporter
RENAME TO nouveau-nom-table
Permet de changer le nom de la table
RENAME COLUMN old-name TO new-name
Permet de changer le nom dune colonne
ADD ( nom-col type-col [DEFAULT valeur] [contrainte-col])
Permet dajouter une ou plusieurs colonnes ou contraintes
MODIFY ( nom-col type-col [DEFAULT valeur] [contrainte-col])
Permet de modifier une ou plusieurs colonnes ou contraintes
DROP COLUMN nom-col [CASCADE CONSTRAINTS]
Permet de modifier une ou plusieurs colonnes ou contraintes
RENAME: renommer une colonne ou une contrainte
Bases de Donnes Avances TP2
18
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL ALTER TABLE - Exercices
Modifier la table Chanson afin quelle contienne une colonne Artiste
Solution: ALTER TABLE Chanson ADD ( Artiste varchar(64) )
Modifier la table Chanson afin que numero soit obligatoire
Solution: ALTER TABLE Chanson MODIFY ( Numero NOT NULL )
Modifier la table Artiste afin que le genre soit limit Rock, Hard Rock ou Jazz
Solution: ALTER TABLE Artiste ADD ( CONSTRAINT CKGenre CHECK (Genre in ('Rock', 'Hard Rock', 'Jazz')) );
Bases de Donnes Avances TP2
19
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL DROP TABLE, DROP VIEW
DROP TABLE nom_table [CASCADE CONSTRAINTS]
Permet de supprimer une table et tout son contenu CASCADE CONSTRAINTS supprime toutes les contraintes de cl externe rfrenant cette table. Si on cherche dtruire une table dont certains attributs sont rfrencs sans spcifier CASCADE CONSTRAINT: refus
DROP VIEW nom_vue [CASCADE CONSTRAINTS]
Permet de supprimer une vue CASCADE CONSTRAINTS supprime toutes les contraintes de cl externe rfrenant cette vue: si on cherche dtruire une vue dont des attributs sont rfrencs sans spcifier cette option une erreur est gnre
Exercice: Dtruisez la table ChansonsUSA et la vue VueChansonsUSA.
Solution: DROP TABLE ChansonsUSA; DROP VIEW VueChansonsUSA;
Bases de Donnes Avances TP2
20
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL Langage de manipulation de donnes
INSERT
Insertion de tuples dans une table
UPDATE
Mise jour de tuples dans une table
DELETE
Effacer des tuples dans une table
Bases de Donnes Avances TP2
21
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL INSERT
INSERT [INTO] nom-table [(nom-colonne*)] {VALUES (valeur*) | requte | DEFAULT VALUES }
Permet dinsrer des valeurs dans une table nom-colonnes: spcifie les colonnes vises par linsertion. Peut tre omis si les valeurs concernent toutes les colonnes de la table dans le bon ordre. VALUES (valeurs): spcifie les valeurs insrer:
INSERT nom-table VALUES (valeurs*)
requte: les valeurs insrer sont donns par une requte qui doit renvoyer les mmes nombre, ordre et types de colonnes spcifis par nom-colonnes
INSERT INTO nom-table requte
DEFAULT VALUES: insre une ligne avec toutes les valeurs par dfaut
Bases de Donnes Avances TP2
22
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL INSERT - Exercices
Insrez des nouveaux artistes, albums et chansons
Solution: INSERT INTO Artiste VALUES ('Gotthard', 'Rock', 'CH' ); INSERT INTO Album VALUES('Gotthard',1992,'Gotthard'); INSERT INTO Chanson VALUES('Angel','Gotthard',8,7.25,null); INSERT INTO Chanson VALUES('Hush','Gotthard',4,2.13,null);
Crez une table Genres avec un attribut Genre (identifiant). Insrez ensuite les genres des artistes utiliss dans la base.
Solution: CREATE TABLE Genres (Genre varchar(16) PRIMARY KEY); INSERT INTO Genres SELECT DISTINCT Genre FROM Artiste;
Bases de Donnes Avances TP2
23
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL UPDATE
UPDATE nom-table SET nom-colonne = expression * [WHERE condition]
Permet de mettre jour les colonnes spcifis dune table selon une ou plusieurs expressions qui peuvent tre une constante, une expression contenant des oprateurs ou une sous-requte SQL WHERE condition permet de spcifier de mettre jour seulement les tuples qui satisfassent cette condition. Par dfault ce sont tous les tuples de toute la table qui sont mis jour
Bases de Donnes Avances TP2
24
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL UPDATE - Exercices
Mettre jour la table Chanson en mettant le nom de lartiste qui est reli la chanson
Solution: UPDATE Chanson SET Artiste = ( SELECT [Link] FROM ALBUM, ARTISTE WHERE [Link]=[Link] AND [Link]=[Link] );
Mettre jour la table Chanson en mettant la dure en secondes (il faut dabord modifier le type pour tenir compte de a)
Solution: ALTER Table Chanson modify Duree number(6,2); UPDATE Chanson set Duree = trunc(Duree) * 60 + ((Duree-trunc(Duree))*100); 25
Bases de Donnes Avances TP2
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL DELETE
DELETE FROM nom-table [WHERE condition]
Efface tous les tuples qui rpondent la condition spcifie Si aucune condition est spcifie tous les tuples sont effacs
Exercice: effacer tous les artistes suisses de la base y compris leur chansons et albums
Solution: DELETE FROM Chanson WHERE Album in ( SELECT Titre FROM Album WHERE Artiste in ( SELECT Nom FROM Artiste WHERE Nationalite='CH' ) ); DELETE FROM Album WHERE Artiste in ( SELECT Nom FROM Artiste WHERE Nationalite='CH' ); DELETE FROM Artiste WHERE Nationalite = 'CH'; 26
Bases de Donnes Avances TP2
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL langage de requte
SELECT [DISTINCT] nom-cols [AS nom-cols] FROM nom-tables [ WHERE conditions] [ GROUP BY nom-cols] [ HAVING conditions ] [ ORDER BY nom-cols [ASC|DESC] ]
Permet de rcuperer linformation contenue dans la base de donne qui satisfait les conditions de la requte DISTINCT spcifie denlever les doublons dans les rsultats nom-cols spcifie quelles colonnes retenir (projection). Le caractre * rcupre toutes les colonnes des tables prcises dans la clause FROM. AS permet de donner un nom aux colonnes cres par la requte WHERE spcifie les conditions de slection des tuples (slection), qui peuvent tre nimporte quel prdicat qui doit pouvoir tre valu vrai ou faux selon les valeurs:
Comparison une valeur (<, <=, =, >=, >, <>), v. quantifis (ALL, SOME, ANY) Comparison une fourchette ([NOT] BETWEEN), comparison partielle ([NOT] LIKE) Oprateurs logiques: AND, OR, NOT Conditions dexistence: nom-col IS [NOT] NULL, [NOT] EXISTS (sous-requte) Conditions densemble: nom-col [NOT] IN (liste-de-valeur ou sous-requte)
Bases de Donnes Avances TP2
27
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL langage de requte
SELECT [DISTINCT] nom-cols [AS nom-cols] FROM nom-tables [ WHERE conditions] [ GROUP BY nom-cols] [ HAVING conditions ] [ ORDER BY nom-cols [ASC|DESC] ]
GROUP BY permet de regrouper les donnes dans la table selon les colonnes slectionnes. Peut sutiliser avec des fonctions daggrgation: count, sum, avg, max, min,... dans la clause SELECT HAVING spcifie les conditions de retention des regroupements de lignes ORDER BY permet de trier les donnes dans la table selon les colonnes slectionnes en ordre ascendant (ASC) ou descendant (DESC) Note: les diffrents oprateurs et fonctions peuvent tre utilises dans la selection ou ou dans les conditions...
Bases de Donnes Avances TP2
28
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL oprations sur requetes
requte-sql UNION requte-sql requte-sql INTERSECT requte-sql requte-sql MINUS requte-sql
Bases de Donnes Avances TP2
29
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL langage de requte Exercices
Prparation: insrez un jeu de donnes dans la base (utiliser le script TP2 [Link]) Requte de base avec selection et projection: obtenir les noms et la nationalite des artistes qui font du Rock ou du Hard Rock
SELECT Nom, Nationalite FROM Artiste WHERE Genre = 'Rock' OR Genre ='Hard Rock'
Requte sans doublons: obtenir la liste des albums dont on a rpertori des chansons dans la base
SELECT DISTINCT Album FROM Chanson
Requte avec variable et ordonnancement: obtenir le contenu dun certain album (chanson, numero, dure) par ordre de numero
SELECT Titre, Numero, Duree FROM Chanson WHERE Album = :Album ORDER BY Numero ASC
Bases de Donnes Avances TP2
30
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL langage de requte Exercices
Requte sur plusieurs tables avec jointure: obtenir une table avec les artistes, les albums et leurs annes, en ordre dartiste et annee
SELECT [Link] AS Artiste, Annee, [Link] AS Album FROM Album, Artiste WHERE [Link]=[Link] ORDER BY Artiste, Annee SELECT [Link] AS Artiste, Annee, [Link] AS Album FROM Album JOIN Artiste ON [Link]=[Link]
Outer Joins: obtenir la liste des Artistes avec leur album, en incluant mme les artistes pour lesquels on na pas dalbum repertoris
SELECT Nom, Annee, [Link] FROM artiste LEFT JOIN album ON [Link]=[Link];
Requte avec sous-requte: obtenir la liste dartistes dont on na pas dalbum
SELECT nom from artiste where nom not in (SELECT artiste from album)
Bases de Donnes Avances TP2
31
Universit de Lausanne > Ecole des HEC > Cours de Bases de Donnes Avances > TP2
SQL langage de requte Exercices
Requte avec fonctions daggregation: obtenir la liste des albums avec le numero de chansons quils contiennent et leur dure totale
SELECT [Link], count(Duree) AS Nombre_Chansons, sum(Duree) AS Duree_Totale FROM album JOIN chanson ON [Link]=[Link] GROUP by [Link]
Requte avec fonctions daggregation et selection: obtenir la liste des artistes avec le nombre dalbum pour ceux dont on a plus dun seul album
SELECT Nom, count(titre) FROM artiste join album on artiste=nom GROUP BY Nom HAVING count(titre)>1
Bases de Donnes Avances TP2
32