数据库上机题

第一次实验
要求：使用查询分析器建立图书、读者和借阅三个表，其结构为：
图书 Book （书号 Bno， 类别 Bclass， 出版社 Bpublish， 作者 Bauthor， 书名 Bname， 定价 Bprice，备注 Bremark）；
读者 Reader（编号 Rno，姓名 Rname，所在系 Rdept，性别 Rsex，电话 Rphone）；
借阅 Borrow（书号 Bno，读者编号 Rno，借阅日期 Lentdate）。

注意：在使用查询分析器新建查询时，将数据库对象选为刚才新建的数据库名。初学阶段，
每写完一条 SQL 语句，请先使用工具“分析”检查 SQL 语法是否正确，无误后再“执行”。
要求：① 对每个属性选择合适的数据类型；② 定义每个表的主码、是否允许空值和默
认值等列级数据约束；③实现相关约束：借阅表与图书表之间、借阅表与读者表之间的外码
约束；读者性别只能是“男”或“女”的约束（通过帮助自学）。

create table Book(
Bno varchar(20) primary key,
Bclass varchar(20) unique,
Bpublish varchar(20),
Bauthor varchar(20),
Bname varchar(20),
Bprice double,
Bremark varchar(20)
);

create table Reader(
Rno varchar(20) primary key,
Rname varchar(20),
Rdept varchar(20),
Rsex enum('男','女') not null,
Rphone varchar(20)
);

create table Borrow(
Bno varchar(20) ,
Rno varchar(20),
Lentdate date not null,
primary key (Bno,Rno),
foreign key(Bno) references Book(Bno),
foreign key(Rno) references Reader(Rno)
);

3、使用查询分析器修改表的结构。在“图书 Book”表中，增加两个字段，分别为“数量
Bnumber”和“购买日期 Bbuydate”。在“借阅”表中增加一个“还书日期 Returndate”字段。
4、用查询分析器为读者表按读者姓名升序建唯一索引，然后删除该索引（自学）。

alter table Book add Bnumber int;
alter table Book add Bbuydate date not null;
alter table Borrow add Returndate date not null;

建立索引
create unique index readerIndex on Reader(Rname);

删除索引
drop index Reader.readerIndex;

第二次实验：
1、建立书上的三个表并加入一定的数据

create database S_C;

create table Student(
Sno char(9) primary key,
Sname char(20) unique,
Ssex char(2),
Sage smallint,
Sdept char(20)
);

create table Course(
Cno char(4) primary key,
Cname char(40)
);

create table SC(
Sno char(9),
Cno char(4),
Grade smallint,
primary key (Sno,Cno),
foreign key (Sno) references Student(Sno),
foreign key (Cno) references Course(Cno)
);

加入数据
insert into Student values ('200215121','李勇','男',20,'CS');

insert into Course values ('1','shujuku','5',4);
insert into Course values ('2','shuxue',null,2);
insert in

to Course values ('3','xinxixitong','1',4);
insert into Course values ('4','caozuoxitong','6',3);
insert into Course values ('5','shujujiegou','7',4);
insert into Course values ('6','shujuchuli',null,2);
insert into Course values ('7','pascalyuyan','6',4);

insert into SC values ('200215121','1',92);
insert into SC values ('200215121','2',85);
insert into SC values ('200215121','3',88);
insert into SC values ('200215122','2',90);
insert into SC values ('200215122','3',80);


增删改查的Demo：


（ 1）查询张立的年龄。

select Sage from Student where Sname='zhangli';

（ 2）查询信息系所有年龄不大于 21 岁的男生情况。

select * from Student where Sdept='IS' and Sage<21 and Ssex='M';

（ 3）查询 1996 年出生的男生的姓名。

select Sname from Student where 2016-Sage=1996 and Ssex='M';

（ 4）查询信息系、数学系所有姓“王”的同学的姓名和年龄。

select Sname,Sage from Student where Sdept='MA' or Sdept='IS' and Sname like 'wang%';

