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

SELECT * FROM Student RIGHT JOIN (
SELECT t1.sid,class1,class2 FROM
(SELECT sid,score AS class1 FROM SC WHERE SC.CId=’01’)AS t1,
(SELECT sid,score AS class2 FROM SC WHERE SC.CId=’02’)AS t2
WHERE t1.sid=t2.sid AND t1.class1>t2.class2

)r
ON Student.SId=r.sid;

因为需要全部的学生信息，则需要在sc（分数表）表获取符合条件的sid(学生id)与student表进行join，可以左连接也可以右连接

第二种：

SELECT * FROM (
SELECT t1.sid,class1,class2
FROM
(SELECT sid,score AS class1 FROM SC WHERE SC.CId=’01’)AS t1,
(SELECT sid,score AS class2 FROM SC WHERE SC.CId=’02’)AS t2
WHERE t1.sid=t2.sid AND t1.class1>t2.class2
)r
LEFT JOIN Student
ON Student.SId=r.sid;

2：查询同时存在01和02课程的情况

这是查询01和02 课程都存在的学生吗？

SELECT FROM
(SELECT FROM SC WHERE SC.CId=’01’)AS t1,
(SELECT * FROM SC WHERE SC.CId=’02’)AS t2
WHERE t1.sid=t2.sid;

3:查询存在01课程但可能不存在02课程情况（不存在显示为0）

SELECT FROM
(SELECT FROM SC WHERE SC.CId=’01’)AS t1
LEFT JOIN
(SELECT * FROM SC WHERE SC.CId=’02’)AS t2
ON t1.sid=t2.sid;

4查询不存在01课程但存在02课程情况

SELECT * FROM SC
WHERE SC.SId NOT IN
(SELECT sid FROM SC WHERE SC.CId=’01’)
AND SC.CId=’02’;

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

SELECT Student.SId,sname,ss FROM Student,
(SELECT sid,avg(score)AS ss FROM SC GROUP BY sid HAVING avg(score)>60)r
WHERE Student.SId=r.sid;

6：查询在sc（成绩）表存在成绩的学生信息
SELECT DISTINCT Student.
FROM Student,SC
WHERE Student.SId=SC.SId;
7:查询所有同学的学生编号，学生姓名，选课总数，所有课程的成绩总和
SELECT Student.SId,Student.Sname,r.coursenumber,r.scorenumber FROM Student,
(SELECT SC.SId,sum(SC.score)AS scorenumber,count(SC.CId)AS coursenumber FROM SC
GROUP BY SC.SId)r
WHERE Student.SId=r.sid;
8:查询有成绩的学生信息
SELECT FROM Student
WHERE EXISTS (SELECT SC.SId FROM SC WHERE Student.SId=SC.SId);
9:查姓李老师的数量
SELECT count()
FROM Teacher
WHERE tname LIKE ‘李%’;
10：查询学过张三老师授课的学生信息
SELECT Student. from Student,Teacher,Course,SC
WHERE
Student.SId=SC.SId
AND Course.CId=SC.CId
AND Course.TId=Teacher.TId
AND tname=’张三’;
11：查询没有学全所有课程的学生信息
SELECT FROM Student
WHERE Student.SId IN (
SELECT SC.SId FROM SC
WHERE SC.CId IN (
SELECT SC.CId FROM SC
WHERE SC.SId=’01’));
12:查询至少有一门课与学号为01的同学所学相同的同学信息
SELECT FROM Student
WHERE Student.SId IN (
SELECT SC.SId FROM SC
WHERE SC.CId IN(
SELECT SC.CId FROM SC
WHERE SC.SId=’01’
)
);
13:查询没学过张三老师讲授的任意一门课程的学生姓名
SELECT FROM Student
WHERE Student.SId NOT IN(
SELECT SC.SId FROM SC,Course,Teacher
WHERE
SC.CId=Course.CId
AND Course.TId=Teacher.TId
AND Teacher.Tname=’张三’
);
14:查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT Student.SId,Student.Sname,b.avg
FROM Student RIGHT JOIN
(SELECT sid,avg(score)AS avg FROM SC
WHERE sid IN(
SELECT sid FROM SC
WHERE score<60
GROUP BY sid
HAVING count(score)>1)
GROUP BY sid)b ON Student.SId=b.sid;
15:查询01课程分数小于60，按分数降序排列学生信息
SELECT Student.,SC.score FROM Student,SC
WHERE Student.SId=SC.SId
AND SC.score<60
AND SC.CId=’01’
ORDER BY SC.score DESC;
16：查询各科成绩最高分、最低分和平均分
select SC.CId ,max(SC.score)as 最高分,min(SC.score)as 最低分,AVG(SC.score)as 平均分,count()as 选修人数,sum(case when SC.score>=60 then 1 else 0 end )/count()as 及格率,sum(case when SC.score>=70 and SC.score<80 then 1 else 0 end )/count(_)as 中等率,sum(case when SC.score>=80 and SC.score<90 then 1 else 0 end )/count(_)as 优良率,sum(case when SC.score>=90 then 1 else 0 end )/count()as 优秀率 from SCGROUP BY SC.CIdORDER BY count()DESC, SC.CId ASC
17:按各科成绩进行排序，并显示排名，score重复式保留名次空缺
SELECT a.CId,a.SId,a.score,COUNT(b.score)+1 AS rankFROM SC AS aLEFT JOIN SC AS bON a.score18:查询学生总成绩，并进行排名，总分重复时不保留名次空缺
SET @crank=0;SELECT q.SId,total,@crank :=@crank +1 AS rank FROM(SELECT SC.SId,SUM(SC.score)AS total FROM SCGROUP BY SC.SIdORDER BY total DESC)q;
19:查询各科成绩前三名的记录
SELECT FROM SCWHERE (SELECT COUNT()FROM SC AS aWHERE SC.CId=a.CId AND SC.score20:查询每门课程被选修的学生数
SELECT CId,COUNT(SId)FROM SCGROUP BY CId;