xtu数据库原理实验（3）

实验要求前9条一条SQL完成

操作1

update INSTRUCTOR set salary=40000 where ID='99052';

//检验是否成功的语句，可以添加截图到实验报告中
select salary from INSTRUCTOR where ID='99052';

操作2

update INSTRUCTOR set salary=salary*1.1 where DEPT_NAME='Comp.Sci.';

操作3

先自查操作前Taylor楼所有的工资（不要怀疑为什么这么少，我查了下因为本身院系就很少）

select * from INSTRUCTOR where DEPT_NAME in 
(select distinct DEPT_NAME from DEPARTMENT where BUILDING='Taylor');

select DEPT_NAME from DEPARTMENT where BUILDING='Taylor';

update INSTRUCTOR set salary=salary+1000 where DEPT_NAME in 
(select distinct DEPT_NAME from DEPARTMENT where BUILDING='Taylor');

操作4

update INSTRUCTOR set salary=CASE
    when ID in (select ID from TEACHES) THEN salary*0.92
    ELSE salary*0.9     
END;

操作5

select BUILDING from DEPARTMENT where DEPT_NAME='History';

update DEPARTMENT set BUILDING=
(select BUILDING from DEPARTMENT where DEPT_NAME='English') 
where DEPT_NAME='History';

操作6

insert into INSTRUCTOR VALUES(10086,'jack',NULL,30000);

操作7

delete from INSTRUCTOR where DEPT_NAME is NULL;

操作8

delete from SECTION where not exists 
(select * from TAKES t 
where course_id=t.course_id and sec_id=t.sec_id
and semester=t.semester and year=t.year);

操作9

update STUDENT s set TOT_CRED=TOT_CRED+(select SUM(c.CREDITS) 
from COURSE c,TAKES t
where s.ID=t.ID and t.COURSE_ID=c.COURSE_ID
and t.GRADE!='C-'
group by t.id);

操作10

create table history_student
(
    ID varchar(5),
    name varchar(20) not null,
    dept_name varchar(20),
    tot_cred  numeric(3,0) check (tot_cred >= 0),
    primary key (ID),
    foreign key (dept_name) references department
    on delete set null
);

操作11

insert into history_student select * from STUDENT where TOT_CRED>100;

操作12

操作13

delete from history_student where ID='47677';
select * from history_student where ID='47677';

操作14、15

rollback;
commit;

或者右键连接选项框可以直接回滚或者提交