编辑代码

create table Student(S varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' ,'男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
create table Course(C varchar(10),Cname nvarchar(10),T varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(T varchar(10),Tname nvarchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(S varchar(10),C varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);




# 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
## 法1：
SELECT s.S, s.Sname, c.C, c.Cname, sc.score as score01, sc2.score as score02
FROM Student s, Course c, SC sc, SC sc2
WHERE s.S = sc.S AND c.C = sc.C AND sc.score > sc2.score 
AND sc2.S = s.S AND sc2.C = '02' AND sc.C = '01';

## 法2：
SELECT S.S, S.Sname, SC.score 
FROM Student S, SC 
WHERE S.S=SC.S AND SC.C='01' AND SC.score > (SELECT score FROM SC WHERE S.S=SC.S AND C='02');

## 法3：
SELECT stu.*, sc0.score
FROM student stu
INNER JOIN (
	SELECT sc1.* FROM sc sc1
	INNER JOIN sc sc2 ON sc1.S = sc2.S
	AND sc1.c = 01
	AND sc2.c = 02
	WHERE
		sc1.score > sc2.score
) sc0 ON stu.S = sc0.S;


# 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
## 法1：
SELECT s.S, s.Sname, c.C, c.Cname, sc.score as score01, sc2.score as score02
FROM Student s, Course c, SC sc, SC sc2
WHERE s.S = sc.S AND c.C = sc.C AND sc.score < sc2.score 
AND sc2.S = s.S AND sc2.C = '02' AND sc.C = '01';

## 法2：
SELECT S.S, S.Sname, SC.score 
FROM Student S, SC 
WHERE S.S=SC.S AND SC.C='01' AND SC.score < (SELECT score FROM SC WHERE S.S=SC.S AND C='02');

## 法3：
SELECT stu.*, sc0.score
FROM student stu
INNER JOIN (
	SELECT sc1.* FROM sc sc1
	INNER JOIN sc sc2 ON sc1.S = sc2.S
	AND sc1.c = 01
	AND sc2.c = 02
	WHERE
		sc1.score < sc2.score
) sc0 ON stu.S = sc0.S;


#3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
## 法1：
SELECT SC.S, S.Sname, AVG(SC.score) AS avg_score 
FROM Student S, SC 
WHERE S.S=SC.S 
GROUP BY SC.S, S.Sname 
HAVING AVG(SC.score)>=60;

## 法2：
SELECT stu.S, stu.Sname, sc.avgsc
FROM student stu
INNER JOIN (
	SELECT sc.S, AVG(score) avgsc
	FROM sc
	GROUP BY S
	HAVING avgsc >= 60
) sc ON stu.S = sc.S;


#4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
## 法1：
SELECT SC.S, S.Sname, AVG(SC.score) AS avg_score 
FROM Student S, SC 
WHERE S.S=SC.S 
GROUP BY SC.S, S.Sname 
HAVING AVG(SC.score)<60;

## 法2：
SELECT stu.S, stu.Sname, sc.avgsc
FROM student stu
INNER JOIN (
	SELECT sc.S, AVG(score) avgsc
	FROM sc
	GROUP BY S
	HAVING avgsc < 60
) sc ON stu.S = sc.S;

# 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
## 法1：
SELECT SC.S, S.Sname, COUNT(*) AS total_courses, SUM(SC.score) AS total_score 
FROM Student S, SC 
WHERE S.S=SC.S 
GROUP BY SC.S, S.Sname;

## 法2：
SELECT stu.S, stu.Sname, COUNT(sc.c), IFNULL(SUM(sc.score), 0) sumsc
FROM student stu
LEFT JOIN sc sc ON stu.S = sc.S
GROUP BY stu.S, stu.Sname;


# 6、查询"李"姓老师的数量 
## 法1：
SELECT COUNT(*) AS "李姓老师的数量"
FROM teacher
WHERE Tname LIKE '李%';


#7、查询学过"张三"老师授课的同学的信息 
## 法1：
SELECT DISTINCT S.S, S.Sname, course.cname
FROM Student S, SC, Course ,teacher T
WHERE S.S=SC.S AND SC.C=Course.C AND T.T=Course.T AND T.Tname='张三';

## 法2：
SELECT student.*
FROM student
INNER JOIN sc s ON student.S = s.S
INNER JOIN (
	SELECT teacher.T, c
	FROM teacher
	LEFT JOIN course c ON teacher.T = c.T
	WHERE Tname = '张三'
) t ON s.c = t.c;


# 8、查询没学过"张三"老师授课的同学的信息 
## 法1：
SELECT *
FROM Student S 
WHERE NOT EXISTS (
    SELECT sc.s,sc.c  FROM SC, Course ,teacher T
    WHERE S.S=SC.S AND SC.C=Course.C AND T.T=Course.T AND T.Tname='张三'
);

## 法2：
SELECT *
FROM Student S 
WHERE s.s not in(
    SELECT sc.s FROM SC, Course ,teacher T
    WHERE S.S=SC.S AND SC.C=Course.C AND T.T=Course.T AND T.Tname='张三'
);

## 法3：
SELECT stu.*
FROM student stu
WHERE stu.S NOT IN (
	SELECT student.S
	FROM student
	INNER JOIN sc s ON student.S = s.S
	INNER JOIN (
		SELECT teacher.T, c
		FROM teacher
		LEFT JOIN course c ON teacher.T = c.T
		WHERE Tname = '张三'
	) t ON s.c = t.c
);

# 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
## 法1：
SELECT S.S, S.Sname 
FROM Student S, SC SC1, SC SC2 
WHERE S.S=SC1.S AND S.S=SC2.S AND SC1.C='01' AND SC2.C='02';

## 法2：
SELECT s.*
FROM sc sc1
INNER JOIN sc sc2 ON sc1.S = sc2.S
AND sc1.c = 01
AND sc2.c = 02
LEFT JOIN student s ON sc1.S = s.S;


# 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
## 法1：
SELECT S.S, S.Sname 
FROM Student S, SC SC1 
WHERE S.S=SC1.S AND SC1.C='01' AND NOT EXISTS (
    SELECT * FROM SC SC2 WHERE S.S=SC2.S AND SC2.C='02'
);

## 法2：
SELECT *
FROM student
WHERE S IN (SELECT S FROM sc WHERE sc.c = 01)
AND S NOT IN (SELECT S FROM sc WHERE sc.c = 02);

#-- 11、查询没有学全所有课程的同学的信息 
## 法1：
SELECT S, Sname 
FROM Student 
WHERE S NOT IN (
    SELECT S FROM SC GROUP BY S HAVING COUNT(DISTINCT C) = (SELECT COUNT(*) FROM Course)
);

## 法2：
SELECT stu.*, COUNT(sc.c) stucount
FROM sc sc
RIGHT JOIN student stu ON sc.S = stu.S
GROUP BY stu.S, Sname, Sage, Ssex
HAVING stucount < (SELECT COUNT(course.c) FROM course);


#-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息 
## 法1：
SELECT DISTINCT s2.S, s2.Sname 
FROM Student AS s1, Student AS s2, SC AS sc1, SC AS sc2 
WHERE s1.S = '01' AND s1.S = sc1.S AND s2.S = sc2.S AND sc1.C = sc2.C AND s2.S <> '01';

## 法2：
SELECT DISTINCT stu.*
FROM student stu
LEFT JOIN sc sc ON stu.S = sc.S
WHERE sc.c IN (SELECT sc1.c FROM sc sc1 WHERE sc1.S = 1);

#-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 
## 法1：
SELECT s.S, s.Sname
FROM Student AS s
WHERE NOT EXISTS (
    SELECT c.C FROM Course AS c
    WHERE NOT EXISTS (
        SELECT * FROM SC AS sc
        WHERE s.S = sc.S AND c.C = sc.C
    )
) AND s.S <> '01' AND EXISTS (
    SELECT * FROM SC WHERE S = '01'
);

## 法2：
SELECT *
FROM student
INNER JOIN (
	SELECT sc1.S
	FROM sc sc1
	INNER JOIN (SELECT c FROM sc WHERE S = 1) s1 ON sc1.c = s1.c
	WHERE S != 1
	GROUP BY sc1.S
	HAVING COUNT(sc1.c) = (SELECT COUNT(sc.c) FROM sc WHERE S = 1)
) t ON student.S = t.S;


#-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名 
## 法1：
SELECT DISTINCT Sname
FROM Student
WHERE S NOT IN (
    SELECT S FROM SC, Course, Teacher 
    WHERE SC.C = Course.C AND Course.T = Teacher.T AND Tname = '张三'
);

## 法2：
SELECT s.*
FROM student s
WHERE s.S NOT IN (
	SELECT stu.S
	FROM student stu
	LEFT JOIN sc sc ON stu.S = sc.S
	LEFT JOIN course c ON sc.c = c.c
	LEFT JOIN teacher t ON c.T = t.T
	WHERE t.Tname = '张三'
);


#-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 
## 法1：
SELECT sc.S, s.Sname, AVG(sc.score) AS avg_score
FROM SC AS sc, Student AS s
WHERE sc.S = s.S AND sc.score < 60
GROUP BY sc.S, s.Sname
HAVING COUNT(*) >= 2;

## 法2：
SELECT stu.S, stu.Sname, AVG(sc.score)
FROM student stu
LEFT JOIN sc sc ON stu.S = sc.S
WHERE sc.score < 60
GROUP BY stu.S, stu.Sname
HAVING COUNT(sc.c) >= 2;

#-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息
## 法1：
SELECT sc.S, s.Sname, sc.score
FROM SC AS sc, Student AS s
WHERE sc.C = '01' AND sc.S = s.S AND sc.score < 60
ORDER BY sc.score DESC;

## 法2：
SELECT *
FROM student stu
LEFT JOIN sc sc ON stu.S = sc.S
WHERE sc.score < 60 AND sc.c = 1
ORDER BY sc.score DESC;

#-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
## 法1：
SELECT s.*, c.Cname, sc.score, (SELECT AVG(score) FROM SC WHERE S = s.S) AS avg_score
FROM Student AS s
JOIN SC ON s.S = SC.S
JOIN Course AS c ON SC.C = c.C
ORDER BY avg_score DESC;

## 法2：
SELECT stu.Sname AS "学生姓名",
	SUM(CASE sc.c WHEN 1 THEN sc.score ELSE 0 END) AS "语文",
	SUM(CASE sc.c WHEN 2 THEN sc.score ELSE 0 END) AS "数学",
	SUM(CASE sc.c WHEN 3 THEN sc.score ELSE 0 END) AS "英语",
	IFNULL(AVG(sc.score), 0) AS "平均成绩"
FROM student stu
LEFT JOIN sc sc ON stu.S = sc.S
GROUP BY stu.Sname
ORDER BY "平均成绩" DESC;

#-- 18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
## 法1：
SELECT 
    Course.C, Course.Cname,
    MAX(SC.score) AS max_score,
    MIN(SC.score) AS min_score,
    AVG(SC.score) AS avg_score,
    AVG(CASE WHEN SC.score >= 60 THEN 1 ELSE 0 END) AS pass_rate,
    AVG(CASE WHEN SC.score BETWEEN 80 AND 89 THEN 1 ELSE 0 END) AS medium_rate,
    AVG(CASE WHEN SC.score BETWEEN 90 AND 99 THEN 1 ELSE 0 END) AS good_rate,
    AVG(CASE WHEN SC.score = 100 THEN 1 ELSE 0 END) AS excellent_rate
FROM Course, SC
WHERE Course.C = SC.C
GROUP BY Course.C, Course.Cname;

## 法2：
SELECT c.c AS "课程ID",
	Cname AS "课程name",
	MAX(sc.score) AS "最高分",
	MIN(sc.score) AS "最低分",
	AVG(sc.score) AS "平均分",
	SUM(CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END) / COUNT(sc.S) AS "及格率",
	SUM(CASE WHEN sc.score BETWEEN 70 AND 80 THEN 1 ELSE 0 END) / COUNT(sc.S) AS "中等率",
	SUM(CASE WHEN sc.score BETWEEN 80 AND 90 THEN 1 ELSE 0 END) / COUNT(sc.S) AS "优良率",
	SUM(CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END) / COUNT(sc.S) AS "优秀率"
FROM sc sc
LEFT JOIN course c ON sc.c = c.c
GROUP BY c.c, Cname;

#-- 19、按各科成绩进行排序，并显示排名
## 法1：
select res.c,res.s,res.score,res.rank
from (SELECT C, S, score,
CASE 
	WHEN @prev_course = c THEN 
		CASE 
			WHEN @prev_score = score THEN 
				@rank := @rank + 1
      ELSE 
				@rank := @rank + 1
		END 
    ELSE @rank := 1 
END AS rank,
    @prev_course := c, 
    @prev_score := score
FROM SC,(SELECT @rank := 0, @prev_course := NULL, @prev_score := NULL,@big := 1) init
ORDER BY C,score desc) as res;


#-- 20、查询学生的总成绩并进行排名
## 法1：
SELECT s.*,@rank := @rank + 1 AS rank
from(SELECT s.S, s.Sname, SUM(sc.score) AS total_score
FROM Student AS s, SC AS sc
WHERE s.S = sc.S
GROUP BY s.S
ORDER BY total_score DESC) as s,(SELECT @rank := 0) init;


#-- 21、查询不同老师所教不同课程平均分从高到低显示 
## 法1：
SELECT T.Tname, C.Cname, AVG(SC.score) AS avg_score
FROM Teacher T, Course C, SC
WHERE T.T = C.T AND C.C = SC.C
GROUP BY T.Tname, C.Cname
ORDER BY AVG(SC.score) DESC;

## 法2：
SELECT
	t.Tname,
	c.Cname,
	avg(sc.score) avgsc
FROM
	sc sc
LEFT JOIN course c ON sc.c = c.c
LEFT JOIN teacher t ON c.T = t.T
GROUP BY
	t.Tname,
	c.Cname
ORDER BY
	avgsc DESC;


#-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
## 法1：
select res1.c,st.s,st.sname,res1.rank from (select res.c,res.s,res.score,res.rank
from (SELECT C, S, score,
CASE 
	WHEN @prev_course = c THEN 
		CASE 
			WHEN @prev_score = score THEN 
				@rank := @rank + 1
      ELSE 
				@rank := @rank + 1
		END 
    ELSE @rank := 1 
END AS rank,
    @prev_course := c, 
    @prev_score := score
FROM SC,(SELECT @rank := 0, @prev_course := NULL, @prev_score := NULL,@big := 1) init
ORDER BY C,score desc) as res) as res1
left join student st on res1.s = st.s
where res1.rank = 2 or res1.rank = 3
order by res1.c,res1.rank;

## 法2：
(
	SELECT
		stu.*, sc.c,
		sc.score
	FROM
		student stu
	LEFT JOIN sc sc ON stu.S = sc.S
	WHERE
		c = 1
	ORDER BY
		sc.score DESC
	LIMIT 2
)
UNION
	(
		SELECT
			stu.*, sc.c,
			sc.score
		FROM
			student stu
		LEFT JOIN sc sc ON stu.S = sc.S
		WHERE
			c = 2
		ORDER BY
			sc.score DESC
		LIMIT 2
	)
UNION
	(
		SELECT
			stu.*, sc.c,
			sc.score
		FROM
			student stu
		LEFT JOIN sc sc ON stu.S = sc.S
		WHERE
			c = 3
		ORDER BY
			sc.score DESC
		LIMIT 2
	);




#-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比 
## 法1：
SELECT SC.C, C.Cname,
       SUM(CASE WHEN SC.score>=85 AND SC.score<=100 THEN 1 ELSE 0 END) AS '[100-85]',
       SUM(CASE WHEN SC.score>=70 AND SC.score<85 THEN 1 ELSE 0 END) AS '[85-70]',
       SUM(CASE WHEN SC.score>=60 AND SC.score<70 THEN 1 ELSE 0 END) AS '[70-60]',
       SUM(CASE WHEN SC.score>=0 AND SC.score<60 THEN 1 ELSE 0 END) AS '[0-60]',
       SUM(CASE WHEN SC.score>=85 AND SC.score<=100 THEN 1 ELSE 0 END)*100.0/COUNT(sc.s) AS '  [100-85%]',
       SUM(CASE WHEN SC.score>=70 AND SC.score<85 THEN 1 ELSE 0 END)*100.0/COUNT(*) AS '[85-70%]',
       SUM(CASE WHEN SC.score>=60 AND SC.score<70 THEN 1 ELSE 0 END) AS '[70-60%]',
       SUM(CASE WHEN SC.score>=0 AND SC.score<60 THEN 1 ELSE 0 END)*100.0/COUNT(*) AS '[0-60%]'
FROM SC, Course C
WHERE SC.C = C.C
GROUP BY SC.C, C.Cname;


#-- 24、查询学生平均成绩及其名次 
## 法1：
SELECT s.*,@rank := @rank + 1 AS rank
from(SELECT s.S, s.Sname, SUM(sc.score)/count(sc.score) AS avg_score
FROM Student AS s, SC AS sc
WHERE s.S = sc.S
GROUP BY s.S
ORDER BY avg_score DESC) as s,(SELECT @rank := 0) init;


#-- 25、查询各科成绩前三名的记录
## 法1：
select res1.c,st.s,st.sname,res1.rank from (select res.c,res.s,res.score,res.rank
from (SELECT C, S, score,
CASE 
	WHEN @prev_course = c THEN 
		CASE 
			WHEN @prev_score = score THEN 
				@rank := @rank + 1
      ELSE 
				@rank := @rank + 1
		END 
    ELSE @rank := 1 
END AS rank,
    @prev_course := c, 
    @prev_score := score
FROM SC,(SELECT @rank := 0, @prev_course := NULL, @prev_score := NULL,@big := 1) init
ORDER BY C,score desc) as res) as res1
left join student st on res1.s = st.s
where res1.rank = 1 or res1.rank = 2 or res1.rank = 3
order by res1.c,res1.rank;

## 法2：
(select sc.S,stu.Sname,c.Cname,sc.score from sc sc
    left join student stu on sc.S = stu.S
    left join course c on sc.c = c.c where c.c = 1
    group by sc.S, stu.Sname, c.Cname, sc.score order by sc.score desc limit 3)
union (select sc.S,stu.Sname,c.Cname,sc.score from sc sc
    left join student stu on sc.S = stu.S
    left join course c on sc.c = c.c where c.c = 2
    group by sc.S, stu.Sname, c.Cname, sc.score order by sc.score desc limit 3)
union (select sc.S,stu.Sname,c.Cname,sc.score from sc sc
    left join student stu on sc.S = stu.S
    left join course c on sc.c = c.c where c.c = 3
    group by sc.S, stu.Sname, c.Cname, sc.score order by sc.score desc limit 3);

#-- 26、查询每门课程被选修的学生数 
## 法1：
SELECT SC.C, C.Cname, COUNT(DISTINCT SC.S) AS num_of_students
FROM SC, Course C
WHERE SC.C = C.C
GROUP BY SC.C, C.Cname;

## 法2：
select c.Cname,count(sc.c) 学生数 from sc sc
    left join course c on sc.c = c.c group by c.Cname;


#-- 27、查询出只有两门课程的全部学生的学号和姓名 
## 法1：
SELECT S.S, S.Sname
FROM SC, Student S
WHERE SC.S = S.S
GROUP BY S.S, S.Sname
HAVING COUNT(*) = 2;

## 法2：
select stu.S,stu.Sname from sc sc
    left join student stu on sc.S = stu.S 
group by stu.S, stu.Sname having count(sc.c) = 2;



#-- 28、查询男生、女生人数 
## 法1：
SELECT S.Ssex, COUNT(*) AS num_of_students
FROM Student S
GROUP BY S.Ssex;

## 法2：
select Ssex,count(*) 人数 from student group by Ssex;


#-- 29、查询名字中含有"风"字的学生信息
## 法1：
SELECT *
FROM Student
WHERE Sname LIKE '%风%';


#-- 30、查询同名同性学生名单，并统计同名人数 
## 法1：
SELECT Sname, Ssex, COUNT(*) AS num_of_students
FROM Student
GROUP BY Sname, Ssex
HAVING COUNT(*) > 1;

## 法2：
select count(*) 同名同性学生人数 from student stu1
    join student stu2 on stu1.S=stu2.S and stu1.Ssex = stu2.Ssex
    and stu1.S != stu2.S;

#-- 31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime) 
## 法1：
SELECT S, Sname FROM Student WHERE YEAR(Sage) = 1990;

## 法2：
select * from student where Sage like '%1990%';


#-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 
## 法1：
SELECT sc.C, Cname, AVG(score) AS avg_score FROM SC, Course
WHERE SC.C = Course.C GROUP BY sc.C, Cname ORDER BY avg_score DESC, sc.C ASC;

## 法2：
select sc.c,c.Cname, avg(sc.score) avgsc from sc sc
left join course c on sc.c = c.c
group by sc.c,c.Cname order by avgsc desc ,sc.c asc;

#-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 
## 法1：
SELECT sc.S, Sname, AVG(score) AS avg_score FROM SC, Student
WHERE SC.S = Student.S GROUP BY S, Sname HAVING AVG(score) >= 85;

## 法2：
select stu.S, Sname, avg(sc.score) avgsc from student stu
    left join sc sc on stu.S = sc.S
    group by stu.S, Sname having avgsc >=85;

#-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数 
## 法1：
SELECT Sname, score FROM SC, Student, Course
WHERE SC.S = Student.S AND SC.C = Course.C AND Course.Cname = '数学' AND score < 60;

## 法2：
select stu.Sname,sc.score from student stu
    left join sc sc on stu.S = sc.S
    left join course c on sc.c = c.c
    where Cname = '数学' and score < 60;


#-- 35、查询所有学生的课程及分数情况； 
## 法1：
SELECT Sname, Course.Cname, score FROM SC, Student, Course
WHERE SC.S = Student.S AND SC.C = Course.C;

## 法2：
select stu.S,Sname,
       sum(case sc.c when 1 then sc.score end ) 语文,
       sum(case sc.c when 2 then sc.score end ) 数学,
       sum(case sc.c when 3 then sc.score end ) 英语
    from student stu
    left join sc sc on stu.S = sc.S
    left join course c on sc.c = c.c
    group by stu.S, Sname;




#-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数； 
## 法1：
SELECT Sname, Course.Cname, score FROM SC, Student, Course
WHERE SC.S = Student.S AND SC.C = Course.C AND score >= 70;

## 法2：
select stu.Sname,c.Cname,sc.score from student stu
    left join sc sc on stu.S = sc.S
    left join course c on sc.c = c.c
    where score > 70;

#-- 37、查询不及格的课程
## 法1：
SELECT Course.Cname FROM SC, Course
WHERE SC.C = Course.C AND score < 60 GROUP BY Course.Cname;


#-- 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名； 
## 法1：
SELECT Student.S, Sname FROM SC, Student
WHERE SC.S = Student.S AND SC.C = '01' AND score >= 80;

## 法2：
select stu.S,stu.Sname from student stu
    left join sc sc on stu.S = sc.S
    where sc.c = 1 and sc.score >= 80;
#-- 39、求每门课程的学生人数 
## 法1：
SELECT Course.Cname, COUNT(*) AS student_num FROM SC, Course
WHERE SC.C = Course.C GROUP BY Course.Cname;

## 法2：
select c.Cname,count(sc.S) 学生人数 from sc sc
    left join course c on sc.c = c.c
    group by c.Cname;


#-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
## 法1：
SELECT Student.S, Sname, score FROM SC, Student, Course, Teacher
WHERE SC.S = Student.S AND SC.C = Course.C AND Course.T = Teacher.T AND Teacher.Tname = '张三'
ORDER BY score DESC
LIMIT 1;

## 法2：
select stu.*,select1.maxstu from student stu
    left join sc sc on stu.S = sc.S
    left join course c on sc.c = c.c
    left join
    (select c.c,max(score) maxstu from student stu
    left join sc sc on stu.S = sc.S
    left join course c on sc.c = c.c
    left join teacher t on c.T = t.T
    where t.Tname = '张三' group by c.c) select1 on c.c = select1.c
    where sc.score = select1.maxstu;

#-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 
## 法1：
SELECT SC1.S, SC1.C, SC1.score
FROM SC AS SC1, SC AS SC2
WHERE SC1.C <> SC2.C AND SC1.score = SC2.score AND SC1.S = SC2.S;

## 法2：
select sc1.S, sc1.c, sc1.score from sc sc1 join sc sc2
    where sc1.score = sc2.score and sc1.S != sc2.S and sc1.c != sc2.c;

#-- 42、查询每门功成绩最好的前两名 
## 法1：
SELECT C.Cname, S.Sname, MAX(SC.score) AS max_score
FROM Course AS C, Student AS S, SC
WHERE C.C = SC.C AND S.S = SC.S
AND ( SELECT COUNT(DISTINCT score)
      FROM SC AS SC2
      WHERE SC2.C = C.C AND SC2.score > SC.score ) < 2
GROUP BY C.Cname, S.Sname
ORDER BY C.Cname ASC, max_score DESC;

## 法2：
(select stu.S,stu.Sname,c.Cname,sc.score from student stu
    left join sc sc on stu.S = sc.S
    left join course c on sc.c = c.c where sc.c = 1
    group by stu.S, stu.Sname, c.Cname, sc.score
    having max(sc.score) order by sc.score desc limit 2)
union (select stu.S,stu.Sname,c.Cname,sc.score from student stu
    left join sc sc on stu.S = sc.S
    left join course c on sc.c = c.c where sc.c = 2
    group by stu.S, stu.Sname, c.Cname, sc.score
    having max(sc.score) order by sc.score desc limit 2)
union (select stu.S,stu.Sname,c.Cname,sc.score from student stu
    left join sc sc on stu.S = sc.S
    left join course c on sc.c = c.c where sc.c = 3
    group by stu.S, stu.Sname, c.Cname, sc.score
    having max(sc.score) order by sc.score desc limit 2);


#-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列  
## 法1：
SELECT SC.C, COUNT(DISTINCT SC.S) AS student_count
FROM SC
GROUP BY SC.C 
HAVING COUNT(DISTINCT SC.S) > 5
ORDER BY student_count DESC, SC.C ASC;


#-- 44、检索至少选修两门课程的学生学号 
## 法1：
SELECT S
FROM SC
GROUP BY S 
HAVING COUNT(DISTINCT C) >= 2;

#-- 45、查询选修了全部课程的学生信息 
## 法1：
SELECT SC.S, S.Sname
FROM SC, Student AS S
WHERE S.S = SC.S
GROUP BY SC.S, S.Sname
HAVING COUNT(DISTINCT C) = ( SELECT COUNT(DISTINCT C)
                             FROM Course ) ;

## 法2：
select stu.* from student stu
    left join sc sc on stu.S = sc.S
    group by stu.S,Sname,Sage,Ssex
    having count(sc.c) = (select count(*) from course);
#-- 46、查询各学生的年龄
## 法1：
SELECT Sname, YEAR(NOW()) - YEAR(Sage) - (CASE WHEN MONTH(NOW()) < MONTH(Sage) OR (MONTH(NOW()) = MONTH(Sage) AND DAY(NOW()) < DAY(Sage)) THEN 1 ELSE 0 END) AS age
FROM Student;

## 法2：
select Sname,year(now())-year(Sage) 年龄  from student;

#-- 47、查询本周过生日的学生
## 法1：
SELECT S, Sname, Sage
FROM Student
WHERE
(
MONTH(Sage) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1-DAYOFWEEK(NOW()) DAY))AND
DAY(Sage) = DAY(DATE_ADD(CURDATE(), INTERVAL 1-DAYOFWEEK(NOW()) DAY))) OR
(
MONTH(Sage) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1-DAYOFWEEK(NOW())+1 DAY))AND
DAY(Sage) = DAY(DATE_ADD(CURDATE(), INTERVAL 1-DAYOFWEEK(NOW())+1 DAY))) OR
(
MONTH(Sage) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1-DAYOFWEEK(NOW())+2 DAY))AND
DAY(Sage) = DAY(DATE_ADD(CURDATE(), INTERVAL 1-DAYOFWEEK(NOW())+2 DAY))) OR
(
MONTH(Sage) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1-DAYOFWEEK(NOW())+3 DAY))AND
DAY(Sage) = DAY(DATE_ADD(CURDATE(), INTERVAL 1-DAYOFWEEK(NOW())+3 DAY))) OR
(
MONTH(Sage) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1-DAYOFWEEK(NOW())+4 DAY))AND
DAY(Sage) = DAY(DATE_ADD(CURDATE(), INTERVAL 1-DAYOFWEEK(NOW())+4 DAY))) OR
(
MONTH(Sage) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1-DAYOFWEEK(NOW())+5 DAY))AND
DAY(Sage) = DAY(DATE_ADD(CURDATE(), INTERVAL 1-DAYOFWEEK(NOW())+5 DAY))) OR
(
MONTH(Sage) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1-DAYOFWEEK(NOW())+6 DAY))AND
DAY(Sage) = DAY(DATE_ADD(CURDATE(), INTERVAL 1-DAYOFWEEK(NOW())+6 DAY)));