（ 5）查询姓“王”的女同学的人数。

select count(*) from Student where Sname like 'wang%' and Ssex='F';

（ 6）查询 2 号课程的最低分。

select min(Grade) from SC where Cno='2';


（ 1）查询学号为 201215125 学生所学课程的课程名。

select Cname from Course where Cno IN (
select Cno from SC where Sno = '200215125'
);

（ 2）查询每一个同学的学号、姓名、选修的课程名及分数。
select
Student.Sno,Student.Sname,Cname,Grade
from
Student,Course,SC
where
https://www.360docs.net/doc/9d10973073.html,o=https://www.360docs.net/doc/9d10973073.html,o and SC.Sno = Student.Sno;

（ 3）查询选修了 4 个学分的课程的学生学号。

select distinct Sno from SC where Cno IN (
select Cno from Course where Ccredit=4
);

（ 4）查询刘晨同学不及格的课程名称。

select Cname from Course where https://www.360docs.net/doc/9d10973073.html,o IN (
select Cno from SC,Student where Student.Sname='wuzhe' and Student.Sno=SC.Sno and Grade<60
);

（ 5）求年龄大于女同学平均年龄的男学生姓名和年龄。

select Sname,Sage from Student where Ssex='M' and Sage>(
select AVG(Sage) from Student where Ssex='F'
);

（ 6）查询至少选修了 2 号课程和 3 号课程的学生姓名。

select Sname from Student where Sno In (
select Sno from SC where Cno = '2'
)
and Sname In (
select Sname from Student where Sno in (
select Sno from SC where Cno = '3'
)
);


（ 7）查询刘同学没选的课程的课程号。

select Cno from Course where not exists (
select * from SC where https://www.360docs.net/doc/9d10973073.html,o=https://www.360docs.net/doc/9d10973073.html,o and Sno IN (
select Sno from Student where Sname='liucheng'
)
);

（ 8）检索全部学生都选修的课程的课程号、课程名

select Cno,Cname from Course where not exists (
select * from SC C1 where not exists (
select * from SC C2 where
https://www.360docs.net/doc/9d10973073.html,o = https://www.360docs.net/doc/9d10973073.html,o and C1.Sno = C2.Sno
)
);

（ 1）查询以 6 号课程为先行课和学分为 4 的课程

select Cno,Cname from Course where Cpno='6' and Ccredit='4';

（ 2）查询选修了 1 号课程的学生学号与选修成绩不及格的学生学号的差集。

select Sno from SC where Cno='1' and Grade>=60;

（ 3）查询选修了 1 号课程并且选修了以 1 号课程为先行课的课程的学生学号。

select Sno from SC where Cno='1' and Sno in (select Sno from SC where Cno in (Select Cno from Course where Cpno='1'));

（1）统计每个同学的平均分，按平均分的降序排列。

select Sno,AVG(Grade) from SC group by Sno order by Grade Desc;

（2）查询总分最高的学生的学号。

select Sno from SC GROUP BY Sno having SUM(Grade)>=ALL (
select SUM(Grade) from SC group by Sno
);

（3）查询总学分已超过 6 学分的学生学号、总学分。

select Sno,SUM(Ccredit) from SC,Course where https://www.360docs.net/doc/9d10973073.html,o = https://www.360docs.net/doc/9d10973073.html,o GROUP by Sno having SUM(Ccredit)>6;

（4）求各门课程的课程名及相应的选课人数。

