表结构如图：
如果需要表结构和数据给我留言。
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT stu.*,c1.score '01课程',c2.score '02课程',c3.score '03课程' from (SELECT score,Scid from sc
WHERE cid=01) c1,(SELECT score,Scid from sc
WHERE Cid=02) c2,student stu,(SELECT score,Scid from sc
WHERE Cid=02) c3
WHERE c1.Scid=c2.scid and stu.Sid=c1.scid and c3.scid=c2.scid and
c1.score>c2.score
--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT stu.*,c1.score '01课程',c2.score '02课程',c3.score '03课程' from (SELECT score,Scid from sc
WHERE cid=01) c1,(SELECT score,Scid from sc
WHERE Cid=02) c2,student stu,(SELECT score,Scid from sc
WHERE Cid=02) c3
WHERE c1.Scid=c2.scid and stu.Sid=c1.scid and c3.scid=c2.scid and
c1.score<c2.score
--3、查询平均成绩大于等于70分的同学的学生编号和学生姓名和平均成绩
SELECT student.sid,student.sname,AVG(sc.score)
FROM student,sc
WHERE student.sid=sc.scid GROUP BY student.sid
HAVING AVG(sc.score)>=70
--4、查询平均成绩小于70分的同学的学生编号和学生姓名和平均成绩
SELECT student.sid,student.sname,AVG(sc.score)
FROM student,sc
WHERE student.sid=sc.scid GROUP BY student.sid
HAVING AVG(sc.score)<70
--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT student.sid,student.sname,COUNT(sc.cid),sum(sc.score)
FROM student,sc
WHERE student.sid=sc.scid GROUP BY student.sid
--6、查询"李"姓老师的数量
SELECT count(tname) FROM teacher where tname like "李%"
--7、查询学过"张三"老师授课的同学的信息
SELECT student.* from student,teacher,sc
WHERE student.sid=sc.scid and teacher.tid=sc.cid and teacher.tname ="张三"
group BY student.sname
--8、查询没学过"张三"老师授课的同学的信息
SELECT student.* from student,teacher,sc
WHERE teacher.tid=sc.cid
and student.sname not in (SELECT student.sname from student,teacher,sc
WHERE student.sid=sc.scid and teacher.tid=sc.cid and teacher.tname ="张三")
group BY student.sname
--9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT * FROM student WHERE sid in (SELECT s1.scid FROM (SELECT scid
FROM sc WHERE cid='01') s1,(SELECT scid FROM sc WHERE cid='02') s2
WHERE s1.scid=s2.scid)
--10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT student.* FROM student
where sid =(SELECT scid from sc where scid not in (SELECT scid FROM sc WHERE cid='02') GROUP BY scid )
--11、查询没有学全所有课程的同学的信息 ok
select stu.*
from student stu LEFT JOIN sc
on sc.scid = stu.sid
GROUP BY sc.scid having COUNT(sc.cid) < (select COUNT(*) from course)
--12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息 ok
SELECT student.* FROM student,sc
where sc.scid=student.sid
and sc.cid in (SELECT cid FROM sc WHERE sc.scid =01)
and not student.sid=01
GROUP BY student.sid
--13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 ok
SELECT * from (SELECT student.* from
(select Count(cid) cc,scid from sc where scid not in (01) GROUP BY scid) A
left JOIN student on A.scid=student.sid and A.cc=(SELECT count(cid) from sc where sc.scid=01)) d
where sid is not null
--14、查询没学过"张三"老师讲授的任一门课程的学生姓名 ok
SELECT student.sname from student,teacher,sc
WHERE teacher.tid=sc.cid
and student.sname not in (SELECT student.sname from student,teacher,sc
WHERE student.sid=sc.scid and teacher.tid=sc.cid and teacher.tname ="张三")
group BY student.sname
--15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 ok
select stu.sid,stu.sname,b.ss
from student stu
right JOIN (
SELECT scid,avg(score) as ss from sc where score <60
GROUP BY scid HAVING count(score)>=2) b
on b.scid = stu.sid
--16、检索"01"课程分数小于60，按分数降序排列的学生信息 ok
SELECT s.*,sc.score from student s,sc
where s.sid=sc.scid and score<60 and sc.cid=01
--17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 ok
SELECT student.sid,b.01,b.02,b.03,b.平均成绩
FROM student
LEFT JOIN
(SELECT scid,max(CASE cid when '01' THEN score ELSE 0 END) '01',
max(CASE cid when '02' THEN score ELSE 0 END) '02',
max(CASE cid when '03' THEN score ELSE 0 END) '03',
avg(score) '平均成绩'
from sc GROUP BY scid) B
on student.sid=b.scid
ORDER BY b.平均成绩 desc
--18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--及格为>=60，中等为：-80，优良为：-90，优秀为：>=90 ok
select distinct c.Cid,c.Cname,
B.`平均分`,B.`最低分`,B.`最高分`,
b.`及格率`
from Course c
left join (
select avg(sc.score) `平均分`,max(sc.score) `最高分`,
min(sc.score) `最低分`,cid,
CONCAT(round(sum(case when score>=60 then 1 else 0 end)/count(*)*100,2),'%') as `及格率`
from SC
group by sc.Cid
) B
on c.cid=B.Cid
--19、按各科成绩进行排序，并显示排名
SELECT A.sname,B.score,B.cid,(SELECT count(C.score)+1 from (SELECT C.score,count(C.score) from sc C GROUP BY C.score ORDER BY c.score desc) C
WHERE C.score>B.score) as paiming
from (SELECT sname,sid from student) A LEFT JOIN sc B
on B.scid=A.sid
WHERE b.score >0
ORDER BY b.cid,b.score desc
--20、查询学生的总成绩并进行排名 ok
SELECT student.sname,sum(sc.score)
from student LEFT JOIN sc
on sc.scid=student.sid
GROUP BY student.sid
ORDER BY SUM(sc.score) desc
21-25略
--26、查询每门课程被选修的学生数
SELECT cid,COUNT(scid) 学生数 from sc GROUP BY cid
--27、查询出只有两门课程的全部学生的学号和姓名
SELECT sid,sname from student
where sid in (SELECT scid from (SELECT scid,count(cid) 课程数 FROM sc
GROUP BY scid) A where A.课程数=2)
--28、查询男生、女生人数
SELECT ssex,COUNT(ssex) 人数 from student GROUP BY ssex
--29、查询名字中含有"风"字的学生信息
SELECT * from student where sname like '%风%'
--30、查询同名同性学生名单，并统计同名人数
SELECT A.*,B.同名人数 from student A
LEFT JOIN
(SELECT sname,ssex,COUNT(*) 同名人数 from student GROUP BY sname,ssex) B
on A.sname=B.sname and a.ssex=b.ssex
where b.同名人数>1
--31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
SELECT * FROM student where year(sage)=1990
--32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT cid,avg(score) 平均成绩 from sc GROUP BY cid ORDER BY 平均成绩 desc,cid
--33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SELECT A.sid,a.sname,b.平均成绩 FROM student a
LEFT JOIN
(SELECT scid,avg(score) 平均成绩 from sc GROUP BY scid) B
on A.sid=B.scid
WHERE b.平均成绩>85
--34、查询课程名称为"数学"，且分数低于 60 的学生姓名和分数
SELECT b.sname,a.score from (SELECT * from sc where score<60
and cid=(SELECT cid from course WHERE cname='数学')) A
LEFT JOIN student B
on A.scid=b.sid
--35、查询所有学生的课程及分数情况；
SELECT a.sid,b.cid,b.score FROM student a LEFT JOIN sc b
on a.sid=b.scid
--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
SELECT a.sname,d.cname,d.score FROM
(SELECT b.*,c.cname from (SELECT * from sc where score>70) b LEFT JOIN course C
on b.cid=c.cid) d
LEFT JOIN student a on d.scid=a.sid
--37、查询不及格的课程
SELECT * FROM sc where score<60
--38、查询课程编号为 01 且课程成绩在70分以上的学生的学号和姓名；
SELECT a.sid,b.sname
from (SELECT * from sc where score>70)
39-45略
--46、查询各学生的年龄
SELECT *,(case when convert(int,'1'+substring(convert(varchar(10),sage,112),
5,8))
<convert(int,'1'+substring(convert(varchar(10),getDate(),112)，5，8))
then datediff(yy,sage,getdate())
else datediff(yy,sage,getdate())-1
end) age
from student
47-50略