SQL开发题目--答案

SQL开发题目与答案1SQL开发知识（每题2分，时间50分钟）11ORACLE数据库的字符串拼接符是答案_D_ABCD|12在日常工作，经常会根据实际要求对字符串的内容进行截取，那么SUBSTR及SUBSTRB两个函数的结果描述正确的是答案_C_A完成一样B完全不一样C单字节截取一样D双字节截取一样13当工作中需要从A表的COL1字段中查询出符合第三位字符中为”B”及倒数第二位字符串为“C”的数据，WHERE条件部分正确的是答案_D_AWHERECOL1LIKE_BCBWHERECOL1LIKEB_CCWHERECOL1LIKE_BC_DWHERECOL1LIKE_BC_14下列是关于ROUND和TRUNC操作的描述，正确的是答案_A_AROUND在截掉数据时有四舍五入的判断,TRUNC直接按要求截掉没有进位B都是将数据按指定的长度截断CTRUNC是四舍五入，ROUND是截断15都会四舍五入ORACLE用来判断列值是否为空的操作符答案_C_ANULLBASNULLCISNULLDLIKENULL16关于日常常见的几种表与表的内外连接方式，正确的是答案_D_AINNERJOIN、LEFTOUTERJOIN、RIGHTOUTERJOIN、WHOLEOUTERJOINBINNERJOIN、LEFTJOIN、RIGHTJOIN、WHOLEJOINCINNERJOIN、OUTERLEFTJOIN、OUTERRIGHTJOIN、OUTERFULLJOINDINNERJOIN、LEFTOUTERJOIN、RIGHTOUTERJOIN、FULLOUTERJOIND17ORACLE发出下列SELECT语句SQLSELECTEEMPNO,EENAME,DLOC2FROMEMPE,DEPTD3WHEREEDEPTNODDEPTNO4ANDSUBSTREENAME,1,1S下列哪个语句是ORACLE数据库中可用的ANSI兼容等价语句答案_AASELECTEMPNO,ENAME,LOCFROMEMPJOINDEPTONEMPDEPTNODEPTDEPTNOWHERESUBSTREMPENAME,1,1SBSELECTEMPNO,ENAME,LOCFROMEMP,DEPTONEMPDEPTNODEPTDEPTNOWHERESUBSTREMPENAME,1,1SCSELECTEMPNO,ENAME,LOCFROMEMPJOINDEPTWHEREEMPDEPTNODEPTDEPTNOANDSUBSTREMPENAME,1,1SDSELECTEMPNO,ENAME,LOCFROMEMPJOINDEPTONEMPDEPTNODEPTDEPTNOANDSUBSTREMPENAME,1,1S18检查SQLPLUS的下列输出PRODUCTIDPRODUCTNAMEBOXLOCATION578XWIDGETIDAHOTENNESSEE456YWIDGET下列哪个选项是产生这个结果的查询类型答案_AA全外连接B左外连接C右外连接D等连接19要从ORDERS表中取得数据，其中包括三个列CUSTOMER、ORDER_DATE与ORDER_AMT。可以用下列哪个WHERE子句查询ORDERS表，取得客户名称为LESLIE且订单金额超过2700的订单答案_DAWHERECUSTOMERLESLIEBWHERECUSTOMERLESLIEANDORDER_AMT2700DWHERECUSTOMERLESLIEANDORDER_AMT2700110看看下列语句SQLSELECTENAME2FROMEMP3WHEREEMPNOIN4SELECTEMPNO5FROMEXPENSE6WHEREVOUCH_AMT10000下列哪个SQL语句产生与上面相同的输出，改写成使用运算符答案_BASELECTEENAMEFROMEMPEWHEREEXISTSSELECTXEMPNOFROMEXPENSEXWHEREXVOUCH_AMT10000ANDXEMPNOEEMPNOBSELECTEENAMEFROMEMPEWHEREEXISTSSELECTXEMPNOFROMEXPENSEXWHEREXVOUCH_AMT10000ANDXEMPNOEEMPNOCSELECTEENAMEFROMEMPEWHEREXEMPNOEEMPNOANDEXISTSSELECTXEMPNOFROMEXPENSEXWHEREXVOUCH_AMT10000DSELECTEENAMEFROMEMPE,EXPENSEXWHEREXEMPNOEEMPNOANDXVOUCH_AMT10000ANDEXISTSSELECTXEMPNOFROMEXPENSEXWHERE用下列代码所示的内容回答后面问题EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO7369SIMTHCLERK790217DEC80800207499ALLENSALESMAN769820FEB811600300307521WARDSALESMAN769822FEB811250500307566JONESMANAGER783902APR812975207654MARTINSALESMAN769828SEP8112501400307698BLAKEMANAGER783901MAY812850307782CLARKMANAGER783909JUN812450107788SCOTTANALYST756619APR873000207839KINGPRESIDENT17NOV815000107844TURNERSALESMAN769808SET8115000307876ADAMSCLERK778823MAY871100207900JAMESCLERK768903DEC81950307902FORDANALYST756603DEC813000207934MILLERCLERK778223JAN82130010111以下选项中哪个查询返回能正确返回员工对应的经理名称答案_BASELECTAEANME,BNAMEFROMEMPARIGHTJOINEMPBONAEMPNOBMAGBSELECTAEANME,BNAMEFROMEMPALEFTJOINEMPBONAEMPNOBMAGCSELECTAEANME,BNAMEFROMEMPAINNERJOINEMPBONAEMPNOBMAGDSELECTAEANME,BNAMEFROMEMPA,EMPBWHEREAEMPNOBEMPNO112以下选项中哪个是下列查询返回的值SELECTTO_CHARHIREDATA,YYYYMMDDFROMEMPWHERENAMELIKE_AN答案_CE28SEP81F20FEB81G1981020H19810928113以下选项中哪个是下列查询返回的值SELECTMAXLENGTHJOB,MINSALFROMEMP答案_CI9,5000J8,950K9,800L8,5000114以下选项中哪个是下列查询返回的值SELECTSUMSALSUMCOMMFROMEMPWHEREJOBANALYSTANDENAMELIKEJ答案_DA6000B9925C9975DNULL115以下选项中哪个是下列查询返回的值SELECTCOUNTENAMEFROMEMPWHEREDEPTNO10答案_CA1B2C3DNULL116以下选项中哪个是下列查询返回的值SELECTCOUNTFROMEMPWHEREMGR77002答案_AA5B6C7DNUL117以下选项中哪个是下列SQL命令产生的第三个员工的名称为SELECTLOWERENAMEFROMEMPWHEREJOBSALESMANORDERBYEMPNODESC答案_DAWARDBMARTIONCMARTIONDWARD118以下选项中哪个是发出下列查询后ORACLE返回的值SELECTSUBSTRJOB,2,4FROMEMPWHEREENAMELIKEUPPER_AR答案_CAMANABCLECANAGDSALE119以下选项中哪个是发出下列查询后ORACLE返回的值SELECTTRUNKMONTHS_BETWEENMINHIREDATE,MAXHIREDATEFROMEMP答案_CA24B25C24D25120以下选项中哪个是发出下列查询后ORACLE返回的值SELECTMOD,300FROMEMPWHERELOWERENAMEALLEN答案_CA100B1000C1600D800根据下面的初始化信息完成相关查询SQLCREATETABLESTUDENTSNOVARCHAR210PRIMARYKEY,SNAMEVARCHAR220,SAGENUMBER2,SSEXVARCHAR25CREATETABLETEACHERTNOVARCHAR210PRIMARYKEY,TNAMEVARCHAR220CREATETABLECOURSECNOVARCHAR210,CNAMEVARCHAR220,TNOVARCHAR220,CONSTRAINTPK_COURSEPRIMARYKEYCNO,TNOCREATETABLESCSNOVARCHAR210,CNOVARCHAR210,SCORENUMBER4,2,CONSTRAINTPK_SCPRIMARYKEYSNO,CNO/初始化学生表的数据/INSERTINTOSTUDENTVALUESS001,STUDENT1,23,男INSERTINTOSTUDENTVALUESS002,STUDENT2,23,男INSERTINTOSTUDENTVALUESS003,STUDENT3,25,男INSERTINTOSTUDENTVALUESS004,STUDENT4,20,女INSERTINTOSTUDENTVALUESS005,STUDENT5,20,女INSERTINTOSTUDENTVALUESS006,STUDENT6,21,男INSERTINTOSTUDENTVALUESS007,STUDENT7,21,男INSERTINTOSTUDENTVALUESS008,STUDENT8,21,女INSERTINTOSTUDENTVALUESS009,STUDENT9,23,女INSERTINTOSTUDENTVALUESS010,STUDENT10,22,女COMMIT/初始化教师表/INSERTINTOTEACHERVALUEST001,TECCHER1INSERTINTOTEACHERVALUEST002,TECCHER2INSERTINTOTEACHERVALUEST003,TECCHER3COMMIT/初始化课程表/INSERTINTOCOURSEVALUESC001,J2SE,T002INSERTINTOCOURSEVALUESC002,JAVAWEB,T002INSERTINTOCOURSEVALUESC003,SSH,T001INSERTINTOCOURSEVALUESC004,ORACLE,T001INSERTINTOCOURSEVALUESC005,SQLSERVER2005,T003INSERTINTOCOURSEVALUESC006,C,T003INSERTINTOCOURSEVALUESC007,JAVASCRIPT,T002INSERTINTOCOURSEVALUESC008,DIVCSS,T001INSERTINTOCOURSEVALUESC009,PHP,T003INSERTINTOCOURSEVALUESC010,EJB30,T002COMMIT/初始化成绩表/INSERTINTOSCVALUESS001,C001,789INSERTINTOSCVALUESS002,C001,809INSERTINTOSCVALUESS003,C001,819INSERTINTOSCVALUESS004,C001,609INSERTINTOSCVALUESS001,C002,829INSERTINTOSCVALUESS002,C002,729INSERTINTOSCVALUESS003,C002,819INSERTINTOSCVALUESS001,C003,59COMMIT121查询男生、女生人数SELECTSSEX,COUNTFROMSTUDENTGROUPBYSSEX122查询每门课程被选修的学生数SELECTCNO,COUNTSNOFROMSCGROUPBYCNO123查询出只选修了一门课程的全部学生的学号和姓名SELECTSCSNO,STSNAME,COUNTCNOFROMSTUDENTSTLEFTJOINSCONSCSNOSTSNOGROUPBYSTSNAME,SCSNOHAVINGCOUNTCNO1124查询平均成绩大于60分的同学的学号和平均成绩；SELECTSNO,AVGSCOREFROMSCGROUPBYSNOHAVINGAVGSCORE60125查询所有同学的学号、姓名、选课数、总成绩；SELECTA,SSNAMEFROMSELECTSNO,SUMSCORE,COUNTCNOFROMSCGROUPBYSNOA,STUDENTSWHEREASNOSSNO126查询所有课程成绩小于60分的同学的学号、姓名；SELECTST,SSCOREFROMSTUDENTSTJOINSCSONSTSNOSSNOJOINCOURSECONSCNOCCNOWHERESSCORE60THEN1ELSE0END/COUNTAS及格率（名称可处定义）FROMSCGROUPBYCNOORDERBYAVGSCORE,及格率（名称可处定义）DESC130统计列印各