--------------------------------------

VIEW-----------------------------------
mysql> CREATE VIEW SalePerOrder AS SELECT
orderNumber,SUM(quantityOrdered*priceEach) AS Total FROM orderdetails
GROUP BY orderNumber ORDER BY Total DESC//
Query OK, 0 rows affected (0.13 sec)
mysql> SHOW FULL TABLES//
+-------------------------+------------+
| Tables_in_classicmodels | Table_type |
+-------------------------+------------+
| SalePerOrder | VIEW |
| customers | BASE TABLE |
| employees | BASE TABLE |
| offices | BASE TABLE |
| orderdetails | BASE TABLE |
| orders | BASE TABLE |
| payments | BASE TABLE |
| productlines | BASE TABLE |
| products | BASE TABLE |
+-------------------------+------------+
9 rows in set (0.00 sec)
mysql> CREATE VIEW BigSalesOrder AS SELECT orderNumber,ROUND(Total,2)
Total FROM SalePerOrder WHERE Total>60000//
Query OK, 0 rows affected (0.12 sec)
mysql> SHOW FULL TABLES//
+-------------------------+------------+
| Tables_in_classicmodels | Table_type |
+-------------------------+------------+
| BigSalesOrder | VIEW |
| SalePerOrder | VIEW |
| customers | BASE TABLE |
| employees | BASE TABLE |
| offices | BASE TABLE |
| orderdetails | BASE TABLE |
| orders | BASE TABLE |
| payments | BASE TABLE |
| productlines | BASE TABLE |
| products | BASE TABLE |
+-------------------------+------------+
10 rows in set (0.00 sec)
mysql> CREATE OR REPLACE VIEW CustomerOrders AS SELECT
orderNumber,customerName,SUM(quantityOrdered*priceEach) Total FROM
orderdetails INNER JOIN orders O USING(orderNumber) INNER JOIN customers
USING(customerNumber) GROUP BY orderNumber//
Query OK, 0 rows affected (0.09 sec)
mysql> SELECT * FROM CustomerOrders ORDER BY Total DESC;
-> //
+-------------+------------------------------------+----------+
| orderNumber | customerName | Total |
+-------------+------------------------------------+----------+
| 10165 | Dragon Souveniers, Ltd. | 67392.85 |
| 10287 | Vida Sport, Ltd | 61402.00 |
| 10310 | Toms Spezialitäten, Ltd | 61234.67 |
| 10212 | Euro+ Shopping Channel | 59830.55 |
| 10207 | Diecast Collectables | 59265.14 |
| 10127 | Muscle Machine Inc | 58841.35 |
| 10204 | Muscle Machine Inc | 58793.53 |
| 10126 | Corrida Auto Replicas, Ltd | 57131.92 |
| 10222 | Collectable Mini Designs Co. | 56822.65 |
| 10142 | Mini Gifts Distributors Ltd. | 56052.56 |
| 10390 | Mini Gifts Distributors Ltd. | 55902.50 |
| 10312 | Mini Gifts Distributors Ltd. | 55639.66 |
| 10135 | Mini Gifts Distributors Ltd. | 55601.84 |
| 10192 | Online Diecast Creations Co. | 55425.77 |
| 10181 | Herkku Gifts | 55069.55 |
| 10159 | Corporate Gift Ideas Co. | 54682.68 |
| 10105 | Danish Wholesale Imports | 53959.21 |
| 10314 | Heintze Collectables | 53745.34 |
| 10304 | Auto Associés & Cie. | 53116.99 |
| 10306 | AV Stores, Co. | 52825.29 |
| 10185 | Mini Creations Ltd. | 52548.49 |
| 10419 | Salzburg Collectables | 52420.07 |
| 10407 | The Sharp Gifts Warehouse | 52229.55 |
| 10360 | Kelly's Gift Shop | 52166.00 |
| 10106 | Rovelli Gifts | 52151.81 |
| 10266 | L'ordine Souveniers | 51619.02 |
| 10336 | La Corne D'abondance, Co. | 51209.58 |
| 10276 | Online Mini Collectables | 51152.86 |
| 10108 | Cruz & Sons Co. | 51001.22 |
| 10122 | Marseille Mini Autos | 50824.66 |
| 10414 | Gifts4AllAges.com | 50806.85 |
| 10322 | Online Diecast Creations Co. | 50799.69 |
| 10168 | Technics Stores Inc. | 50743.65 |
| 10145 | Toys4GrownUps.com | 50342.74 |
| 10103 | Baane Mini Imports | 50218.95 |
| 10329 | Land of Toys Inc. | 50025.35 |
| 10208 | Saveley & Henriot, Co. | 49614.72 |
| 10375 | La Rochelle Gifts | 49523.67 |
| 10211 | Auto Canal+ Petit | 49165.16 |
| 10339 | Tokyo Collectables, Ltd | 48927.64 |
| 10291 | Scandinavian Gift Ideas | 48809.90 |
| 10110 | AV Stores, Co. | 48425.69 |
| 10321 | FunGiftIdeas.com | 48355.87 |
| 10280 | Amica Models & Co. | 48298.99 |
| 10282 | Mini Gifts Distributors Ltd. | 47979.98 |
| 10275 | La Rochelle Gifts | 47924.19 |
| 10382 | Mini Gifts Distributors Ltd. | 47765.59 |
| 10184 | Iberia Gift Imports, Corp. | 47513.19 |
| 10305 | Marta's Replicas Co. | 47411.33 |
| 10225 | Vida Sport, Ltd | 47375.92 |
| 10210 | Osaka Souveniers Co. | 47177.59 |
| 10332 | AV Stores, Co. | 47159.11 |
| 10262 | Euro+ Shopping Channel | 47065.36 |
| 10386 | Euro+ Shopping Channel | 46968.52 |
| 10412 | Euro+ Shopping Channel | 46895.48 |
| 10316 | giftsbymail.co.uk | 46788.14 |
| 10373 | Oulu Toy Supplies, Inc. | 46770.52 |
| 10398 | Reims Collectables | 46656.94 |
| 10350 | Euro+ Shopping Channel | 46493.16 |
| 10331 | Motor Mint Distributors Inc. | 45994.07 |
| 10120 | Australian Collectors, Co. | 45864.03 |
| 10363 | Suominen Souveniers | 45785.34 |
| 10253 | UK Collectables, Ltd. | 45443.54 |
| 10273 | Petit Auto | 45352.47 |
| 10182 | Mini Gifts Distributors Ltd. | 45084.38 |
| 10153 | Euro+ Shopping Channel | 44939.85 |
| 10223 | Australian Collectors, Co. | 44894.74 |
| 10324 | Vitachrome Inc. | 44400.50 |
| 10117 | Dragon Souveniers, Ltd. | 44380.15 |
| 10358 | Euro+ Shopping Channel | 44185.46 |
| 10167 | Scandinavian Gift Ideas | 44167.09 |
| 10259 | Handji Gifts& Co | 44160.92 |
| 10401 | Tekni Collectables Inc. | 43525.04 |
| 10229 | Mini Gifts Distributors Ltd. | 43369.30 |
| 10285 | Marta's Replicas Co. | 43134.04 |
| 10341 | Salzburg Collectables | 42813.83 |
| 10250 | The Sharp Gifts Warehouse | 42798.08 |
| 10180 | Daedalus Designs Imports | 42783.81 |
| 10308 | Mini Classics | 42339.76 |
| 10420 | Souveniers And Things Co. | 42251.51 |
| 10263 | Gift Depot Inc. | 42044.77 |
| 10347 | Australian Collectors, Co. | 41995.62 |
| 10425 | La Rochelle Gifts | 41623.44 |
| 10148 | Anna's Decorations, Ltd | 41554.73 |
| 10248 | Land of Toys Inc. | 41445.21 |
| 10404 | Down Under Souveniers, Inc | 41426.81 |
| 10143 | Mini Creations Ltd. | 41016.75 |
| 10227 | Saveley & Henriot, Co. | 40978.53 |
| 10357 | Mini Gifts Distributors Ltd. | 40676.26 |
| 10197 | Enaco Distributors | 40473.86 |
| 10342 | Australian Collectors, Co. | 40265.60 |
| 10104 | Euro+ Shopping Channel | 40206.20 |
| 10203 | Euro+ Shopping Channel | 40062.53 |
| 10349 | Muscle Machine Inc | 39964.63 |
| 10194 | Saveley & Henriot, Co. | 39712.10 |
| 10281 | Diecast Classics Inc. | 39641.43 |
| 10367 | Toys4GrownUps.com | 39580.60 |
| 10354 | Down Under Souveniers, Inc | 39440.59 |
| 10288 | Handji Gifts& Co | 38785.48 |
| 10140 | Technics Stores Inc. | 38675.13 |
| 10169 | Anna's Decorations, Ltd | 38547.19 |
| 10176 | L'ordine Souveniers | 38524.29 |
| 10150 | Dragon Souveniers, Ltd. | 38350.15 |
| 10196 | Super Scale Inc. | 38139.18 |
| 10260 | GiftsForHim.com | 37769.38 |
| 10173 | Rovelli Gifts | 37723.79 |
| 10328 | Rovelli Gifts | 37654.09 |
| 10155 | Toys of Finland, Co. | 37602.48 |
| 10283 | Royal Canadian Collectables, Ltd. | 37527.58 |
| 10175 | Stylish Desk Decors, Co. | 37455.77 |
| 10271 | Mini Gifts Distributors Ltd. | 37430.89 |
| 10254 | Down Under Souveniers, Inc | 37281.36 |
| 10403 | UK Collectables, Ltd. | 37258.94 |
| 10383 | Euro+ Shopping Channel | 36851.98 |
| 10301 | Norway Gifts By Mail, Co. | 36798.88 |
| 10206 | Canadian Gift Exchange Network | 36527.61 |
| 10410 | GiftsForHim.com | 36442.34 |
| 10161 | Heintze Collectables | 36164.46 |
| 10311 | Euro+ Shopping Channel | 36140.38 |
| 10195 | Mini Classics | 36092.40 |
| 10215 | West Coast Collectables Co. | 36070.47 |
| 10241 | Mini Caravy | 36069.26 |
| 10119 | Salzburg Collectables | 35826.33 |
| 10270 | Souveniers And Things Co. | 35806.73 |
| 10193 | Australian Collectables, Ltd | 35505.63 |
| 10246 | Euro+ Shopping Channel | 35420.74 |
| 10416 | L'ordine Souveniers | 35362.26 |
| 10292 | Land of Toys Inc. | 35321.97 |
| 10405 | Mini Caravy | 35157.75 |
| 10318 | Diecast Classics Inc. | 35152.12 |
| 10371 | Mini Gifts Distributors Ltd. | 35137.54 |
| 10268 | Extreme Desk Decorations, Ltd | 35034.57 |
| 10325 | Baane Mini Imports | 34638.14 |
| 10183 | Classic Gift Ideas, Inc | 34606.28 |
| 10380 | Euro+ Shopping Channel | 34404.21 |
| 10299 | Toys of Finland, Co. | 34341.08 |
| 10372 | Tokyo Collectables, Ltd | 33967.73 |
| 10293 | Amica Models & Co. | 33924.24 |
| 10230 | Blauer See Auto, Co. | 33820.62 |
| 10178 | Alpha Cognac | 33818.34 |
| 10313 | Canadian Gift Exchange Network | 33594.58 |
| 10393 | Down Under Souveniers, Inc | 33593.32 |
| 10114 | La Corne D'abondance, Co. | 33383.14 |
| 10278 | Signal Gift Stores | 33347.88 |
| 10348 | Corrida Auto Replicas, Ltd | 33145.56 |
| 10151 | Oulu Toy Supplies, Inc. | 32723.04 |
| 10147 | Collectables For Less Inc. | 32680.31 |
| 10124 | Signal Gift Stores | 32641.98 |
| 10381 | Corporate Gift Ideas Co. | 32626.09 |
| 10359 | Reims Collectables | 32600.61 |
| 10220 | Clover Collections, Co. | 32538.74 |
| 10378 | Euro+ Shopping Channel | 32289.12 |
| 10284 | Norway Gifts By Mail, Co. | 32260.16 |
| 10245 | Super Scale Inc. | 32239.47 |
| 10138 | Kelly's Gift Shop | 32077.44 |
| 10361 | Souveniers And Things Co. | 31835.36 |
| 10400 | The Sharp Gifts Warehouse | 31755.34 |
| 10234 | Extreme Desk Decorations, Ltd | 31670.37 |
| 10177 | CAF Imports | 31428.21 |
| 10296 | Bavarian Collectables Imports, Co. | 31310.09 |
| 10251 | Tekni Collectables Inc. | 31102.85 |
| 10162 | Corporate Gift Ideas Co. | 30876.44 |
| 10388 | FunGiftIdeas.com | 30293.77 |
| 10399 | Kelly's Gift Shop | 30253.75 |
| 10149 | Signal Collectibles Ltd. | 29997.09 |
| 10188 | Herkku Gifts | 29954.91 |
| 10391 | Anna's Decorations, Ltd | 29848.52 |
| 10141 | Suominen Souveniers | 29716.86 |
| 10129 | Stylish Desk Decors, Co. | 29429.14 |
| 10424 | Euro+ Shopping Channel | 29310.30 |
| 10235 | Royal Canadian Collectables, Ltd. | 29284.42 |
| 10411 | Québec Home Shopping Network | 29070.38 |
| 10417 | Euro+ Shopping Channel | 28574.90 |
| 10413 | Gift Depot Inc. | 28500.78 |
| 10247 | Suominen Souveniers | 28394.54 |
| 10369 | Collectables For Less Inc. | 28322.83 |
| 10187 | King Kong Collectables, Co. | 28287.73 |
| 10238 | Danish Wholesale Imports | 28211.70 |
| 10191 | Toms Spezialitäten, Ltd | 27988.47 |
| 10389 | Scandinavian Gift Ideas | 27966.54 |
| 10396 | Mini Gifts Distributors Ltd. | 27695.54 |
| 10319 | Microscale Inc. | 27550.51 |
| 10164 | Mini Auto Werke | 27121.90 |
| 10370 | Anna's Decorations, Ltd | 27083.78 |
| 10356 | Lyon Souveniers | 26311.63 |
| 10353 | Gift Ideas Corp. | 26304.13 |
| 10333 | Mini Wheels Co. | 26248.78 |
| 10244 | Euro+ Shopping Channel | 26155.91 |
| 10109 | Motor Mint Distributors Inc. | 25833.14 |
| 10355 | Euro+ Shopping Channel | 25529.78 |
| 10337 | Classic Legends Inc. | 25505.98 |
| 10252 | Auto Canal+ Petit | 25080.96 |
| 10232 | giftsbymail.co.uk | 24995.61 |
| 10340 | Enaco Distributors | 24945.21 |
| 10172 | Gift Depot Inc. | 24879.08 |
| 10300 | Blauer See Auto, Co. | 24101.81 |
| 10139 | Souveniers And Things Co. | 24013.52 |
| 10174 | Australian Gift Network, Co | 23936.53 |
| 10201 | Mini Wheels Co. | 23923.93 |
| 10302 | UK Collectables, Ltd. | 23908.24 |
| 10272 | Diecast Classics Inc. | 23715.70 |
| 10418 | Extreme Desk Decorations, Ltd | 23627.44 |
| 10377 | Toys of Finland, Co. | 23602.90 |
| 10226 | Collectable Mini Designs Co. | 23552.59 |
| 10134 | Lyon Souveniers | 23419.47 |
| 10307 | Classic Gift Ideas, Inc | 23333.06 |
| 10334 | Volvo Model Replicas, Co | 23014.17 |
| 10261 | Québec Home Shopping Network | 22997.45 |
| 10179 | Kelly's Gift Shop | 22963.60 |
| 10237 | Vitachrome Inc. | 22602.36 |
| 10217 | Handji Gifts& Co | 22474.17 |
| 10133 | Euro+ Shopping Channel | 22366.04 |
| 10107 | Land of Toys Inc. | 22292.62 |
| 10186 | Double Decker Gift Stores, Ltd | 22275.73 |
| 10214 | Corrida Auto Replicas, Ltd | 22162.61 |
| 10163 | Classic Legends Inc. | 22042.37 |
| 10258 | Tokyo Collectables, Ltd | 22037.91 |
| 10115 | Classic Legends Inc. | 21665.98 |
| 10406 | Danish Wholesale Imports | 21638.62 |
| 10374 | Australian Gift Network, Co | 21432.31 |
| 10209 | Men 'R' US Retailers, Ltd. | 21053.69 |
| 10198 | Cruz & Sons Co. | 20644.24 |
| 10327 | Danish Wholesale Imports | 20564.86 |
| 10160 | Men 'R' US Retailers, Ltd. | 20452.50 |
| 10228 | Cambridge Collectables Co. | 20355.24 |
| 10267 | Muscle Machine Inc | 20314.44 |
| 10202 | GiftsForHim.com | 20220.04 |
| 10279 | Euro+ Shopping Channel | 20009.53 |
| 10315 | La Rochelle Gifts | 19501.82 |
| 10326 | Volvo Model Replicas, Co | 19206.68 |
| 10224 | Daedalus Designs Imports | 18997.89 |
| 10344 | Marseille Mini Autos | 18888.31 |
| 10264 | Gifts4AllAges.com | 18473.71 |
| 10394 | Euro+ Shopping Channel | 18102.74 |
| 10395 | Lyon Souveniers | 17928.09 |
| 10309 | Baane Mini Imports | 17876.32 |
| 10157 | Frau da Collezione | 17746.26 |
| 10297 | Clover Collections, Co. | 17359.53 |
| 10200 | King Kong Collectables, Co. | 17193.06 |
| 10343 | Reims Collectables | 17104.91 |
| 10131 | Gift Ideas Corp. | 17032.29 |
| 10171 | Québec Home Shopping Network | 16909.84 |
| 10221 | Petit Auto | 16901.38 |
| 10320 | Volvo Model Replicas, Co | 16799.03 |
| 10257 | The Sharp Gifts Warehouse | 16753.30 |
| 10121 | Reims Collectables | 16700.47 |
| 10379 | Euro+ Shopping Channel | 16621.27 |
| 10111 | Mini Wheels Co. | 16537.85 |
| 10239 | Oulu Toy Supplies, Inc. | 16212.59 |
| 10330 | Cruz & Sons Co. | 15822.84 |
| 10231 | CAF Imports | 15322.93 |
| 10240 | Osaka Souveniers Co. | 15183.63 |
| 10170 | Mini Auto Werke | 15130.97 |
| 10295 | Gifts4AllAges.com | 15059.76 |
| 10123 | Atelier graphique | 14571.44 |
| 10366 | Royale Belge | 14379.90 |
| 10136 | Alpha Cognac | 14232.70 |
| 10346 | Signal Gift Stores | 14191.12 |
| 10384 | Corporate Gift Ideas Co. | 14155.57 |
| 10137 | Reims Collectables | 13920.26 |
| 10128 | Euro+ Shopping Channel | 13884.99 |
| 10368 | Mini Gifts Distributors Ltd. | 13874.75 |
| 10351 | Stylish Desk Decors, Co. | 13671.82 |
| 10205 | Euro+ Shopping Channel | 13059.16 |
| 10362 | Technics Stores Inc. | 12692.19 |
| 10219 | Signal Collectibles Ltd. | 12573.28 |
| 10289 | Herkku Gifts | 12538.01 |
| 10274 | Collectables For Less Inc. | 12530.51 |
| 10397 | Alpha Cognac | 12432.32 |
| 10402 | Auto Canal+ Petit | 12190.85 |
| 10338 | Royale Belge | 12081.52 |
| 10249 | Cambridge Collectables Co. | 11843.45 |
| 10113 | Mini Gifts Distributors Ltd. | 11044.30 |
| 10415 | Australian Collectables, Ltd | 10945.26 |
| 10190 | Euro+ Shopping Channel | 10721.86 |
| 10101 | Blauer See Auto, Co. | 10549.01 |
| 10100 | Online Diecast Creations Co. | 10223.83 |
| 10166 | FunGiftIdeas.com | 9977.85 |
| 10152 | Australian Gift Network, Co | 9821.32 |
| 10352 | Auto-Moto Classics Inc. | 9658.74 |
| 10265 | Australian Collectables, Ltd | 9415.13 |
| 10392 | Mini Auto Werke | 8807.12 |
| 10423 | Petit Auto | 8597.73 |
| 10365 | Mini Creations Ltd. | 8307.28 |
| 10199 | West Coast Collectables Co. | 7678.25 |
| 10112 | Volvo Model Replicas, Co | 7674.94 |
| 10421 | Mini Gifts Distributors Ltd. | 7639.10 |
| 10218 | Frau da Collezione | 7612.06 |
| 10125 | Australian Collectors, Co. | 7565.08 |
| 10323 | Blauer See Auto, Co. | 7466.32 |
| 10213 | Double Decker Gift Stores, Ltd | 7310.42 |
| 10233 | Tekni Collectables Inc. | 7178.66 |
| 10146 | Gift Ideas Corp. | 6631.36 |
| 10335 | Mini Gifts Distributors Ltd. | 6466.44 |
| 10269 | Salzburg Collectables | 6419.84 |
| 10243 | Diecast Collectables | 6276.60 |
| 10298 | Atelier graphique | 6066.78 |
| 10130 | Auto-Moto Classics Inc. | 6036.96 |
| 10236 | Motor Mint Distributors Inc. | 5899.38 |
| 10290 | Auto-Moto Classics Inc. | 5858.56 |
| 10422 | Diecast Classics Inc. | 5849.44 |
| 10216 | Auto Associés & Cie. | 5759.42 |
| 10102 | Vitachrome Inc. | 5494.78 |
| 10256 | Danish Wholesale Imports | 4710.73 |
| 10255 | Mini Caravy | 4632.31 |
| 10156 | Euro+ Shopping Channel | 4599.52 |
| 10385 | Mini Gifts Distributors Ltd. | 4466.71 |
| 10154 | Boards & Toys Co. | 4465.85 |
| 10294 | Online Mini Collectables | 4424.40 |
| 10189 | Toys4GrownUps.com | 3879.96 |
| 10387 | Dragon Souveniers, Ltd. | 3516.04 |
| 10303 | Iberia Gift Imports, Corp. | 3474.66 |
| 10376 | Boards & Toys Co. | 3452.75 |
| 10118 | Enaco Distributors | 3101.40 |
| 10132 | Down Under Souveniers, Inc | 2880.00 |
| 10277 | Dragon Souveniers, Ltd. | 2611.84 |
| 10317 | Technics Stores Inc. | 2434.25 |
| 10409 | Handji Gifts& Co | 2326.18 |
| 10286 | La Corne D'abondance, Co. | 1960.80 |
| 10364 | Marseille Mini Autos | 1834.56 |
| 10242 | Microscale Inc. | 1679.92 |
| 10345 | Atelier graphique | 1676.14 |
| 10116 | Royale Belge | 1627.56 |
| 10158 | Baane Mini Imports | 1491.38 |
| 10144 | Royale Belge | 1128.20 |
| 10408 | Tokyo Collectables, Ltd | 615.45 |
+-------------+------------------------------------+----------+
326 rows in set (0.01 sec)
mysql> CREATE VIEW AboveAvgProducts AS SELECT
productCode,productName,buyPrice FROM products WHERE buyPrice>(SELECT
AVG(buyPrice) FROM products) ORDER BY buyPrice DESC//
Query OK, 0 rows affected (0.13 sec)
mysql> SHOW FULL TABLES//
+-------------------------+------------+
| Tables_in_classicmodels | Table_type |
+-------------------------+------------+
| AboveAvgProducts | VIEW |
| BigSalesOrder | VIEW |
| CustomerOrders | VIEW |
| SalePerOrder | VIEW |
| customers | BASE TABLE |
| employees | BASE TABLE |
| offices | BASE TABLE |
| orderdetails | BASE TABLE |
| orders | BASE TABLE |
| payments | BASE TABLE |
| productlines | BASE TABLE |
| products | BASE TABLE |
+-------------------------+------------+
12 rows in set (0.00 sec)