select Cname,COUNT(https://www.360docs.net/doc/9d10973073.html,o) from Course,SC where https://www.360docs.net/doc/9d10973073.html,o=https://www.360docs.net/doc/9d10973073.html,o group by Cname;

（5）统计有学生选修的课程门数。

select COUNT(Cno) from Course where exists (
select * from SC where https://www.360docs.net/doc/9d10973073.html,o=https://www.360docs.net/doc/9d10973073.html,o
);

（6）求选修 1 号课程的学生的平均年龄。

select AVG(Sage) from Student,SC where Student.Sno=SC.Sno and https://www.360docs.net/doc/9d10973073.html,o='1';


（7）统计每门课程的学生选修人数(超过 2 人的课程才统计)。要求输出课程号和选修人
数，查询结果按人数降序排列，若人数相同，按课程号升序排列。

select Cno,COUNT(Cno) from SC group by Cno having COUNT(Cno)>=2 order by COUNT(Sno) desc,Cno;


1、查询所有选修了一号课程的学生姓名

select Sname from Student where
exists (
select * from SC where Cno='1' and Student.Sno=SC.Sno
);

2、查询没有选修一号课程学生姓名

select Sname from Student where
not exists (
select * from SC where Cno='1' and Student.Sno=SC.Sno
);

3、查询选修了所有课程的学生

select Sname from Student where
not exists (
select * from Course where
not exists (
select * from SC where
SC.Sno = Student.Sno and
https://www.360docs.net/doc/9d10973073.html,o = https://www.360docs.net/doc/9d10973073.html,o
)
);

4、查询至少选修了学生200215122选修的全部课程的学生号码

select Distinct Sno from SC C3 where
not exists (
select * from SC C1 where C1.Sno='200215128' and
not exists (
select * from SC C2 where
C2.Sno = C3.Sno and
https://www.360docs.net/doc/9d10973073.html,o = https://www.360docs.net/doc/9d10973073.html,o
)
);


(1) 在 SC 表中删除尚无成绩的选课元组。

delete from SC where Grade is null;

(2) 把张同学的选课和成绩全部删去。

delete from SC where Sno in (
select Sno from Student where Sname like 'zhang%'
);

(3) 删除“戴娆”同学的所有信息。

delete from SC where Sno in (
select Sno from Student where Sname = 'dairao'
);

delete from Student where Sname='dairao';

(4) 把选修数学课不及格的成绩全改为空值。

update SC set Grade = null where Grade<60 and Cno in (
select Cno from Course where Cname='shuxue'
);


(5) 把低于总平均成绩的女同学成绩提高 5%。

update SC set Grade = Grade*1.05 where Grade < (
select AVG(Grade) from (select * from SC) t2
) and Sno in (
select Sno from Student where Ssex='F'
);


（ 1）为信息系所有男生建一视图 IS_View。

create view IS_View AS
select * from Student where Ssex='M' and Sdept='IS';

（ 2）建立一个视图 S_G_View，包含学生学号及其选修课程的平均成绩。

create view S_G_View(Sno,Gavg) as
select Sno,AVG(Grade) from SC group by Sno;

（ 3）建立一个视图 CS_View，包含姓名、课程名、学分和成绩。

create view CS_View(Sname,Cname,Ccredit,Grade) as
select Sname,Cname,Ccredit,Grade from Student,Course,SC
where Student.Sno=SC.Sno and https://www.360docs.net/doc/9d10973073.html,o=https://www.360docs.net/doc/9d10973073.html,o;

（ 1）利用视图 IS_View 查询信息系“吴哲”同学的基本信息。

select * from IS_View where Sname = 'wuzhe';

（ 2）利用视图 S_G_View 查询平均成绩在 80 分以上的学生的学号及其平均成绩。

select Sno,Gavg from S_G_View where Gavg>=80;

（ 3）利用视图 CS_View 查询选修 6 个以上的学分且成绩及格的学生姓名及相应的学分。

select Sname,SUM(Ccredit) from CS_View where Grade>=60 group by Sname having SUM(Ccredit)>=6 ;

（ 1）通过视图 IS_View 将学号为“ 200215125”的学生姓名改为“张强”。

update IS_View set Sname='zhangqiang' where Sno = '200215125';

（ 2）通过视图 CS_View 将所有学生选修信息系统课程的成绩提高 10%。

update CS_View set Grade = Grade*1.1 where Cname = 'xinxixitong';

删除建立的所有视图

drop view IS_View,S_G_View,CS_View;