传智播客——Oracle（3）

今天中午轮到我守教室了，一个人的教室静悄悄的，听着耳机里传来的优雅音乐，打开Word整理一下上午的上课知识吧。上午讲了SQL查询的最后一部分——子查询，这部分的内容没什么知识点，重在练习。这里，我换一种方式与大家分享今天的学习吧，来完成几道典型的Oracle子查询题目。

1.       查询管理者是king的员工姓名和工资

A: select e.last_name, e.salary

from employees e

where manager_id in (select employee_id from employees where last_name = 'King')

2. 查询工资比部门平均工资高的员工的员工号, 姓名和工资

A: select employee_id, last_name, salary, department_id

from employees e

where salary > (select avg(salary) from employees where department_id = e.department_id group by department_id)

order by department_id, salary

注意：department_id = e.department_id是自连接的运用.

3.     查询 1999 年来公司的人所在部门中的最高工资的那些人

A: select last_name, salary, department_id, email

from employees e

where salary = (

                  select max(salary)

                  from employees

                  where department_id in (select distinct department_id

                                          from employees

                                          where hire_date like '%99' and department_id is not null

                                          )

                         and department_id = e.department_id

                  group by department_id

                )

下午讲的内容很多，主要是关于数据库对象的处理：

1.     主要的数据库对象有：表，视图，序列，索引和同义词

2.     向表中插入数据:

insert into my_employee values(1,'patel','Ralph','Rpatel',895)

3.    提交:commit

4.更新数据：update my_employee set first_name = 'drelxer' where id=1;

5. 删除数据: delete from my_employee;

6.回滚:rollback;

7.清空表：truncate table my_employee;

8.创建表：create table dept_1(id number(7), name varchar2(25));

9.拷贝数据: insert into dept_1(id,name)

select department_id,department_name from departments;

10.修改列的数据类型：ALTER TABLE dept80

MODIFY            (last_name VARCHAR2(30));