Création d'une base de données

• Création de la base de données avec les tables qui correspondent (avec les clés primaires et étrangères)

CREATE SCHEMA video;

-- Création des tables
SET SEARCH_PATH TO video;

CREATE TABLE genres (
	codegenre VARCHAR (10) PRIMARY KEY,
	libellegenre VARCHAR(10) NOT NULL
);

CREATE TABLE pays (
	codepays VARCHAR (10) PRIMARY KEY,
	pays VARCHAR (25) NOT NULL
);

CREATE TABLE acteurs (
	id SERIAL PRIMARY KEY,
	nomacteur VARCHAR (20),
	prenomacteur VARCHAR (20),
	sexe VARCHAR (10),
	codepays VARCHAR (10) REFERENCES pays (codepays)
);

CREATE TABLE realisateurs (
	id SERIAL PRIMARY KEY,
	nomrealisateur VARCHAR (20) NOT NULL,
	prenomrealisateur VARCHAR (20) NOT NULL,
	codepays VARCHAR (10) REFERENCES pays (codepays)
);

CREATE TABLE films (
	id VARCHAR (200) PRIMARY KEY,
	titre VARCHAR (255),
	producteur VARCHAR (40),
	codepays VARCHAR (10) REFERENCES pays (codepays),
	annee INT,
	duree NUMERIC,
	resume VARCHAR (4000),
	affiche VARCHAR (200),
	note NUMERIC
);

CREATE TABLE roles (
	idfilm VARCHAR (200) REFERENCES films (id),
	idacteur SERIAL REFERENCES acteurs (id),
	role VARCHAR (10),
	PRIMARY KEY (idfilm, idacteur)
);

CREATE TABLE classificationsfilms (
	idfilm VARCHAR (200) REFERENCES films (id),
	codegenre VARCHAR (10) REFERENCES genres (codegenre),
	PRIMARY KEY (idfilm, codegenre)
);

CREATE TABLE realisations (
	idfilm VARCHAR (200) REFERENCES films (id),
	idrealisateur SERIAL REFERENCES realisateurs (id),
	PRIMARY KEY (idfilm, idrealisateur)
);

CREATE TABLE villes (
	codeville SERIAL PRIMARY KEY,
	nomville VARCHAR (20),
	codepostal INT
);

CREATE TABLE clients (
	numeroclient SERIAL PRIMARY KEY,
	nomclient VARCHAR (20),
	prenomclient VARCHAR (20),
	titre VARCHAR (50),
	adresse1 VARCHAR (30),
	adresse2 VARCHAR (30),
	codeville SERIAL REFERENCES villes (codeville),
	telephone VARCHAR (20),
	dateadhesion VARCHAR (20),
	dateexpiration VARCHAR (20)
);

CREATE TABLE supports (
	codesupport VARCHAR(3) PRIMARY KEY,
	intitule VARCHAR (20)
);

CREATE TABLE videos (
	id SERIAL PRIMARY KEY,
	idfilm VARCHAR (200) REFERENCES films (id),
	duree INT,
	codesupport VARCHAR (3) REFERENCES supports (codesupport),
	langue VARCHAR (10),
	dateachat VARCHAR (20),
	commentaires VARCHAR (20)
);

CREATE TABLE locations (
	idvideo SERIAL REFERENCES videos (id),
	numeroclient SERIAL REFERENCES clients (numeroclient),
	datelocation VARCHAR (20),
	dateretour VARCHAR (20),
	tarif NUMERIC,
	retourvalide VARCHAR (10),
	PRIMARY KEY (idvideo, numeroclient, datelocation)
);

• Insertion des données

-- PAYS
INSERT INTO pays (codepays, pays) VALUES ('BRA', 'Brazil');
INSERT INTO pays (codepays, pays) VALUES ('CHN', 'ChinA');
INSERT INTO pays (codepays, pays) VALUES ('DEU', 'Germany');
INSERT INTO pays (codepays, pays) VALUES ('ESP', 'Spain');
INSERT INTO pays (codepays, pays) VALUES ('FRA', 'France');
INSERT INTO pays (codepays, pays) VALUES ('IND', 'India');
INSERT INTO pays (codepays, pays) VALUES ('ITA', 'Italy');
INSERT INTO pays (codepays, pays) VALUES ('JPN', 'Japan');
INSERT INTO pays (codepays, pays) VALUES ('USA', 'United States of America');
INSERT INTO pays (codepays, pays) VALUES ('NZL', 'New Zealand');
INSERT INTO pays (codepays, pays) VALUES ('GBR', 'GBR');
INSERT INTO pays (codepays, pays) VALUES ('Can', 'Canada');
INSERT INTO pays (codepays, pays) VALUES ('NEW', 'New Zealand');
INSERT INTO pays (codepays, pays) VALUES ('CAN', 'Canada');

• Rechercher des éléments grâce à l'aide des LMD, LDD et des jointures

SELECT nomacteur, prenomacteur, COUNT(videos.id), SUM(tarif)
FROM acteurs
	INNER JOIN roles ON acteurs.id = roles.idacteur
	INNER JOIN films ON roles.idfilm = films.id
	INNER JOIN videos ON films.id = videos.idfilm
	INNER JOIN locations ON videos.id = locations.idvideo
GROUP BY nomacteur, prenomacteur
ORDER BY SUM(tarif) DESC
LIMIT 5;

Cette requête permet d'extraire la liste des 5 acteurs les plus rentables.

Résultat :

• Mise en place des procédures stockées et des fonctions

Procédure stockée pour calculer le périmètre d'un rectangle :

CREATE OR REPLACE FUNCTION afficher_perimetre_rectangle (longeur INT, largeur INT) RETURNS VOID
AS
$$
DECLARE
	perimetre INT = (longeur + largeur) * 2;
BEGIN
	RAISE INFO 'Longueur = %, Largeur = %, Périmètre = %', longeur, largeur, perimetre;
END
$$
LANGUAGE plpgsql;

SELECT afficher_perimetre_rectangle (10, 15);

Résultat :

Fonction pour calculer un PGCD :

CREATE OR REPLACE FUNCTION pgcd (chiffre1 INT, chiffre2 INT) RETURNS INT
AS
$$
BEGIN
WHILE chiffre1 <> chiffre2 LOOP
		IF chiffre1 > chiffre2 then
			chiffre1 = chiffre1 - chiffre2;
		ELSE
			chiffre2 = chiffre2 - chiffre1;
		END IF;	
	END LOOP;
return chiffre1;
END
$$
LANGUAGE plpgsql;

SELECT pgcd (24, 64);

Résultat :