1)
CREATE TABLE Chansons (
id NUMBER(10) PRIMARY KEY,
titre VARCHAR2(100),
artiste VARCHAR2(100),
annee NUMBER(4),
pays VARCHAR2(50)
);
2)
CREATE OR REPLACE TRIGGER Maj_artiste
BEFORE INSERT OR UPDATE ON Chansons
FOR EACH ROW
BEGIN
IF :NEW.pays IS NULL THEN
SELECT pays INTO :NEW.pays
FROM Artistes
WHERE artiste = :NEW.artiste;
END IF;
END;
3)
CREATE TABLE Genres (
id NUMBER(10) PRIMARY KEY,
nom VARCHAR2(50)
);
4)
CREATE OR REPLACE TRIGGER Maj_genre
BEFORE INSERT ON Chansons
FOR EACH ROW
DECLARE
genre_exist NUMBER(1);
BEGIN
SELECT COUNT(*) INTO genre_exist
FROM Genres
WHERE nom = :NEW.genre;
IF genre_exist = 0 THEN
INSERT INTO Genres (id, nom)
VALUES (Seq_Genres.nextval, :NEW.genre);
END IF;
END;
5)
CREATE TABLE Chansons_Genres (
id NUMBER(10) PRIMARY KEY,
id_chanson NUMBER(10),
id_genre NUMBER(10),
FOREIGN KEY (id_chanson) REFERENCES Chansons(id),
FOREIGN KEY (id_genre) REFERENCES Genres(id)
);
6)
CREATE OR REPLACE TRIGGER Maj_Chansons_Genres
AFTER INSERT ON Chansons_Genres
FOR EACH ROW
BEGIN
UPDATE Chansons
SET genre = :NEW.id_genre
WHERE id = :NEW.id_chanson;
END;
7)
CREATE OR REPLACE PROCEDURE Top_chansons_genre(p_genre IN VARCHAR2)
AS
BEGIN
SELECT *
FROM (
SELECT Chansons.id, Chansons.titre, Chansons.artiste, COUNT(*) AS nb_ecoutes
FROM Chansons, Chansons_Genres, Genres
WHERE Chansons.id = Chansons_Genres.id_chanson
AND Chansons_Genres.id_genre = Genres.id
AND Genres.nom = p_genre
GROUP BY Chansons.id, Chansons.titre, Chansons.artiste
ORDER BY nb_ecoutes DESC
)
WHERE ROWNUM <= 10;
END;