sql_数据操作练习总结

create

create table ( 
	emp_no char(10),
	emp_name char(20),
	emp_job char(10),
	emp_hire date,
	salary number,
	dep_no char(10) );

create table ( 
	dep_no char(10),
	dep_name char(20),
	dep_location char(40) );

insert

insert into emp ( 
	 emp_no,
	 emp_name,
	 emp_hire )	 
values (
	'1011',
	'王晓明',
	current_date );

insert into 
	emp 
values ( 
	'1012',
	'孙宇',
	'教辅',
	to_date( '2017-10-12', 'YY-MM-DD' ),
	5400,
	'04' );

insert into employee_backup ( 
	emp_no,
	emp_name,
	emp_job,
	emp_hire,
	emp_salry,
	emp_dep_no ) 
select 
	*
from
	employee
where 
	emp_job = '教辅';

 事务的提交 commit

update

# 1) 把教师岗位的雇员薪水提高10%；
update 
	employee 
set 
	salary = salay*1.1;

# 2) 把1004雇员的入职日期修改为：1983-12-6、薪水减少5%；
update
	employee
set
	emp_hire = to_date( '1983-12-6', 'YY-MM-DD'),
	salary = salary*0.95
where
	emp_no = '1004'

# 3) 把1001雇员的薪水和入职日期修改为与1005雇员相同；
update
	employee
set
	( emp_salary, emp_hire ) = ( select 
								 	emp_salary,
									emp_hire
								from 
									employee
								where 
									emp_no = '1005')
where 
	emp_no = '1001'

# 4) 修改EMP_BAK中所有人员的薪水为EMP表中的最高薪水。
update
	employee_backup
set
	salary = ( select 
					max( salary )
				from 
					emplyee )
where 
	1 = 1;

delete

# 1) 删除EMP_BAK表中所有记录；
drop table employee_backup;

# 2) 删除雇员表中“教师”岗位且1988年之前入职的雇员；
delete
from 
	employee
where
	( emp_job, extract(year from emp_hire) ) in ( select
									emp_job,
									emp_hire
								from
									employee
								where
									job = '教师'
									and
									extract(year from emp_hire) = to_date( 'YY', '1998' );
									

# 3) 删除“体育学院”的雇员信息
delete
from
	employee
where
	emp_no = select ( 
					dep_no
				from
					department 
				where
					dep_name = '体育学院' );