活動(dòng)手冊練習(xí)題p

1、Cable Lock for which活動(dòng)手冊-練習(xí)題Activity Book -Exercises第二章 Chapter21. Display the details of all the customers顯示所有客戶的詳細(xì)信息2. Diplay the ID,type,numberand expiry year of all the credit cards in thefollowi ng format以下面的格式（參考活動(dòng)手冊）顯示所有信用卡的編號，類型，卡號和到期年份3. Display the customerID and the acco unt nu mber of al

2、l the customers who live in the TerritoryID 4顯示所有住在TerritoryID為4的地區(qū)的客戶的客戶編號和賬號4. Display all the details of the sales orders that have a cost exceed ing $2000顯示所有成本(Lin eTotal )超過2000的銷售訂單的詳細(xì)信息5. Display the sales order details of the product n amed the ProductID is 843顯示產(chǎn)品編號為843的產(chǎn)品的訂單信息，不用考慮名稱Cable

3、 Lock6. Display the list of all the orders placed on Jun e.06,2004顯示所有2004年6月6日下的訂單的信息7. Display a report of all the orders in the following format以下面的格式(參考活動(dòng)手冊)顯示所有訂單的報(bào)告/信息8. Display a list of all the sales orders in the price range of $2000 and $2100顯示價(jià)格(Lin eTotal )在2000到2100之間的銷售訂單的信息Note:L in e

4、Total computes the subtotal of each product,Computed asUn itPrice*(1 -Uni tPriceDiscou nt)*OrderQty 、備注：Lin eTotal是每個(gè)產(chǎn)品的小計(jì)，計(jì)算方式為單價(jià)(1-單價(jià)折扣)*訂單數(shù)量9. Display the name,countryregion code,and sales year to date for theterritory with Territory ID as 1顯示地區(qū)編號為1的地區(qū)的名稱，國家地區(qū)代碼和至今的銷售記錄10. Display the details fo

5、the orders that have a tax amount of more than $10,000顯示稅額超過10000的訂單的信息11. Display the sales territory details of Canada,Franee, and Germany顯示加拿大，法國和德國的銷售區(qū)域信息12. Display a report that contains the IDs of sales persons living in the territory with TerritorylD as 2 or 4,.The report is required in the

6、following format.顯示包含居住在TerritorylD為2或4的地區(qū)的銷售人員的ID的報(bào)告。報(bào)告顯示 格式如下(參考活動(dòng)手冊)。13. Display the details of the Vista credit cards thatare expiring in the year2006.顯示2006年即將到期的Vista信用卡的詳細(xì)信息14. Display the details of all the orders that were shipped after July 12,2004.in the顯示2004年7月12日后裝運(yùn)的所有訂單的詳細(xì)信息15. Displa

7、y the orders placed on July 01,2001 that have a total cost of more than $10,000 in the following format.用以下格式（參考活動(dòng)手冊）顯示2001年7月1日所下的，總成本（TotalDue ）超過10000的訂單的信息。16. Display the details of the orders that have been placed by customers on li ne顯示用戶在線下的訂單的詳細(xì)信息17. Display the order ID and the total amoun

8、t 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.用以下格式（參考活動(dòng)手冊）顯示所有銷售訂單的訂單編號和待收總金額（TotalDue）。確保待收額最高的訂單排在列表的頂部。18. Display the Order ID and the tax amount for the sales orders that are less than $2000. The data should be disp

9、layed in asce nding order.顯示金額（TotalDue ）小于2000的銷售訂單的ID和交稅金額，數(shù)據(jù)應(yīng)以升序顯示。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 Dollarn ame.顯示名稱中含有“ Dollar ”字樣的所有貨幣的詳細(xì)信息21. Dis

10、play all territories whose n ames beg in with N顯示名稱以“ N”幵頭的所有區(qū)域的信息22. Display the SalesPers on ID, the TerritorylD, and the sales quota for thosesales persons who have been assigned a sales quota.The data should be displayed in the following format.顯示指定了銷售配額的銷售人員的SalesPersonID , TerritoryID和銷售配額。數(shù)據(jù)要

