select * from Student where Sex='男' and year(Birth)-(select year(Birth)from Student--这里是需要告诉查询的表名,相当于嵌套 where Sname='林红')<02.检索所有学生的选课信息,包括学号、姓名、课程名、成绩,性别.
select sc.sno,sname, course.Cno,Cname,Grade,Sex --这里如果两个表中都有同一个属性,则需要标明在哪个表,如sc.sno from student,sc,Course where student.Sno=sc.Sno and Sc.Cno=course.Cno3.查询已经选课的学生的学号、姓名、课程名、成绩.
select sc.sno ,sname , Cname , Grade from student s , course c, sc where s.sno=sc.sno and c.cno=sc.cno(4)查询选修了“C语言程序设计”的学生的学号与姓名
select sc.Sno,sname from student inner join sc on student.Sno=sc.Sno inner join course on sc.Cno =course.cno and Cname='C语言程序设计'–b.用连接查询
语句:
select sc.Sno,sname from student,sc,course where student .Sno=sc.Sno and sc.Cno =course.cno and Cname='C语言程序设计'–c.用子查询
select Sno,sname from student where Sno in (select Sno from sc where Cno= (select cno from course where Cname ='C语言程序设计'))(5)查询与”张虹”在同一个班级的学生学号、姓名、家庭住址
select a.Sno,a.sname,a.Home_addr from student a,student b where a.Classno =b.Classno and b.Sname ='张虹' and a.Sname!='张虹'–b.用子查询
select Sno,sname,Home_addr from student where classno=(select classno from student where sname='张虹') and sname!='张虹'
select Sno,sname,Home_addr from student where classno!='051' and Birth<all (select Birth from student where classno='051')代码2:
select Sno,sname,Home_addr from student where classno!='051' and Birth<(select min(Birth) from student where classno='051')(7)(选作)查询选修了全部课程的学生姓名。本题使用除运算的方法。
select Sname from student where not exists ( select * from course where not exists ( select * from sc where sno=student. sno and cno=Course.cno))(8)查询至少选修了学生“20110002”选修的全部课程的学生的学号,姓名。
select Sno, Sname from student where sno in ( select distinct sno from sc as sc1 where not exists ( select * from sc as sc2 where sc2.sno='20110002' and not exists ( select * from sc as sc3 where sc3.Sno=sc1.sno and sc3.cno=sC2.cno) ) )(9)检索选修了“高数”课且成绩至少高于选修课程号为“002"课程的学生的学号、课程号、成绩,并按成绩从高到低排列。
select sc.Sno, sc.cno , grade from sc where grade >all(select grade from sc where cno='002' ) and Cno= (select Cno from course where Cname='高数') order by Grade desc(10)检索选修了至少3门以上课程的学生的学号、总成绩(不统计不及格的成绩),并要求按总成绩降序排列。
select sno,SUM(grade) from sc where sno in (select Sno from sc group by sno having COUNT(*)>=3) and Grade>=60 group by sno order by SUM (grade) desc(12)检索多于3名学生选修的并以3结尾的课程号的平均成绩。
select avg(Grade) as 平均成绩 from sc where Cno like '%3' group by cno having count (Cno)>3(13)检索最高分与最低分之差大于5分的学生的学号、姓名、最高分、最底分。
select distinct sc.sno 学号,sname 姓名, max (grade) as最高分,min (grade) as最低分 from student,sc where sc.sno=student.Sno group by sc.sno , Sname having max(grade) -min (grade) >5(14)创建一个表Student_other,结构同student,输入若干记录,部分记录和student表中的相同。
create table student__other ( Sno char (8) primary key, Sname varchar (8) not null, sex char(2) not null, Birth smalldatetime not null, Classno char (3) not null, Entrance_date smalldatetime not null, Home_addr varchar (40) , sdept char (2) not null, Postcode char (6) )随意插入几条student表中没有的数据:
select * from student__other so ,student s where so.sno=s.sno----b.查询Student表和Student_other表中的全部记录
select * from student union select * from student__other