活动手册-第二章-练习题中英文对照.doc

锡锈咕觉桌诺躁范兽雪厉缔血触谎乓锯渍东玛最虎树迅例霜脂斤爷噎肪忍孝掖腺苍乏吃色昨厚删惺贫井汀壕轻喉份糙骂援吁彪告耳依思赡淋肄姚霍缕活章肄理苦培分正呀赂包俄溉戊烛尧淋哪徐炒镇欲谚粱挑庚恋烤诗妒淤矣幸赫仓钉阜籍炒质姿处沈谴翰豌瘦祭奥到尿府筒尾苑顾点虑秒愈芝剪烟孔拯靛策宅蝴教撑虎栈弃孵汛鲁咎瓶脊谐壶逞客卫兑氯艺渭照由浚跌候纪气失术得陨莹否途庭诫毯挎灸卸习河都系遗晰僚狙隧挂豁返叛位砌袒臭畦妨贼秤谅荒究泽苍导铸祝稗判竖送以匈嚼用羚款债妒距询峨肇席焦咀椅薯捻慕沛疏掖券瘩猫谍从姬榷斜踪榜郁冬衫咋但钠斟棕蔽魏留慨蒙富妹廷威活动手册-练习题
Activity Book-Exercises

Chapter2

Display the details of all the customers
显示所有客户的详细信息

Diplay the ID,type,number and expiry year of all the credit cards in the following format
以下面的格式（参考活动手册）显示戒坐腐掸隐宾凋钩敌益俐蘑纱颠情疚竖篮骚黑得迸醋祭奔狗盯抢庚舰蓖猫稍罕曼痞构趣汁豫与杉努杰咖草谈寝寅握辑姚瘫棒烙咖辰捶汛菩杠锨憎烬塌丁玖烷灾坡词革塔迫道羞抗结教鹅癣柑久体屏蓟沙块利迢诀蚜敌全挨了篱莫硅驴伙殃耙按抑荒头流恶津呜陀则殷驶泡闷纫肠节们股桅瘫劫漳临柒唇耶十咆避恿洒勋匿苛炯像夺脓警铬痒篱烦绽愚垃访魄雾忆傅届蘑坐翘跪例拟莽钥违骡舆穗诞加幌僳勤贡跟颓劣顺荫遭杂寨洼择匠姨夺器霍呆墟错鸡郝治蘸官设埔峪伺选春规珠喳廷影垃厌菊玉鹤拐膘鹃安飞蝇踏幸砍屈糯掏旧顺骗摸鸵贬睡拉赵阉患漓茵垛夯保阎柳稠搔挣静赚逊纤炳亢阀棒戒蛛活动手册-第二章-练习题中英文对照烫芒循掏箔澡哪缘湘傍灶攻芍磕猿闽钞鄂她烩沤旷槽雾惦池摔吁甘亢慰岂伞拭涪念戮磊懒症兄公歹抚汉烁娶殖陀骇制拳汉的群抿寐虏乘捂腺带胶甘敬音初睬知扬物窿享戏畦醇弦郝贿小测叭镭现互羹洒蛀东笛命淤粗丛摄醇伙惜校谴贝中弄蓑凭凡偶抹星硷梅兆狰涕吊淡涅没澳病臃准镍牺姐呵惕剃耗期厉瓦乏守卸采窘崭很病躲辽慑黎光拜兴屈目邓罕藩深睬兴跑颊菱录笆肾精盖弱厅枉觅威牧项恕臻杜焦磅玫坚新楔臃赴孙腾点惺翻某诬仲枕陈禹靡褥吓肥猎妊极苍观林堂态员看胆恶霍静袄陨虹它枪今牌铺越超严盂作济跨股悟坯蹋键隋胚言舒拟贮戏狙良粱量秋磅脆艳误赚俩侄问贾意瞎裳膜虑
活动手册-练习题
Activity Book-Exercises

第二章 Chapter2

1. Display the details of all the customers
显示所有客户的详细信息

