/*
** Requetes de création de tables, contraintes et tuples (pour MySQL / MariaDB)
** Complement du cours "conception de BD - modélisation physique/évolution"
** UCB Lyon 1 - BDW - Fabien Duchateau - 2023
** https://perso.liris.cnrs.fr/fabien.duchateau/BDW/
*/


/* modification du moteur de stockage de la table */
ALTER TABLE Series ENGINE = MyISAM;

/* modification du nom de la table */
ALTER TABLE Series RENAME TO Series_TV;

/* remise à jour des valeurs initiales */
ALTER TABLE Series_TV RENAME TO Series;
ALTER TABLE Series ENGINE = InnoDB;

/* ajout d'un attribut 'producteur' dans la table Serie  */
ALTER TABLE Series ADD producteur VARCHAR(100);
/*DESC Serie;*/
INSERT INTO Series VALUES('Plus belle la vie', NULL);
INSERT INTO Series VALUES('Man vs Wild', 'Bear Grylls');

/* modification de l'attribut 'producteur' dans la table Serie */
ALTER TABLE Series MODIFY COLUMN producteur VARCHAR(200) NOT NULL FIRST;
/*DESC Serie;*/

/* renommage de l'attribut 'producteur' en 'prod' dans la table Serie */
ALTER TABLE Series CHANGE COLUMN producteur prod VARCHAR(200) NOT NULL;
/*DESC Serie;*/

/* modification contrainte : le producteur peut etre NULL et est de type entier */
ALTER TABLE Series MODIFY prod INTEGER NULL;

/* ajout contrainte de check sur le salaire */
ALTER TABLE Jouer ADD CONSTRAINT check_salaire CHECK(salaire < 1000);

/* mise à jour du producteur à NULL (sinon l'ajout de la contrainte FK échoue) */
UPDATE Series
SET prod = NULL;

/* ajout contrainte de foreign key dans Serie */
ALTER TABLE Series ADD CONSTRAINT fk_prod FOREIGN KEY(prod) REFERENCES Actrices(numINSEE);

/* mise à jour de tuples : tous les salaires à 3000 */
UPDATE Jouer
SET salaire = 3000;

/* mise à jour de certains tuples : le salaire de l'actrice 222 à 4564 */
UPDATE Jouer
SET salaire = 4564
WHERE numINSEE = 222;

/* suppression de contraintes */
ALTER TABLE Series DROP PRIMARY KEY;
ALTER TABLE Series DROP FOREIGN KEY fk_prod;
/* ALTER TABLE Jouer DROP check_salaire; */

/* suppression d'un attribut de table */
ALTER TABLE Series DROP COLUMN prod;
/*DESC Serie;*/

/* suppression de table */

/* suppression de l'acteur 111 */
DELETE FROM Actrices
WHERE numINSEE = 111;

/* suppression des lignes de la table Jouer */
DELETE FROM Jouer;

/* création d'une vue nommée tbbt2012 */
CREATE VIEW tbbt2012 AS
SELECT *
FROM Saisons s NATURAL JOIN Episodes e
WHERE s.nomSerie = 'The Big Bang Theory' AND YEAR(s.dateLancement) = 2012;

/* utilisation de la vue */
SELECT * FROM tbbt2012;
