分组函数
SQL> select department_id,max(salary),min(salary),avg(salary) from emp group by department_id;
Group by 必须前后列一致。

having语句 过滤函数
SQL> select department_id,max(salary),min(salary),avg(salary) from emp group by department_id having avg(salary)>10000;

SQL> select department_id,max(salary),min(salary),avg(salary) from emp where HIRE_DATE>=‘2010-01-01’ group by department_id having avg(salary)>10000;

多表连接 join on
Select () from join () on
SQL99语法
SQL> select city_name,street_address from dmhr.location l join dmhr.city c on l.city_id=c.city_id;

SQL92语法 类连接
SQL> select city_name,street_address from dmhr.location l,dmhr.city c where l.city_id=c.city_id;

SQL> select employee_name,department_name from emp e join dept d on e.department_id=d.department_id;
hash连接，hash强制连接，针对department_id构造hash表。
SQL> select employee_name,department_name from emp e inner hash join dept d on e.department_id=d.department_id;

如果左join表列存在空值，用一般连接方式不会显示左列空值的行。需要用外连接left join
select employee_name,department_name from emp e left join dept d on e.department_id=d.department_id;
如果右join表列存在空值，用一般连接方式不会显示左列空值的行。需要用外连接right join
select employee_name,department_name from emp e right join dept d on e.department_id=d.department_id;

如果左和右都存在空值，用一般连接方式不会显示左列空值的行。需要用外连接full join
select employee_name,department_name from emp e full join dept d on e.department_id=d.department_id;

SQL> select department_id from emp where employee_name=‘王迪玉’;

行号 DEPARTMENT_ID

1 1105
子查询
SQL> select employee_name,salary from emp e where department_id=(select department_id from emp where employee_name=‘王迪玉’);