1. SQL 语句与SQL*Plus 命令
SQL | SQL*Plus |
一种语言 | 一种环境 |
ANSI标准 | Oracle 的特性之一 |
关键字不能缩写 | 关键字可以缩写 |
使用语句控制数据库中的表的定义信息 和表中的数据 | 命令不能改变数据库中的数据的值 |
集中运行 |
2.以下几个查询很重要,仔细体会
1 (有员工的城市)各个城市的平均工资
select city,avg(salary) from employees e,departments d locations l
where e.department_id = d.department_id and d.location_id = l.location_id group by city;
2 查询哪些城市有员工,哪些城市没有员工
select city from employees e,departments d,locations l
where e.department_id = d.department_id and d.location_id = l.location_id
group by city having count(employee_id) > 0
1)组函数不能放在 where子句中, 需要放在 having 子句中
2) 需按 city 进行分组
3 manager 中最高工资是谁 manager 中最低工资是多少 manager 中平均工资是多少
首先要确定哪些人是manager
公司有哪些人是 manager :
方案1
select distinct m.employee_id,m.last_name from employees e,employees m
where e.manager_id = m_employee_id order by m.employee_id
方案2
select distinct manager_id from employees where manager_id is not null order by manager_id
平均工资:
select avg(m.salary) from employees e,employees m
where e.manager_id = m.employee_id order by m.employee_id
最高工资:
/*
select max(m.salary) from employees e,employees m
where e.manager_id= m.employee_id order by m.employee_id
*/
可以用子查询
select last_name,salary from employees where salary =
(select max(m.salary) from employees e,employees m where e.manager_id = m.employee_id )
order by employee_id
5. 选择所有员工的姓名,员工号,以及他的管理者的姓名和员工号,结果类似于下面的格式
-------------------------------------
employees| Emp# |manager |Mgr#
-------------------------------------
kochhar 101 king 100
-------------------------------------
select work.last_name "employees", work.employee_id "Emp#" , manager.last_name "manager" ,
manager.employee_id "Mgi#" from employees work , employees manager
where work.manager_id = manager.employee_id and lower(work.last_name) = 'kochhar'
6. 查询各部门员工姓名和他们的同事姓名,结果类似于下面的格式
-----------------------------------------
Department_id | Last_name |colleague
-----------------------------------------
20 fay hartstein
-----------------------------------------
select e.department_id "Department_id", e.last_name "Last_name",colleague.last_name "colleague"
from employees e , employees colleague
where e.manager_id = colleague.employee_id and lower(e.last_name) = 'fay'