Sql常见面试题(总结).pdf

Sql 常见面试题 总结 1 用一条 SQL 语句 查询出每门课都大于 80 分的学生姓名 name kecheng fenshu 张三 语文 81 张三 数学 75 李四 语文 76 李四 数学 90 王五 语文 81 王五 数学 100 王五 英语 90 A select distinct name from table where name not in select distinct name from table where fenshu 80 一个叫 department 的表 里面只有一个字段 name 一共有 4 条纪录 分别是 a b c d 对应四个球对 现在四个球对进行比赛 用一条 sql 语句显示所有可能的比赛组合 你先按你自己的想法做一下 看结果有我的这个简单吗 答 select a name b name from team a team b where a name b Debit101ccur 面试题 怎么把这样一个表儿 year month amount 1991 1 1 1 1991 2 1 2 1991 3 1 3 1991 4 1 4 1992 1 2 1 1992 2 2 2 1992 3 2 3 1992 4 2 4 查成这样一个结果 year m1 m2 m3 m4 1991 1 1 1 2 1 3 1 4 1992 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 year aaa year as m4 from 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 name 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 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 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 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 select from studentinfo where not exists select from student where studentinfo id student id and 系名称 高级 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 score 60 1 fail pass as mark from course 完全正确 SQL desc course v Name Null Type COURSEID NUMBER COURSENAME VARCHAR2 10 SCORE NUMBER SQL select from course v COURSEID COURSENAME SCORE 1 java 70 2 oracle 90 3 xml 40 4 jsp 30 5 servlet 80 SQL select courseid coursename score decode sign score 60 1 fail pass as mark from course v COURSEID COURSENAME SCORE MARK 1 java 70 pass 2 oracle 90 pass 3 xml 40 fail 4 jsp 30 fail 5 servlet 80 pass 原表 id proid proname 1 1 M 1 2 F 2 1 N 2 2 G 3 1 B 3 2 A 查询后的表 id pro1 pro2 1 M F 2 N G 3 B A 写出查询语句 解决方案 sql 求解 表a 列 a1 a2 记录 1 a 1 b 2 x 2 y 2 z 用select 能选成以下结果吗 1 ab 2 xyz 使用pl sql 代码实现 但要求你组合后的长度不能超出 oracle varchar2 长度的限制 下面是一个例子 create or replace type strings table is table of varchar2 20 create or replace function merge pv in strings table return varchar2 is ls varchar2 4000 begin for i in 1 pv count loop ls ls pv i end loop return ls end create table t id number name varchar2 10 insert into t values 1 Joan insert into t values 1 Jack insert into t values 1 Tom insert into t values 2 Rose insert into t values 2 Jenny column names format a80 select t0 id merge cast multiset select name from t where t id t0 id as strings table names from select distinct id from t t0 drop type strings table drop function merge drop table t 用sql Well if you have a thoretical maximum which I would assume you would given the legibility of listing hundreds of employees in the way you describe then yes But the SQL needs to use the LAG function for each employee hence a hundred emps a hundred LAGs so kind of bulky This example uses a max of 6 and would need more cut n pasting to do more than that SQL select deptno dname emps 2 from 3 select d deptno d dname rtrim e ename 4 lead e ename 1 over partition by d deptno 5 order by e ename 6 lead e ename 2 over partition by d deptno 7 order by e ename 8 lead e ename 3 over partition by d deptno 9 order by e ename 10 lead e ename 4 over partition by d deptno 11 order by e ename 12 lead e ename 5 over partition by d deptno 13 order by e ename emps 14 row number over partition by d deptno 15 order by e ename x 16 from emp e dept d 17 where d deptno e deptno 18 19 where x 1 20 DEPTNO DNAME EMPS 10 ACCOUNTING CLARK KING MILLER 20 RESEARCH ADAMS FORD JONES ROONEY SCOTT SMITH 30 SALES ALLEN BLAKE JAMES MARTIN TURNER WARD also 先create function get a2 create or replace function get a2 tmp a1 number return varchar2 is Col a2 varchar2 4000 begin Col a2 for cur in select a2 from unite a where a1 tmp a1 loop Col a2 Col a2 cur a2 end loop return Col a2 end get a2 select distinct a1 get a2 a1 from unite a 1 ABC 2 EFG 3 KMN 一个SQL 面试题 去年应聘一个职位未果 其间被考了一个看似简单的题 但我没有找到好的大案 不知各位大虾有无好的解法 题为 有两个表 t1 t2 Table t1 SELLER NON SELLER A B A C A D B A B C B D C A C B C D D A D B D C Table t2 SELLER COUPON BAL A 9 100 B 9 200 C 9 300 D 9 400 A 9 5 100 B 9 5 20 A 10 80 要求用SELECT 语句列出如下结果 如 A 的 SUM BAL 为 B C D 的和 B 的 SUM BAL 为A C D 的和 且用的方法不要增加数据库负担 如用临时表等 NON SELLER COUPON SUM BAL A 9 900 B 9 800 C 9 700 D 9 600 A 9 5 20 B 9 5 100 C 9 5 120 D 9 5 120 A 10 0 B 10 80 C 10 80 D 10 80 关于论坛上那个 SQL 微软面试题 问题 一百个账户各有100 某个账户某天如有支出则添加一条新记录 记录其余额 一百天后 请输出每天所有账户的余额信息 这个问题的难点在于每个用户在某天可能有多条纪录 也可能一条纪录也没有 不包括第 一天 返回的记录集是一个100 天 100 个用户的纪录集 下面是我的思路 1 创建表并插入测试数据 我们要求username 从 1 100 CREATE TABLE dbo TABLE2 username varchar 50 NOT NULL 用户名 outdate datetime NOT NULL 日期 cash float NOT NULL 余额 ON PRIMARY declare i int set i 1 while i 100 begin insert table2 values convert varchar 50 i 2001 10 1 100 insert table2 values convert varchar 50 i 2001 11 1 50 set i i 1 end insert table2 values convert varchar 50 i 2001 10 1 90 select from table2 order by outdate convert int username 2 组合查询语句 a 我们必须返回一个从第一天开始到 100 天的纪录集 如 2001 10 1 这个日期是任意的 到 2002 1 8 由于第一天是任意一天 所以我们需要下面的 SQL 语句 select top 100 dateadd d convert int username 1 min outdate as outdate from table2 group by username order by convert int username 这里的奥妙在于 convert int username 1 记得我们指定用户名从 1 100 group by username min outdate 第一天就可能每个用户有多个纪录 返回的结果 outdate 2001 10 01 00 00 00 000 2002 01 08 00 00 00 000 b 返回一个所有用户名的纪录集 select distinct username from table2 返回结果 username 1 10 100 99 c 返回一个 100 天记录集和 100 个用户记录集的笛卡尔集合 select from select top 100 dateadd d convert int username 1 min outdate as outdate from table2 group by username order by convert int username as A CROSS join select distinct username from table2 as B order by outdate convert int username 返回结果100 100 条纪录 outdate username 2001 10 01 00 00 00 000 1 2002 01 08 00 00 00 000 100 d 返回当前所有用户在数据库的有的纪录 select outdate username min cash as cash from table2 group by outdate username order by outdate convert int username 返回纪录 outdate username cash 2001 10 01 00 00 00 000 1 90 2002 01 08 00 00 00 000 100 50 e 将c 中返回的笛卡尔集和 d 中返回的纪录做 left join select C outdate C username D cash from select from select top 100 dateadd d convert int username 1 min outdate as outdate from table2 group by username order by convert int username as A CROSS join select distinct username from table2 as B as C left join select outdate username min cash as cash from table2 group by outdate username as D on C username D username and datediff d C outdate D outdate 0 order by C outdate convert int C username 注意 用户在当天如果没有纪录