PLSQL 的简单命令之五
时间：2021-07-01 10:21:17
帮助过：24人阅读
1. 查询和Zlotkey相同部门的员工姓名和雇用日期
select a.last_name,a.hire_date ,b.department_name
from employees a,departments b
where b.department_name
in
(select department_name
from departments, employees
where last_name
= ‘Zlotkey‘)
--2. 查询工资比公司平均工资高的员工的员工号，姓名和工资。
select job_id,last_name,salary
from employees
where salary
> all (
select avg(salary)
from employees)
--3. 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
select a.job_id,a.last_name,a.department_id,a.salary,b.avgsalary
from employees a,(
select avg(salary)
as avgsalary,department_id
from employees
group by department_id) b
where a.salary
> b.avgsalary
and a.department_id
= b.department_id
/*
其中各部门的平均工资如下
select avg(salary),department_id from employees
group by department_id
别名为b的表（查询结果的表 ）
(select avg(salary) as avgsalary,department_id from employees
group by department_id) b
*/
--4. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
select job_id,last_name,department_id
from employees
where department_id
in (
select department_id
from employees
where last_name
like ‘%u%‘)
--5. 查询在部门的location_id为1700的部门工作的员工的员工号
select job_id
from employees
where department_id
in (
select department_id
from departments
where location_id
=1700)
--6.查询管理者是King的员工姓名和工资
select manager_id,last_name,salary
from employees
where manager_id
in (
select employee_id
from employees
where last_name
=‘King‘
group by employee_id)
--1. 运行以下脚本创建表my_employees
create table my_employees
(id number,
first_name varchar2(
10),
last_name varchar2(
10),
userid varchar2(
10),
salary number
);
--2. 显示表my_employees的结构
select * from 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_employees
values (
1,
‘patel‘,
‘Ralph‘,
‘Rpatel‘,
895);
insert into my_employees
values (
2,
‘Dancs‘,
‘Betty‘,
‘Bdancs‘,
860);
insert into my_employees
values (
3,
‘Biri‘,
‘Ben‘,
‘Bbiri‘,
1100);
insert into my_employees
values (
4,
‘Newman‘,
‘Chad‘,
‘Cnewman‘,
750);
insert into my_employees
values (
5,
‘Ropeburn‘,
‘Audrey‘,
‘Aropebur‘,
1550);
--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
--8. 提交
commit
--9. 删除所有数据
delete from my_employees
--10. 检查所作的修正
select * from my_employees
--11. 回滚
rollback
--12. 清空表my_employees
truncate table my_employees
PLSQL 的简单命令之五
标签：相同 span 别名 字母 select number rtm ble job
