-- 子查询还有一种用法叫exist,写个例子
SELECT * FROM grade g WHERE EXISTS (SELECT * FROM `subject` s WHERE s.gradeid = g.id);-- 有课程的班级

-- 查询三种 其他不同数据库  的分页写法
select top 10 stuno from student where stuno not in(select top 30 stuno from student ORDER BY stuno ASC)ORDER BY stuno;-- Sqlserver
SELECT * FROM ( SELECT A.*,stuno RN FROM (SELECT * FROM student) A  WHERE stuno <= 40 ) B WHERE RN >= 21;-- oracle 
select top 10 * from (select top 10*5 * from student order by stuno) order by stuno desc;-- access

-- 查询出考试成绩在60分以上的考试记录
select * from examresult where score>60;

-- 每月最后一天出生的学生信息
select * from student where MONTH(birthday)<>MONTH(DATE_ADD(birthday,INTERVAL 1 DAY));

-- 周日入学的学生信息
select * from student where DAYOFWEEK(enterdate)=1;

-- 年龄最大的学生的全部个人信息
select * from student where birthday = (select min(birthday) from student);

-- 查询累计参加考试次数 超过3次的学生信息（全部信息），如果一门课有考过几次，只算一门课。
select * from student s inner join (select st_id,count(*) time from (select st_id,sb_id,count(*) 考试次数 from examresult group by st_id,sb_id) t1 group by st_id) t2 on (s.stuno=t2.st_id) where time >=3;

-- 查询有过补考的学生信息（同一门课考超过1次，认为是补考）
select * from student s inner join (select st_id,sb_id,count(*) 考试次数 from examresult group by st_id,sb_id) t on(s.stuno=t.st_id) where 考试次数>1;

-- 使用分组查询获得每门课程最近一次考试的日期。显示课程号和日期。'
select sb_id,max(exam_date) from examresult group by sb_id

-- 使用连接查询得到各门课程最近一次考试成绩的学生姓名、年级、课程名称、考试日期和考试成绩
select st.sname,g.gname,sb.subjectname,t2.exam_date,t2.score from (select st_id,score,sb_id,max(exam_date) exam_date from (select * from examresult where exam_date is not null order by exam_date desc) t1 group by sb_id) t2 inner join student st on(t2.st_id=st.stuno) inner join `subject` sb on(sb.id=t2.sb_id) inner join grade g on(g.id=st.gradeid);

-- 查询年级信息，以及该年级的课程数，没有课程的也要显示，对应的课程数目为0
select gname,count(t.subjectname) from (select g.gname,s.subjectname from grade g left join `subject` s on (g.id=s.gradeid)) t group by gname;