软件测试笔试——数据库题型
1.一个简单的学生成绩表，表名Student，有字符类型的Name项和整型的score项，这两个项都不允许为空。按下面要求写出相应的SQL语句。
a) 求成绩大于60的学生数量。
答：select count(*) from student where score>60;
b）把学生“张三”的成绩由75分改成80分。
答：update student set score=80 where name=’张三’;
2.
student表(sno,sname,sage)--学号，姓名，学生年龄
course表(cno,cname,tno)--课程代码，课程名称，老师工号
sc表(sno,cno,score)--学号，课程代码，分数
teacher表(tno,tname)--老师工号，老师姓名
1.查询'001'课程比'002'课程成绩高的的所有学生学号
1) select t1.sno from sc t1,sc t2 where t1.sno = t2.sno and t1.cno = '001' and t2.cno = '002' and t1.score>t2.score;
2.查询平均成绩大于60分的学生学号和平均成绩
2) select sno,avg(score) from sc group by sno having avg(score)>60;
3.查询所有学生的学号，姓名，选课数，总成绩。
3) select sum(t2.score),count(t2.cno),t1.sno,t1.sname from student t1,sc t2 where t1.sno = t2.sno group by t1.sno,t1.sname;
4.查询姓李老师的个数
4) select count(*) from teacher where tname like '李%';
5.查询没有学过叶平老师课程的学生的姓名和学号
5) select t1.sno,t1.sname from student t1, sc t2,course t3, teacher t4
where t1.sno = t2.sno and t2.cno = t3.cno and t3.tno = t4.tno and
t4.tname!='叶平';
6.查询同时学过001，002号课程的学生学号和姓名
6) select t1.sno,t1.sname from student t1 where exists(select '1' from
sc t2 where t2.sno = t1.sno and t2.cno = '001') and exists (select '1'
from sc t2 where t2.sno = t1.sno and t2.cno = '002');
7.查询学过叶平老师所有课程的学生学号和姓名
7) select sno,sname from student where sno in (select sno from sc where
cno in (select cno from course where tno = (select tno from teacher
where tname='叶平')) group by sno having count(*) = (select count(cno)
from course where tno = (select tno from teacher where tname='叶平')));
8.查询所有课程成绩小于60分的学生姓名和学号
8) select sno,sname from student where sno in(select sno from sc group by sno having max(score)<60);
3.
作者表authors
作者编号authorId int
作者姓名name varchar(50)
性别sex varchar(2)
年龄age int
居住城市city varchar(50)
联系电话telephone varchar(11)
销量sales int
最新出版日期 ISBN datetime
1)查询姓张的作者信息
1）select * from authors where name like '%张%';
2）查询联系电话第三位为8，9并以888结尾的作者信息
2）select * from authors where (SUBSTR(telephone,3,1)='8' or
SUBSTR(telephone,3,1)='9') and
SUBSTR(telephone,length(telephone)-2,3)='888';
3)查询年龄在20-50之间的男性作者信息
3）select * from authors where age>20 and age<50 and sex='男';
select * from authors where age BETWEEN 20 and 50 and sex='男';
4）查询显示作者姓名的第二个字符
4）select SUBSTR(name,2,1) from authors;
5）查询显示作者姓名的长度
5）select length(name) from authors;
6）查询显示最年轻的5为作者的平均销量
6）select avg(sales) from (select sales from authors order by age asc limit 0,5) t;
7）查询显示作者的姓名，出声年份，销量，并按销量降序排列
7）select name,YEAR(CURDATE())-age,sales from authors order by sales desc;
8）查询显示最新出版日期在今年前半年的作者信息
8）select * from authors where MONTH(ibsn)<7;
4.
三个表,表字段如下
学生表：s(sno,sname),sno为学号，sname为姓名。
课程表：c(cno,cname,cteacher),cno为课程号，cname为课程名，cteacher为任课老师的姓名。
选课表：sc(sno,cno,scgrade),scgrade为成绩。
用sql实现下列需求：
1)检索出没有选修过'李明'老师课程的所有同学姓名。
1）select sname from s where sno not in(select DISTINCT sno from sc where cno=(select cno from c where cteacher='李明'));
2)检索出两门以上(含两门)不及格的学生姓名和平均成绩。
2）select sname,avg(scgrade) from s,sc where s.sno = sc.sno and scgrade<60 group by s.sno having count(*)>=2;
3)检索出既学过'1'号课程，又学过'2'号课程的所有学生的姓名。
3）select sname from s,sc where s.sno = sc.sno and cno in(1,2) group by s.sno having count(*)=2;
4)检索出'1'号课程成绩比'2'号课程成绩高的所有学生的学号及其'1'号课和'2'号课的成绩。
4）select t1.sno,t1.scgrade,t2.scgrade from sc t1,sc t2 where t1.sno =
t2.sno and t1.cno = 1 and t2.cno = 2 and t1.scgrade>t2.scgrade;
答：
建表语句：
drop table if EXISTS s;
create table s(
sno int not null PRIMARY key,
sname VARCHAR(10)
)DEFAULT CHARSET=utf8;
drop table if EXISTS c;
create table c(
cno int not null PRIMARY key,
sname VARCHAR(12),
cteacher VARCHAR(20)
)DEFAULT charset=utf8;
drop table if EXISTS sc;
create table sc(
sno int,
cno int,
scgrade int
)default charset=utf8;
插入测试数据：
delete from s;
delete from c;
delete from sc;
insert into s VALUES(16001,'张三'),(16002,'李四'),(16003,'王五');
insert into c values(1,'语文','老张'),(2,'数学','张少天'),(3,'外语','李明');
insert into sc values(16001,1,55),(16001,2,40),(16002,1,51),(16002,3,80),(16003,1,40),(16003,2,55),(16003,3,50);
5.
Student(Sid,Sname,Sage,Ssex) 学生表
Course(Cid,Cname,Tid) 课程表
SC(Sid,Cid,score) 成绩表
Teacher(Tid,Tname) 教师表
（1）查询所有课程成绩小于60分的同学学号、姓名；
1）select sid,sname from student where sid not (select distinct sid from sc where score>=60);
（2）查询和16001号的同学学习的课程完全相同的其他同学学号和姓名；
2）SELECT t1.sid,t1.sname FROM student t1 WHERE EXISTS (SELECT * FROM sc
t2 WHERE t1.sid = t2.sid AND t2.cid IN (SELECT DISTINCT cid FROM sc
WHERE sid = 16001)) AND NOT EXISTS (SELECT * FROM sc t2 WHERE t1.sid =
t2.sid AND t2.cid NOT IN (SELECT DISTINCT cid FROM sc WHERE sid =
16001)) AND t1.sid != 16001;
（3）查询各科成绩最高和最低的分：以如下形式显示：课程ID、最高分、最低分。
3）select cid,max(score),min(score) from sc group by cid;
建表语句以及初始化数据：
drop table if EXISTS student;
create table student(
sid int not null PRIMARY key,
sname VARCHAR(10),
sage int,
ssex VARCHAR(10)
)DEFAULT CHARSET=utf8;
drop table if EXISTS course;
create table course(
cid int not null PRIMARY key,
cname VARCHAR(12),
tid int not null
)DEFAULT charset=utf8;
drop table if EXISTS sc;
create table sc(
sid int,
cid int,
score int
)default charset=utf8;
drop table if EXISTS teacher;
create table teacher(
tid int,
tname VARCHAR(10)
)default charset=utf8;
delete from student;
delete from course;
delete from sc;
delete from teacher;
insert into student VALUES(16001,'张三',21,'男'),(16002,'李四',21,'男'),(16003,'王五',21,'男');
insert into course values(1,'语文',1001),(2,'数学',1002),(3,'外语',1003);
insert into sc values(16001,1,55),(16001,2,40),(16002,1,51),(16002,3,80),(16003,1,40),(16003,2,55),(16003,3,50);
insert into teacher values(1001,'李老师'),(1002,'徐老师'),(3,'张老师');
查询语句：
