day01:

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

分布查询：
SELECT from student,
(SELECT from(SELECT sc1.,sc2.cid as cid2,sc2.score as score2 FROM sc sc1,sc sc2 WHERE sc1.sid=sc2.sid and sc1.cid=01 and sc2.cid=02)as temp
WHERE temp.score>temp.score2)temp2 WHERE student.sid=temp2.sid
子查询：
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;— 省略前面的字段了（写不动了）

— 1.1 查询同时存在” 01 “课程和” 02 “课程的情况

SELECT sc1.*,sc2.cid,sc2.score FROM sc sc1,sc sc2 WHERE sc1.sid=sc2.sid and sc1.cid=01 and sc2.cid=02

— 1.2 查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )

SELECT from (select sc1.,sc2.sid sid2,sc2.cid cid2,sc2.score score2 from
(select from sc where sc.CId = ‘01’)
as sc1
left join
(select from sc where sc.CId = ‘02’) as sc2
on sc1.SId = sc2.SId)temp WHERE temp.score2=null;

— 1.3 查询不存在” 01 “课程但存在” 02 “课程的情况

select * from sc
where sc.SId not in (
select SId from sc
where sc.cid = ‘01’
)
AND sc.cid= ‘02’;

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

SELECT student.*,temp.avg from student,(SELECT sid ,avg(score)avg from sc GROUP BY sid HAVING avg>=60)temp WHERE student.sid=temp.sid

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

select DISTINCT student.*
from student,sc
where student.sid=sc.sid

day02:

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

SELECT * from student LEFT JOIN(SELECT sid,COUNT(cid),SUM(score) from sc GROUP BY SId) temp on student.sid=temp.sid

— 4.1 查有成绩的学生信息

SELECT DISTINCT student.*from student ,sc WHERE student.sid =sc.SId

— 5. 查询「李」姓老师的数量

SELECT COUNT(tname)姓李的人数 from teacher WHERE tname LIKE ‘李%’ GROUP BY tname

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

SELECT student.*from teacher join student join course join sc on teacher.TId=course.TId and
course.CId=sc.CId and sc.SId=student.SId WHERE teacher.Tname=’张三’

day03:

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

SELECT student. from sc,student WHERE sc.sid=student.SId GROUP BY sid HAVING COUNT()<3
— 查询至少有一门课与学号为” 01 “的同学所学相同的同学的信息
SELECT *from student WHERE sid in (SELECT sid from sc WHERE cid in (SELECT cid from sc WHERE sid=1))
— 查询和” 01 “号的同学学习的课程 完全相同的其他同学的信息

day04:

— 查询没学过”张三”老师讲授的任一门课程的学生姓名

select * from student
where student.sid not in(
select sc.sid from sc where sc.cid in(
select course.cid from course where course.tid in(
select teacher.tid from teacher where tname = “张三”
)
)
);

— 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT student.,avg(sc.score)平均分 from sc,student WHERE sc.SId=student.sid and sc.sid in(SELECT sid from sc WHERE score<60 GROUP BY sid HAVING count()>=2) GROUP BY sc.sid HAVING count(*)>=2

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

SELECT student.*,sc.score FROM sc,student WHERE sc.SId=student.sid and sc.cid=01 and sc.score<60 ORDER BY sc.score DESC