数据库实验及答案.doc

实验二 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 o=o and cname=’数学’
3.查询至少选修了一门其直接先行课为5号课程的学生的姓名。
Select sname
From student s, sc, course c
Where s.sno=sc.sno and o=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 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门课程以上的学生学号。
select sno
from sc
group by sno
having count(*)>2
12.查询“数据库”的间接先行课。
Select ame
From course c1,course c2,course c3
Where c1.cpno=o and ame=’数据库’ and c2.cpno=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 o=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 o=’1’ and o=’3’
18.查询只选修了1号课程和3号课程的学生学号。
select sno
from sc
where cno=’1’ and sno in(
select sno from sc
where cno=’3’)
and sno inselect 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 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 o=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 o=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 o,ame
from sc ,course c
where o=o and not exists (select *
from sc sc2
where o=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’。