Esercizi con soluzione linguaggio SQL e HTML TracciaB1

Specifiche degli attributi

N.B. Cognome: Stringa di lunghezza variabile (massimo 25 caratteri);

N.B. L'attributo Cognome non può essere nullo;

N.B. NumComponenti: Numero intero;

N.B. Il valore di default dell'attributo NumComponenti è 3.

Tavolo

La tabella costituita dai seguenti attributi e caratteristiche:

N.B. Numero: Numero intero;

N.B. Sussiste il vincolo di chiave primaria sull'attributo Numero;

N.B. Tipologia: Stringa di lunghezza variabile (massimo 15 caratteri);

N.B. Il valore di default dell'attributo Tipologia 'Esterno';

N.B. L'attributo Tipologia non può essere nullo.

Prenotazione

La tabella costituita dai seguenti attributi e caratteristiche:

N.B. FamigliaID: Numero intero;

N.B. Sussiste il vincolo di integrità referenziale per l'attributo FamigliaID, il quale si riferisce alla tabella Famiglia;

N.B. La reazione all'operazione di cancellazione è SET NULL;

NumTavolo: Numero intero;

• l'attributo NumTavolo, N.B. Sussiste il vincolo di integrità referenziale per il quale Tavolo si riferisce alla tabella
• GiornoPrenotaz: Stringa di lunghezza fissa di 3 caratteri;
• Il valore di default dell'attributo GiornoPrenotaz 'Lun'. N.B. Possibili Soluzioni

a. CREATE TABLE Famiglia (FID INT PRIMARY KEY, Cognome VARCHAR(25) NOT NULL, NumComponenti INT DEFAULT 3)

b. CREATE TABLE Tavolo (Numero INT PRIMARY KEY, Tipologia VARCHAR(15) DEFAULT 'Esterno' NOT NULL)

c. CREATE TABLE Prenotazione (FamigliaID INT, NumTavolo INT, GiornoPrenotaz CHAR(3) DEFAULT 'Lun', FOREIGN KEY (FamigliaID) REFERENCES Famiglia(FID) ON DELETE SET NULL, FOREIGN KEY (NumTavolo) REFERENCES Tavolo(Numero))

- Basi di Dati e Sistemi Informativi C.L. in Ingegneria Gestionale - Appello 05/07/2018 Possibili Soluzioni Pagina 1 di 5

Si simuli l'esecuzione sequenziale delle seguenti istruzioni SQL su un ipotetico

Tavolo; a. <INSERT INTO Tavolo(Numero, Tipologia) VALUES (76, 'Esterno')> Contenuto delle tabelle: Tavolo: | Numero | Tipologia | |--------|-----------| | 76 | Esterno | b. <INSERT INTO Tavolo(Numero) VALUES (100)> Contenuto delle tabelle: Tavolo: | Numero | Tipologia | |--------|-----------| | 76 | Esterno | | 100 | | c. <INSERT INTO Famiglia(FID, Cognome) VALUES (23, 'Argento')> Contenuto delle tabelle: Famiglia: | FID | Cognome | |-----|---------| | 23 | Argento | d. <INSERT INTO Famiglia(FID, Cognome) VALUES (23, 'Oro')> Errore di violazione dei vincoli di integrità: Violazione del vincolo di chiave primaria. La chiave primaria (FID) deve essere univoca. Contenuto delle tabelle: Famiglia: | FID | Cognome | |-----|---------| | 23 | Argento | e. <INSERT INTO Famiglia(FID, Cognome) VALUES (26, 'Oro')> Contenuto delle tabelle: Famiglia: | FID | Cognome | |-----|---------| | 23 | Argento | | 26 | Oro | f. <INSERT INTO Prenotazione(FamigliaID, NumTavolo, GiornoPrenotaz) VALUES (23, 76, 'Mar')> Contenuto delle tabelle: Prenotazione: | FamigliaID | NumTavolo | GiornoPrenotaz | |------------|-----------|----------------| | 23 | 76 | Mar | g. <INSERT INTO Prenotazione(FamigliaID, NumTavolo, GiornoPrenotaz) VALUES (23, 100, 'Mer')> Errore di violazione dei vincoli di integrità: Violazione del vincolo di chiave esterna. La chiave esterna (FamigliaID) deve fare riferimento a una chiave primaria esistente nella tabella Famiglia. Contenuto delle tabelle: Prenotazione: | FamigliaID | NumTavolo | GiornoPrenotaz | |------------|-----------|----------------| | 23 | 76 | Mar | h. <DELETE FROM Tavolo> Contenuto delle tabelle: Tavolo: | Numero | Tipologia | |--------|-----------| | 76 | Esterno | | 100 | | Famiglia: | FID | Cognome | |-----|---------| | 23 | Argento | | 26 | Oro | Prenotazione: | FamigliaID | NumTavolo | GiornoPrenotaz | |------------|-----------|----------------| | 23 | 76 | Mar |

