Bygg ut frågan i exemplet ovan så att customerid från tabellen customer visas och även finns med i group by-delen. Dessutom lägg till count(*) och count(orderid) i select-delen. Count(*) räknar antal poster (rader), count(orderid) visar antalet rader där orderid inte är null. Förklara resultatet
93 rader
| CustomerID | COUNT(*) | COUNT(orderid) |
|---|---|---|
| ALFKI | 6 | 6 |
| ANATR | 4 | 4 |
| ANTON | 7 | 7 |
| AROUT | 13 | 13 |
| BERGS | 18 | 18 |
| BLAUS | 7 | 7 |
| BLONP | 11 | 11 |
| BOLID | 3 | 3 |
| BONAP | 17 | 17 |
| BOTTM | 14 | 14 |
| BSBEV | 10 | 10 |
| CACTU | 6 | 6 |
| CENTC | 1 | 1 |
| CHOPS | 8 | 8 |
| COMMI | 5 | 5 |
| CONSH | 3 | 3 |
| DRACD | 6 | 6 |
| DUMON | 4 | 4 |
| EASTC | 8 | 8 |
| ERNSH | 30 | 30 |
| FAMIA | 7 | 7 |
| 1 | 0 | |
| FOLIG | 5 | 5 |
| FOLKO | 19 | 19 |
| FRANK | 15 | 15 |
| FRANR | 3 | 3 |
| FRANS | 6 | 6 |
| FURIB | 8 | 8 |
| GALED | 5 | 5 |
| GODOS | 10 | 10 |
| GOURL | 9 | 9 |
| GREAL | 11 | 11 |
| GROSR | 2 | 2 |
| HANAR | 14 | 14 |
| HILAA | 18 | 18 |
| HUNGC | 5 | 5 |
| HUNGO | 19 | 19 |
| ISLAT | 10 | 10 |
| KOENE | 14 | 14 |
| LACOR | 4 | 4 |
| LAMAI | 14 | 14 |
| LAUGB | 3 | 3 |
| LAZYK | 2 | 2 |
| LEHMS | 15 | 15 |
| LETSS | 4 | 4 |
| LILAS | 14 | 14 |
| LINOD | 12 | 12 |
| LONEP | 8 | 8 |
| MAGAA | 10 | 10 |
| MAISD | 7 | 7 |
| MEREP | 13 | 13 |
| MORGK | 5 | 5 |
| NORTS | 3 | 3 |
| OCEAN | 5 | 5 |
| OLDWO | 10 | 10 |
| OTTIK | 10 | 10 |
| 1 | 0 | |
| PERIC | 6 | 6 |
| PICCO | 10 | 10 |
| PRINI | 5 | 5 |
| QUEDE | 9 | 9 |
| QUEEN | 13 | 13 |
| QUICK | 28 | 28 |
| RANCH | 5 | 5 |
| RATTC | 18 | 18 |
| REGGC | 12 | 12 |
| RICAR | 11 | 11 |
| RICSU | 10 | 10 |
| ROMEY | 5 | 5 |
| SANTG | 6 | 6 |
| SAVEA | 31 | 31 |
| SEVES | 9 | 9 |
| SIMOB | 7 | 7 |
| SPECD | 4 | 4 |
| SPLIR | 9 | 9 |
| SUPRD | 12 | 12 |
| THEBI | 4 | 4 |
| THECR | 3 | 3 |
| TOMSP | 6 | 6 |
| TORTU | 10 | 10 |
| TRADH | 6 | 6 |
| TRAIH | 3 | 3 |
| VAFFE | 11 | 11 |
| 1 | 0 | |
| 1 | 0 | |
| VICTE | 10 | 10 |
| VINET | 5 | 5 |
| WANDK | 10 | 10 |
| WARTH | 15 | 15 |
| WELLI | 9 | 9 |
| WHITC | 14 | 14 |
| WILMK | 7 | 7 |
| WOLZA | 7 | 7 |
Visa alla produkter som har ett högre pris (unit price) än ’Ikura’.
23 rader
| ProductID | ProductName | UnitPrice |
|---|---|---|
| 8 | Northwoods Cranberry Sauce | 40.0000 |
| 9 | Mishi Kobe Niku | 97.0000 |
| 12 | Queso Manchego La Pastora | 38.0000 |
| 17 | Alice Mutton | 39.0000 |
| 18 | Carnarvon Tigers | 62.5000 |
| 20 | Sir Rodney's Marmalade | 81.0000 |
| 26 | Gumbr Gummibrchen | 31.2300 |
| 27 | Schoggi Schokolade | 43.9000 |
| 28 | Rssle Sauerkraut | 45.6000 |
| 29 | Thringer Rostbratwurst | 123.7900 |
| 32 | Mascarpone Fabioli | 32.0000 |
| 38 | Cte de Blaye | 263.5000 |
| 43 | Ipoh Coffee | 46.0000 |
| 51 | Manjimup Dried Apples | 53.0000 |
| 53 | Perth Pasties | 32.8000 |
| 56 | Gnocchi di nonna Alice | 38.0000 |
| 59 | Raclette Courdavault | 55.0000 |
| 60 | Camembert Pierrot | 34.0000 |
| 62 | Tarte au sucre | 49.3000 |
| 63 | Vegie-spread | 43.9000 |
| 64 | Wimmers gute Semmelkndel | 33.2500 |
| 69 | Gudbrandsdalsost | 36.0000 |
| 72 | Mozzarella di Giovanni | 34.8000 |
Visa alla anställda som bor i samma stad (city)som kunden (customer) B's Beverages. (för att få med ' i wheresatsen skriver du '' [alltså två enkelfnuttar], i C# hade du skrivit \')
4 rader
| EmployeeID | LastName | FirstName | Title | TitleOfCourtesy | BirthDate | HireDate | Address | City | Region | PostalCode | Country | HomePhone | Extension | Photo | Notes | ReportsTo | PhotoPath | Salary |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 5 | Buchanan | Steven | Sales Manager | Mr. | 1955-03-04 00:00:00 | 1993-10-17 00:00:00 | 14 Garrett Hill | London | SW1 8JR | UK | (71) 555-4848 | 3453 | img | img | 2 | http://accweb/emmployees/buchanan.bmp | 1744.21 | |
| 6 | Suyama | Michael | Sales Representative | Mr. | 1963-07-02 00:00:00 | 1993-10-17 00:00:00 | Coventry House Miner Rd. | London | EC2 7JR | UK | (71) 555-7773 | 428 | img | img | 5 | http://accweb/emmployees/davolio.bmp | 2004.07 | |
| 7 | King | Robert | Sales Representative | Mr. | 1960-05-29 00:00:00 | 1994-01-02 00:00:00 | Edgeham Hollow Winchester Way | London | RG1 9SP | UK | (71) 555-5598 | 465 | img | img | 5 | http://accweb/emmployees/davolio.bmp | 1991.55 | |
| 9 | Dodsworth | Anne | Sales Representative | Ms. | 1966-01-27 00:00:00 | 1994-11-15 00:00:00 | 7 Houndstooth Rd. | London | WG2 7LT | UK | (71) 555-4444 | 452 | img | img | 5 | http://accweb/emmployees/davolio.bmp | 2333.33 |
Visa alla kunder (customers) som inte har lagt någon order (orders).
4 rader
| CustomerID | CompanyName | ContactName | ContactTitle | Address | City | Region | PostalCode | Country | Phone | Fax |
|---|---|---|---|---|---|---|---|---|---|---|
| FISSA | FISSA Fabrica Inter. Salchichas S.A. | Diego Roel | Accounting Manager | C/ Moralzarzal, 86 | Madrid | 28034 | Spain | (91) 555 94 44 | (91) 555 55 93 | |
| PARIS | Paris spcialits | Marie Bertrand | Owner | 265, boulevard Charonne | Paris | 75012 | France | (1) 42.34.22.66 | (1) 42.34.22.77 | |
| Val2 | IT | Val2 | IT | |||||||
| VALON | IT | Valon Hoti | IT |
Visa alla ordrar där totalsumman är mer än 2000.
211 rader
| OrderID | Ordersumma |
|---|---|
| 10252 | 3730.0000 |
| 10255 | 2490.5000 |
| 10258 | 2018.6000 |
| 10263 | 2464.8000 |
| 10267 | 4031.0000 |
| 10273 | 2142.4000 |
| 10285 | 2179.2000 |
| 10286 | 3016.0000 |
| 10290 | 2169.0000 |
| 10298 | 3127.0000 |
| 10302 | 2708.8000 |
| 10305 | 4157.0000 |
| 10314 | 2327.0000 |
| 10316 | 2835.0000 |
| 10324 | 6155.9000 |
| 10327 | 2262.5000 |
| 10329 | 4819.4000 |
| 10332 | 2233.6000 |
| 10335 | 2545.2000 |
| 10337 | 2467.0000 |
| 10339 | 3463.2000 |
| 10340 | 2564.4000 |
| 10342 | 2300.8000 |
| 10344 | 2856.0000 |
| 10345 | 2924.8000 |
| 10351 | 5677.6000 |
| 10353 | 10741.6000 |
| 10359 | 3654.4000 |
| 10360 | 7390.2000 |
| 10361 | 2273.6000 |
| 10369 | 2527.2000 |
| 10372 | 12281.2000 |
| 10382 | 2900.0000 |
| 10384 | 2222.4000 |
| 10390 | 2275.2000 |
| 10393 | 3302.6000 |
| 10395 | 2333.2000 |
| 10398 | 2736.0000 |
| 10400 | 3063.0000 |
| 10401 | 3868.6000 |
| 10402 | 2713.5000 |
| 10406 | 2018.2000 |
| 10413 | 2123.2000 |
| 10417 | 11283.2000 |
| 10419 | 2208.0000 |
| 10424 | 11493.2000 |
| 10430 | 5796.0000 |
| 10431 | 2523.0000 |
| 10436 | 2210.8000 |
| 10440 | 5793.1000 |
| 10451 | 4277.4000 |
| 10452 | 2096.0000 |
| 10455 | 2684.0000 |
| 10458 | 3891.0000 |
| 10461 | 2051.6000 |
| 10465 | 2719.0000 |
| 10479 | 10495.6000 |
| 10490 | 3163.2000 |
| 10503 | 2048.5000 |
| 10510 | 4735.4400 |
| 10511 | 3000.0000 |
| 10513 | 2427.5000 |
| 10514 | 8623.4500 |
| 10515 | 10588.5000 |
| 10516 | 2614.5000 |
| 10518 | 4150.0500 |
| 10519 | 2356.0000 |
| 10522 | 2657.8000 |
| 10523 | 2715.9000 |
| 10524 | 3192.6500 |
| 10530 | 4180.0000 |
| 10533 | 2295.2000 |
| 10535 | 2156.5000 |
| 10536 | 2085.0000 |
| 10540 | 10191.7000 |
| 10541 | 2162.8000 |
| 10546 | 2812.0000 |
| 10549 | 4181.5000 |
| 10555 | 3680.5000 |
| 10558 | 2142.9000 |
| 10561 | 2844.5000 |
| 10566 | 2040.0000 |
| 10567 | 3109.0000 |
| 10570 | 2595.0000 |
| 10573 | 2082.0000 |
| 10575 | 2147.4000 |
| 10583 | 2413.9000 |
| 10588 | 3900.0000 |
| 10593 | 2493.0000 |
| 10595 | 6300.0000 |
| 10598 | 2388.5000 |
| 10601 | 2285.0000 |
| 10605 | 4326.0000 |
| 10607 | 6475.4000 |
| 10612 | 6375.0000 |
| 10616 | 5032.0000 |
| 10618 | 2697.5000 |
| 10629 | 2775.0500 |
| 10633 | 6483.0500 |
| 10634 | 4985.5000 |
| 10637 | 2896.2500 |
| 10638 | 2720.0500 |
| 10641 | 2054.0000 |
| 10657 | 4371.6000 |
| 10658 | 4668.0000 |
| 10663 | 2032.0000 |
| 10666 | 4666.9400 |
| 10670 | 2301.7500 |
| 10672 | 4210.5000 |
| 10678 | 5256.5000 |
| 10687 | 6201.9000 |
| 10688 | 3490.0000 |
| 10691 | 10164.8000 |
| 10693 | 2334.0000 |
| 10694 | 4825.0000 |
| 10698 | 3600.7300 |
| 10700 | 2048.0000 |
| 10701 | 3370.0000 |
| 10703 | 2545.0000 |
| 10709 | 3424.0000 |
| 10711 | 4451.7000 |
| 10713 | 2827.9000 |
| 10714 | 2941.0000 |
| 10718 | 3463.0000 |
| 10742 | 3118.0000 |
| 10745 | 4529.8000 |
| 10746 | 2311.7000 |
| 10748 | 2196.0000 |
| 10755 | 2598.0000 |
| 10756 | 2487.5000 |
| 10757 | 3082.0000 |
| 10760 | 3304.0000 |
| 10762 | 4337.0000 |
| 10764 | 2540.0000 |
| 10766 | 2310.0000 |
| 10772 | 3603.2200 |
| 10773 | 2216.2500 |
| 10776 | 6984.5000 |
| 10787 | 2760.8000 |
| 10789 | 3687.0000 |
| 10795 | 2499.2500 |
| 10796 | 2878.0800 |
| 10801 | 4035.8000 |
| 10802 | 3923.7500 |
| 10804 | 2290.4000 |
| 10805 | 2775.0000 |
| 10814 | 2070.0000 |
| 10816 | 8891.0000 |
| 10817 | 11490.7000 |
| 10823 | 3107.5000 |
| 10831 | 2684.4000 |
| 10836 | 4705.5000 |
| 10838 | 2584.5000 |
| 10841 | 4581.0000 |
| 10845 | 4059.0000 |
| 10847 | 6164.9000 |
| 10851 | 2740.0000 |
| 10852 | 2984.0000 |
| 10854 | 3490.0000 |
| 10855 | 2275.2500 |
| 10857 | 2630.9500 |
| 10861 | 3523.4000 |
| 10865 | 17250.0000 |
| 10868 | 2004.6000 |
| 10871 | 2083.4000 |
| 10872 | 2166.8000 |
| 10877 | 2086.0000 |
| 10886 | 3127.5000 |
| 10889 | 11380.0000 |
| 10892 | 2200.0000 |
| 10893 | 5502.1100 |
| 10894 | 2898.0000 |
| 10895 | 6379.4000 |
| 10897 | 10835.2400 |
| 10912 | 8267.4000 |
| 10924 | 2034.5000 |
| 10930 | 2455.0000 |
| 10938 | 3642.5000 |
| 10941 | 4769.0000 |
| 10948 | 2362.2500 |
| 10949 | 4422.0000 |
| 10953 | 4675.0000 |
| 10962 | 3584.0000 |
| 10964 | 2052.5000 |
| 10977 | 2233.0000 |
| 10979 | 4813.5000 |
| 10981 | 15810.0000 |
| 10985 | 2248.2000 |
| 10986 | 2220.0000 |
| 10987 | 2772.0000 |
| 10988 | 3772.0000 |
| 10990 | 4931.0000 |
| 10991 | 2870.0000 |
| 10993 | 6527.2500 |
| 11001 | 2769.0000 |
| 11007 | 2633.9000 |
| 11008 | 4903.5000 |
| 11012 | 2974.0000 |
| 11017 | 6750.0000 |
| 11021 | 6941.4900 |
| 11028 | 2160.0000 |
| 11030 | 16321.9000 |
| 11031 | 2393.5000 |
| 11032 | 8902.5000 |
| 11033 | 3592.0000 |
| 11039 | 3090.0000 |
| 11053 | 3658.7500 |
| 11056 | 3740.0000 |
| 11064 | 4722.3000 |
| 11068 | 2384.8000 |
| 11072 | 5218.0000 |
Hur många order har varje kund lagt, visa bara de kunder som har minst 10 ordrar. Visa kunden med flest ordrar först.
28 rader
| CustomerID | AntalOrder |
|---|---|
| SAVEA | 31 |
| ERNSH | 30 |
| QUICK | 28 |
| HUNGO | 19 |
| FOLKO | 19 |
| RATTC | 18 |
| BERGS | 18 |
| HILAA | 18 |
| BONAP | 17 |
| WARTH | 15 |
| LEHMS | 15 |
| FRANK | 15 |
| BOTTM | 14 |
| KOENE | 14 |
| HANAR | 14 |
| WHITC | 14 |
| LILAS | 14 |
| LAMAI | 14 |
| AROUT | 13 |
| QUEEN | 13 |
| MEREP | 13 |
| SUPRD | 12 |
| REGGC | 12 |
| LINOD | 12 |
| GREAL | 11 |
| RICAR | 11 |
| VAFFE | 11 |
| BLONP | 11 |
Visa högsta, lägsta och genomsnittligt pris för produkter i varje kategori, visa bara de kategorier som har minst 10 produkter
4 rader
| CategoryID | Högsta | Lägsta | Genomsnittlig |
|---|---|---|---|
| 1 | 263.5000 | 4.5000 | 37.97916667 |
| 2 | 43.9000 | 10.0000 | 23.06250000 |
| 3 | 81.0000 | 9.2000 | 25.16000000 |
| 8 | 62.5000 | 6.0000 | 20.68250000 |
Visa högsta, lägsta och genomsnittligt pris för produkter i varje kategori, visa bara de kategorier vars medelpris är högre än medelpriset för alla produkter (oavsett kategori) som har minst 10 produkter.
3 rader
| CategoryID | Högsta | Lägsta | Genomsnittlig |
|---|---|---|---|
| 1 | 263.5000 | 4.5000 | 37.97916667 |
| 6 | 123.7900 | 7.4500 | 54.00666667 |
| 7 | 53.0000 | 10.0000 | 32.37000000 |