SQL面试题 

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                                    





            

            
posted @ 
2018-03-01 14:23 
两粒 
阅读(358) 
评论(0) 
编辑 
收藏 
举报