PPT - Dipartimento di Informatica

Download Report

Transcript PPT - Dipartimento di Informatica

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:

. Le righe della tabella ATLETA che si riferiscono ad atleti il cui nome inizia per “M” oppure non e’ specificato.

Esercizio 2

. Tutti i codici delle gare di Atletica cui partecipano atleti della societa’ “Borgorosso”.

Esercizio 2

. Tutti i nomi/cognomi degli atleti che partecipano ad almeno due gare.

Esercizio 2

. Il numero totale di Discipline cui ha partecipato l’atleta 234 nella Gara identificata dal codice 12.

Esercizio 2

. Per ogni societa’, il numero totale di gare vinte.

Esercizio 2

. 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:

. Popola la tabella Sportivi(Nome, Cognome, Societa’) come insieme di tutti gli atleti ed allenatori presenti nello schema.

Esercizio 3

. Aggiorna i dati degli Atleti nati in data 10/11/2012, settando il nuovo valore della societa’ a “Polisportiva Rossi”.

Esercizio 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:

. 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:

. 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:

. 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:

. Per ogni citta’, il numero di sale con piu’ di 60 posti.

Esercizio 5

. Il numero totale dei posti dei cinema di Bologna.

Esercizio 5

. Per ogni regista, la somma totale degli incassi totalizzati dai suoi film.

Esercizio 5

. 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:

. Le citta’ con un aeroporto di cui non e’ noto il numero di piste.

Esercizio 5

. Le nazioni da cui parte ed arriva il volo con codice AZ1343.

Esercizio 5

. I tipi di aereo usati nei voli che partono da Torino.

Esercizio 5

. Il numero di voli internazionali che partono da Napoli.

Esercizio 5

. La quantita’ totale di merci trasportata da aerei che partono ogni giovedi’ da Napoli e diretti in aeroporti italiani.

Esercizio 5

. Le citta’ francesi da cui partono piu’ di venti voli alla settimana diretti per l’Italia.

Esercizio 5

. Le citta’ servite dall’aereo con numero minimo di passeggeri.

Esercizio 5

. 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:

. 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

. Il codice dei prodotti venduti in una sola citta’.

Esercizio 6

. La citta’ in cui viene venduto al prezzo + basso il prodotto con codice 123.

Esercizio 6

. 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:

. 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:

. 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:

. 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:

. 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)

. 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)

. 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:

. 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:

. 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:

. 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:

. 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:

. 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:

. 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:

. Il nome degli atleti che hanno preso parte a tutte le gare svolte in Francia.