sql1面试题5.pdf

Sql 常见面试题 总结 1 用一条 SQL 语句 查询出每门课都大于 80 分的学生姓名 namekechengfenshu 张三语文81 张三数学75 李四语文76 李四数学90 王五语文81 王五数学100 王五英语90 1 select stu namefrom stu grade minus select stu name from stu grade g whereg grade 80 2 select distinct stu name from stu grade ou where not exists select 1 from stu grade inn whereou stu name inn stu name and inn grade 80 3 select distinct t1 stu name from stu grade t1 where t1 stu name not in selectdistinctt2 stu namefromstu gradet2where t2 grade 80 2 学生表 如下 自动编号学号姓名 课程编号 课程名称 分数 12005001 张三 0001数学69 22005002 李四 0001数学89 32005001 张三 0001数学69 删除除了自动编号不同 其他都相同的学生冗余信息 A delete tablename where 自动编号 not in select min 自动编号 from tablename group by 学 号 姓名 课程编号 课程名称 分数 1 在 where 的左右都不能使用 组函数 2 一般来说 都可以使用exists 代替 in 如果在 in 的条件中使用了 组函数 查 询到值 那么就不能使用exists 来代替 in 了 就像本题 3 一个叫 department 的表 里面只有一个字段 name 一共有 4 条纪录 分别是 a b c d 对 应四个球对 现在四个球对进行比赛 用一条 sql 语句显示所有可能的比赛组合 你先按你自己的想法做一下 看结果有我的这个简单吗 答 select a name b namefrom team a team bwhere a name b Debit101ccur 面试题 怎么把这样一个表儿查成这样一个结果 yearmonth amountyear m1m2m3m4 199111 11991 1 1 1 2 1 3 1 4 199121 21992 2 1 2 2 2 3 2 4 199131 3 199141 4 199212 1 199222 2 199232 3 199242 4 答案一 select year select amount fromaaa m where month 1and m year aaa year as m1 select amount fromaaa m where month 2and m year aaa year as m2 select amount fromaaa m where month 3and m year aaa year as m3 select amount fromaaa m where month 4and m year aaa year as m4 from aaagroup 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 精妙的精妙的 SQLSQL 语句 语句 精妙 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 deletefrominfowherenotexists select frominfobzwhere info infid infobz infid 说明 SQL select fromstudentinfowherenotexists select fromstudentwhere studentinfo id student id and 系 名 称 高级高级 sqlsql 面试题面试题 原表 为了便于阅读 查询此表后的结果显式如下 及格分数为 60 courseid coursename scorecourseid coursename score mark 1 java 701 java 70 pass 2 oracle 902 oracle 90 pass 3 xml 403 xml 40 fail 4 jsp 304 jsp 30 fail 5 servlet 805 servlet 80 pass SQL select courseid coursename score decode sign score 60 1 fail pass as mark from course v 一个 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 关于论坛上那个关于论坛上那个 SQLSQL 微软面试题微软面试题 问题 一百个账户各有 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 条纪录 outdateusername 2001 10 01 00 00 00 0001 2002 01 08 00 00 00 000100 d 返回当前所有用户在数据库的有的纪录 select outdate username min cash as cash from table2 group by outdate username order by outdate convert int username 返回纪录 outdateusernamecash 2001 10 01 00 00 00 000190 2002 01 08 00 00 00 00010050 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 注意 用户在当天如果没有纪录 cash 字段返回 NULL 否则 cash 返回每个用户当天的余额 outdateusernamecash 2001 10 01 00 00 00 000190 2001 10 01 00 00 00 0002100 2001 10 02 00 00 00 000190 2001 10 02 00 00 00 0002NULL 注意这里 2002 01 08 00 00 00 00010050 f 好了 现在我们最后要做的就是 如果 cash 为 NULL 我们要返回小于当前纪录日期的第 一个用户余额 由于我们使用 order by cash 所以返回 top 1 纪录即可 使用 min 应该也可 以 这个余额即为当前的余额 case isnull D cash 0 when 0 then select top 1 cash from table2 where table2 username C username and datediff d C outdate table2 outdate 0 order by table2 cash else D cash end as cash g 最后组合的完整语句就是 select C outdate C username case isnull D cash 0 when 0 then select top 1 cash from table2 where table2 username C username and datediff d C outdate table2 outdate b score and a s b s 2 查询平均成绩大于 60 分的同学的学号和平均成绩 select S avg score from sc group by S having avg score 60 3 查询所有同学的学号 姓名 选课数 总成绩 select Student S Student Sname count SC C sum score from Student left Outer join SC on Student S SC S group by Student S Sname 4 查询姓 李 的老师的个数 select count distinct Tname from Teacher where Tname like 李 5 查询没学过 叶平 老师课的同学的学号 姓名 select Student S Student Sname from Student whereS notin selectdistinct SC S fromSC Course Teacherwhere SC C Course C and Teacher T Course T and Teacher Tname 叶平 6 查询学过 001 并且也学过编号 002 课程的同学的学号 姓名 selectStudent S Student SnamefromStudent SCwhereStudent S SC S and SC C 001 andexists Select fromSCSC 2whereSC 2 S SC S and SC 2 C 002 exists 是 Oracle sql 中的一个函数 表示是否存在符合某种条件的记录 7 查询学过 叶平 老师所教的所有课的同学的学号 姓名 select S Sname from Student where S in select S from SC Course Teacher where SC C Course C and Teacher T Course T andTeacher Tname 叶平 groupbyS having count SC C select count C from Course Teacher where Teacher T Course T and Tname 叶平 8 查询课程编号 002 的成绩比课程编号 001 课程低的所有同学的学号 姓名 Select S Sname from select Student S Student Sname score select score from SC SC 2 where SC 2 S Student S and SC 2 C 002 score2 from Student SC where Student S SC S and C 001 S 2 where score2 60 10 查询没有学全所有课的同学的学号 姓名 select Student S Student Sname from Student SC where Student S SC S group by Student S Student Sname having count C 60 THEN 1 ELSE 0 END COUNT AS 及 格百分数 FROM SC T Course where t C course C GROUP BY t C ORDER BY 100 SUM CASE WHEN isnull score 0 60 THEN 1 ELSE 0 END COUNT DESC 20 查询如下课程平均成绩和及格率的百分数 用 1 行 显示 企业管理 001 马克思 002 OO 23 统 计 列 印 各 科 成 绩 各 分 数 段 人 数 课 程ID 课 程 名 称 100 85 85 70 70 60 60 SELECT SC C as 课程 ID Cname as 课程名称 SUM CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END AS 100 85 SUM CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END AS 85 70 SUM CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END AS 70 60 SUM CASE WHEN score T2 平均成绩 as 名次 S as 学生学号 平均成绩 FROM SELECT S AVG score 平均成绩 FROM SC GROUP BY S AS T2 ORDER BY 平均成绩 desc 25 查询各科成绩前三名的记录 不考虑成绩并列情况 SELECT t1 S as 学生 ID t1 C as 课程 ID Score as 分数 FROM SC t1 WHERE score IN SELECT TOP 3 score FROM SC WHERE t1 C C ORDER BY score DESC ORDER BY t1 C 26 查询每门课程被选修的学生数 select c count S from sc group by C 27 查询出只选修了一门课程的全部学生的学号和姓名 select SC S Student Sname count C AS 选课数 from SC Student where SC S Student S group by SC S Student Sname having count C 1 28 查询男生 女生人数 Select count Ssex as 男生人数 from Student group by Ssex having Ssex 男 Select count Ssex as 女生人数 from Student group by Ssex having Ssex 女 29 查询姓 张 的学生名单 SELECT Sname FROM Student WHERE Sname like 张 30 查询同名同性学生名单 并统计同名人数 select Sname count from Student group by Sname having count 1 31 1981 年出生的学生名单 注 Student 表中 Sage 列的类型是 datetime select Sname CONVERT char 11 DATEPART year Sage as age from student where CONVERT char 11 DATEPART year Sage 1981 32 查询每门课程的平均成绩 结果按平均成绩升序排列 平均成绩相同时 按课程号降序 排列 Select C Avg score from SC group by C order by Avg score C DESC 33 查询平均成绩大于 85 的所有学生的学号 姓名和平均成绩 select Sname SC S avg score from Student SC where Student S SC S group by SC S Sname havingavg score 85 34 查询课程名称为 数据库 且分数低于 60 的学生姓名和分数 Select Sname isnull score 0 from Student SC Course where SC S Student S and SC C Course C and Course Cname 数据库 and score 70 AND SC S student S 37 查询不及格的课程 并按课程号从大到小排列 select c from sc where scor e 80 and C 003 39 求选了课程的学生人数 select count from sc 40 查询选修 叶平 老师所授课程的学生中 成绩最高的学生姓名及其成绩 select Student Sname score from Student SC Course C Teacher where Student S SC S and SC C C C and C T Teacher T and Teacher Tname 叶平 and SC score