怎么样宣传自己的网站,外发加工网站源码下载,商务网站开发流程,做不规则几何图形的网站文章目录 一. 数据介绍1. 数据结构2. 创建数据库,数据表3. 员工表(employees)练习1 4. 顾客表(customers)练习2 5. 商品(products)和商品类别(categories)表练习3 6. 供应商表#xff08;suppliers#xff09;练习4 7. 订单和订单明细表练习5 二. 数据汇总三. 使用CASE WHEN … 文章目录 一. 数据介绍1. 数据结构2. 创建数据库,数据表3. 员工表(employees)练习1 4. 顾客表(customers)练习2 5. 商品(products)和商品类别(categories)表练习3 6. 供应商表suppliers练习4 7. 订单和订单明细表练习5 二. 数据汇总三. 使用CASE WHEN 和 GROUP BY将数据分组四. 窗口函数 一. 数据介绍
报表项目数据.sql(原始数据下载)
1. 数据结构 2. 创建数据库,数据表
3. 员工表(employees) 保存员工基本信息 唯一ID (employee_id).姓名(first_name and last_name).职务 (title). 需要注意的是 reports_to这一列, 保存的是员工所对应的直属领导的员工ID (也在这张表中保存) 此外还有其它列包括入职时间生日… …
练习1 选中employees 表的所有数据 select * from employeesemployee_idlast_namefirst_nametitlebirth_datehire_dateaddresscityregionpostal_codecountryreports_to1DavolioNancySales Representative1968-12-082012-05-01507 - 20th Ave. E. Apt. 2ASeattleWA98122USA22FullerAndrewVice President, Sales1972-02-192012-08-14908 W. Capital WayTacomaWA98401USAnull3SmithJohnSales Representative1983-08-302012-04-01722 Moss Bay Blvd.KirklandWA98033USA24PeacockMargaretSales Representative1957-09-192013-05-034110 Old Redmond Rd.RedmondWA98052USA25BuchananStevenSales Manager1975-03-042013-10-1714 Garrett HillLondonnullSW1 8JRUK26SuyamaMichaelSales Representative1983-07-022013-10-17Coventry House Miner Rd.LondonnullEC2 7JRUK57KingRobertSales Representative1980-05-292014-01-02Edgeham Hollow Winchester WayLondonnullRG1 9SPUK58CallahanLauraInside Sales Coordinator1978-01-092014-03-054726 - 11th Ave. N.E.SeattleWA98105USA29DodsworthAnneSales Representative1986-01-272014-11-157 Houndstooth Rd.LondonnullWG2 7LTUK510SmithJohnSales Representative1994-08-302017-03-2122 Abbey RdLondonnullNW6 5JGUK2
4. 顾客表(customers)
每一个顾客都有唯一IDcustomer_id, 顾客的ID是公司全名的缩写用5个字母表示公司全名在 company_name 列中保存contact_name 和 contact_title 两列代表了客户公司的联系人信息名字和职务 除此之外还保存了顾客的地址信息和联系方式city, region, postal_code, country, fax
customer_idcompany_namecontact_namecontact_titleaddresscityregionpostal_codecountryfaxALFKIAlfreds FutterkisteMaria AndersSales RepresentativeObere Str. 57Berlinnull12209Germany030-0076545ANATRAna Trujillo Emparedados y heladosAna TrujilloOwnerAvda. de la Constitución 2222México D.F.null05021Mexico(5) 555-3745ANTONAntonio Moreno TaqueríaAntonio MorenoOwnerMataderos 2312México D.F.null05023MexiconullAROUTAround the HornThomas HardySales Representative120 Hanover Sq.LondonnullWA1 1DPUK(171) 555-6750BERGSBerglunds snabbköpChristina BerglundOrder AdministratorBerguvsvägen 8LuleånullS-958 22Sweden0921-12 34 67BLAUSBlauer See DelikatessenHanna MoosSales RepresentativeForsterstr. 57Mannheimnull68306Germany0621-08924BLONPBlondesddsl père et filsFrédérique CiteauxMarketing Manager24, place KléberStrasbourgnull67000France88.60.15.32BOLIDBólido Comidas preparadasMartín SommerOwnerC/ Araquil, 67Madridnull28023Spain(91) 555 91 99BONAPBon app’Laurence LebihanOwner12, rue des BouchersMarseillenull13008France91.24.45.41BOTTMBottom-Dollar MarketsElizabeth LincolnAccounting Manager23 Tsawassen Blvd.TsawassenBCT2F 8M4Canada(604) 555-3745BSBEVB’s BeveragesVictoria AshworthSales RepresentativeFauntleroy CircusLondonnullEC2 5NTUKnullCACTUCactus Comidas para llevarPatricio SimpsonSales AgentCerrito 333Buenos Airesnull1010Argentina(1) 135-4892CENTCCentro comercial MoctezumaFrancisco ChangMarketing ManagerSierras de Granada 9993México D.F.null05022Mexico(5) 555-7293CHOPSChop-suey ChineseYang WangOwnerHauptstr. 29Bernnull3012SwitzerlandnullCOMMIComércio MineiroPedro AfonsoSales AssociateAv. dos Lusíadas, 23Sao PauloSP05432-043BrazilnullCONSHConsolidated HoldingsElizabeth BrownSales RepresentativeBerkeley Gardens 12 BreweryLondonnullWX1 6LTUK(171) 555-9199DRACDDrachenblut DelikatessenSven OttliebOrder AdministratorWalserweg 21Aachennull52066Germany0241-059428DUMONDu monde entierJanine LabruneOwner67, rue des Cinquante OtagesNantesnull44000France40.67.89.89EASTCEastern ConnectionAnn DevonSales Agent35 King GeorgeLondonnullWX3 6FWUK(171) 555-3373ERNSHErnst HandelRoland MendelSales ManagerKirchgasse 6Graznull8010Austria7675-3426FAMIAFamilia ArquibaldoAria CruzMarketing AssistantRua Orós, 92Sao PauloSP05442-030BrazilnullFISSAFISSA Fabrica Inter. Salchichas S.A.Diego RoelAccounting ManagerC/ Moralzarzal, 86Madridnull28034Spain(91) 555 55 93FOLIGFolies gourmandesMartine RancéAssistant Sales Agent184, chaussée de TournaiLillenull59000France20.16.10.17FOLKOFolk och fä HBMaria LarssonOwnerÅkergatan 24BräckenullS-844 67SwedennullFRANKFrankenversandPeter FrankenMarketing ManagerBerliner Platz 43Münchennull80805Germany089-0877451FRANRFrance restaurationCarine SchmittMarketing Manager54, rue RoyaleNantesnull44000France40.32.21.20FRANSFranchi S.p.A.Paolo AccortiSales RepresentativeVia Monte Bianco 34Torinonull10100Italy011-4988261FURIBFuria Bacalhau e Frutos do MarLino RodriguezSales ManagerJardim das rosas n. 32Lisboanull1675Portugal(1) 354-2535GALEDGalería del gastrónomoEduardo SaavedraMarketing ManagerRambla de Cataluña, 23Barcelonanull08022Spain(93) 203 4561GODOSGodos Cocina TípicaJosé Pedro FreyreSales ManagerC/ Romero, 33Sevillanull41101SpainnullGOURLGourmet LanchonetesAndré FonsecaSales AssociateAv. Brasil, 442CampinasSP04876-786BrazilnullGREALGreat Lakes Food MarketHoward SnyderMarketing Manager2732 Baker Blvd.EugeneOR97403USAnullGROSRGROSELLA-RestauranteManuel PereiraOwner5ª Ave. Los Palos GrandesCaracasDF1081Venezuela(2) 283-3397HANARHanari CarnesMario PontesAccounting ManagerRua do Paço, 67Rio de JaneiroRJ05454-876Brazil(21) 555-8765HILAAHILARION-AbastosCarlos HernándezSales RepresentativeCarrera 22 con Ave. Carlos Soublette #8-35San CristóbalTáchira5022Venezuela(5) 555-1948HUNGCHungry Coyote Import StoreYoshi LatimerSales RepresentativeCity Center Plaza 516 Main St.ElginOR97827USA(503) 555-2376HUNGOHungry Owl All-Night GrocersPatricia McKennaSales Associate8 Johnstown RoadCorkCo. CorknullIreland2967 3333ISLATIsland TradingHelen BennettMarketing ManagerGarden House Crowther WayCowesIsle of WightPO31 7PJUKnullKOENEKöniglich EssenPhilip CramerSales AssociateMaubelstr. 90Brandenburgnull14776GermanynullLACORLa corne d’abondanceDaniel ToniniSales Representative67, avenue de l’EuropeVersaillesnull78000France30.59.85.11LAMAILa maison d’AsieAnnette RouletSales Manager1 rue Alsace-LorraineToulousenull31000France61.77.61.11LAUGBLaughing Bacchus Wine CellarsYoshi TannamuriMarketing Assistant1900 Oak St.VancouverBCV3F 2K1Canada(604) 555-7293LAZYKLazy K Kountry StoreJohn SteelMarketing Manager12 Orchestra TerraceWalla WallaWA99362USA(509) 555-6221LEHMSLehmanns MarktstandRenate MessnerSales RepresentativeMagazinweg 7Frankfurt a.M.null60528Germany069-0245874LETSSLet’s Stop N ShopJaime YorresOwner87 Polk St. Suite 5San FranciscoCA94117USAnullLILASLILA-SupermercadoCarlos GonzálezAccounting ManagerCarrera 52 con Ave. Bolívar #65-98 Llano LargoBarquisimetoLara3508Venezuela(9) 331-7256LINODLINO-DelicatesesFelipe IzquierdoOwnerAve. 5 de Mayo PorlamarI. de MargaritaNueva Esparta4980Venezuela(8) 34-93-93LONEPLonesome Pine RestaurantFran WilsonSales Manager89 Chiaroscuro Rd.PortlandOR97219USA(503) 555-9646MAGAAMagazzini Alimentari RiunitiGiovanni RovelliMarketing ManagerVia Ludovico il Moro 22Bergamonull24100Italy035-640231MAISDMaison DeweyCatherine DeweySales AgentRue Joseph-Bens 532BruxellesnullB-1180Belgium(02) 201 24 68MEREPMère PaillardeJean FresnièreMarketing Assistant43 rue St. LaurentMontréalQuébecH1J 1C3Canada(514) 555-8055MORGKMorgenstern GesundkostAlexander FeuerMarketing AssistantHeerstr. 22Leipzignull04179GermanynullNORTSNorth/SouthSimon CrowtherSales AssociateSouth House 300 QueensbridgeLondonnullSW7 1RZUK(171) 555-2530OCEANOcéano Atlántico Ltda.Yvonne MoncadaSales AgentIng. Gustavo Moncada 8585 Piso 20-ABuenos Airesnull1010Argentina(1) 135-5535OLDWOOld World DelicatessenRene PhillipsSales Representative2743 Bering St.AnchorageAK99508USA(907) 555-2880OTTIKOttilies KäseladenHenriette PfalzheimOwnerMehrheimerstr. 369Kölnnull50739Germany0221-0765721PARISParis spécialitésMarie BertrandOwner265, boulevard CharonneParisnull75012France(1) 42.34.22.77PERICPericles Comidas clásicasGuillermo FernándezSales RepresentativeCalle Dr. Jorge Cash 321México D.F.null05033Mexico(5) 545-3745PICCOPiccolo und mehrGeorg PippsSales ManagerGeislweg 14Salzburgnull5020Austria6562-9723PRINIPrincesa Isabel VinhosIsabel de CastroSales RepresentativeEstrada da saúde n. 58Lisboanull1756PortugalnullQUEDEQue DelíciaBernardo BatistaAccounting ManagerRua da Panificadora, 12Rio de JaneiroRJ02389-673Brazil(21) 555-4545QUEENQueen CozinhaLúcia CarvalhoMarketing AssistantAlameda dos Canàrios, 891Sao PauloSP05487-020BrazilnullQUICKQUICK-StopHorst KlossAccounting ManagerTaucherstraße 10Cunewaldenull01307GermanynullRANCHRancho grandeSergio GutiérrezSales RepresentativeAv. del Libertador 900Buenos Airesnull1010Argentina(1) 123-5556RATTCRattlesnake Canyon GroceryPaula WilsonAssistant Sales Representative2817 Milton Dr.AlbuquerqueNM87110USA(505) 555-3620REGGCReggiani CaseificiMaurizio MoroniSales AssociateStrada Provinciale 124Reggio Emilianull42100Italy0522-556722RICARRicardo AdocicadosJanete LimeiraAssistant Sales AgentAv. Copacabana, 267Rio de JaneiroRJ02389-890BrazilnullRICSURichter SupermarktMichael HolzSales ManagerGrenzacherweg 237Genèvenull1203SwitzerlandnullROMEYRomero y tomilloAlejandra CaminoAccounting ManagerGran Vía, 1Madridnull28001Spain(91) 745 6210SANTGSanté GourmetJonas BergulfsenOwnerErling Skakkes gate 78Stavernnull4110Norway07-98 92 47SAVEASave-a-lot MarketsJose PavarottiSales Representative187 Suffolk Ln.BoiseID83720USAnullSEVESSeven Seas ImportsHari KumarSales Manager90 Wadhurst Rd.LondonnullOX15 4NBUK(171) 555-5646SIMOBSimons bistroJytte PetersenOwnerVinbæltet 34Kobenhavnnull1734Denmark31 13 35 57SPECDSpécialités du mondeDominique PerrierMarketing Manager25, rue LauristonParisnull75016France(1) 47.55.60.20SPLIRSplit Rail Beer AleArt BraunschweigerSales ManagerP.O. Box 555LanderWY82520USA(307) 555-6525SUPRDSuprêmes délicesPascale CartrainAccounting ManagerBoulevard Tirou, 255CharleroinullB-6000Belgium(071) 23 67 22 21THEBIThe Big CheeseLiz NixonMarketing Manager89 Jefferson Way Suite 2PortlandOR97201USAnullTHECRThe Cracker BoxLiu WongMarketing Assistant55 Grizzly Peak Rd.ButteMT59801USA(406) 555-8083TOMSPToms SpezialitätenKarin JosephsMarketing ManagerLuisenstr. 48Münsternull44087Germany0251-035695TORTUTortuga RestauranteMiguel Angel PaolinoOwnerAvda. Azteca 123México D.F.null05033MexiconullTRADHTradição HipermercadosAnabela DominguesSales RepresentativeAv. Inês de Castro, 414Sao PauloSP05634-030Brazil(11) 555-2168TRAIHTrail’s Head Gourmet ProvisionersHelvetius NagySales Associate722 DaVinci Blvd.KirklandWA98034USA(206) 555-2174VAFFEVaffeljernetPalle IbsenSales ManagerSmagsloget 45Århusnull8200Denmark86 22 33 44VICTEVictuailles en stockMary SaveleySales Agent2, rue du CommerceLyonnull69004France78.32.54.87VINETVins et alcools ChevalierPaul HenriotAccounting Manager59 rue de l’AbbayeReimsnull51100France26.47.15.11WANDKDie Wandernde KuhRita MüllerSales RepresentativeAdenauerallee 900Stuttgartnull70563Germany0711-035428WARTHWartian HerkkuPirkko KoskitaloAccounting ManagerTorikatu 38Oulunull90110Finland981-443655WELLIWellington ImportadoraPaula ParenteSales ManagerRua do Mercado, 12ResendeSP08737-363BrazilnullWHITCWhite Clover MarketsKarl JablonskiOwner305 - 14th Ave. S. Suite 3BSeattleWA98128USA(206) 555-4115WILMKWilman KalaMatti KarttunenOwner/Marketing AssistantKeskuskatu 45Helsinkinull21240Finland90-224 8858WOLZAWolski ZajazdZbyszek PiestrzeniewiczOwnerul. Filtrowa 68Warszawanull01-012Poland(26) 642-7012
练习2 查询每个客户的 ID, company name, contact name, contact title, city, 和 country.并按照国家名字排序 select customer_id,company_name,contact_name,contact_title,city,country from customersorder by countrycustomer_idcompany_namecontact_namecontact_titlecitycountryOCEANOcéano Atlántico Ltda.Yvonne MoncadaSales AgentBuenos AiresArgentinaCACTUCactus Comidas para llevarPatricio SimpsonSales AgentBuenos AiresArgentinaRANCHRancho grandeSergio GutiérrezSales RepresentativeBuenos AiresArgentinaERNSHErnst HandelRoland MendelSales ManagerGrazAustriaPICCOPiccolo und mehrGeorg PippsSales ManagerSalzburgAustriaMAISDMaison DeweyCatherine DeweySales AgentBruxellesBelgiumSUPRDSuprêmes délicesPascale CartrainAccounting ManagerCharleroiBelgiumWELLIWellington ImportadoraPaula ParenteSales ManagerResendeBrazilHANARHanari CarnesMario PontesAccounting ManagerRio de JaneiroBrazilGOURLGourmet LanchonetesAndré FonsecaSales AssociateCampinasBrazilQUEDEQue DelíciaBernardo BatistaAccounting ManagerRio de JaneiroBrazilQUEENQueen CozinhaLúcia CarvalhoMarketing AssistantSao PauloBrazilCOMMIComércio MineiroPedro AfonsoSales AssociateSao PauloBrazilTRADHTradição HipermercadosAnabela DominguesSales RepresentativeSao PauloBrazilFAMIAFamilia ArquibaldoAria CruzMarketing AssistantSao PauloBrazilRICARRicardo AdocicadosJanete LimeiraAssistant Sales AgentRio de JaneiroBrazilBOTTMBottom-Dollar MarketsElizabeth LincolnAccounting ManagerTsawassenCanadaLAUGBLaughing Bacchus Wine CellarsYoshi TannamuriMarketing AssistantVancouverCanadaMEREPMère PaillardeJean FresnièreMarketing AssistantMontréalCanadaSIMOBSimons bistroJytte PetersenOwnerKobenhavnDenmark
5. 商品(products)和商品类别(categories)表
商品表中保存了在Northwind商店中出售的商品信息 每一种商品都有唯一的 product_id 和商品名字product_name.每一种商品都有一个供应商 (supplier_id)每一种商品都有一个商品类别 (category_id).每一种商品都有确定的单价 unit_price.字段 discontinued 代表商品是否缺货 false (有货) true (缺货) 商品类别表 categories 保存了所有商品的类别 每个类别都有唯一的id每个类别都有自己的名称 category_name字段description 存储了类别的简短描述信息 商品表中包含了 category_id 字段所以可以使用join 将商品表中的信息与商品类别表中的信息进行关联查询
商品类别
category_idcategory_namedescription1BeveragesSoft drinks, coffees, teas, beers, and ales2CondimentsSweet and savory sauces, relishes, spreads, and seasonings3ConfectionsDesserts, candies, and sweet breads4Dairy ProductsCheeses5Grains/CerealsBreads, crackers, pasta, and cereal6Meat/PoultryPrepared meats7ProduceDried fruit and bean curd8SeafoodSeaweed and fish
产品表
product_idproduct_namesupplier_idcategory_idquantity_per_unitunit_priceunits_in_stockunits_on_orderdiscontinued1Chai1110 boxes x 20 bags18.00390f2Chang1124 - 12 oz bottles19.001740f3Aniseed Syrup1212 - 550 ml bottles10.001370f4Chef Anton’s Cajun Seasoning2248 - 6 oz jars22.00530f5Chef Anton’s Gumbo Mix2236 boxes21.3500t6Grandma’s Boysenberry Spread3212 - 8 oz jars25.001200f7Uncle Bob’s Organic Dried Pears3712 - 1 lb pkgs.30.00150f8Northwoods Cranberry Sauce3212 - 12 oz jars40.0060f9Mishi Kobe Niku4618 - 500 g pkgs.97.00290t10Ikura4812 - 200 ml jars31.00310f11Queso Cabrales541 kg pkg.21.002230f12Queso Manchego La Pastora5410 - 500 g pkgs.38.00860f13Konbu682 kg box6.00240f14Tofu6740 - 100 g pkgs.23.25350f15Genen Shouyu6224 - 250 ml bottles15.50390f16Pavlova7332 - 500 g boxes17.45290f17Alice Mutton7620 - 1 kg tins39.0000t18Carnarvon Tigers7816 kg pkg.62.50420f19Teatime Chocolate Biscuits8310 boxes x 12 pieces9.20250f20Sir Rodney’s Marmalade8330 gift boxes81.00400f21Sir Rodney’s Scones8324 pkgs. x 4 pieces10.00340f22Gustaf’s Knäckebröd9524 - 500 g pkgs.21.001040f23Tunnbröd9512 - 250 g pkgs.9.00610f24Guaraná Fantástica10112 - 355 ml cans4.50200t25NuNuCa Nuß-Nougat-Creme11320 - 450 g glasses14.00760f26Gumbär Gummibärchen113100 - 250 g bags31.23150f27Schoggi Schokolade113100 - 100 g pieces43.90490f28Rössle Sauerkraut12725 - 825 g cans45.60260t29Thüringer Rostbratwurst12650 bags x 30 sausgs.123.7900t30Nord-Ost Matjeshering13810 - 200 g glasses25.89100f31Gorgonzola Telino14412 - 100 g pkgs12.50070f32Mascarpone Fabioli14424 - 200 g pkgs.32.00940f33Geitost164500 g2.501120f34Sasquatch Ale16124 - 12 oz bottles14.001110f35Steeleye Stout16124 - 12 oz bottles18.00200f36Inlagd Sill17824 - 250 g jars19.001120f37Gravad lax17812 - 500 g pkgs.26.001150f38Côte de Blaye18112 - 75 cl bottles263.50170f39Chartreuse verte181750 cc per bottle18.00690f40Boston Crab Meat19824 - 4 oz tins18.401230f41Jack’s New England Clam Chowder19812 - 12 oz cans9.65850f42Singaporean Hokkien Fried Mee20532 - 1 kg pkgs.14.00260t43Ipoh Coffee20116 - 500 g tins46.001710f44Gula Malacca20220 - 2 kg bags19.45270f45Rogede sild2181k pkg.9.50570f46Spegesild2184 - 450 g glasses12.00950f47Zaanse koeken22310 - 4 oz boxes9.50360f48Chocolade22310 pkgs.12.751570f49Maxilaku23324 - 50 g pkgs.20.001060f50Valkoinen suklaa23312 - 100 g bars16.25650f51Manjimup Dried Apples24750 - 300 g pkgs.53.00200f52Filo Mix24516 - 2 kg boxes7.00380f53Perth Pasties24648 pieces32.8000t54Tourtière25616 pies7.45210f55Pâté chinois25624 boxes x 2 pies24.001150f56Gnocchi di nonna Alice26524 - 250 g pkgs.38.002110f57Ravioli Angelo26524 - 250 g pkgs.19.50360f58Escargots de Bourgogne27824 pieces13.25620f59Raclette Courdavault2845 kg pkg.55.00790f60Camembert Pierrot28415 - 300 g rounds34.00190f61Sirop d’érable29224 - 500 ml bottles28.501130f62Tarte au sucre29348 pies49.30170f63Vegie-spread7215 - 625 g jars43.90240f64Wimmers gute Semmelknödel12520 bags x 4 pieces33.252280f65Louisiana Fiery Hot Pepper Sauce2232 - 8 oz bottles21.05760f66Louisiana Hot Spiced Okra2224 - 8 oz jars17.004100f67Laughing Lumberjack Lager16124 - 12 oz bottles14.00520f68Scottish Longbreads8310 boxes x 8 pieces12.50610f69Gudbrandsdalsost16410 kg pkg.36.00260f70Outback Lager7124 - 355 ml bottles15.001510f71Flotemysost16410 - 500 g pkgs.21.50260f72Mozzarella di Giovanni14424 - 200 g pkgs.34.80140f73Röd Kaviar17824 - 150 g jars15.001010f74Longlife Tofu475 kg pkg.10.00420f75Rhönbräu Klosterbier12124 - 0.5 l bottles7.751250f76Lakkalikööri231500 ml18.00570f77Original Frankfurter grüne Soße12212 boxes13.00320f
练习3 查询每一个商品的product_namecategory_namequantity_per_unitunit_priceunits_in_stock 并且通过 unit_price 字段排序 SELECTproduct_name,category_name,quantity_per_unit,unit_price,units_in_stock
FROM products
JOIN categoriesON products.category_id categories.category_id
ORDER BY unit_price;product_namecategory_namequantity_per_unitunit_priceunits_in_stockGeitostDairy Products500 g2.50112Guaraná FantásticaBeverages12 - 355 ml cans4.5020KonbuSeafood2 kg box6.0024Filo MixGrains/Cereals16 - 2 kg boxes7.0038TourtièreMeat/Poultry16 pies7.4521Rhönbräu KlosterbierBeverages24 - 0.5 l bottles7.75125TunnbrödGrains/Cereals12 - 250 g pkgs.9.0061Teatime Chocolate BiscuitsConfections10 boxes x 12 pieces9.2025Zaanse koekenConfections10 - 4 oz boxes9.5036Rogede sildSeafood1k pkg.9.505Jack’s New England Clam ChowderSeafood12 - 12 oz cans9.6585Longlife TofuProduce5 kg pkg.10.004Aniseed SyrupCondiments12 - 550 ml bottles10.0013Sir Rodney’s SconesConfections24 pkgs. x 4 pieces10.003SpegesildSeafood4 - 450 g glasses12.0095Gorgonzola TelinoDairy Products12 - 100 g pkgs12.500Scottish LongbreadsConfections10 boxes x 8 pieces12.506ChocoladeConfections10 pkgs.12.7515Original Frankfurter grüne SoßeCondiments12 boxes13.0032Escargots de BourgogneSeafood24 pieces13.2562
6. 供应商表suppliers
供应商表与用户表类似每个供应商都有唯一ID supplier_id每个供应商都有公司名字company_name表中还记录了供应商的地址信息 addresscityregionpostal_codecountry
supplier_idcompany_nameaddresscityregionpostal_codecountry1Exotic Liquids49 Gilbert St.LondonnullEC1 4SDUK2New Orleans Cajun DelightsP.O. Box 78934New OrleansLA70117USA3Grandma Kelly’s Homestead707 Oxford Rd.Ann ArborMI48104USA4Tokyo Traders9-8 Sekimai Musashino-shiTokyonull100Japan5Cooperativa de Quesos ‘Las Cabras’Calle del Rosal 4OviedoAsturias33007Spain6Mayumi’s92 Setsuko Chuo-kuOsakanull545Japan7Pavlova, Ltd.74 Rose St. Moonie PondsMelbourneVictoria3058Australia8Specialty Biscuits, Ltd.29 King’s WayManchesternullM14 GSDUK9PB Knäckebröd ABKaloadagatan 13GöteborgnullS-345 67Sweden10Refrescos Americanas LTDAAv. das Americanas 12.890Sao Paulonull5442Brazil11Heli Süßwaren GmbH Co. KGTiergartenstraße 5Berlinnull10785Germany12Plutzer Lebensmittelgroßmärkte AGBogenallee 51Frankfurtnull60439Germany13Nord-Ost-Fisch Handelsgesellschaft mbHFrahmredder 112aCuxhavennull27478Germany14Formaggi Fortini s.r.l.Viale Dante, 75Ravennanull48100Italy15Norske MeierierHatlevegen 5Sandvikanull1320Norway16Bigfoot Breweries3400 - 8th Avenue Suite 210BendOR97101USA17Svensk Sjöföda ABBrovallavägen 231StockholmnullS-123 45Sweden18Aux joyeux ecclésiastiques203, Rue des Francs-BourgeoisParisnull75004France19New England Seafood CanneryOrder Processing Dept. 2100 Paul Revere Blvd.BostonMA02134USA20Leka Trading471 Serangoon Loop, Suite #402Singaporenull0512Singapore21LyngbysildLyngbysild Fiskebakken 10Lyngbynull2800Denmark22Zaanse SnoepfabriekVerkoop Rijnweg 22Zaandamnull9999 ZZNetherlands23Karkki OyValtakatu 12Lappeenrantanull53120Finland24G’day, Mate170 Prince Edward Parade Hunter’s HillSydneyNSW2042Australia25Ma Maison2960 Rue St. LaurentMontréalQuébecH1J 1C3Canada26Pasta Buttini s.r.l.Via dei Gelsomini, 153Salernonull84100Italy27Escargots Nouveaux22, rue H. VoironMontceaunull71300France28Gai pâturageBat. B 3, rue des AlpesAnnecynull74000France29Forêts d’érables148 rue ChasseurSte-HyacintheQuébecJ2S 7S8
练习4 列出所有提供了4种以上不同商品的供应商列表 所需字段supplier_id, company_name, and products_count (提供的商品种类数量). SELECTs.supplier_id, s.company_name, COUNT(*) AS products_count
FROM products p
JOIN suppliers s ON p.supplier_id s.supplier_id
GROUP BY s.supplier_id,s.company_name
HAVING COUNT(*) 3;supplier_idcompany_nameproducts_count2New Orleans Cajun Delights416Bigfoot Breweries67Pavlova, Ltd.512Plutzer Lebensmittelgroßmärkte AG58Specialty Biscuits, Ltd.4
7. 订单和订单明细表
订单表 orders 中的每一条数据包含了一个订单的基本信息 订单ID order_id顾客ID customer_id, 销售员的员工ID employee_id 订单相关的时间信息 (下单日期order_date 和配送日期 shipped_date) 和其他配送相关信息 ship_via 运输方式freight 运费ship_address 收货地址ship_city 收货城市ship_region 收货地区ship_postal_code 收货地址邮编ship_country 收货国家 订单表
order_idcustomer_idemployee_idorder_dateshipped_dateship_viafreightship_addressship_cityship_regionship_postal_codeship_country10248VINET52016-07-042016-07-16332.3859 rue de l’AbbayeReimsnull51100France10249TOMSP62016-07-052016-07-10111.61Luisenstr. 48Münsternull44087Germany10250HANAR42016-07-082016-07-12265.83Rua do Paço, 67Rio de JaneiroRJ05454-876Brazil10251VICTE32016-07-082016-07-15141.342, rue du CommerceLyonnull69004France10252SUPRD42016-07-092016-07-11251.30Boulevard Tirou, 255CharleroinullB-6000Belgium10253HANAR32016-07-102016-07-16258.17Rua do Paço, 67Rio de JaneiroRJ05454-876Brazil10254CHOPS52016-07-112016-07-23222.98Hauptstr. 31Bernnull3012Switzerland10255RICSU92016-07-122016-07-153148.33Starenweg 5Genèvenull1204Switzerland10256WELLI32016-07-152016-07-17213.97Rua do Mercado, 12ResendeSP08737-363Brazil10257HILAA102016-07-162016-07-22381.91Carrera 22 con Ave. Carlos Soublette #8-35San CristóbalTáchira5022Venezuela10258ERNSH12016-07-172016-07-231140.51Kirchgasse 6Graznull8010Austria10259CENTC42016-07-182016-07-2533.25Sierras de Granada 9993México D.F.null05022Mexico10260OTTIK42016-07-192016-07-29155.09Mehrheimerstr. 369Kölnnull50739Germany10261QUEDE42016-07-192016-07-3023.05Rua da Panificadora, 12Rio de JaneiroRJ02389-673Brazil10262RATTC82016-07-222016-07-25348.292817 Milton Dr.AlbuquerqueNM87110USA10263ERNSH92016-07-232016-07-313146.06Kirchgasse 6Graznull8010Austria10264FOLKO62016-07-242016-08-2333.67Åkergatan 24BräckenullS-844 67Sweden10265BLONP22016-07-252016-08-12155.2824, place KléberStrasbourgnull67000France10266WARTH32016-07-262016-07-31325.73Torikatu 38Oulunull90110Finland10267FRANK42016-07-292016-08-061208.58Berliner Platz 43Münchennull80805Germany10268GROSR82016-07-302016-08-02366.295ª Ave. Los Palos GrandesCaracasDF1081Venezuela10269WHITC52016-07-312016-08-0914.561029 - 12th Ave. S.SeattleWA98124USA10270WARTH12016-08-012016-08-021136.54Torikatu 38Oulunull90110Finland10271SPLIR62016-08-012016-08-3024.54P.O. Box 555LanderWY82520USA10272RATTC62016-08-022016-08-06298.032817 Milton Dr.AlbuquerqueNM87110USA10273QUICK32016-08-052016-08-12376.07Taucherstraße 10Cunewaldenull01307Germany10274VINET62016-08-062016-08-1616.0159 rue de l’AbbayeReimsnull51100France10275MAGAA12016-08-072016-08-09126.93Via Ludovico il Moro 22Bergamonull24100Italy10276TORTU82016-08-082016-08-14313.84Avda. Azteca 123México D.F.null05033Mexico10277MORGK22016-08-092016-08-133125.77Heerstr. 22Leipzignull04179Germany10278BERGS82016-08-122016-08-16292.69Berguvsvägen 8LuleånullS-958 22Sweden10279LEHMS82016-08-132016-08-16225.83Magazinweg 7Frankfurt a.M.null60528Germany10280BERGS22016-08-142016-09-1218.98Berguvsvägen 8LuleånullS-958 22Sweden10281ROMEY42016-08-142016-08-2112.94Gran Vía, 1Madridnull28001Spain10282ROMEY42016-08-152016-08-21112.69Gran Vía, 1Madridnull28001Spain10283LILAS32016-08-162016-08-23384.81Carrera 52 con Ave. Bolívar #65-98 Llano LargoBarquisimetoLara3508Venezuela10284LEHMS42016-08-192016-08-27176.56Magazinweg 7Frankfurt a.M.null60528Germany10285QUICK12016-08-202016-08-26276.83Taucherstraße 10Cunewaldenull01307Germany10286QUICK82016-08-212016-08-303229.24Taucherstraße 10Cunewaldenull01307Germany10287RICAR82016-08-222016-08-28312.76Av. Copacabana, 267Rio de JaneiroRJ02389-890Brazil10288REGGC42016-08-232016-09-0317.45Strada Provinciale 124Reggio Emilianull42100Italy10289BSBEV72016-08-262016-08-28322.77Fauntleroy CircusLondonnullEC2 5NTUK10290COMMI82016-08-272016-09-03179.70Av. dos Lusíadas, 23Sao PauloSP05432-043Brazil10291QUEDE62016-08-272016-09-0426.40Rua da Panificadora, 12Rio de JaneiroRJ02389-673Brazil10292TRADH12016-08-282016-09-0221.35Av. Inês de Castro, 414Sao PauloSP05634-030Brazil10293TORTU12016-08-292016-09-11321.18Avda. Azteca 123México D.F.null05033Mexico10294RATTC42016-08-302016-09-052147.262817 Milton Dr.AlbuquerqueNM87110USA10295VINET22016-09-022016-09-1021.1559 rue de l’AbbayeReimsnull51100France10296LILAS62016-09-032016-09-1110.12Carrera 52 con Ave. Bolívar #65-98 Llano LargoBarquisimetoLara3508Venezuela10297BLONP52016-09-042016-09-1025.7424, place KléberStrasbourgnull67000France10298HUNGO62016-09-052016-09-112168.228 Johnstown RoadCorkCo. CorknullIreland10299RICAR42016-09-062016-09-13229.76Av. Copacabana, 267Rio de JaneiroRJ02389-890Brazil10300MAGAA22016-09-092016-09-18217.68Via Ludovico il Moro 22Bergamonull24100Italy10301WANDK82016-09-092016-09-17245.08Adenauerallee 900Stuttgartnull70563Germany10302SUPRD42016-09-102016-10-0926.27Boulevard Tirou, 255CharleroinullB-6000Belgium10303GODOS72016-09-112016-09-182107.83C/ Romero, 33Sevillanull41101Spain10304TORTU12016-09-122016-09-17263.79Avda. Azteca 123México D.F.null05033Mexico10305OLDWO82016-09-132016-10-093257.622743 Bering St.AnchorageAK99508USA10306ROMEY12016-09-162016-09-2337.56Gran Vía, 1Madridnull28001Spain10307LONEP22016-09-172016-09-2520.5689 Chiaroscuro Rd.PortlandOR97219USA10308ANATR72016-09-182016-09-2431.61Avda. de la Constitución 2222México D.F.null05021Mexico10309HUNGO32016-09-192016-10-23147.308 Johnstown RoadCorkCo. CorknullIreland10310THEBI82016-09-202016-09-27217.5289 Jefferson Way Suite 2PortlandOR97201USA10311DUMON12016-09-202016-09-26324.6967, rue des Cinquante OtagesNantesnull44000France10312WANDK22016-09-232016-10-03240.26Adenauerallee 900Stuttgartnull70563Germany10313QUICK22016-09-242016-10-0421.96Taucherstraße 10Cunewaldenull01307Germany10314RATTC12016-09-252016-10-04274.162817 Milton Dr.AlbuquerqueNM87110USA10315ISLAT42016-09-262016-10-03241.76Garden House Crowther WayCowesIsle of WightPO31 7PJUK10316RATTC12016-09-272016-10-083150.152817 Milton Dr.AlbuquerqueNM87110USA10317LONEP62016-09-302016-10-10112.6989 Chiaroscuro Rd.PortlandOR97219USA10318ISLAT82016-10-012016-10-0424.73Garden House Crowther WayCowesIsle of WightPO31 7PJUK10319TORTU72016-10-022016-10-11364.50Avda. Azteca 123México D.F.null05033Mexico10320WARTH52016-10-032016-10-18334.57Torikatu 38Oulunull90110Finland10321ISLAT32016-10-032016-10-1123.43Garden House Crowther WayCowesIsle of WightPO31 7PJUK10322PERIC72016-10-042016-10-2330.40Calle Dr. Jorge Cash 321México D.F.null05033Mexico10323KOENE42016-10-072016-10-1414.88Maubelstr. 90Brandenburgnull14776Germany10324SAVEA92016-10-082016-10-101214.27187 Suffolk Ln.BoiseID83720USA10325KOENE12016-10-092016-10-14364.86Maubelstr. 90Brandenburgnull14776Germany10326BOLID42016-10-102016-10-14277.92C/ Araquil, 67Madridnull28023Spain10327FOLKO22016-10-112016-10-14163.36Åkergatan 24BräckenullS-844 67Sweden10328FURIB42016-10-142016-10-17387.03Jardim das rosas n. 32Lisboanull1675Portugal10329SPLIR42016-10-152016-10-232191.67P.O. Box 555LanderWY82520USA10330LILAS32016-10-162016-10-28112.75Carrera 52 con Ave. Bolívar #65-98 Llano LargoBarquisimetoLara3508Venezuela10331BONAP92016-10-162016-10-21110.1912, rue des BouchersMarseillenull13008France10332MEREP32016-10-172016-10-21252.8443 rue St. LaurentMontréalQuébecH1J 1C3Canada10333WARTH52016-10-182016-10-2530.59Torikatu 38Oulunull90110Finland10334VICTE82016-10-212016-10-2828.562, rue du CommerceLyonnull69004France10335HUNGO72016-10-222016-10-24242.118 Johnstown RoadCorkCo. CorknullIreland10336PRINI72016-10-232016-10-25215.51Estrada da saúde n. 58Lisboanull1756Portugal10337FRANK42016-10-242016-10-293108.26Berliner Platz 43Münchennull80805Germany10338OLDWO42016-10-252016-10-29384.212743 Bering St.AnchorageAK99508USA10339MEREP22016-10-282016-11-04215.6643 rue St. LaurentMontréalQuébecH1J 1C3Canada10340BONAP12016-10-292016-11-083166.3112, rue des BouchersMarseillenull13008France10341SIMOB72016-10-292016-11-05326.78Vinbæltet 34Kobenhavnnull1734Denmark10342FRANK42016-10-302016-11-04254.83Berliner Platz 43Münchennull80805Germany10343LEHMS42016-10-312016-11-061110.37Magazinweg 7Frankfurt a.M.null60528Germany10344WHITC42016-11-012016-11-05223.291029 - 12th Ave. S.SeattleWA98124USA10345QUICK22016-11-042016-11-112249.06Taucherstraße 10Cunewaldenull01307Germany10346RATTC32016-11-052016-11-083142.082817 Milton Dr.AlbuquerqueNM87110USA10347FAMIA42016-11-062016-11-0833.10Rua Orós, 92Sao PauloSP05442-030Brazil
订单明细表
订单明细表order_items中的每一条记录代表了订单中的一件商品记录了订单中每件商品的 订单ID order_id商品ID product_id商品单价unit_price购买数量 quantity折扣 discount.
order_idproduct_idunit_pricequantitydiscount102481114.00120.0010248429.80100.00102487234.8050.00102491418.6090.00102495142.40400.0010250417.70100.00102505142.40350.15102506516.80150.15102512216.8060.05102515715.60150.05102516516.80200.00102522064.80400.0510252332.00250.05102526027.20400.00102533110.00200.00102533914.40420.00102534916.00400.0010254243.60150.15102545519.20210.1510254748.00210.0010255215.20200.00102551613.90350.00102553615.20250.00102555944.00300.00102565326.20150.00102567710.40120.00102572735.10250.00102573914.4060.00102577710.40150.0010258215.20500.2010258517.00650.20102583225.6060.2010259218.00100.00102593720.8010.0010260417.70160.25102605715.60500.00102606239.40150.25102607012.00210.2510261218.00200.00102613514.40200.0010262517.00120.2010262724.00150.00102625630.4020.00102631613.90600.2510263243.60280.00102633020.70600.2510263748.00360.2510264215.20350.0010264417.70250.15102651731.20300.00102657012.00200.00102661230.40120.05102674014.70500.00102675944.00700.15102677614.40150.15102682999.00100.00102687227.8040.0010269332.00600.05102697227.80200.05102703615.20300.00102704336.80250.0010271332.00240.00102722064.8060.00102723110.00400.00102727227.80240.00102731024.80240.05102733110.00150.0510273332.00200.00102734014.70600.05102737614.40330.05102747117.20200.00102747227.8070.0010275243.60120.05102755944.0060.05102761024.80150.0010276134.80100.00102772836.40200.00102776239.40120.00102784415.50160.00102785944.00150.00102786335.1080.00102787312.00250.00102791731.20150.2510280243.60120.00102805519.20200.0010280756.20300.0010281197.3010.0010281243.6060.00102813514.4040.00102823020.7060.00102825715.6020.00102831512.40200.0010283197.30180.00102836027.20350.00102837227.8030.00102842735.10150.25102844415.50210.00102846027.20200.25102846711.2050.2510285114.40450.20
练习5
提取订单编号为10250的订单详情显示如下信息 product_name, quantity, unit_price order_items 表)discount order_date ,按商品名字排序 SELECTproduct_name,quantity,order_items.unit_price,discount,order_date
FROM order_items
JOIN productsON order_items.product_id products.product_id
JOIN ordersON orders.order_id order_items.order_id
WHERE orders.order_id 10250
ORDER BY product_name;product_namequantityunit_pricediscountorder_dateJack’s New England Clam Chowder107.700.002016-07-08Louisiana Fiery Hot Pepper Sauce1516.800.152016-07-08Manjimup Dried Apples3542.400.152016-07-08
二. 数据汇总
三. 使用CASE WHEN 和 GROUP BY将数据分组
四. 窗口函数
