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
CREATE TABLE FIUMI ( NomeF CHAR(20), Lunghezza INT, PRIMARY KEY (NomeF) )
3.1.2: Creazione della tabella NAZIONI
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
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
INSERT INTO LAGHI (NomeL , Superficie)
VALUES ('Huron' , 59000 ),
('Lago di Garda', 370 ),
('Lago Superiore', 82000 )
3.6: Modifica la popolazione di una città
UPDATE CITTÀ SET Popolazione = 807000 WHERE NomeC = 'Marsiglia'
3.7: Correzione del nome del Danubio (scritto danubio) nella tabella ATTRAVERSA
UPDATE ATTRAVERSA SET Fiume = 'Danubio' WHERE Fiume = 'danubio'
3.8.1: Cancellazione dei dati del Mar Nero dalla tabella MARI
DELETE FROM MARI WHERE NomeM = 'Mar Nero'
3.8.2: Cancellazione dei dati di tutte le città della Germania dalla tabella CITTÀ
DELETE FROM CITTÀ WHERE Nazione = 'Germania'
3.9.1: I laghi
SELECT * FROM LAGHI
3.9.2: Gli oceani
SELECT NomeM FROM MARI WHERE Oceano = true
3.9.3: Città italiane: nome e popolazione
SELECT NomeC, Popolazione FROM CITTÀ WHERE Nazione = 'Italia'
SELECT NomeC, Popolazione FROM CITTÀ WHERE Nazione = 'Italia' ORDER BY NomeC
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
SELECT DISTINCT Nazione FROM CITTÀ WHERE Popolazione >= 2000000
3.11: Nazioni con la densità di popolazione
SELECT NomeN AS Nazione, Superficie, Popolazione, (Popolazione/Superficie) AS Densità FROM NAZIONI;
3.12: Gli oceani
SELECT NomeM AS Oceani FROM MARI WHERE Oceano = true
Città di una certa nazione: nome e popolazione
SELECT NomeC AS Città, Popolazione FROM CITTÀ WHERE Nazione = [Nazione?]
Nazioni con città di almeno 2.000.000 di abitanti
SELECT Nazione FROM CITTÀ WHERE Popolazione >= 2000000
Città italiane con popolazione inferiore a 1.000.000
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
SELECT NomeC AS Città FROM CITTÀ WHERE Popolazione IS NULL
3.15.2: Città per cui è noto il valore di popolazione
SELECT NomeC AS Città FROM CITTÀ WHERE Popolazione IS NOT NULL
3.16.1: Nazioni con superficie compresa tra 300.000 e 600.000
SELECT NomeN AS Nazione FROM NAZIONI WHERE Superficie BETWEEN 300000 AND 600000
3.16.2: …
SELECT NomeN AS Nazione FROM NAZIONI WHERE (Superficie >= 300000) AND (Superficie <= 600000)
3.17: Città italiane, francesi, tedesche
SELECT NomeC AS Città
FROM CITTÀ
WHERE Nazione IN('Italia';'Francia';'Germania')
3.18: Nazioni con il nome che comincia con la lettera A
SELECT NomeN AS Nazione FROM NAZIONI WHERE NomeN LIKE "A*"
3.19.1: Nazioni in ordine crescente di superficie
SELECT NomeN AS Nazione, Superficie FROM NAZIONI ORDER BY Superficie ASC
SELECT NomeN AS Nazione, Superficie FROM NAZIONI ORDER BY Superficie;
3.19.2: Città ordinate per nazioni e in ordine alfabetico
SELECT NomeC AS Città, Nazione FROM CITTÀ ORDER BY Nazione, NomeC
3.19.3: Nazioni in ordine decrescente di densità di popolazione
SELECT NomeN AS Nazione, (Popolazione/Superficie) AS Densità FROM NAZIONI ORDER BY 2 DESC;
SELECT NomeN AS Nazione, (Popolazione/Superficie) AS Densità FROM NAZIONI ORDER BY (Popolazione/Superficie) DESC;
3.20.1: Numero delle città
SELECT Count(*) FROM CITTÀ;
3.20.2: Numero delle città italiane
SELECT Count(*) FROM CITTÀ WHERE Nazione = 'Italia'
3.20.3: Numero delle città italiane per cui è noto il valore della popolazione
SELECT Count(Popolazione) FROM CITTÀ WHERE Nazione = 'Italia'
3.20.4: ...
SELECT Count(*) FROM CITTÀ WHERE (Nazione = 'Italia') AND (Popolazione IS NOT NULL)
3.21: Numero di nazioni con almeno una città
ELENCO_CITTÀ: SELECT DISTINCT Nazione FROM CITTÀ; SELECT Count(*) FROM ELENCO_CITTÀ
3.22: Popolazione europea
SELECT Sum(Popolazione) FROM Nazioni WHERE Continente = 'EU'
3.23: Densità media di popolazione delle nazioni europee
SELECT Avg(Popolazione/Superficie) AS [Densità media] FROM Nazioni WHERE Continente = 'EU'
3.24: Superficie massima e densità minima delle nazioni
SELECT Max(Superficie), Min(Popolazione/Superficie) FROM Nazioni
3.25: Nazioni con popolazione massima e minima delle rispettive città
SELECT Nazione, Max(Popolazione), Min(Popolazione) FROM Città GROUP BY Nazione;
Nazioni con popolazione media delle città
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)
SELECT Nazione, Avg(Popolazione) FROM Città GROUP BY Nazione HAVING Avg(Popolazione) > 1500000
3.27.1: Nazioni e continente di appartenenza
SELECT NomeN AS [Nome Nazione], NomeCon AS [Nome Continente] FROM NAZIONI, CONTINENTI WHERE Continente = Sigla
3.27.3: Nazioni e continente di appartenenza
SELECT NomeN, NomeCon FROM NAZIONI, CONTINENTI WHERE NAZIONI.Continente = CONTINENTI.Sigla
3.27.4: ...
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
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
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
SELECT NomeN, Superficie FROM NAZIONI, ATTRAVERSA WHERE (NomeN = Nazione) AND (Fiume = 'Danubio')
3.31: Le nazioni con il continente di appartenenza
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
SELECT NomeN, NomeCon FROM NAZIONI INNER JOIN CONTINENTI ON NAZIONI.Continente = CONTINENTI.Sigla WHERE Superficie < 1000
3.32.2: ...
SELECT NomeN, NomeCon FROM NAZIONI, CONTINENTI WHERE (Continente = Sigla) AND (Superficie < 1000)
3.33.1: Tutte le città e le eventuali nazioni
SELECT NomeC, Nazione FROM CITTÀ LEFT JOIN NAZIONI ON CITTÀ.Nazione = NAZIONI.NomeN
3.33.2: Tutte le nazioni e le eventuali capitali
SELECT NomeN, NomeC FROM CITTÀ RIGHT JOIN NAZIONI ON CITTÀ.IdCittà = NAZIONI.Capitale
3.34: Le città che mancano della nazione
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
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
SELECT NomeN
FROM NAZIONI
WHERE Superficie = (SELECT MAX(Superficie)
FROM NAZIONI)
oppure
MASSIMO: SELECT Max(Superficie) FROM NAZIONI; SELECT NomeN FROM NAZIONI WHERE Superficie = MASSIMO
oppure...
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
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
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
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;
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
SELECT NomeC
FROM CITTÀ
WHERE (Nazione = 'Francia') AND
(Popolazione > ANY SELECT Popolazione
FROM CITTÀ
WHERE Nazione = 'Germania')
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
SELECT NomeC
FROM CITTÀ
WHERE (Nazione = 'Francia') AND
(Popolazione > ALL SELECT Popolazione
FROM CITTÀ
WHERE Nazione = 'Germania')
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
SELECT N1.NomeN
FROM NAZIONI AS N1
WHERE N1.Superficie > (SELECT AVG(N2.Superficie)
FROM NAZIONI AS N2
WHERE N2.Continente = N1.Continente)
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
SELECT NomeN, NomeCon
FROM NAZIONI INNER JOIN CONTINENTI
WHERE NOT EXISTS (SELECT *
FROM LITORALE
WHERE Nazione = NomeN)
oppure...
SELECT NomeN, NomeCon
FROM NAZIONI INNER JOIN CONTINENTI
WHERE NomeN NOT IN (SELECT Nazione
FROM LITORALE)
oppure...
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
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...
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
SELECT NomeN
FROM NAZIONI INNER JOIN CONTINENTI ON NAZIONI.Continente = CONTINENTI.NomeCon
WHERE (NomeCon = 'Europa') AND
(NomeN IN SELECT Nazione
FROM LITORALE)
oppure...
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
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à
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
DELETE FROM NAZIONI
WHERE Continente IN (SELECT Sigla
FROM CONTINENTI
WHERE NomeCon LIKE 'America%')
3.48: Le città italiane
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
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.
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.
SELECT Avg(LN.SupTotale/N.Superficie) FROM NAZIONI AS N INNER JOIN LaghiNazione LN ON N.NomeN=LN.Nazione