数据库实验及答案

实验二 SQL 语言的基本操作

实验目的和要求：

掌握利用SQL 语句完成各种查询操作的能力。重点掌握用SELECT 语句进行各种查询； 掌握INSERT 语句的用法。

实验内容：

用SQL 语句完成一下的要求：

1. 查询信息系（IS ）的所有学生信息

select * from student where sdept=’is ’

2. 查询选修了“数学”课的所有学生名单

Select s.sno,sname

From student s,course c,sc

Where s.sno=sc.sno and sc.cno=c.cno and cname=’数学’

3. 查询至少选修了一门其直接先行课为5号课程的学生的姓名。

Select sname

From student s, sc, course c

Where s.sno=sc.sno and sc.cno=c.cno and pcno=’5’

4. 查询全体学生的姓名和出生年份。

select sname,year(now())-sage as '出生年份' from student

5. 查询所有姓王的学生。

select *

from student where sname like '王%'

6. 查询选修了3号课程的学生姓名及成绩，并按成绩降序排序。

Select sname,grade

From student s, sc

Where s.sno=sc.sno and sc.cno=’3’

Order by grade desc

7. 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

Select *

From student

Order by sdept asc,age desc

8. 计算2号课程的平均成绩。

Select avg(grade)

From sc

Where cno=’2’

9. 查询选修了2号课程的学生的最高成绩。

select max(grade) from sc where cno='2'

10. 求各个课程号及相应的选课人数。

Select cno as 课程号,count(sno) as 人数

From sc

Group by cno

11. 查询至少选修了3门课程以上的学生学号。

se lect sno

from sc

group by sno

having count(*)>2

12. 查询“数据库”的间接先行课。

Select c3.cname

From course c1,course c2,course c3

Where c1.cpno=c2.cno and c1.cname=’数据库’ and c2.cpno=c3.cno

13. 查询平均成绩最高的学生的学号和姓名。

select top 1 sno,avg(grade)

from sc

group by sno

order by avg(grade) desc

14. 查询数学成绩最高的学生的学号和姓名。

select top 1 s.sno,sname,grade

from student s,course c, sc

where s.sno=sc.sno and c.cno=sc.cno and cname='数学'

order by grade desc

15. 查询出成绩最低学号最大的学生学号。

select top 1 sc.sno,grade

from sc

order by grade asc,sno desc

16. 查询成绩高于学生平均成绩的记录。

Select *

From sc

Where grade>(select avg(grade)

From sc )

17. 查询至少选修了1号课程和3号课程的学生学号。

Select sc1.sno

From sc sc1,sc sc2

Where sc1.sno=sc2.sno and sc1.cno=’1’ and sc2.cno=’3’

18. 查询只选修了1号课程和3号课程的学生学号。

select sno

from sc

where cno=’1’ and sno in(

select sno from sc

where cno=’3’)

and sno in(select sno from sc group by sno having count(cno)=2)

19. 查询没有选修1号课程的学生姓名。

Select distinct s.sname

From student s, sc

Where s.sno=sc.sno and sc.cno!='1'

20. 查询选修了全部课程的学生姓名。

Select sname

From student s

Where not exist (select *

From course c

Where not exist (select *

From sc

Where s.sno=sc.sno and sc.cno=c.cno))

21. 查询至少选修了95002所选修的全部课程的学生学号。

Select sc1.sno

From sc sc1

Where not exist (select *

From sc sc2

Where sc2.sno=’95002’ and

Not exist( select *

From sc sc3

Where sc2.cno=sc3.cno and sc1.sno=sc3.sno))

22. 查询没有不及格课程的学生的学号和姓名。

Select distinct sc.sno,s.sname

from sc,student s

