11-06作业

--作业2012-11-6
--一、子查询
--1.查询高二二班的所有学生,学生姓名\性别\学号...
在where中 应用子查询
select * from Student where sClassId=(select clsId from Class where cName='高二二班')
--相关子查询
select * from Student where exists(select * from Class where cName='高二二班' and Class.clsId=Student.sClassId)

--2.查询高二二班和高二一班的所有学生,学生姓名\性别\学号。
select * from Student where sClassId in (select clsId from Class where cName='高二二班' or cName='高二一班')

--3.查询刘备、关羽、张飞的成绩
select * from Score where studentId IN (Select sId from Student where sName in('刘备','关羽','张飞'))

select * from Score where studentId IN (Select sId from Student where sName='刘备' or sName='关羽' or sName='张飞')

--二、分页（row_number() over()）
--4.查询MyStudents表中 第8页中的数据（每页3条记录）
select top 3 * from MyStudents
where FId not in
(select top (8*7) FId from MyStudents
order by FId )
order by FId

--三、join
--1.查询年龄超过20岁的姓名\年龄\所在班级
select sName,sAge,cName from Student as TS inner join Class TC on TC.clsId=TS.sClassId where sAge>20

--2.查询出所有参加考试的同学的学生编号，姓名，考试成绩。
select sId,sName,english,math from Student inner join Score on Score.studentId=Student.sId

--3.查询出所有没有参加考试的同学的学生编号，姓名，考试成绩。
select sId ,sName from Student where sId not in (select studentId from Score)