SQL SERVER : Création d’une base de données pour une bibliothèque avec Transact-SQL

-- Création de la base de données Bibliothèque
CREATE DATABASE Bibliothèque;
GO

-- Connexion à la base de données Bibliothèque
USE Bibliothèque;
GO

-- Création de la table Livres
CREATE TABLE Livres (
  ID_Livre INT IDENTITY(1,1) PRIMARY KEY,
  Titre VARCHAR(100) NOT NULL,
  ID_Auteur INT NOT NULL,
  Date_Publication DATE NOT NULL
);
GO

-- Création de la table Auteurs
CREATE TABLE Auteurs (
  ID_Auteur INT IDENTITY(1,1) PRIMARY KEY,
  Nom VARCHAR(50) NOT NULL,
  Prénom VARCHAR(50) NOT NULL
);
GO

-- Création de la table Abonnés
CREATE TABLE Abonnés (
  ID_Abonné INT IDENTITY(1,1) PRIMARY KEY,
  Nom VARCHAR(50) NOT NULL,
  Prénom VARCHAR(50) NOT NULL
);
GO

-- Création de la table Emprunts
CREATE TABLE Emprunts (
  ID_Emprunt INT IDENTITY(1,1) PRIMARY KEY,
  ID_Livre INT NOT NULL,
  ID_Abonné INT NOT NULL,
  Date_Emprunt DATE NOT NULL,
  Date_Retour DATE NOT NULL
);
GO

-- Insertion de données dans la table Auteurs
INSERT INTO Auteurs (ID_Auteur, Nom, Prénom)
VALUES (1, 'Doe', 'John'),
       (2, 'Smith', 'Jane'),
       (3, 'Brown', 'James');

-- Insertion de données dans la table Abonnés
INSERT INTO Abonnés (ID_Abonné, Nom, Prénom)
VALUES (1, 'Johnson', 'Emily'),
       (2, 'Miller', 'Michael'),
       (3, 'Davis', 'Sarah');

-- Insertion de données dans la table Livres
INSERT INTO Livres (ID_Livre, Titre, ID_Auteur, Date_Publication)
VALUES (1, 'Livre 1', 1, '2010-01-01'),
       (2, 'Livre 2', 2, '2011-02-01'),
       (3, 'Livre 3', 3, '2012-03-01');

-- Insertion de données dans la table Emprunts
INSERT INTO Emprunts (ID_Abonné, ID_Livre, Date_Emprunt, Date_Retour)
VALUES (1, 1, '2022-01-01', '2022-01-10'),
       (2, 2, '2022-02-01', '2022-02-10'),
       (3, 3, '2022-03-01', '2022-03-10');

SELECT Livres.ID_Livre, Livres.Titre, Auteurs.Nom, Auteurs.Prénom
FROM Livres
JOIN Auteurs ON Livres.ID_Auteur = Auteurs.ID_Auteur
WHERE Livres.Date_Publication > '2010-01-01';

Cette requête utilise la clause WHERE pour filtrer les lignes de la table Livres en ne sélectionnant que celles dont la date de publication est supérieure au 1er janvier 2010. Elle utilise également la clause JOIN pour associer les données de la table Livres à la table Auteurs en utilisant la clé étrangère ID_Auteur. Enfin, elle sélectionne les colonnes ID_Livre, Titre, Nom et Prénom pour afficher les informations sur les livres et les auteurs correspondants.

SELECT Auteurs.Nom, Auteurs.Prénom, COUNT(Livres.ID_Livre) AS Nombre_de_livres
FROM Livres
JOIN Auteurs ON Livres.ID_Auteur = Auteurs.ID_Auteur
GROUP BY Auteurs.Nom, Auteurs.Prénom;

