Basi di Dati e Sistemi Informativi

(1)

Basi di Dati e Sistemi Informativi

Esercitazione:

Il Linguaggio SQL (DDL+DML)

Home page del corso:

http://www.cs.unibo.it/~difelice/dbsi/

(2)

Esercizio 1

➢ Scrivere il codice SQL dello schema

Codice Autore Conferenza Anno Formato Pagine

ARTICOLI

➢ Autore deve essere sempre definito.

➢ Conferenza è una stringa di lunghezza max 8.

➢ Formato è una stringa di lunghezza 3.

➢ Il numero di pagine deve essere sempre maggiore di 0, nel caso di omissione si assume pari ad 1.

(3)

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

);

(4)

Esercizio 1

Nome Data Organizzatore NumPartecipanti

CONFERENZE

➢ Vincolo di integrita’: ARTICOLO.{Conferenza, Data} à CONFERENZA.{Nome, Data}

➢ Rimuovendo una conferenza da

CONFERENZE, vengono rimossi anche gli articoli corrispondenti

(5)

Esercizio 1

CREATE TABLE CONFERENZE ( NOME VARCHAR(8),

DATA DATE,

ORGANIZZATORE VARCHAR(20),

NUMPARTECIPANTI INTEGER CHECK (NUMPARTECIPANTI>0),

PRIMARY KEY(NOME,DATA) );

(6)

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

);

(7)

Esercizio 1

NomeConf DataConf Nome Cognome Ruolo

PARTECIPANTE

➢ Vincolo di integrita’: PARTECIPANTE.

{NomeConf, DataConf} à CONFERENZA.

{Nome, Data}

➢ Ogni aggiornamento di {Nome, Data} nella

relazione CONFERENZA viene propagato anche alla relazione PARTECIPANTE.

(8)

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

);

(9)

Esercizio 2

➢ Definire in SQL i seguenti domini:

1. Saldo: intero, compreso tra -200 e 200, valore di default pari a 5.

2. NomeUtente: stringa di max 10 caratteri,

valore di default pari al nome dell’utente che esegue le operazioni di inserimento dati.

(10)

Esercizio 2

➢ Definire in SQL i seguenti domini:

1. Saldo: intero, compreso tra -200 e 200, valore di default pari a 5.

2. NomeUtente: stringa di max 10 caratteri,

valore di default pari al nome dell’utente che esegue le operazioni di inserimento dati.

CREATE DOMAIN SALDO AS SMALLINT DEFAULT 5 CHECK (VALUE<200 AND VALUE> -200)

CREATE DOMAIN NOMEUTENTE AS VARCHAR(10) DEFAULT USER

(11)

Esercizio 3 (per casa) à

➢ Scrivere il codice SQL dello schema seguente:

Nome Cognome DataNascita Presenza Goal NSquadra

GIOCATORI

➢ Presenza è un intero, valore di default 0.

➢ Goal è un intero, valore di default 0, e può assumere valori compresi tra 0 e 30.

➢ Esiste un vincolo di integrità tra

GIOCATORI.Nsquadra e SQUADRE.Nome

(12)

Esercizio 3 (per casa) à

Nome Campionato Data Fondazione Città

SQUADRE

➢ Nome è una stringa di lunghezza massima 20 caratteri.

➢ Campionato è un carattere, e può assumere solo i valori {A,B,C}.

➢ Città deve essere sempre definito

(13)

Esercizio 4.a

Dato il seguente schema:

CIRCOLI(Nome, Indirizzo, Anno, Telefono) UTENTI(Id, Nome, Cognome, AnnoNascita, Professionista)

ISCRIZIONI(IdUtente, NomeCircolo, Quota) Scrivere la query SQL che determina:

0.Nome ed indirizzo dei CIRCOLI fondati dal 2000 in poi.

(14)

Esercizio 4.a

1.Nome dei circoli che iniziano per “C” e che hanno come terza lettera la “s”.

(15)

Esercizio 4.a

2.Id degli Utenti iscritti al circolo “CUSB Tennis”

(16)

Esercizio 4.a

3.Nome e cognome degli Utenti iscritti al circolo “CUSB Tennis”

(17)

Esercizio 4.a

4.Numero degli utenti professionisti presenti nel database

(18)

Esercizio 4.a

5.Somma totale delle quote versate da utenti nati dal 2000 in poi

(19)

Esercizio 4.a

5.Quota media delle iscrizioni presso circoli “storico”. Un circolo si definisce storico se è stato fondato prima del 2000.

