数据库实验二（ 数据查询 ）

3.1 项目信息管理数据库 DDL(Oracle SQL)alter table Departmentdrop constraint FK_DEPARTME_管理_TEACHER;alter table MyProjectdrop constraint FK_MYPROJEC_PP_MYPROJEC;alter table MyProjectdrop constraint FK_MYPROJEC_PROJECTMA_TEACHER;alter table TMdrop constraint FK_TM_TM_TEACHER;alter table TMdrop constraint FK_TM_TM2_MYPROJEC;alter table Teacherdrop constraint FK_TEACHER_BELONGTO_DEPARTME;drop index 管理_FK;drop table Department cascade constraints;drop index PP_FK;drop index ProjectManager_FK;drop table MyProject cascade constraints;drop index TM2_FK;drop index TM_FK;drop table TM cascade constraints;drop index BelongTo_FK;drop table Teacher cascade constraints;/*==============================================================*//* Table: Department *//*==============================================================*/create table Department(DNO VARCHAR2(10) not null,TNO VARCHAR2(10),DName NVARCHAR2(20) not null,constraint PK_DEPARTMENT primary key (DNO));/*==============================================================*//* Index: 管理_FK *//*==============================================================*/create index 管理_FK on Department (TNO ASC);/*==============================================================*//* Table: MyProject *//*==============================================================*/create table MyProject(PNO VARCHAR2(10) not null,ParentPNO VARCHAR2(10),TNO VARCHAR2(10) not null,PName NVARCHAR2(30) not null,PFund NUMBER(20,2),PBeginDate DATE not null,PTimeSpan INTEGER not null,constraint PK_MYPROJECT primary key (PNO));/*==============================================================*//* Index: ProjectManager_FK *//*==============================================================*/create index ProjectManager_FK on MyProject (TNO ASC);/*==============================================================*//* Index: PP_FK *//*==============================================================*/create index PP_FK on MyProject (ParentPNO ASC);/*==============================================================*//* Table: TM *//*==============================================================*/create table TM(TNO VARCHAR2(10) not null,PNO VARCHAR2(10) not null,WorkDays INTEGER,constraint PK_TM primary key (TNO, PNO));/*==============================================================*//* Index: TM_FK *//*==============================================================*/create index TM_FK on TM (TNO ASC);/*==============================================================*//* Index: TM2_FK */41/*==============================================================*/create index TM2_FK on TM (PNO ASC);/*==============================================================*//* Table: Teacher *//*==============================================================*/create table Teacher(TNO VARCHAR2(10) not null,DNO VARCHAR2(10) not null,TName NVARCHAR2(20) not null,TSex VARCHAR2(3) not null,TSalary NUMBER(30,2),TBirthday DATE not null,constraint PK_TEACHER primary key (TNO));/*==============================================================*//* Index: BelongTo_FK *//*==============================================================*/create index BelongTo_FK on Teacher (DNO ASC);alter table Departmentadd constraint FK_DEPARTME_管理_TEACHER foreign key (TNO)references Teacher (TNO);alter table MyProjectadd constraint FK_MYPROJEC_PP_MYPROJEC foreign key (ParentPNO)references MyProject (PNO);alter table MyProjectadd constraint FK_MYPROJEC_PROJECTMA_TEACHER foreign key (TNO)references Teacher (TNO);alter table TMadd constraint FK_TM_TM_TEACHER foreign key (TNO)references Teacher (TNO);alter table TMadd constraint FK_TM_TM2_MYPROJEC foreign key (PNO)references MyProject (PNO);alter table Teacheradd constraint FK_TEACHER_BELONGTO_DEPARTME foreign key (DNO)references Department (DNO);3.2 项目信息管理数据库初始化数据--系表数据insert into department(dno,dname) values('d001', '计算机科学系');insert into department(dno,dname) values('d002', '网络工程系');insert into department(dno,dname) values('d003', '软件工程系');--教师表数据insert into teacher(tno, tname, tsex, tsalary, tbirthday, dno) values('t001', '张三', '男', 3000,To_date('7-7-1977', 'DD-mm-yyyy'), 'd001');insert into teacher(tno, tname, tsex, tsalary, tbirthday, dno) values('t002', '李四', '女', 3600,To_date('21-10-1979', 'DD-mm-yyyy'), 'd001');insert into teacher(tno, tname, tsex, tsalary, tbirthday, dno) values('t003', '王五', '女', 5600,To_date('7-7-1981', 'DD-mm-yyyy'), 'd002');insert into teacher(tno, tname, tsex, tsalary, tbirthday, dno) values('t004', '刘晨', '女', 5800,To_date('7-7-1985', 'DD-mm-yyyy'), 'd002');insert into teacher(tno, tname, tsex, tsalary, tbirthday, dno) values('t005', '王二小', '男',3500, To_date('7-7-1981', 'DD-mm-yyyy'), 'd003');insert into teacher(tno, tname, tsex, tsalary, tbirthday, dno) values('t006', '李小龙', '男',5687, To_date('7-12-1990', 'DD-mm-yyyy'), 'd003');insert into teacher(tno, tname, tsex, tsalary, tbirthday, dno) values('t007', '熊猫', '男', 6000,To_date('27-11-1980', 'DD-mm-yyyy'), 'd003');insert into teacher(tno, tname, tsex, tsalary, tbirthday, dno) values('t008', '李小小', '女',5687, To_date('17-10-1985', 'DD-mm-yyyy'), 'd001');--myproject 数据insert into myproject(PNO, pname, pfund, pbegindate, ptimespan, parentpno, tno)values('p0001', '信息安全技术研究', 30, To_date('7-12-2012', 'DD-mm-yyyy'), 3, null,'t001');insert into myproject(PNO, pname, pfund, pbegindate, ptimespan, parentpno, tno)values('p0002', '云计算研究', 40, To_date('7-12-2008', 'DD-mm-yyyy'), 4, null, 't003');insert into myproject(PNO, pname, pfund, pbegindate, ptimespan, parentpno, tno)values('p0003', '信息中心网络研究', 60, To_date('7-12-2012', 'DD-mm-yyyy'), 6, null,'t006');insert into myproject(PNO, pname, pfund, pbegindate, ptimespan, parentpno, tno)values('p0004', '对等网络研究', 30, To_date('7-12-2010', 'DD-mm-yyyy'), 3, 'p0002','t006');--tm 数据insert into tm(tno, pno, workdays) values('t001', 'p0001', 180);insert into tm(tno, pno, workdays) values('t001', 'p0002', 180);insert into tm(tno, pno, workdays) values('t001', 'p0004', 360);insert into tm(tno, pno, workdays) values('t001', 'p0003', 540);insert into tm(tno, pno, workdays) values('t002', 'p0001', 720);insert into tm(tno, pno, workdays) values('t002', 'p0002', 90);insert into tm(tno, pno, workdays) values('t002', 'p0004', 90);insert into tm(tno, pno, workdays) values('t003', 'p0001', 180);insert into tm(tno, pno, workdays) values('t003', 'p0002', 360);insert into tm(tno, pno, workdays) values('t003', 'p0003', 360);insert into tm(tno, pno, workdays) values('t003', 'p0004', 360);insert into tm(tno, pno, workdays) values('t004', 'p0001', 180);insert into tm(tno, pno, workdays) values('t004', 'p0002', 180);insert into tm(tno, pno, workdays) values('t004', 'p0003', 360);insert into tm(tno, pno, workdays) values('t005', 'p0004', 180);insert into tm(tno, pno, workdays) values('t005', 'p0002', 720);insert into tm(tno, pno, workdays) values('t005', 'p0003', 180);insert into tm(tno, pno, workdays) values('t006', 'p0004', 360);insert into tm(tno, pno, workdays) values('t006', 'p0002', 180);insert into tm(tno, pno, workdays) values('t006', 'p0003', 720);update department set tno='t006' where dno='d003';commit;1. 查询系号为“d001”的所有教师的教工号、名称和工资； select tno,tname,tsalary from teacher where dno='d001';2. 查询工资在 3000 到 5000 之间的教师姓名、年龄（提示：可使用当前年份减去教师的 出生年份，教师的出生年份可以使用函数 extract(year from tbirthday)获取）； select tname,2017-extract(year from tbirthday) as age from teacher where tsalary between 3000 and 5000;3. 查询参加了项目的教工的编号，排除相同的元素； select distinct tno from myproject;4. 查询名字中包含字“小”的教工姓名、出生日期；select tname,tbirthday from teacher where tname like '小%';5. 查询名字中第二个字为“小”的教工姓名、出生日期； select tname,tbirthday from teacher where tname like'_小%';6. 查询所有不姓“李”、并且姓名为三个字的教工姓名、性别；select tname,tsex from teacher where tname not like'李%'  and tname like'___';7. 查询 Department 表有系主任的系号、系名称；select * from department where tno is not null;8. 查询工资在 4000 以上或者性别为女的教师详细信息，按性别排序输出；select * from teacher where tsex like '女%' or tsalary>4000 order by tsex;9. 查询参与了项目的教工总人数； select count(distinct tno) from tm;10. 查询“张三”负责的项目数量； select count(*) from tm where  tno in(select tno from teacher where tname like '张三');11. 查询所有教师的平均工资、工资总和、高工资、低工资； select avg(tsalary),sum(tsalary),max(tsalary),min(tsalary) from teacher;12. 创建视图 departmentSalary，查询各个系的教师的平均工资、工资总和、高工资、 低工资;create view departmentsalary as select dno,avg(tsalary) as 平均工资,sum(tsalary) as 工资和,max(tsalary) as 最高工资,min(tsalary) as 最低工资 from teacher group by dno;13. 查询各个系的详细信息，包括各个系的教师的平均工资、工资总和、高工资、低 工资(提示：可以使用 department 表与视图 departmentSalary 进行连接运算完成)； select * from departmentsalary;14. 查询教师平均工资大于 4500 的系号、系名称、平均工资（要求不能使用视图 departmentSalary，可把 department 与 teacher 连接后再进行分组）select department.dno,dname,avg(tsalary) as 平均工资 from department,teacher where department.dno=teacher.dno having avg(tsalary)>4500 group by department.dno,dname;//使用group by 的时候如果前面用了聚合函数，那么所有列都要写在by后面.15. 查询教师参与项目的情况，列出教师编号、姓名和项目名称，没有参与项目的教师也 列出来（提示：用左外连接）； select teacher.tno,tname,pname from teacher left outer join myproject on(teacher.tno=myproject.tno);16. 查询与“李小龙”工资相同的教师详细信息（要求分别使用自身连接、子查询两种查 询方法完成）(1) 自身链接select second.* from teacher first,teacher second where first.tsalary=second.tsalary and first.tname like '李小龙';(2) 子查询select * from teacher where tsalary in(select tsalary from teacher where tname='李小龙');17. 查询参与了“云计算研究”并且工资在 4000 以上的教师详细信息； select * from teacher where tsalary>4000 and tno in(select tno from tm where pno in(select pno from myproject where pname='云计算研究'));18. 查询小于或等于“同一系中教师平均工资”的教师编号、姓名、年龄； select tno,tname,2017-extract(year from tbirthday) from teacher x where tsalary<=(select avg(tsalary) from teacher y where x.dno=y.dno);19. 查询比“计算机科学系”教师工资都高、并且不是“网络工程系”的教师信息;select * from teacher where tsalary>ALL(select tsalary from teacher where dno in(select dno from department where dname='计算机科学系')) and dno in(select dno from department where dname not like '网络工程系');20. 查询没有参与项目“p0001”的教师编号、姓名； select tno,tname from teacher where tno !=ALL(select tno from tm where pno='p0001');21. 查询参与了所有项目的教师姓名；select tname from teacher where not exists(select * from myproject where not exists(select * from tm where tno=teacher.tno and pno=myproject.pno ));22. 查询工资大于 3500 或者在计算机科学系工作的教师详细信息（要求使用关键字 UNION）； select * from teacher where tsalary>3500 union select * from teacher where dno in(select dno from department where dno=teacher.dno and dname like '计算机科学系');23. 查询工资大于 3500 并且不在计算机科学系工作的教师详细信息（要求使用关键字 MINUS）select * from teacher where tsalary>3500 intersect select * from teacher where dno in(select dno from department where dno=teacher.dno and dname not like '计算机科学系');