Cette requête utilise la clause JOIN pour associer les données de la table Livres à la table Auteurs en utilisant la clé étrangère ID_Auteur. Elle utilise la clause GROUP BY pour regrouper les lignes en fonction des valeurs des colonnes Nom et Prénom de la table Auteurs. Enfin, elle utilise l’opérateur COUNT pour compter le nombre de livres associés à chaque auteur. Le résultat de la requête est une liste de noms, prénoms et nombres de livres publiés par chaque auteur.

SELECT Abonnés.Nom, Abonnés.Prénom
FROM Abonnés
JOIN Emprunts ON Abonnés.ID_Abonné = Emprunts.ID_Abonné
WHERE Emprunts.Date_Emprunt BETWEEN '2022-01-01' AND '2022-12-31'
GROUP BY Abonnés.Nom, Abonnés.Prénom
HAVING COUNT(Emprunts.ID_Emprunt) > 3;

Cette requête utilise la clause JOIN pour associer les données de la table Abonnés à la table Emprunts en utilisant la clé étrangère ID_Abonné. Elle utilise la clause WHERE pour filtrer les lignes de la table Emprunts en ne sélectionnant que les emprunts qui ont eu lieu entre le 1er janvier 2022 et le 31 décembre 2022. Elle utilise également la clause GROUP BY pour regrouper les lignes en fonction des valeurs des colonnes Nom et Prénom de la table Abonnés. Enfin, elle utilise la clause HAVING pour filtrer les groupes en ne sélectionnant que ceux qui ont plus de 3 emprunts associés. Le résultat de la requête est une liste de noms et prénoms d’abonnés qui ont emprunté plus de 3 livres au cours de l’année 2022.

SELECT Livres.ID_Livre, Livres.Titre, Livres.Date_Publication
FROM Livres
LEFT JOIN Emprunts ON Livres.ID_Livre = Emprunts.ID_Livre
WHERE Emprunts.ID_Emprunt IS NULL;

Cette requête utilise la clause JOIN pour associer les données de la table Abonnés à la table Emprunts en utilisant la clé étrangère ID_Abonné. Elle utilise la clause WHERE pour filtrer les lignes de la table Emprunts en ne sélectionnant que les emprunts qui ont eu lieu entre le 1er janvier 2022 et le 31 décembre 2022. Elle utilise également la clause GROUP BY pour regrouper les lignes en fonction des valeurs des colonnes Nom et Prénom de la table Abonnés. Enfin, elle utilise la clause HAVING pour filtrer les groupes en ne sélectionnant que ceux qui ont plus de 3 emprunts associés. Le résultat de la requête est une liste de noms et prénoms d’abonnés qui ont emprunté plus de 3 livres au cours de l’année 2022.

SELECT Abonnes.Nom, Abonnes.Prenom
FROM Abonnes
INNER JOIN (SELECT ID_Abonne, COUNT(*) AS Nombre_Emprunts
            FROM Emprunts
            WHERE YEAR(Date_Emprunt) = 2022
            GROUP BY ID_Abonne
            ORDER BY Nombre_Emprunts DESC
            LIMIT 1) AS Emprunts_Max
ON Abonnes.ID_Abonne = Emprunts_Max.ID_Abonne;

Cette requête utilise une sous-requête (INNER JOIN) pour compter le nombre d’emprunts pour chaque abonné en utilisant la condition YEAR(Date_Emprunt) = 2022 pour filtrer les emprunts effectués au cours de l’année 2022. La clause GROUP BY regroupe les lignes en fonction de l’identifiant de l’abonné (ID_Abonne) et la clause ORDER BY trie les résultats en fonction du nombre d’emprunts (Nombre_Emprunts) en ordre décroissant. La clause LIMIT 1 limite les résultats à une seule ligne, qui représente l’abonné qui a emprunté le plus de livres au cours de l’année 2022. Enfin, la requête principale utilise la jointure (INNER JOIN) pour associer les données de la sous-requête avec les données de la table Abonnes en utilisant la clé étrangère ID_Abonne. Le résultat de la requête est le nom et le prénom de l’abonné qui a emprunté le plus de livres au cours de l’année 2022.