(20)

Esercizio 4.a

6.Per ogni utente, calcolare il numero di circoli a cui si è iscritti la somma totale delle quote di iscrizione versate

(21)

Esercizio 4.a

6.Id e nome degli utenti iscritti ad almeno due circoli

(22)

Esercizio 4.a

7 . Nome ed indirizzo dei circoli cui è iscritto l’utente con id pari a 4.

(23)

Esercizio 4.a

8 . Nome e cognome degli utenti che NON sono iscritti ad alcun circolo.

(24)

Esercizio 4.a

9 . Nome e indirizzo dei circoli che vantano SOLO utenti professionisti tra i propri iscritti.

(25)

Esercizio 4.b

Atleta(Codice, Nome, Cognome, Societa’)

Gara(CodiceGara, Disciplina, Data, CodiceVincitore) Partecipazione(CodiceGara, CodiceAtleta)

Scrivere la query SQL che determina:

0.Nomi e cognomi di Atleti della societa’ Borgorosso.

(26)

Esercizio 4

SELECT NOME, COGNOME FROM ATLETA

WHERE (SOCIETA’=‘Borgorosso’)

(27)

Esercizio 4.b

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

(28)

Esercizio 4.b

SELECT *

FROM ATLETA

WHERE ((Nome LIKE ‘M%’) OR (Nome IS NULL))

(29)

Esercizio 4.b

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

(30)

Esercizio 4.b

SELECT CODICEGARA

FROM PARTECIPAZIONE, ATLETA

WHERE ((PARTECIPAZIONE.CODICEATLETA=

ATLETA.CODICE) AND

(SOCIETA’ = ‘BORGOROSSO’))

(31)

Esercizio 4.b

3. Tutti i codici delle gare di Atletica cui partecipano atleti della societa’ “Borgorosso”, svolte in data 7/10/2013.

(32)

Esercizio 4.b

SELECT CODICEGARA

FROM PARTECIPAZIONE, ATLETA, GARA WHERE ((CODICEATLETA=CODICE) AND

(GARA.CODICEGARA=PARTECIPAZIONE.

CODICEGARA) AND

(SOCIETA’ = ‘BORGOROSSO’) AND (DATA=07/10/2013))

(33)

Esercizio 4.b

4. I nomi delle societa’ i cui atleti hanno vinto una gara di LancioDelDisco. Filtrare i duplicati.

(34)

Esercizio 4.b

SELECT DISTINCT(SOCIETA’) FROM ATLETA, GARA

WHERE ((CODICEVINCITORE=CODICE) AND (DISCIPLINA=‘LANCIODELDISCO’))

(35)

Esercizio 4.b

5. Il numero di gare svolte in data 7/10/2013.

(36)

Esercizio 4.b

SELECT COUNT(*) FROM GARA

WHERE (DATA=‘7/10/2013’)

(37)

Esercizio 4.b

6. Per ogni societa’, il numero di atleti che ne fanno parte.

(38)

Esercizio 4.b

SELECT SOCIETA’, COUNT(*) AS NUMERO FROM ATLETA

GROUPBY SOCIETA’

(39)

Esercizio 4.b

7. Le societa’ che hanno almeno 3 atleti tesserati.

(40)

Esercizio 4.b

SELECT SOCIETA’

FROM ATLETA

GROUPBY SOCIETA’

HAVING COUNT(*) >=3

(41)

Esercizio 4.b

7. Gli atleti che hanno partecipato ad almeno 5 gare.

(42)

Esercizio 4.b

SELECT CODICE

FROM ATLETA, PARTECIPAZIONE WHERE (CODICEATLETA=CODICE) GROUPBY CODICE,CODICEATLETA HAVING (COUNT(*)>=5)

(43)

Esercizio 4.b

8. Gli atleti che NON hanno mai partecipato ad una gara.

(44)

Esercizio 4.b

SELECT CODICE FROM ATLETA EXCEPT

SELECT DISTINCT(CODICEATLETA) FROM PARTECIPAZIONE

(45)

Esercizio 5

Atleta(Codice, Nome, Cognome, Societa’), 50 elementi Gara(CodiceGara, Disciplina, Data, CodiceVincitore) Partecipazione(CodiceGara, CodiceAtleta)