11、求按照以下格式顯示（參考活動(dòng)手冊）。23. Display the top three sales pers ons based on the bonus.根據(jù)獎(jiǎng)金顯示前三個(gè)銷售人員的信息24. Display the details of those stores that have Bike in their n ames.顯示名稱中含有“ Bike ”字樣的商店的詳細(xì)信息25. Display the differe nt types of credit cards used for purchas ing products.顯示用于購買產(chǎn)品的信用卡的不同類型26. Display a

12、report that contains the employee ID, log in 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，職位的報(bào)告。報(bào)告應(yīng)該顯示除前5個(gè)員工之外的10個(gè)員工的信息。27. Display the maximum, minimum, and the average rate of sales o

13、rders.顯示銷售訂單的最大值，最小值和平均值。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 totalord

14、er value for each order以下語句用于顯示每個(gè)訂單的訂單總額，語句的輸出結(jié)果是？SELECTSalesOrderlD,ProductlD,sum(L in eTotal)fromSales.SalesOrderDetail group by SalesOrderID.31.Display a report containing the product ID and the total cost of productsfor the productID whose total cost is more tha n $10,000顯示包含總成本(Lin eTotal之和)超過1

15、0000的產(chǎn)品的產(chǎn)品編號和總成本32. Con sider the follow ing SQL query containing the ROLLUP operator.考慮下面包含Rollup操作符的SQL查詢語句SELECT ProductID,Lin eTotal as Total frm Sales.SalesOrderDetailgroup by Rollup(ProductID)The precedi ng query gen erates errors duri ng executio n . Ide ntify the possible causes of such erro

16、rs and rectify上面的查詢在執(zhí)行的過程中出現(xiàn)了錯(cuò)誤。識(shí)別可能導(dǎo)致該錯(cuò)誤的原因并更正。33. Display the total amount collected from the orders for each order date.顯示每個(gè)訂單日期和對應(yīng)日期收到的訂單總金額( TotalDue之和)34. Display the total unit price and the total amount collected after selling theproducts, 774, and 777. In additi on, calculate the total amou

17、nt collected from these two products.顯示銷售產(chǎn)品774和777后收到的總單價(jià)和總金額，此外，計(jì)算從這兩個(gè)產(chǎn)品 中收到的總金額。35. Display the sales order ID and the maximum and minimum values of the order based on the sales order ID. In additi on, en sure that the order amount is greater than $ 5000.顯示訂單的銷售訂單編號并基于銷售訂單編號顯示最大值和最小值。此外，確保訂單金額大于50

18、00 （ Lin eTotal）.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.用以下格式（參考活動(dòng)手冊）顯示包含銷售訂單ID和總額大于5000的總金額平均值的報(bào)告37. Display the customer ID, name and sales person ID for all the stores. According to the requirement,

19、only first 15 letters of the customer name shouldbe displayed.顯示所有商店的客戶編號，名稱和銷售人員編號。根據(jù)要求，只應(yīng)顯示客戶姓名的前15個(gè)字符。38. Display the details of all orders in the following format.用以下格式（參考活動(dòng)手冊）顯示所有訂單的信息。39. Display SalesOrderID, OrderQty, and UnitPrice from the SalesOrderDetail table where a similar unit price n

20、 eeds to be marked with an ide ntical value.顯示 SalesOrderDetail 表中的 SalesOrderID ，OrderQty 和 UnitPrice，并按照單價(jià)排名，其中相同的單價(jià)要有相同的排名。40. Display the EmployeeID and the HireDate of the employees from the Employee table. The month and the year n eed to be displayed.顯示Employee表中員工的員工編號和雇傭日期。需要顯示月份和年份。41. Writ

21、e 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 the n ran ked based on the cost rate in desce nding order.編寫查詢檢索成本率大于12的產(chǎn)品地點(diǎn)的詳細(xì)信息。此外，需要將地點(diǎn)分成三組，然后根據(jù)成本率按降序排列它們。n amed42. 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.編寫查詢以檢索產(chǎn)品價(jià)格在360到499之間的產(chǎn)品標(biāo)價(jià)(ListPrice )并用以下格式顯示價(jià)格。The list price of Product Name ” isPrice ”43. Co nsider the followi ng SQL query:SELECT ProductID, L