SQL面试题
1. 有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条SQL语句查询出这三条记录并按以下条件显示出来： 大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。
select
(case when 语文 >= 80 then '优秀' when 语文 >= 60 and 语文 < 80 then '及格' else '不及格' end) as 语文,
(case when 数学 >= 80 then '优秀' when 数学 >= 60 and 数学 < 80 then '及格' else '不及格' end) as 数学,
(case when 英语 >= 80 then '优秀' when 英语 >= 60 and 英语 < 80 then '及格' else '不及格' end) as 英语
from
[table]
2. 有一张table-t表,里面有三个字段：英语,其中有三条记录分别表示语文70分,数学80分,英语58,请用一条sql语句查询出这三条记录并按以下条件显示出来：大于或等于80表优秀,大于等于60表及格,小于60表不及格显示格式：及格 优秀 不及格
select case when 语文>=80 then '优秀' when 语文>=60 then '及格' else '不及格' end 语文 ,
case when 数学>=80 then '优秀' when 数学>=60 then '及格' else '不及格' end 数学 ,
case when 英语>=80 then '优秀' when 英语>=60 then '及格' else '不及格' end 英语 from table_t
3. 表结构和数据
date result
2011-02-01 胜
2011-02-01 负
2011-02-01 胜
2011-02-02 胜
2011-02-02 负
查询出来的结果：
date 胜 负
2011-02-01 2 1
2011-02-02 1 1
请写出SQL语句：
SELECT
date,
( SELECT count( * ) FROM table1 WHERE date = t.date AND result = '胜' ) AS '胜',
( SELECT count( * ) FROM table1 WHERE date = t.date AND result = '负' ) AS '负'
FROM
table1 AS t
GROUP BY
date;
4. 已知商品的关系模型：G(GNO,GNAME,GSALES,GPRICE,GTIME),GNO为商品编码，GNAME为商品名称，GSALES为商品销售量，GPRICE为商品价格，GTIME为商品上架时间。按要求写SQL语句，假设数据库是mysql。创建这张商品表的sql。取商品销售额排前5名的商品名称和上架时间。
创建表
CREATE TABLE `G` (
`GNO` varchar(10) NOT NULL,
`GNAME` varchar(20) NULL,
`GSALES` decimal(10, 2) NULL,
`GPRICE` decimal(10, 2) NULL,
`GTIME` datetime(0) NULL,
PRIMARY KEY (`GNO`)
);
查询销售额前5的商品名称和上架时间
select GNAME,GTIME from g order by GSALES desc limit 0,5
5. 用SQL语句查询每门课都大于80的学生姓名
name kecheng fenshu
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90
方法一：
思路：如果能获得一张表，由学生姓名，语文成绩，数学成绩，英语成绩的表，剩下的就是在WHERE条件中筛选及可以获得想要的结果。
具体办法：通过自连接的办法，以“姓名”为连接条件，自连接三次，便可以获得包含又姓名和三门课程成绩的数据行。虽然可以得到想要的数据列。但会有很多冗余重复列！
点评：此方法是根据题目，依题解题，中规中矩！ 不过多张表连接非常耗费时间。而且SQL语句也比较复杂，需要注意事项很多。
SELECT D.name FROM (
SELECT S.name,S.score AS ITEM1,S1.score AS ITEM2,S2.score AS ITEM3
FROM Student S
inner join Student S1 on S.name = S1.name and S.course <> S1.course
inner join Student S2 on S.name = S2.name and S.course <> S2.course
WHERE S.score>=80 and S1.score>=80 and S2.score>=80
) D
GROUP BY D.name
方法二：
思路：采用逆向思维想想。。。。。。求三门成绩都大于80的人，也可以是使先查出有成绩小于80 的人，再除去这些人不就是三门成绩都大于80的人了么？ 以前学过的数学逻辑逆向思维还真是有用的阿！！
具体办法：先扫描表，查出有成绩小于80的人的姓名，然后再次扫描表，用not in 或not exists 方法。
点评：此方法采用逆向思维，能快速写出高效且简单的 SQL语句。
//not in
SELECT DISTINCT A.name FROM Student A
WHERE A.name not in(
SELECT Distinct S.name FROM Student S WHERE S.score <80)
//not exists
SELECT DISTINCT A.name From Student A
where not exists (SELECT 1 From Student S Where S.score <80 AND S.name =A.name)
方法三：
SELECT S.name
FROM Student S
GROUP BY S.name
Having MIN(S.score)>=80
6. 删除除了自动编号不同,其他都相同的学生冗余信息
有如下学生表
自动编号 学号 姓名 课程编号 课程名称 分数
1 2005001 张三 0001 数学 69
2 2005002 李四 0001 数学 89
3 2005001 张三 0001 数学 69
要求：删除除了自动编号不同,其他都相同的学生冗余信息
DELETE from score where (name,kecheng,fenshu) in(select name,kecheng,fenshu from score GROUP by name,kecheng,fenshu having count(name,kecheng,fenshu) > 1) ;
7. 使用数据库语句创建学生基本信息表student（s-no、s-name、s-sex、birthday、polity） 和学生成绩表sco（s-no、c-no、score）。针对这两个表，利用select语句实现下列查询。
Student
s-no | s-name | s-sex | birthday | polity |
110110 | 张三 | 男 | 12/2 | |
110112 | 李娟 | 女 | 01/2 | |
110113 | 王浩 | 男 | 03/5 | |
110114 | 余美玲 | 女 | 03/8 |
成绩(sco)
s-no | c-no | score |
110110 | 语文 | 80 |
110110 | 数学 | 79 |
110113 | 语文 | 81 |
110113 | 数学 | 60 |
110112 | 语文 | 59 |
110112 | 数学 | 58 |
（1）所有学生的基本信息，并安学号排序。
（2）所有女生的信息和女生人数。
（3）所有男生的姓名、出生日期和年龄
（4）所有学生姓名、出生日期、年龄、选修的课程和成绩
（5）指定某学生的成绩
（6）不及格学生的姓名
（7）按性别进行分组查询，查询男女生的平均成绩
8. 有如下两张表，学生基本信息表（student）和考试成绩登记表（scope）
Student
学生证号 | 姓名 | 性别 | 年龄 |
1101001 | 张三 | 男 | 15 |
1101002 | 李娟 | 女 | 16 |
1101003 | 王武 | 男 | 15 |
1101004 | 张三 | 男 | 16 |
1101005 | 王兰 | 女 | 15 |
Scope
学生证号 | 语文 | 数学 | 地理 |
1101001 | 78 | 89 | 65 |
1101002 | 84 | 83 | 78 |
1101003 | 76 | 93 | 67 |
1101004 | 73 | 92 | 62 |
1101005 | 75 | 86 | 72 |
(1) 用一个sql语句，查询姓名为“张三”的如下信息：“学生证号/姓名/性别/语文/数学/地理”
(2) 用一个sql语句，查询该班级中语文成绩>数学成绩的学生的如下信息：“学生证号/姓名/性别/语文/数学/地理”
(3) 用一个sql语句，为学号“1101002”的学生语文、数学、地理成绩分别加5分
(4) 用一个sql语句，查询男、女学生的语文平均成绩
(5) 用一个sql语句，查询语文成绩大于语文平均成绩的学生的：“学号/姓名/语文”
9. 现有product库，属性分别为id，name，status，结果如下：
id name status
1 苹果 好吃
2 梨 难吃
3 橘子 好吃
4 葡萄 NULL
现在需要把status的‘好吃’更新为‘0’，‘难吃’更新为‘1’，请使用SQL语句写出符合条件的语句?
10. 现有3个表：
S(学号(SNO)PK，姓名（SNAME)）;
C(编号（CNO）PK，名称（CNAME），教师（CTEACHER））；
SC（学号（SNO）FK，课程编号（CNO）FK）PK，成绩（SCGRADE））；
完成以下查询功能。
1、找出没有报“李明”老师的学生的姓名。
2、列出有2门以上（含2门）课程不及格的学生的姓名和平均成绩。
3、列出学过“1”号课程，并且学过“2”号课程的所有学生的姓名。
4、列出1号课程成绩高于2号课程成绩的学生的姓名。
5、列出1号课程成绩高于2号课程成绩的学生的姓名，1号课程成绩，2号课程成绩和平均成绩。
11. 数据库表如下，写出sql语句求出每个人的平均成绩，格式为【姓名，平均成绩】
tbl_score
id 学生ID 课程名称 成绩
1 1 数学 99
2 2 数学 100
3 1 语文 80
4 2 语文 96
tbl_student
id 姓名 年龄
1 张三 12
2 李四 13
12. 有一个人员信息表user_info(user_id,name,dept_id,amt)、部门信息表dept_info(dept_id,dept_desc)，人员功夫表user_kongfu(user_id,kf_id,main_kf_flag)和功夫信息代码表kf_info(kf_id,kf_desc)表中的数据分别如下：
人员信息表：
用户ID 姓名 部门ID 个人资产(金额)
1001 令狐冲 1 70
1002 任我行 2 0
1003 东方不败 2 528
1004 风清阳 1 10
1005 左冷禅 4 230
1006 冲虚 3 50
1007 岳不群 1 158
1008 天门道人 5 -10
部门信息表：
部门ID 部门名称
1 华山
2 黑木崖
3 武当
4 嵩山
5 泰山
人员功夫表(1是主功夫)：
用户ID 功夫ID 主功夫标志
1001 1 1
1001 2 0
1001 4 0
1002 4 1
1003 3 1
1004 1 1
1005 5 1
1006 7 1
1007 2 0
1007 3 1
1008 6 0
功夫信息代码表：
功夫ID 功夫描述
1 独孤九剑
2 紫霞神功
3 辟邪剑法
4 吸星大法
5 寒冰掌
6 三脚猫
7 太极剑法
其中，五岳剑派为华山、嵩山和泰山。
请给出SQL统计有主功夫没有次功夫的人员名单以及部门名称、功夫描述和个人资产。
13. 求出小于45岁的各个老师所带的大于12岁的学生人数
teacher 表 teaID name age
student 表 stuID name age
teacher_student表 teaID stuID
要求用一条sql查询出这样的结果
1、显示的字段要有老师name, age 每个老师所带的学生人数
2、只列出老师age为40以下，学生age为12以上的记录
查看原文：http://www.coder306.cn/?p=140