数据库原理实验指导课堂使用的数据库表

create database studentregistrationsystem;

use [studentregistrationsystem];

create table srs_student (
             sno char(5) not null,
             sname varchar(10),
             ssex char(2),
             sbirth smalldatetime,
             sdept varchar(20),
             sparent varchar(20)
);

create table srs_teacher(
             tno int not null,
             tname char(10),
             tsex char(2)  default '男',
             tphone varchar(12),
             tdept varchar(20),
             taddr varchar(30),
             tprof varchar(10),
             tpay decimal(8,2),
             tweight smallint
);


create table srs_course(
             cno char(3) not null,
             cname varchar(20),
             cpno char(3),
             credit decimal(3,1)
);


create table srs_sc (
             sno char(5) not null,
             cno char(3) not null,
             tno char(5) not null,
             grade decimal(5,1),
);

/*表的主键在第二次实验统一添加*/
录入数据：
insert into srs_student values('06001','张睿智','男','1994-06-05 00:00:00','计算机',null);
insert into srs_student values('06002','李强','男','1992-11-13 00:00:00','计算机',null);
insert into srs_student values('06003','王士文','男','1994-07-08 00:00:00','外国语',null);
insert into srs_student values('06004','欧阳开元','男','1994-05-09 00:00:00','电子工程',null);
insert into srs_student values('06005','张明明','女','1994-03-20 00:00:00','电子工程',null);
insert into srs_student values('06006','陈东升','男','1993-06-09 00:00:00','计算机',null);
insert into srs_student values('06007','胡晓明','女','1993-07-21 00:00:00','电子工程',null);
insert into srs_student values('06008','乔羽','女','1994-11-10 00:00:00','外国语',null);
insert into srs_student values('06009','刘文红','女','1992-06-12 00:00:00','外国语',null);
insert into srs_student values('06010','高叔阳','男','1994-08-15 00:00:00','计算机',null);

insert into srs_course values(1,'数据库',5,4.0);
insert into srs_course values(2,'数学',NULL,2.0);
insert into srs_course values(3,'信息系统',1,4.0);
insert into srs_course values(4,'操作系统',6,3.5);
insert into srs_course values(5,'数据结构',7,4.0);
insert into srs_course values(6,'数据处理',NULL,2.0);
insert into srs_course values(7,'PASCAL语言',6,3.0);

insert into srs_teacher values(1,'刘明','男','13980234566','计算机','成都市洗面桥街23号','教授','5467.89','56');
insert into srs_teacher values(2,'王芳','女','13980280236','计算机','成都市武侯祠大街23号','副教授','4890.23','47');
insert into srs_teacher values(3,'李庆','男','13889701249','外国语','成都市浆洗街11号','讲师','3786.45','64');
insert into srs_teacher values(4,'曾书','男','18980027665','电子工程','成都市建设南路9号','副教授','4998.43','62');
insert into srs_teacher values(5,'杨晓','女','15065432168','外国语','成都市双楠街15号','副教授','4567.00','48');
insert into srs_teacher values(6,'刘晓','男','18980027366','电子工程','成都花园12-4-6','教授','5687.20',null);

insert into srs_sc values('06001',2,4,67.0);
insert into srs_sc values('06001',3,5,78.0);
insert into srs_sc values('06001',5,2,89.0);
insert into srs_sc values('06002',3,5,90.0);
insert into srs_sc values('06002',4,1,98.0);
insert into srs_sc values('06004',3,5,89.0);
insert into srs_sc values('06004',4,1,92.0);
insert into srs_sc values('06004',6,3,58.0);
insert into srs_sc values('06004',7,4,79.0);
insert into srs_sc values('06008',1,3,78.0);
insert into srs_sc values('06009',1,3,70.0);


实验二上机作业：
1.1 alter table srs_sc add constraint PK_SCT primary key(sno,cno,tno);
1.2 alter table srs_teacher alter column tno char(4);
1.3 alter table srs_student drop column sparent;
1.4 alter table srs_student add total decimal(4,1) default 0;

2.1 只有srs_sc表存索引，索引类型为聚集索引。
2.2 create clustered index Index_Cno on srs_course(cno);
2.3 create unique index Index_Sname on srs_student(sname desc);
2.4 create index Index_tx on srs_teacher(tname,tdept);
2.5 create index Index_Grade on srs_sc(grade desc);
2.6 drop index srs_teacher.Index_tx;

3.1  只有srs_sc表存在主键。
     alter table srs_teacher add primary key (tno);
     alter table srs_student add primary key (sno);
     alter table srs_course add primary key (cno);
3.2  alter table srs_sc add foreign key (sno) references srs_student (sno);
     alter table srs_sc add foreign key (cno) references srs_course (cno);
     alter table srs_sc add foreign key (tno) references srs_teacher (tno);
3.3  alter table srs_teacher add constraint Unique_TNP unique(tname,tphone);
3.4  alter table srs_student add constraint Df_sbirth default getdate() for sbirth;
3.5  alter table srs_sc add constraint Ck_grade check(grade>=0 AND grade<=100);


实验三上机作业：
select Tno,Tname,Tphone from Teacher

select * from Teacher where Tdept='计算机' AND Tprof='副教授'