Famiglia WHERE Cognome LIKE 'A%'

INSERT INTO Famiglia(FID, Cognome, NumComponenti) VALUES (28, 'Bronzo', 4)

INSERT INTO Prenotazione(FamigliaID, NumTavolo, GiornoPrenotaz) VALUES (28, 100, 'Gio')

INSERT INTO Prenotazione(FamigliaID, NumTavolo, GiornoPrenotaz) VALUES (28, 107, 'Gio')

DELETE FROM Famiglia WHERE FID = 26

Possibili Soluzioni [Parziali]

Famiglia Tavolo Prenotazione

Numero Tipologia

FID Cognome NumComponenti FamigliaID NumTavolo GiornoPrenotaz

28 Bronzo 4 76 Esterno NULL

76 Mar100 Esterno NULL 100 Mer

28 100 Giod, k

Vincoli di integrità violati nelle seguenti istruzioni: Famiglia, Tavolo Prenotazione

Per gli Esercizi 3, 4, 5, 6 e 7, si consideri il contenuto delle tabelle e(le cui specifiche sono descritte in Esercizio 1), riportato rispettivamente nelle Figure 1, 2 e 3:

Famiglia Tavolo Prenotazione

TID Tipologia

FID Cognome NumComponenti FamigliaID NumTavolo GiornoPrenotaz

60 Interno

200 Rosso 5 200 60 Gio

62 Esterno

205 Verde 4 200

72 Ven63 Interno209 Viola 3 215 60 
Lun65 Interno215 Blu 2 215 62 
Mer68 Esterno260 Nero 3 215 68 
Sab72 Interno282 Arancio 4 260 65 
Lun260 72 
Sab282 60 
Mer282 72 
GioFigura 1 Figura 2 Figura 3

l'esecuzione per ciascuna di esse

Si formulino le seguenti interrogazioni in linguaggio SQL, simulando e
riportando il relativo risultato. N.B. Si utilizzi il contenuto delle tabelle riportato nelle Figure 1, 2 e 3:

'Lun')

3. Estrarre il numero di famiglie che hanno prenotato un tavolo per la giornata di lunedì (valore:

'Mer').

oppure per la giornata di mercoledì (valore:

Possibile Soluzione 1

SELECT count(DISTINCT FamigliaID)
FROM Prenotazione
WHERE GiornoPrenotaz = 'Lun' OR GiornoPrenotaz = 'Mer'

–Basi di Dati e Sistemi Informativi C.L. in Ingegneria Gestionale– –
Appello 05/07/2018 Possibili Soluzioni Pagina 2 di 5

Possibile Soluzione 2

SELECT count(DISTINCT FamigliaID)
FROM Prenotazione
WHERE GiornoPrenotaz LIKE 'Lun' OR

GiornoPrenotaz LIKE 'Mer' Possibile Soluzione 3

SELECT count(*) FROM Famiglia WHERE FID IN (SELECT FamigliaID FROM Prenotazione WHERE GiornoPrenotaz LIKE 'Lun' OR GiornoPrenotaz LIKE 'Mer')

Risultato dell'Interrogazione 34.

Estrarre esclusivamente la tipologia dei tavoli prenotati dalla famiglia, avente identificativo uguale a 260.

Possibile Soluzione SELECT DISTINCT Tipologia FROM Tavolo WHERE Numero IN (SELECT NumTavolo FROM Prenotazione WHERE FamigliaID = 260)

Risultato dell'Interrogazione Tipologia Interno con la lettera 'i' 5.

Estrarre il numero di famiglie ove il cognome non contiene ed il numero di componenti è pari a 3.

Possibile Soluzione SELECT count(*) FROM Famiglia WHERE Cognome NOT LIKE '%i%' AND NumComponenti = 3

Risultato dell'Interrogazione 1 - Basi di Dati e Sistemi Informativi C.L. in Ingegneria Gestionale - Appello 05/07/2018 Possibili Soluzioni Pagina 3 di 5