1.
Créer un Tablespace
CREATE TABLESPACE AGENCE_VOYAGE
DATAFILE 'agence_voyage_data01.dbf'
SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
2. Créer un Utilisateur
CREATE USER AGENCE_USER IDENTIFIED BY your_password;
ALTER USER AGENCE_USER QUOTA UNLIMITED ON AGENCE_VOYAGE;
GRANT CONNECT, RESOURCE TO AGENCE_USER;
3. Créer les Tables
Connectez-vous avec l'utilisateur que vous venez de créer.
CONNECT AGENCE_USER/your_password;
-- Créer la table CLIENT
CREATE TABLE CLIENT (
NumCli NUMBER PRIMARY KEY,
Nom VARCHAR2(50),
Prenom VARCHAR2(50),
e_mail VARCHAR2(100),
NumCB VARCHAR2(20)
) TABLESPACE AGENCE_VOYAGE;
-- Insérer des informations dans la table CLIENT
INSERT INTO CLIENT (NumCli, Nom, Prenom, e_mail, NumCB) VALUES (1, 'Dupont', 'Jean',
'[Link]@[Link]', '1234-5678-9012-3456');
INSERT INTO CLIENT (NumCli, Nom, Prenom, e_mail, NumCB) VALUES (2, 'Martin', 'Sophie',
'[Link]@[Link]', '2345-6789-0123-4567');
INSERT INTO CLIENT (NumCli, Nom, Prenom, e_mail, NumCB) VALUES (3, 'Durand', 'Paul',
'[Link]@[Link]', '3456-7890-1234-5678');
INSERT INTO CLIENT (NumCli, Nom, Prenom, e_mail, NumCB) VALUES (4, 'Leroy', 'Claire',
'[Link]@[Link]', '4567-8901-2345-6789');
INSERT INTO CLIENT (NumCli, Nom, Prenom, e_mail, NumCB) VALUES (5, 'Moreau', 'Marc',
'[Link]@[Link]', '5678-9012-3456-7890');
INSERT INTO CLIENT (NumCli, Nom, Prenom, e_mail, NumCB) VALUES (6, 'Roux', 'Lucie',
'[Link]@[Link]', '6789-0123-4567-8901');
INSERT INTO CLIENT (NumCli, Nom, Prenom, e_mail, NumCB) VALUES (7, 'Simon', 'Alice',
'[Link]@[Link]', '7890-1234-5678-9012');
INSERT INTO CLIENT (NumCli, Nom, Prenom, e_mail, NumCB) VALUES (8, 'Lemoine', 'Julien',
'[Link]@[Link]', '8901-2345-6789-0123');
INSERT INTO CLIENT (NumCli, Nom, Prenom, e_mail, NumCB) VALUES (9, 'Nicolas', 'Emma',
'[Link]@[Link]', '9012-3456-7890-1234');
INSERT INTO CLIENT (NumCli, Nom, Prenom, e_mail, NumCB) VALUES (10, 'Gauthier', 'Antoine',
'[Link]@[Link]', '0123-4567-8901-2345');
-- Créer la table VOYAGE
CREATE TABLE VOYAGE (
CodeVoyage NUMBER PRIMARY KEY,
Destination VARCHAR2(100),
Duree NUMBER,
Prix NUMBER
) TABLESPACE AGENCE_VOYAGE;
-- Insérer des informations dans la table VOYAGE
INSERT INTO VOYAGE (CodeVoyage, Destination, Duree, Prix) VALUES (1, 'Paris', 7, 450000);
INSERT INTO VOYAGE (CodeVoyage, Destination, Duree, Prix) VALUES (2, 'New York', 10, 600000);
INSERT INTO VOYAGE (CodeVoyage, Destination, Duree, Prix) VALUES (3, 'Tokyo', 14, 800000);
INSERT INTO VOYAGE (CodeVoyage, Destination, Duree, Prix) VALUES (4, 'Londres', 5, 300000);
INSERT INTO VOYAGE (CodeVoyage, Destination, Duree, Prix) VALUES (5, 'Rio de Janeiro', 12, 500000);
INSERT INTO VOYAGE (CodeVoyage, Destination, Duree, Prix) VALUES (6, 'Sydney', 10, 700000);
INSERT INTO VOYAGE (CodeVoyage, Destination, Duree, Prix) VALUES (7, 'Rome', 6, 250000);
INSERT INTO VOYAGE (CodeVoyage, Destination, Duree, Prix) VALUES (8, 'Moscou', 9, 400000);
INSERT INTO VOYAGE (CodeVoyage, Destination, Duree, Prix) VALUES (9, 'Cape Town', 11, 550000);
INSERT INTO VOYAGE (CodeVoyage, Destination, Duree, Prix) VALUES (10, 'Bangkok', 15, 650000);
-- Créer la table RESERVATION
CREATE TABLE RESERVATION (
NumCli NUMBER,
CodeVoyage NUMBER,
DateRes DATE,
PRIMARY KEY (NumCli, CodeVoyage),
FOREIGN KEY (NumCli) REFERENCES CLIENT(NumCli),
FOREIGN KEY (CodeVoyage) REFERENCES VOYAGE(CodeVoyage)
) TABLESPACE AGENCE_VOYAGE;
-- Insérer des informations dans la table RESERVATION
INSERT INTO RESERVATION (NumCli, CodeVoyage, DateRes) VALUES (1, 1, SYSDATE);
INSERT INTO RESERVATION (NumCli, CodeVoyage, DateRes) VALUES (2, 2, SYSDATE);
INSERT INTO RESERVATION (NumCli, CodeVoyage, DateRes) VALUES (3, 3, SYSDATE);
INSERT INTO RESERVATION (NumCli, CodeVoyage, DateRes) VALUES (1, 4, SYSDATE);
INSERT INTO RESERVATION (NumCli, CodeVoyage, DateRes) VALUES (5, 5, SYSDATE);
INSERT INTO RESERVATION (NumCli, CodeVoyage, DateRes) VALUES (6, 6, SYSDATE);
INSERT INTO RESERVATION (NumCli, CodeVoyage, DateRes) VALUES (7, 7, SYSDATE);
INSERT INTO RESERVATION (NumCli, CodeVoyage, DateRes) VALUES (8, 8, SYSDATE);
INSERT INTO RESERVATION (NumCli, CodeVoyage, DateRes) VALUES (9, 9, SYSDATE);
INSERT INTO RESERVATION (NumCli, CodeVoyage, DateRes) VALUES (10, 10, SYSDATE);
4. Formuler les Requêtes SQL
1. Nom, prénom et e-mail des clients ayant une réservation en cours
SELECT [Link], [Link], C.e_mail
FROM CLIENT C
JOIN RESERVATION R ON [Link] = [Link];
2. Nom, prénom et e-mail des clients n’ayant aucune réservation en cours
SELECT [Link], [Link], C.e_mail
FROM CLIENT C
WHERE NOT EXISTS (
SELECT 1
FROM RESERVATION R
WHERE [Link] = [Link]
);
3. Destination et liste des clients ayant réservés pour un voyage de plus de 10 jours et coûtant
moins de 500000 FCFA
SELECT [Link], [Link], [Link]
FROM VOYAGE V
JOIN RESERVATION R ON [Link] = [Link]
JOIN CLIENT C ON [Link] = [Link]
WHERE [Link] > 10 AND [Link] < 500000;
4. Numéros de tous les clients ayant réservés sur tous les voyages proposés
SELECT DISTINCT [Link]
FROM RESERVATION R;