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