《sql常见面试题》由会员分享，可在线阅读，更多相关《sql常见面试题（17页珍藏版）》请在金锄头文库上搜索。
1、Sql 常见面试题（总结）1.用一条 SQL 语句 查询出每门课都大于 80 分的学生姓名 name kecheng fenshu 张三 语文 81张三 数学 75李四 语文 76李四 数学 90王五 语文 81王五 数学 100王五 英语 90A: select distinct name from table where name not in (select distinct name from table where fenshub.Debit101ccur*面试题：怎么把这样一个表儿year month amount1991 1 1.11991 2 1.21991 3 1.31991
2、 4 1.41992 1 2.11992 2 2.21992 3 2.31992 4 2.4查成这样一个结果year m1 m2 m3 m41991 1.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、3 and m.year=aaa.year) as m3,(select amount from aaa m where month=4 and m.year=aaa.year) as m4from aaa group by year这个是 ORACLE 中做的：select * from (select name, year b1, lead(year) over(partition by name order by year) b2, lead(m,2) over(partition by name order by year) b3,rank()over(partition by nam
4、e order by year) rk from t) where rk=1;*精妙的 SQL 语句！精妙 SQL 语句 作者：不详 发文时间：2003.05.29 10:55:05 说明：复制表(只复制结构,源表名：a 新表名：b) SQL: select * into b from a where 11 说明：拷贝表(拷贝数据,源表名：a 目标表名：b) SQL: insert into b(a, b, c) select d,e,f from b; 说明：显示文章、提交人和最后回复时间 SQL: select a.title,a.username,b.adddate from table
5、 a,(select max(adddate) adddate from table where table.title=a.title) b 说明：外连接查询(表名 1：a 表名 2：b) SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 说明：日程安排提前五分钟提醒 SQL: select * from 日程安排 where datediff(minute,f 开始时间,getdate()5 说明：两张关联表，删除主表中已经在副表中没有的信息 SQL: delete from info
6、where not exists ( select * from infobz where info.infid=infobz.infid ) 说明：- SQL: SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE FROM TABLE1, (SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,YYYY/MM) = TO_
7、CHAR(SYSDATE, YYYY/MM) X, (SELECT NUM, UPD_DATE, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,YYYY/MM) = TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, YYYY/MM) /01,YYYY/MM/DD) - 1, YYYY/MM) ) Y, WHERE X.NUM = Y.NUM （+） AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) X.STOCK_ONHAND ) B WHERE A.NUM = B.NUM 说明：- SQL:
8、select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称=&strdepartmentname& and 专业名称=&strprofessionname& order by 性别,生源地,高考总成绩 说明： 从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源） SQL: SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, yyyy) AS telyear, S
9、UM(decode(TO_CHAR(a.telfeedate, mm), 01, a.factration) AS JAN, SUM(decode(TO_CHAR(a.telfeedate, mm), 02, a.factration) AS FRI, SUM(decode(TO_CHAR(a.telfeedate, mm), 03, a.factration) AS MAR, SUM(decode(TO_CHAR(a.telfeedate, mm), 04, a.factration) AS APR, SUM(decode(TO_CHAR(a.telfeedate, mm), 05, a.f
10、actration) AS MAY, SUM(decode(TO_CHAR(a.telfeedate, mm), 06, a.factration) AS JUE, SUM(decode(TO_CHAR(a.telfeedate, mm), 07, a.factration) AS JUL, SUM(decode(TO_CHAR(a.telfeedate, mm), 08, a.factration) AS AGU, SUM(decode(TO_CHAR(a.telfeedate, mm), 09, a.factration) AS SEP, SUM(decode(TO_CHAR(a.telf
11、eedate, mm), 10, a.factration) AS OCT, SUM(decode(TO_CHAR(a.telfeedate, mm), 11, a.factration) AS NOV, SUM(decode(TO_CHAR(a.telfeedate, mm), 12, a.factration) AS DEC FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration FROM TELFEESTAND a, TELFEE b WHERE a.tel = b.telfax) a GROUP BY
12、a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, yyyy) 说明：四表联查问题： SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where . 说明：得到表中最小的未使用的 ID 号 SQL: SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 E
13、LSE 1 END) as HandleID FROM Handle WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)*有两个表 A 和 B，均有 key 和 value 两个字段，如果 B 的 key 在 A 中也有，就把 B 的 value换为 A 中对应的 value这道题的 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.k
14、ey);*高级 sql 面试题原表: courseid coursename score - 1 java 70 2 oracle 90 3 xml 40 4 jsp 30 5 servlet 80 - 为了便于阅读,查询此表后的结果显式如下(及格分数为 60): courseid coursename score mark - 1 java 70 pass 2 oracle 90 pass 3 xml 40 fail 4 jsp 30 fail 5 servlet 80 pass - 写出此查询语句没有装，没试过 select courseid, coursename ,score ,decode（sign(scor
