Schema relazionale
- NAZIONI(NomeN, Superficie, Popolazione, Capitale, Continente)
- CONTINENTI(Sigla, NomeCon)
- CITTÀ(IdCittà, NomeC, Popolazione, Nazione)
- MARI(NomeM, Oceano)
- FIUMI(NomeF, Lunghezza, Affluente, Immissario, Foce)
- LAGHI(NomeL, Superficie)
- LITORALE(Nazione, Mare)
- ATTRAVERSA(Nazione, Fiume)
- PRESENZA(Nazione, Lago)
- CONFINE(Nazione1, Nazione2)
Query
3.1.1: Creazione della tabella FIUMI
1 2 3 4 5 6 |
CREATE TABLE FIUMI ( NomeF CHAR(20), Lunghezza INT, PRIMARY KEY (NomeF) ) |
3.1.2: Creazione della tabella NAZIONI
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE NAZIONI ( NomeN CHAR(20), Continente CHAR(2), Capitale INT, Superficie INT, Popolazione INT, PRIMARY KEY(NomeN), UNIQUE (Capitale), FOREIGN KEY(Continente) REFERENCES CONTINENTI(Sigla), FOREIGN KEY(Capitale) REFERENCES CITTÀ(IdCittà) ) |
3.1.3: Creazione della tabella ATTRAVERSA
1 2 3 4 5 6 7 8 |
CREATE TABLE ATTRAVERSA ( Nazione CHAR(20), Fiume CHAR(20), PRIMARY KEY(Nazione,Fiume), FOREIGN KEY(Nazione) REFERENCES NAZIONI(NomeN), FOREIGN KEY(Fiume) REFERENCES FIUMI(NomeF) ) |
3.5: Inserimento dei dati di alcuni laghi
1 2 3 4 |
INSERT INTO LAGHI (NomeL , Superficie) VALUES ('Huron' , 59000 ), ('Lago di Garda', 370 ), ('Lago Superiore', 82000 ) |
3.6: Modifica la popolazione di una città
1 2 3 |
UPDATE CITTÀ SET Popolazione = 807000 WHERE NomeC = 'Marsiglia' |
3.7: Correzione del nome del Danubio (scritto danubio) nella tabella ATTRAVERSA
1 2 3 |
UPDATE ATTRAVERSA SET Fiume = 'Danubio' WHERE Fiume = 'danubio' |
3.8.1: Cancellazione dei dati del Mar Nero dalla tabella MARI
1 2 |
DELETE FROM MARI WHERE NomeM = 'Mar Nero' |
3.8.2: Cancellazione dei dati di tutte le città della Germania dalla tabella CITTÀ
1 2 |
DELETE FROM CITTÀ WHERE Nazione = 'Germania' |
3.9.1: I laghi
1 2 |
SELECT * FROM LAGHI |
3.9.2: Gli oceani
1 2 3 |
SELECT NomeM FROM MARI WHERE Oceano = true |
3.9.3: Città italiane: nome e popolazione
1 2 3 |
SELECT NomeC, Popolazione FROM CITTÀ WHERE Nazione = 'Italia' |
1 2 3 4 |
SELECT NomeC, Popolazione FROM CITTÀ WHERE Nazione = 'Italia' ORDER BY NomeC |
1 2 3 4 |
SELECT NomeC, Popolazione FROM CITTÀ WHERE Nazione = 'Italia' ORDER BY Popolazione DESC; |
3.10: Nazioni in cui ci sono città con almeno 2000000 di abitanti, senza ripetizioni
1 2 3 |
SELECT DISTINCT Nazione FROM CITTÀ WHERE Popolazione >= 2000000 |
3.11: Nazioni con la densità di popolazione
1 2 |
SELECT NomeN AS Nazione, Superficie, Popolazione, (Popolazione/Superficie) AS Densità FROM NAZIONI; |
3.12: Gli oceani
1 2 3 |
SELECT NomeM AS Oceani FROM MARI WHERE Oceano = true |
Città di una certa nazione: nome e popolazione
1 2 3 |
SELECT NomeC AS Città, Popolazione FROM CITTÀ WHERE Nazione = [Nazione?] |
Nazioni con città di almeno 2.000.000 di abitanti
1 2 3 |
SELECT Nazione FROM CITTÀ WHERE Popolazione >= 2000000 |
Città italiane con popolazione inferiore a 1.000.000
1 2 3 4 |
SELECT NomeC AS Città, Popolazione FROM CITTÀ WHERE (Nazione = 'Italia') AND (Popolazione < 2000000) ORDER BY NomeC |
3.15.1: Città per cui NON è noto il valore di popolazione
1 2 3 |
SELECT NomeC AS Città FROM CITTÀ WHERE Popolazione IS NULL |
3.15.2: Città per cui è noto il valore di popolazione
1 2 3 |
SELECT NomeC AS Città FROM CITTÀ WHERE Popolazione IS NOT NULL |
3.16.1: Nazioni con superficie compresa tra 300.000 e 600.000
1 2 3 |
SELECT NomeN AS Nazione FROM NAZIONI WHERE Superficie BETWEEN 300000 AND 600000 |
3.16.2: …
1 2 3 |
SELECT NomeN AS Nazione FROM NAZIONI WHERE (Superficie >= 300000) AND (Superficie <= 600000) |
3.17: Città italiane, francesi, tedesche
1 2 3 |
SELECT NomeC AS Città FROM CITTÀ WHERE Nazione IN('Italia';'Francia';'Germania') |
3.18: Nazioni con il nome che comincia con la lettera A
1 2 3 |
SELECT NomeN AS Nazione FROM NAZIONI WHERE NomeN LIKE "A*" |
3.19.1: Nazioni in ordine crescente di superficie
1 2 3 |
SELECT NomeN AS Nazione, Superficie FROM NAZIONI ORDER BY Superficie ASC |
1 2 3 |
SELECT NomeN AS Nazione, Superficie FROM NAZIONI ORDER BY Superficie; |
3.19.2: Città ordinate per nazioni e in ordine alfabetico
1 2 3 |
SELECT NomeC AS Città, Nazione FROM CITTÀ ORDER BY Nazione, NomeC |
3.19.3: Nazioni in ordine decrescente di densità di popolazione
1 2 3 |
SELECT NomeN AS Nazione, (Popolazione/Superficie) AS Densità FROM NAZIONI ORDER BY 2 DESC; |
1 2 3 |
SELECT NomeN AS Nazione, (Popolazione/Superficie) AS Densità FROM NAZIONI ORDER BY (Popolazione/Superficie) DESC; |
3.20.1: Numero delle città
1 2 |
SELECT Count(*) FROM CITTÀ; |
3.20.2: Numero delle città italiane
1 2 3 |
SELECT Count(*) FROM CITTÀ WHERE Nazione = 'Italia' |
3.20.3: Numero delle città italiane per cui è noto il valore della popolazione
1 2 3 |
SELECT Count(Popolazione) FROM CITTÀ WHERE Nazione = 'Italia' |
3.20.4: …
1 2 3 |
SELECT Count(*) FROM CITTÀ WHERE (Nazione = 'Italia') AND (Popolazione IS NOT NULL) |
3.21: Numero di nazioni con almeno una città
1 2 3 4 5 6 |
ELENCO_CITTÀ: SELECT DISTINCT Nazione FROM CITTÀ; SELECT Count(*) FROM ELENCO_CITTÀ |
3.22: Popolazione europea
1 2 3 |
SELECT Sum(Popolazione) FROM Nazioni WHERE Continente = 'EU' |
3.23: Densità media di popolazione delle nazioni europee
1 2 3 |
SELECT Avg(Popolazione/Superficie) AS [Densità media] FROM Nazioni WHERE Continente = 'EU' |
3.24: Superficie massima e densità minima delle nazioni
1 2 |
SELECT Max(Superficie), Min(Popolazione/Superficie) FROM Nazioni |
3.25: Nazioni con popolazione massima e minima delle rispettive città
1 2 3 |
SELECT Nazione, Max(Popolazione), Min(Popolazione) FROM Città GROUP BY Nazione; |
Nazioni con popolazione media delle città
1 2 3 |
SELECT Nazione, Avg(Popolazione) FROM Città GROUP BY Nazione; |
3.26: Nazioni con popolazione media delle città (solo se la media è superiore a 1.500.000)
1 2 3 4 |
SELECT Nazione, Avg(Popolazione) FROM Città GROUP BY Nazione HAVING Avg(Popolazione) > 1500000 |
3.27.1: Nazioni e continente di appartenenza
1 2 3 |
SELECT NomeN AS [Nome Nazione], NomeCon AS [Nome Continente] FROM NAZIONI, CONTINENTI WHERE Continente = Sigla |
3.27.3: Nazioni e continente di appartenenza
1 2 3 |
SELECT NomeN, NomeCon FROM NAZIONI, CONTINENTI WHERE NAZIONI.Continente = CONTINENTI.Sigla |
3.27.4: …
1 2 3 |
SELECT NomeN AS [Nome Nazione], NomeCon AS [Nome Continente] FROM NAZIONI AS N, CONTINENTI AS C WHERE N.Continente = C.Sigla |
3.28: Fiumi che sono affluenti di altri con la lunghezza dei due fiumi
1 2 3 |
SELECT AFFLUENTI.NomeF, AFFLUENTI.Lunghezza, FIUMI.NomeF, FIUMI.Lunghezza FROM FIUMI AS AFFLUENTI, FIUMI WHERE AFFLUENTI.NomeF = FIUMI.Affluente |
3.29: Le nazioni europee con popolazione, capitale e popolazione della capitale
1 2 3 |
SELECT NomeN, NAZIONI.Popolazione, NomeC, CITTÀ.Popolazione FROM CONTINENTI, NAZIONI, CITTÀ WHERE (Sigla = Continente) AND (Capitale = IdCittà) AND (NomeCon = 'Europa') |
3.30: Le nazioni, con superficie, attraversate dal Danubio
1 2 3 |
SELECT NomeN, Superficie FROM NAZIONI, ATTRAVERSA WHERE (NomeN = Nazione) AND (Fiume = 'Danubio') |
3.31: Le nazioni con il continente di appartenenza
1 2 |
SELECT NomeN, NomeCon FROM NAZIONI INNER JOIN CONTINENTI ON NAZIONI.Continente = CONTINENTI.Sigla |
3.32.1: Le nazioni, con superficie minore di 1000, con il continente di appartenenza
1 2 3 |
SELECT NomeN, NomeCon FROM NAZIONI INNER JOIN CONTINENTI ON NAZIONI.Continente = CONTINENTI.Sigla WHERE Superficie < 1000 |
3.32.2: …
1 2 3 |
SELECT NomeN, NomeCon FROM NAZIONI, CONTINENTI WHERE (Continente = Sigla) AND (Superficie < 1000) |
3.33.1: Tutte le città e le eventuali nazioni
1 2 |
SELECT NomeC, Nazione FROM CITTÀ LEFT JOIN NAZIONI ON CITTÀ.Nazione = NAZIONI.NomeN |
3.33.2: Tutte le nazioni e le eventuali capitali
1 2 |
SELECT NomeN, NomeC FROM CITTÀ RIGHT JOIN NAZIONI ON CITTÀ.IdCittà = NAZIONI.Capitale |
3.34: Le città che mancano della nazione
1 2 3 |
SELECT NomeC FROM CITTÀ LEFT JOIN NAZIONI ON CITTÀ.Nazione = NAZIONI.NomeN WHERE NomeN IS NULL |
3.35: Le nazioni con continente, superficie, popolazione, capitale e popolazione della capitale
1 2 3 |
SELECT NomeN, NomeCon, Superficie, NAZIONI.Popolazione, NomeC, CITTÀ.Popolazione FROM (NAZIONI INNER JOIN CONTINENTI ON NAZIONI.Continente = CONTINENTI.Sigla) INNER JOIN CITTÀ ON NAZIONI.Capitale = CITTÀ.IdCittà |
3.36: La nazione con la superficie massima
1 2 3 4 |
SELECT NomeN FROM NAZIONI WHERE Superficie = (SELECT MAX(Superficie) FROM NAZIONI) |
oppure
1 2 3 4 5 6 7 |
MASSIMO: SELECT Max(Superficie) FROM NAZIONI; SELECT NomeN FROM NAZIONI WHERE Superficie = MASSIMO |
oppure…
1 2 3 |
SELECT TOP 1 NomeN FROM NAZIONI ORDER BY Superficie DESC |
3.37: Elenco delle nazioni per cui la popolazione della capitale è inferiore alla popolazione media delle città, con popolazione della capitale
1 2 3 4 |
SELECT C.Nazione, C.Popolazione FROM NAZIONI INNER JOIN CITTÀ AS C ON NAZIONI.Capitale = C.IdCittà WHERE C.Popolazione < (SELECT Avg(Popolazione) FROM CITTÀ) |
Elenco delle nazioni per cui la popolazione della capitale è inferiore alla popolazione media delle città, con popolazione della capitale e scarto
1 2 3 4 5 |
SELECT C.Nazione, C.Popolazione, (SELECT Avg(Popolazione) FROM CITTÀ)-C.Popolazione FROM NAZIONI INNER JOIN CITTÀ AS C ON NAZIONI.Capitale=C.IdCittà WHERE C.Popolazione < (SELECT AVG(Popolazione) FROM CITTÀ); |
3.38: Elenco delle nazioni bagnate da almeno 2 mari, con i nomi dei mari
1 2 3 4 5 6 7 |
SELECT L.Nazione, L.Mare FROM LITORALE AS L WHERE L.Nazione IN (SELECT Nazione FROM LITORALE GROUP BY Nazione HAVING COUNT(*) > 1) ORDER BY L.Nazione; |
1 2 3 4 5 6 7 8 9 10 |
NAZIONI_BAGNATE: SELECT Nazione FROM LITORALE GROUP BY Nazione HAVING Count(*) > 1; SELECT L.Nazione, L.Mare FROM LITORALE AS L WHERE L.Nazione IN NAZIONI_BAGNATE ORDER BY L.Nazione; |
3.39: Nome delle città della Francia con popolazione superiore a quella di almeno una città della Germania
1 2 3 4 5 6 |
SELECT NomeC FROM CITTÀ WHERE (Nazione = 'Francia') AND (Popolazione > ANY SELECT Popolazione FROM CITTÀ WHERE Nazione = 'Germania') |
1 2 3 4 5 6 |
SELECT NomeC FROM CITTÀ WHERE (Nazione = 'Francia') AND (Popolazione > SELECT Min(Popolazione) FROM CITTÀ WHERE Nazione = 'Germania') |
3.40: Nome delle città della Francia con popolazione superiore a quella di tutte le città della Germania
1 2 3 4 5 6 |
SELECT NomeC FROM CITTÀ WHERE (Nazione = 'Francia') AND (Popolazione > ALL SELECT Popolazione FROM CITTÀ WHERE Nazione = 'Germania') |
1 2 3 4 5 6 |
SELECT NomeC FROM CITTÀ WHERE (Nazione = 'Francia') AND (Popolazione > SELECT Max(Popolazione) FROM CITTÀ WHERE Nazione = 'Germania') |
3.41: Nome delle nazioni che hanno superficie superiore alla superficie media delle nazioni del continente a cui appartengono
1 2 3 4 5 |
SELECT N1.NomeN FROM NAZIONI AS N1 WHERE N1.Superficie > (SELECT AVG(N2.Superficie) FROM NAZIONI AS N2 WHERE N2.Continente = N1.Continente) |
1 2 3 4 5 6 7 8 |
SUP_MEDIE: SELECT Continente, Avg(Superficie) AS sup_media FROM NAZIONI GROUP BY Continente; SELECT NomeN, FROM NAZIONI INNER JOIN SUP_MEDIE ON NAZIONI.Continente = SUP_MEDIE.Continente WHERE NAZIONI.Superficie > SUP_MEDIE.sup_media |
3.42: Nome e continente delle nazioni che non sono bagnate da nessun mare
1 2 3 4 5 |
SELECT NomeN, NomeCon FROM NAZIONI INNER JOIN CONTINENTI WHERE NOT EXISTS (SELECT * FROM LITORALE WHERE Nazione = NomeN) |
oppure…
1 2 3 4 |
SELECT NomeN, NomeCon FROM NAZIONI INNER JOIN CONTINENTI WHERE NomeN NOT IN (SELECT Nazione FROM LITORALE) |
oppure…
1 2 3 4 5 6 7 8 |
NUMERO_MARI: SELECT Nazione, Count(*) AS numero_mari FROM LITORALE GROUP BY Nazione; SELECT NomeN, NomeCon FROM NAZIONI INNER JOIN CONTINENTI WHERE NomeN NOT IN NUMERO_MARI |
3.43: Elenco delle città con popolazione maggiore o uguale a 1.000.000 per ogni nazione in cui esiste almeno una città con popolazione inferiore a 10.000
1 2 3 4 5 6 |
SELECT C1.NomeC, C1.Popolazione FROM CITTÀ AS C1 WHERE (C1.Popolazione >= 1000000) AND (EXISTS SELECT * FROM CITTÀ AS C2 WHERE (C1.Nazione = C2.Nazione) AND (C2.Popolazione < 10000)) |
oppure…
1 2 3 |
SELECT DISTINCT C1.NomeC, C1.Popolazione FROM CITTÀ AS C1 INNER JOIN CITTÀ AS C2 ON C1.Nazione = C2.Nazione WHERE (C1.Popolazione >= 1000000) AND (C2.Popolazione < 10000) |
3.44: Nazioni dell’Europa che sono bagnate dal mare
1 2 3 4 5 |
SELECT NomeN FROM NAZIONI INNER JOIN CONTINENTI ON NAZIONI.Continente = CONTINENTI.NomeCon WHERE (NomeCon = 'Europa') AND (NomeN IN SELECT Nazione FROM LITORALE) |
oppure…
1 2 3 4 |
SELECT DISTINCT NomeN FROM (NAZIONI INNER JOIN CONTINENTI ON NAZIONI.Continente = CONTINENTI.NomeCon) INNER JOIN LITORALE ON NAZIONI.NomeN = LITORALE.Nazione WHERE NomeCon = 'Europa' |
3.45: Nazioni dell’Europa che NON sono bagnate dal mare
1 2 3 4 5 |
SELECT NomeN FROM NAZIONI INNER JOIN CONTINENTI ON NAZIONI.Continente = CONTINENTI.NomeCon WHERE (NomeCon = 'Europa') AND (NomeN NOT IN SELECT Nazione FROM LITORALE) |
3.46: Inserimento nella tabella Città delle città contenute nella tabella NuoveCittà
1 2 3 |
INSERT INTO CITTÀ(NomeC, Nazione) SELECT NC.NuovaC, NC.Nazione FROM NuovaCittà AS NC |
3.47: Cancellazione dalla tabella Nazioni di tutte le nazioni dell’America
1 2 3 4 |
DELETE FROM NAZIONI WHERE Continente IN (SELECT Sigla FROM CONTINENTI WHERE NomeCon LIKE 'America%') |
3.48: Le città italiane
1 2 3 4 5 6 7 |
CittàItaliane: SELECT NomeC, Popolazione FROM CITTÀ WHERE Nazione = 'Italia'; SELECT * FROM CittàItaliane; |
3.49.1: Le nazioni con il numero di laghi e la loro superficie totale
1 2 3 4 |
LaghiNazione: SELECT Nazione, Count(*) AS NumLaghi, Sum(Superficie) AS SupTotale FROM LAGHI INNER JOIN PRESENZA ON LAGHI.NomeL = PRESENZA.Lago GROUP BY Nazione |
3.49.2: La nazione che la più grande superficie totale di laghi.
1 2 3 4 |
SELECT Nazione FROM LaghiNazione WHERE SupTotale = (SELECT Max(SupTotale) FROM LaghiNazione) |
3.49.3: Il rapporto medio tra superficie dei laghi e superficie della nazione.
1 2 |
SELECT Avg(LN.SupTotale/N.Superficie) FROM NAZIONI AS N INNER JOIN LaghiNazione LN ON N.NomeN=LN.Nazione |