[Oracle DB(오라클)/#2 SQL 함수]

SeungWoo·2024년 1월 3일

[ORACLE(오라클)/#]

목록 보기
6/7
post-thumbnail

다중행 함수

  • WHERE절에는 함수가 올 수없다

COUNT

  • COUNT(*) : 전체 행의 수를 출력 ( NULL포함 )
  • COUNT(expr) : exor을 만족하는 행의 수를 출력( NULL 제외 )
SELECT COUNT(department_id)
FROM employees;

DISTINCT

  • all이 default값으 넣어져 있어서 DISTINCT를 사용해야 된다.
  • 중복 제거
SELECT count(DISTINCT department_id)
FROM employees;

is NULL

  • 부서가 없다 ( NULL값이다 )
SELECT last_name, department_id
FROM employees
WHERE department_id is NULL;

LIKE

  • %는 0개 이상 앞뒤로 붙으면 
SELECT last_name, department_id, salary
FROM employees
WHERE job_id like '%REP%';

GROUP

  • 특정 컬럼값으로 묶기
SELECT job_id,sum(salary), trunc(sum(salary)/count(last_name))
FROM employees
WHERE job_id like '%REP%'
GROUP by job_id;

SELECT job_id, sum(salary), AVG(salary)
FROM employees
WHERE job_id like '%REP%'
GROUP by job_id;

HAVING

  • GROUP에 오는 조건문
SELECT job_id, sum(salary), AVG(salary)
FROM employees
WHERE job_id like '%REP%'
GROUP by job_id
HAVING sum(salary) > 7000;

ROWNUM

  • 어떤 특정 테이블에서 데이터를 조회할때 수도 칼럼
  • 진짜로 존재하는 COLUMN이 아니라 오라클에서 사용되는 가상 COLUMN이고, 각행의 고유한 순번이 있다
  • 일반적으로 결과 집합에서 특정 순서를 할당하거나, 일부 행을 제한하거나, 특정 순서로 정렬된 결과를 가져올 때 사용
SELECT ROWNUM, salary
FROM employees;

ORDER BY

  • 데이터의 정렬 ( 제일 마지막에 진행 된다 )
  • ASC 사용사, 내림차순(default)
  • DESC 사용시, 올림차순
  • ROWNUM이 선언이 되고, ORDER BY가 실행된다
SELECT ROWNUM, salary
FROM employees
WHERE ROWNUM < 5
ORDER BY salary;

ORDER BY ASC

  • 내림차순( Default )
SELECT employee_id, last_name, salary*12 as "ANNSAL"
FROM employees
WHERE department_id = 60 
ORDER BY ANNSAL DESC;

ORDER BY DESC

  • 올림차순
SELECT employee_id, last_name, salary*12 as "ANNSAL"
FROM employees
WHERE department_id = 60 
ORDER BY ANNSAL DESC;

서브쿼리

하나의 SQL문안에 포함되어 있는 또 다른 SQL문

  • 소괄호()로 표현된 쿼리
  • 각각 SQL문을 한번에 실행하기 위해서
  • 메인 쿼리문 안에 서브 쿼리문을 씀( 메인쿼리가 서브쿼리를 포함하는 종속적인 관계 )
  • 실행순서
    FORM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

서브 쿼리

  • SELECT절에는 SELECT절이 있다.
SELECT last_name, hire_date
FROM employees
WHERE hire_date = (SELECT  MIN(hire_date) FROM employees);

WHERE 서브쿼리

SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (SELECT salary 
                FROM employees 
                WHERE last_name = 'Abel');

FROM 서브쿼리

<부서별로 가장 적은 임금>
SELECT department_id, Min(salary)
FROM employees
GROUP BY department_id;

ORDER BY랑 ROWNUM

  • order by는 마지막에 실행된다.
  • 그래서 ROWNUM이랑 같이 쓰기 위해 실행순서를 서브쿼리 안에서 order by를 실행하고 rownum를 실행
SELECT last_name, salary
FROM (SELECT last_name, salary
      FROM employees
      ORDER BY salary ASC)
WHERE ROWNUM < 2;

IN

  • 검색된 값 중에 하나만 일치하면 TRUE
SELECT employee_id, last_name, salary
FROM employees
WHERE salary IN (SELECT salary
                FROM employees
                WHERE last_name = 'King');

ANY

  • 검색된 조건( 특정한 값이 아닌 범위)에 맞는 것이 1개 이상이면 TRUE
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > ANY(SELECT salary
                FROM employees
                WHERE last_name = 'King');

ALL

  • 모든 검색된 값과 조건에 맞아야 TRUE
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > ALL(SELECT salary
                FROM employees
                WHERE last_name = 'King');

ORDER BY HAVING

SELECT department_id, min(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (SELECT MIN(salary)
                FROM employees
                where department_id = 50);

SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id
HAVING AVG(salary) = (SELECT MIN(AVG(salary))
                            FROM employees 
                            GROUP BY job_id);

ASC and DESC

SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 60
ORDER BY salary ASC, employee_id desc;

JOIN

2개 이상의 테이블을 열결 또는 결합하여 데이터를 출력하는 것
( 2개 이상의 테이블을 하나의 테이블처럼 보이게 출력하기 위해 사용 )

  • INNER JOIN
    • Equi / Non-Equi
    • 조인 조건에 대해서 동일한 값이 있는 행을 리턴
    • 정확히 매칭되는 집합만을 경과 집합으로 리턴
    • 일반적으로 조인이라고 하면 INNER JOIN을 가리킴
    • NATUAL JOIN
    • 3-Way JOIN
      • 공통된 칼럼이 존재해야만 INNER JOIN이 가능합니다.
      • 두 테이블 사이에는 공통된 서로의 관계가 성립되지 않는다.
  • OUTER JOIN
    • OUTER 집합을 기준으로 조인한다.

NATURAL JOIN

  • 공통된 조건이 하나없지만, 자동으로 조인을 실행해주는 것
  • 이름도 같고, 데이터 타입도 같아야 자동으로 JOIN 해준다.
  • 두 테이블의 동일한 이름(컬럼명)을 컬럼은 모두 조인이 된다.
SELECT department_id, department_name, Location_id, city
FROM departments
NATURAL JOIN locations;

SELECT department_id, department_name, Location_id, city
FROM departments
NATURAL JOIN locations
WHERE department_id IN (20,50);

USING

  • JOIN할때 특정 공통된 칼럼이 있을때, 그 값을 가져온다.
  • USING에서는 알리아스에서 주는 특정 칼럼을 명칭하면 안된다.
SELECT employee_id, last_name, location_id, department_id
FROM employees
JOIN departments
USING(department_id);

SELECT city, department_name
FROM locations 
JOIN departments
USING (location_id);

ALIAS

  • JOIN을 할때, 특정 칼럼이 중복될 경우 특정 칼럼을 추출하기 위해 별칭을 달고, 구분지어야 된다 
<알리아스로 주고 조건을 쓸떄 알리아스를 쓰면 안된다>
SELECT L.city, D.department_name
FROM locations L
JOIN departments D
USING (location_id)
WHERE location_id=1400; 
(X) WHERE L.location_id=1400;

SELECT first_name, department_name, E.manager_id
FROM employees E 
JOIN departments D
USING (department_id)
WHERE department_id = 50;

ON

  • ALIAS로 구분지어논 같은 칼럼을 가지고 값을 추출해라
SELECT employee_id, E.department_id, location_id
FROM employees E
JOIN departments D
ON E.department_id = D.department_id;

3-way JOIN

  • 공통된 테이블이 없어서 브릿지 역할을 하기위해 3중 JOIN을 사용한다
  • 공통된 두 테이블를 먼저 JOIN을 끝내고 나서 다시 JOIN을 한다.
SELECT employee_id, city, department_name
FROM employees E
JOIN departments D
ON E.department_id = D.department_id 
JOIN locations L
ON D.location_id = L.location_id;

INNER JOIN + ON + WHERE

SELECT E.employee_id, E.last_name, E.department_id, D.location_id
FROM employees E
JOIN departments D
ON E.department_id = D.department_id
WHERE E.employee_id = D.manager_id;

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
JOIN departments d
ON E.department_id = D.department_id
WHERE e.last_name = 'Matos';

ON 대신 WHERE 추가 조건은 AND

  • JOIN 대신 ' , '로 두 테이블을 구분 짖고 조인 조건 ON 대신 WHERE을 사용하면서, 추가 요구 조건은 AND로 덫붙여서 사용한다.
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
JOIN departments d
ON E.department_id = D.department_id
WHERE e.last_name = 'Matos';

==============같은 의미 ============================

SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.last_name = 'Matos';

(3-way-join)ON 대신 WHERE 추가 조건은 AND

  • 추가적인 조건문이나 추가 테이블은 AND로 연결 지을 수 있다.
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.location_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;
profile
SeungWoo
This is my study archive
이전 포스트

[Oracle DB(오라클)/#1 SQL 함수]

다음 포스트

[Oracle DB(오라클)/#3 SQL 함수]

0개의 댓글