select distinct Sname,Sdept from Student

select Tno 教师号,Tname 姓名,Tpay 工资,Tpay*0.95 '预发的95%工资' from Teacher

select * from Student where Sno like '06%' AND total>58 AND total<85 order by sno desc

select top 2  Sno,AVG(Grade) avgstu from SC group by Sno order by avgstu desc

select * from Course where Cno not in(1,4,7)

select * from Student where sname like '刘%'  or Sname like '张%' or Sname like '高%'

select Sdept,COUNT(*) from Student group by Sdept

select Cno,AVG(grade) agrade from SC group by Cno order by agrade desc

select Sno,COUNT(*) num from sc where grade>=90 group by Sno having COUNT(*)>=2

select cno,COUNT(*) num,AVG(grade) avgg,MAX(grade) maxx,MIN(grade) minn from SC group by Cno

select * from Course where Cpno is not null


实验四上机作业：
use JX
select Student.* from Student,SC where Cno=3 and Grade>=70 and Student.Sno=SC.Sno

select * from student where Sbirth < (select  min(Sbirth) from Student where Sdept='电子工程') AND Sdept not like '电子工程'

select Student.Sname,SC.Grade,Teacher.Tname from Student,SC,Teacher where SC.Cno=3 and Teacher.Tno=SC.Tno and Student.Sno=SC.Sno

select Cno,COUNT(Cno) number from SC group by Cno having COUNT(Cno)<15

select * from student where sno in(select Sno from SC group by Sno having COUNT(*)<3)

select * from student where not exists(select Sno from SC where Sno=Student.Sno)

select Student.Sname,Course.Cname,SC.Grade from Student,Course,SC where student.Sno in (select Sno from SC where Cno=3) and student.Sno in (select Sno from SC where Cno=4) and SC.Sno=Student.Sno and SC.Cno=Course.Cno

select * from srs_student where sno in (select s.sno from srs_sc s,srs_sc c where s.cno in (select cno from srs_course where cname='信息系统') and c.cno in (select cno from srs_course where cname='操作系统') and s.sno=c.sno)

select srs_student.*,srs_sc.* from srs_student left join srs_sc on srs_student.sno=srs_sc.sno

Select srs_student.sname,srs_sc.grade from srs_student,srs_course,srs_sc where srs_course.cno=srs_sc.cno and srs_student.sno=srs_sc.sno and srs_course.cname='数据库'

select sname '学生/老师' from srs_student  union select tname from srs_teacher

select c1.cname '课程名',c2.cname '间接先修课程名' from srs_course c1,srs_course c2 where c1.cpno=c2.cno

select srs_student.sno,srs_student.sname,srs_student.sdept from srs_student where not exists( select * from srs_sc where srs_student.sno=srs_sc.sno and cno=(select srs_course.cno from srs_course where cname='数据结构'));

需要注意的是：
如：查询任课门数低于2门的教师信息（需要考虑没有被选课的老师也属于子查询 用 not exists）
比较：
	查询所有姓“张”的学生的选课信息，包括学号、姓名、课程和成绩 。
      	查询所有姓“刘”的教师基本信息及其担任的课程名称（包括未任课的教师）。
集合查询：
	使用union的并查询----查询选修了“3”号或“4”号的学生学号。
	使用in的交查询-------查询同时选修了“3”号且“4”号课程的学生信息。
	使用not in的差查询---查询选修了“3”号课程而未选修“4”号课程的选课信息。

第五实验作业：
insert into srs_student(sno,sname,ssex,sdept) values('06011','王文娟','女','计算机');

insert into srs_student(sname,ssex,sdept) values('王娟','女','计算机');

消息515，级别16，状态2，第1 行
不能将值NULL 插入列'sno'，表'studentregistrationsystem.dbo.srs_student'；列不允许有Null 值。INSERT 失败。
语句已终止。
原因：主键值不能为空，因此，插入数据失败。

select * into CP_Student from srs_student where sno is null

insert into CP_Student(sno,sname,ssex,sbirth,sdept,total) select * from srs_student where sdept='计算机' or sdept='外国语'
update srs_teacher set tname='王芳龄' where tname='王芳'

update srs_teacher set tpay=tpay*1.02 where tprof='副教授'

update srs_sc set grade=grade+1 where sno in (select sno from srs_student where sname='李强'

update srs_sc set grade =case when srs_student.sdept='计算机' then grade*0.96 when srs_student.sdept='外国语' then grade+2 else grade end from srs_student where srs_student.sno=srs_sc.sno

update srs_course set credit=case when credit<3 then credit+0.4 when credit>3 then credit-0.5 when credit=3 then credit+0.2 end 

delete from srs_student where sno='06011'

delete from CP_Student where YEAR(sbirth)<=1987

delete from CP_Student 

select * into CP_Teacher from srs_teacher where tno is null

insert into CP_Teacher select * from srs_teacher where tprof='副教授'

insert into CP_Teacher select * from srs_teacher where tprof!='副教授'

Select into 和 insert into 的区别：前者是通过数据库里已存在的表创建一个结构一样或结构和数据都一样的新表，后者则是对已存在的表进行插入数据。