笔试题 【SQL and Excel题目】.docx

Name:Test for Solution PositionsThe test should take 45 minutes.SQL SkillWith SQL, how do you select all the records from a table named Persons where the value of the column FirstName starts with an a?SELECT * FROM Persons WHERE FirstName LIKE a%SELECT * FROM Persons WHERE FirstName LIKE %aSELECT all FROM Persons WHERE FirstName=aSELECT all FROM Persons WHERE FirstName=%a%With the table listed below, finish the tasks.OrdersCustomerOrder QuantityDateCityTom2001/03/2011New YorkBob1501/07/2011ChicagoTom1001/09/2011DetroitJohn1201/09/2011New YorkWith SQL,what query can find the total order quantityof each customer for the table “Orders” listed above?Write down your answer:With SQL,add 1 unit to order quantity of every order issued by TomWrite down your answer:You have 2 sheets “Orders” and “Cities”. You have to use a query to generate a table of “CityCust”.OrdersCustomerOrder QuantityDateCityTom2001/03/2011New YorkBob1501/07/2011ChicagoTom1001/09/2011DetroitJohn1201/09/2011New York CitiesCityStateNew YorkNew YorkChicagoMichiganCityCustCustomerCityStateTomNew YorkNew YorkJohnNew YorkNew YorkBobChicagoMichiganWrite down your query:Excel SkillWrite the first formula to populate the “新名称” E columnby replacing text “办事处” with “分公司” in the B column.Write the first formula:增加一列辅助列，假设“翻译”在B列，则在B列后插入一列C列在C1列输入公式＝IF(B1=翻译,indirect(Brow()-3),b1)然后，下拉复制此公式到C列最后一行。选中C列，复制，就地选择性粘贴——数值，然后就可以删除B列了。=INDEX(A:A,CEILING((ROW())/3,1))Write the first formula to populate the “所属DC” C column, you may need information from column G and H.Write the first formula:c单元格输入=IF(AND($A2=$K$2,B$1=$K$1),$K$3, )，How to summarize the total sales volume of every branch in the first season?LP/MIP UnderstandingLlama Window Ltd. produces 2 types of windows– wood and aluminum. The company will receive $60 profitby selling a piece of wood window and $30 profit by a piece ofaluminum window. There are 3 workers: Paul can make at most 6 wood frames each day. Mike can make at most