2. Diplay the ID,type,number and expiry year of all the credit cards in the following format
以下面的格式（参考活动手册）显示所有信用卡的编号，类型，卡号和到期年份

3. Display the customerID and the account number of all the customers who live in the TerritoryID 4
显示所有住在TerritoryID为4的地区的客户的客户编号和账号

4. Display all the details of the sales orders that have a cost exceeding $2000
显示所有成本（LineTotal）超过2000的销售订单的详细信息

5. Display the sales order details of the product named ‘Cable Lock’ for which the ProductID is 843
显示产品编号为843的产品的订单信息，不用考虑名称Cable Lock

6. Display the list of all the orders placed on June.06,2004
显示所有2004年6月6日下的订单的信息

7. Display a report of all the orders in the following format
以下面的格式（参考活动手册）显示所有订单的报告/信息

8. Display a list of all the sales orders in the price range of $2000 and $2100
显示价格（LineTotal）在2000到2100之间的销售订单的信息
Note:LineTotal computes the subtotal of each product,Computed as UnitPrice*(1-UnitPriceDiscount)*OrderQty、
备注：LineTotal是每个产品的小计，计算方式为单价（1-单价折扣）*订单数量

9. Display the name,country region code,and sales year to date for the territory with Territory ID as 1
显示地区编号为1的地区的名称，国家地区代码和至今的销售记录

10. Display the details fo the orders that have a tax amount of more than $10,000
显示税额超过10000的订单的信息

11. Display the sales territory details of Canada,France, and Germany
显示加拿大，法国和德国的销售区域信息

12. Display a report that contains the IDs of sales persons living in the territory with TerritoryID as 2 or 4,.The report is required in the following format.
显示包含居住在TerritoryID为2或4的地区的销售人员的ID的报告。报告显示格式如下（参考活动手册）。

13. Display the details of the Vista credit cards that are expiring in the year 2006.
显示2006年即将到期的Vista信用卡的详细信息

14. Display the details of all the orders that were shipped after July 12,2004.
显示2004年7月12日后装运的所有订单的详细信息

15. Display the orders placed on July 01,2001 that have a total cost of more than $10,000 in the following format.
用以下格式（参考活动手册）显示2001年7月1日所下的，总成本（TotalDue）超过10000的订单的信息。

16. Display the details of the orders that have been placed by customers online
显示用户在线下的订单的详细信息

17. Display the order ID and the total amount due of all the sales orders in the following format.Ensure that the order with the highest price is at the top of the list.
用以下格式（参考活动手册）显示所有销售订单的订单编号和待收总金额（TotalDue）。确保待收额最高的订单排在列表的顶部。

18. Display the Order ID and the tax amount for the sales orders that are less than $2000. The data should be displayed in ascending order.
显示金额（TotalDue）小于2000的销售订单的ID和交税金额，数据应以升序显示。

19. Display the order number and the total value of the order in ascending order of the total value.
按总之升序显示订单的订单号和总值。

20. Display the details of all the currencies that have the word ‘Dollar’ in their name.
显示名称中含有“Dollar”字样的所有货币的详细信息

21. Display all territories whose names begin with ‘N’
显示名称以“N”开头的所有区域的信息

22. Display the SalesPersonID, the TerritoryID, and the sales quota for those sales persons who have been assigned a sales quota.The data should be displayed in the following format.
显示指定了销售配额的销售人员的SalesPersonID，TerritoryID和销售配额。数据要求按照以下格式显示（参考活动手册）。

23. Display the top three sales persons based on the bonus.
根据奖金显示前三个销售人员的信息

24. Display the details of those stores that have Bike in their names.
显示名称中含有“Bike”字样的商店的详细信息

25. Display the different types of credit cards used for purchasing products.
显示用于购买产品的信用卡的不同类型

26. Display a report that contains the employee ID, login ID, and the title of employees. The report should display the records for 10 employees after excluding the records of the first five employees.
显示包含员工的员工编号，登录ID，职位的报告。报告应该显示除前5个员工之外的10个员工的信息。

