1.学生其存放在Student表中,学生选课情况存放在SC表中,课程情况存放在Course表中。
(1)查询与“刘晨”在同一个系学习的学生。
答:SELECT Sno,Sname,Sdept FROM Student WHERE Sdept IN(SELECT Sdept FROM Student WHERE Sname=’刘晨’);
或者:SELECT S1.Sno,S1.Sname,S1.Sdept FROM Student S1,Student S2 WHERE S1.Sdept=S2.Sdept AND S2.Sname=’刘晨’; (2)查询选修了课程名为“信息系统”的学生学号和姓名
答:SELECT Sno,Sname FROM Student WHERE Sno IN(SELECT Sno FROM SC) WHERE Cno IN(SELECT Cno FROM Course WHERE Cname=’信息系统’);
(1)查询与“刘晨”在同一个系学习的学生。
答:SELECT Sno,Sname,Sdept FROM Student WHERE Sdept IN(SELECT Sdept FROM Student WHERE Sname=’刘晨’);
或者:SELECT S1.Sno,S1.Sname,S1.Sdept FROM Student S1,Student S2 WHERE S1.Sdept=S2.Sdept AND S2.Sname=’刘晨’; (2)查询选修了课程名为“信息系统”的学生学号和姓名
答:SELECT Sno,Sname FROM Student WHERE Sno IN(SELECT Sno FROM SC) WHERE Cno IN(SELECT Cno FROM Course WHERE Cname=’信息系统’);
或者:SELECT Sno,Sname FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Course.Cname=’信息系统’;
2.分页语句取出sql表中第31到40的记录(以自动增长ID为主键) sql server方案:
select top 10 * from t where id not in (select top 30 id from t order by id ) orde by id
3.用一条SQL语句 查询出每门课都大于80分的学生姓名
name kecheng fenshu
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90
select distinct name from score where name not in (select distinct name from score where score<=80)