sql: lezione 5 nataliya rassadko [email protected]

73
SQL: Lezione 5 Nataliya Rassadko [email protected]

Upload: felisa-parente

Post on 01-May-2015

216 views

Category:

Documents


0 download

TRANSCRIPT

Page 1: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

SQL: Lezione 5

Nataliya [email protected]

Page 2: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

Agenda Collegamento a PostgreSQL Compito da casa Operatori aggregati

GROUP BY HAVING

Esercizi

Page 3: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

Collegamento a PostgreSQL Command line

psql -U user01 -d db01 -h ares.science.unitn.it user01 puo’ essere sostituito da user02, user03, …, user99,

user100 Stessa cosa per db01 Ci bisognerà di inserire password – stessa cosa

Visualizzare tutte le tabelle SET search_path TO imdb,public

Query Nomi di tabelle e attributi vanno “”, e.g., “Movie” Prima di nomi di tabelle va imdb., e.g., imdb.“Movie” Tutti nomi sono case sensitive, e.g., tabella “movie” non esiste

Page 4: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

Compito da casa Esercizi Compito da casa per gli schemi

Products Ships

Provette vecchie

Page 5: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

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

SELECT MAX(“length”), Min(“length”) FROM imdb.“Movie”WHERE “inColor”=false

SELECT SUM(“length”) FROM imdb.“Movie”WHERE “inColor”=false

Page 6: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

ESERCIZIO Visualizzare i film con la lunghezza che

