实验二 - 数据的查询、更新(2)

实验二 数据的查询、更新 徐龙琴设计制作

select count(*) from student

2)查询选修了课程的学生人数

select count(distinct Sno) from SC

3)计算选2号课程的学生平均成绩

select AVG(Grade) from SC where Cno='2'

4)查询选修2号课程的学生最高分数

select MAX(Grade) from SC where Cno='2'

5)求各个课程号及相应的选课人数

select Cno, COUNT(Sno) from SC group by Cno

6)查询选修了2门以上的课程的学生学号 select Sno from SC group by Sno having count(*)>2

7)查询每个学生及其选修课程的情况 select Student.Sno,Cname from SC,Course,Student where Student.Sno=SC.Sno and

Course.Cno=SC.Cno

8)查询每一门课的间接先修课(即先修课的先修课)

select first.Cno, second.Cpno from Course first,Course second where first.Cno=second.Cno

9)查询选修2号课程且成绩在90分以上（包括90分）的所有学生。

select Student.Sno,Sname from Student,SC

where Student.Sno=SC.Sno and SC.Grade>=90 and SC.Cno='2'

实验二 数据的查询、更新 徐龙琴设计制作

6. 用T-SQL语句完成下面的查询

1)查询与“刘晨”在同一个系学习的学生

select Sno,Sname,Sdept from Student where Sdept IN

(select Sdept from Student

where Sname='刘晨')

2)查询选修了课程名为“数学”的学生学号和姓名

select Student.Sno,Student.Sname from Course,Student,SC

where Student.Sno=SC.Sno and Course.Cno=SC.Cno

and Course.Cname='数学'

3)查询其它系中比信息系中某一学生年龄小的学生姓名和年龄 select Sname,Sage from Student where Sage

select Sage from Student where Sdept='IS')

and Sdept<>'IS'

4)查询其它系中比计算机系所有学生年龄都小的学生姓名及年龄

select Sname,Sage from Student where Sage

select Sage from Student where Sdept='CS')

and Sdept<>'CS'

5)查询所有选修了2号课程的学生姓名

select Sname from Student where EXISTS

(select * from SC

where Sno=Student.Sno and Cno='2')

6)查询没有选修3号课程的学生姓名

实验二 数据的查询、更新 徐龙琴设计制作

select Sname from Student where NOT EXISTS (select * from SC

where Sno=Student.Sno and Cno='3')

7、用T-SQL语句完成下面的复杂查询

1)至少选修刘老师所授课程中一门课程的女学生姓名

select Sname

from Student,Course,SC

where Student.Sno=SC.Sno and Course.Cno=SC.Cno

and Course.Teacher like'刘%'and Student.Ssex='女'

2)检索王同学不学的课程的课程号

select Cno from Course where Cno not in

(Select SC.Cno

from Student,SC,Course where Student.Sno=SC.Sno and Course.Cno=SC.Cno

and Student.Sname like '王%' )

select Cno from SC

where Cno not in(select Cno

from Student，SC

where Student.Sno=SC.Sno and Sname like'王%')

3)检索全部学生都选修的课程的课程号与课程名。

select Cno,Cname from Course where not exists (select *

from Student where not exists (select * from SC

where SC.Sno=Student.Sno and SC.Cno=Course.Cno)

)

4)检索选修课程包含刘老师所授课的学生学号。

实验二 数据的查询、更新 徐龙琴设计制作

select Sno from SC x

where not exists (

select * from Course

where Teacher like '刘%' and not exists ( select *

from SC y

where y.Cno=Course.Cno and y.Sno=x.Sno)

)

5)求选修课程号为2的学生的平均年龄。

Select AVG(Sage) from Student,SC

where Student.Sno=SC.Sno and SC.Cno='2'

6)求刘老师所授课程的每门课程的学生平均成绩。

Select Teacher ,Cname ,AVG(Grade) from Student,SC,Course

where Student.Sno=SC.Sno and Course.Cno=SC.Cno and Teacher like '刘%'

group by Teacher ,Course.Cno,Cname

7)检索学号比刘同学大，而年龄比他小的学生姓名。 select Sname from Student where Sno>(

select Sno from Student where Sname='刘同') and Sage<( select Sage from Student where Sname='刘同')

8)求年龄大于女同学平均年龄的男同学姓名和年龄。

select Sname,Sage from Student where Ssex ='男'and Sage>( select avg(Sage)

from Student where Ssex='女' )

实验二 数据的查询、更新 徐龙琴设计制作

9)求年龄大于所有女同学年龄的男学生姓名和年龄。

select Sname,Sage from Student

where Ssex='男' and Sage>all(

select Sage

from Student where Ssex='女')

10)检索每一门课程成绩都大于等于80分的学生学号、姓名和性别，并把检索到的值送往另一个已存在的基本表S（SNO,SNAME,SEX）。

select Sno SNO ,Sname SNAME,Ssex SEX into S from Student where Sno in (

select Sno from SC

where Grade>=80)

11)把选课数学课不及格的成绩全改为空值。

update SC set Grade ='' where Sno=(

select Sno from SC

where Grade<60 )

and Cno=(

select Cn …… 此处隐藏：1761字，全部文档内容请下载后查看。喜欢就下载吧 ……