学生选课表练习(面试题)

一、表结构：

学生表：Student（编号sid，姓名sname，生日birthday，性别ssex，班级 classid)

课程表：Course（课程编号cid，课程名称cname，教师编号tid）

成绩表：Sc（学生编号sid，课程编号cid，成绩score）

教师表：Teacher（教师编号tid，姓名tname）

班级表：Class （班级编号 classid，班级名称 classname）

学生表 Student

create table Student(Sid int primary key, Sname varchar(10), birthday datetime, Ssex varchar(10), classid int);

insert into Student values('1' , '赵雷' , '1990-01-01' , '男', '1');

insert into Student values('2' , '钱电' , '1990-12-21' , '男', '2');

insert into Student values('3' , '孙风' , '1990-05-20' , '男', '1');

insert into Student values('4' , '李云' , '1990-08-06' , '男', '2');

insert into Student values('5' , '周梅' , '1991-12-01' , '女', '1');

insert into Student values('6' , '吴兰' , '1992-03-01' , '女', '2');

insert into Student values('7' , '郑竹' , '1989-07-01' , '女', '1');

insert into Student values('8' , '王菊' , '1990-01-20' , '女', '2');

成绩表 SC

create table SC(Sid int, Cid int, score decimal(18,1));

insert into SC values('1' , '1' , 80);

insert into SC values('1' , '2' , 90);

insert into SC values('1' , '3' , 99);

insert into SC values('2' , '1' , 70);

insert into SC values('2' , '2' , 60);

insert into SC values('2' , '3' , 80);

insert into SC values('3' , '1' , 80);

insert into SC values('3' , '2' , 80);

insert into SC values('3' , '3' , 80);

insert into SC values('4' , '1' , 50);

insert into SC values('4' , '2' , 30);

insert into SC values('4' , '3' , 20);

insert into SC values('5' , '1' , 76);

insert into SC values('5' , '2' , 87);

insert into SC values('6' , '1' , 31);

insert into SC values('6' , '3' , 34);

insert into SC values('7' , '2' , 89);

insert into SC values('7' , '3' , 98);

课程表 Course

create table Course(Cid int primary key,Cname varchar(10),Tid varchar(10));

insert into Course values('1' , '语文' , '2');

insert into Course values('2' , '数学' , '1');

insert into Course values('3' , '英语' , '3');

教师表 Teacher

create table Teacher(

       Tid int primary key auto_increment,

       Tname varchar(10),

       Tsex TINYINT default 1,

       Tbirthday date,

       Taddress varchar(255),

       Temail varchar(255),

       Tmoney DECIMAL(20,2)

);

insert into Teacher values('1' , '张三',1,'1988-1-15','陕西咸阳','zhangsan@qq.com',3000.00);

insert into Teacher values('2' , '李四',0,'1992-5-9','陕西宝鸡','lisi@qq.com',4000.00);

insert into Teacher values('3' , '王五',1,'1977-7-1','山西太原','wangwu@qq.com',5000.00);

班级表 Class

create table Class(classid int primary key, classname varchar(20));

insert into Class values('1', '一班');

insert into Class values('2', '二班');

二、面试题：

1.查询” 01 “课程比” 02 “课程成绩高的学生的信息及课程分数

select student.* from student
inner join 
(select * from sc where cid=1)t1
on student.sid=t1.sid
inner join 
(select * from sc where cid=2)t2
on t1.sid=t2.sid
where t1.score>t2.score

 2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

select student.Sid,student.sname,avg(score) from sc,student
where student.Sid=sc.Sid
group by student.sid
having avg(score)>=60;

 3. 查询在 SC 表存在成绩的学生信息

# 方法一
select DISTINCT student.* from student
inner join  sc on student.Sid=sc.Sid

# 方法二
select * from student where sid in(
	select sid from sc
)

 4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

select student.Sid,Sname,count(Cid),sum(score) from student
left join sc on student.Sid=sc.Sid
group by student.Sid

5. 查询「李」姓老师的数量

select count(*) from teacher where tname like '李%'

6. 查询学过「张三」老师授课的同学的信息 

select * from student,class,teacher,course,sc 
where student.classid=class.classid
and student.Sid=sc.Sid and sc.Cid=course.Cid 
and teacher.Tid=course.Tid and teacher.Tname='张三'

 7. 查询没有学全所有课程的同学的信息

select student.* ,count(cid) from student
left join sc on student.Sid=sc.Sid
group by student.Sid
having count(cid) <(select count(*) from course)

8. 查询至少有一门课与学号为” 01 “的同学所学相同的同学的信息

select distinct student.* from student
left join sc on student.Sid=sc.Sid
where cid  in(
	select cid from sc where sid=1
)and student.sid <>1

9. 查询和” 01 “号的同学学习的课程完全相同的其他同学的信息
-- 完全相同
-- 1.范围相同
-- 2.个数相同 

select student.* from student
inner join sc on student.Sid=sc.Sid
where
student.Sid not in(
select sid from sc where cid not in(
 select cid from sc where sid=1  
	)
)
group by student.Sid
having count(cid)=(select count(*) from sc where sid=1)

10. 查询没学过”张三”老师讲授的任一门课程的学生姓名

select student.Sname from student 
where sid NOT IN
(select DISTINCT sc.Sid from sc 
inner join course  on sc.Cid = course.Cid
inner join teacher  on course.Tid = teacher.Tid
where teacher.Tname = '张三');

11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select student.sid, student.sname, AVG(score) from student,sc
where sc.score<60 and student.sid = sc.sid
group by student.Sid having count(cid)>=2

12. 检索” 01 “课程分数小于 60，按分数降序排列的学生信息

select * from student,sc
where student.sid=sc.sid and cid=1 and score<60
order by score desc 

13. 查询各科成绩最高分、最低分和平均分，以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率(及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90）。

select sc.cid,cname,max(score),min(score),avg(score),
	count(case when score >=60 then score end)/count(*) * 100,
	count(case when score >=70 and score <80 then score end)/count(*) * 100,
	count(case when score >=80 and score <90 then score end)/count(*) * 100,
	count(case when score >=90 then score end)/count(*) * 100
from course,sc 
where course.cid = sc.cid
group by course.cid