db2 V9 730 认证练习四SQL.docx
- 文档编号:779388
- 上传时间:2023-04-30
- 格式:DOCX
- 页数:49
- 大小:27.17KB
db2 V9 730 认证练习四SQL.docx
《db2 V9 730 认证练习四SQL.docx》由会员分享,可在线阅读,更多相关《db2 V9 730 认证练习四SQL.docx(49页珍藏版)》请在冰点文库上搜索。
db2V9730认证练习四SQL
PracticeQuestions
1.
Giventhefollowingtwotables:
NAMES
------------------------
NAMENUMBER
-----------------
WayneGretzky99
JaromirJagr68
BobbyOrr4
BobbyHull23
MarioLemieux66
POINTS
------------------------
NAMEPOINTS
-----------------
WayneGretzky244
BobbyOrr129
BrettHull121
MarioLemieux189
JoeSakic94
Howmanyrowswouldbereturnedusingthefollowingstatement?
SELECTnameFROMnames,points
∙A.0
∙B.5
∙C.10
∙D.25
2.
Giventhefollowingtwotables:
TAB1
-------------------
COL_1COL_2
----------
A10
B12
C14
TAB2
-------------------
COL_ACOL_B
----------
A21
C23
D25
Assumingthefollowingresultsaredesired:
COL_1COL_2COL_ACOL_B
A10A21
B12--
C14C23
--D25
Whichofthefollowingjoinswillproducethedesiredresults?
∙A.SELECT*FROMtab1INNERJOINtab2ONcol_1=col_a
∙B.SELECT*FROMtab1LEFTOUTERJOINtab2ONcol_1=col_a
∙C.SELECT*FROMtab1RIGHTOUTERJOINtab2ONcol_1=col_a
∙D.SELECT*FROMtab1FULLOUTERJOINtab2ONcol_1=col_a
3.
Giventhefollowingtable:
TAB1
-------------------
COL1COL2
----------
A10
B20
C30
A10
D40
C30
Assumingthefollowingresultsaredesired:
TAB1
-------------------
COL1COL2
----------
A10
B20
C30
D40
Whichofthefollowingstatementswillproducethedesiredresults?
∙A.SELECTUNIQUE*FROMtab1
∙B.SELECTDISTINCT*FROMtab1
∙C.SELECTUNIQUE(*)FROMtab1
∙D.SELECTDISTINCT(*)FROMtab1
4.
Giventhefollowingtwotables:
EMPLOYEE
IDNAMEDEPTID
-----------------------
01MickJagger10
02KeithRichards20
03RonnieWood20
04CharlieWatts20
05BillWyman30
06BrianJones-
DEPARTMENT
IDDEPTNAME
-----------------
10ExecutiveStaff
20Sales
30Marketing
40Engineering
50HumanResources
WhichtwoofthefollowingquerieswilldisplaytheemployeenameanddepartmentnameforallemployeesthatareinSales?
∙A.SELECTe.name,d.deptname
FROMemployeee,departmentd
WHEREe.deptid=d.idANDd.id='20'
∙B.SELECTe.name,d.deptname
FROMemployeeeFULLOUTERJOINdepartmentd
ONe.deptid=d.id
WHEREd.id='20'
∙C.SELECTe.name,d.deptname
FROMemployeeeRIGHTOUTERJOINdepartmentd
ONe.deptid=d.id
WHEREd.id='20'
∙D.SELECTe.name,d.deptname
FROMemployeeeLEFTOUTERJOINdepartmentd
ONe.deptid=d.id
WHEREd.id='20'
∙E.SELECTe.name,d.deptname
FROMemployeeeINNERJOINdepartmentd
ONe.deptid=d.id
WHEREd.id='20'
5.
Giventhefollowingqueries:
SELECTc1FROMtab1;
SELECTc1FROMtab2;
Whichofthefollowingsetoperatorscanbeusedtoproducearesultdatasetthatcontainsonlyrecordsthatarenotfoundintheresultdatasetproducedbyeachqueryafterduplicaterowshavebeeneliminated?
∙A.UNION
∙B.INTERSECT
∙C.EXCEPT
∙D.MERGE
6.
Giventhefollowingtwotables:
NAMES
------------------------
NAMENUMBER
-----------------
WayneGretzky99
JaromirJagr68
BobbyOrr4
BobbyHull23
BrettHull16
MarioLemieux66
MarkMessier11
POINTS
------------------------
NAMEPOINTS
----------------
WayneGretzky244
JaromirJagr168
BobbyOrr129
BrettHull121
MarioLemieux189
JoeSakic94
Whichofthefollowingstatementswilldisplaytheplayername,number,andpointsforallplayersthathavescoredpoints?
∙A.SELECTp.name,n.number,p.pointsFROMnamesn
INNERJOINpointspONn.name=p.name
∙B.SELECTp.name,n.number,p.pointsFROMnamesn
LEFTOUTERJOINpointspONn.name=p.name
∙C.SELECTp.name,n.number,p.pointsFROMnamesn
RIGHTOUTERJOINpointspONn.name=p.name
∙D.SELECTp.name,n.number,p.pointsFROMnamesn
FULLOUTERJOINpointspONn.name=p.name
7.
Giventhefollowingtables:
YEAR_2006
EMPIDNAME
----------------
1Jagger,Mick
2Richards,Keith
3Wood,Ronnie
4Watts,Charlie
5Jones,Darryl
6Leavell,Chuck
YEAR_1962
EMPIDNAME
----------------
1Jagger,Mick
2Richards,Keith
3Jones,Brian
4Wyman,Bill
5Watts,Charlie
6Stewart,Ian
IfthefollowingSQLstatementisexecuted,howmanyrowswillbereturned?
SELECTnameFROMyear_2007
UNIONALL
SELECTnameFROMyear_1962
∙A.6
∙B.9
∙C.10
∙D.12
8.
Giventhefollowingtable:
EMPLOYEE
EMPIDNAMEINSTRUMENT
-----------------
1Jagger,Mick01
2Richards,Keith02
3Wood,Ronnie02
4Watts,Charlie03
5Jones,Darryl04
6Leavell,Chuck05
Ifthefollowingqueryisexecuted:
SELECTname,
CASEWHENinstrument='01'THEN'HARMONICA'
WHENinstrument='02'THEN'GUITAR'
WHENinstrument='03'THEN'DRUMS'
ELSE'UNKNOWN'
ENDASinstrument
FROMemployee
Whatwillbetheresults?
•A.
NAMEINSTRUMENT
------------------------------
Jagger,MickHARMONICA
Richards,KeithGUITAR
Wood,RonnieGUITAR
Watts,CharlieDRUMS
Jones,DarrylERROR
Leavell,ChuckERROR.
•B.
NAMEINSTRUMENT
-----------------------------
Jagger,MickHARMONICA
Richards,KeithGUITAR
Wood,RonnieGUITAR
Watts,CharlieDRUMS
Jones,Darryl04
Leavell,Chuck05
•C.
NAMEINSTRUMENT
-----------------------------
Jagger,MickHARMONICA
Richards,KeithGUITAR
Wood,RonnieGUITAR
Watts,CharlieDRUMS
Jones,DarrylUNKNOWN
Leavell,ChuckUNKNOWN
•D.
NAMEINSTRUMENT
-----------------------------
Jagger,MickHARMONICA
Richards,KeithGUITAR
Wood,RonnieGUITAR
Watts,CharlieDRUMS
Jones,Darryl-
Leavell,Chuck-
9.
Giventhefollowingtabledefinition:
SALES
---------------------------------------
INVOICE_NOCHAR(20)NOTNULL
SALES_DATEDATE
SALES_PERSONVARCHAR(25)
REGIONCHAR(20)
SALES_AMTDECIMAL(9,2)
WhichofthefollowingquerieswillreturnSALESinformation,sortedbySALES_PERSON,fromAtoZ,andSALES_DATE,frommostrecenttoearliest?
∙A.SELECTinvoice_no,sales_person,sales_date,sales_amtFROMsalesSORTBYsales_person,sales_dateDESC
∙B.SELECTinvoice_no,sales_person,sales_date,sales_amtFROMsalesSORTBYsales_personDESC,sales_date
∙C.SELECTinvoice_no,sales_person,sales_date,sales_amtFROMsalesORDERBYsales_person,sales_dateDESC
∙D.SELECTinvoice_no,sales_person,sales_date,sales_amtFROMsalesORDERBYsales_personDESC,sales_date
10.
Giventhefollowingstatement:
SELECThyear,AVG(salary)
FROMSELECTYEAR(hiredate)AShyear,salary
FROMemployeeWHEREsalary>30000)
GROUPBYhyear
Whichofthefollowingdescribestheresultifthisstatementisexecuted?
∙A.Thestatementwillreturntheyearandaveragesalaryforallemployeesthathaveasalarygreaterthan$30,000,sortedbyyear.
∙B.Thestatementwillreturntheyearandaveragesalaryforallemployeeshiredwithinagivenyearthathaveasalarygreaterthan$30,000.
∙C.Thestatementwillreturntheyearandaveragesalaryforallyearsthateveryemployeehiredhadasalarygreaterthan$30,000.
∙D.Thestatementwillreturntheyearandaveragesalaryforallyearsthatanyemployeehadasalarygreaterthan$30,000.
11.
WhichtwoofthefollowingstatementsaretrueabouttheHAVINGclause?
∙A.TheHAVINGclauseisusedinplaceoftheWHEREclause.
∙B.TheHAVINGclauseusesthesamesyntaxastheWHEREclause.
∙C.TheHAVINGclausecanonlybeusedwiththeGROUPBYclause.
∙D.TheHAVINGclauseacceptswildcards.
∙E.TheHAVINGclauseusesthesamesyntaxastheINclause.
12.
Giventhefollowingtable:
CURRENT_EMPLOYEES
--------------------------
EMPIDINTEGERNOTNULL
NAMECHAR(20)
SALARYDECIMAL(10,2)
PAST_EMPLOYEES
--------------------------
EMPIDINTEGERNOTNULL
NAMECHAR(20)
SALARYDECIMAL(10,2)
Assumingbothtablescontaindata,whichofthefollowingstatementswillNOTsuccessfullyadddatatotableCURRENT_EMPLOYEES?
∙A.INSERTINTOcurrent_employees(empid)VALUES(10)
∙B.INSERTINTOcurrent_employeesVALUES(10,'JAGGER',85000.00)
∙C.INSERTINTOcurrent_employeesSELECTempid,name,salaryFROMpast_employeesWHEREempid=20
∙D.INSERTINTOcurrent_employees(name,salary)VALUES(SELECTname,salaryFROMpast_employeesWHEREempid=20)
13.
GiventhefollowingUPDATEstatement:
UPDATEemployeesSETworkdept=
(SELECTdeptnoFROMdepartmentWHEREdeptno='A01')
WHEREworkdeptISNULL
Whichofthefollowingdescribestheresultifthisstatementisexecuted?
∙A.ThestatementwillfailbecauseanUPDATEstatementcannotcontainasubquery.
∙B.Thestatementwillonlysucceedifthedataretrievedbythesubquerydoesnotcontainmultiplerecords.
∙C.Thestatementwillsucceed;ifthedataretrievedbythesubquerycontainsmultiplerecords,onlythefirstrecordwillbeusedtoperformtheupdate.
∙D.ThestatementwillonlysucceedifeveryrecordintheEMPLOYEEStablehasanullvalueintheWORKDEPTcolumn.
14.
Giventhefollowingtabledefinition:
SALES
--------------------------
SALES_DATEDATE
SALES_PERSONCHAR(20)
REGIONCHAR(20)
SALESINTEGER
WhichofthefollowingSQLstatementswillremoveallrowsthathadaSALES_DATEintheyear1995?
∙A.DELETE*FROMsalesWHEREYEAR(sales_date)=1995
∙B.DELETEFROMsalesWHEREYEAR(sales_date)=1995
∙C.DROP*FROMsalesWHEREYEAR(sales_date)=1995
∙D.DROPFROMsalesWHEREYEAR(sales_date)=1995
15.
Giventhefollowingtabledefinition:
EMPLOYESS
--------------------------
EMPIDINTEGER
NAMECHAR(20)
DEPTCHAR(10)
SALARYDECIMAL(10,2)
COMMISSIONDECIMAL(8,2)
AssumingtheDEPTcolumncontainsthevalues'ADMIN','PRODUCTION',and'SALES',whichofthefollowingstatementswillproducearesultdatasetinwhichallADMINdepartmentemployeesaregroupedtogether,allPRODUCTIONdepartmentemployeesaregroupedtogether,andallSALESdepartmentemployeesaregroupedtogether?
∙A.SELECTname,deptFROMemployeesORDERBYdept
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- db2 V9 730 认证练习四SQL 认证 练习 SQL
![提示](https://static.bingdoc.com/images/bang_tan.gif)