Correction
Q3
CREATE TABLE Livre (
Numero INT PRIMARY KEY,
Titre VARCHAR(100),
Auteur VARCHAR(100),
DomaineScientifique VARCHAR(100),
DateEdition DATE
);
CREATE TABLE Abonne (
Numero INT PRIMARY KEY,
Nom VARCHAR(100),
Adresse VARCHAR(255),
Telephone VARCHAR(15)
);
CREATE TABLE Agent (
Numero INT PRIMARY KEY,
Nom VARCHAR(100),
Adresse VARCHAR(255),
Telephone VARCHAR(15)
);
CREATE TABLE FicheEmprunt (
Numero INT PRIMARY KEY,
NumeroLivre INT,
DateDebut DATE,
DateRetourPrevue DATE,
DateRetourReelle DATE,
NumeroAbonne INT,
NumeroAgent INT,
Rendu BOOLEAN,
FOREIGN KEY (NumeroLivre) REFERENCES Livre(Numero),
FOREIGN KEY (NumeroAbonne) REFERENCES Abonne(Numero),
FOREIGN KEY (NumeroAgent) REFERENCES Agent(Numero)
);
Q4
INSERT INTO Livre VALUES (1, 'Livre A', 'Auteur A', 'Science', '2020-01-01');
INSERT INTO Livre VALUES (2, 'Livre B', 'Auteur B', 'Math', '2021-05-10');
INSERT INTO Abonne VALUES (33, 'Abonne X', 'Adresse X', '123456789');
INSERT INTO Abonne VALUES (34, 'Abonne Y', 'Adresse Y', '987654321');
INSERT INTO Agent VALUES (10, 'Agent M', 'Adresse M', '111222333');
INSERT INTO Agent VALUES (20, 'Agent N', 'Adresse N', '444555666');
INSERT INTO FicheEmprunt VALUES (100, 1, '2023-11-01', '2023-11-10', NULL, 33, 20, 0);
INSERT INTO FicheEmprunt VALUES (101, 2, '2023-11-05', '2023-11-15', '2023-11-14', 34, 10, 1);
Q5
R1
SELECT [Link], [Link]
FROM Agent
JOIN FicheEmprunt ON [Link] = [Link]
WHERE [Link] = 20;
R2
SELECT Nom, Adresse
FROM Abonne
WHERE Numero = 33;
R3
SELECT [Link], [Link]
FROM Livre
JOIN FicheEmprunt ON [Link] = [Link]
WHERE [Link] = 0;
R4
SELECT [Link], [Link]
FROM Abonne
JOIN FicheEmprunt ON [Link] = [Link]
WHERE [Link] = 4;
R5
SELECT COUNT(*) AS NombreAbonnes
FROM Abonne;
R6
SELECT Numero, Adresse, Telephone
FROM Agent;