SQL练习题

例题1、写出一条Sql语句：取出表A中第31到第40记录（Mysql）select*fromAlimit30,10MS-SQLServer解1：selecttop10*fromAwhereidnotin(selecttop30idfromA)解2：selecttop10*fromAwhereid(selectmax(id)from(selecttop30idfromA)asA)解3：select*from(select*,Row_Number()OVER(ORDERBYidasc)rowidFROMA)asAwhererowidbetween31and40Oracleselect*from(selectA.*,row_number()over(orderbyidasc)rankFROMA)whererank=31ANDrank=40;2、用一条SQL语句查询出每门课都大于80分的学生姓名namekechengfenshu张三语文81张三数学75李四语文76李四数学90王五语文81王五数学100王五英语90A:selectdistinctnamefromtablewherenamenotin(selectdistinctnamefromtablewherefenshu=80)selectnamefromtablegroupbynamehavingmin(fenshu)803、学生表如下:自动编号学号姓名课程编号课程名称分数12005001张三0001数学6922005002李四0001数学8932005001张三0001数学69删除除了自动编号不同,其他都相同的学生冗余信息A:deletetablenamewhere自动编号notin(selectmin(自动编号)fromtablenamegroupby学号,姓名,课程编号,课程名称,分数)4、请用SQL语句实现：从TestDB数据表中查询出所有月份的发生额都比101科目相应月份的发生额高的科目。请注意：TestDB中有很多科目，都有1－12月份的发生额。AccID：科目代码，Occmonth：发生额月份，DebitOccur：发生额。数据库名：JcyAudit，数据集：Select*fromTestDB答：selecta.*fromTestDBa,(selectOccmonth,max(DebitOccur)Debit101ccurfromTestDBwhereAccID='101'groupbyOccmonth)bwherea.Occmonth=b.Occmonthanda.DebitOccurb.Debit101ccur5、一个叫team的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，现在四个球对进行比赛，用一条sql语句显示所有可能的比赛组合.你先按你自己的想法做一下，看结果有我的这个简单吗？selecta.name,b.namefromteama,teambwherea.nameb.name6、面试题：怎么把这样一个表儿yearmonthamount199111.1199121.2199131.3199141.4199212.1199222.2199232.3199242.4查成这样一个结果yearm1m2m3m419911.11.21.31.419922.12.22.32.4答案一、selectyear,(selectamountfromaaamwheremonth=1andm.year=aaa.year)asm1,(selectamountfromaaamwheremonth=2andm.year=aaa.year)asm2,(selectamountfromaaamwheremonth=3andm.year=aaa.year)asm3,(selectamountfromaaamwheremonth=4andm.year=aaa.year)asm4fromaaagroupbyyear7、说明：复制表(只复制结构,源表名：a新表名：b)SQL:select*intobfromawhere11(where1=1，拷贝表结构和数据内容)Oracle:createtablebAsSelect*fromawhere1=2[（不等于）(SQLServerCompact)比较两个表达式。当使用此运算符比较非空表达式时，如果左操作数不等于右操作数，则结果为TRUE。否则，结果为FALSE。]8、说明：拷贝表(拷贝数据,源表名：a目标表名：b)SQL:insertintob(a,b,c)selectd,e,ffroma;9、说明：显示文章、提交人和最后回复时间SQL:selecta.title,a.username,b.adddatefromtablea,(selectmax(adddate)adddatefromtablewheretable.title=a.title)b10.说明：外连接查询(表名1：a表名2：b)SQL:selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTERJOINbONa.a=b.cORACLE：selecta.a,a.b,a.c,b.c,b.d,b.ffroma,bwherea.a=b.c(+)11.说明：日程安排提前五分钟提醒SQL:select*from日程安排wheredatediff('minute',f开始时间,getdate())512.说明：两张关联表，删除主表中已经在副表中没有的信息SQL:Deletefrominfowherenotexists(select*frominfobzwhereinfo.infid=infobz.infid)13.有两个表A和B，均有key和value两个字段，如果B的key在A中也有，就把B的value换为A中对应的value这道题的SQL语句怎么写？updatebsetb.value=(selecta.valuefromawherea.key=b.key)whereb.idin(selectb.idfromb,awhereb.key=a.key);高级sql面试题14.原表:courseidcoursenamescore-------------------------------------1Java702oracle903xml404jsp305servlet80-------------------------------------为了便于阅读,查询此表后的结果显式如下(及格分数为60):courseidcoursenamescoremark---------------------------------------------------1Java70pass2oracle90pass3xml40fail4jsp30fail5servlet80pass---------------------------------------------------写出此查询语句selectcourseid,coursename,score,decode（sign(score-60),-1,'fail','pass')asmarkfromcourse(SQL:selectcourseid,coursename,score,（casewhenscore60then'fail'else'pass'end)asmarkfromcourse)ORACLE:selectcourseid,coursename,score,decode（sign(score-60),-1,'fail','pass')asmarkfromcourse(DECODE函数是ORACLEPL/SQL是功能强大的函数之一，目前还只有ORACLE公司的SQL提供了此函数)完全正确SQLdesccourse_vNameNull?Type-----------------------------------------------------------------------------COURSEIDNUMBERCOURSENAMEVARCHAR2(10)SCORENUMBERSQLselect*fromcourse_v;COURSEIDCOURSENAMESCORE------------------------------1java702oracle903xml404jsp305servlet80SQLselectcourseid,coursename,score,decode(sign(score-60),-1,'fail','pass')asmarkfromcourse_v;COURSEIDCOURSENAMESCOREMARK----------------------------------1java70pass2oracle90pass3xml40fail4jsp30fail5servlet80pass15.SQL面试题（1）createtabletesttable1(idintIDENTITY,departmentvarchar(12))select*fromtesttable1insertintotesttable1values('设计')insertintotesttable1values('市场')insertintotesttable1values('售后')/*结果iddepartment1设计2市场3售后*/createtabletesttable2(idintIDENTITY,dptIDint,namevarchar(12))insertintotesttable2values(1,'张三')insertintotesttable2values(1,'李四')insertintotesttable2values(2,'王五')insertintotesttable2values(3,'彭六')insertintotesttable2values(4,'陈七')/*用一条SQL语句，怎么显示如下结果iddptIDdepartmentname11设计张三21设计李四32市场王五43售后彭六54黑人陈七*/答案：SELECTtesttable2.*,ISNULL(department,'黑人')FROMtesttable1rightjointesttable2ontesttable2.dptID=testtable1.ID也做出来了可比这方法稍复杂。/16.sql面试题（2）有表A，结构如下：A:p_IDp_Nums_id11001112022801311013803其中：p_ID为产品ID，p_Num为产品库存量，s_id为仓库ID。请用SQL语句实现将上表中的数据合并，合并后的数据为：p_IDs1_ids2_ids3_id110120280031108其中：s1_id为仓库1的库存量，s2_id为仓库2的库存量，s3_id为仓库3的库存量。如果该产品在某仓库中无库存量，那么就是0代替。结果：selectp_id,sum(casewhens_id=1thenp_numelse0end)ass1_id,sum(casewhens_id=2thenp_numelse0end)ass2_id,sum(casewhens_id=3thenp_numelse0end)ass3_idfrommyProgroupbyp_id什么是相关子查询?如何使用这些查询?经验更加丰富的开发人员将能够准确地描述这种类型的查询。相关子查询是一种包含子查询的特殊类型的查询。查询里包含的子查询会真正请求外部查询的值，从而形成一个类似于循环的状况。19、为管理业务培训信息，建立3个表：S(S#,SN,SD,SA)S#,SN,SD,SA分别代表学号，学员姓名，所属单位，学员年龄C(C#,CN)C#,CN分别代表课程编号，课程名称SC(S#,C#