数据库上机实验五

实验五SQL语言综合实验
一、实验目的
利用SQL语言的9个动词以及相关语法解决综合问题；体会SQL语言在数据库系统设计、实现过程中的作用。

二、实验内容及步骤
特别说明：实验过程中所有的SQL语句的都需要保存到一个word文件或文本文档中，实验结束前需将SQL语句粘贴到网络课程中提交；数据库文件需分离后压缩提交FTP。

以下操作均在查询分析器中实现：
1、建立以dbszh+“学号”命名的数据库，如dbszh101 //设1班01号学生。

2、在第一步建立的数据库中建立S，C，SC基本表
❖S(sno,sname,sex,bdate,height) 030920311 王义平男1984-08-20 1.71 ❖C(cno,lhour,credit,semester) cs-201 80 4 秋
❖Sc(sno,cno,grade) 030920311 cs-201 80.0
各字段数据类型请参考示例元组设定，定义S表的主码为sno,定义C表的主码为cno，定义SC表的主码为(sno,cno),定义sno为SC表的外码，定义cno为SC表的外码。

3、修改基本表
(1)在C表中增加一个新的属性“cname”，char(8)型，允许为空值。

(2)将C表中的cname属性类型改为char(20)型。

(3)删除C表中的cname属性。

4、建立索引
(1)在SC表的多个字段建立索引，以“INDEX+自己的学号”命名索引名（如：index101），先按学号升序索引，然后按成绩降序索引。

(2)删除刚才建立的索引。

5、更新数据
（1）利用INSERT语句插入元组：
S表中：
030920311 王义平男1984-08-20 1.71
050620312 张三男1984-04-20 1.9
“你的学号”“你的姓名”“你的性别”“你的出生日期”“你的身高”
C表中：（课程号cno值的前两位表示开课系）
cs-201 80 4 秋
cs-202 60 4 春
dj-101 30 2 秋
Sc表中：
030920311 cs-201 75
“你的学号”cs-202 68
“你的学号”dj-101
（2）利用UPDATE修改元组
将SC表中你自己的成绩都改为90分。

6、查询
（1）查询选修计算机系秋季所开课程的男生姓名、课程号、学分数、成绩（2）查询至少选修一门电机系课程的女生的姓名
（3）查询每位学生已选课程的门数和总平均成绩
（4）查询缺成绩的学生的姓名、缺成绩的课程号及其学分数
7、视图
（1）创建视图，以“V+你的学号”命名（如：V101），提供计算机系秋季所开课程的课程号和学分数。

（2）利用第1步创建的视图和给定的3个基本表，查询出选修计算机系秋季所开课程的男生的姓名、课程号、学分数、成绩。

8、删除
（1）删除你自己的所有选课记录。

（2）删除SC表。

--实验五
--1
create database dbszh113
--2
--s表
create table s
(sno char(10)primary key,
sname char(20),
sex char(2)check(sex in('男','女')), bdate datetime,
height float
);
--c表
create table c
(cno char(10)primary key,
lhour int,
credit int,
semester char(4)
);
--sc表
create table sc
(sno char(10),
cno char(10),
grade float,
primary key(sno,cno),
foreign key(sno)references s(sno), foreign key(cno)references c(cno) );
--3
--1
alter table c
add cname char(8);
--2
alter table c
alter column cname char(20);
--3
alter table c
drop column cname;
--4
--1
create unique index index113 on sc(sno asc,cno desc);
--2
drop index index113 on sc ;
--5
--1.1
insert
into s
values('030920311','王平义','男','1984-08-20',1.71);
insert
into s
values('050620312','张三','男','1984-04-20',1.9);
insert
into s
values('110303113','叶惠','女','1992-02-01',1.69);
--1.2
insert
into c
values('cs-201','80','4','秋');
insert
into c
values('cs-202','60','4','春');
insert
into c
values('dj-101','30','2','秋');
--1.3
insert
into sc
values('030920311','cs-201','75');
insert
into sc
values('110303113','cs-202','68');
insert
into sc
values('110303113','dj-101',null);
--3
update sc
set grade=90
where sno='110303113';
--6
--1
select sname,o,credit,grade
from s,c,sc
where s.sno=sc.sno and o=o
and o like'cs%'and semester='秋'and sex='男';
--2
select sname
from s,sc
where sc.sno=s.sno and o like'dj%'and sex='女';
--3
select sno,count(cno),avg(grade)
from sc
group by sno;
--4
select sname,o,credit
from s,c,sc
where s.sno=sc.sno and o=o
and grade is null;
--7
--1
create view v113
as
select cno,credit
from c
where cno like'cs%'and semester='秋';
--2
select sname,o,v113.credit,grade
from s,sc,v113
where s.sno=sc.sno and o=o and sex='男';
--8
--1
delete
from sc
where sno='110303113';
--2
drop table sc;。