SELECT Livres.ID_Livre, Livres.Titre
FROM Livres
WHERE Livres.ID_Auteur = 1;

Cette requête utilise la clause WHERE pour filtrer les lignes de la table Livres en fonction de l’identifiant de l’auteur (ID_Auteur) et affiche uniquement les colonnes ID_Livre et Titre des livres publiés par l’auteur avec l’identifiant 1. Le résultat de la requête est la liste des livres publiés par cet auteur.

SELECT Abonnés.Nom, Abonnés.Prénom, Livres.Titre, Emprunts.Date_Emprunt
FROM Abonnés
INNER JOIN Emprunts ON Abonnés.ID_Abonné = Emprunts.ID_Abonné
INNER JOIN Livres ON Emprunts.ID_Livre = Livres.ID_Livre;

Cette requête utilise des jointures INNER JOIN pour combiner les informations provenant des tables Abonnés, Emprunts et Livres. Les jointures permettent de faire correspondre les enregistrements de chaque table en fonction des valeurs des identifiants des abonnés (ID_Abonné), des livres (ID_Livre) et des emprunts (ID_Emprunt). Le résultat de la requête est une liste des informations sur les emprunts, incluant le nom et le prénom des abonnés, le titre du livre emprunté et la date de l’emprunt.

SELECT Abonnés.Nom, Abonnés.Prénom, Livres.Titre, Emprunts.Date_Emprunt
FROM Abonnés
INNER JOIN Emprunts ON Abonnés.ID_Abonné = Emprunts.ID_Abonné
INNER JOIN Livres ON Emprunts.ID_Livre = Livres.ID_Livre
ORDER BY Abonnés.Nom ASC, Emprunts.Date_Emprunt DESC;

Cette requête utilise les mêmes jointures INNER JOIN pour combiner les informations des tables Abonnés, Emprunts et Livres. La clause ORDER BY permet de trier les résultats de la requête. Le tri est effectué en premier lieu par le nom d’abonné en ordre alphabétique croissant (ASC), puis par la date d’emprunt en ordre décroissant (DESC).

CREATE PROCEDURE SELECT_LIVRES (@ID_AUTEUR INT = NULL)
AS
BEGIN
    SELECT L.ID_LIVRE, L.TITRE, A.NOM, A.PRENOM
    FROM LIVRES L
    JOIN AUTEURS A ON L.ID_AUTEUR = A.ID_AUTEUR
    WHERE (@ID_AUTEUR IS NULL OR L.ID_AUTEUR = @ID_AUTEUR)
END

CREATE FUNCTION FN_GET_NB_LIVRES()
RETURNS INT
AS
BEGIN
    DECLARE @NB_LIVRES INT
    SELECT @NB_LIVRES = COUNT(*) FROM LIVRES
    RETURN @NB_LIVRES
END

CREATE TRIGGER TR_VERIF_EMPRUNT
ON EMPRUNTS
AFTER INSERT
AS
BEGIN
    DECLARE @ID_LIVRE INT
    SELECT @ID_LIVRE = ID_LIVRE FROM INSERTED

    IF EXISTS (SELECT * FROM EMPRUNTS WHERE ID_LIVRE = @ID_LIVRE AND DATE_RETOUR IS NULL)
    BEGIN
        RAISERROR ('Le livre est déjà emprunté', 16, 1)
        ROLLBACK
    END
END

CREATE VIEW VW_AUTEURS_PLUS_3_LIVRES
AS
SELECT A.ID_AUTEUR, A.NOM, A.PRENOM
FROM AUTEURS A
JOIN LIVRES L ON A.ID_AUTEUR = L.ID_AUTEUR
GROUP BY A.ID_AUTEUR, A.NOM, A.PRENOM
HAVING COUNT(L.ID_LIVRE) > 3