Gestione dati e archivi - Esercizi SQL

FROM BAGNA, COMUNIWHERE COMUNI.NomeC=BAGNA.NomeCAND Regione='Lombardia'AND Popolazione> 500000;

• CORSI_ACQUA(NomeF,Lunghezza, Profondita_media)
• COMUNI(NomeC, Regione, Popolazione)

CORSI_ACQUA COMUNI

BAGNA(NomeF ,NomeC )

Determinare i nomi dei fiumi con lunghezzacompresa tra 20 e 30 Km che attraversano almenoun comune in Liguria

SELECT CORSI_ACQUA.NomeFFROM CORSI_ACQUA, BAGNA, COMUNIWHERE CORSI_ACQUA.NomeF=BAGNA.NomeFAND COMUNI.NomeC=BAGNA.NomeCAND Regione='Liguria'AND Lunghezza BETWEEN 20 AND 30;

• CORSI_ACQUA(NomeF,Lunghezza, Profondita_media)
• COMUNI(NomeC, Regione, Popolazione)

CORSI_ACQUA COMUNI

BAGNA(NomeF ,NomeC )

Determinare i nomi dei comuni che non sono percorsi da alcun corso d'acquacon lunghezza inferiore ai 3 Km

SELECT DISTINCT COMUNI.NomeCFROM COMUNI, BAGNAWHERE COMUNI.NomeC=BAGNA.NomeCAND NomeF NOT IN (SELECT NomeF FROM CORSI_ACQUA WHERE Lunghezza<3);

oppure

SELECT DISTINCT COMUNI.NomeCFROM COMUNI,

BAGNAWHERE COMUNI.NomeC=BAGNA.NomeCAND NomeF IN (SELECT NomeF FROM CORSI_ACQUA WHERE Lunghezza>=3);

• Quadro(Nome,Anno,periodo,id-museo,autore)
• Pittore(CF,Nome,Cognome,Nazionalità,Anno-di-nascita)
• Museo(Id,Nome,Annodicostruzione,Città,Nazione,Cognomedirettore)

1. Determinare i nomi di tutti i quadri dipinti da Salvator Dalì
2. Determinare i nomi dei musei che espongono quadri di DeChirico

• Pittore(CF,Nome,Cognome,Nazionalità,Anno-di-nascita)
• Museo(Id,Nome,Annodicostruzione,Città,Nazione,Cognomedirettore)

Museo Pittore

• Quadro(Nome,Anno,periodo,id-museo ,autore )

La relazione Pittore contiene informazioni relative ai pittori (i.e., codicefiscale, nome, cognome, nazionalità e anno di nascita): chiave primaria ècodice fiscale

La relazione Museo contiene informazioni relative ai musei (i.e.,codice, nome, anno di costruzione, città, nazione e cognome deldirettore): Id, ovvero il codice del museo

è chiave primaria dellarelazione• La relazione Quadro contiene informazioni relative ai quadri (i.e., nomedel quadro, anno di realizzazione, periodo artistico): chiave primaria lacoppia nome del quadro e autore, dove autore è chiave esterna dallarelazione Pittore. Inoltre id-museo è chiave esterna da Museo••

Pittore(CF,Nome,Cognome,Nazionalità,Anno-di-nascita)• Museo(Id,Nome,Annodicostruzione,Città,Nazione,Cognomedirettore)Museo Pittore• Quadro(Nome,Anno,periodo,id-museo ,autore )

1) Determinare i nomi di tutti i quadri dipinti da Salvator Dalì

SELECT Quadro.Nome
FROM Quadro, Pittore
WHERE Quadro.autore= Pittore.CF AND
Pittore.Nome=‘Salvator’ AND Cognome=‘Dalì’; 6

• Pittore(CF,Nome,Cognome,Nazionalità,Anno-di-nascita)• Museo(Id,Nome,Annodicostruzione,Città,Nazione,Cognomedirettore)Museo Pittore• Quadro(Nome,Anno,periodo,id-museo ,autore )

2) Determinare i nomi dei

musei che espongono quadridi De Chirico

SELECT Museo.Nome

FROM Quadro, Pittore, Museo

WHERE Quadro.autore= Pittore.CF

AND Quadro.id-museo=Museo.id

AND Pittore.Cognome='De Chirico';

STUDENTI (Matricola, Nome, Cognome, Sesso, Diploma, Età)

Nome, Dipartimento, Orario Ricevimento)

PROFESSORI (CodiceProf, PROFESSORICORSI (Nome, Periodo, Aula, CodiceProf )

STUDENTI CORSI, Corso )

PIANI_DI_STUDIO (Matricola)

La relazione STUDENTI contiene informazioni relative agli studenti di una università (i.e., matricola, nome, cognome, sesso, diploma ed età). Chiave primaria è la matricola

La relazione PROFESSORI contiene informazioni relative ai professori di una università (i.e., codice, nome, cognome, dipartimento, orario ricevimento). Chiave primaria è il codice del professore

La relazione CORSI contiene informazioni relative ai corsi erogati dall'università (i.e., nome del corso, periodo, aula e codice del professore). Chiave primaria

è il codice del corso, mentre CodiceProf è chiave esterna daPROFESSORI• La relazione PIANI_DI_STUDIO contiene informazioni relative ai piani distudio degli studenti (i.e., matricola dello studente e codice del corsoselezionato). Chiave primaria è la coppia Matricola (matricola studente) eCorso (i.e., codice del corso), dove Matricola è chiave esterna daSTUDENTI e Corso è chiave esterna da CORSI 7STUDENTI (Matricola, Nome, Cognome, Sesso, Diploma, Età)PROFESSORI (CodiceProf, Nome, Dipartimento, OrarioRicevimento)PROFESSORICORSI (Nome, Periodo, Aula, CodiceProf )STUDENTI CORSIPIANI_DI_STUDIO (Matricola , Corso )

