Esercizi SQL basi di dati

Dimissione(NTS, Data, Ora, Reparto)

PostiLetto(Reparto, Data, MattSera, LiberiMaschi, LiberiFemmine)

Calcolare il numero di pazienti attualmente ricoverati in ciascun reparto dell’ospedale (si

noti che i pazienti attualmente ricoverati sono quelli ammessi e non ancora dimessi, e si

badi a generare un risultato che abbia la struttura [<nome_reparto>, <numero_ricoverati>]

SELECT Reparto, COUNT(*) AS NumeroRicoveriAttuali

FROM Ammissione AS A

WHERE NOT EXISTS ( SELECT *

FROM Dimissione

WHERE (NTS = A.NTS AND Data > A.Data ) OR (NTS = A.NTS

AND Data = A.Data AND Ora > A.Ora) )

GROUP BY Reparto

Trovare il paziente (o i pazienti) con la degenza più lunga, misurata in giorni (si trascurino

cioè gli orari di ammissione e dimissione, e si ipotizzi che una operazione di differenza

sulle date restituisca un numero intero che rappresenta il numero di giorni, anziché un

valore di tipo interval)

CREATE VIEW DurataRicoveri (NTS, Giorni) AS

SELECT NTS, D.Data – A.Data

FROM Ammissione A JOIN Dimissione D ON A.NTS = D.NTS

WHERE A.Data < D.Data AND NOT EXISTS ( SELECT *

FROM Dimissione D2

WHERE A.NTS = D2.NTS AND D2.DATA

BETWEEN A.Data AND D.Data )

SELECT *

FROM DurataRicoveri

WHERE Giorni >= ALL ( SELECT Giorni

FROM DurataRicoveri )

Film(Titolo, NomeRegista, Anno)

Proiezione(NomeCin, CittàCin, TitoloFilm)

Interpreta(Attore, Film, Personaggio)

Modificare lo schema precedente, aggiungendo un attributo ‘Tipo’ alla tabella Film .

ALTER TABLE Film

ADD COLUMN Tipo VARCHAR(25) DEFAULT 'Normale'

Dare al nuovo attributo il valore “Flop” se il film è attualmente in proiezione in meno di 10

sale.

UPDATE Film

SET Tipo = 'Flop'

WHERE 10 > ALL ( SELECT COUNT(*)

FROM Proiezione

WHERE TitoloFilm = Titolo )

Nomi dei registi che hanno diretto nel 1993 più film di quanti ne avevano diretti nel 1992

SELECT NomeRegista

FROM Film F

WHERE Anno = 1993

GROUP BY NomeRegista

HAVING COUNT(*) > ALL ( SELECT COUNT(*)

FROM Film G

WHERE G.Anno = 1992 AND F.NomeRegista =

G.NomeRegista )

Condominio(Codice, Via, Civico, Città)

Appartamento(Condominio, Numero, Scala, Piano, CodFiscProprietario, Millesimi)

Proprietario(CodiceFiscale, Nome, Cognome, DataNascita)

CREATE TABLE Condominio (

Codice VARCHAR(16) PRIMARY KEY,

Via VARCHAR(64) NOT NULL,

Civico INTEGER NOT NULL CHECK(Civico > 0),

Città VARCHAR(64) NOT NULL

)

CREATE TABLE Appartamento (

Condominio VARCHAR(16)

Numero INTEGER NOT NULL CHECK(Numero > 0)

Scala VARCHAR(4) NOT NULL

Piano INTEGER NOT NULL CHECK(Piano > 0),

CodFiscProprietario CHAR(16)

Millesimi NUMERIC(6,2) NOT NULL

PRIMARY KEY (Condominio, Numero),

FOREIGN KEY (Condominio) REFERENCES Condominio(Codice)

ON UPDATE CASCADE

ON DELETE CASCADE

FOREIGN KEY (CodFiscProprietario) REFERENCES Proprietario(CodiceFiscale)

ON UPDATE CASCADE

ON DELETE SET NULL

CHECK(Millesimi > 0 AND 1000,00 = ( SELECT SUM(Millesimi)

FROM Appartamento A

WHERE A.Condominio = Condominio )

)

Creare una view che mostri di ogni condominio il proprietario con più millesimi (si tenga

presente che uno stesso proprietario può possedere più appartamenti in un condominio ).

CREATE VIEW MillesimiPerPersona (Condominio, CodFiscProprietario, Millesimi) AS

SELECT Condominio, CodFiscProprietario, SUM(Millesimi)

FROM Appartamento

GROUP BY Condominio, CodFiscProprietario

CREATE VIEW MaxMillesimi (Condominio, CodFiscProprietario) AS

SELECT Condominio, CodFiscProprietario

FROM MillesimiPerPersona

WHERE Millesimi = ANY ( SELECT MAX(Millesimi)

FROM MillesimiPerPersona B

WHERE B.Condominio = Condominio )

Giocatore(NumTessera, Nome, Squadra, Altezza, DataNascita, PresenzeInNazionale)

Squadra(Nazione, Allenatore, NumPartiteVinte)

Partita(IdPartita, Data, Squadra1, Squadra2, SetVintiSquadra1, SetVintiSquadra2, Arbitro)

Partecipazione(IdPartita, TesseraGiocatore, Ruolo, PuntiRealizzati)

CREATE TABLE Partita (

IdPartita INTEGER PRIMARY KEY,

Data DATE NOT NULL,

Squadra1 VARCHAR(64) NOT NULL,

Squadra2 VARCHAR(64) NOT NULL,

SetVintiSquadra1 INTEGER CHECK(SetVintiSquadra1 >= 0),

SetVintiSquadra2 INTEGER CHECK(SetVintiSquadra2 >= 0),

Arbitro VARCHAR(64) NOT NULL,

FOREIGN KEY (Squadra1) REFERENCES Squadra(Nazione)

ON UPDATE CASCADE

ON DELETE NO ACTION,

FOREIGN KEY (Squadra2) REFERENCES Squadra(Nazione)

ON UPDATE CASCADE

ON DELETE NO ACTION

)

CREATE TABLE Partecipazione (

IdPartita INTEGER,

TesseraGiocatore INTEGER,

Ruolo VARCHAR(16) NOT NULL,

PuntiRealizzati INTEGER CHECK(PuntiRealizzati >= 0),

PRIMARY KEY(IdPartita, TesseraGiocatore),

FOREIGN KEY (IdPartita) REFERENCES Partita(IdPartita)

ON UPDATE CASCADE

ON DELETE NO ACTION,

FOREIGN KEY (TesseraGiocatore) REFERENCES Giocatore(NumTessera)

ON UPDATE CASCADE

ON DELETE NO ACTION,

)

Estrarre i giocatori che hanno giocato in tutte le partite della loro squadra.

SELECT NumTessera, Nome

FROM Giocatore G1

WHERE NOT EXISTS (

SELECT IdPartita

FROM Partecipazione P1 JOIN Partita ON P1.IdPartita = IdPartita

WHERE (Squadra1 = G1.Squadra OR Squadra2 = G1.Squadra) AND

G1.NumTessera NOT IN ( SELECT TesseraGiocatore

FROM Partecipazione P2

WHERE P2.IdPartita = P1.IdPartita ) )

Estrarre il numero di set vinti da ogni squadra .

CREATE VIEW SetVinti (Squadra, SetVinti) AS

SELECT Squadra1, SetVintiSquadra1

FROM Partita

UNION ALL

SELECT Squadra2, SetVintiSquadra2

FROM Partita

SELECT Squadra, SUM(SetVinti)

FROM SetVinti

GROUP BY Squadra

Sapendo che una partita è vinta quando il numero di set vinti è pari a 3, scrivere un

comando SQL che assegna l’attributo NumPartiteVinte della tabella SQUADRA, in base ai

dati contenuti nella tabella Partita.

UPDATE Squadra AS S SET NumPartiteVinte = (

SELECT COUNT(*)

FROM SetVinti

WHERE SetVinti = 3 AND Squadra = S.Nazione

)

Ordine(Prod, Forn, Data, Qta)

Totale(Prod, Forn, Qta)

La prima registra ordini che vengono emessi in date progressive verso fornitori, la

seconda accumula il totale degli ordini emessi verso un particolare fornitore. Come è fatto

un trigger che aggiorna i totali in modo automatico, a seguito dell'inserzione di nuovi

ordini?

CREATE TRIGGER AggiornaQuantita

AFTER INSERT ON Ordine

FOR EACH ROW

WHEN TRUE

UPDATE Totale SET Qta = Qta + NEW.Qta

WHERE Prod = NEW.Prod AND Forn = NEW.Forn

Prodotto(CodProd, QtaDisp)

Ordine(Cliente, CodProd, Data, Qta)

La prima memorizza i prodotti contenuti in un magazzino con le rispettive quantità

disponibili; la seconda memorizza gli ordini pervenuti per i prodotti del magazzino.

Si esprima un vincolo in grado di verificare che la quantità di un ordine non superi la

quantità disponibile in magazzino per il prodotto ordinato . Attenzione: ci possono essere

già altri ordini del medesimo prodotto nella tabella ORDINE

CREATE ASSERTION QuantitaSufficiente

CHECK(NOT EXISTS ( SELECT *

FROM Prodotto P

WHERE Qta < ALL ( SELECT SUM(Qta)

FROM Ordine O

WHERE P.CodProd = O.CodProd ) )

Check-in(CodiceVolo, Data, NPosto, Classe, NomePasseggero)

Occupazione(CodiceVolo, Data, Classe, TotalePosti)

La prima registra le presenze su uno specifico aereo pronto per il decollo, e la seconda

conta per ogni classe il numero totale di passeggeri che hanno fatto check in. In che modo

esprimereste in SQL un vincolo per cui il totale dei posti occupati in una data classe

coincide con la somma dei check-in dei passeggeri presenti in quella classe?

CREATE ASSERTION PostiOccupati

CHECK(NOT EXISTS ( SELECT *

FROM Occupazione O

WHERE TotalePosti <> ALL ( SELECT COUNT(*)

FROM Check-in

WHERE CodiceVolo = O.CodiceVolo

AND Data = O.Data AND

Classe = O.Classe ) )

Prodotto(Codice, Nome, Descrizione, Peso, Costo)

Lineaordine(Ordine, Prodotto, Quantità, Ammontare)

Ordine(CodOrdine, Cliente, Data, Totale, Sconto)

Cliente(Codice, Nome, Indirizzo, Categoria)

Formulare un comando SQL che permette di assegnare il valore ‘Abituale’ all’attributo

Categoria per i clienti che hanno ordinato più di 5 volte lo stesso prodotto.

UPDATE Cliente SET Categoria = 'Abituale'

WHERE Codice IN ( SELECT Cliente

FROM Ordine JOIN Lineaordine ON CodOrdine = Ordine

GROUP BY Cliente, Prodotto

HAVING COUNT(*) > 5 )

Ordine(CodOrdine, Cliente, Data, Totale, Sconto)

Formulare una asserzione SQL che verifichi che il valore di Sconto è o nullo o > 0

CREATE ASSERTION CheckSconto

CHECK(NOT EXISTS ( SELECT *

FROM Ordine

WHERE Sconto IS NOT NULL AND Sconto <= 0 )

Lineaordine(Ordine, Prodotto, Quantità, Ammontare)

Ordine(CodOrdine, Cliente, Data, Totale, Sconto)

Formulare una asserzione SQL che verifichi che per ciascun ordine la somma

dell’ammontare delle linee d’ordine ad esso associate sia pari alla somma di Totale e

Sconto.

CREATE ASSERTION CheckAmmontare

CHECK(NOT EXISTS ( SELECT Ordine, SUM(Ammontare)

FROM Lineaordine LO

GROUP BY Ordine

HAVING SUM(Ammontare) <> ALL (

SELECT Totale + Sconto AS Ammontare

FROM Ordine

WHERE CodOrdine = LO.Ordine ) )

Lo schema seguente descrive un insieme gerarchico di prodotti, dove per i prodotti non

contenuti in altri prodotti assumiamo Livello (che descrive il livello di profondità a cui il

prodotto è situato nella gerarchia) pari a zero e SuperProdotto pari a null:

Prodotto(Codice, Nome, Descrizione, SuperProdotto, Livello)

Scrivere una regola attiva che alla cancellazione di un prodotto cancelli tutti i sottoprodotti

corrispondenti

CREATE TRIGGER Cancella

BEFORE DELETE ON Prodotto

FOR EACH ROW

WHEN TRUE

DELETE FROM Prodotto WHERE SuperProdotto = OLD.Codice

Scrivere una regola attiva che alla creazione di un nuovo prodotto (eventualmente "figlio"

di un prodotto esistente) calcola il valore dell'attributo Livello.

CREATE TRIGGER Aggiungi

AFTER INSERT ON Prodotto

FOR EACH ROW

WHEN TRUE

BEGIN

UPDATE Prodotto SET Livello = 0

WHERE Codice = NEW.Codice AND NEW.SuperProdotto IS NULL;

UPDATE Prodotto SET Livello = 1 + ( SELECT Livello

FROM Prodotto

WHERE Codice = NEW.SuperProdotto )

WHERE Codice = NEW.Codice AND NEW.SuperProdotto IS NOT NULL;

END;

Implementare mediante regole attive un vincolo di pseudo- integrità referenziale

sull'attributo SuperProdotto, per cui gli unici valori ammessi sono null o il codice di un altro

prodotto (occorre vietare anche che un prodotto sia SuperProdotto di se stesso).

CREATE TRIGGER ForeignKey

BEFORE INSERT ON Prodotto

FOR EACH ROW

WHEN NEW.SuperProdotto IS NOT NULL AND

( NEW.SuperProdotto = NEW