所有表都放在最后
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','陕西咸阳','[email protected]',3000.00);
insert into Teacher values('2' , '李四',0,'1992-5-9','陕西宝鸡','[email protected]',4000.00);
insert into Teacher values('3' , '王五',1,'1977-7-1','山西太原','[email protected]',5000.00);
