测试派 Give testers a home.

很多同学会趁着年底尝试找找工作，因为年底的岗位招聘一般来说都是急招，应聘的竞争者少，且薪资不低。特别是工作年限4年以下的宝子，面试前一般会先笔试，如果不想自己笔试受挫，那么以下给大家分享的这一波近期vip学员面试碰到的数据库笔试题，一定要牢牢码住！并多多练习！

1、物料表、物料生效通知表的数据库操作题

表1 S1#(item_code,item_name, creater,create_tine, i tem_ver, ca_id; 物料表(物料号，物料名称，创建人，创建时间、物料版本，通知单号) (key: item_code + ca_id)

表2 S2#(ca_id, ca_descri, ca_valid_time); 物料生效通知单表(通知单号，通知单说明，生效时间) (key: ca_id)

问题1、写出liuzz创建的所有物料信息

SELECT * FROM S1#，S#2 WHERE S1#.ca_id = S2#.ca_id AND creater = 'liuzz';

问题2，查询通知单编号为CA001所生效的物料，物料创建人以及物料的版本信息

SELECT S1#.item_code, S1#.creater, S1#.item_ver FROM S1#, S2# WHERE S1#.ca_id = S2#.ca_id AND S2#.ca_id = 'CA001';

问题3，更新物料编号为S001的创建人为LIUQQ

UPDATE S1# SET creater = 'LIUQQ' WHERE item_code = 'S001';

问题4，统计出在2014/05/04后通知单生效的所有物料个数（要求去掉重复物料）

