08、SQL--数据处理（2）、事务回滚（含习题）

四、数据库事务：

 

 

 

1、事务：

 

 

（1）步、

delete from employees

where employee_id=206

 

（2）步、

select count(*) from employees

 

（3）步、

commit ；

 

（4）步、

delete from employees

where employee_id=205

 

（5）步、设置一个savepoint A：

savepoint A ；

 

（6）步、查询个数：

select count（*） from employees ；

 

（7）、rollback to savepoint A ；

 

 

 

 

2、在system用户登录后访问scott用户的employees表：

System用户：select * from scott.employees for update ；点回车后没有反应。

因为scott用户对employees表锁定了，只有在scott用户用commit ； 释放锁。

 

 

 

 

 

五、测验题一：

 

55. 更改 108 员工的信息: 使其工资变为所在部门中的最高工资, job 变为公司中平均工资最低的 job

       1). 搭建骨架      

 update employees

       set salary = (



       ), job_id = (



       ) where employee_id = 108;

      

       2). 所在部门中的最高工资        

select max(salary)

       from employees

       where department_id = (

              select department_id

              from employees

              where employee_id = 108

       )

      

       3). 公司中平均工资最低的 job      

 select job_id

       from employees

       group by job_id

       having avg(salary) =  (

              select min(avg(salary))

              from employees

              group by job_id

       )

      

       4). 填充（总合）：      

update employees e set salary = (

              select max(salary)

              from employees

              where department_id = e.department_id

       ), job_id = (

              select job_id

              from employees

              group by job_id

              having avg(salary) =  (

                     select min(avg(salary))

                     from employees

                     group by job_id

              )

       ) where employee_id = 108;

      

56. 删除 108 号员工所在部门中工资最低的那个员工.

 

       1). 查询 108 员工所在的部门 id      

 select department_id

       from employees

       where employee_id = 108;

      

       2). 查询 1) 部门中的最低工资:
      

select min(salary)

       from employees

       where department_id = (

              select department_id

              from employees

              where employee_id = 108

       )

      

       3). 删除 1) 部门中工资为 2) 的员工信息:

 delete from employees e

              where department_id = (

                       select department_id

                       from employees e

                       where employee_id = 108

              ) and salary = (

                       select min(salary)

                       from employees

                       where department_id = e.department_id

              )     

 

六、测验题二：

 

1、运行以下脚本创建表my_employees

Create table my_employee (  id         number(3),

first_name varchar2(10),

                            Last_name  varchar2(10),

                            User_id    varchar2(10),

                            Salary     number(5));

 

2、显示表my_employees的结构

DESC my_employees;

 

3、向表中插入下列数据

ID	FIRST_NAME	LAST_NAME	USERID	SALARY
1	patel	Ralph	Rpatel	895
2	Dancs	Betty	Bdancs	860
3	Biri	Ben	Bbiri	1100
4	Newman	Chad	Cnewman	750
5	Ropeburn	Audrey	Aropebur	1550

   

    INSERT INTO my_employee

    VALUES(1,’patel’,’Palph’,’Rpatel’895);

 

4、提交

COMMIT;

 

5、将3号员工的last_name修改为“drelxer”

UPDATE my_employees

SET last_name = ‘drelxer’

WHERE id = 3;

 

6、将所有工资少于900的员工的工资修改为1000
UPDATE my_employees

SET salary = 1000

WHERE salary< 900

 

7、检查所作的修正

SELECT * FROM my_employees

WHERE salary < 900

 

8、提交

COMMIT;

 

9、删除所有数据

DELETE FROM my_employees;

 

10、检查所作的修正

SELECT * FROM my_employees;

 

11、回滚

ROLLBACK;

 

12、清空表my_employees

TRUNCATE TABLE my_employees