数据库实验及答案

实验二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 https://www.360docs.net/doc/6413656175.html,o=https://www.360docs.net/doc/6413656175.html,o and cname=’数学’

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

Select sname

From student s, sc, course c

Where s.sno=sc.sno and https://www.360docs.net/doc/6413656175.html,o=https://www.360docs.net/doc/6413656175.html,o 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 https://www.360docs.net/doc/6413656175.html,o=’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 https://www.360docs.net/doc/6413656175.html,ame

From course c1,course c2,course c3

Where c1.cpno=https://www.360docs.net/doc/6413656175.html,o and https://www.360docs.net/doc/6413656175.html,ame=’数据库’ and c2.cpno=https://www.360docs.net/doc/6413656175.html,o 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 https://www.360docs.net/doc/6413656175.html,o=https://www.360docs.net/doc/6413656175.html,o 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 https://www.360docs.net/doc/6413656175.html,o=’1’ and https://www.360docs.net/doc/6413656175.html,o=’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 https://www.360docs.net/doc/6413656175.html,o!='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 https://www.360docs.net/doc/6413656175.html,o=https://www.360docs.net/doc/6413656175.html,o)) 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 https://www.360docs.net/doc/6413656175.html,o=https://www.360docs.net/doc/6413656175.html,o 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<60)

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

Select distinct https://www.360docs.net/doc/6413656175.html,o,https://www.360docs.net/doc/6413656175.html,ame

from sc ,course c

where https://www.360docs.net/doc/6413656175.html,o=https://www.360docs.net/doc/6413656175.html,o and not exists (select *

from sc sc2

where https://www.360docs.net/doc/6413656175.html,o=https://www.360docs.net/doc/6413656175.html,o and sc2.grade<60)

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 <20)

不能正确添加，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’。