home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
ftp.disi.unige.it
/
2015-02-11.ftp.disi.unige.it.tar
/
ftp.disi.unige.it
/
pub
/
.person
/
MesitiM
/
teach
/
sol6.txt
< prev
next >
Wrap
Text File
|
2003-04-15
|
9KB
|
233 lines
use anna
/*creazione tabelle*/
CREATE TABLE Magazzino(
IDprod decimal(5,0) PRIMARY KEY,
Nome varchar(50) NOT NULL,
qtaIniziale decimal(8,0) DEFAULT 0 CHECK (qtaIniziale >= 10) ,
scortaMinima decimal(8,0) DEFAULT 0 CHECK (scortaMinima >=0),
CHECK (qtaIniziale >= scortaMinima)
);
CREATE TABLE Clienti(
IDcli varchar(6) PRIMARY KEY,
cognome varchar(30) NOT NULL,
nome varchar(30) NOT NULL,
indirizzo varchar(30),
citta varchar(15),
telefono varchar(25),
UNIQUE(telefono)
);
CREATE TABLE Ordini(
IDcli varchar(6) FOREIGN KEY REFERENCES Clienti(IDcli) ON UPDATE NO ACTION,
IDprod decimal(5,0)FOREIGN KEY REFERENCES Magazzino (IDprod) ON UPDATE NO ACTION,
Data datetime,
quantita decimal(8,2) DEFAULT 10 CHECK (cast(quantita as int) % 10 = 0),
prezzoUnit decimal(5,2),
PRIMARY KEY(IDcli,IDprod,Data)
);
/*popolamento tabelle*/
INSERT INTO Magazzino VALUES (1, 'Piatto di portata mod.34/c', 500, 20)
INSERT INTO Magazzino VALUES (2, 'Padella con manico cm. 23', 230, 20)
INSERT INTO Magazzino VALUES (3, 'Padella con manico cm.34', 230, 40)
INSERT INTO Magazzino VALUES (4, 'Caraffa latte ml.2', 150 , 10)
INSERT INTO Magazzino VALUES (5, 'Caraffa bibita fredda l.1', 150 , 0)
INSERT INTO Magazzino VALUES (6, 'Caraffa termica caldo/freddo l. 0,5',50 , 0)
INSERT INTO Magazzino VALUES (7, 'Piatto di portata ovale mod.41/c', 200 , 30)
INSERT INTO Magazzino VALUES (8, 'Piatto di portata tondo mod.45/c', 200, 40)
INSERT INTO Magazzino VALUES (9, 'Pentola con manico cm. 15', 350, 30)
INSERT INTO Magazzino VALUES (10, 'Pentola con manico antiaderente cm. 15',220 , 30)
INSERT INTO Clienti VALUES ('ra123', 'Rossa', 'Aldo', 'via G. Verdi 34/6', 'Genova', '010192301')
INSERT INTO Clienti VALUES ('rc1453', 'Ronda', 'Carla', 'via G. Corsari 76/12', 'Genova','010903320')
INSERT INTO Clienti VALUES ('cp921', 'Cantilli', 'Paolo', 'via S.L. Cecchi 7/2','Milano','027711200')
INSERT INTO Clienti VALUES ('rg177', 'Rondani', 'Gianna', 'p.zza G. leopardi 7/2','Roma','067199055')
INSERT INTO Clienti VALUES ('vp443', 'Vincenzi', 'Paola', 'via Tre Mulini 65/1','Camogli','0187112070')
INSERT INTO Clienti VALUES ('dg3377', 'Devoto', 'Gianni', 'via XX Settembre 176/8','Genova','010713590')
INSERT INTO Clienti VALUES ('fv13', 'Ferrazza', 'Valentina','via A. Bensa 35/1', 'Roma','0671808854')
INSERT INTO Clienti VALUES ('sp2365', 'Serranti', 'Pietro', 'via XX Settembre 13/10', 'Rapallo','0185223877')
INSERT INTO Clienti VALUES ('fm1231', 'Fabbri', 'Maura', 'p.zza L. Nano 7/9', 'Camogli', '0187304456')
INSERT INTO Clienti VALUES ('gs4342', 'Gavi', 'Stefano', 'p.zza Tre Fontane 37/11', 'Genova', '010563877')
INSERT INTO Ordini VALUES ('sp2365', 1, '02/20/2000', 10, 34)
INSERT INTO Ordini VALUES ('sp2365', 4, '7/20/1998', 20, 20)
INSERT INTO Ordini VALUES ('sp2365', 2, '02/27/1999', 20, 12)
INSERT INTO Ordini VALUES ('gs4342', 4, '9/8/2000', 10, 20)
INSERT INTO Ordini VALUES ('gs4342', 9, '06/2/1999', 30, 35)
INSERT INTO Ordini VALUES ('vp443', 5, '9/9/2001', 20, 12)
INSERT INTO Ordini VALUES ('vp443', 7, '11/27/1999', 20, 25)
INSERT INTO Ordini VALUES ('rg177', 3, '9/21/2001', 30, 20)
INSERT INTO Ordini VALUES ('rg177', 8, '02/20/1998', 40, 15)
INSERT INTO Ordini VALUES ('rg177', 9, '7/20/1998', 20, 35)
INSERT INTO Ordini VALUES ('fv13', 1, '02/27/1999', 30, 37)
INSERT INTO Ordini VALUES ('fm1231', 3, '9/8/2000', 20, 25)
INSERT INTO Ordini VALUES ('fm1231', 4, '06/2/2000', 10, 14)
INSERT INTO Ordini VALUES ('fm1231', 1, '9/9/2001', 20, 30)
INSERT INTO Ordini VALUES ('ra123', 8 , '02/27/1999', 50, 20)
INSERT INTO Ordini VALUES ('cp921', 3, '9/8/2000', 20, 27)
INSERT INTO Ordini VALUES ('ra123', 2 , '02/27/1999', 10, 14)
INSERT INTO Ordini VALUES ('cp921', 5, '9/8/2000', 10, 15)
INSERT INTO Ordini VALUES ('ra123', 3, '9/8/2000', 10, 15)
INSERT INTO Ordini VALUES ('rc1453', 3, '9/8/2000', 10, 15)
INSERT INTO Ordini VALUES ('dg3377', 3, '9/8/2000', 10, 15)
INSERT INTO Ordini VALUES ('gs4342', 3, '9/8/2000', 10, 15)
INSERT INTO Ordini VALUES ('ra123', 5, '9/8/2000', 10, 15)
INSERT INTO Ordini VALUES ('rc1453', 5, '9/8/2000', 10, 15)
INSERT INTO Ordini VALUES ('dg3377', 5, '9/8/2000', 10, 15)
INSERT INTO Ordini VALUES ('gs4342', 5, '9/8/2000', 10, 15)
-- ALTERAZIONE DI TABELLE
-- 1
ALTER TABLE Clienti ADD fido decimal(10) DEFAULT 10000 CHECK (fido>=10000)
-- NOTA: Utilizzando l'opzione (NON STANDARD) di SQL-Server WITH VALUES
-- e' possibile aggiornare automaticamente il valore dell'attributo
-- fido per tutti i clienti gia' introdotti nella tabella. Altrimenti occorre eseguire
-- il comando di update per modificare tale valore
UPDATE Clienti SET fido = 10000
-- 3
UPDATE Clienti SET fido = fido * 1.3 where citta = 'Genova'
-- 4
-- Per rimuovere il numero di telefono occorre prima rimuovere il constraint di unicita' e poi
-- procedere a rimuovere la colonna
ALTER TABLE Clienti DROP CONSTRAINT 'nome del constraint'
ALTER TABLE Clienti DROP column telefono
-- QUERIES
--1 Per ogni cliente, trovare la quantita' totale di prodotti ordinati,
-- il numero di ``tipi di prodotto'' e il prezzo totale che deve pagare.
SELECT IDcli, sum(quantita), count(DISTINCT IDprod), sum(quantita*prezzoUnit)
FROM Ordini
GROUP BY IDcli
-- 2 Trovare il nome dei prodotti in magazzino che non sono
-- stati ordinati da nessun cliente.
SELECT magazzino.IDprod, nome
FROM magazzino JOIN ordini ON magazzino.IDprod=ordini.IDprod
WHERE magazzino.IDprod NOT IN (SELECT DISTINCT IDprod from Ordini)
SELECT DISTINCT magazzino.IDprod, nome
FROM magazzino LEFT OUTER JOIN ordini ON magazzino.IDprod=ordini.IDprod
where IDcli IS NULL
-- 3 Trovare il nome e cognome dei clienti che non hanno
-- effettuato alcun ordine.
SELECT DISTINCT clienti.IDcli, clienti.cognome, clienti.nome
FROM clienti LEFT OUTER JOIN ordini ON clienti.IDcli=ordini.IDcli
where IDprod IS NULL
-- 4 Trovare i prodotti ordinati da tutti i clienti di
Genova
SELECT DISTINCT X.IDprod
FROM magazzino X
WHERE NOT EXISTS (SELECT *
FROM clienti Y
where Y.citta = 'genova' and
not exists (select *
from ordini
where IDprod = X.IDprod and IDcli = Y.IDcli))
-- ATTENZIONE: La seguente espressione potrebbe essere formulata per risolvere questa interrogazioni ma e'
-- SBAGLIATA
-- SELECT IDprod, nome
-- FROM magazzino X
-- WHERE NOT EXIST (SELECT *
-- FROM ordini JOIN Clienti on ordini.IDcli = clienti.IDcli and
-- citta <> 'Genova' and IDprod = X.IDprod)
-- VISTE
-- 1 Creare una vista contente le informazioni sui ``clienti fidati''.
-- I ``clienti fidati'' sono quelli che hanno un fido superiore a
-- 12000 euro.
CREATE VIEW ClientiFidati AS SELECT * FROM Clienti WHERE fido > 12000
-- 2 Creare una vista contente per ogni prodotto la quantita' ordinata.
CREATE VIEW OrdiniTotali(IDprod,TotaleQuantita) AS
SELECT IDprod, sum(quantita) FROM Ordini GROUP BY IDprod
-- 3 Creare una vista contente le informazioni dei prodotti sotto scorta.
CREATE VIEW sottoScorta(IDprod, quantitaInMagazzino) AS
SELECT magazzino.IDprod, qtaIniziale - TotaleQuantita
FROM magazzino JOIN OrdiniTotali ON magazzino.IDprod = OrdiniTotali.IDprod
WHERE qtaIniziale - TotaleQuantita < scortaMinima
-- 4 Creare una vista contente per ogni cliente il fido utilizzato
CREATE VIEW FidoUtilizzato(IDcli, fidoUtilizzato) AS
select IDcli, sum(quantita*prezzoUnit)
from ordini
group by IDcli
-- 5 Creare una vista contente per ogni cliente il fido
-- ancora a disposizione di un cliente
CREATE VIEW fidoResiduo(IDcli, fidoResiduo) AS
select clienti.IDcli, fido-fidoUtilizzato
from clienti join fidoUtilizzato on clienti.IDcli=fidoUtilizzato.IDcli
--- MODIFICHE DATI
--1
update ClientiFidati
set fido= fido - (select sum(quantita*prezzoUnit)
from ordini)
-- 2
insert into clientiFidati values ('Anc01', 'Ancona', 'Massimo', 'Via Patagonia', 'Genova', 0103536666, 20000)
insert into clientiFidati values ('Cat01', 'Catania', 'Barbara', 'Largo Argentina', 'Genova', 0103536688, 10000)
-- sono permessi entrambi. Se si usasse la option nella creazione della vista il secondo inserimento non sarebbe ammesso
-- 3
update Clienti
set fido = fido * 1.5
where IDcli IN (select IDcli
from ordini
group by IDcli
having avg(quantita*prezzoUnit) >=
(select avg(quantita*prezzoUnit)
from ordini)
)
-- 4
-- Questa interrogazione non e' possibile perche' si agisce su una vista che contiene espressioni matematiche