0526 WHERE조건, ORDER BY, GROUP BY, 집계함수

* WHERE조건 - 복합조건

축구에 관한 도서 중 가격이 20,000원 이상인 도서를 검색.

select *
from Book
where bookname like '%축구%' and price >=20000;

--출판사가 굿스포츠나 대한미디어인 책 정보 검색
select *
from Book
where publisher = '굿스포츠' or publisher = '대한미디어';

-- 급여가 7000 ~ 10000이외인 사원의 이름과 성 및 급여를 출력

select first_name ||' '|| last_name as Name, salary
from employees
where salary<7000 or salary>10000;

-- 사원의 성 중에 E 및 O 글자가 포함된 사원을 출력. 이때 머리글은 e or o Name이라고 출력하시오.

select employee_id as "e or o Name"
from employees
where last_name like '%e%' or last_name like '%o%';

 

*ORDER BY

도서를 가격 순으로 검색하고, 가격이 같으면 이름순으로 검색하시오.

SELECT *
FROM BOOK
ORDER BY PRICE, BOOKNAME;

--이전 7000~10000 예제를 salary가 적은 순으로 정렬.

select first_name ||' '|| last_name as Name, salary
from employees
where salary<7000 or salary>10000
order by salary;

 

-연습문제

1. 현재 날짜 타입을 날짜 함수를 통해 확인하고, 2006년 05월 20일부터 2007년 05월 20일 사이에 고용된 사원들의 이름과 성(Name), 업무, 입사일을 출력하시오. 단, 입사일이 빠른 순으로 정렬.

select first_name ||' '|| last_name as Name, job_id, hire_date
from employees
where hire_date between '06/05/20' and '07/05/20'
order by hire_date;

2. 급여와 수당율(commission_pct)에 대한 지출 보고서를 작성. 수당을 받는 모든 사원의 이름과 성(Name), 급여, 업무, 수당율을 출력. 급여가 큰 순서대로 정렬하고 같으면 수당율이 큰 순서대로 정렬.

select first_name ||' '|| last_name as Name, salary, job_id, commission_pct
from employees
where commission_pct is not null
order by salary, commission_pct desc;

 

*집계함수

 : 그룹함수라고도 하며 여러 행을 하나의 결과 값으로 도출한다.

SUM, AVG, COUNT, MAX, MIN

- 2반 김연아 고객이 주문한 도서의 총 판매액을 구하시오.

select sum(saleprice) as 총매출
from orders
where custid = 2;

- 고객이 주문한 도서의 총 판매액, 평균값, 최저가, 최고가를 구하시오.

select  sum(saleprice) as Total,
	avg(saleprice) as Average,
	min(saleprice) as Minimum,
	max(saleprice) as Maximum
from orders;

 

*GROUP BY

고객별로 주문한 도서의 총 수량과 총 판매액을 구하시오.

select custid, count(*) as 도서수량, sum(saleprice) as 총액
from orders
group by custid;

where절에는 집계함수를 사용할 수 없음! --> HAVING을 사용한다.

*HAVING

• 반드시 GROUP BY절과 같이 작성.
• WHERE절보다 뒤에 나와야 한다.
• 검색조건에는 SUM, AVG, MAX, MIN, COUNT와 같은 집계함수가 와야한다.

-연습문제(HR)

1. 각 사원이 소속된 부서별로 급여합계, 급여평균, 급여최댓값, 급여 최솟값을 집계해 $ 표시와 함께 출력. 단, 부서에 소속되지 않은 사원에 대한 정보는 제외한다.

select department_id, sum(salary)|| '$', avg(salary)|| '$', min(salary)|| '$', max(salary)|| '$'
from employees
group by department_id
having department_id is not null;

2. 사원들의 업무별 전체 급여평균이 $10000(scott : $3000)보다 큰 경우를 조회하여 업무별 급여 평균을 출력. 단, 업무에 사원이 포함된 경우는 제외하고 전체 급여 평균이 높은 순서대로 출력.

SELECT JOB_ID, AVG(SALARY)
FROM EMPLOYEES
WHERE JOB_ID NOT LIKE '%CRERK'
GROUP BY JOB_ID
HAVING SUM(SALARY)>10000
ORDER BY AVG(SALARY) DESC;

 

*JOIN (조인)

 : 여러 정보를 합치는 것. CROSS / INNER / OUTER / SELF

* 크로스 조인 : 특별한 조건없이 연결

SELECT * 
FROM CUSTOMER CROSS JOIN ORDERS;

--오라클식으로 쓰면
SELECT* FROM CUSTOMER, ORDERS;

* 내부조인(INNER JOIN) : 참조키를 기준으로 일치하는 행만 조인. 연관된 레코드만 조인된다.

- 고객과 고객의 주문에 관한 데이터를 보이시오.(+고객번호 순으로 정렬.)

--ANSI
SELECT *
FROM CUSTOMER INNER JOIN ORDERS
ON CUSTOMER.CUSTID = ORDERS.CUSTID;

--ORACLE
SELECT *
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CUSTID = ORDERS.CUSTID
ORDER BY CUSTOMER.CUSTID;

- 고객별로 주문한 모든 도서의 총 판매액을 구하고, 고객별로 정렬.

SELECT NAME, SUM(SALEPRICE)
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CUSTID = ORDERS.CUSTID
GROUP BY CUSTOMER.NAME
ORDER BY CUSTOMER.NAME;

