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 >
Text File  |  2003-04-15  |  9KB  |  233 lines
  1. use anna
  2.  
  3. /*creazione tabelle*/
  4. CREATE TABLE Magazzino(
  5. IDprod decimal(5,0) PRIMARY KEY,
  6. Nome varchar(50) NOT NULL,
  7. qtaIniziale decimal(8,0) DEFAULT 0 CHECK (qtaIniziale >= 10) ,
  8. scortaMinima decimal(8,0) DEFAULT 0 CHECK (scortaMinima >=0),
  9. CHECK (qtaIniziale >= scortaMinima)
  10. );
  11.  
  12. CREATE TABLE Clienti(
  13. IDcli varchar(6) PRIMARY KEY,
  14. cognome varchar(30) NOT NULL,
  15. nome varchar(30) NOT NULL,
  16. indirizzo varchar(30),
  17. citta varchar(15),
  18. telefono varchar(25),
  19. UNIQUE(telefono)
  20. );
  21.  
  22. CREATE TABLE Ordini(
  23. IDcli varchar(6) FOREIGN KEY REFERENCES Clienti(IDcli) ON UPDATE NO ACTION,
  24. IDprod decimal(5,0)FOREIGN KEY REFERENCES Magazzino (IDprod) ON UPDATE NO ACTION, 
  25. Data datetime,
  26. quantita decimal(8,2) DEFAULT 10 CHECK (cast(quantita as int) % 10 = 0),
  27. prezzoUnit decimal(5,2),
  28. PRIMARY KEY(IDcli,IDprod,Data)
  29. );
  30.  
  31. /*popolamento tabelle*/
  32. INSERT INTO Magazzino  VALUES (1, 'Piatto di portata mod.34/c', 500, 20)
  33. INSERT INTO Magazzino  VALUES (2, 'Padella con manico cm. 23', 230, 20)
  34. INSERT INTO Magazzino  VALUES (3, 'Padella con manico cm.34', 230, 40)
  35. INSERT INTO Magazzino  VALUES (4, 'Caraffa latte ml.2', 150 , 10)
  36. INSERT INTO Magazzino  VALUES (5, 'Caraffa bibita fredda l.1', 150 , 0)
  37. INSERT INTO Magazzino  VALUES (6, 'Caraffa termica caldo/freddo l. 0,5',50 , 0)
  38. INSERT INTO Magazzino  VALUES (7, 'Piatto di portata ovale mod.41/c', 200 , 30)
  39. INSERT INTO Magazzino  VALUES (8, 'Piatto di portata tondo mod.45/c', 200, 40)
  40. INSERT INTO Magazzino  VALUES (9, 'Pentola con manico cm. 15', 350, 30)
  41. INSERT INTO Magazzino  VALUES (10, 'Pentola con manico antiaderente cm. 15',220 , 30)
  42.  
  43.  
  44. INSERT INTO Clienti  VALUES ('ra123', 'Rossa', 'Aldo', 'via G. Verdi 34/6', 'Genova', '010192301')
  45. INSERT INTO Clienti  VALUES ('rc1453', 'Ronda', 'Carla', 'via G. Corsari 76/12', 'Genova','010903320')
  46. INSERT INTO Clienti  VALUES ('cp921', 'Cantilli', 'Paolo', 'via S.L. Cecchi 7/2','Milano','027711200')
  47. INSERT INTO Clienti  VALUES ('rg177', 'Rondani', 'Gianna', 'p.zza G. leopardi 7/2','Roma','067199055')
  48. INSERT INTO Clienti  VALUES ('vp443', 'Vincenzi', 'Paola', 'via Tre Mulini 65/1','Camogli','0187112070')
  49. INSERT INTO Clienti  VALUES ('dg3377', 'Devoto', 'Gianni', 'via XX Settembre 176/8','Genova','010713590')
  50. INSERT INTO Clienti  VALUES ('fv13', 'Ferrazza', 'Valentina','via A. Bensa 35/1', 'Roma','0671808854')
  51. INSERT INTO Clienti  VALUES ('sp2365', 'Serranti', 'Pietro', 'via XX Settembre 13/10', 'Rapallo','0185223877')
  52. INSERT INTO Clienti  VALUES ('fm1231', 'Fabbri', 'Maura', 'p.zza L. Nano 7/9', 'Camogli', '0187304456')
  53. INSERT INTO Clienti  VALUES ('gs4342', 'Gavi', 'Stefano', 'p.zza Tre Fontane 37/11', 'Genova', '010563877')
  54.  
  55. INSERT INTO Ordini VALUES ('sp2365', 1, '02/20/2000', 10, 34)
  56. INSERT INTO Ordini VALUES ('sp2365', 4, '7/20/1998', 20, 20)
  57. INSERT INTO Ordini VALUES ('sp2365', 2, '02/27/1999', 20, 12)
  58. INSERT INTO Ordini VALUES ('gs4342', 4, '9/8/2000', 10, 20)
  59. INSERT INTO Ordini VALUES ('gs4342', 9, '06/2/1999', 30, 35)
  60. INSERT INTO Ordini VALUES ('vp443', 5, '9/9/2001', 20, 12)
  61. INSERT INTO Ordini VALUES ('vp443', 7, '11/27/1999', 20, 25)
  62. INSERT INTO Ordini VALUES ('rg177', 3, '9/21/2001', 30, 20)
  63. INSERT INTO Ordini VALUES ('rg177', 8, '02/20/1998', 40, 15)
  64. INSERT INTO Ordini VALUES ('rg177', 9, '7/20/1998', 20, 35)
  65. INSERT INTO Ordini VALUES ('fv13', 1, '02/27/1999', 30, 37)
  66. INSERT INTO Ordini VALUES ('fm1231', 3, '9/8/2000', 20, 25)
  67. INSERT INTO Ordini VALUES ('fm1231', 4, '06/2/2000', 10, 14)
  68. INSERT INTO Ordini VALUES ('fm1231', 1, '9/9/2001', 20, 30)
  69. INSERT INTO Ordini VALUES ('ra123', 8 , '02/27/1999', 50, 20)
  70. INSERT INTO Ordini VALUES ('cp921', 3, '9/8/2000', 20, 27)
  71. INSERT INTO Ordini VALUES ('ra123', 2 , '02/27/1999', 10, 14)
  72. INSERT INTO Ordini VALUES ('cp921', 5, '9/8/2000', 10, 15)
  73.  
  74.  
  75. INSERT INTO Ordini VALUES ('ra123', 3, '9/8/2000', 10, 15)
  76. INSERT INTO Ordini VALUES ('rc1453', 3, '9/8/2000', 10, 15)
  77. INSERT INTO Ordini VALUES ('dg3377', 3, '9/8/2000', 10, 15)
  78. INSERT INTO Ordini VALUES ('gs4342', 3, '9/8/2000', 10, 15)
  79.  
  80. INSERT INTO Ordini VALUES ('ra123', 5, '9/8/2000', 10, 15)
  81. INSERT INTO Ordini VALUES ('rc1453', 5, '9/8/2000', 10, 15)
  82. INSERT INTO Ordini VALUES ('dg3377', 5, '9/8/2000', 10, 15)
  83. INSERT INTO Ordini VALUES ('gs4342', 5, '9/8/2000', 10, 15)
  84.  
  85. -- ALTERAZIONE DI TABELLE
  86.  
  87. -- 1
  88.  
  89.   ALTER TABLE Clienti ADD  fido decimal(10) DEFAULT 10000 CHECK (fido>=10000)
  90.  
  91. -- NOTA: Utilizzando l'opzione (NON STANDARD) di SQL-Server  WITH VALUES 
  92. -- e' possibile aggiornare automaticamente il valore dell'attributo 
  93. -- fido per tutti i clienti gia' introdotti nella tabella. Altrimenti occorre eseguire
  94. -- il comando di update per modificare tale valore
  95.  
  96.    UPDATE Clienti SET fido = 10000
  97.  
  98. -- 3
  99.  
  100.    UPDATE Clienti SET fido = fido * 1.3 where citta = 'Genova'
  101.  
  102. -- 4
  103.   
  104. -- Per rimuovere il numero di telefono occorre prima rimuovere il constraint di unicita' e poi
  105. -- procedere a rimuovere la colonna
  106.  
  107. ALTER TABLE Clienti DROP CONSTRAINT 'nome del constraint'
  108. ALTER TABLE Clienti DROP column telefono
  109.  
  110.  
  111. -- QUERIES
  112.  
  113. --1 Per ogni cliente, trovare la quantita' totale di prodotti ordinati,
  114. --  il numero di ``tipi di prodotto'' e il prezzo totale che deve pagare.
  115.  
  116.  
  117. SELECT IDcli, sum(quantita), count(DISTINCT IDprod), sum(quantita*prezzoUnit)
  118. FROM Ordini
  119. GROUP BY IDcli
  120.  
  121.  
  122. -- 2 Trovare il nome dei prodotti in magazzino che non sono
  123. --   stati ordinati da nessun cliente.
  124.  
  125. SELECT magazzino.IDprod, nome
  126. FROM magazzino JOIN ordini ON magazzino.IDprod=ordini.IDprod
  127. WHERE magazzino.IDprod NOT IN (SELECT DISTINCT IDprod from Ordini)
  128.  
  129. SELECT DISTINCT  magazzino.IDprod, nome
  130. FROM magazzino LEFT OUTER JOIN ordini ON magazzino.IDprod=ordini.IDprod
  131. where IDcli IS NULL
  132.  
  133. -- 3 Trovare il nome e cognome dei  clienti che non hanno
  134. --   effettuato alcun ordine.
  135.  
  136. SELECT DISTINCT clienti.IDcli, clienti.cognome, clienti.nome
  137. FROM clienti LEFT OUTER JOIN ordini ON clienti.IDcli=ordini.IDcli
  138. where IDprod IS NULL
  139.  
  140. -- 4 Trovare i prodotti ordinati da tutti i clienti di
  141.     Genova
  142.  
  143.   SELECT DISTINCT X.IDprod
  144.     FROM magazzino X
  145.     WHERE NOT EXISTS (SELECT * 
  146.                       FROM clienti Y
  147.                       where Y.citta = 'genova' and 
  148.                              not exists (select * 
  149.                                         from ordini 
  150.                                         where IDprod = X.IDprod and IDcli = Y.IDcli))
  151.  
  152. -- ATTENZIONE: La seguente espressione potrebbe essere formulata per risolvere questa interrogazioni ma e'
  153. --             SBAGLIATA
  154.  
  155. --    SELECT IDprod, nome
  156. --    FROM magazzino X
  157. --    WHERE NOT EXIST (SELECT * 
  158. --                     FROM ordini JOIN Clienti on ordini.IDcli = clienti.IDcli and
  159. --                  citta <> 'Genova' and IDprod = X.IDprod)
  160.  
  161. -- VISTE
  162.  
  163. -- 1  Creare una vista contente le informazioni sui  ``clienti fidati''.
  164. --    I ``clienti fidati'' sono quelli che hanno un fido superiore a
  165. --    12000 euro.
  166.  
  167. CREATE VIEW ClientiFidati AS SELECT * FROM Clienti WHERE fido > 12000
  168.  
  169. -- 2 Creare una vista contente per ogni prodotto la quantita' ordinata.
  170.  
  171.      CREATE VIEW OrdiniTotali(IDprod,TotaleQuantita) AS 
  172.           SELECT IDprod, sum(quantita) FROM Ordini GROUP BY IDprod
  173.  
  174. -- 3 Creare una vista contente le informazioni dei prodotti sotto scorta. 
  175.  
  176. CREATE VIEW sottoScorta(IDprod, quantitaInMagazzino) AS
  177. SELECT magazzino.IDprod, qtaIniziale - TotaleQuantita
  178. FROM magazzino JOIN OrdiniTotali ON magazzino.IDprod = OrdiniTotali.IDprod
  179. WHERE qtaIniziale - TotaleQuantita < scortaMinima
  180.  
  181. -- 4 Creare una vista contente per ogni cliente il fido utilizzato
  182.  
  183.  
  184. CREATE VIEW FidoUtilizzato(IDcli, fidoUtilizzato) AS
  185. select IDcli, sum(quantita*prezzoUnit)
  186. from ordini
  187. group by IDcli 
  188.  
  189. -- 5 Creare una vista contente per ogni cliente il fido
  190. --   ancora a disposizione di un cliente 
  191.  
  192. CREATE VIEW fidoResiduo(IDcli, fidoResiduo) AS
  193. select clienti.IDcli, fido-fidoUtilizzato
  194. from clienti join fidoUtilizzato on clienti.IDcli=fidoUtilizzato.IDcli
  195.  
  196.  
  197. --- MODIFICHE DATI
  198.  
  199. --1 
  200.  
  201. update ClientiFidati
  202. set fido= fido - (select sum(quantita*prezzoUnit)
  203.                    from ordini)
  204.  
  205.  
  206. -- 2
  207.  
  208. insert into clientiFidati values ('Anc01', 'Ancona', 'Massimo', 'Via Patagonia', 'Genova', 0103536666, 20000)
  209.  
  210. insert into clientiFidati values ('Cat01', 'Catania', 'Barbara', 'Largo Argentina', 'Genova', 0103536688, 10000)
  211.  
  212. -- sono permessi entrambi. Se si usasse la option nella creazione della vista il secondo inserimento non sarebbe ammesso
  213.  
  214.  
  215. -- 3 
  216.  
  217. update Clienti 
  218. set fido = fido * 1.5 
  219. where IDcli IN (select IDcli
  220.                 from ordini
  221.                 group by IDcli
  222.                 having avg(quantita*prezzoUnit) >= 
  223.                        (select avg(quantita*prezzoUnit) 
  224.                         from ordini)
  225.                 ) 
  226.  
  227.  
  228. -- 4
  229.  
  230. -- Questa interrogazione non e' possibile perche' si agisce su una vista che contiene espressioni matematiche
  231.  
  232.  
  233.