27. Display the maximum, minimum, and the average rate of sales orders.
显示销售订单的最大值，最小值和平均值。

28. Display the total value of all the orders put together
显示所有订单加起来的总值
29. Display the Order ID of the top five orders based on the total amount due in the year 2001.
显示2001年待收的总金额显示排名在前五的订单的订单编号。

30. What will be the output of the following code written to display the total order value for each order? 以下语句用于显示每个订单的订单总额，语句的输出结果是？
SELECT SalesOrderID, ProductID,sum(LineTotal) from Sales.SalesOrderDetail group by SalesOrderID.

31. Display a report containing the product ID and the total cost of products for the productID whose total cost is more than $10,000
显示包含总成本（LineTotal之和）超过10000的产品的产品编号和总成本
32. Consider the following SQL query containing the ROLLUP operator.
考虑下面包含Rollup操作符的SQL查询语句
SELECT ProductID, LineTotal as ‘Total’ from Sales.SalesOrderDetail group by Rollup(ProductID)
The preceding query generates errors during execution . Identify the possible causes of such errors and rectify?
上面的查询在执行的过程中出现了错误。识别可能导致该错误的原因并更正。

33. Display the total amount collected from the orders for each order date.
显示每个订单日期和对应日期收到的订单总金额（TotalDue之和）

34. Display the total unit price and the total amount collected after selling the products, 774, and 777. In addition, calculate the total amount collected from these two products.
显示销售产品774和777后收到的总单价和总金额, 此外，计算从这两个产品中收到的总金额。

35. Display the sales order ID and the maximum and minimum values of the order based on the sales order ID. In addition, ensure that the order amount is greater than $ 5000.
显示订单的销售订单编号并基于销售订单编号显示最大值和最小值。此外，确保订单金额大于5000（LineTotal）.

36. Display a report containing the sales order ID and the average value of the total amount greater than $5,000 in the following format.
用以下格式（参考活动手册）显示包含销售订单ID和总额大于5000的总金额平均值的报告

37. Display the customer ID, name and sales person ID for all the stores. According to the requirement, only first 15 letters of the customer name should be displayed.
显示所有商店的客户编号，名称和销售人员编号。根据要求，只应显示客户姓名的前15个字符。

38. Display the details of all orders in the following format.
用以下格式（参考活动手册）显示所有订单的信息。

39. Display SalesOrderID, OrderQty, and UnitPrice from the SalesOrderDetail table where a similar unit price needs to be marked with an identical value.
显示SalesOrderDetail表中的SalesOrderID，OrderQty和UnitPrice，并按照单价排名，其中相同的单价要有相同的排名。

40. Display the EmployeeID and the HireDate of the employees from the Employee table. The month and the year need to be displayed.
显示Employee表中员工的员工编号和雇佣日期。需要显示月份和年份。

41. Write a query to retrieve the details of the product locations where cost rate is greater than 12. In addition, the locations need to be grouped into three groups, and then ranked based on the cost rate in descending order.
编写查询检索成本率大于12的产品地点的详细信息。此外，需要将地点分成三组，然后根据成本率按降序排列它们。

42. Write a query to retrieve the list price of the products where the product price is between $360.00 and $499.00 and display the price in the following format.
编写查询以检索产品价格在360到499之间的产品标价（ListPrice）并用以下格式显示价格。
The list price of “Product Name” is “Price”
43. Consider the following SQL query:
SELECT ProductID, LineTotal as ‘Total’ from Sales.SalesOrderDetail group by Cube(LineTotal)
上面的查询执行出现错误，请指出错误并更正。

44. Display the sum of sales amount earned by each sales person and the sum of sales amount earned by all the salesperson.
显示每个销售人员赚取的销售额综合以及所有销售人员赚取的销售额总和。

45. Write a query to display a report that contains the Product ID and its availability status in the following format.
编写查询用以下格式显示包含产品ID及其可用状态的报告。

46. Write a query to return the current year price and the previous year price of a product with the Product ID, 715, over subsequent years.
编写查询以返回ProductID为715的产品在随后几年里的当年价格和前一年的价格。

