//1.创建学生选课关系数据库中的STUDENT表（特别提示：表结构见1.3节学生选课关系数据库，使用课本上的表结构是错误的，后同）；
CREATE TABLE STUDENT
(SNO VARCHAR(9)PRIMARY KEY,
SNAME VARCHAR(9),
SSEX VARCHAR(20),
SAGE SMALLINT,
SDEPT VARCHAR(20));// table STUDENT 已创建。
//2.创建学生选课关系数据库中的COURSE表；
CREATE TABLE COURSE
(CNO VARCHAR(9) PRIMARY KEY,
CNAME VARCHAR(20),
CPNO VARCHAR(9),
CCREDIT SMALLINT);//table COURSE 已创建。
//3.创建学生选课关系数据库中的SC表；
CREATE TABLE SC
(SNO VARCHAR(9),
CNO VARCHAR(9),
GRADE INT);// table SC 已创建。
//4.运行下列语句，为基本表添加数据；
--以下为学生表的初始数据
insert into Student(sname,ssex,sno, sage, sdept) values('李勇','男','200215121',20,'CS');
insert into Student(sname,ssex,sno, sage, sdept) values('刘晨','女','200215122',19,'CS');
insert into Student(sname,ssex,sno, sage, sdept) values('王敏','女','200215123',18,'MA');
insert into Student(sname,ssex,sno, sage, sdept) values('张立','男','200215125',19,'IS');//1 行已插入。
//1 行已插入。
//1 行已插入。
//1 行已插入。
--以下为课程表的初始数据
insert into course(cno,cname,cpno,ccredit) values('6','数据处理',null,2);
insert into course(cno,cname,cpno,ccredit) values('2','数学',null,2);
insert into course(cno,cname,cpno,ccredit) values('7','PASCAL语言','6',4);
insert into course(cno,cname,cpno,ccredit) values('5','数据结构','7',4);
insert into course(cno,cname,cpno,ccredit) values('1','数据库','5',4);
insert into course(cno,cname,cpno,ccredit) values('3','信息系统','1',4);
insert into course(cno,cname,cpno,ccredit) values('4','操作系统','6',3);//1 行已插入。
//1 行已插入。
//1 行已插入。
//1 行已插入。
//1 行已插入。
//1 行已插入。
//1 行已插入。
--以下为选修表的初始数据
insert into sc(sno,cno,grade) values('200215121','1',92);
insert into sc(sno,cno,grade) values('200215121','2',85);
insert into sc(sno,cno,grade) values('200215121','3',88);
insert into sc(sno,cno,grade) values('200215122','2',90);
insert into sc(sno,cno,grade) values('200215122','3',80);//1 行已插入。
//1 行已插入。
//1 行已插入。
//1 行已插入。
commit;//5.修改Student表结构，为Student表格添加一个“入学时间”属性，属性名为Senrollment，各元组在属性Senrollment的值是多少；
ALTER TABLE Student
ADD Senrollment varchar(9);// 各元组在属性Senrollment的值是null
// table STUDENT已变更。
//6.修改Student表结构，把Ssex列的宽度设置为6个字节；
alter table Student alter column Ssex char(6);
alter table Student modify Ssex varchar(6);// table STUDENT已变更。
//7.修改Student表结构，删除Senrollment列（注：删除SYS模式下表的列将被拒绝）；
alter table Student
drop column Senrollment;// table STUDENT已变更。
//8.创建视图ds，该视图包含所有选修了“数据库”的学生信息（注：如果提示没有创建视图的权限，请使用管理员账户SYS或SYSTEM连接数据库并授予当前用户Create view权限）；
CREATE VIEW ds AS
select *from student
where student.sno in(select sno from sc where sc.cno in(select cno from course where cname ='数据库'))// view DS 已创建。
//9.创建视图maleStudent，该视图包含男学生所有信息，通过视图maleStudent更新基本表数据时必须保证学生性别为男；
create view maleStudent as select *from Student where ssex='男';// view MALESTUDENT 已创建。
//10.删除视图maleStudent；
drop view maleStudent
//11.为Course表的CName列建立唯一索引，索引名称为uniqueCname；是否能够为Course表的Cpno列建立唯一索引？为什么？
create UNIQUE index uniqueCname on course(CName);// unique index UNIQUECNAME 已创建。
不能，因为唯一索引要求值不能重复
//12.为Cource表的Cpno列建立普通索引，索引名称为indexCpno2；
create index indexCpno2 on course(cpno);// index INDEXCPNO2 已创建。
//13.删除索引indexCpno2；
drop index indexCpno2
// index INDEXCPNO2已删除。
//14.删除基本表Student，如果发生错误，请分析原因；
drop table student
//15.删除基本表SC；
drop table sc;// table STUDENT已删除。
// 实验内容第二部分（无需截图）
// 本部分实验采用项目信息管理关系数据库，实验前请在QQ群共享文件夹中下载文件“项目信息管理信息DDL和初始插入数据.txt”，该文件内部包括“项目信息管理关系数据库的DDL代码”和“项目信息管理关系数据库初始化数据代码”，
//用于建立实验所需基本表并插入初始化数据，后续实验也都采用项目信息管理关系数据库。
//1.查询系号为“d001”的所有教师的教工号、名称和工资；
select TNO,TNAME,TSALARY from teacher WHERE DNO ='d001'//2.查询工资在3000到5000之间的教师姓名、年龄（提示：可使用当前年份减去教师的出生年份，教师的出生年份可以使用函数extract(year from tbirthday)获取）；
select tname,2021-extract(year from tbirthday)from teacher where TSALARY >=3000and TSALARY<=5000//3.查询参加了项目的教工的编号，排除相同的元素；
select tno from teacher where teacher.tno in(select tno from MyProject);//4.查询名字中包含字“小”的教工姓名、出生日期；
select tname,tbirthday from teacher where tname like '%小%';//5.查询名字中第二个字为“小”的教工姓名、出生日期；
select tname,tbirthday from teacher where tname like '_小%';//6.查询所有不姓“李”、并且姓名为三个字的教工姓名、性别；
select tname,tbirthday from teacher where tname not like '李%'and length(tname)=3;//7.查询Department表有系主任的系号、系名称；
select *from Department where tno isnot null;//8.查询工资在4000以上或者性别为女的教师详细信息，按性别降序排列输出；
select *from teacher where tsalary >4000or tsex ='女' order by tsex desc;//9.查询参与了项目的教工总人数；
select count(DISTINCT tno)from myproject
//10.查询“张三”负责的项目数量；
select count(pno)from myproject where myproject.tno in(select tno from teacher where tname ='张三')11.查询所有教师的平均工资、工资总和、最高工资、最低工资；
select AVG(tsalary),sum(tsalary),max(tsalary),min(tsalary)from teacher;12.创建视图departmentSalary，查询各个系的教师的平均工资、工资总和、最高工资、最低工资；
create view departmentSalary (dno,avg_salary,sum_salary,max_salary,min_salary)as select dno,AVG(tsalary),sum(tsalary),max(tsalary),min(tsalary)from teacher GROUP BY dno order by dno;13.查询各个系的详细信息，包括各个系的教师的平均工资、工资总和、最高工资、最低工资(提示：可以使用department表与视图departmentSalary进行连接运算完成)；
select department.dname,departmentSalary.*from
department,departmentSalary where department.dno = departmentSalary.dno
14.查询教师平均工资大于4500的系号、系名称、平均工资（提示：要求不能使用视图departmentSalary，可把department与teacher连接后再进行分组，然后使用having子句对分组进行筛选）；
第一种写法：
select department.dno,dname,avg(tsalary)as 平均工资 from department,teacher
where department.dno=teacher.dno having avg(tsalary)>4500 group by department.dno,dname;
第二种：
select a.dno,b.dname,a.avg_salary from(select dno,AVG(tsalary)as avg_salary from teacher GROUP BY dno) a,department b where a.dno = b.dno and a.avg_salary >450015.查询教师参与项目的情况，列出教工号、姓名和项目名称，没有参与项目的教师也列出来（提示：用左外连接）；
select t.tno,t.tname,m.pname from teacher t
left join MyProject m
on m.tno=t.tno
order by tno
16.查询与“李小龙”工资相同的教师详细信息（要求分别使用自身连接、子查询两种查询方法完成）；
自身连接:
select a.*from teacher a,teacher b where a.tsalary=b.tsalary and b.tname='李小龙';
子查询:
select *from teacher where tsalary in(select tsalary from teacher where tname='李小龙');17.查询参与了“云计算研究”并且工资在4000以上的教师详细信息；
select t.*from teacher t,MyProject m where pname ='云计算研究'and tsalary >4000;18.查询小于或等于“同一系中教师平均工资”的教工号、姓名、年龄（提示：请参阅书本的“相关子查询”示例）；
select t.tno,t.tname,2021-extract(year from t.tbirthday)as age,t.tsalary as tyear from teacher t,(select dno,AVG(tsalary)as avf_salary
from teacher group by dno) c where c.dno = t.dno and t.tsalary <= avf_salary
19.查询比“计算机科学系”教师工资都高、并且不是“网络工程系”的教师信息；
select b.*from teacher b,(select avg(a.tsalary)as acg_salary from(select *from teacher t where t.dno in(select d.dno from department d where d.dname ='计算机科学系')) a) c
where b.tsalary > c.acg_salary and b.dno notin(select p.dno from department p where p.dname ='网络工程系')20.查询没有参与项目“p0001”的教工号、姓名；
select t.tno,t.tname from teacher t where t.tno notin(select m.tno from tm m where m.pno ='p0001');21.查询参与了所有项目的教师姓名；
select *from teacher t where t.tno in(select tno from(select tno,count(pno)as num from Tm group by tno) where num =4)22.查询工资大于3500或者在计算机科学系工作的教师详细信息（要求使用关键字UNION）；
select *from teacher where tsalary >3500
union select *from teacher where dno
in(select d.dno from department d where d.dname ='计算机科学系')23.查询工资大于3500并且不在计算机科学系工作的教师详细信息（要求使用关键字MINUS）；
select *from teacher where tsalary >3500
minus select *from teacher where dno
in(select d.dno from department d where d.dname ='计算机科学系')
实验内容第三部分（无需截图）
1.列出Teacher表的所有约束，并说明每个约束的具体含义及其对表列取值的影响；
select table_name,constraint_name,constraint_type from user_constraints where table_name like 'TEACHER';
TEACHER FK_TEACHER_BELONGTO_DEPARTME R
TEACHER SYS_C007581 C
TEACHER SYS_C007582 C
TEACHER SYS_C007583 C
TEACHER SYS_C007584 C
TEACHER SYS_C007585 C
TEACHER PK_TEACHER P
2.使用SQL语句在Teacher表中插入2条元组，元组内容任意设置，要求能取空值的列均设置为空（提示：如果插入失败，则查看是否满足基本表的约束条件）；
insert into teacher(tno, tname, tsex, tsalary, tbirthday, dno) values('t009','郑**','男', null, To_date('1-1月-2000','DD-mon-yyyy'),'d001');
insert into teacher(tno, tname, tsex, tsalary, tbirthday, dno) values('t011','是呆瓜','男', null, To_date('1-1月-2000','DD-mon-yyyy'),'d001');3.利用“create table teacher2 as select *from teacher”语句创建表teacher2，并列出Teacher2表的所有约束，比较Teacher2表与Teacher表的约束差异；
create table teacher2 as select *from teacher
select table_name,constraint_name,constraint_type from user_constraints where table_name like 'TEACHER2';
TEACHER FK_TEACHER_BELONGTO_DEPARTME R
TEACHER SYS_C007581 C
TEACHER SYS_C007582 C
TEACHER SYS_C007583 C
TEACHER SYS_C007584 C
TEACHER SYS_C007585 C
TEACHER PK_TEACHER P
TEACHER2 SYS_C007593 C
TEACHER2 SYS_C007594 C
TEACHER2 SYS_C007595 C
TEACHER2 SYS_C007596 C
TEACHER2 SYS_C007597 C
4.使用带子查询的插入语句把teacher表中的所有男教师插入到teacher2表中；
insert into teacher2 (select *from teacher where tsex ='男')5.为表Teacher添加check约束，使性别的取值只能为“男”或者“女”；
ALTER TABLE Teacher
ADD CHECK (tsex ='男'or tsex ='女')6.删除teacher2表中“计算机科学系”的所有教师；
delete from teacher2 where dno in(select d.dno from department d where d.dname ='计算机科学系')7.删除teacher2表中的所有教师；
delete from teacher2
8.修改teacher2表，使列tno为主码，主码约束名字为PK_teacher2；
alter table teacher2 add constraint PK_teacher2 primary key(tno);9.为teacher2表添加唯一约束，使tname的取值不能重复；
alter table teacher2 add constraint un_teacher2_tname unique(tname);10.修改teacher2表，使列dno成为外码，引用department表的主码dno，当删除department表中的元组时，级联删除Teacher2表中的元组（提示：删除并重新创建外码约束，使用ON DELETE CASCADE选项）；
alter table teacher2 add constraint fk_teacher2_to_department foreign key (dno)references department (dno) on delete cascade;11.在department表中插入一个新系，系号为“xyz”，在Teacher2表中为该新系添加两个教师信息；
INSERT INTO department VALUES('d004',null,'xyz');
INSERT INTO Teacher2 VALUES('t011','d004','是呆瓜','男', null, To_date('1-1月-2000','DD-mon-yyyy'));
INSERT INTO Teacher2 VALUES('t012','d004','小米','男', null, To_date('1-1月-2000','DD-mon-yyyy'));12.分别写出删除department表中系号为d001和xyz的记录的SQL语句并执行，比较并分析执行结果（提示：在Teacher表和Teacher2表中的外码定义是不同的）；
delete from department where dno ='d001'
删除department表中系号为d001会报错:违反完整约束条件
delete from department where dname ='xyz'13.在tm中插入一条元组，只设置tno、pno的值；
INSERT INTO tm(tno, pno) VALUES('t040','p0001');14.给teacher表中的所有教师的工资增加100；
update teacher
set tsalary = tsalary+10015.给teacher表中的“计算机科学系”教师的工资增加100；
update teacher
set tsalary = tsalary+100
where dno in(select d.dno from department d where d.dname ='计算机科学系')16.创建两个视图VT、VT2，两个视图均为包含所有teacher表的男教师的信息，但视图VT2的定义带有with check option选项，设置一条女教师信息记录，指出通过哪个视图可以成功插入记录，并说明with check option选项的作用；
create view VT as select *from teacher where tsex='男';
create view VT2 as select *from teacher where tsex='男'with check option;
insert into VT2(tno,tname,tsex,tsalary,tbirthday,dno) values('t100','小红','男',3000,To_date('20-10月-1985','DD-mon-yyyy'),'d001');
成功
insert into VT(tno,tname,tsex,tsalary,tbirthday,dno) values('t100','小红','男',3000,To_date('20-10月-1985','DD-mon-yyyy'),'d001');
失败
本文记录了进行数据库实验的初步体验，涵盖了实验的基本内容和初步学习心得。
扫一扫
9691
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
