절

/* group by 절 */

select distinct department_id from EMPLOYEES; 중복 허용 X

select department_id from EMPLOYEES group by department_id;

//위와 같지만 그룹으로 묶는 개념

/* 부서별 급여 합계 */

select department_id, sum(salary) 

from EMPLOYEES

group by department_id;

/* ORA-00937: 단일 그룹의 그룹 함수가 아닙니다

00937. 00000 -  "not a single-group group function"*/

select distinct department_id, sum(salary)

from employees;

/* 부서별 사원수와 평균 급여를 구해보자 */03                                    

select department_id, sum(salary), count(salary), avg(salary)

from employees

group by department_id;

/* 부서별 직급별 사원수와 평균 급여를 구하는 예*/4

select department_id, job_id, sum(salary), count(salary), avg(salary)

from employees

group by department_id, job_id;

select department_id, job_id, sum(salary), count(salary), avg(salary)     5

from employees

group by department_id, job_id

order by department_id, job_id;

select department_id, job_id,            6

to_char(sum(salary),'999,999') 총급여,

to_char(avg(salary),'999,999') 평균급여

from EMPLOYEES

GROUP by department_id, job_id

order by department_id, job_id;

select department_id, job_id,

to_char(sum(salary),'999,999') 총급여,

to_char(avg(salary),'999,999') 평균급여

from EMPLOYEES

where department_id = 80

GROUP by department_id, job_id

order by department_id, job_id;

/* having 절 : 현재 부서별 사원수*/

select department_id, count(*)

from EMPLOYEES

where department_id is not null

      /*and count(*) >= 10 where절에서 사용할 수 없다.(집계함수는 사용을 할수가 없다.)*/

group by department_id

having count(*) >= 10;

/*rollup : 그룹별 합계 정보를 추가해서 보여주는 함수*/

select l.CITY, d.DEPARTMENT_NAME, e.JOB_ID,count(*) 사원수, sum(e.salary) 총급여  

from EMPLOYEES e, DEPARTMENTS d,LOCATIONS l

where e.department_id = d.department_id and d.LOCATION_ID = l.LOCATION_ID

group by rollup(l.city, d.department_name, e.job_id)

order by l.city, d.department_name, e.job_id;

/*조인(join): 일반적으로 '내부조인' 방식을 의미*/

select * from employees;

select emp.first_name, emp.last_name, emp.email, 

      dep.department_name

from employees emp, departments dep

where emp.DEPARTMENT_ID = dep.DEPARTMENT_ID;

select * from jobs;

select emp.first_name, emp.last_name, emp.email,            

       emp.department_id, dep.department_name,

       emp.job_id, job.job_title, loc.city

from employees emp,

     departments dep,

     jobs job,

     locations loc

where emp.department_id = dep.department_id

  And emp.job_id = job.job_id

  And dep.location_id = loc.location_id

  And loc.city = 'Seattle';

  

/*셀프 조인*/

select emp1.EMPLOYEE_ID, emp1.first_name, 

       emp2.employee_id 상사id,

       emp2.first_name 상사이름

from employees emp1, employees emp2

where emp1.MANAGER_ID = emp2.employee_id;

/*외부조인*/

select emp.employee_id,

       emp.first_name,

       emp.department_id,

       dep.department_name,

       loc.city

from employees emp, departments dep, locations loc

where emp.department_id = dep.department_id (+)

And   dep.location_id = loc.location_id (+);

  

/*서브쿼리*/

select Round(avg(salary))

from employees;

select employee_id, first_name, last_name

from employees

where salary < 6462;

select employee_id, first_name, last_name

from EMPLOYEES

where salary < Round(avg(salary)); /*where 절에서는 집계함수 사용불가*/

select employee_id, first_name, last_name

from EMPLOYEES

where salary < (select round(avg(salary))

                from employees);

select location_id 

from locations

where state_province is null;

select *

from departments

where location_id in ( select location_id 

                      from locations

                      where country_id ='US' );

select *

from departments

where location_id in ( 1400, 1500, 1700 ); 

                      

/*월급이 가장 적은 사원*/ 

select emp.first_name, emp.last_name, job.JOB_TITLE

from employees emp, jobs job

where emp.salary = (select min(salary) from employees)

and emp.job_id = job.job_id;

/*평균 급여보다 많이 받는 사원들의 명단 조회 */

select emp.first_name, emp.last_name, job.job_title

from employees emp, jobs job

where emp.salary >( select avg(salary) from employees)

and emp.job_id = job.job_id;

/* any, all */

select salary 

from employees

where department_id = 20;

select employee_id, department_id, salary

from employees 

where salary > any ( select salary 

                     from employees

                     where department_id = 20);

                     

select employee_id, department_id, salary

from employees 

where salary > ( select min(salary)

                     from employees

                     where department_id = 20);                     

select employee_id, department_id, salary

from employees 

where salary > all( select salary 

                     from employees

                     where department_id = 20);

                     

select employee_id, department_id, salary

from employees 

where salary > ( select max(salary)

                     from employees

                     where department_id = 20);                     

select employee_id, department_id, salary

from employees 

where salary in ( select salary 

                     from employees

                     where department_id = 20);