SELECT COUNT(DISTINCT S1#.item_code) FROM S1#, S2# WHERE S1#.ca_id = S2#.ca_id AND S2#.ca_valid_time > '2014/05/04;

2、库存、出库关系的数据库题

1）分别写出AAA的建表语句；

答案：

CREATE TABLE AAA (
Mc种类 VARCHAR(50) NOT NULL,
S1库存总量 INT NOT NULL
);

2）用一条SQL语句求出A出库的数量是多少？

答案：

SELECT sum( S1出库数量 ) AS 出库总量 FROM BBB WHERE Mc种类 = 'A';

3）用一条SQL语句求出A,B各剩下多少库存？

答案：

SELECT AAA.Mc种类, S1库存总量 - COALESCE(SUM(BBB.S1出库数量), 0) AS 剩余库存量

FROM AAA

LEFT JOIN BBB ON AAA.Mc种类 = BBB.Mc种类

WHERE AAA.Mc种类 IN ('A', 'B')

GROUP BY AAA.Mc种类, S1库存总量;

3、学生表、班级信息表、学生班级表的数据库操作

1）select * from student where score > 80 or score in (60,61,62);

2）select class.c_name, count(*), avg(score) from student,class,student_class where student.s_id = student_class.s_id and student_class.c_id = class.c_id and student.gender = '女' group by c_id；

4、stock_rating表操作

参考答案：

SELECT stockId, rating, ratingDate

FROM stock_rating

WHERE stockId IN ('600036', '002146')

AND ratingDate = (SELECT MAX(ratingDate) FROM stock_rating WHERE stockId IN ('600036', '002146'))

5、客户表、商品表、商家表、购买记录表的数据操作

I.客户表customer(CId,Cname,CDate,Ctype)--CId 客户编号,Cname 客户名称,Cdate创建日期,Ctype客户类别

II.商品表good(GId,Gname,SId)--GId商品编号Gname 商品名称,SId商家编号

III.商家表seller (SId,Sname)--SId 商家编号,Sname 商家名称

IV.购买记录表order(CId, GId,num)--CId 客户编号,GId 货物编号,num购买件数

现需按照如下要求编写出对应的SQL语句：

1. 查询买过「xx商家」（Sname=’xx商家’）商品的用户信息
2. 查询购买两种及以上商品，且每种类别购买数都少于100件的客户姓名、客户编号，和客户的平均购买件数
3. 查询同时购买01,02商品且购买" 01"商品比" 02 "商品件数多的用户信息和购买的件数

参考答案：

1、

SELECT c.CId, c.Cname

FROM customer c

INNER JOIN order o ON c.CId = o.CId

INNER JOIN good g ON o.GId = g.GId

INNER JOIN seller s ON g.SId = s.SId

WHERE s.Sname = 'xx商家';

2、

SELECT c.CId, c.Cname, AVG(o.num) AS average_purchases

FROM customer c

INNER JOIN order o ON c.CId = o.CId

GROUP BY c.CId, c.Cname

HAVING COUNT(DISTINCT o.GId) >= 2 AND MAX(o.num) < 100;

3、

SELECT c.CId, c.Cname, o1.num AS num_01, o2.num AS num_02

FROM customer c

INNER JOIN order o1 ON c.CId = o1.CId AND o1.GId = '01'

INNER JOIN order o2 ON c.CId = o2.CId AND o2.GId = '02'

WHERE o1.num > o2.num;

6、学生表、课程表、学生课程关联表的数据操作

参考答案：

1、查询每个学生所选择的课程SELECT

答案：

student.sname, course.cname FROM student,stu_course,course where student.sid = stu_course.sid and stu_course.cid = course.cid;

2、选择了所有课程的学生

答案：

SELECT s.sid, s.sname

FROM student s

WHERE NOT EXISTS (

SELECT c.cid FROM course c

WHERE NOT EXISTS (

SELECT sc.sid FROM stu_course sc

WHERE sc.sid = s.sid AND sc.cid = c.cid));

7、base_user表、account表的数据操作

有如下两张数据表：

表一：base_user

表二：account

请用一条sql查询语句，查询出以下结果：

参考答案：

select * from base_user t1 left join account as t2 on t1.id = t2.id;

8、管理培训信息系统的表数据操作

为管理培训信息，建立3个表

1、S(Sno,SName,Dept,Age)Sno,SNameDept,Age分别代表学号，学员姓名，所属单位，学员年龄

2、C(Cno,CName)Cno,CName分别代表课程编号，课程名称

3、SC(Sno,Cno,Score)Sno,Cno,G分别代表学号，所选的课程编号，学习成绩

问题：

(1)查询选修课程名称为”科研管理”的学员学号和姓名?

(2)查询没选修课程编号为”C5”的学员姓名和所属单位?

(3)列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

(4)查询选修了课程的学员人数

(5)查询选修课程超过5门的学员学号和所属单位?

参考答案：

1）select S.Sno,S.SName from C,S,SC where S.Sno=SC.Sno and SC.Cno=C.Cno and C.CName="科研管理";

2）select S.SName,S.Dept from S,SC where S.Sno=SC.Sno and SC.Sno <> "C5";

3）select S.SNname,avg(SC.Score) from S, SC where S.Sno=SC.Sno and SC.Score<60 group by S.SName having count(distinct SC.Cno)>=2;

4）select 学员人数=count(distinct Sno) from SC;

5）select Sno,Dept from S where Sno in(select Sno from SC group by Sno having count(*)>5);

9、学生成绩信息表的数据操作

1、按分数倒序排列显示出姓名、科目、分数

答案：Select 姓名,科目,分数 from 表A order by 分数 desc

2、查询出语文分数大于70分的人数

答案：Select count(1) from 表A where 科目=’语文’ and 分数>70

3、查询出数学最高分的姓名、分数

答案：Select 姓名,分数 from 表A where 科目=’数学’ order by 分数 desc limit 1

4、查询出所有科目评价为良好的男生姓名、科目、分数

答案：Select 姓名,科目,分数 from 表A where 性别=’男’ and 分数>=71 and 分数<=85

5、一条查询中分别输出男女生的数学最低分

答案：Select min(分数),性别 from A where 科目=’数学’ group by 性别

6、一条查询中输出语文和数学分数前两名的姓名、科目、分数

Select 姓名,科目,分数 from A where 科目=’语文’ order by 分数 desc limit 2

Union all

Select 姓名,科目,分数 from A where 科目=’数学’ order by 分数 desc limit 2

7、将所有男生的性别修改成01

update 表A set 性别=01 where 性别=“男”

8、根据分数上下限设置表A的是否及格列（优秀、良好、及格都表示及格）以及评价列

Update A a inner join B b on a.分数>=b.分数下限 and a.分数<=b.分数上限 set 评价

10、学生成绩表操作

MySQL数据库中有一个学生成绩表student ,一共有name, score两个字段，分别表示学生的姓名和分数。请查询出分数排名前60%的学生名单及其对应的分数。

参考答案：

select * from (

SELECT A.*, @row_num:=@row_num+1 AS ROW_NUM

FROM (select name,score from student1 order by score desc) A, (SELECT @row_num:=0) B

) C where C.ROW_NUM<=(@row_num*0.6)

学无止境，行以致远。