Basi di Dati e Sistemi
Informativi
Esercitazione:
Il Linguaggio SQL
Home page del corso:
http://www.cs.unibo.it/~difelice/dbsi/
Esercizio 1
 Scrivere il codice SQL dello schema
ARTICOLI
Codice
Autore
Conferenza
Anno
Formato
Pagine
 Autore deve essere sempre definito.
 Conferenza e’ una stringa di lunghezza max 8.
 Formato e’ una stringa di lunghezza 3.
 Il numero di pagine deve essere sempre maggiore
di 0, nel caso di omissione si assume pari ad 1.
Esercizio 1
CREATE TABLE ARTICOLI (
CODICE SMALLINT PRIMARY KEY,
AUTORE VARCHAR(20) NOT NULL,
CONFERENZA CHAR(8),
FORMATO CHAR(3),
ANNO DATE,
PAGINE SMALLINT DEFAULT 1
CHECK (PAGINE>1)
);
Esercizio 1
 Scrivere il codice SQL dello schema
CONFERENZE
Nome
Data
Organizzatore
NumPartecipanti
 Vincolo di integrita’: ARTICOLO.{Conferenza,
Data}  CONFERENZA.{Nome, Data}
 Rimuovendo una conferenza da
CONFERENZE, vengono rimossi anche gli
articoli corrispondenti
Esercizio 1
CREATE TABLE CONFERENZE (
NOME VARCHAR(8),
DATA DATE,
ORGANIZZATORE VARCHAR(20),
NUMPARTECIPANTI INTEGER CHECK
(NUMPARTECIPANTI>0),
PRIMARY KEY(NOME,DATA)
);
Esercizio 1
CREATE TABLE ARTICOLI (
CODICE SMALLINT PRIMARY KEY,
AUTORE VARCHAR(20) NOT NULL,
CONFERENZA CHAR(8),
FORMATO CHAR(3),
DATA DATE,
PAGINE SMALLINT DEFAULT 1
CHECK (PAGINE>1)
FOREIGN KEY (CONFERENZA,DATA)
REFERENCES CONFERENZE(NOME, DATA)
ON DELETE CASCADE
);
Esercizio 1
 Scrivere il codice SQL dello schema
PARTECIPANTE
NomeConf
DataConf
Nome
Cognome
Ruolo
 Vincolo di integrita’:
PARTECIPANTE.{NomeConf, DataConf} 
CONFERENZA.{Nome, Data}
 Ogni aggiornamento di {Nome, Data} nella