hanno la lunghezza massima e minimatra tutti i film nella tabella Movie.Movie (title, year, length, inColor, studioName, producerC#)

SELECT imdb.”Movie”.”title”, “length”

FROM imdb.“Movie”

Where “length” in (select max(“length”) from imdb.”Movie” union select min(“length”) from imdb.”Movie”)

Page 7: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

ESERCIZIO Visualizzare tutti i film che superano la

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

SELECT Movie.title FROM Movie WHERE length >

(SELECT AVG(Movie.length) FROM Movie)

Page 8: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

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

Select nome From Impiegati Where dipart<>’amministrazione’

Union all

Select cognome From Impiegati Where dipart<>’amministrazione’

Page 9: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

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

Select Dipart From Impiegato where Stipendio >= all (select stipendio from Impiegato)

Select Dipart From Impiegato where Stipendio =

(select MAX(stipendio) from Impiegato)

Page 10: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

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

Select *From Persona PWhere exist (Select * From Persone P1 Where P1.Nome = P.Nome and P1.Cognome = P.Cognome and P1.CodFIscale <> P.CodFiscale)

Page 11: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

ESERCIZIO Calcolare la somma della durata di tutti i film

prodotti nell’anno 1970.

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

Page 12: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

ESERCIZIO Elencare, in ordine di durata decrescente,

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

SELECT “title”FROM “Movie” JOIN “StarsIn” ON “Movie”.”title”=“StarsIn”.”movieTitle”WHERE “StarsIn”.”starName”=‘Roberts Julia’

Page 13: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

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

SELECT COUNT (DISTINCT "title") FROM “Movie”;

SELECT COUNT ("title") FROM “Movie”;

Page 14: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

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

Select “starName”From “MovieStar”Where “movieTitle” IN(Select “title” From “Movie”Where “length”=(SELECT MIN(“length”) FROM “Movie”) or length(SELECT MAX(“length”) FROM “Movie”))

Page 15: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

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

Select *From “Movie”Where “studioName” IN(Select “name” From “Studio” join “MovieExec” on “presC#=“cert#”Where “netWorth”>’750.000’ and “MovieExec”.“name” in (Select name From “MovieStar”))

Page 16: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

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

Select *From “Studio” join “MovieExec” on “presC#=“cert#”Where “MovieExec”.“name”,“MovieExec”.“presC#”in (Select “starName”,”producerC#” From “StarIn”,“Movie”Where “StarIn”.“movieTitle”=“Movie”.“title” and “StarIn”.“movieYear”=“Movie”.“year”)

Page 17: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

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

Select “Studio”.“name”From “Studio”, “MovieExec”Where cert#=presC# and (“netWorth”= (Select min(netWorth) from “MovieExec”)

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

Page 18: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

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

Select “Studio”.“name”From “Studio”, “MovieExec”Where cert#=presC# and “netWorth”>= all (Select “netWorth” from “MovieExec”)

Page 19: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

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

Select “title”From “Movie”Where “title” <> all (Select title from StarIn, MovieStarwhere “StarIn”.“StarName”=“MovieStar”.“name” and “address”<>’New York’)

Page 20: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

Compito da Casa: Schema di DB Products

Trovare “maker” di PC con “speed” >=1200MHz

SELECT maker FROM PC JOIN Product ON PC.model=Product.model WHERE speed>=1200

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

Page 21: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

Compito da Casa: Schema di DB Products

Trovare tutti i Printer con prezzo massimo

SELECT * FROM Printer WHERE price >=ALL (SELECT price FROM Printer)

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

Page 22: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

Compito da Casa: Schema di DB Products

Trovare tutti i Laptop con “speed” meno di qualsiasi “speed” di qualsiasi PC

SELECT * FROM Laptop WHERE speed < ALL (SELECT speed FROM Printer)

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

Page 23: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

Compito da Casa: Schema di DB Products

Trovare “maker” che producono i printer di prezzo minimo che hanno “color”=‘true’

SELECT maker FROM Product JOIN Printer ON Product.model=Printer.model WHERE color=‘true’ AND price = (SELECT min(price) FROM Printer)

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

Page 24: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

Compito da Casa: Schema di DB Navi 2

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

SELECT country FROM Classes WHERE numGuns = (SELECT max(numGuns) FROM Classes)

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

Page 25: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

Compito da Casa 2

Trovare “class” di navi che hanno “result”=‘sunk’ in qualche battaglia

SELECT class FROM Classes JOIN Ships WHERE name in (SELECT ship FROM Outcomes WHERE result=‘sunk’)

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

Page 26: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

Compito da Casa 2

Trovare “name” delle navi che hanno missili di calibro “bore”>16 pollici

SELECT name FROM Ships JOIN Classes WHERE bore>16

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

Page 27: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

Compito da Casa 2

Trovare “name” di battaglie dove partecipavano le navi di “class”=‘Kongo’

SELECT name FROM (Battles JOIN Ships) JOIN Classes WHERE class=‘Kongo’

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

Page 28: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

Compito da Casa 2

Trovare “name” delle navi con “numGuns” massimo tra le navi che hanno I missili dello stesso calibro (“bore”)

SELECT name, bore FROM Ships JOIN Classes WHERE numGuns = (SELECT max(numGuns), bore FROM Classes GROUP BY bore)

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

Page 29: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

Provetta 2006 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

SELECT I.Nome, Cognome, U.Nome, Descrizione FROM (Ufficcio U JOIN Lavora-in L ON U.CodUfficio=L.CodUfficio) JOIN Impiegato I ON I.CodImpiegato=L.CodImpiegato WHERE U.CodUfficio IN (SELECT Ufficio.CodUfficio FROM (Impiegato NATURAL JOIN Lavora_in) NATURAL JOIN Ufficio GROUP BY Ufficio.CodUfficio HAVING min(Salary)>1500)

Page 30: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

Provetta 2006 Lavora-in (CodImpigato, CodUfficio) Ufficio (CodUfficio, Nome, Descrizione, Indirizzo,

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

Stipendio)

Vizualizzare la media degli stipendi per ciascun ufficio

SELECT avg(Stipendio), U.Nome FROM join_delle_tabelle GROUP BY U.Nome

Page 31: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

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

Page 32: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

Soluzione SELECT sum(matricola) as NumeroAppelli, avg(voto) as

VotoMedio, Esame from Esami JOIN Studenti WHERE Laurea=”C2” GROUP BY Esame – restituamo numero appelli effetutai e votazione media per esame. Relazione R1

SELECT Esame, Data, count(Matricola) AppEsData FROM Esami JOIN Studenti WHERE Laurea=”C2” Group BY Esame, Data – restituamo numero apelli per esame per data. Relazione R2

SELECT Esame, avg(AppEsData) as StudentiMedioAppello FROM R2 Group BY Esame – Restituamo numero medio di studenti per esame per data (cioe’ per appello). Relazione R3

SELECT NumeroAppelli, VotoMedio, StudentiMedioAppello FROM R1, R3 WHERE R1.Esame=R3.Esame

Page 33: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

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

Page 34: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

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

Page 35: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

OPERATORI AGGREGATI Gli operatori aggregati vengono gestiti

come un’estensione delle normali interrogazioni.

Prima viene normalmente eseguita l’interrogazione, considerando solo le parti from e where.

L’operatore aggregato viene poi applicato alla tabella contenente il risultato dell’interrogazione.

Page 36: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

OPERATORI AGGREGATI Come visto nella scorsa lezione, lo

standard SQL prevede cinque operatori aggregati:

COUNT SUM MAX MIN AVG

Page 37: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

RAGGRUPPAMENTO Gli operatori aggregati sono gli operatori

che vengono applicati su un insieme di righe.

Gli esempi visti operano su tutte le righe che vengono prodotte come risultato dell’interrogazione.

Sorge la necessità di applicare l’operatore aggregato distintamente a sottoinsiemi di righe

Page 38: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

RAGGRUPPAMENTO Per poter usare in questo modo l’operatore

aggregato, SQL mette a disposizione la clausola group by.

Group by permette di specificare come dividere le tabelle in sottoinsiemi.

La clausola ammette come argomento un insieme di attributi e la query raggrupperà le righe che possiedono gli stessi valori per questo iniseme di attributi.

Page 39: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

RAGGRUPPAMENTO Estrarre la somma degli stipendi di tutti gli

impiegati dello stesso dipartimentoImpiegato (Nome, Cognome, Dipart, Ufficio, Stipendio, Città)

SELECT Dipart, SUM(Stipendio)FROM ImpiegatoGROUP BY Dipart;

Page 40: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

RAGGRUPPAMENTO Per prima cosa l’interrogazione viene

eseguita come se la clausola group by non esistesse.

Vengono selezionati gli attributi che appaiono come argomento dell’operatore aggregato o come argomento della clausola group by.

Page 41: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

RAGGRUPPAMENTO La tabella ottenuta viene poi analizzata,

dividendo le righe in insiemi caratterizzati dallo stesso valore degli attributi che compaiono come argomento della clausola group by.

Dopo che le righe sono state raggruppate in sottoinsiemi, l’operatore aggregato viene applicato separatamente su ogni sottoinsieme.

Page 42: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

RAGGRUPPAMENTO Il risultato dell’interrogazione è costituito

da una tabella con righe che contengono l’esito della valutazione dell’operatore aggregato affiancato al valore dell’attributo che è stato usato per l’aggregazione.

In un interrogazione con clausola group by, possono comparire come argomento della select solamente un sottoinsieme degli attributi usati nella clausola group by

Page 43: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

RAGGRUPPAMENTO

SELECT Colonna, Funzione FROM NomeTabellaGROUP BY Colonna

Page 44: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

ESERCIZIO Elencare la somma della lunghezza dei

film prodotti dopo 1930 per produttori.Movie (title, year, length, inColor, studioName, producerC#)

MovieExec (name, address, cert#, netWorth)

SELECT "name", SUM("length")FROM imdb."MovieExec", imdb."Movie"WHERE "producerC#"="cert#"GROUP BY "name";

Page 45: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

ESERCIZIO Trovare la somma della lunghezza dei film

prodotti da ciascuno studio di produzione:Movie (title, year, length, inColor, studioName, producerC#)

MovieExec (name, address, cert#, netWorth)

SELECT "studioName", SUM("length")FROM imdb."Movie"GROUP BY "studioName";

Page 46: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

HAVING Abbiamo visto come tramite la clausola

group by le righe possano venire raggruppate in sottoinsiemi.

Un applicazione può aver bisogno di considerare solo i sottoinsiemi che soddisfano certe condizioni.

Se le condizioni che i sottoinsiemi devono soddisfare sono verificabili a livello delle singole righe allora basta usare la clausola where

Page 47: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

HAVING Se le condizioni sono delle condizioni di

tipo aggregato, sarà necessario utilizzare un nuovo costrutto, la clausola having.

La clausola having descrive le condizioni che si devono applicare al termine dell’esecuzione di una interrogazione che fa uso della clausola group by.

Page 48: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

ESERCIZIO Estrarre i produttori per cui la media della

lunghezza dei film prodotti dopo 1980 sia superiore a 90.

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

MovieExec (name, address, cert#, netWorth)

SELECT MovieExec.nameFROM MovieExec, MovieWHERE Movie.year>’1980’ and cert#=producerC#GROUP BY MovieExec.nameHAVING AVG(‘lenght’)<‘90’

Page 49: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

HAVING Ogni sottoinsieme di righe costruite dalla

group by fa parte del risultato dell’interrogazione solo se il predicato argomento della having risulta soddisfatto.

L'uso della clausola HAVING consente di sottoporre al controllo di una o più condizioni i gruppi creati con la clausola Group by.

Page 50: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

HAVING La condizione scritta dopo Having

normalmente controlla il valore restituito dalle funzioni di aggregazione (Count, Sum, Avg, Min, Max).

SELECT Colonna, Funzione FROM NomeTabellaGROUP BY Colonna HAVING Condizione

Page 51: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

HAVING La sitassi permette anche la definizione di

interrogazioni con la clausola having senza una corrispondente clausola group by.

In questo caso, l’intero insieme di righe è trattato come un unico raggruppamento, ma questo ha in generale un limitato campo di applicabilità.

Se la condizione non è soddisfatta il risultato sarà vuoto.

Page 52: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

HAVING Come la clausola where, anche la clausola

having ammette come argomento un espressione booleana sui predicati semplici.

I predicati semplici sono normalmente confronti tra il risultato della valutazione di un operatore aggregato e una generica espressione.

Page 53: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

HAVING Solo i predicati in cui compaiono operatori

aggregati devono essere argomento della clausola having.

COUNT AVG SUM MAX MIN

Page 54: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

ESERCIZIO Estrarre i dipartimenti per cui la media

degli stipendi degli impiegati che lavorano nell’ufficio 20 è superiore a 25 milioni.

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

SELECT DipartFROM ImpiegatoWHERE Ufficio=20GROUP BY DipartHAVING AVG(Stipendio)<‘25’

Page 55: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

ESERCIZIO Estrarre i dipartimenti che spendono più di

100 milioni in stipendi.IMPIEGATO (Nome, Cognome, Dipart, Ufficio, Stipendio, Città)

SELECT Dipart, sum (Stipendio) as SommaStipendiFROM ImpiegatoGROUP BY DipartHAVING AVG(Stipendio)>‘100’

Page 56: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

ESERCIZIO Estrarre i produttori per cui il numero dei

film prodotti negli anni 90 sia superiore a 5.

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

MovieExec (name, address, cert#, netWorth)

SELECT MovieExec.nameFROM MovieExec, MovieWHERE (Movie.year>’1989’ and Movie.year<’1999’) and cert#=producerC#GROUP BY MovieExec.nameHAVING COUNT(title)>5

Page 57: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

ESERCIZIO Estrarre i film a cui hanno partecipato

almento 5 attrici.MovieStar (name, address, gender, birthdate) StarsIn (movieTitle, movieYear, starName)

SELECT movieTitle, Count(starName)FROM StarsIn, MovieStarWHERE (starName=name and gender=‘f’)GROUP BY movieTitleHAVING Count(starName)> 5

Page 58: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

ESERCIZIO Trovare per ogni star apparsa in almeno 3

film, il nome dell’attore, l’anno e il titolo del primo film. (output name e year).

StarsIn (movieTitle, movieYear, starName)

SELECT "starName", “movieTitle”, MIN("movieYear")FROM imdb."StarsIn"GROUP BY "starName“HAVING COUNT ("movieYear")>=3;

Page 59: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

ESERCIZIO Who were the male stars in Meet the

Fockers?StarsIn (movieTitle, movieYear, starName)

MovieStar (name, address, gender, birthdate)

SELECT nameFROM imdb."MovieStar", imdb."StarsIn“WHERE "gender" = 'M' AND"name" = "starName" AND"movieTitle" = 'Meet the Fockers';

Page 60: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

ESERCIZIO Trovare il nome e il netWorth dei

produttori più ricchi.MovieExec (name, address, cert#, netWorth)

SELECT "name", "netWorth"FROM imdb."MovieExec"WHERE "netWorth" = (SELECT MAX("netWorth") FROM imdb."MovieExec");

Page 61: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

ESERCIZIO Estrarre gli attori che hanno partecipato a

un numero film la cui lunghezza totale supera i 500 minuti.

Movie (title, year, length, inColor, studioName, producerC#)StarsIn (movieTitle, movieYear, starName)

SELECT starName,FROM StarsIn, MovieWHERE (movieTile=title)GROUP BY starNameHAVING SUM(length)>500

Page 62: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

ESERCIZIO Quali produttori hanno il networth

maggiore del produttore dell’ultimo Frankenstein?

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

MovieExec (name, address, cert#, netWorth)

SELECT name,FROM MovieExecWHERE networth> (select networth From MovieExec, MovieWhere title=‘Frankenstein’ and producerc#=cert# and year> ALL( Select year from MovieWhere title =‘Frankenstein’ )

Page 63: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

ESERCIZIO In quale anno la maggior parte dei film è

stato prodotto? Con una query singola visualizzare l’anno e il numero dei film.

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

SELECT "Rel"."year", "Rel"."Num“FROM ( SELECT "year", COUNT ("title") as "Num“ FROM imdb."Movie“ GROUP BY "year") as "Rel“WHERE "Rel"."Num"=(SELECT MAX("Rel1"."Num") FROM (SELECT COUNT ("title") as "Num“FROM imdb."Movie“ GROUP BY "year") as "Rel1");

Page 64: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

ESERCIZIO Trovare tutti i produttori con networth

minore del 5% della media di tutti i pruduttori.

MovieExec (name, address, cert#, netWorth)

SELECT "name", "netWorth“FROM imdb."MovieExec“WHERE "netWorth" < (SELECT AVG("netWorth") FROM imdb."MovieExec")/20;

Page 65: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

ESERCIZIO Quali films sono più lunghi di “City by the

Sea”?Movie (title, year, length, inColor, studioName, producerC#)

SELECT M1.titleFROM Movie as M1, Movie as M2WHEREM2.title= ‘City by the Sea’ and M1.lenght>M2.lenght

Page 66: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

ESERCIZIO Quali star appaiono nei film prodotti dallo

studio ‘DreamWorks SKG [us]’ in 2002?Movie (title, year, length, inColor, studioName, producerC#)

StarsIn (movieTitle, movieYear, starName)

SELECT starNamefrom imdb."StarsIn", imdb."Movie“WHERE "title"="movieTitle" and "studioName"='DreamWorks SKG [us]' and "year"=2002;

Page 67: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

ESERCIZIO Trovare per ogni star apparsa in almeno 3 film, il

nome dell’attore, l’anno e il titolo del primo film. Se un attore ha più di un film nel primo anno allora visualizzare tutti questi film.

StarsIn (movieTitle, movieYear, starName)

SELECT "starName", "movieTitle", "movieYear“FROM imdb."StarsIn"WHERE ("starName", "movieYear") IN (SELECT "starName", MIN("movieYear")FROM imdb."StarsIn“GROUP BY "starName“HAVING COUNT ("movieYear")>=3);

Page 68: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

Compito da casa: Products

Trovare velocità media di PC Trovare velocità media di laptop che costano più di 2000 Trovare prezzo medio di PC fatti da produttore “A” Trovare prezzo medio di PC e i laptop fatti da produttore “D” Per ogni velocità di PC, trovare prezzo medio di PC Per ogni produttore, trovare medio di screen di laptop Trovare i produttori che fanno almeno tre modelli differenti di

PC Per ogni produttore di PC, trovare prezzo massimo di PC Per ogni PC con speed>800, trovare il prezzo medio Trovare valore medio di HD di PC per quelli produttori che

fanno i printers

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

Page 69: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

Compito da casa: Navi

Trovere il numero di classi di navi Trovare numGuns medio per ogni classe di navi Per ogni classe, trovare l’anno quando la prima

nave di quello classe stata lanciata (launched) Per ogni classe, trovare numero delle navi che si

sono affondati (result=“sunk”) in qualche battaglia Per ogni classe con almeno tre navi, trovare

numero delle navi di quello classe che si sono affondati in qualche battaglia

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

Page 70: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

ESERCIZIOEstrarre la capienza complessiva degli stadi

in cui si giocano le partite che hannno come prima squadra una nazione sudamericanan ( nota: ai fini della valutazione della capienza complessiva, si sommino le capienze associate a ciascuna gara, anche se più gare si svolgono nello stesso stadio)Stadio(Nome,Citta,Capienza)Incontro(NomeStadio,Data,Ora,S1,S2)Nazionale(Paese,Continente,Categoria)

Page 71: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

ESERCIZIO

Select SUM(Capienza)

From Stadio join Incontro on Nome= NomeStadio Where S1 in Select Paese from Nazionale where Continente=‘Sudamerica’

Page 72: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

ESERCIZIOEstrarre i nomi degli stai in cui non gioca

nessuna nazionale europea:Stadio(Nome,Citta,Capienza)

Incontro(NomeStadio,Data,Ora,S1,S2)

Nazionale(Paese,Continente,Categoria)

Select Nome from Stadio where Nome not in (Select NomeStadio from Incontro where (S1 in (Select Paese from Nazionale where Continente=‘Europa’)) Or (S2 in (Select Paese from Nazionale where Continente=‘Europa’))

Page 73: SQL: Lezione 5 Nataliya Rassadko rassadko@disi.unitn.it

ESERCIZIOEstrarre i nomi dei proprietari di solo moto

giapponesi di almeno due marche diverseMoto(Targa,Cilindrata,Marca,Nazione,Tasse)

Proprietario(Nome,Targa)

Select Nome from Proprietario join Moto on Proprietario.Targa=Moto.Targa where Nome not in (Select Nome from Proprietario join Moto on Proprietario.Targa=Moto.Targa where Nazione<>’Giappone’) group by Nome having count(distinct Marca)>=2