数据库系统第三章作业

数据库系统第三章作业

一、设有3个关系：
S(S#,SNAME,SEX,AGE,sdept)
SC(S#,C#,GRADE)
C(C#,CNAME,TEACHER)
用数据定义语言DDL定义上述三个表，请为每个属性定义适当的数据类型，宽度（和精度），每个表上的主键、外键以及恰当的数据完整性约束。

⑴、创建学生表s（4分）
Create table s(
S# char(10) primary key,
sname varchar(8) not null,
sex char(2),
age int
sdept varchar(30));

⑵、创建课程表C （4分）
Create table C(
C# char(4) primary key,
Cname varchar(20) not null,
TEACHER varchar(8) not null);

⑶、创建选课表sc （4分）
Create table sc(
S# char(10),
C# char(4),
Grade int,
primary key(s#,c#),
foreign key s# references s(s#),
foreign key c# references c(c#));

二、在一题的基础上，用SELECT语句完成下列（1）~（12）查询。

（1）检索LIU老师所授课程的课程号和课程名；
select c#,cname from C
where teacher='LIU';

（2）检索年龄大于23的男同学的学号和姓名；
select s#,sname from S
where sex='男' and age>23;

（3）检索学号为S3的学生所学课程的课程名与任课老师；
select cname,teacher from C
where c# in(select c# from SC where s#='S3');
或者：
select cname,teacher from C,sc
where c.c#= s.c# and sc.s#='S3');

（4）检索至少选修LIU 老师所授课程中一门课程的女学生的姓名；
select sname from S
where sex='女' and s# in(select distinct s# from SC
                      where c# in(select c# from C
                            where teacher='LIU'));
或者：
select sname 
from S,SC,C
where sex='女' and S.s# =SC. s# AND SC.C#=C.C# AND teacher='LIU';

（5）检索WANG同学不学课程的课程号；
select distinct c# 
from C 
where c# not in(select distinct c# from sc
where s# in (select s# from s where sname='WANG'));

（6）检索至少选修2门课的学生的学号；
select distinct a.s# 
from SC as a,SC as b
where a.s#=b.s# and a.c#!=b.c#;
或者：
select distinct s# 
from SC 
group by s# having count(c#)>=2;

（7）检索全部学生都选修的课程的课程号和课程名；
select c#,cname 
from c
where not exists(select * from s where not exists(
        select * from sc where s#=s.s# and c#=c.c#);
或者：
select c#,cname 
from c
where c# in(select c# from sc
        group by c# 
        having count(s#)=(select count(*) from s); 
        
（8）检索选修课程包含LIU老师所授全部课程的学生的学号；
select distinct s# 
from sc a
where not exists(select * from c where teacher='LIU' 
                 and not exists(select * from sc as b
                     where b.s#=a.s# and b.c#=c.c#));

（9）检索所有姓张的学生；
SELECT * 
FROM S 
WHERE SNAME LIKE '张%';

（10）查询每个学生的平均成绩；
SELECT S#,AVG(GRADE) 
FROM SC 
GROUP BY S#;

（11）查询平均成绩大于等于80分的学生的学号和姓名。
SELECT S#,SNAME
FROM S
WHERE S# IN(SELECT S# FROM SC
           GROUP BY S# HAVING AVG(GRADE)>=80);
           
（12）查询平均成绩第一名的学生的学号和姓名。
SELECT S#,SNAME
FROM S
WHERE S# IN (SELECT TOP 1 S# FROM SC
           GROUP BY S#
           ORDER BY AVG(GRADE) DESC);

三、在一题的基础上，创建下列视图（1）~（4）
（1）请创建选修LIU老师所带课程的学生视图，视图包括：学号、姓名、性别、课程号、课程名、成绩。
CEREATE VIEW V_LIU(学号,姓名,性别,课程号,课程名,成绩)
AS
SELECT S.S#,SNAME,SEX,C#,CNAME,GRADE
FROM S,SC,C
WHERE S.S#=SC.S# AND SC.C#=C.C# AND TEACHER='LIU';

（2）创建所有男生的视图。
CREATE VIEW V_MALE
AS
SELECT * FROM S
WHERE SEX=’男’;

（3）创建所有学生的选课视图，视图包括：学号、姓名、性别、课程号、课程名、成绩。
CEREATE VIEW V_LIU(学号,姓名,性别,课程号,课程名,成绩)
AS
SELECT S.S#,SNAME,SEX,C#,CNAME,GRADE
FROM S,SC,C
WHERE S.S#=SC.S# AND SC.C#=C.C#

4）创建学生平均成绩的视图，视图包括：学号、姓名和平均成绩。
Create VIEW V_AVG(学号,姓名,平均成绩)
As
Select s.s#,sname,avg(grade)
From s,sc
Where s.s#=sc.s#
Group by s.s#,sname;

四、在一题的基础上，用数据操纵语言DML（INSERT,UPDATE,DELETE)完成下列（1）~（5）命令。

（1）把所有学生的年龄增加1岁；
Update s
Set age=age+1;

（2）把所有选修LIU老师所带课程的学生成绩置零；
Update sc
Set grade=0
Where c# in(select c# from c where teacher='LIU');

3）删除所有女生的选课记录；
DELETE FROM SC
WHERE S# IN(SELECT S# FROM S
             WHERE SEX='女');
             
（4）删除全部的学生记录；（请注意外键关系）
--先删除子表SC表
Delete from sc
--然后删除主表S
DELETE FROM S;

（5）向学生表S插入一条记录。
insert into s values('1006','黎明','男',20);