where sc.sno=s.sno and not exists (select *

from sc sc2

where sc.sno=sc2.sno and sc2.grade

23. 查询没有不及格学生的课程的课程号和课程名。

Select distinct sc.cno,c.cname

from sc ,course c

where sc.cno=c.cno and not exists (select *

from sc sc2

where sc.cno=sc2.cno and sc2.grade

24. 建立信息系学生视图，并从视图中查询年龄最大的学生记录。

go

Create view is_student(sno,sname,sage)

as Select sno,sname,sage

From s

Where sdept='is'

Select max(sage)

From is_student

1. 用SQL 语句定义表student(sno,sname,ssex,sage，sdept) ，并加入如下约束： 主键：sno ；sname 有唯一约束；sname,ssex,sage 都不允许空；

create table student

(sno char(10) not NULL unique,

sname char(20) not NULL unique,

ssex char(2) not null,

sage int not null,

sdept char(20) not null,

primary key (sno)

)

2. 用SQL 语句定义表course(cno,cname,cpno,credit)，并加入如下约束：

主键：cno ；cname 不允许空；

create table course

(cno char(10) not NULL unique,

cname char(20) not NULL,

cpno char(10),

credit char(10),

primary key (cno)

)

3. 用SQL 语句定义表sc(sno,cno,cj)，并加入如下约束：

主键：sno,cno ；为sno 定义名为lsno 的默认参照完整性；为cno 定义名为lcno 的默认参照完整性；

create table sc

(sno char(10) not NULL,

cno char(10) not NULL,

grade int,

primary key (sno,cno),

constraint lsno foreign key (sno) references student(sno),

constraint lcno foreign key (cno) references course(cno)

);

4. 用SQL 语句向student 表输入如下元组：

('95001','李勇',' 男',20,'CS');

('95002','刘晨',' 女',21,'IS');

insert

into student

values ('95001','李勇',' 男',20,'CS');

另一组数据同上进行插入。

用SQL 语句向course 表输入如下元组：

('1','数据库','5',4);

('2','数学',NULL,2);

insert

into course

values ('1','数据库','5',4);

另一组数据同上进行插入。

用SQL 语句向sc 表输入如下元组：

('95001','1',92);

('95001','2',85);

('95002','2',90);

insert

into sc

values ('95001','1',92);

其它组数据同上进行插入。

5. 执行下列语句，并查看执行结果。如果不能正确执行给出错误原因。

insert into student values('95001','张力',' 男',20,'CS');

不能执行，student 中sno 属性为unique ，student 中已经有学号为95001的学生信息了，所以不能再插入相同学号的学生信息。

insert into student values('95003','李勇',' 男',20,'CS');

不能执行，student 中cname 属性为unique ，student 中已经有姓名为李勇的学生信息了，所以不能再插入相同姓名的学生信息。

insert into SC values('95004','1',92);

不能执行，根据参照完整性，在student 表中没有95004的信息，所以不能插入。 delete from student where sno='95001';

不能执行，因为在sc 表中有95001的信息。

update course set cno='3' where cno='2';

不能执行，因为sc 表中有cno=‘2’的信息。

6. 给student 表的ssex 列添加名为fm 的约束，使其取值只能取' 男' 或' 女' 。 alter table student

add constraint fm check (ssex in ('男',' 女'))

执行insert into student values('95005','张力','f',20,'CS') ，查看执行结果。 不能进行插入，因为，所输入的信息中性别必须是’男’或’女’。

7. 给student 表的sage 列添加约束，使其年龄不得超过20岁。查看约束是否能正确添加，并分析其原因。

alter table student

add constraint age check (sage

不能正确添加，ALTER TABLE 语句与 COLUMN CHECK 约束 'age' 冲突。该冲突发生于数据库 '学生信息' ，表 'student', column 'sage'，因为表数据有sage 〉20的信息。

8. 删除约束lsno 和lcno 。

alter table sc

drop constraint lsno,lcno

9. 为sc 表添加在列sno 上的外键约束lsno1，并定义为级联删除。执行delete from student where sno='95001';查看执行结果。

alter table sc

add constraint lsno1 foreign key (sno) references student(sno)

on delete cascade;

由于是级联删除，所以除student 表中学号为95001的学生的信息被删除外，其在sc 表中的信息也被删除了。

10. 为sc 表添加在列cno 上的外键约束lcno1，并定义为级联修改。执行update course set cno='3' where cno='2';查看执行结果。

alter table sc

add constraint lcno1 foreign key (cno) references course(cno)

on update cascade;

修改成功，且course 表和sc 表中cno=‘2’都被修改成了cno=‘3’。