/* 2. Premières requêtes ******************************************************/ /* Question 1. */ SELECT AVG(prix_peinture) FROM Entre_tube WHERE prix_peinture IS NOT NULL; /* Question 2. */ (SELECT num_piece FROM Cloisonette WHERE prix_decoupage IS NULL OR prix_percage IS NULL) UNION (SELECT num_piece FROM Entre_tube WHERE prix_pliage IS NULL OR prix_peinture IS NULL); /* Question 3. */ SELECT num_piece, (prix_decoupage + prix_percage) FROM Cloisonette WHERE prix_decoupage IS NOT NULL AND prix_percage IS NOT NULL; /* 3. Modification du contenu de la base **************************************/ /* Question 4. */ UPDATE Cloisonette SET adresse = 'Nouvelle adresse' WHERE nom_client = 'Thalès' AND adresse IS NOT NULL; UPDATE Entre_tube SET adresse = 'Nouvelle adresse' WHERE nom_client = 'Thalès' AND adresse IS NOT NULL; /* 4. Modification du schéma de la base ***************************************/ ALTER TABLE Cloisonette ADD COLUMN prix_peinture INT(11) NULL DEFAULT 0 AFTER prix_percage; CREATE TABLE Clanchelle (num_piece INT(11) AUTO_INCREMENT PRIMARY KEY, nom_client VARCHAR(20) NULL, adresse VARCHAR(20) NULL, prix_decoupage INT(11) NULL, prix_pliage INT(11) NULL); /* ATTENTION ! Cette requête ne _marche pas_, à cause d'un bug de MySQL. */ SELECT AVG(prix_peinture) FROM ((SELECT prix_peinture FROM Entre_tube WHERE prix_peinture IS NOT NULL) UNION (SELECT prix_peinture FROM Cloisonette WHERE prix_peinture <> 0)) AS ListePrixPeinture; (SELECT num_piece FROM Cloisonette WHERE prix_decoupage IS NULL OR prix_percage IS NULL OR prix_peinture IS NULL) UNION (SELECT num_piece FROM Entre_tube WHERE prix_pliage IS NULL OR prix_peinture IS NULL) UNION (SELECT num_piece FROM Clanchelle WHERE prix_decoupage IS NULL OR prix_pliage IS NULL); SELECT num_piece, (prix_decoupage + prix_percage + prix_peinture) FROM Cloisonette WHERE prix_decoupage IS NOT NULL AND prix_percage IS NOT NULL AND prix_peinture IS NOT NULL; /* 5. Diversification de la production ****************************************/ /* Question 6. */ SELECT AVG(prix_operation) FROM Subit, Operation WHERE code_operation = code AND nom = 'Peinture'; SELECT DISTINCT num_piece FROM Subit WHERE operation_finie = FALSE; SELECT Piece.numero, SUM(prix_operation) FROM Piece, Type, Subit WHERE Piece.numero = num_piece AND est_du_type = Type.numero AND Type.nom = 'Cloisonette' AND Piece.numero NOT IN (SELECT num_piece FROM Subit WHERE operation_finie = FALSE) GROUP BY Piece.numero;