SQL笔试题

====== MySQL数据库 ======

已知表:

1，学生表student

2，课程表course

3，成绩表score

创建表语句：

create table student(
sno int not null auto_increment,
sname varchar(20),
sage int(3),
ssex char(1),
primary key(sno)
);

create table course(
cno int not null auto_increment,
cname varchar(20),
primary key(cno)
);

create table score(
sno int,
cno int,
score int(3),
primary key(sno,cno)
);

题1：取得 课程1 成绩大于 课程2 的学生的 姓名，课程名，课程编号，分数

select sname,c.cno,cname,score from student s,course c,score sc
where s.sno in(
select a.sno from score a, score b 
where a.sno=b.sno and a.cno=1 and b.cno=2 and a.score>b.score)
and c.cno in(1,2) and sc.sno=s.sno and sc.cno=c.cno

order by s.sname;

题2：取得平均成绩大于 90 的学生 姓名，平均成绩

select sname,avg_score from student s,
(select sno,avg(score) as avg_score from score sc group by sno having avg_score > 90) t

where s.sno=t.sno;

题3：取各科成绩的前三名（不考虑成绩并列） 学生姓名，课程名，成绩

select sname,cname,score from 
(select  sno,cno,score from score sc1       
where  (select count(1) from score sc2 where sc2.cno=sc1.cno and sc2.score >= sc1.score) <=3
order by cno,score desc) t inner join student s on t.sno=s.sno inner join course c on t.cno=c.cno

order by t.cno,t.score desc;