Gestione dati e archivi - Esercizi SQL

Schemi relazionali e query SQL

Schema relazionale: Marinai e barche

Sailors(sid, sname, rating, age), Boats(bid, bname, color), 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
• La 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

Query SQL relative ai marinai e barche

Trovare i nomi dei marinai che hanno un imbarco su almeno una nave

SELECT DISTINCT sname
FROM Sailors, Reserves
WHERE Sailors.sid=Reserves.sid;

Trovare i nomi dei marinai che sono imbarcati sulla nave 103

SELECT sname
FROM Sailors, Reserves
WHERE Sailors.sid=Reserves.sid AND Reserves.bid='103';

Trovare i nomi dei marinai che sono imbarcati su una nave rossa

SELECT sname
FROM Sailors, Reserves, Boats
WHERE Sailors.sid=Reserves.sid AND Reserves.bid=Boats.bid AND Boats.color='red';

Trovare i nomi dei marinai che sono imbarcati su una nave rossa o verde

SELECT DISTINCT sname
FROM Sailors, Reserves, Boats
WHERE Sailors.sid=Reserves.sid AND Reserves.bid=Boats.bid AND (Boats.color='red' OR Boats.color='green');

Schema relazionale: Corsi d'acqua e comuni

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

La relazione CORSI_ACQUA contiene informazioni relative ai fiumi (i.e., nome fiume, lunghezza, profondità media): nome fiume è chiave primaria della relazione.

La relazione COMUNI contiene informazioni relative ai comuni (i.e., Nome comune, regione e popolazione): nome comune è chiave primaria della relazione.

La relazione BAGNA contiene informazioni sui fiumi che attraversano le città: NomeF e NomeC sono chiavi primarie e chiavi esterne rispettivamente da CORSI_ACQUA e COMUNI.

Query SQL relative ai corsi d'acqua e comuni

Determinare i nomi dei comuni lombardi, con popolazione maggiore di 500000 abitanti, percorsi da almeno un corso d'acqua

SELECT DISTINCT NomeC
FROM BAGNA, COMUNI
WHERE COMUNI.NomeC=BAGNA.NomeC AND Regione='Lombardia' AND Popolazione>500000;

Determinare i nomi dei fiumi con lunghezza compresa tra 20 e 30 Km che attraversano almeno un comune in Liguria

SELECT CORSI_ACQUA.NomeF
FROM CORSI_ACQUA, BAGNA, COMUNI
WHERE CORSI_ACQUA.NomeF=BAGNA.NomeF AND COMUNI.NomeC=BAGNA.NomeC AND Regione='Liguria' AND Lunghezza BETWEEN 20 AND 30;

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

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

oppure

SELECT DISTINCT COMUNI.NomeC
FROM COMUNI, BAGNA
WHERE COMUNI.NomeC=BAGNA.NomeC AND NomeF IN (SELECT NomeF FROM CORSI_ACQUA WHERE Lunghezza>=3);

Schema relazionale: Quadri, pittori e musei

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

Query SQL relativa ai quadri

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 Pittore.Cognome = 'Dalì';