oracle課堂筆記--第十二天

標籤：strong   des   order   table   last   文法   nts   out   into   

分組函數    ，多行函數

對非空的資料總結

只有在列裡才先把空值排除

分組不需要

SQL> select count(*), sum(salary), avg(salary), min(salary), max(salary) from employees;

 

SQL> create table t1(x int);

SQL> insert into t1 values (null);

SQL> insert into t1 values (1);

SQL> commit;

SQL> select count(*) from t1;

SQL> select count(x) from t1;

SQL> select max(x) from t1;

SQL> select min(x) from t1;

SQL> select sum(x) from t1;

SQL> select avg(x) from t1;

 

SQL> select avg(salary), avg(nvl(commission_pct, 0)) from employees;

SQL> select count(distinct department_id) from employees; 去除重複值

 

Group by分組：

SQL> select department_id, avg(salary) from employees group by department_id;

多列分組：

SQL> select department_id, job_id, max(salary) from employees group by department_id, job_id;

SQL> select department_id, job_id, max(salary), last_name from employees group by department_id, job_id; 錯誤文法

 

練習：

公司中不同職位的數量

select (distinct job_id)from employees;

 

 

計算每個部門的人數 

Select Department_id,count(employees_id)

From employees

Where department_id is not null

Group by department_id

 

按年份分組，求員工的工資總和

Select extract（year from hire_date）,avg(salary)

From employees

Group by extract(year from hire_date)

 

Having語句：

SQL> select department_id, avg(salary) from employees where avg(salary)>=5000 group by department_id; 錯誤語句

SQL> select department_id, avg(salary) from employees group by department_id having avg(salary)>=5000;

 

練習：

按部門求出所有有部門的普通員工的平均工資，部門平均工資少於5000的不顯示，最終結果按平均工資的降序排列。

select department_id, avg(salary) avg_sal

from employees

where job_id not like ‘%\_MGR‘ escape ‘\‘ and department_id is not null 

group by department_id

having avg(salary)>=5000

order by avg_sal desc;

多表串連 no

emp： dept:

empno ename deptno deptno dname

100 abc 10 10 sales

101 def 10 20 market

102 xyz 20 30 it

103 opq null

 

for emp in 100 .. 103

  for dept in 10 .. 30

    emp.deptno=dept.deptno

 

100         abc         10              10          sales

101         def         10              10          sales

102         xyz         20              20          market

 

 

擷取如下資訊，準備工作：

employees:

員工總數：107

SQL> select count(*) from employees;

有部門的員工數：106

SQL> select count(*) from employees where department_id is not null;

SQL> select count(department_id) from employees;

沒有部門的員工數：1

SQL> select count(*) from employees where department_id is null;

 

departments：

部門總數：27

SQL> select count(*) from departments;

有員工的部門數：11

SQL> select count(distinct department_id) from employees;

沒有員工的部門數：16

SQL> select count(*) from departments where department_id not in (select department_id from employees where department_id is not null);

 

for dept in 1..27

  for emp in 1..107

   dept.deptid不在emp表中出現

 

select count(*)

from employees e, departments d

where e.department_id(+)=d.department_id

and e.employee_id is null;

 

select count(*)

from departments d

where not exists

(select 1 from employees where department_id=d.department_id);

 

select (select count(*) from departments)-(select count(distinct department_id) from employees) from dual;

 

內串連：106(106, 11)

select e.last_name, d.department_name

from employees e, departments d

where e.department_id=d.department_id;

 

select e.last_name, d.department_name

from employees e join departments d on e.department_id=d.department_id;

 

左外串連：107（106+1）

select e.last_name, d.department_name

from employees e, departments d

where e.department_id=d.department_id(+);

 

select e.last_name, d.department_name

from departments d, employees e

where e.department_id=d.department_id(+);

 

select e.last_name, d.department_name

from employees e left outer join departments d

on e.department_id=d.department_id;

 

右外串連：122（106+16）

select e.last_name, d.department_name

from employees e, departments d

where e.department_id(+)=d.department_id;

 

select e.last_name, d.department_name

from employees e right outer join departments d

on e.department_id=d.department_id;

 

完全外串連：123（106+1+16）

select e.last_name, d.department_name

from employees e full outer join departments d

on e.department_id=d.department_id;

 

多表串連的擴充：

n張表串連：

select e.last_name, d.department_name, l.city

from employees e, departments d, locations l

where e.department_id=d.department_id

and d.location_id=l.location_id;

 

select e.last_name, d.department_name, l.city

from employees e join departments d on e.department_id=d.department_id

               join locations l on d.locationy_id=l.location_id;

 

select e.last_name, d.department_name, l.city

from employees e, departments d, locations l

where e.department_id=d.department_id(+)

and d.location_id=l.location_id(+);

 

select e.last_name, d.department_name, l.city

from employees e left outer join departments d on e.department_id=d.department_id

               left outer join locations l on d.location_id=l.location_id;

oracle課堂筆記--第十二天