Trovare il numero di studenti che hanno nel piano distudi il corso di Basi di dati

SELECT COUNT(*)FROM PIANI_DI_STUDIOWHERE Corso = 'Basi di dati';

STUDENTI (Matricola, Nome, Cognome, Sesso, Diploma, Età)PROFESSORI (CodiceProf, Nome, Dipartimento, OrarioRicevimento)PROFESSORICORSI (Nome, Periodo, Aula, CodiceProf)

1. STUDENTI CORSIPIANI_DI_STUDIO (Matricola , Corso )

   Trovare il nome dello studente più giovane

   SELECT Nome
   FROM STUDENTI
   WHERE Età = (SELECT MIN(Età)
   FROM STUDENTI);

2. Dati gli schemi

   Customers(cid, cname, city)

   Agents(aid, aname, city)

   Products(pid, pname, city, quantity, price)

   Orders(ordnum, cid, aid, pid, qty, cost)

   Calcolare, per ogni tipo di prodotto, il numero di pezzi venduti

   SELECT pid, SUM(qty) AS total
   FROM orders
   GROUP BY pid;

3. La seguente query non è corretta. Perché?

   SELECT pid, cid, SUM(qty) AS total
   FROM orders
   GROUP BY pid;

   La funzione SUM calcola un singolo valore per ogni gruppo. È necessario che ogni attributo nella SELECT abbia valore univoco. In questo caso, l'attributo cid non verifica questa condizione.

4. Bisogna escludere per forza cid?

   No. Basta includere cid nel GROUP BY:

   SELECT pid, cid, SUM(qty) AS total
   FROM orders
   GROUP BY pid, cid;

   Calcola per ogni prodotto il numero di pezzi venduti

percliente

• La query seguente è sintatticamente corretta?

SELECT pid, SUM(qty)
FROM orders
WHERE sum(qty)>1000
GROUP BY pid;

• No. La funzione di aggregazione SUM non può apparire nella clausola WHERE. Inoltre, la condizione espressa nel WHERE elimina tuple prima che il GROUP BY faccia il partizionamento... 10... ossia, quando il query processor controlla la condizione nel WHERE, non ha ancora calcolato i valori della funzione SUM perché non ha ancora eseguito il GROUP BY

Sintassi corretta:

SELECT pid, SUM(qty)
FROM orders
GROUP BY pid
HAVING SUM(qty)>1000;

• Schema relazionale:

Sailors(sid, sname, rating, age),
Boats(bid, bname, color),
Sailors Boats Reserves(sid, bid, day)

• La relazione Sailors contiene informazioni relative ai marinai (i.e., codice, nome, livello ed età): sid è chiave primaria
• La relazione Boats contiene informazioni relative alle barche (i.e., codice, nome e colore): bid è chiave primaria

relazione Reserves contiene informazioni relative agli imbarchi (i.e., codice del marinaio, codice della barca in cui si è imbarcato e giorno di imbarco): sid e bid sono chiave primarie e chiavi esterne rispettivamente da Sailors e Boats

11. Trovare l'età media dei marinai con rating uguale a 10

SELECT AVG (age)
FROM Sailors
WHERE rating=10;

12. Trovare l'età del marinaio più giovane per ogni valore del rating

SELECT rating, MIN (age)
FROM Sailors
GROUP BY rating;

13. Trovare tra tutti i marinai con età maggiore di 18, l'età del marinaio più giovane per ogni livello di rating. Si è interessati solo a livelli di rating a cui sono associati almeno due marinai di età maggiore di 18

SELECT rating, MIN (age) as MinAge
FROM Sailors
WHERE age >= 18
GROUP BY rating
HAVING COUNT (*) > 1;

14. Trovare i marinai con rating più alto

SELECT sid
FROM Sailors
WHERE rating >= ALL (SELECT rating
FROM Sailors);

1. Per ogni regista, il numero di film diretti dopo il 1990

1. L'incasso totale di tutte le proiezioni dei suoi film
2. Per ogni film di S.Spielberg, il titolo del film, il numero totale di proiezioni a Pisa e l'incasso totale
3. Per ogni regista e per ogni attore, il numero di film del regista con l'attore
4. Il regista ed il titolo dei film in cui recitano meno di 6 attori
5. Per ogni film prodotto dopo il 2000, il codice, il titolo e l'incasso totale di tutte le sue proiezioni
6. Il numero di attori dei film in cui appaiono solo attori nati prima del 1970
7. Per ogni film di fantascienza, il titolo e l'incasso totale di tutte le sue proiezioni
8. Per ogni film di fantascienza il titolo e l'incasso totale di tutte le sue proiezioni successive al 1/1/01
9. Per ogni film di fantascienza che non è mai stato proiettato prima del 1/1/01 il titolo e l'incasso totale di tutte le sue proiezioni
10. Per ogni sala di Pisa, che nel mese di gennaio 2005 ha incassato più di 20000 il nome della sala

€,l’incasso totale (sempre del mese di gennaio 2005)12. I titoli dei film che non sono mai stati proiettati a Pisa

Soluzioni

1. SELECT Regista, count(*)

   FROM Film

   WHERE AnnoProduzione > 1990

   GROUP BY Regista;

2. SELECT Film.Regista, sum(p.Incasso) as IncassoTotale

   FROM Film, Proiezioni