数据库得查询

 0hi猿团提供了移动跨平台开发视频，包括html5，apicloud appcan，dcloud,具体请看http://www.9y.cm 
1. 查询和Zlotkey相同部门的员工姓名和雇用日期

select last_name, hire_date
from employees
where department_id = (
                        select department_id
                        from employees
                        where lower(last_name) = 'zlotkey'
                      )

2. 查询工资比公司平均工资高的员工的员工号，姓名和工资。

select employee_id, last_name, salary
from employees
where salary > (select avg(salary) from employees)

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

 

4. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

select employee_id, last_name
from employees
where department_id in (
                          select distinct department_id
                          from employees
                          where last_name like '%u%'
                       )

4. 查询在部门的location_id为1700的部门工作的员工的员工号，

select employee_id
from employees e, departments d
where e.department_id = d.department_id and d.location_id = 1700

或

select employee_id
from employees e
where department_id in (select department_id from departments where location_id = 1700)

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

select e.last_name, e.salary
from employees e, employees m
where e.manager_id = m.employee_id and m.last_name = 'King'

或

select e.last_name, e.salary
from employees e
where manager_id in (select employee_id from employees where last_name = 'King')

 

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, count(employee_id)
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
order by count(employee_id)

1) 组函数不能放在 where 子句中, 需要放在 having 子句中
2) 需按 city 进行分组
3) where 一定在 from 的后边
4) order by 一定放在 select 查询语句的最后边.

3. manager 中的最高工资是谁?    或
   manager 中的平均工资是多少? 

平均工资
 
方案1：6296.226415
select avg(salary)
from employees
where manager_id is not null
order by manager_id

方案2: 11992.4528301
select avg(m.salary)
from employees e, employees m
where e.manager_id = m.employee_id
order by m.employee_id

方案 1 错误, 其返回的是公司有 manager 的平均工资

最高工资
select max(m.salary)
from employees e, employees m
where e.manager_id = m.employee_id
order by m.employee_id

最高工资及其 last_name
select distinct m.last_name, m.salary
from employees e, employees m
where m.salary = (
                   select max(m.salary)
                   from employees e, employees m
                   where e.manager_id = m.employee_id
                 )

分析: count(decode(to_char(hire_date, 'YYYY'), 1995, 1))

to_char(hire_date, 'YYYY') 返回类似于 1995 这样的字符串
decode(to_char(hire_date, 'YYYY'), 1995, 1) 若 to_char(hire_date, 'YYYY') 返回值为 1995, 则返回 1 否则返回 NULL
count(decode(to_char(hire_date, 'YYYY'), 1995, 1)) 因为 count() 忽略控制所以会返回所有 1995 年的记录数

oracle 最常用的命令行命令:
启动监听: lsnrctl start 监听名
启动数据库实例: oradim -startup -sid 数据库实例的 id

2009-07-28
1.  查询平均工资高于 公司平均工资 的部门有哪些?

2.  查询各个部门最高工资的员工的详细信息： last_name, department_id, email, salary

答案1:
select last_name, department_id, email, salary
from employees
where salary in ( select max(salary)
                  from employees
                  group by department_id)
该答案错误. 因为可能会返回一个的多个员工信息, 当在部门中有员工的工资刚好是其他部门的最高工资时就会出现这种情况.

答案2:
select last_name, department_id, email, salary
from employees e
where salary = (select max(salary)
                from employees
                where department_id = e.department_id)
order by department_id

3. any 的作用: 和子查询返回的任意一个值比较 -- 和 in 一致

select last_name, salary, department_id
from employees
where salary = any(select max(salary) from employees group by department_id)
order by department_id
返回 15 条记录

等价于

select last_name, salary, department_id
from employees
where salary in (select max(salary) from employees group by department_id)
order by department_id

3.1

select last_name, salary, department_id
from employees
where salary >= any(select max(salary) from employees group by department_id)
order by salary
作用是和最小的那个值比较

4. all 的作用: 和子查询返回的任意所有值比较

select last_name, salary, department_id
from employees
where salary = all(select max(salary) from employees group by department_id)
order by department_id
返回 0 条记录

4.1
select last_name, salary, department_id
from employees
where salary >= all(select max(salary) from employees group by department_id)
order by salary
作用和最高的那个比较

5. 各个部门中最高工资中最低的那个部门的最高工资是多少
select min(max_sal)
from (
 select max(salary) max_sal
 from employees
 group by department_id
     )

6. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

1) 查询平均工资最高的部门的 department_id

1.1)
查询得到最高平均工资
select max(avg_sal)
from (select avg(salary) avg_sal from employees group by department_id)

