【网学网提醒】：网学会员为您提供SQLSERVER学习材料题目参考，解决您在SQLSERVER学习材料题目学习中工作中的难题，参考学习。
的数据库设计》《基于SQLServer的数据库设计》教学案例
目
录
1．数据表(项目表items_ordered)······················································································42.顾客表(customer)·············································································································53.作者表(authers)················································································································54.书表(books)······················································································································65.习题··································································································································61)从itemsordered表中查找客户号为10449的客户的购买项目列表。·················62)在itemsordered表中找出曾买过帐篷的所有记录列表。····································63）在itemsordered表中检索任何时间且开始字母为‘S’的项目的所有客户号，订购日期，项目价格的列表.······························································································64)显示itemsordered表的一个指定项目的列表清单。···········································65)在itemsordered表中检索任一项目的最高价格。················································67)itemsordered表的总记录数是多少？·····································································78)在itemsordered表中所有被订购的帐篷中，帐篷的最低价格是多少？·············79)有多少人在customers表中只有一种状态？检索该状态并显示每一种的人数。710)在itemsordered表中，检索每一特定项目的项目名，最高价和最低价。·······711)每个顾客订货多少次？在itemsordered表中检索顾客号，订购次数，订购总额。·······························································
······································································712)在一个州有一个人以上的customers表中，有多少人是处在指定的州？找出这个州如果人数大于一则显示每一个州的人数有多少························································713)在itemsordered表中检索每一指定项目名、项目的最高价和最低价。如项目的最高价格大于190.00则显示此结果··················································································814)每位客户订了多少次货？用itemsordered表，如果订购数大于一项则显示客户号，订购次数和订购总额。·······························································································815)显示customers表中所有客户的lastname,firstname和city记录。显示结果根据lastname升序排列。···································································································816)如上题，显示结果降序排列。············································································817)在itemsordered表中检索价格大于10.00的所有项目的价格列表。并根据价格升序排列·····························································································································818)在itemsordered表中检索所有项目名不是‘snowshoes’和‘earmuffs’的客户ID，订货日期和项目名······································································································819)检索开始字母是，‘S’‘P’或‘F’的所有项目的项目名称和价格。···········8，20)在itemsordered表中检索所有价格值在10.00到80.00间的所有项目的订购日期，项目名和价格列表······································································································9
21)在customers表中检索所有州是Arixona或Washington或Oklahoma或Colorado或Hawaii的姓，城市名，州名的记录列表。·······································
·························922)在itemsordered表中检索项目名和每项目的单位价格。··································923)写一个SQL查询语句，用join决定在customers表中哪个项目是根据每一客户进行排序。查找对应itemsordered表中有订购的每一客户的
firstname,lastname,order_date,item和价格列表。（customer表和items_ordered表关联）·····································································································································924)如上题，根据州降序排列仍然显示以上相同结果··············································925)写一个查询语句，检索所有作者名称和所写书的名称。··································926)显示一作者所写书的列表，以及他的全名且作者ID号等于1························1027)找出Jason写的所有书······················································································10
SQLServer范例
1．数据表(项目表items_ordered)．数据表项目表
customeridorder_date103301010110298101011029910339104491043910101104491043910298104101043810438102981044910449101011033010101102991043810413104101031510449101011029830-Jun-1999Raft01-Jul-1999Skateboard01-Jul-1999LifeVest06-Jul-1999Parachute27-Jul-1999Umbrella13-Aug-1999Unicycle14-Aug-1999SkiPoles18-Aug-1999RainCoat01-Sep-1999SnowShoes18-Sep-1999Tent19-Sep-1999Lantern28-Oct-1999SleepingBag01-Nov-1999Umbrella02-Nov-1999Pillow01-Dec-1999Helmet15-Dec-1999Bicycle22-Dec-1999Canoe30-Dec-1999HoolaHoop01-Jan-2000Flashlight02-Jan-2000Lantern18-Jan-2000Tent19-Jan-2000Lawnchair30-Jan-2000Unicycle2-Feb-2000Compass29-Feb-2000Flashlight08-Mar-2000SleepingBag18-Mar-2000PocketKnifeItemquantityprice111411121112111111341141112128.0058.0033.00125.001250.004.50180.7925.5018.3045.0088.0029.0089.226.758.5022.00380.50280.0014.7528.0016.0038.0079.9932.00192.508.004.5088.7022.38
30-Jun-1999Pogostick
18-Jan-2000InflatableMattress1
104491029810330
19-Mar-2000Canoepaddle01-Apr-2000EarMuffs19-Apr-2000Shovel
211
40.0012.5016.75
2.顾客表顾客表(customer)顾客表
customeridfirstnamelastname1010110298102991031510325103291033010338103391040810410104131041910429104381043910449
s
cityLyndenPinetopSnoqualmieOshkosh
stateWashingtonArizonaWashingtonWisconsinIdahoHawaiiOregonArizonaArizonaSouthCaro
linaArizonaArizonaNorthCarolinaColoradoColoradoArizona
JohnLeroyElroyLisaGingerKellyShawnMichaelAnthonyElroyDonaldLindaSarahKevinConradIsabela
GrayBrownKellerJones
SchultzPocatelloMendozaKailuaDaltonHowellTillamook
CannonBeachOregon
SanchezWinslowCleaverGlobeCharlestonGilaBendGreensboroDurangoTellurideYumaDavidsGrahamSmithGilesMoore
MaryAnnHowell
SakaharaNogales
3.作者表作者表(authers)作者表
author_id123lastnameHunterKanetkarPaifirstnameJansonYashwantKirancompanyO’ReillyBPBPublicationsPaiLtd.
Table:books
4.书表书表(books)书表
book_id1234TitleLetusCLetusC++JavaServletProgrammingCProjectsauthor_id2212pages100200300400
5.习题习题1)从itemsordered表中查找客户号为10449的客户的购买项的客户的购买项目列表。目列表。
Answer
2)在itemsordered表中找出曾买过帐篷的所有记录列表。表中找出曾买过帐篷的所有记录列表。
Answer
3）在itemsordered表中检索任何时间且开始字母为‘S’的项）表中检索任何时间且开始字母为‘’目的所有客户号，订购日期，项目价格的列表目的所有客户号，订购日期，项目价格的列表.
Answer
4)显示itemsordered表的一个指定项目的列表清单。表的一个指定项目的列表清单。
Answer
5)在itemsordered表中检索任一项目的最高价格。表中检索任一项目的最高价格。
Answer
检索所有项目在十二月份销售的平均价格。6)检索所有项目在十二月份销售的平均价格。
Answer
7)itemsordered表的总记录数是多少？表的总记录数是多少？
Answer
8)在itemsordered表中所有被订购的帐篷中，帐篷的最低价表中所有被订购的帐篷中，格是多少？格是多少？
Answer
9)有多少人在customers表中只有一种状态？检索该状态并显有多少人在表中只有一种状态？示每一种的人数。示每一种的人数。
Answer
10)在itemsordered表中，检索每一特定项目的项目名，最高表中，检索每一特定项目的项目名，价和最低价。价和最低价。
Answer
11)每个顾客订货多少次？在itemsordered表中检索顾客号，每个顾客订货多少次？表中检索顾客号，订购次数，订购总额。订购次数，订购总额。
Answer
12)在一个州有一个人以上的customers表中，有多少人是处表中，在指定的州？在指定的州？找出这个州如果人数大于一则显示每一个州的人数有多少
Answer
13)在itemsordered表中检索每一指定项目名、项目的最高价表中检索每一指定项目名、和最低价。和最低价。如项目的最高价格大于190.00则显示此结果
Answer
14)每位客户订了多少次货？用itemsordered表，如果订购数每位客户订了多少次货？大于一项则显
示客户号，订购次数和订购总额。大于一项则显示客户号，订购次数和订购总额。
Answer.
15)显示customers表中所有客户的lastname,firstname和city记录。升序排列。记录。显示结果根据lastname升序排列。
SAnswer
16)如上题，显示结果降序排列。如上题，显示结果降序排列。
Answer
17)在itemsordered表中检索价格大于10.00的所有项目的价格列表。格列表。并根据价格升序排列
Answer.
18)在itemsordered表中检索所有项目名不是‘snowshoes’表中检索所有项目名不是‘和‘earmuffs’的客户ID，订货日期和项目名’，
Answer.
19)检索开始字母是，S’‘P’或‘F’的所有项目的项目名检索开始字母是，’‘，’’称和价格。称和价格。
Answer
20)在itemsordered表中检索所有价格值在10.00到80.00间的所有项目的订购日期，的所有项目的订购日期，项目名和价格列表
Answer
21)在customers表中检索所有州是Arixona或Washington或Oklahoma或Colorado或Hawaii的姓，城市名，州名的记录的姓，城市名，列表。列表。
Answer
22)在itemsordered表中检索项目名和每项目的单位价格。表中检索项目名和每项目的单位价格。
Answer.
23)写一个SQL查询语句，join决定在customers表中哪个查询语句，用项目是根据每一客户进行排序。项目是根据每一客户进行排序。查找对应itemsordered表中有订购的每一客户的firstname,lastname,order_date,item和价格列表。格列表。（customer表和items_ordered表关联）（表关联）
Answer
24)如上题，根据州降序排列仍然显示以上相同结果如上题，
Answer
25)写一个查询语句，检索所有作者名称和所写书的名称。写一个查询语句，检索所有作者名称和所写书的名称。
Answer
26)显示一作者所写书的列表，显示一作者所写书的列表，以及他的全名且作者ID号等于1.
Answer
27)
找出Jason写的所有书
Answer
Answers
1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.
24.25.26.27.
