Basi di dati sql

Date le tabelle:

Clienti (Cod_cli, Cognome, Nome, Citta, Salario, Eta)
Prenot (Cod_cli, Cod_alb, Camera, Giorni, Acconto, DataIn, DataOut)
Alberghi (Cod_alb, Nome, Citta, Totale_camere)

Scrivere le interrogazioni SQL che restituiscono le seguenti informazioni:

1) Visualizzare tutti i dati dei clienti che hanno una prenotazione per la camera 237

SELECT clienti.*
FROM clienti,prenot
WHERE clienti.cod_cli=prenot.cod_cli
AND camera=”237”;
2) Visualizzare tutti i dati dei clienti e delle relative prenotazioni per tutti coloro che hanno età compresa fra 25 e 40 anni

SELECT *
FROM clienti, prenot
WHERE clienti.cod_cli=prenot.cod_cli
AND eta>25 AND eta 3) Visualizzare tutti i dati delle prenotazioni effettuate da clienti che risiedono a Gotham City

SELECT prenot.*
FROM clienti, prenot
WHERE codice=cod_cli
AND citta=”GothamCity”;
4) Visualizzare cognome, nome, acconto e camera dei clienti che hanno versato un acconto superiore a 100.000 e che hanno più di 25 anni

SELECT cognome, nome, acconto, camera
FROM clienti, prenot
WHERE clienti.cod_cli=prenot.cod_cli
AND prenot.acconto>100000
AND clienti.eta>25;
5) Per ogni albergo visualizzare cognome, nome e città dei clienti che hanno versato un acconto superiore alla media

SELECT alberghi.nome, clienti.cognome, clienti.nome, clienti.citta
FROM clienti, prenot, alberghi
WHERE clienti.cod_cli = prenot.cod_cli
AND prenot.cod_alb = alberghi.cod_alb
AND prenot.acconto > SELECT (AVG(acconto) FROM prenot);
6) Visualizzare nome dell’albergo, cognome, nome e città di residenza dei 5 clienti che hanno prenotato per i periodi più lunghi

SELECT TOP 5 alberghi.nome, clienti.cognome, clienti.nome, clienti.citta
FROM clienti, prenot, alberghi
WHERE clienti.cod_cli = prenot.cod_cli
AND prenot.cod_alb = alberghi.cod_alb
ORDER BY prenot.giorni DESC;
7) Per ogni albergo, visualizzare cognome, nome e città dei clienti che hanno versato un acconto superiore alla media di quell’albergo

SELECT alberghi.nome, clienti.cognome, clienti.nome, clienti.citta
FROM clienti, acconti, prenot, alberghi
WHERE clienti.cod_cli=prenot.cod_cli
AND prenot.cod_alb=acconti.cod_alb
AND prenot.cod_alb=alberghi.cod_alb
AND prenot.acconto >= SELECT (cod_alb, AVG(acconto) AS media FROM prenot GROUP BY cod_alb);
8) Visualizzare cognome nome e salario dei clienti che abitano in città che contengono almeno 2 clienti

SELECT cognome, nome
FROM clienti
WHERE citta IN (SELECT citta
FROM clienti
GROUP BY citta
HAVING COUNT(*) >= 2);

9) Visualizzare i dati di tutti i clienti che sono stati nella camera 237, per ciascun albergo, nell'arco della giornata
SELECT c.*, a.nome
FROM Clienti c, Alberghi a, Prenot p
WHERE p.cod_cli=c.cod_cli and p.cod_alb=a.cod_alb and p.camera = 237
GROUP BY a.nome

Date le tabelle:

Auto (Targa, Marca, Cilindrata, Potenza, CodF*, CodAss*)
Proprietari (CodF, Nome, Residenza)
Assicurazioni (CodAss, Nome, Sede)
Sinistro (CodS, Località, Data)
Autocoinvolte (CodS*, Targa*, ImportoDelDanno)

Scrivere le interrogazioni SQL che restituiscono le seguenti informazioni:

10) Per ciascuna Assicurazione, il nome, la sede ed il numero di auto assicurate

SELECT As.Nome, As.Sede, count(*) as NumAuto
FROM Assicurazioni As, Auto Au
WHERE Au.CodAss = As.CodAss
GROUP BY As.CodAss, As.Nome, As.Sede
11) Per ciascuna auto “Fiat”, la targa dell’auto ed il numero di sinistri in cui è stata coinvolta

SELECT A.Targa, count(*) as NumSin
FROM Auto A, AutoCoinvolte AC
WHERE A. Marca=”Fiat” and A.Targa=AC.Targa
GROUP BY A.Targa
12) Per ciascuna auto coinvolta in più di un sinistro, la targa dell’auto, il nome dell’Assicurazione ed il totale dei danni riportati

SELECT Au.Targa, As.Nome, sum(AC.ImportoDelDanno)
FROM AutoCoinvolte AC, Auto Au, Assicurazioni As
WHERE Au.Targa=AC.Targa and Au.CodAss=As.CodAss
GROUP BY Au,Targa, As.Nome
HAVING count(*) >1
13) CodF e Nome di coloro che possiedono più di un’auto

SELECT P.CodF, P.Nome
FROM Proprietari P, Auto A
WHERE P.CodF = A.CodF
GROUP BY P.CodF, P.Nome
HAVING count(*) >1
oppure
SELECT P.CodF, P.Nome
FROM Proprietari P
WHERE 1 FROM Auto A
WHERE P.CodF = A.CodF)
14) La targa delle auto che non sono state coinvolte in sinistri dopo il 20/01/01

SELECT A.Targa
FROM Auto A
WHERE Not Exists ( SELECT *
FROM AutoCoinvolte AC, Sinistri S
WHERE A.Targa =AC.Targa and AC.CodS=S.CodS and
S.Data>20/01/01)

oppure
SELECT A.Targa
FROM Auto A
WHERE A.Targa Not In (SELECT AC.Targa
FROM AutoCoinvolte AC, Sinistri S
WHERE AC.CodS=S.CodS and S.Data>20/01/01