软件测试笔试——数据库题型

软件测试笔试——数据库题型

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,'张老师');

查询语句：