TdE BD1 11 09 2023 ITA PARTE1 SOLUZIONI

Page 1

1) CREATE TABLE Componente( Codice VARCHAR(16) PRIMARY KEY , Nome VARCHAR(255) NOT NULL, Tipo ENUM('Strada','Piazza','Giardino') NOT NULL, Cap INT NOT NULL, -- VARCHAR(5) Dimensione INT NOT NULL, IDProge�sta VARCHAR(16) REFERENCES Proge�sta(ID) ON UPDATE CASCADE ON DELETE SET NULL ) CREATE TABLE Componente( Codice VARCHAR(16), Nome VARCHAR(255) NOT NULL, Tipo ENUM('Strada','Piazza','Giardino') NOT NULL, Cap INT NOT NULL, Dimensione INT NOT NULL, IDProge�sta VARCHAR(16), PRIMARY KEY(Codice), FOREIGN KEY (IDProge�sta) REFERENCES Proge�sta(ID) ON UPDATE CASCADE ON DELETE SET NULL ) 2) SELECT C1.Codice, C1.Nome FROM Componente C1 JOIN Incrocio I on C1.Codice = I.Componente1 JOIN Componente C2 on C2.Codice = I.Componente2 WHERE C1.Tipo = 'Piazza' AND C1.IdProge�sta is NULL AND C2.Tipo = 'Strada' GROUP BY C1.Codice HAVING COUNT(I.Componente2) >= 4 CREATE VIEW CodicePiazza (ID) AS ( SELECT Codice FROM Componente WHERE Tipo = ‘Piazza’ AND IdProge�sta is NULL) SELECT C1.Codice, C1.Nome FROM Componente C2 JOIN Incrocio I on C2.Codice = I.Componente2 WHERE C2.Tipo = 'Strada' AND C2.IdProge�sta is NULL AND I.Componente1 IN (SELECT ID FROM CodicePiazza) GROUP BY I.Componente1 HAVING COUNT(I.Componente2) >= 4 SELECT C1.Codice, C1.Nome FROM Componente C1 WHERE C1.Tipo = 'Strada' AND C1.IdProge�sta is NULL AND (SELECT COUNT(I.Componente2) FROM Incrocio I WHERE I.Componente1 = C1.Codice AND I.Componente2 IN (Select * FROM Componente WHERE Tipo = ‘Strada’) ) >= 4 3) SELECT C1.* FROM Componente C1 WHERE Tipo = 'Strada' AND C1.Codice IN (SELECT Componente1 FROM Incrocio WHERE Semaforo IS TRUE) AND NOT EXISTS (SELECT * FROM Incrocio I JOIN Componente C2 ON I.Componente2 = C2.Codice WHERE I.Componente1 = C1.Codice AND C2.Tipo = 'Giardino') SELECT C1.* FROM Componente C1 WHERE Tipo =…