relazione CONFERENZA viene propagato
anche alla relazione PARTECIPANTE.
Esercizio 1
CREATE TABLE PARTECIPANTE (
NOMECONF CHAR(8),
DATACONF DATE,
NOME VARCHAR(20),
COGNOME VARCHAR(30),
RUOLO CHARACTER(3),
PRIMARY KEY (NOMECONF,DATACONF,
NOME, COGNOME)
FOREIGN KEY (NOMECONF,DATACONF)
REFERENCES CONFERENZE(NOME, DATA)
ON UPDATE CASCADE
);
Esercizio 2
Dato il seguente schema:
Atleta(Codice, Nome, Cognome, DataNascita, Societa’)
Gara(CodiceGara, Disciplina, Data, CodiceVincitore)
Partecipazione(CodiceGara, CodiceAtleta)
Societa’(Nome, Sede, Anno, NumeroTrofei)
Scrivere la query SQL che determina:
1. Le righe della tabella ATLETA che si riferiscono ad atleti
il cui nome inizia per “M” oppure non e’ specificato.
Esercizio 2
Dato il seguente schema:
Atleta(Codice, Nome, Cognome, DataNascita, Societa’)
Gara(CodiceGara, Disciplina, Data, CodiceVincitore)
Partecipazione(CodiceGara, CodiceAtleta)
Societa’(Nome, Sede, Anno, NumeroTrofei)
Scrivere la query SQL che determina:
2. Tutti i codici delle gare di Atletica cui partecipano atleti
della societa’ “Borgorosso”.
Esercizio 2
Dato il seguente schema:
Atleta(Codice, Nome, Cognome, DataNascita, Societa’)
Gara(CodiceGara, Disciplina, Data, CodiceVincitore)
Partecipazione(CodiceGara, CodiceAtleta)
Societa’(Nome, Sede, Anno, NumeroTrofei)
Scrivere la query SQL che determina:
3. Tutti i nomi/cognomi degli atleti che partecipano ad
almeno due gare.
Esercizio 2
Dato il seguente schema:
Atleta(Codice, Nome, Cognome, DataNascita, Societa’)
Gara(CodiceGara, Disciplina, Data, CodiceVincitore)
Partecipazione(CodiceGara, CodiceAtleta)
Societa’(Nome, Sede, Anno, NumeroTrofei)
Scrivere la query SQL che determina:
4. Il numero totale di Discipline cui ha partecipato l’atleta
234 nella Gara identificata dal codice 12.
Esercizio 2
Dato il seguente schema:
Atleta(Codice, Nome, Cognome, DataNascita, Societa’)
Gara(CodiceGara, Disciplina, Data, CodiceVincitore)
Partecipazione(CodiceGara, CodiceAtleta)
Societa’(Nome, Sede, Anno, NumeroTrofei)
Scrivere la query SQL che determina:
5. Per ogni societa’, il numero totale di gare vinte.
Esercizio 2
Dato il seguente schema:
Atleta(Codice, Nome, Cognome, DataNascita, Societa’)
Gara(CodiceGara, Disciplina, Data, CodiceVincitore)
Partecipazione(CodiceGara, CodiceAtleta)
Societa’(Nome, Sede, Anno, NumeroTrofei, Presidente)
Scrivere la query SQL che determina:
6. Il nome dei presidenti di societa’ che hanno vinto piu’ di
4 gare svolte in data 10/11/2012.
Esercizio 3
Dato il seguente schema:
Atleta(Codice, Nome, Cognome, DataNascita, Societa’)
Allenatore(Codice, Nome, Cognome, Profilo, Societa’)
Preparazione(CodiceAtleta, CodiceAllenatore)
Societa’(Nome, Sede, Anno, NumeroTrofei, Presidente)
Sportivi(Nome, Cognome, Societa’)
Scrivere la query SQL che:
1. Popola la tabella Sportivi(Nome, Cognome, Societa’)
come insieme di tutti gli atleti ed allenatori presenti nello
schema.
Esercizio 3
Dato il seguente schema:
Atleta(Codice, Nome, Cognome, DataNascita, Societa’)
Allenatore(Codice, Nome, Cognome, Profilo, Societa’)
Preparazione(CodiceAtleta, CodiceAllenatore)
Societa’(Nome, Sede, Anno, NumeroTrofei, Presidente)
Scrivere la query SQL che:
2. Aggiorna i dati degli Atleti nati in data 10/11/2012,
settando il nuovo valore della societa’ a “Polisportiva
Rossi”.
Esercizio 3
Dato il seguente schema:
Atleta(Codice, Nome, Cognome, DataNascita, Societa’)
Allenatore(Codice, Nome, Cognome, Profilo, Societa’)
Preparazione(CodiceAtleta, CodiceAllenatore)
Societa’(Nome, Sede, Anno, NumeroTrofei, Presidente)
Scrivere la query SQL che:
3. Rimuove tutti gli allenatori la cui societa’ di appartenenza
non e’ specificata.
Esercizio 4
Dato il seguente schema:
Persona(Nome, DataNascita, CittaNascita, CodiceFiscale)
Discendenza(Figlio, Padre)
Matrimonio (Marito, Moglie, Citta’, Data)
Scrivere la query SQL che determina:
1. Il numero di persone nate a Bologna nell’anno 1985.
Esercizio 4
Dato il seguente schema:
Persona(Nome, AnnoNascita, CittaNascita, CodiceFiscale)
Discendenza(Figlio, Padre)
Matrimonio (Marito, Moglie, Citta’, Anno)
Scrivere la query SQL che determina:
2. Il totale di matrimoni svolti ogni anno a Bologna.
Esercizio 4
Dato il seguente schema:
Persona(Nome, AnnoNascita, CittaNascita, CodiceFiscale)
Discendenza(Figlio, Padre)
Matrimonio (Marito, Moglie, Citta’, Anno)
Scrivere la query SQL che determina:
3. Il numero di persone che si chiamano Michele e non
hanno figli.
Esercizio 5
Dato il seguente schema:
FILM (CodFilm, Titolo, AnnoProduzione, Nazionalità,
Regista, Genere)
PROIEZIONI (CodProiezione, CodFilm, CodSala, Incasso,
DataProiezione)
SALE (CodSala, Posti, Nome, Città)
Scrivere la query SQL che determina:
1. Per ogni citta’, il numero di sale con piu’ di 60 posti.
Esercizio 5
Dato il seguente schema:
FILM (CodFilm, Titolo, AnnoProduzione, Nazionalità,
Regista, Genere)
PROIEZIONI (CodProiezione, CodFilm, CodSala, Incasso,
DataProiezione)
SALE (CodSala, Posti, Nome, Città)
Scrivere la query SQL che determina:
2. Il numero totale dei posti dei cinema di Bologna.
Esercizio 5
Dato il seguente schema:
FILM (CodFilm, Titolo, AnnoProduzione, Nazionalità,
Regista, Genere)
PROIEZIONI (CodProiezione, CodFilm, CodSala, Incasso,
DataProiezione)
SALE (CodSala, Posti, Nome, Città)
Scrivere la query SQL che determina:
3. Per ogni regista, la somma totale degli incassi totalizzati
dai suoi film.
Esercizio 5
Dato il seguente schema:
FILM (CodFilm, Titolo, AnnoProduzione, Nazionalità,
Regista, Genere)
PROIEZIONI (CodProiezione, CodFilm, CodSala, Incasso,
DataProiezione)
SALE (CodSala, Posti, Nome, Città)
Scrivere la query SQL che determina:
4. Per ogni citta’, gli incassi di film Italiani proietattati in
data 10/11/2012.
Esercizio 5
Dato il seguente schema:
AEROPORTO(Citta’, Nazione, NumPiste)
VOLO(IdVolo, GiornoSett, CittaPart, CittaArr, TipoAereo,
OraPartenza, OraArrivo)
AEREO(TipoAereo, NumPasseggeri, QtaMerci)
Scrivere la query SQL che determina:
1. Le citta’ con un aeroporto di cui non e’ noto il numero di
piste.
Esercizio 5
Dato il seguente schema:
AEROPORTO(Citta’, Nazione, NumPiste)
VOLO(IdVolo, GiornoSett, CittaPart, CittaArr, TipoAereo,
OraPartenza, OraArrivo)
AEREO(TipoAereo, NumPasseggeri, QtaMerci)
Scrivere la query SQL che determina:
2. Le nazioni da cui parte ed arriva il volo con codice
AZ1343.
Esercizio 5
Dato il seguente schema:
AEROPORTO(Citta’, Nazione, NumPiste)
VOLO(IdVolo, GiornoSett, CittaPart, CittaArr, TipoAereo,
OraPartenza, OraArrivo)
AEREO(TipoAereo, NumPasseggeri, QtaMerci)
Scrivere la query SQL che determina:
3. I tipi di aereo usati nei voli che partono da Torino.
Esercizio 5
Dato il seguente schema:
AEROPORTO(Citta’, Nazione, NumPiste)
VOLO(IdVolo, GiornoSett, CittaPart, CittaArr, TipoAereo,
OraPartenza, OraArrivo)
AEREO(TipoAereo, NumPasseggeri, QtaMerci)
Scrivere la query SQL che determina:
4. Il numero di voli internazionali che partono da Napoli.
Esercizio 5
Dato il seguente schema:
AEROPORTO(Citta’, Nazione, NumPiste)
VOLO(IdVolo, GiornoSett, CittaPart, CittaArr, TipoAereo,
OraPartenza, OraArrivo)
AEREO(TipoAereo, NumPasseggeri, QtaMerci)
Scrivere la query SQL che determina:
5. La quantita’ totale di merci trasportata da aerei che
partono ogni giovedi’ da Napoli e diretti in aeroporti
italiani.
Esercizio 5
Dato il seguente schema:
AEROPORTO(Citta’, Nazione, NumPiste)
VOLO(IdVolo, GiornoSett, CittaPart, CittaArr, TipoAereo,
OraPartenza, OraArrivo)
AEREO(TipoAereo, NumPasseggeri, QtaMerci)
Scrivere la query SQL che determina:
6. Le citta’ francesi da cui partono piu’ di venti voli alla
settimana diretti per l’Italia.
Esercizio 5
Dato il seguente schema:
AEROPORTO(Citta’, Nazione, NumPiste)
VOLO(IdVolo, GiornoSett, CittaPart, CittaArr, TipoAereo,
OraPartenza, OraArrivo)
AEREO(TipoAereo, NumPasseggeri, QtaMerci)
Scrivere la query SQL che determina:
6. Le citta’ servite dall’aereo con numero minimo di
passeggeri.
Esercizio 5
Dato il seguente schema:
AEROPORTO(Citta’, Nazione, NumPiste)
VOLO(IdVolo, GiornoSett, CittaPart, CittaArr, TipoAereo,
OraPartenza, OraArrivo)
AEREO(TipoAereo, NumPasseggeri, QtaMerci)
Scrivere la query SQL che determina:
7. Il massimo numero di passeggeri che possono arrivare
dalla Francia in Italia il giovedi’.
Esercizio 6
Dato il seguente schema:
NEGOZI(IdNegozio, Nome, Citta)
PRODOTTI(CodProdotto, NomeProdotto, Marca)
LISTINO(Negozio, CodProdotto, Prezzo)
FORNITORE(Codice, Nome, Cognome, Sede)
RIFORNIMENTO(CodiceFornitore,Negozio)
Scrivere la query SQL che determina:
1. L’id dei negozi che vendono il prodotto con codice 123
ad un prezzo superiore alla media (calcolata sui listini di
tutti i negozi).
Esercizio 6
Dato il seguente schema:
NEGOZI(IdNegozio, Nome, Citta)
PRODOTTI(CodProdotto, NomeProdotto, Marca)
LISTINO(Negozio, CodProdotto, Prezzo)
FORNITORE(Codice, Nome, Cognome, Sede)
RIFORNIMENTO(CodiceFornitore,Negozio)
Scrivere la query SQL che determina:
2. Il codice dei prodotti venduti in una sola citta’.
Esercizio 6
Dato il seguente schema:
NEGOZI(IdNegozio, Nome, Citta)
PRODOTTI(CodProdotto, NomeProdotto, Marca)
LISTINO(Negozio, CodProdotto, Prezzo)
FORNITORE(Codice, Nome, Cognome, Sede)
RIFORNIMENTO(CodiceFornitore,Negozio)
Scrivere la query SQL che determina:
3. La citta’ in cui viene venduto al prezzo + basso il
prodotto con codice 123.
Esercizio 6
Dato il seguente schema:
NEGOZI(IdNegozio, Nome, Citta)
PRODOTTI(CodProdotto, NomeProdotto, Marca)
LISTINO(Negozio, CodProdotto, Prezzo)
FORNITORE(Codice, Nome, Cognome, Sede)
RIFORNIMENTO(CodiceFornitore,Negozio)
Scrivere la query SQL che determina:
4. Nome/Cognome dei fornitori che NON riforniscono
alcun negozio di Bologna.
Esercizio 7
Dato il seguente schema:
STADIO(Nome, Citta, Capienza)
INCONTRO(NomeStadio, Data, Ora, Squadra1, Squadra2)
NAZIONALE(Nazione, Continente, Livello)
Scrivere la query SQL che determina:
1. La citta/le citta in cui si trova lo stadio in cui la squadra
inglese gioca il maggior numero di partite.
Esercizio 7
Dato il seguente schema:
STADIO(Nome, Citta, Capienza)
INCONTRO(NomeStadio, Data, Ora, Squadra1, Squadra2)
NAZIONALE(Nazione, Continente, Livello)
Scrivere la query SQL che determina:
2. I nomi degli stadi in cui non gioca nessuna squadra
europea.
Esercizio 7
Dato il seguente schema:
STADIO(Nome, Citta, Capienza)
INCONTRO(NomeStadio, Data, Ora, Squadra1, Squadra2)
NAZIONALE(Nazione, Continente, Livello)
Scrivere la query SQL che determina:
3. La capienza complessiva degli stadi in cui gioca una
nazione europea.
Esercizio 7
Dato il seguente schema:
STADIO(Nome, Citta, Capienza)
INCONTRO(NomeStadio, Data, Ora, Squadra1, Squadra2)
NAZIONALE(Nazione, Continente, Livello)
Scrivere la query SQL che determina:
4. I nomi delle squadre che incontrano solo squadre dello
stesso livello.
Esercizio 8
Dato il seguente schema:
PROFESSORE(Codice, Nome, Cognome, Dipartimento,
Qualifica, Stipendio)
CORSO(CodiceCorso, CodiceProf, OreCorso)
0. Costruire il codice SQL dello schema, definendo i vincoli
di chiave di ciascuna tabella e gli eventuali vincoli di
integrita’ inter-relazionali esistenti.
- Definire un dominio per CodiceCorso come: stringa,
lunghezza 6 caratteri, la stringa deve iniziare per “C” e
terminare per “00”.
Esercizio 8
Dato il seguente schema:
PROFESSORE(Codice, Nome, Cognome, Dipartimento,
Qualifica, Stipendio)
CORSO(CodiceCorso, CodiceProf, OreCorso)
0. Costruire il codice SQL dello schema, definendo i vincoli
di chiave di ciascuna tabella e gli eventuali vincoli di
integrita’ inter-relazionali esistenti.
- Ogni aggiornamento del CodiceCorso della tabella
CORSO deve essere propagato anche all’attributo Codice
della tabella PROFESSORE.
Esercizio 8
Dato il seguente schema:
PROFESSORE(Codice, Nome, Cognome, Dipartimento,
Qualifica, Stipendio)
CORSO(CodiceCorso, CodiceProf, OreCorso)
Definire il seguente vincolo sullo schema:
1. Ogni dipartimento deve avere almeno 30 professori, e lo
stipendio medio in ogni dipartimento deve essere inferiore a
25000 euro.
Esercizio 8
Dato il seguente schema:
PROFESSORE(Codice, Nome, Cognome, Dipartimento,
Qualifica, Stipendio)
CORSO(CodiceCorso, CodiceProf, OreCorso)
Definire il seguente vincolo sullo schema:
2. I professori ordinari dovrebbero insegnare per almeno 120
ore, mentre i ricercatori non dovrebbero superare le 60 ore
di lezione.
Esercizio 8
Dato il seguente schema:
PROFESSORE(Codice, Nome, Cognome, Dipartimento,
Qualifica, Stipendio)
CORSO(CodiceCorso, CodiceProf, OreCorso)
Definire il seguente vincolo sullo schema:
3. Ogni volta che si rimuove una riga dalla tabella
PROFESSORI, se il totale dei Professori diventa minore di
20 per il dipartimento di Informatica, devono essere rimossi
tutti i CORSI tenuti da professori di Informatica.
Esercizio 8
Dato il seguente schema:
PROFESSORE(Codice, Nome, Cognome, Dipartimento,
Qualifica, Stipendio)
CORSO(CodiceCorso, CodiceProf, OreCorso)
Definire il seguente vincolo sullo schema:
4. Definire una vista
DIPARTIMENTIARISCHIO(Dipartimento, MonteOre),
definita come l’insieme dei dipartimenti in cui il totale delle
ore di lezione svolte da docenti di quel dipartimento sia
inferiore a 400.
Esercizio 8
Dato il seguente schema:
ATLETA(Nome, Nazione, Eta’)
PARTECIPAZIONE(NomeAtleta, NomeGara, Piazzamento)
GARA(Nome, Nazione)
Scrivere la query SQL che determina:
1. Il nome degli atleti che hanno gareggiato solo nella
propria nazione.
Esercizio 8
Dato il seguente schema:
ATLETA(Nome, Nazione, Eta’)
PARTECIPAZIONE(NomeAtleta, NomeGara, Piazzamento)
GARA(Nome, Nazione)
Scrivere la query SQL che determina:
2. Il nome degli atleti che provengono da una nazione in cui
non si svolge alcuna gara.
Esercizio 8
Dato il seguente schema:
ATLETA(Nome, Nazione, Eta’)
PARTECIPAZIONE(NomeAtleta, NomeGara, Piazzamento)
GARA(Nome, Nazione)
Scrivere la query SQL che determina:
3. Il nome degli atleti che hanno preso parte a tutte le gare
svolte in Francia.