#-- 48、查询下周过生日的学生
## 法1：
SELECT S, Sname, Sage
FROM Student
WHERE 
		(
		MONTH(Sage) = MONTH(DATE_ADD(CURDATE(), INTERVAL 8-DAYOFWEEK(NOW()) DAY))AND 
		DAY(Sage) = DAY(DATE_ADD(CURDATE(), INTERVAL 8-DAYOFWEEK(NOW()) DAY))) OR
		(
		MONTH(Sage) = MONTH(DATE_ADD(CURDATE(), INTERVAL 8-DAYOFWEEK(NOW())+1 DAY))AND 
		DAY(Sage) = DAY(DATE_ADD(CURDATE(), INTERVAL 8-DAYOFWEEK(NOW())+1 DAY))) OR
		(
		MONTH(Sage) = MONTH(DATE_ADD(CURDATE(), INTERVAL 8-DAYOFWEEK(NOW())+2 DAY))AND 
		DAY(Sage) = DAY(DATE_ADD(CURDATE(), INTERVAL 8-DAYOFWEEK(NOW())+2 DAY))) OR
		(
		MONTH(Sage) = MONTH(DATE_ADD(CURDATE(), INTERVAL 8-DAYOFWEEK(NOW())+3 DAY))AND 
		DAY(Sage) = DAY(DATE_ADD(CURDATE(), INTERVAL 8-DAYOFWEEK(NOW())+3 DAY))) OR
		(
		MONTH(Sage) = MONTH(DATE_ADD(CURDATE(), INTERVAL 8-DAYOFWEEK(NOW())+4 DAY))AND 
		DAY(Sage) = DAY(DATE_ADD(CURDATE(), INTERVAL 8-DAYOFWEEK(NOW())+4 DAY))) OR
		(
		MONTH(Sage) = MONTH(DATE_ADD(CURDATE(), INTERVAL 8-DAYOFWEEK(NOW())+5 DAY))AND 
		DAY(Sage) = DAY(DATE_ADD(CURDATE(), INTERVAL 8-DAYOFWEEK(NOW())+5 DAY))) OR
		(
		MONTH(Sage) = MONTH(DATE_ADD(CURDATE(), INTERVAL 8-DAYOFWEEK(NOW())+6 DAY))AND 
		DAY(Sage) = DAY(DATE_ADD(CURDATE(), INTERVAL 8-DAYOFWEEK(NOW())+6 DAY)));


#-- 49、查询本月过生日的学生
## 法1：
SELECT * FROM Student
WHERE MONTH(Sage) = MONTH(NOW());


#-- 50、查询下月过生日的学生
## 法1：
SELECT * FROM Student
WHERE MONTH(Sage) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));