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

所有表都放在最后

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

SELECT DISTINCT student.*,t1.score FROM student
INNER JOIN 
(SELECT * FROM sc WHERE sc.Cid = 1) t1 
ON t1.Sid = student.Sid
INNER JOIN 
(SELECT * FROM sc WHERE sc.Cid = 2) t2 
ON t2.Sid = t1.Sid
WHERE t1.score > t2.score

查询结果：

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

SELECT student.Sid,student.Sname,AVG(score) FROM student,sc
WHERE student.Sid = sc.Sid
GROUP BY Sid
HAVING AVG(score)>=60

查询结果：

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

-- 方法一
SELECT * FROM sc WHERE sid IN(
SELECT sid FROM sc
)
-- 方法二
SELECT DISTINCT student.* FROM student
INNER JOIN sc 
ON sc.Sid = student.Sid

执行结果：

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

SELECT student.Sid,student.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 teacher.Tname LIKE '李%'

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

SELECT DISTINCT student.* FROM student,teacher,course,sc,class
WHERE student.Sid = sc.Sid
AND sc.Cid = course.Cid
AND course.Tid = teacher.Tid
AND student.classid = class.classid
AND teacher.Tname = '张三'

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

SELECT student.* 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 sc.Sid = student.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.Cid 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,sname,AVG(score) FROM sc,student
WHERE score < 60 AND sc.Sid = student.Sid
GROUP BY sid HAVING COUNT(cid) >= 2

查询结果： 

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

SELECT DISTINCT * 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,course.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

查询结果：

---

---

class表：

班级表 Class

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

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

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

course表：

课程表 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');

sc表：

成绩表 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)

student表：

学生表 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');

teacher表：

教师表 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);