数据库系统概论(第四版)-第三章全部实验

实验一【附】1、定义一个学生-课程模式S-T create schema”s-t”authorization WANG(若上句没有模式名”s-t”，则默认为WANG) 2、删除模式ZHANGdrop schema ZHANG cascade(cascade级联:对象全删；restrict限制)1.建立学生-课程数据库s_t;create database s_t2.在数据库s_t下新建三张表：Student、Course、SC。

(1)学生表：Student(Sno，Sname，Sex，Sage，Sdept)。

Student由学号(Sno)、姓名(Sname)、性别(Sex)、年龄(Sage)、所在系(Sdept)五个属性组成，其中Sno为主键。

create table Student(Sno char(9)primary key not null,Sname char(20)unique not null,Ssex char(2)not null,Sage smallint not null,Sdept char(20)not null)Insert into Student(Sno,Sname,Ssex,Sage,Sdept) values('200215121','李勇','男',20,'CS')insert into Student(Sno,Sname,Ssex,Sage,Sdept) values('200215122','刘晨','女',19,'CS')insert into Student(Sno,Sname,Ssex,Sage,Sdept) values('200215123','王敏','女',18,'MA')insert into Student(Sno,Sname,Ssex,Sage,Sdept) values('200215125','张立','男',19,'IS')(2)课程表：Course(Cno，Cname，Cpno,Ccredit) Course课程号(Cno)、课程名(Cname)、先行课的课程号(Cpno)、学分(Ccredit)四个属性组成，其中Cno为主键。

其中Cpno参考了本表的Cno字段的值，Cno为主键create table course(cno char(40)primary key not null,cname char(4)not null,ccredit smallint not nullforeign key(Cpon)references course(Cno)) insert into Course values('1','数据库','5',4) insert into Course values('2','数学','',2)insert into Course values('3','信息系统','1',4) insert into Course values('4','操作系统','6',3) insert into Course values('5','数据结构','7',4)insert into Course values('6','数据处理','',2) insert into Course values('7','PASCAL语言','6',4)学生选课表：SC(Sno，Cno，Grade)SC由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成，其Sno、Cno的组合为主键。

create table SC(Sno char(9)not null,cno char(4)not null,Grade smallint not null)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('200215121','3',80))3基本表的修改：a）向Student表增加“入学时间”列，期数据类型为日期型b)将年龄的数据类型由字符型改为整型c)增加课程名称必须取唯一值的约束条件alter table studebt add s_entrance datetime alter table student alter column sage intalter table course add unique(cname)实验二1.查询全体学生的学号和姓名select sno,snamefrom student2.查询全体学生的详细记录select*from student3.查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示所有系名。

select sname,'year of birth:',2011-sage,lower( sdept)from student4．查询选修了课程的学生学号，去掉结果中的重复行。

select distinct snofrom sc5．查询所有年龄在20~23岁之间的学生姓名、系别和年龄。

select sname,sdept,sagefrom studentwhere sage not between20and236．查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别。

select sname,ssexfrom studentwhere sdept in('cs','ma','is')7．查询所有姓刘的学生的姓名\学号和性别。

select sname,sno,ssexfrom studentwhere sname like'刘%’8．查询DB_Design课程的课程号和学分。

selcet cno,ccreditfrom coursewhere cname like'DB\_design'escape'\'9．查询计算机科学系年龄在20岁以下的学生姓名。

select sname,sagefrom studentwhere sdept='cs'and sage<2010.查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列select sno,gradefrom scwhere cno='3'order by grade desc11.计算1号课程的学生最高分数。

select max(grade)from scwhere cno='1'12.查询选修了2门以上课程的学生学号。

select snofrom scgroup by snohaving count(*)>213.查每个学生的情况以及他所选修的课程select student.*,sc.*from student,scwhere sc.sno=student.sno14.求学生的学号、姓名、选修课程及成绩select student.sno,sname,cname,gradefrom student,sc,coursewhere sc.sno=student.sno and o=course .cno15.求选修课程号为2且成绩在90以上的学生学号、姓名和成绩。

select student.sno,sname,gradefrom student,scwhere sc.sno=student.sno and sc.sno='2'and sc.grade>9016.查询每一门课程的间接先行课（即先行课的先行课）select o,second.cpnofrom course first,course secondwhere frist.cpno=o实验三1.查询与“刘晨”在同一个系学习的学生select sno,sname,sdeptfrom studentwhere sdept in(select sdeptfrom studentwhere sname='刘晨')2.查询选修了课程名为IS的学生学号和姓名select sno,snamefrom studentwhere sno in(select snofrom scwhere cno in(select cnofrom coursewhere cname='IS'))3.找出每个学生超过他选修课程平均成绩的课程号select sno,cnofrom sc xwhere grade>=(select avg(grade)from sc ywhere y.sno=o)4.查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄select sname,sagefrom studentwhere sage<ANY(select sagefrom studentwhere sdept='CS')and sdept<>'cs'5.查询所有选修了1号课程的学生姓名select snamefrom studentwhere exists(select*from scwhere sno=student.sno and cno='1')6.查询选修了全部课程的学生姓名select snamefrom studentwhere not exists(select*from coursewhere not exists(select*from scwhere sno=student.snoand cno=o))实验四1将一新学生记录(学号：95050；姓名：陈冬；性别：男；年龄：18；所在系：计算机科学系)插入学生表中。