Last active
March 26, 2018 00:19
-
-
Save tim-hub/d44d4c5c5f1e764dbac68d9c0757e167 to your computer and use it in GitHub Desktop.
Assignment Data Warehouse Sql To test to get data from northwind 3 and 4, top 100 results. This is only for checking the data before and after you merger your data, SQL codes' author is Tim, Keep all rights.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- Assignment Data Warehouse Sql To test get data from northwind 3 and 4 | |
| use northwind3 -- or 4 | |
| go | |
| SELECT TOP(100) c.city, sum((1-od.Discount) * od.UnitPrice * Quantity) as Amount | |
| From [Order Details] od, Orders o, Customers c | |
| Where od.OrderID = o.OrderID and o.CustomerID = c. CustomerID | |
| GROUP BY c.CITY; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Albuquerque 51097.800403595 | |
| Anchorage 15177.4624481201 | |
| Boise 104361.949699402 | |
| Butte 1947.23999023438 | |
| Elgin 3063.20000076294 | |
| Eugene 18507.4499397278 | |
| Kirkland 1571.19999313354 | |
| Lander 11441.6299667358 | |
| Portland 7619.59997558594 | |
| San Francisco 3076.47245979309 | |
| Seattle 27363.6050167084 | |
| Walla Walla 357 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| I. de Margarita 16476.564994812 | |
| Helsinki 3161.34997177124 | |
| Luleå 24927.5774688721 | |
| Oulu 15648.7025566101 | |
| Sao Paulo 40486.4614715576 | |
| Rio de Janeiro 51956.9799633026 | |
| Brandenburg 30908.3838577271 | |
| Salzburg 23128.8599777222 | |
| Bräcke 29567.5625534058 | |
| Warszawa 3531.94997406006 | |
| Montréal 28872.190322876 | |
| Paris 2423.35000228882 | |
| Lyon 9182.43002891541 | |
| Resende 6068.199883461 | |
| London 52825.0094499588 | |
| Campinas 8414.13497161865 | |
| Kobenhavn 16817.0975103378 | |
| Buenos Aires 8119.09999847412 | |
| Graz 104874.978218079 | |
| Sevilla 11446.3598937988 | |
| Bruxelles 9736.07501220703 | |
| Barquisimeto 16076.6000671387 | |
| München 26656.5592651367 | |
| Frankfurt a.M. 19261.410068512 | |
| San Cristóbal 22768.763874054 | |
| Cowes 6146.30000305176 | |
| Lille 11666.9000015259 | |
| Tsawassen 19999.6000976563 | |
| Münster 4778.14000701904 | |
| Cork 49979.9051265717 | |
| Torino 1545.69999885559 | |
| Madrid 5700.14000797272 | |
| Köln 12496.1999435425 | |
| Bergamo 7176.21497154236 | |
| Strasbourg 18534.079864502 | |
| Århus 15843.9247589111 | |
| Vancouver 522.5 | |
| Lisboa 11472.3625831604 | |
| Charleroi 24088.7803421021 | |
| Aachen 3763.20999145508 | |
| Versailles 1992.04999160767 | |
| Stavern 5735.14998435974 | |
| Marseille 21963.2524261475 | |
| Barcelona 836.699996948242 | |
| Leipzig 5042.20004272461 | |
| Reims 1479.99998474121 | |
| Mannheim 3239.79998779297 | |
| Toulouse 9328.19985485077 | |
| Caracas 1488.69999694824 | |
| Berlin 4272.9999370575 | |
| Cunewalde 110277.305076599 | |
| Reggio Emilia 7048.23991203308 | |
| Genève 19343.778673172 | |
| Bern 12348.8799934387 | |
| Stuttgart 9588.42499542236 | |
| Nantes 4788.06002807617 | |
| México D.F. 23582.0775909424 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Exotic Liquids Chai 10.0000 | |
| Exotic Liquids Chang 19.0000 | |
| Exotic Liquids Aniseed Syrup 10.0000 | |
| New Orleans Cajun Delights Chef Anton's Cajun Seasoning 22.0000 | |
| New Orleans Cajun Delights Chef Anton's Gumbo Mix 21.3500 | |
| Grandma Kelly's Homestead Grandma's Boysenberry Spread 25.0000 | |
| Grandma Kelly's Homestead Uncle Bob's Organic Dried Pears 30.0000 | |
| Grandma Kelly's Homestead Northwoods Cranberry Sauce 40.0000 | |
| Tokyo Traders Mishi Kobe Niku 97.0000 | |
| Tokyo Traders Ikura 31.0000 | |
| Cooperativa de Quesos 'Las Cabras' Queso Cabrales 21.0000 | |
| Cooperativa de Quesos 'Las Cabras' Queso Manchego La Pastora 38.0000 | |
| Mayumi's Konbu 6.0000 | |
| Mayumi's Tofu 23.2500 | |
| Mayumi's Genen Shouyu 15.5000 | |
| Pavlova, Ltd. Pavlova 17.4500 | |
| Pavlova, Ltd. Alice Mutton 39.0000 | |
| Pavlova, Ltd. Carnarvon Tigers 62.5000 | |
| Specialty Biscuits, Ltd. Teatime Chocolate Biscuits 9.2000 | |
| Specialty Biscuits, Ltd. Sir Rodney's Marmalade 81.0000 | |
| Specialty Biscuits, Ltd. Sir Rodney's Scones 10.0000 | |
| PB Knäckebröd AB Gustaf's Knäckebröd 21.0000 | |
| PB Knäckebröd AB Tunnbröd 9.0000 | |
| Refrescos Americanas LTDA Guaraná Fantástica 4.5000 | |
| Heli Süßwaren GmbH & Co. KG NuNuCa Nuß-Nougat-Creme 14.0000 | |
| Heli Süßwaren GmbH & Co. KG Gumbär Gummibärchen 31.2300 | |
| Heli Süßwaren GmbH & Co. KG Schoggi Schokolade 43.9000 | |
| Plutzer Lebensmittelgroßmärkte AG Rössle Sauerkraut 45.6000 | |
| Plutzer Lebensmittelgroßmärkte AG Thüringer Rostbratwurst 123.7900 | |
| Nord-Ost-Fisch Handelsgesellschaft mbH Nord-Ost Matjeshering 25.8900 | |
| Formaggi Fortini s.r.l. Gorgonzola Telino 12.5000 | |
| Formaggi Fortini s.r.l. Mascarpone Fabioli 32.0000 | |
| Norske Meierier Geitost 2.5000 | |
| Bigfoot Breweries Sasquatch Ale 14.0000 | |
| Bigfoot Breweries Steeleye Stout 18.0000 | |
| Svensk Sjöföda AB Inlagd Sill 19.0000 | |
| Svensk Sjöföda AB Gravad lax 26.0000 | |
| Aux joyeux ecclésiastiques Côte de Blaye 263.5000 | |
| Aux joyeux ecclésiastiques Chartreuse verte 18.0000 | |
| New England Seafood Cannery Boston Crab Meat 18.4000 | |
| New England Seafood Cannery Jack's New England Clam Chowder 9.6500 | |
| Leka Trading Singaporean Hokkien Fried Mee 14.0000 | |
| Leka Trading Ipoh Coffee 46.0000 | |
| Leka Trading Gula Malacca 19.4500 | |
| Lyngbysild Rogede sild 9.5000 | |
| Lyngbysild Spegesild 12.0000 | |
| Zaanse Snoepfabriek Zaanse koeken 9.5000 | |
| Zaanse Snoepfabriek Chocolade 12.7500 | |
| Karkki Oy Maxilaku 20.0000 | |
| Karkki Oy Valkoinen suklaa 16.2500 | |
| G'day, Mate Manjimup Dried Apples 53.0000 | |
| G'day, Mate Filo Mix 7.0000 | |
| G'day, Mate Perth Pasties 32.8000 | |
| Ma Maison Tourtière 7.4500 | |
| Ma Maison Pâté chinois 24.0000 | |
| Pasta Buttini s.r.l. Gnocchi di nonna Alice 38.0000 | |
| Pasta Buttini s.r.l. Ravioli Angelo 19.5000 | |
| Escargots Nouveaux Escargots de Bourgogne 13.2500 | |
| Gai pâturage Raclette Courdavault 55.0000 | |
| Gai pâturage Camembert Pierrot 34.0000 | |
| Forêts d'érables Sirop d'érable 28.5000 | |
| Forêts d'érables Tarte au sucre 49.3000 | |
| Pavlova, Ltd. Vegie-spread 43.9000 | |
| Plutzer Lebensmittelgroßmärkte AG Wimmers gute Semmelknödel 33.2500 | |
| New Orleans Cajun Delights Louisiana Fiery Hot Pepper Sauce 21.0500 | |
| New Orleans Cajun Delights Louisiana Hot Spiced Okra 17.0000 | |
| Bigfoot Breweries Laughing Lumberjack Lager 14.0000 | |
| Specialty Biscuits, Ltd. Scottish Longbreads 12.5000 | |
| Norske Meierier Gudbrandsdalsost 36.0000 | |
| Pavlova, Ltd. Outback Lager 15.0000 | |
| Norske Meierier Flotemysost 21.5000 | |
| Formaggi Fortini s.r.l. Mozzarella di Giovanni 34.8000 | |
| Svensk Sjöföda AB Röd Kaviar 15.0000 | |
| Tokyo Traders Longlife Tofu 10.0000 | |
| Plutzer Lebensmittelgroßmärkte AG Rhönbräu Klosterbier 7.7500 | |
| Karkki Oy Lakkalikööri 18.0000 | |
| Plutzer Lebensmittelgroßmärkte AG Original Frankfurter grüne Soße 13.0000 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Exotic Liquids Chai 18.0000 | |
| Exotic Liquids Chang 19.0000 | |
| Exotic Liquids Aniseed Syrup 10.0000 | |
| New Orleans Cajun Delights Chef Anton's Cajun Seasoning 22.0000 | |
| New Orleans Cajun Delights Chef Anton's Gumbo Mix 21.3500 | |
| Grandma Kelly's Homestead Grandma's Boysenberry Spread 25.0000 | |
| Grandma Kelly's Homestead Uncle Bob's Organic Dried Pears 30.0000 | |
| Grandma Kelly's Homestead Northwoods Cranberry Sauce 40.0000 | |
| Tokyo Traders Mishi Kobe Niku 97.0000 | |
| Tokyo Traders Ikura 31.0000 | |
| Cooperativa de Quesos 'Las Cabras' Queso Cabrales 21.0000 | |
| Cooperativa de Quesos 'Las Cabras' Queso Manchego La Pastora 38.0000 | |
| Mayumi's Konbu 6.0000 | |
| Mayumi's Tofu 23.2500 | |
| Mayumi's Genen Shouyu 15.5000 | |
| Pavlova, Ltd. Pavlova 17.4500 | |
| Pavlova, Ltd. Alice Mutton 39.0000 | |
| Pavlova, Ltd. Carnarvon Tigers 62.5000 | |
| Specialty Biscuits, Ltd. Teatime Chocolate Biscuits 9.2000 | |
| Specialty Biscuits, Ltd. Sir Rodney's Marmalade 81.0000 | |
| Specialty Biscuits, Ltd. Sir Rodney's Scones 10.0000 | |
| PB Knäckebröd AB Gustaf's Knäckebröd 21.0000 | |
| PB Knäckebröd AB Tunnbröd 9.0000 | |
| Refrescos Americanas LTDA Guaraná Fantástica 4.5000 | |
| Heli Süßwaren GmbH & Co. KG NuNuCa Nuß-Nougat-Creme 14.0000 | |
| Heli Süßwaren GmbH & Co. KG Gumbär Gummibärchen 31.2300 | |
| Heli Süßwaren GmbH & Co. KG Schoggi Schokolade 43.9000 | |
| Plutzer Lebensmittelgroßmärkte AG Rössle Sauerkraut 45.6000 | |
| Plutzer Lebensmittelgroßmärkte AG Thüringer Rostbratwurst 123.7900 | |
| Nord-Ost-Fisch Handelsgesellschaft mbH Nord-Ost Matjeshering 25.8900 | |
| Formaggi Fortini s.r.l. Gorgonzola Telino 12.5000 | |
| Formaggi Fortini s.r.l. Mascarpone Fabioli 32.0000 | |
| Norske Meierier Geitost 2.5000 | |
| Bigfoot Breweries Sasquatch Ale 14.0000 | |
| Bigfoot Breweries Steeleye Stout 18.0000 | |
| Svensk Sjöföda AB Inlagd Sill 19.0000 | |
| Svensk Sjöföda AB Gravad lax 26.0000 | |
| Aux joyeux ecclésiastiques Côte de Blaye 263.5000 | |
| Aux joyeux ecclésiastiques Chartreuse verte 18.0000 | |
| New England Seafood Cannery Boston Crab Meat 18.4000 | |
| New England Seafood Cannery Jack's New England Clam Chowder 9.6500 | |
| Leka Trading Singaporean Hokkien Fried Mee 14.0000 | |
| Leka Trading Ipoh Coffee 46.0000 | |
| Leka Trading Gula Malacca 19.4500 | |
| Lyngbysild Rogede sild 9.5000 | |
| Lyngbysild Spegesild 12.0000 | |
| Zaanse Snoepfabriek Zaanse koeken 9.5000 | |
| Zaanse Snoepfabriek Chocolade 12.7500 | |
| Karkki Oy Maxilaku 20.0000 | |
| Karkki Oy Valkoinen suklaa 16.2500 | |
| G'day, Mate Manjimup Dried Apples 53.0000 | |
| G'day, Mate Filo Mix 7.0000 | |
| G'day, Mate Perth Pasties 32.8000 | |
| Ma Maison Tourtière 7.4500 | |
| Ma Maison Pâté chinois 24.0000 | |
| Pasta Buttini s.r.l. Gnocchi di nonna Alice 38.0000 | |
| Pasta Buttini s.r.l. Ravioli Angelo 19.5000 | |
| Escargots Nouveaux Escargots de Bourgogne 13.2500 | |
| Gai pâturage Raclette Courdavault 55.0000 | |
| Gai pâturage Camembert Pierrot 34.0000 | |
| Forêts d'érables Sirop d'érable 28.5000 | |
| Forêts d'érables Tarte au sucre 49.3000 | |
| Pavlova, Ltd. Vegie-spread 43.9000 | |
| Plutzer Lebensmittelgroßmärkte AG Wimmers gute Semmelknödel 33.2500 | |
| New Orleans Cajun Delights Louisiana Fiery Hot Pepper Sauce 21.0500 | |
| New Orleans Cajun Delights Louisiana Hot Spiced Okra 17.0000 | |
| Bigfoot Breweries Laughing Lumberjack Lager 14.0000 | |
| Specialty Biscuits, Ltd. Scottish Longbreads 12.5000 | |
| Norske Meierier Gudbrandsdalsost 36.0000 | |
| Pavlova, Ltd. Outback Lager 15.0000 | |
| Norske Meierier Flotemysost 21.5000 | |
| Formaggi Fortini s.r.l. Mozzarella di Giovanni 34.8000 | |
| Svensk Sjöföda AB Röd Kaviar 15.0000 | |
| Tokyo Traders Longlife Tofu 10.0000 | |
| Plutzer Lebensmittelgroßmärkte AG Rhönbräu Klosterbier 7.7500 | |
| Karkki Oy Lakkalikööri 18.0000 | |
| Plutzer Lebensmittelgroßmärkte AG Original Frankfurter grüne Soße 13.0000 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| use northwind3 | |
| go | |
| SELECT sp.CompanyName, p.ProductName, p.UnitPrice | |
| FROM Suppliers sp, Products P | |
| WHERE | |
| sp.SupplierID = p.SupplierID | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| use northwind3 | |
| go | |
| SELECT TOP(10000) p.ProductName, od.UnitPrice *(1-od.Discount) as SalePrice, | |
| p.UnitPrice as CostPrice, (od.UnitPrice*(1-od.Discount)- p.UnitPrice- o.Freight/od.Quantity) as Profit | |
| FROM [Order Details] od, Products P, Orders o | |
| WHERE | |
| od.ProductID = p.ProductID and o.OrderID = od.OrderID | |
| ORDER BY profit DESC |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Albuquerque 51097.8003330231 | |
| Anchorage 15177.4624938965 | |
| Boise 104361.949920654 | |
| Butte 1947.23999023438 | |
| Elgin 3063.20000076294 | |
| Eugene 18507.4499664307 | |
| Kirkland 1571.19999313354 | |
| Lander 11441.6299972534 | |
| Portland 7619.60001373291 | |
| San Francisco 3076.47247505188 | |
| Seattle 27363.6050434113 | |
| Walla Walla 357 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Aachen 3763.21001434326 | |
| Århus 15843.9248199463 | |
| Barcelona 836.699996948242 | |
| Barquisimeto 16076.5999908447 | |
| Bergamo 7176.21500205994 | |
| Berlin 4272.99999809265 | |
| Bern 12348.8800125122 | |
| Bräcke 29567.5625762939 | |
| Brandenburg 30908.3839836121 | |
| Bruxelles 9736.07500457764 | |
| Buenos Aires 8119.09997558594 | |
| Campinas 8414.13500213623 | |
| Caracas 1488.69999694824 | |
| Charleroi 24088.7799758911 | |
| Cork 49979.9050006866 | |
| Cowes 6146.29999542236 | |
| Cunewalde 110277.304977417 | |
| Frankfurt a.M. 19261.4100112915 | |
| Genève 19343.7788906097 | |
| Graz 104874.978713989 | |
| Helsinki 3161.35000610352 | |
| I. de Margarita 16476.5650253296 | |
| Kobenhavn 16817.0975255966 | |
| Köln 12496.1999893188 | |
| Leipzig 5042.19998168945 | |
| Lille 11666.9000015259 | |
| Lisboa 11472.3626556396 | |
| London 52825.0100679398 | |
| Luleå 24927.5774688721 | |
| Lyon 9182.42999076843 | |
| Madrid 5700.14009380341 | |
| Mannheim 3239.80000305176 | |
| Marseille 21963.2524261475 | |
| México D.F. 23582.0776252747 | |
| Montréal 28872.1902999878 | |
| München 26656.559387207 | |
| Münster 4778.13998413086 | |
| Nantes 4788.06007385254 | |
| Oulu 15648.7025642395 | |
| Paris 2423.34999847412 | |
| Reggio Emilia 7048.23990440369 | |
| Reims 1480.00000762939 | |
| Resende 6068.19990634918 | |
| Rio de Janeiro 51956.979932785 | |
| Salzburg 23128.8600311279 | |
| San Cristóbal 22768.7639884949 | |
| Sao Paulo 40486.4615192413 | |
| Sevilla 11446.3600158691 | |
| Stavern 5735.14999961853 | |
| Strasbourg 18534.0799789429 | |
| Stuttgart 9588.42500305176 | |
| Torino 1545.69999885559 | |
| Toulouse 9328.20000362396 | |
| Tsawassen 19999.6000213623 | |
| Vancouver 522.5 | |
| Versailles 1992.04999542236 | |
| Warszawa 3531.94997596741 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| use northwind3 | |
| go | |
| SELECT TOP(100) c.city, sum(od.UnitPrice* od.Quantity * (1- od.Discount)) as SalesAmount | |
| FROM [Order Details] od, Orders o, Customers c | |
| WHERE | |
| od.OrderID = o.OrderID and o.CustomerID = c.CustomerID | |
| GROUP BY c.city | |
| ORDER BY c.city |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment