Esercizio
Si consideri la realtà riguardante un nuovo negozio di videocassette. Per mantenere aggiornate le fatture, i film ed i clienti, è possibile utilizzare un sistema per la gestione di un database relazionale costituito da quattro tabelle:
la tabella chiamata FAT contiene i dati su ciascuna fattura relativa al noleggio delle videocassette;
la tabella chiamata CLI contiene i dati dei clienti che noleggiano videocassette;
la tabella chiamata FILM contiene i dati di ciascun film posseduto;
la tabella chiamata F_V contiene una riga per ogni videocassetta e il numero della fattura associata.
I campi della tabella FAT con i relativi tipi di dati sono i seguenti:
NUM numero della fattura intero
IDCLI numero che identifica cliente che noleggia una videocassetta 2 caratteri
TOT totale su tutte le videocassette noleggiate decimale ###.###
DATA data in cui è stata noleggiata la videocassetta data
REST data in cui è stata restituita la videocassetta data
I campi della tabella CLI con i relativi tipi di dati sono i seguenti:
ID numero che identifica il cliente che noleggia una videocassetta
COGNOME cognome del cliente 2 caratteri
NOME nome del cliente 12 caratteri
CITTA città del cliente 8 caratteri
PROV provincia del cliente 2 caratteri
I campi della tabella FILM con i relativi tipi di dati sono i seguenti:
NUM numero che identifica il film intero
TITOLO titolo del film 20 caratteri
GENERE genere del film 8 caratteri
STAR attore principale del film 10 caratteri
QT quantità di videocassette dello stesso film presenti intero
PREZZO prezzo della videocassetta all’acquisto decimale ###.###
I campi della tabella F_V con i relativi tipi di dati sono i seguenti:
NUM_F numero che identifica la fattura intero
NUM_V numero che identifica una videocassetta intero
| Le chiavi primarie sono le seguenti: NUM è la chiave primaria di FAT; ID è la chiave primaria di CLI; NUM è la chiave primaria di FILM; NUM_F, NUM_V è la chiave primaria di F_V. | Le chiavi esterne sono le seguenti: IDCLI è una chiave esterna di FAT; NUM_F è una chiave esterna di F_V; NUM_V è una chiave esterna di F_V. |
a) Si disegni il diagramma E/R da cui provengono le quattro tabelle.
b) Rispondere ai quesiti proposti con delle istruzioni in SQL.
| # | QUESITO | ISTRUZIONE SQL |
| | | |
| 1 | Creare la tabella FAT. | CREATE TABLE FAT ( NUM SMALLINT, IDCLI CHAR(2), TOT DECIMAL(6,3), DATA DATE, REST DATE ); |
| 2 | Creare la tabella FILM | CREATE TABLE FILM ( NUM SMALLINT, TITOLO CHAR(20), GENERE CHAR(8), STAR CHAR(10), QT SMALLINT, PREZZO DECIMAL(6,3) ); |
| 3 | Creare la tabella CLI. | CREATE TABLE CLI ( ID CHAR(2), COGNOME CHAR(12), NOME CHAR(8), CITTA CHAR(15), PROV CHAR(2) ); |
| 4 | Creare la tabella F_V. | CREATE TABLE F_V ( NUM_F SMALLINT, NUM_V SMALLINT ); |
| 5 | Creare l’indice sul campo chiave della tabella FILM. | CREATE UNIQUE INDEX INDFILM ON FILM (NUM); |
| 6 | Creare l’indice sul campo chiave della tabella FAT. | CREATE UNIQUE INDEX INDFAT ON FAT (NUM); |
| 7 | Creare l’indice sul campo chiave della tabella CLI. | CREATE UNIQUE INDEX INDCLI ON CLI (ID); |
| 8 | Creare l’indice sui campi chiave della tabella F_V. | CREATE UNIQUE INDEX INDF_V ON F_V (NUM_F, NUMV); |
| 9 | Leggere la tabella dei film, e visualizzare per tutte le righe, le colonne contenenti il titolo e la star. | SELECT TITOLO, STAR FROM FILM; |
| 10 | Leggere la tabella delle fatture, e per tutte le righe, visualizzare in numero, la data di noleggio e la data di restituzione. | SELECT NUM, DATA, REST FROM FAT; |
| 11 | Visualizzare i titoli dei film. | SELECT TITOLO FROM FILM; |
| 12 | Visualizzare il genere dei film. | SELECT GENERE FROM FILM; |
| 13 | Visualizzare la città dei clienti. | SELECT CITTA FROM CLI; |
| 14 | Visualizzare le star dei film. | SELECT STAR FROM FILM; |
| 15 | Visualizzare le star dei film, senza riportare più volte lo stesso nome. | SELECT DISTINCT STAR FROM FILM; |
| 16 | Visualizzare i diversi generi dei film, senza riportare più volte lo stesso genere. | SELECT DISTINCT GENERE FROM FILM; |
| 17 | Trovare quali film sono stati noleggiati (tabella F_V), visualizzando il numero d’ordine dei diversi film e cancellando le righe doppie. | SELECT DISTINCT NUM_F FROM F_V; |
| 18 | Visualizzare tutti i titoli, le quantità e i relativi prezzi dei film. | SELECT TITOLO, QT ,PREZZO FROM FILM; |
| 19 | Visualizzare tutti i nomi ed i cognomi dei clienti. | SELECT COGNOME, NOME FROM CLI; |
| 20 | Visualizzare tutte le informazioni relative alle fatture. | SELECT * FROM FAT; |
| 21 | Visualizzare tutte le informazioni relative ai clienti. | SELECT * FROM CLI; |
| 22 | Visualizzare tutte le informazioni relative ai film. | SELECT * FROM FILM; |
| 24 | Visualizzare tutte le informazioni contenute nella tabella F_V. | SELECT * FROM F_V; |
| 25 | Visualizzare tutte le informazioni sulle fatture del cliente con codice 01. | SELECT * FROM FAT WHERE IDCLI = ‘01’; |
| 26 | Visualizzare tutte le informazioni sui clienti che risiedono nella provincia di Varese. | SELECT * FROM CLI WHERE CITTA = ‘VA’; |
| 27 | Visualizzare i titoli dei film che non siano delle commedie. | SELECT TITOLO FROM FILM WHERE GENERE <>‘COMMEDIA’; |
| 28 | Visualizzare dalla tabella delle fatture, tutte le informazioni sui film il cui prezzo di noleggio sia superiore a £ 10.000. | SELECT * FROM FAT WHERE TOT > 10.000; |
| 29 | Visualizzare tutte le informazioni sui film che siano delle commedie. | SELECT * FROM FILM WHERE GENERE = ‘COMMEDIA’; |
| 30 | Visualizzare tutte le informazioni sulle fatture la cui data di noleggio sia maggiore dello {03/03/97}. | SELECT * FROM FAT WHERE DATA > {03/03/97}; |
| 31 | Visualizzare l’elenco delle fatture la cui data di restituzione non sia nota. | SELECT * FROM FAT WHERE DATA = { / / }; |
| 32 | Visualizzare tutte le informazioni contenute nella tabella F_V riguardanti il film 8. | SELECT * FROM F_V WHERE NUM_V = 8; |
| 33 | Visualizzare tutte le informazioni contenute nella tabella FILM ordinate secondo il PREZZO e a parità di prezzo secondo il GENERE. | SELECT * FROM FILM ORDER BY PREZZO, GENERE; |
| 34 | Visualizzare la tabella CLI ordinata in base al cognome e al nome, in ordine crescente. | SELECT * FROM CLI ORDER BY COGNOME, NOME; |
| 35 | Visualizzare la tabella CLI ordinata in base alla provincia, in ordine decrescente. | SELECT * FROM CLI ORDER BY PROV DESC; |
| 36 | Visualizzare tutte le informazioni sulle fatture ordinato secondo TOT decrescente. | SELECT * FROM FAT ORDER BY TOT DESC; |
| 37 | Visualizzare tutte le informazioni sui film, con un prezzo superiore a £ 20.000, ordinati secondo il prezzo. | SELECT * FROM FILM WHERE PREZZO > 20.000 ORDER BY PREZZO; |
| 38 | Visualizzare tutte le informazioni sui film, ordinati secondo il genere e a parità secondo la quantità di videocassette. | SELECT * FROM FILM ORDER BY GENERE, QT; |
| 39 | Visualizzare tutte le informazioni sui film, ordinati secondo la quantità di videocassette in ordine decrescente. | SELECT * FROM FILM ORDER BY QT DESC; |
| 40 | Visualizzare tutte le informazioni sui clienti, ordinati secondo la provincia di residenza e, a parità di provincia, secondo il comune. | SELECT * FROM CLI ORDER BY PROV, CITTA; |
| 41 | Visualizzare tutte le informazioni sulle fatture ordinate secondo la data di restituzione. | SELECT * FROM FAT ORDER BY REST; |
| 42 | Visualizzare tutte le informazioni sui clienti provenienti da ALBESE (CO). | SELECT * FROM CLI WHERE (PROV=’CO’) AND (CITTA=’ALBESE’); |
| 43 | Visualizzare tutte le informazioni sulle fatture il cui ammontare totale superi £ 10.000, corrispondenti ai film che non sono ancora stati restituiti cioè REST = { / / }. | SELECT * FROM FAT WHERE (TOT>10.000) AND (REST={ / / }); |
| 44 | Visualizzare l’elenco dei film classificati come ‘drammi’ con prezzo inferiore a £ 20.000. | SELECT TITOLO FROM FILM WHERE (GENERE=’DRAMMA’) AND (PREZZO<20.000); |
| 45 | Visualizzare tutte le informazioni sui film che non siano ‘COMMEDIA’. | SELECT * FROM FILM WHERE NOT(GENERE=’COMMEDIA’); |
| 46 | Visualizzare tutte le informazioni sui film ad eccezione dei film dell’ORRORE e di quelli con prezzo superiore a £ 90.000. | SELECT * FROM FILM WHERE NOT(GENERE=’ORRORE’) AND NOT(PREZZO>90.000); |
| 47 | Visualizzare tutte le informazioni sulle fatture per un ammontare complessivo compreso tra £ 12.000 e £ 30.000 . | SELECT * FROM FAT WHERE TOT (BETWEEN 12.000 AND 30.000); |
| 48 | Visualizzare tutte le informazioni sui clienti, il cui cognome è compreso fra FASSINA e VISCONTI. | SELECT * FROM CLI WHERE COGNOME (BETWEEN ‘FASSINA’ AND ‘VISCONTI’); |
| 49 | Visualizzare tutte le informazioni sulle fatture la cui data di noleggio è compresa fra il 1/1/97 e il 12/4/97. | SELECT * FROM FAT WHERE DATA (BETWEEN {01/01/97} AND {12/04/97}); |
| 50 | Visualizzare tutte le informazioni sulle fatture relative ai clienti con codice 01,04,06. | SELECT * FROM FAT WHERE IDCLI IN (‘01’,’04’,’06’); |
| 51 | Visualizzare tutte le informazioni sui clienti residenti nelle provincie di MILANO, VENEZIA,VARESE. | SELECT * FROM CLI WHERE PROV IN (‘VA’,’MI’,’VE’); |
| 52 | Visualizzare tutte le informazioni sulle fatture relative ai clienti eccetto quelli con codice 02,03,06. | SELECT * FROM FAT WHERE IDCLI NOT IN (‘02’,’03’,’06’) |
| 53 | Visualizzare tutte le informazioni sui clienti che risiedono in una città che contiene il gruppo ‘ES’ in qualsiasi posizione. | SELECT * FROM CLI WHERE CITTA LIKE ‘%ES%’; |
| 54 | Visualizzare tutte le informazioni sui clienti il cui cognome contiene il gruppo ‘IN’ in qualsiasi posizione. | SELECT * FROM CLI WHERE COGNOME LIKE ‘%IN%’; |
| 55 | Visualizzare tutte le informazioni sui clienti che risiedono in una città il cui nome inizia per ‘M’. | SELECT * FROM CLI WHERE CITTA LIKE ‘M%’; |
| 56 | Collegare le tabelle FILM e F_V secondo il numero di film. Visualizzare il titolo e tutte le informazioni della tabella F_V. | SELECT TITOLO, F_V.* FROM FILM, F_V WHERE NUM = NUM_V; |
| 57 | Collegare le tabelle FAT e F_V secondo il numero di fattura. Visualizzare tutte le informazioni della tabella risultato. | SELECT * FROM FAT, F_V WHERE NUM_F = NUM; |
| 58 | Collegare le tabelle FAT e CLI secondo il numero di identificazione del cliente (ID). Visualizzare i COGNOMI, il codice ID della tabella CLI, le colonne NUM e IDCLI della tabella FAT. Ordinare in modo crescente l’output secondo il codice ID. | SELECT COGNOME, ID, NUM, IDCLI FROM FAT, CLI WHERE ID = IDCLI ORDER BY ID; |
| 59 | Collegare le tabelle FAT e CLI. Visualizzare le informazioni contenute in FAT relative ai film noleggiati da BIANCHI ANDREA. | SELECT * FROM FAT, CLI WHERE (ID = IDCLI) AND (COGNOME=’BIANCHI’) AND (NOME=’ANDREA’); |
| 60 | Collegare le tabelle FAT e CLI. Visualizzare i COGNOMI dei clienti che non hanno restituito le videocassette. | SELECT COGNOME FROM FAT, CLI WHERE (ID = IDCLI) AND (REST={ / / }); |
| 61 | Inserire il vostro nominativo nella tabella dei clienti, completando tutte le colonne. | INSERT INTO CLI VALUES (‘11’,’ROSSI’,’UGO’,’LISSONE’,’MI’) |
| 62 | Inserire una nuova fattura nella tabella FAT completando tutte le colonne. | INSERT INTO FAT VALUES (12,’11’,7.550,{07/07/97},{ / / }); |
| 63 | Creare una nuova tabella FILMCOMM i cui campi sono NUM, TITOLO e STAR. Inserire nella tabella appena creata i film che hanno ‘COMMEDIA’ per genere. | INSERT INTO FILMCOMM (NUM,TITOLO, STAR) SELECT NUM,TITOLO, STAR FROM FILM WHERE GENERE=’COMMEDIA’; |
| 64 | Cancellare LUCA BERTINI dalla tabella dei clienti. | DELETE FROM CLI WHERE (COGNOME=’BERTINI’) AND (NOME=’LUCA’); |
| 65 | Cancellare le fatture emesse nel mese di gennaio del 1997. | DELETE FROM FAT WHERE (DATA>{01/01/97})AND (DATA<{31/01/97}); |
| 66 | Cambiare il genere ‘ORRORE’ con ‘HORROR’. | UPDATE FILM SETGENERE=’HORROR’ WHERE GENERE=’ORRORE’; |
| 67 | Aumentare il prezzo dei film appartenenti al genere ‘DRAMMA’ del 7.5 %. | UPDATE FILM SET PREZZO=PREZZO*1.075 WHERE GENERE=’DRAMMA’; |
| 68 | Visualizzare il numero dei clienti della videoteca. | SELECT COUNT (*) FROM CLI; |
| 69 | Visualizzare il numero complessivo di videocassette. | SELECT SUM(QT) FROM FILM; |
| 70 | Visualizzare il prezzo medio, minimo e massimo delle videocassette. | SELECT AVG(PREZZO), MIN(PREZZO), MAX(PREZZO) FROM FILM; |
| 71 | Visualizzare il numero dei diversi generi di videocassette | SELECT COUNT(DISTINCT GENERE) FROM FILM; |
| 72 | Visualizzare per ogni fattura il numero delle videocassette noleggiate. | SELECT COUNT(NUM_V) FROM F_V GROUP BY NUM_F; |
Una possibile estensione della tabella CLI è la seguente:
ID COGNOME NOME CITTA PROV
----------------------------------------------------------------------------------------------
01 BIANCHI ANDREA SAVONA SV
02 ZANFRINI SILVIA SEVESO MI
03 ROSSI GIORGIO COMO CO
04 FASSINA CARLO VARESE VA
05 DOSSI LAURA MILANO MI
06 VISCONTI MARIA ALBESE CO
07 RAMPINI DAVIDE GIUSSANO MI
08 BERTINI ANNA CAORLE VE
09 GRANDI MICHELE SOTTO IL MONTE BG
10 10 BERTINI LUCA SONDRIO SO
Una possibile estensione della tabella FILM è la seguente:
NUM TITOLO GENERE STAR QT PREZZO
--------------------------------------------------------------------------------------------------------------------------------
1 VIA COL VENTO DRAMMA GABLE 4 9,550
2 VENERDI' 13 ORRORE JASON 2 69,950
3 TOP GUN DRAMMA CRUISE 7 49,950
4 SPLASH COMMEDIA HANKS 3 29,950
5 LA CARICA DEI 101 COMMEDIA 3 59,950
6 BRIVIDO CALDO DRAMMA TURNER 3 19,950
7 RISKY BUSINNESS COMMEDIA CRUISE 2 44,550
8 COCOON FANTASC AMECHE 2 10,000
9 CROCODILE DUNDEE COMMEDIA HARRIS 2 69,950
10 TOOTSIE COMMEDIA HOFFMAN 1 29,950
Una possibile estensione della tabella FAT è la seguente:
NUM IDCLI TOT DATA REST
-------------------------------------------------------------------------------------------------
1 01 9,550 01/02/97 / /
2 02 13,550 12/03/97 / /
3 02 21,010 13/03/97 23/03/97
4 01 11,250 15/03/97 23/03/97
5 03 12,550 15/03/97 / /
6 02 9,750 15/03/97 22/03/97
7 04 10,350 24/04/97 01/05/97
8 04 12,550 03/05/97 / /
9 05 10,350 06/05/97 08/05/97
10 02 7,550 07/05/97 11/05/97
Una possibile estensione della tabella F_V è la seguente:
| NUM_F NUM_V --------------------------------- 1 4 1 5 2 3 3 1 3 10 4 6 4 3 4 5 4 1 5 1 5 2 7 | 7 1 7 3 7 5 8 4 8 7 9 9 9 2 10 4 10 1 10 5 |
Nessun commento:
Posta un commento