1. Determinare la cardinalita’ (#righe) della query seguente:

SELECT CODICE FROM ATLETA

(46)

Esercizio 5

SELECT CODICE FROM ATLETA

Cardinalita’ del risultato: 50

(47)

Esercizio 5

SELECT DISTINCT(NOME) FROM ATLETA

(48)

Esercizio 5

Cardinalita’ del risultato: 1<=x<=50

(49)

Esercizio 5

WHERE (SOCIETA’ =‘Borgorosso’)

(50)

Esercizio 5

WHERE (SOCIETA’ =‘Borgorosso’)

Cardinalita’ del risultato: 1<=x<=50

(51)

Esercizio 5

Atleta(Codice, Nome, Cognome, Societa’), 50 elementi

Gara(CodiceGara, Disciplina, Data, CodiceVincitore), 20 el Partecipazione(CodiceGara, CodiceAtleta), 100 elementi 4. Determinare la cardinalita’ (#righe) della query seguente:

SELECT CODICEGARA

FROM PARTECIPAZIONE, ATLETA WHERE (CODICE=CODICEATLETA)

(52)

Esercizio 5

Atleta(Codice, Nome, Cognome, Societa’), 50 elementi

Gara(CodiceGara, Disciplina, Data, CodiceVincitore), 20 el Partecipazione(CodiceGara, CodiceAtleta), 100 elementi 4. Determinare la cardinalita’ (#righe) della query seguente:

SELECT CODICEGARA

FROM PARTECIPAZIONE, ATLETA WHERE (CODICE=CODICEATLETA)

Cardinalita’ del risultato: 100

(53)

Esercizio 5

Atleta(Codice, Nome, Cognome, Specialita’), 50 elementi Gara(CodiceGara, Disciplina, Data, CodiceVincitore), 20 el Partecipazione(CodiceGara, CodiceAtleta), 100 elementi 5. Determinare la cardinalita’ (#righe) della query seguente:

SELECT *

FROM GARA, ATLETA

WHERE (DISCIPLINA=SPECIALITA’)

Cardinalita’ del risultato: 0<=x<=50*20

(54)

Esercizio 6

UTENTE(CF, Nome, Cognome, Via, Città)

PACCO(Codice, Peso, CFMittente, CFDestinatario, Stato) Scrivere la query SQL che costruisce la tabella PACCO:

1. Peso ha valore di default 0,1

2. CFMittente e CFDestinatario sono in vincolo di integrità referenziale con l’attributo CF della tabella UTENTE

3. Stato può essere “Lost”, “Delivered” o “Transit”

(55)

Esercizio 6

PACCO(Codice, Peso, CFMittente, CFDestinatario, Stato) Scrivere la query SQL che determina:

◇ Numero di pacchi di almeno 15 Kg spediti da utenti residenti a Bologna

(56)

Esercizio 6

◇ CF di utenti che non hanno MAI perso dei pacchi postali a loro destinati (se un pacco è perso, Stato=“Perso”)

(57)

Esercizio 6

◇ CF, Nome e Cognome di utenti di Bologna che hanno effettuato almeno 3 spedizioni

(58)

Esercizio 6

◇ Numero di spedizioni effettuate tra utenti che risiedono nella stessa città.

(59)

Esercizio 7 (per casa)

Docente(Codice, Nome, Cognome)

Aula(Nome, Dipartimento, NumPosti)

Prenotazione(Data, NomeAula, CodDocente) Scrivere la query SQL che determina:

1. Nome e Cognome dei docenti che hanno prenotato l’aula

“Ercolani1” in data 13/10/2015.

(60)

Esercizio 7 (per casa)

2. Capienza massima tra le aule del dipartimento di Fisica, prenotate dal docente con codice 134.

(61)

Esercizio 7 (per casa)

3. Numero dei docenti che hanno prenotato aule del

Dipartimento di Matematica, nel periodo compreso tra il 13/06/2014 ed il 13/10/2014.

(62)

Esercizio 7 (per casa)

4. Codice del docente che ha prenotato almeno due aule (distinte) del Dipartimento di Fisica, in data 13/10/2014 (VINCOLO: non è possibile usare operatori aggregati!)

(63)

Esercizio 7 (per casa)

5. Codice, nome e cognome dei docenti che non hanno mai prenotato aule del dipartimento di Fisica.

(64)

Esercizio 7 (per casa)

6. Per ogni dipartimento, il numero di prenotazioni di aule nel periodo compreso tra il 17/10/2013 ed il 17/11/2014.

(65)

Esercizio 7 (per casa)

7. I codici dei docenti che hanno effettuato almeno 4 prenotazioni di aule del dipartimento di Fisica.

(66)

Esercizio 7 (per casa)

8. Codice, nome e cognome dei docenti che hanno effettuato prenotazioni di aule del dipartimento di Fisica.

(VINCOLO: solo 1 tabella nella clausola FROM)

(67)

Esercizio 7 (per casa)

9. Dipartimento che ospita l’aula con maggior numero di posti.

(68)

Esercizio 7 (per casa)

10. Nomi delle aule del dipartimento di Fisica che non sono state mai prenotate nel periodo dal 30/09/2013 al

30/09/2014.

(69)

Esercizio 7 (per casa)

11. Nome/i delle aule prenotate più della media (di tutte le altre aule), nel periodo compreso tra il 1/1/2013 ed il

31/12/2013

(70)

Esercizio 8

Atleta(Codice, Nome, Cognome, DataNascita, Societa’) Allenatore(Codice, Nome, Cognome, Profilo, Societa’) Sportivi(Nome, Cognome, Societa’)

Scrivere la query SQL che:

1. Inserisce nella tabella sportivi l’utente “Michele Rossi”.

(71)

Esercizio 8

Atleta(Codice, Nome, Cognome, DataNascita, Societa’) Allenatore(Codice, Nome, Cognome, Profilo, Societa’) Sportivi(Nome, Cognome, Societa’)

INSERT INTO SPORTIVI(NOME,COGNOME) VALUES (‘Michele’, ‘Rossi’)

(72)

Esercizio 8

Atleti(Codice, Nome, Cognome, DataNascita, Societa’) Allenatori(Codice, Nome, Cognome, Profilo, Societa’) Sportivi(Nome, Cognome, Societa’)

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

(73)

Esercizio 8

INSERT INTO SPORTIVI(NOME,COGNOME, SOCIETA’) (SELECT NOME,COGNOME,SOCIETA’

FROM ATLETI UNION

SELECT NOME,COGNOME,SOCIETA’

FROM ALLENATORI)

(74)

Esercizio 8

3. Rimuove tutti gli allenatori la cui societa’ di appartenenza non e’ specificata.

(75)

Esercizio 8

DELETE FROM ALLENATORI WHERE (SOCIETA’ IS NULL)

(76)

Esercizio 8

4. Aggiorna i dati degli Atleti nati in data 10/11/2012,

settando il nuovo valore della societa’ a “Polisportiva Rossi”

(77)

Esercizio 8

UPDATE ATLETI

SET SOCIETA’=‘POLISPORTIVA ROSSI’

WHERE DATANASCITA=’10/11/2012’

(78)

Esercizio 9

IMPIEGATO(Matricola, Nome, Cognome, Stipendio, Direttore) DIPARTIMENTO(Codice, Direttore)

SELECT SUM(STIPENDIO) SELECT SUM(STIPENDIO)

FROM IMPIEGATO I FROM IMPIEGATO I, DIPARTIMENTO D WHERE I.DIRETTORE IN WHERE (I.DIRETTORE =

SELECT DIRETTORE D.DIRETTORE) FROM DIPARTIMENTO

Definire se le due query sono equivalenti.

(79)

Esercizio 10

IMPIEGATO(Matricola, Nome, Cognome, Stipendio)

QUERY: Selezionare le matricole di impiegati che guadagnano piu’ di un qualche loro collega.

SELECT MATRICOLA FROM IMPIEGATO

WHERE (STIPENDIO SELECT STIPENDIO FROM IMPIEGATO)

Indicare una condizione valida!

(80)

Esercizio 10

QUERY: Selezionare le matricole di impiegati che guadagnano piu’ di tutti i loro colleghi.

WHERE (STIPENDIO SELECT STIPENDIO FROM IMPIEGATO)

Indicare una condizione valida!

(81)

Esercizio 10

QUERY: Selezionare le matricole di impiegati che guadagnano tra i 20K ed i 30K.

WHERE (MATRICOLA SELECT MATRICOLA FROM IMPIEGATO

WHERE (STIPENDIO IN (20000,30000)) Indicare una condizione valida!

(82)

Esercizio 11

AEROPORTO(Id, Nazione, NumPiste)

VOLO(Codice, Partenza, Arrivo, ModAereo) AEREO(ModelloAereo, NumPasseggeri)

Vincoli di integrita’:

VOLO.Partenza à AEROPORTO.Id VOLO.Arrivo à AEROPORTO.Id

VOLO.ModAereo à AEREO.ModelloAereo

1. Il totale dei passeggeri che hanno volato dalla Francia all’Italia.

(83)

Esercizio 11

2. Per ogni nazione, il numero di voli in partenza.

(84)

Esercizio 11

3. I modelli di aerei che volano da aeroporti francesi ad aeroporti italiani (VINCOLO: Max 1 tabella nel FROM)

(85)

Esercizio 11

4. Le nazioni da cui partono più di venti voli per l’Italia

(86)

Esercizio 11

5. L’aeroporto (id) da cui partono più voli diretti verso l’Italia rispetto alla media degli altri aeroporti.

(87)

Esercizio 12.a

Date le seguenti transazioni:

T₀: r₀(x) r₀(y) r₀(z) w₀(z) C(T₀)

T₁: w₁(y) w₁(z) r₁(a) r₁(b) w₁(a) A(T₁) T₂: r₂(a) r₂(b) w₂(b) w₂(a) C(T₂)

Lo schedule seguente e’ uno schedule seriale:

➢w₁(y) w₁(z) r₁(a) r₁(b) w₁(a) A(T₁) r₂(a) r₂(b) w₂(b) w₂(a) C(T₂) r₀(x) r₀(y) r₀(z) w₀(z) C(T₀)

(88)

Esercizio 12.b

Date le seguenti transazioni:

T₀: r₀(x) r₀(y) r₀(z) w₀(z) C(T₀)

T₁: w₁(y) w₁(z) r₁(a) r₁(b) w₁(a) A(T₁) T₂: r₂(a) r₂(b) w₂(b) w₂(a) C(T₂)

Il seguente schedule e’ uno schedule seriale:

➢w₁(y) w₁(z) r₁(a) r₂(a) r₂(b) w₂(b) w₂(a) C(T₂) r₀(x) r₀(y) r₀(z) w₀(z) C(T₀) r₁(b) w₁(a) A(T₁)

(89)

Esercizio 13

Sia x un oggetto del DB, con WTM(x)=5, RTM(x)=7.

Indicare il valore finale di WTM e RTM (TS-based), quali operazioni sono eseguite e quali abortite.

r₄(x), r₆(x), r₁₇(x), w₁₆(x), w₁₈(x), w₂₃(x), w₂₉(x), r₂₀(x), r₃₀(x), r₂₅(x)

(90)

Esercizio 14

Indicare gli insiemi di UNDO/REDO e le operazioni di recovery, se vengono eseguite le seguenti operazioni sul DB:

DUMP B(T₁) B(T₂) B(T₃) I(T₁,O₁,A₁) D(T₂,O₂,B₂) B(T₄) U(T₄,O₃,B₃,A₃)

U(T₁,O₄,B₄,A₄) C(T₂) CK(T₁,T₃,T₄) B(T₅) B(T₆) U(T₅,O₅,B₅,A₅) A(T₃)

CK(T₁,T₄,T₅,T₆) B(T₇) C(T₄)

U(T₇,O₆,B₆,A₆) U(T₆,O₃,B₇,A₇) B(T₈) A(T₇) guasto

(91)

Esercizio 15

VEICOLO(Targa, Modello, AnnoImmatricolazione) CLIENTI(NrPatente, Nome, Cognome, DataNascita) NOLEGGIO(TargaVeicolo, Data, PatenteCliente)

2. Nome dei clienti (senza duplicati) che NON

hanno mai noleggiato una macchina (VINCOLO:

usare query annidate).

(92)

Esercizio 15

SELECT DISTINCT(Nome) FROM CLIENTI C

WHERE NOT EXIST ( SELECT *

FROM NOLEGGIO N

WHER (C.NRPATENTE=N.PATENTECLIENTE))

(93)

Esercizio 15

SELECT DISTINCT(Nome) FROM CLIENTI C

WHERE NRPATENTE NOT IN

SELECT PATENTECLIENTE FROM NOLEGGIO N

(94)

Esercizio 15

3. Nome e cognome dei clienti che hanno

noleggiato piu’ macchine rispetto alla media (degli altri clienti).

(95)

Esercizio 15

CREATE VIEW TOTNOLEGGI(P, NUM) AS (

SELECT PATENTECLIENTE AS P, COUNT (*) AS NUM

FROM NOLEGGIO

GROUPBY PATENTECLIENTE )

SELECT NOME, COGNOME

FROM TOTNOLEGGI, CLIENTI

WHERE ((NUM > SELECT AVG(NUM) FROM TOTNOLEGGI) AND (P=NRPATENTE))