实验二 数据的查询、更新

数据库实验

实验二 数据的查询、更新

一、实验目的

)

Sage

smallint check(Sage between 15 and 45),

Sdept char(20) default 'CS',

数据库实验

insert into Student values('95001','李敏勇', '男','20', 'CS') insert into Student values('95002','刘晨', '女','19', 'IS') insert into Student values('95003','王敏', '女','18', 'MA')

数据库实验

实验二 数据的查询、更新

徐龙琴设计制作

create table SC ( )

insert into SC values('95001','1', '92') insert into SC values('95001','2', '85') insert into SC values('95001','3', '88') insert into SC values('95002','2', '90') insert into SC values('95003','2', '55') insert into SC values('95004','2', '70')

Sno Cno

int

,

,

check (Grade between 0 and 100),

char(4)

Grade int

primary key (Sno,Cno),

foreign key (Sno) references Student(Sno), foreign key (Cno) references Course(Cno)

3、用T-SQL1) 向学生表:Student

insert into Student values('95030','谢非','男','22','CS')

2)

select Sno from Student where Sname='李敏勇' select Cno fromwhere Cname='数据库' updateset='98'

where=and=1

中谢非的记录并保存

deletefromwhere='谢非'

Student表中删除李敏勇学生的记录,为什么?能不能删除王敏, 张立两个?

delete delete from Student

from Student

where Sname='李敏勇' where Sname='王敏' and Sname='张立'

数据库实验

实验二 数据的查询、更新

徐龙琴设计制作

4、用T-SQL语句，完成下面简单的查询 1）查询全体学生的学号、姓名及年龄.

select Sno,Sname,Sage from Student

2）查询全体学生的姓名, 列名）。

select Sname,Sage,系名=LOWER(Sdept) from Student

3）查询选修了课程的学生学号

select distinct Sno from SC

4）查询信息系全体学生的姓名

select Sname from Student where Sdept='IS'

5）查询所有年龄在20

select Sname ,Sage from Student where Sage<20

6）查询年龄在20到,

select,,fromwherebetweenand

间的学生的姓名,系别及年龄

select,,fromwherenot between 19 and 23

(IS)和计算机系(CS)学生的姓名和性别

select,Ssex fromwherenot in('is','cs')

9）查询所有姓刘的学生的姓名,学号和性别

select Sname,Sno,Ssex from Student

where Sname like '刘%'

数据库实验

实验二 数据的查询、更新

徐龙琴设计制作

10）查询姓“张”且名为一个汉字的学生的姓名

select Sname from Student

where Sname like '张_'

11）查询名字中第2个字为”敏”字的学生姓名和学号

select Sname ,Sno from Student

Where Sname like '_敏%'

12）查询所有不姓刘的学生姓名

select Sname from Student

where Sname not like '刘%'

13）查询全体学生情况,结果按所在系升序排列,select * from Student

order by Sdept asc,Sage desc

14

Select top 10 percent * from Student

152

行数据,若第

Select topwithtiesfrom SC

where>=orderbydesc

select,Cno,fromcomputeSUM() by Sno

select* fromorder by Sno,Cno,Grade

compute max(Grade),min (Grade),sum(Grade),avg(Grade)by Sno

5、用T-SQL语句完成下面的查询 1)查询学生的总人数

数据库实验

实验二 数据的查询、更新

徐龙琴设计制作

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)查询选修了2select Sno from SC group by Sno having count(*)>

7)

select.,from,,where .=.Sno and

.=.Cno

(即先修课的先修课)

http://doc.guandang.neto, second.Cpno from,Course second http://doc.guandang.neto=http://doc.guandang.neto

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

select Student.Sno,Sname from Student,SC

where Student.Sno=SC.Sno and SC.Grade>=90 and http://doc.guandang.neto='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 http://doc.guandang.neto=http://doc.guandang.neto

and http://doc.guandang.netame='数学'

3)

select Sname,Sage from Student where Sage<any(

select Sage from Student where='IS')

and Sdept<>'IS'

4)

select Sname,fromwhere Sage<all(

selectfromwhere='CS')

and<>'CS'

selectfromwhere 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 http://doc.guandang.neto=http://doc.guandang.neto

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

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

select Cno from Course where Cno not in

(Select http://doc.guandang.neto

from Student,SC, where Student.=.and.=http://doc.guandang.neto