1.2)
查询得到最高平均工资的 department_id
select department_id
from employees
group by department_id
having avg(salary) = (
                      select max(avg_sal)
                      from (select avg(salary) avg_sal from employees group by department_id)
                      )

2) 查询 1) 对应的部门的 manager_id
select manager_id
from departments
where department_id = (
                      select department_id
                      from employees
                      group by department_id
                      having avg(salary) = (
                                            select max(avg_sal)
                                            from (select avg(salary) avg_sal from employees group by department_id)
                                            )
                       )

3) 查询 employee_id 为 2) 的那个人的信息
select employee_id, last_name, email, salary, department_id
from employees
where employee_id = (
                    select manager_id
                    from departments
                    where department_id = (
                                          select department_id
                                          from employees
                                          group by department_id
                                          having avg(salary) = (
                                                                select max(avg_sal)
                                                                from (select avg(salary) avg_sal from employees group by department_id)
                                                                )
                                           )
                      )

7. 查询 1999 年来公司的人所在部门中的最高工资的那些人.
1) 查询 1999 年来的那些员工的 department_id, 应该返回多个值

select distinct department_id
from employees
where hire_date like '%99' and department_id is not null

2) 查询 1) 得到的那些部门中的最高工资都是多少

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
                        )
group by department_id

3) 查询 1) 得到的那些部门最高工资的人的详细信息
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
                )

8. 表之间数据的拷贝:
insert into my_dept (department_id, department_name, manager_id, location_id) select * from departments

简易建表的方法:
create table my_dept as select * from departments where 1 = 2

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

1) 查询 108 所在部门的最高工资是多少

select max(salary)
from employees
where department_id = (select department_id from employees where employee_id = 108)

2) 查询 公司中平均工资最低的 job_id

select job_id
from employees
group by job_id
having avg(salary) = (
                      select min(avg_sal)
                      from (select avg(salary) avg_sal from employees group by job_id)
                      )

3) 更新

update employees set salary = (
                                select max(salary)
                                from employees
                                where department_id = (select department_id from employees where employee_id = 108)
                               ),
                     job_id = (
                                select job_id
                                from employees
                                group by job_id
                                having avg(salary) = (
                                                      select min(avg_sal)
                                                      from (select avg(salary) avg_sal from employees group by job_id)
                                                      )
                               )
where employee_id = 108

10. 删除 108 号员工所在部门中工资最低的那个员工.
1) 查询 108 员工所在部门的最低工资

select min(salary)
from employees
where department_id = (select department_id from employees where employee_id = 108)

2) 删除

delete from employees
where salary = (
                select min(salary)
                from employees
                where department_id = (select department_id from employees where employee_id = 108)
                )
      and department_id = (select department_id from employees where employee_id = 108)

11. 事物问题
情境: A 向 B 汇款 100 元.
      包含两个操作: 1) A 的账户减去 100 元
                    2) B 的账户增加 100 元
 
      这两个操作要么都成功, 要么都不成功!

12. ROLLBACK to SAVEPOINT A 该语句不能说明事物已经结束. 因为此时可能还有其他的回滚点可以回滚.

13. 定义非空约束
create table test_constraint(
id varchar2(20) not null, -- 由系统为定义的约束起名字
name varchar2(20) constraint name_not_null not null, -- 自己为约束起名字
email varchar2(20))

14.  定义唯一约束
create table test_constraint(
id varchar2(20) unique,
name varchar2(20) constraint name_uk unique,
email varchar2(20),
hire_date date,
constraint email_hdate_uk unique(email, hire_date))
注意1. 列级约束和表级约束的区别. 其中 email_hdate_uk 为表级约束, 它可以限制几列.
    2. 唯一约束允许为空, 即允许插入多个空值

15.  定义主键约束
create table test_constraint(
id varchar2(20),
name varchar2(20),
constraint test_pr primary key(id))

16. 为已经建好的表添加外键约束
alter table my_emp add constraint dep_fk foreign key(department_id) references my_dep(department_id)

定义列级约束的外键
create table my_emp(
id varchar2(20) primary key,
name varchar2(20) unique,
department_id number(4) references departments(department_id))

定义表级约束的外键
create table my_emp(
id varchar2(20) primary key,
name varchar2(20) unique,
department_id number(4),
constraint dept_id_fk foreign key(department_id) references departments(department_id))
 
建立外键约束是有条件的: 当指向的表的列不存在 唯一约束 时, 无法建立外键约束!

级联删除: ON DELETE CASCADE:  当父表中的列被删除时，子表中相对应的列也被删除
级联置空: ON DELETE SET NULL: 当父表中的列被删除时，子表中相应的列置空