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

活动手册-练习题Activity Book-Exercises第2章 Chapter21. 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 Lock6. 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 quer