Scribd
Upload
16 views2 pages

TP 8 SQL

The document outlines SQL commands for creating and modifying a database named 'GestionEmprunt' which includes tables for CLIENT, AGENCE, COMPTE, and EMPRUNT. It specifies primary and foreign key constraints, as well as check constraints for data integrity. Additionally, it provides various SQL queries for data retrieval and analysis from these tables.

Uploaded by

amissanbennani
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
16 views2 pages

TP 8 SQL

The document outlines SQL commands for creating and modifying a database named 'GestionEmprunt' which includes tables for CLIENT, AGENCE, COMPTE, and EMPRUNT. It specifies primary and foreign key constraints, as well as check constraints for data integrity. Additionally, it provides various SQL queries for data retrieval and analysis from these tables.

Uploaded by

amissanbennani
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

ALTER TABLE CLIENT ADD PRIMARY KEY (Num_Client);

TP 8 SQL
1. CREATE DATABASE GestionEmprunt; ALTER TABLE COMPTE ADD CONSTRAINT FK_Num_Agence FOREIGN KEY
(Num_Agence) REFERENCES AGENCE(Num_Agence);

USE GestionEmprunt;
ALTER TABLE COMPTE
CREATE TABLE AGENCE (Num_Agence INT, Nom VARCHAR(12), Ville VARCHAR(12),
Actif IINT; ADD CONSTRAINT FK_Num_Client FOREIGN KEY (Num_Client) REFERENCES
CLIENT(Num_Client);

USE GestionEmprunt;
ALTER TABLE EMPRUNT ADD PRIMARY KEY (Num_Emprunt);
CREATE TABLE CLIENT (Num_Client INT, Nom VARCHAR(12), Ville VARCHAR(12));

ALTER TABLE EMPRUNT ADD CONSTRAINT FK_Num_Agence_EMPRUNT FOREIGN


USE GestionEmprunt; KEY (Num_Agence) REFERENCES AGENCE(Num_Agence);

CREATE TABLE COMPTE (Num_Compte INT, Num_Agence INT, Num_Client INT,


Solde FLOAT);
ALTER TABLE EMPRUNT ADD CONSTRAINT FK_Num_Client_EMPRUNT FOREIGN
KEY (Num_Client) REFERENCES CLIENT(Num_Client);

USE GestionEmprunt; 3.

CREATE TABLE EMPRUNT (Num_Emprunt INT, Num_Agence INT, Num_Client INT, ALTER TABLE CLIENT ADD CONSTRAINT CK_Ville CHECK (Ville IN ('Marrakech',
Montant FLOAT); 'Tanger', 'Rabat'));

2. 4. a.

ALTER TABLE AGENCE ADD PRIMARY KEY (Num_Agence); SELECT * FROM AGENCE;

b.

ALTER TABLE COMPTE ADD PRIMARY KEY (Num_Compte); SELECT Nom FROM CLIENT WHERE Nom LIKE 'B%E' AND LENGTH(Ville) = 5;

c.
SELECT COUNT (DISTINCT Ville) AS nombre_ville FROM CLIENT;

d.

SELECT*FROM AGENCE, COMPTE WHERE agence.Num_Agence =


compte.Num_Agence;

e.

SELECT client.Nom FROM CLIENT , compte, agence WHERE client.Num_Client =


compte.Num_Client AND compte.Num_Agence = agence.Num_Agence AND
agence.Ville = 'Marrakech';

f.

SELECT * FROM AGENCE WHERE Ville = 'Marrakech’ AND Actif = (SELECT


MIN(Actif) FROM AGENCE WHERE Ville = 'Marrakech');

g.

SELECT AVG (moyen) AS solde_moyen FROM (SELECT Num_Agence, AVG(Solde)


AS moyen FROM COMPTE GROUP BY Num_Agence) AS agences_valides WHERE
moyen >= 20000;

You might also like