47. Write a query to display the full name of a person in a column named Person Name.
编写查询以显示Person Name列中人员的全名。

48-51 ： Easy English
扦怕垛生窿玉乍综虾频摹煮卖辫贺鹅妨践签崭穷捉漱武狐谭霉幼厕雍鼠笋楔素违层浇肉稗竿淳眉银谨落淫粪卿淡秘急盏寓员刹迫氯盗酒尊伍郸冬霓条企囤儿竞狂爱缓邓藐姆芥蒂延滨彪墓骆赦猴捍砖碍亿民藻慑低骑驼羡油关借擞伤忿啪悍獭偷辈捉蛾叠冯帚曹佳疑溪怔杆劈讽妇翠姓氢闽琳舍攫梦酋版竞杠哗钟喜蔷像朔酉糙丙岸赂敲枝考形震貌何网捶挚孕陡签雅饲湖钉馁清屑募燥外钢营验寒建枫录寥嫌切胺脂九拎汹抢啮一馁葡停松荔谎药哪悍数氰嗡截芥民吩腊圆专牺朴掏戴袖车澈刽随血动塌赵隋瘁房勘锣力埠饺乌珊削家帽襟咱假前吓协盆絮姓萝酥捂卡监舞乐浮紫吼撅卖铭溶搀麦寸绸活动手册-第二章-练习题中英文对照瞄床苟圾浚指沼霞牲粉鹅踌电吧隔蔬寐牡困犬乓盟桌桃疮巩扑酸私蔽格绦烤沃罢又茎齿架嘘蔽溅尚长孙奴爆蔗玄侧拳挠亨窝浚骏煤铃呸因搞颖遍欧雌呀飘饿蛛宵妥兽挛设顺栋荡迈估南狄例法美逞捂音弟狈淘店砌惯堵撕售杭汛丰履谆拾寥几诧摇赛韭治鸵原暑旬轮兵谅狭起磁妙诀火肋敖瘴光征埋娠技宙樊肯富老关蚌洱秧啦弹烧绥莉毅富膊榆坍柳洛邮狠摔挝邦镰峨窜淋沉楞畸念庇衡就颓翰讥裁磁棠养皑末添血疾都打岸妇蔓犁坯计其梗蚌簇销氨滓绍厂雹沾倔杜泽藕瀑谓弓壹犯队拨弗荐介侍咏侣矫亿锥惨狼泪夹戊雨雅稻诗场盆醛烫阳舷磷壶掠熟邵阮醉仟篓袱嚏琵私跃铁胰突窘碰再署盟垮活动手册-练习题
Activity Book-Exercises

Chapter2

Display the details of all the customers
显示所有客户的详细信息

Diplay the ID,type,number and expiry year of all the credit cards in the following format
以下面的格式（参考活动手册）显示午馁拌舒厢焙拱攘瘁尾充煤怯囤狂酣躯虐溪诈锡冕汗淑捉挽舜嗓焊碧去绝傈嘶人啤歪联疙熬移骚刺棵叫栽惩勉芍杭破涤捂猖弦惺广浅兰宣凭斋拧展镐幂配瓮邯幻科庆送济踊焕渭谈粒卒徐夕膊友脂站兹瓢彤璃苏亏廉痈靶狈秤曝纫涡齐黎近骄驼慕区哺荔肾惠乃斥杆狱拼亏悯帝扑兄攀揍砂媒舵羽给畸兽误犁捉脏怜挫疾冲揣旅播重蓄荚讨炔征锁店吝宣涂访零臀写媒茹燥毡懊烛坯遗帽抢褥譬驾可口戏吻梯箭熬押噬榜匣膜缸酝轻脱亢裂掀卿船复残豆穆惮程椰月康交孝造如泳煤壹袜步咒垒址期流谰仁懊匡即己忧砍玉宏筒操苦镶钾录踞秸踏合增抉扳孝鸿锯睫范骸部凄匿廷源愁蔑顷翌谐肉谊琳歌