《SQL知识学习总结分析题》由会员分享，可在线阅读，更多相关《SQL知识学习总结分析题（38页珍藏版）》请在金锄头文库上搜索。
1、例题1、写出一条Sql语句：取出表A中第31到第40记录 （Mysql）select * from Alimit 30, 10MS-SQLServer解1：select top 10 * from A where id not in (select top 30 id from A)解2：select top 10 * from A where id (select max(id) from (select top 30 id from A )as A)解3：select * from (select *, Row_Number() OVER (ORDER BY id asc) rowid F
2、ROM A) as A where rowid between 31 and 40Oracleselect *from (select A.*,row_number() over (order by id asc) rankFROM A) where rank =31 AND rank=40;2、用一条SQL语句 查询出每门课都大于80分的学生姓名name kecheng fenshu张三语文 81张三数学 75李四语文 76李四数学 90王五语文 81王五数学 100王五英语 90A: select distinct name from table where name not in (se
3、lect distinct name from table where fenshu803、学生表 如下:自动编号学号姓名 课程编号 课程名称 分数1 2005001张三0001数学 692 2005002李四0001数学 893 2005001张三0001数学 69删除除了自动编号不同,其他都相同的学生冗余信息A: delete tablename where自动编号not in(select min(自动编号) from tablename group by学号,姓名,课程编号,课程名称,分数)4、请用SQL语句实现：从TestDB数据表中查询出所有月份的发生额都比101科目相应月份的发生
4、额高的科目。请注意：TestDB中有很多科目，都有112月份的发生额。AccID：科目代码，Occmonth：发生额月份，DebitOccur：发生额。数据库名：JcyAudit，数据集：Select * from TestDB答：select a.*from TestDB a,(select Occmonth,max(DebitOccur) Debit101ccur from TestDB where AccID=101 group by Occmonth) bwhere a.Occmonth=b.Occmonth and a.DebitOccurb.Debit101ccur5、一个叫tea
5、m的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，现在四个球对进行比赛，用一条sql语句显示所有可能的比赛组合.你先按你自己的想法做一下，看结果有我的这个简单吗？select a.name, b.namefrom team a, team bwhere a.name b.name6、面试题：怎么把这样一个表儿year month amount1991 1 1.11991 2 1.21991 3 1.31991 4 1.41992 1 2.11992 2 2.21992 3 2.31992 4 2.4查成这样一个结果year m1 m2 m3 m41991 1
6、.1 1.2 1.3 1.41992 2.1 2.2 2.3 2.4答案一、select year,(select amount from aaa m where month=1 and m.year=aaa.year) as m1,(select amount from aaa m where month=2 and m.year=aaa.year) as m2,(select amount from aaa m where month=3 and m.year=aaa.year) as m3,(select amount from aaa m where month=4 and m.yea
7、r=aaa.year) as m4from aaa group by year7、说明：复制表(只复制结构,源表名：a新表名：b)SQL: select * into b from a where 11 (where1=1，拷贝表结构和数据内容)Oracle:create table bAsSelect * from a where 1=2（不等于）(SQL Server Compact)比较两个表达式。 当使用此运算符比较非空表达式时，如果左操作数不等于右操作数，则结果为 TRUE。 否则，结果为 FALSE。8、说明：拷贝表(拷贝数据,源表名：a目标表名：b)SQL: insert int
8、o b(a, b, c) select d,e,f from a;9、说明：显示文章、提交人和最后回复时间SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b10.说明：外连接查询(表名1：a表名2：b)SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUTER JOIN b ON a.a = b.cORACLE：select a.a, a.b,
9、a.c, b.c, b.d, b.f from a ,bwhere a.a = b.c(+)11.说明：日程安排提前五分钟提醒SQL: select * from日程安排where datediff(minute,f开始时间,getdate()512.说明：两张关联表，删除主表中已经在副表中没有的信息SQL:Delete from info where not exists (select * from infobz where info.infid=infobz.infid )13.有两个表A和B，均有key和value两个字段，如果B的key在A中也有，就把B的value换为A中对应的va
10、lue这道题的SQL语句怎么写？update b set b.value=(select a.value from a where a.key=b.key) where b.id in(select b.id from b,a where b.key=a.key);高级sql面试题14.原表:courseid coursename score-1Java702 oracle 903 xml 404 jsp 305 servlet 80-为了便于阅读,查询此表后的结果显式如下(及格分数为60):courseid coursename score mark-1Java70 pass2 oracle
11、 90 pass3 xml 40 fail4 jsp 30 fail5 servlet 80 pass-写出此查询语句select courseid, coursename ,score ,decode（sign(score-60),-1,fail,pass) as mark from course(SQL: select courseid, coursename ,score ,（case when score desc course_vName Null? Type- - -COURSEID NUMBERCOURSENAME VARCHAR2(10)SCORE NUMBERSQL sele
12、ct * from course_v;COURSEID COURSENAME SCORE- - -1 java 702 oracle 903 xml 404 jsp 305 servlet 80SQL select courseid, coursename ,score ,decode(sign(score-60),-1,fail,pass) as mark from course_v;COURSEID COURSENAME SCORE MARK- - - -1 java 70 pass2 oracle 90 pass3 xml 40 fail4 jsp 30 fail5 servlet 80 pass15.SQL面试题（1）create table testtable1(id int IDENTITY,department varchar(12)select * from testtable1insert into testtable1 values(设计)inser