- 3개 이상

--ANSI
SELECT CUSTOMER.NAME, BOOK.BOOKNAME 
FROM CUSTOMER
INNER JOIN ORDERS ON CUSTOMER.CUSTID = ORDERS.CUSTID
INNER JOIN BOOK ON BOOK.BOOKID = ORDERS.BOOKID;

--ORACLE
SELECT CUSTOMER.NAME, BOOK.BOOKNAME
FROM CUSTOMER, ORDERS, BOOK
WHERE CUSTOMER.CUSTID = ORDERS.CUSTID 
AND ORDERS.BOOKID = BOOK.BOOKID;

 

* 외부조인(OUTER JOIN) : 참조키를 기준으로 일치하지 않는 행도 포함시키는 조인.

- 도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 판매가격을 구하시오.

--ANSI
SELECT CUSTOMER.NAME, SALEPRICE
FROM CUSTOMER LEFT OUTER JOIN ORDERS
ON CUSTOMER.CUSTID = ORDERS.CUSTID;

--ORACLE
SELECT CUSTOMER.NAME, SALEPRICE
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CUSTID = ORDERS.CUSTID(+);
--데이터가 없을 수도 있는 쪽에 (+)

 

* 셀프조인(SELF JOIN) : 자기 테이블을 별칭을 사용해 이용한다.

--사원 'BLAKE'가 관리하는 부하사원의 이름과 직급 출력.
--ANSI
SELECT STAFF.ENAME, STAFF.JOB
FROM EMP STAFF INNER JOIN EMP MANAGER
ON STAFF.MGR = MANAGER.EMPNO
AND MANAGER.ENAME LIKE 'BLAKE';

--ORACLE
SELECT STAFF.ENAME, STAFF.JOB
FROM EMP STAFF, EMP MANAGER
WHERE STAFF.MGR = MANAGER.EMPNO
AND MANAGER.ENAME LIKE 'BLAKE';

 

* 연습문제(HR)

1. HR스키마에 있는 모든 사원의 ID, LAST_NAME, SALARY, 매니저의 LAST_NAME, 소속부서의 NAME을 출력하시오.

SELECT staff.employee_id, STAFF.LAST_NAME, STAFF.SALARY, MANAGER.LAST_NAME MANAGER_NAME, 
departments.department_name
FROM EMPLOYEES STAFF, EMPLOYEES MANAGER, DEPARTMENTS
WHERE STAFF.EMPLOYEE_ID = manager.manager_id
AND staff.department_id = departments.department_id;

2. HR스키마에 존재하는 EMPLOYEES, DEPARTMENTS, LOCATIONS테이블의 구조 파악 후 OXFORD에 근무하는 사원의 이름과 성(NAME), 업무, 부서이름, 도시이름을 출력하시오. 이때 첫번째 열은 회사이름인 'KOREA'라는 상수값이 출력되도록 하시오.

SELECT 'KOREA', EMPLOYEES.FIRST_NAME || EMPLOYEES.LAST_NAME NAME, EMPLOYEES.JOB_ID, 
departments.department_name, locations.city
FROM EMPLOYEES, DEPARTMENTS, LOCATIONS
WHERE employees.department_id = departments.department_id
AND departments.location_id = locations.location_id
AND LOCATIONS.city = 'Oxford';

3. employees, departments 테이블의 구조를 파악한 후 사원 수가 다섯 명 이상인 부서의 부서이름과 사원수를 출력하시오. 사원수가 많은 순으로 정렬.

select departments.department_name, count(employees.employee_id)
from employees, departments
where EMPLOYEES.department_id = departments.department_id
group by departments.department_name
having count(employees.employee_id)>=5
order by count(employees.employee_id) desc;

4. HR에서 각 사원의 사원 이름과 성(Name), 업무, 부서이름, 입사일, 급여, 해당 직무의 최소,최대 급여를 출력.

select employees.First_name ||' '||employees.last_name Name, jobs.job_id, departments.department_name,
        employees.hire_date, employees.salary, jobs.max_salary, jobs.min_salary
from employees, departments, jobs 
where jobs.job_id = employees.job_id
and employees.department_id = departments.department_id;

5. 각 사원과 직속 상사와의 관계를 이용하여 다음과 같은 형식의 보고서를 작성하고자 한다. A reprt to B. 단, 보고할 상사가 없는 사원이 있다면 그 정보도 포함하여 출력하고, 상사의 이름과 성은 대문자로 출력하시오.

select staff.First_name ||' ' || staff.last_name ||' report to ' ||upper(manager.first_name||' ' || manager.last_name)
from employees staff, employees manager
where staff.manager_id = manager.employee_id(+);

 

*연습문제(SCOTT)

1. 81/01/01이후 입사한 사원들의 그룹별 급여총액과 평균급여 구하기.

select deptno, sum(sal) 급여총액, avg(sal) 평균급여
from emp
where hiredate > '81/01/01'
group by deptno;

2. 10,20인 부서에서 근무하는 직원들의 급여의 총액이 5000이상인 경우 직무와 직무별 평균 급여 출력하기.

select job, avg(sal) 평균급여
from emp
where deptno = 10 or deptno = 20
group by job
having sum(sal)>=5000;

 

재밌다...!!