SQL-0718班考试题目

传智的

create table student(id int,name varchar(20),chinese int,english int,math int,class_id int);

create table myclass(id int primary key, name varchar(20));

insert into student(id,name,chinese,english,math,class_id ) values(1,'何东',80,85,90, 1);
insert into student(id,name,chinese,english,math,class_id ) values(2,'权筝',90,95,95, 2);
insert into student(id,name,chinese,english,math,class_id ) values(3,'何南',80,96,96, 1);
insert into student(id,name,chinese,english,math,class_id ) values(4,'叶坦',81,97,85, 2);
insert into student(id,name,chinese,english,math,class_id ) values(5,'何西',85,84,90, 1);
insert into student(id,name,chinese,english,math,class_id ) values(6,'丁香',92,85,87, 2);
insert into student(id,name,chinese,english,math,class_id ) values(7,'何北',75,81,80, 1);
insert into student(id,name,chinese,english,math,class_id ) values(8,'唐娇',77,80,79, 2);
insert into student(id,name,chinese,english,math,class_id ) values(9,'任知了',95,85,85, 1);
insert into student(id,name,chinese,english,math,class_id ) values(10,'王越',94,85,84, 2);

insert into student(id,name,chinese,english,math,class_id ) values(11,'刘1',63,41,30, 1);
insert into student(id,name,chinese,english,math,class_id ) values(12,'刘2',122,11,77, 2);
insert into student(id,name,chinese,english,math,class_id ) values(13,'刘3',63,41,30, 1);
insert into student(id,name,chinese,english,math,class_id ) values(14,'张4',82,11,77, 2);

insert into student(id,name,chinese,english,math,class_id ) values(15,'张1',63,41,30, 1);
insert into student(id,name,chinese,english,math,class_id ) values(16,'张2',72,71,77, 2);
insert into student(id,name,chinese,english,math,class_id ) values(17,'张3',63,61,30, 1);
insert into student(id,name,chinese,english,math,class_id ) values(18,'张4',67,51,77, 2);

insert into myclass(id,name) values(1, '游戏开发班');
insert into myclass(id,name) values(2, '服务器开发班');
insert into myclass(id,name) values(3, 'dba管理班');

1 SQL语言题目

题目1：以上是mysql脚本，请移植成oracle的数据库脚本   10分
--考察mysql和oracle脚本之区别 
--数据脚本有不对的地方,考察排错能力,自行修改


题目2：求各个班级英语的平均分，并打印班级名称，班级编号 班级的英语平均分 10分
--考察分组和多表查询概念
select myclass.id,myclass.name,t.a 
from myclass,
(select class_id,avg(english) a from student group by class_id) t 
where 
myclass.id=t.class_id;

select c.id,c.name,avg(s.english)  
from myclass c 
inner join 
student s 
on c.id=s.class_id
group by c.id;


题目3: 求各个班级的人数，（注意需要统计3号班级的人数）  10分
--考察多表查询、外连接概念
--显示班级编号，班级名称 班级人数
select  class_id,count(*) from student group by class_id;

-- 显示3号班级0
select myclass.id,myclass.name,count(student.id) 
from myclass 
left join student  
on myclass.id=student.class_id 
group by myclass.id;

--不显示3号班级0
select myclass.id,myclass.name,count(*) 
from myclass  
join student 
group by myclass.id  
where myclass.id=student.class_id ;


题目4：求总分最高的前三名 10分
--考察oracle分页基本概念 考察子查
select student.*,(chinese+english+math) sum 
from student 
order by sum desc 
limit 3;


题目5：求各个班级中，英语成绩，大于本班级英语平均分的同学信息 20分
-- 考察 多表查询 和 相关子查询 
-- 要求 两种实现方法，每一种方法10分
select avg(english) 
from student 
group by class_id;

-- 子查询
select s.class_id,s.id,s.name,s.english,
(select avg(english) from student where class_id=s.class_id) avg
from student s 
where 
s.english > 
(select avg(english) from student where class_id=s.class_id) 
order by 
s.class_id,s.id;

-- 联表查询
select s.class_id,s.id,s.name,s.english,a.avg 
from student s 
inner join
(select class_id,avg(english) AVG from student group by class_id) a
on s.class_id=a.class_id 
and s.english > a.AVG 
order by s.class_id,s.id;

写的一些扩展

扩展：
求各个班级英语分数的前3名学生信息（同分算同一名次）
select s.class_id,s.id,s.name,s.english 
from student s 
where 3 > (select count(distinct s2.english) from student s2 where s.class_id=s2.class_id and s2.english>s.english)
order by 
s.class_id asc,s.english desc; 

扩展：把班级名称打印一起打印出来（联表）
select s.class_id,c.name,s.id,s.name,s.english from student s,myclass c 
where s.class_id=c.id 
and 
3 > (select count(distinct s2.english) from student s2 where s.class_id=s2.class_id and s2.english>s.english)
order by 
s.class_id asc,s.english desc; 


求各个班级英语分数的前4名学生信息（假设没有同分的情况）
select s.class_id,s.id,s.name,s.english 
from student s 
where 4 > (select count(s2.english) from student s2 where s.class_id=s2.class_id and s2.english>s.english)
order by 
s.class_id asc,s.english desc; 

扩展：把班级名称打印一起打印出来（联表）

各个班级学生总成绩排序
select s.*,(s.chinese+s.english+s.math) SUM 
from student s 
order by 
class_id,(s.chinese+s.english+s.math) desc;


求各个班级总分分数的前3名学生信息（同分算同一名次）(s.chinese+s.english+s.math) (s2.chinese+s2.english+s2.math)

select s.class_id,s.id,s.name,(s.chinese+s.english+s.math) SUM 
from student s 
where 3 > (select count(distinct (s2.chinese+s2.english+s2.math)) from student s2 where s.class_id=s2.class_id 
and 
(s2.chinese+s2.english+s2.math)>(s.chinese+s.english+s.math))
order by 
s.class_id asc,(s.chinese+s.english+s.math) desc; 

扩展：把班级名称打印一起打印出来（联表）
select s.class_id,c.name,s.id,s.name,(s.chinese+s.english+s.math) SUM 
from 
student s,myclass c
where 
s.class_id=c.id 
and 
3 > 
(
select count(distinct (s2.chinese+s2.english+s2.math)) 
from student s2 
where s.class_id=s2.class_id 
and 
(s2.chinese+s2.english+s2.math)>(s.chinese+s.english+s.math)
)
order by 
s.class_id asc,(s.chinese+s.english+s.math) desc;