SQLEsercitazione per il corso “Basi di Dati”

Gabriel Kuper

Nataliya Rassadko (rassadko@dit.unitn.it)

Lezione 4 Compito da casa Esercizio JOIN

Cross-join Natural join Theta-join Outer join

Aggregazione COUNT SUM AVG MIN, MAX

ESERCIZIO Visualizzare i film che hanno degli omonimi (ovvero film con

lo stesso titolo ma prodotti in anni diversi o da un diverso produttore)Movie(title,year,length,inColor,studioName,producerC#)StarsIn(movieTitle,movieYear,starName)MovieStar(name,address,gender,birthdate)MovieExec(name,address,cert#,netWorth)Studio(name,address,presC#)

SELECT *FROM “Movie” as MWHERE exist(SELECT *FROM “Movie” as M1WHERE M.“title”=M1.“title” and (M.“year”<>M1.“year” or M.“producerC#”<>M1.“producerc#”)

Compito da Casa 1

Quali attori-uomini partecipavano nel film “Terms of Endearment”? Quali attori partecipavano nel film prodotti da MGM nel 1995? Quali film sono più lunghi del film “Gone With the Wind”? Chi è dirigente di MGM? Trovare i dirigenti (degli studi) con netWorth maggiore di quello di

Mervin Griffin

Movie(title,year,length,inColor,studioName,producerC#)StarsIn(movieTitle,movieYear,starName)MovieStar(name,address,gender,birthdate)MovieExec(name,address,cert#,netWorth)Studio(name,address,presC#)

Compito da Casa 2

Trovare “maker” e “speed” di laptop che hanno “hd”>30Gb Trovare “model” e “price” dei prodotti fatti dal “maker”=‘B’ Trovare tutti “maker” di laptop che non producono PC Trovare le coppie di “model” di PC che hanno stessi “speed” e

“ram” Trovare “maker” che producono almeno due modelli di PC o

laptop con “speed”>=1000MHz

Product (maker, model, type)PC (model, speed, ram, hd, rd, price)Laptop (model, speed, ram, hd, screen, price)Printer (model, color, type, price)

Compito da Casa 3

Trovare tutte le navi con “displacement”>35 tonnelate Trovare “name”, “displacement” e “numGuns” delle navi che

partecipavano nelle battaglie (“battle”) alla isola Guadalcanal Trovare tutti “country” dove si costruiscono le navi con tipo ‘bb’ o ‘bc’ Trovare tutte le navi che, dopo aver ricevuto i danni

(“result”=‘damaged’) in qualche battaglia, partecipavano in altre battaglie

Trovare “name” di battaglie dove partecipavano almeno tre navi dalla stessa “country”

Classes (class, type, country, numGuns, bore, displacement)Ships (name, class, launched)Battles (name, date)Outcomes (ship, battle, result)

ESERCIZIO (3 varianti) Dati i seguenti schemi:

StarsIn (movieTitle, movieYear, starName)

MovieStar (name, address, gender, birthdate) Trovare l’elenco degli attori maschi (‘male’) che hanno

recitato in Meet the Fockers.SELECT MovieStar.name FROM MovieStar, StarINWHERE StarIn.title=‘Meet the Fockers’ AND MovieStar.gender=‘male’AND MovieStar.name=StarIn.starName

SELECT name FROM StarsInWHERE movieTitle=‘Meet the Fockers’ AND name IN (SELECT name FROM MovieStarWHERE gender=‘male’)

SELECT name FROM StarsInWHERE movieTitle=‘Meet the Fockers’

INTERSECT SELECT name FROM MovieStarWHERE gender=‘male’

Cross join = Prodotto Cartesiano Movie CROSS JOIN StarsIn

Si usa molto raramente

NATURAL JOIN Il join è creato su tutte le colonne che hanno il medesimo

nome in entrambe le tabelle.

La tabella del risultato coniene combinazioni delle tuple di due tabelle che hanno stessi valori negli attributi communi

Se attributi communi non ci sono, NATURAL JOIN è uguale al CROSS JOIN

<tabella1> NATURAL JOIN <tabella2>

NATURAL JOIN Eseguire una NATURAL JOIN sulle tabelle:

MovieStar (name, address, gender, birthdate)

MovieExec (name, address, cert#, netWorth)

SELECT * FROM imdb."MovieStar" NATURAL JOIN imdb."MovieExec";

JOIN ... USING Il NATURAL JOIN esegue join di tutte le

colonne che hanno lo stesso nome. Possiamo specificare subset delle colonne su

quali fare join JOIN...USING.

<tabella> JOIN <tabella> USING (<colonna1>, ... <colonna n>)

Theta-join: INNER JOIN R INNER JOIN S ON C Dati i seguenti schemi:

StarsIn (movieTitle, movieYear, starName)

Movie (title, year, length, inColor, studioName, producerC#)

Visualizzare i dati degli attori e dei film che hanno girato:

SELECT * FROM imdb."Movie" JOIN imdb."StarsIn“ ON "title"="movieTitle";

INNER JOIN: Risultato Restituisce solo quelle le righe delle tabelle se c'è un

legame, altrimenti non le mostra

In algebra R ⋈C S = σC (R ⋈ S)

INNER JOIN = JOIN

JOIN: Riassunto Usiamo la keyword NATURAL quando

vogliamo che la join venga effettuata sugli attributi che “naturalmente” collegano le tabelle

Usiamo la keyword ON <attribute> quando desideriamo su quali attributi vogliamo collegare le tabelle

FULL OUTER JOIN Il FULL OUTER JOIN restituisce tutte le

righe della prima tabella e tutte le righe della seconda tabella, anche se non ci sono corrispondenze.

FULL OUTER JOIN Eseguire una NATURAL FULL OUTER JOIN sulle

tabelle:

MovieStar (name, address, gender, birthdate)

MovieExec (name, address, cert#, netWorth)

SELECT * FROM imdb."MovieStar" NATURAL FULL OUTER JOIN imdb."MovieExec";

LEFT OUTER JOIN Il LEFT OUTER JOIN restituisce tutte le

righe della prima tabella, anche se non ci sono corrispondenze nella seconda tabella

SELECT campi FROM prima_tabellaLEFT OUTER JOIN seconda_tabella

   ON prima_tabella.chiave_primaria = seconda_tabella.chiave_esterna

RIGHT OUTER JOIN Il RIGHT OUTER JOIN restituisce tutte le

righe della seconda tabella, anche se non ci sono corrispondenze nella prima tabella

SELECT campi FROM prima_tabellaRIGTH OUTER JOIN seconda_tabella

   ON prima_tabella.chiave_primaria = seconda_tabella.chiave_esterna

OPERATORI AGGREGATI Gli operatori aggregati costituiscono uno delle

più importanti estensioni di SQL rispetto all’algebra relazionale.

In algebra relazionale tutte le condizioni vengono valutate su una tupla alla volta

Spesso però si devono valutare delle proprietà che dipendono da insiemi di tuple.

OPERATORI AGGREGATIImpiegato(nome, congnome, dipart, ufficio,

stipendio, città) Supponiamo che si voglia determinare il

numero degli impiegati del dipartimento Produzione.

Il numero di impiegati corrisponderà al numero di tuple della relazione Impiegato che possiedono “Produzione” come valore dell’attributo Dipart.

OPERATORI AGGREGATI Questo numero non è però una proprietà

posseduta da una tupla in particolare e perciò l’interrogazione non è esprimibile in algebra relazionale.

Per esprimerla in SQL usiamo gli operatori aggregati SUM, AVG, COUNT, MIN, MAX

COUNT La funzione COUNT restituisce il numero di righe presenti

in una tabella.

vengono incluse anche le righe con valore NULL

vengono escluse le righe con valore NULL per attributo

Select Count (*)

From NomeTabella

Select Count (attributo)

From NomeTabella

COUNT Visualizzare il numero delle persone

registrate nella tabella dell’anagrafica che abitano a Milano

Anagrafica (Codice, Nome, Indirizzo)

Select Count(Indirizzo)

From Anagrafica

Where Indirizzo=“Milano”

COUNT Calcolare quanti film sono stati prodotti in

bianco e nero dopo l’anno 1970.movie(title, year, length, inColor, studioName, producerC#)

SELECT COUNT(*)FROM imdb."Movie"WHERE ‘year’>’1970’ and not inCOLOR

SUM Restituisce la somma di tutti i valori contenuti

in una colonna specificata L’attributo utilizzato nel calcolo deve essere

di tipo numerico.

Select Sum(NomeAttributo)

From NomeTabella

SUM Visualizzare l’importo totale dei movimenti

riferiti al giorno 12/03/2006

Movimento (Numero, Descrizione, Data, Importo, Codice)

Select SUM(Importo)

From Movimento

Where data=12/03/2006

SUM Calcolare la somma della durata di tutti i film

prodotti nell’anno 1970.movie(title, year, length, inColor, studioName, producerC#)

SELECT SUM(length)FROM imdb."Movie"WHERE ‘year’=’1970’

AVG Calcola la media (average) dei valori

(numerici) contenuti in una determinata colonna di una tabella.

Select Avg(NomeAttributo)

From NomeTabella

AVG Calcolare l’importo medio dei movimenti

Movimento (Numero, Descrizione, Data, Importo, Codice)

Select Avg(Importo)

From Movimento

AVG Calcolare la media della durata di tutti i film

prodotti nell’anno 1970.movie(title, year, length, inColor, studioName, producerC#)

SELECT AVG(length)FROM imdb."Movie"WHERE ‘year’=’1970’

MIN, MAX Restituiscono rispettivamente il valore

minimo e il valore massimo tra i valori della colonna specificata come argomento della funzione (anche per campi di tipo carattere).

Gli operatori ignorano i campi con valore Null

Select Min(NomeAttributo),Max(NomeAttributo)

From NomeTabella

MIN, MAX Visualizzare la lunghezza minima e massima

dei film prodotti nell’anno 1970.movie(title, year, length, inColor, studioName, producerC#)

COMPITO DA CASA ESERCIZI PROVETTE DEGLI ANNI SCORSI RISPOSTE AI COMPITI DEL TERZO

SLIDE ALTRI EZERCIZI

ESERCIZIO Dato lo schema

Movie (title, year, length, inColor, studioName, producerC#)

Elencare la somma della durata di tutti i film in bianco e nero. Elencare la durata max e min tra quelli in bianco e nero

ESERCIZIO Visualizzare i film che hanno la lunghezza

massima tra tutti i film nella tabella Movie.

Movie (title, year, length, inColor, studioName, producerC#)

ESERCIZIO Visualizzare tutti i film che superano la

lungheza media. Movie (title, year, length, inColor, studioName, producerC#)

ESERCIZIO Estrarre i nomi e i cognomi di tutti gli impiegati, eccEtto

quelli che appartengono al dipartimento di amministrazione, mantenendo i duplicati.

IMPIEGATO (Nome, Cognome, Dipart, Ufficio, Stipendio, Città)

DIPARTIMENTO (Nome, Indirizzo, Città)

ESERCIZIO Estrarre il dipartimento dell’impiegato che

guadagna lo stipendio Massimo (usando solo un interrogazione nidificata)

IMPIEGATO (Nome, Cognome, Dipart, Ufficio, Stipendio, Città)

DIPARTIMENTO (Nome, Indirizzo, Città)

ESERCIZIO Estrarre le persone che hanno degli omonimi

(ovvero persone con lo stesso nome e cognome, ma diverso codice fiscale)

PERSONA (CodFiscale, Nome, Cognome, Città)

ESERCIZIO Calcolare la somma della durata di tutti i film

prodotti nell’anno 1970.

ESERCIZIO Elencare la somma della durata di tutti i film in

bianco e nero. Elencare la durata max e min tra quelli in bianco e

nero

ESERCIZIO Elencare, in ordine di durata decrescente, tutti

i film in cui ha partecipato l’attrice Roberts Julia.

ESERCIZIO Trovare il numero dei titoli distinti dei films. Qual è,

invece, il numero totale dei film?

Movie(title,year,length,inColor,studioName,producerC#)StarsIn(movieTitle,movieYear,starName)MovieStar(name,address,gender,birthdate)MovieExec(name,address,cert#,netWorth)Studio(name,address,presC#)

ESERCIZIO Visualizzare la star che ha partecipato al film più breve in

assoluto e quella che ha partecipato al piu lungo

Movie(title,year,length,inColor,studioName,producerC#)StarsIn(movieTitle,movieYear,starName)MovieStar(name,address,gender,birthdate)MovieExec(name,address,cert#,netWorth)Studio(name,address,presC#)

ESERCIZIO Visualizzare i film prodotti, prima del 1967, da studi cinematografici il cui

presidente sia (anche) un attore e abbia un networth > di 750.000

Movie(title,year,length,inColor,studioName,producerC#)StarsIn(movieTitle,movieYear,starName)MovieStar(name,address,gender,birthdate)MovieExec(name,address,cert#,netWorth)Studio(name,address,presC#)

ESERCIZIO Selezionare gli studi cinematografici il cui presidente ha partecipato ad

almeno un film prodotto dallo studio stesso.

Movie(title,year,length,inColor,studioName,producerC#)StarsIn(movieTitle,movieYear,starName)MovieStar(name,address,gender,birthdate)MovieExec(name,address,cert#,netWorth)Studio(name,address,presC#)

ESERCIZIO Estrarre lo Studio cinematografico il cui presidente guadagna

di meno

(fornire due soluzioni differenti: la prima usando l’operatore min, la seconda usando un’interrogazione nidificata).

Movie(title,year,length,inColor,studioName,producerC#)StarsIn(movieTitle,movieYear,starName)MovieStar(name,address,gender,birthdate)MovieExec(name,address,cert#,netWorth)Studio(name,address,presC#)

ESERCIZIO Estrarre i film in cui non hanno recitato attori provenienti da

New York (fornire DIFFERENTI due soluzioni all’esercizio)

Movie(title,year,length,inColor,studioName,producerC#)StarsIn(movieTitle,movieYear,starName)MovieStar(name,address,gender,birthdate)MovieExec(name,address,cert#,netWorth)Studio(name,address,presC#)

Provetta 2006 Esprimere le sequenti interrogazioni tramite SQL ed algebra relazionale

dati I seguenti schemi:

Lavora-in (CodImpigato, CodUfficio) Ufficio (CodUfficio, Nome, Descrizione,

Indirizzo, Città) Impiegato (CodImpiegato, Nome, Cognome, Titolo,

Stipendio)

Vizualizzare nome (NomeImpiegato, CognomeImpiegato), Titolo e dati dell’ufficcio (NomeUfficio, Reapito, Città) in cui lavorano tutti gli impiegati che percepiscono uno stipendio mensile superiore ai 1500 euro

Vizualizzare la media degli stipendi per ciascun ufficio

Provetta 2006 ESAMI (matricola, esame, data, voto) STUDENTI (matricola, nome, residenza, datadinascita,

laurea)

Si scrive una query SQL che, relativamente ai soli esami del corso di laurea con codice C2, fornisca per ogni esame le seguenti informazioni: numero di appelli effetuati, numero medio di studenti esaminati per appello, votazione media riportata dagli studenti

La query fornisca anche il numero medio di volte che è stato dato ciascun voto e l’età media degli studenti al momento del superamento dell’esame

Provetta 2008 AUTO (Targa, Marca, Cilindrata, Potenza, CodF,

CodAss) PROPRIETARI (CodF, Nome, Residenza) ASSICURAZIONI (CodAss, Nome, Sede) SINISTRO (CodS, Località) AUTOCOINVOLTE (CodS, Targa, ImportoDelDanno)

Estratte CodF e Nome del coloro che possiedono solo auto, con cilindrata superiore a 1.4, di almeno tre marche differenti

Estrarre il codice dei sinistri in cui non sono state coinvolte auto con cilindrata inferiore a 2000cc

Estrarre per ciascuna auto coinvolta in più di un sinistro, la targa dell’auto, il nome dell’Assicurazione ed il totale dei danni riportati

Compito da Casa 3 con risposte

Trovare tutte le navi con “displacement”>35 tonnelate Trovare “name”, “displacement” e “numGuns” delle navi che

partecipavano nelle battaglie (“battle”) alla isola Guadalcanal Trovare tutti “country” dove si costruiscono le navi con tipo ‘bb’ o ‘bc’ Trovare tutte le navi che, dopo aver ricevuto i danni

(“result”=‘damaged’) in qualche battaglia, partecipavano in altre battaglie

Trovare “name” di battaglie dove partecipavano almeno tre navi dalla stessa “country”

Classes (class, type, country, numGuns, bore, displacement)Ships (name, class, launched)Battles (name, date)Outcomes (ship, battle, result)

Query 1 select * from "Ships" where "class" in ( select "class" from "Classes" where "displacement" > 35 )

Query 2 select "S"."name", "C"."displacement",

"C"."numGuns" from "Ships" as "S", (select "class",

"displacement", "numGuns" from "Classes") as "C"

where "C"."class"="S"."class" and "S"."name" in (select "ship" from "Outcomes" where "battle"='Guadalcanal')

Query 3 select "country" from "Classes" where

"type"='bb' or "type"='bc'

Query 4 select "O"."ship", "O"."battle", "B"."date" from "Outcomes" as "O", "Battles" as "B" where "O"."battle"="B"."name" and Exists ( select "O1"."ship", "B1"."date" as "date" from "Outcomes" as "O1", "Battles" as "B1" where "O1"."battle"="B1"."name" and "O1"."result"='damaged' and "date"<"B"."date" and "O"."ship"="O1"."ship")

Query 5 select "name" from "Battles" where "name" in (

select "battle" from "Outcomes“ where exists ( select "s1"."name", "c1"."country", "s2"."name", "c2"."country",

"s3"."name", "c3"."country" from "Ships" "s1", "Ships" "s2", "Ships" "s3", "Classes" "c1",

"Classes" "c2", "Classes" "c3" where "s1"."name">"s2"."name" and "s2"."name">"s3"."name" and "c1"."country"="c2"."country" and "c3"."country"="c2"."country"

and "s1"."class"="c1"."class" and "s2"."class"="c2"."class" and

"s3"."class"="c3"."class" and ("s1"."name"="Outcomes"."ship" or "s2"."name"="Outcomes"."ship"

or "s3"."name"="Outcomes"."ship")))

Compito da Casa 1

Trovare “maker” di PC con “speed” >=1200MHz Trovare tutti i Printer con prezzo massimo Trovare tutti i Laptop con “speed” meno di qualsiasi “speed” di qualsiasi PC Trovare “model” di qualsiasi prodotto (PC, Laptop, Printer) con il prezzo

massimo Trovare “maker” che producono i printer di prezzo minimo che hanno

“color”=‘true’ Trovare I “maker” di PC con il massimo valore di “speed” tra I quelli PC che

hanno “ram” minimo

Product (maker, model, type)PC (model, speed, ram, hd, rd, price)Laptop (model, speed, ram, hd, screen, price)Printer (model, color, type, price)

Compito da Casa 2

Trovare “country” che producono le navi con il massimo numero di “numGuns”

Trovare “class” di navi che hanno “result”=‘sunk’ in qualche battaglia Trovare “name” delle navi che hanno missili di calibro “born”>16

pollici Trovare “name” di battaglie dove partecipavano le navi di

“class”=‘Kongo’ Trovare “name” delle navi con “numGuns” massimo tra le navi che

hanno I missili dello stesso calibro (“bore”)

Classes (class, type, country, numGuns, bore, displacement)Ships (name, class, launched)Battles (name, date)Outcomes (ship, battle, result)