SQL

 

SQL

SQL 작동 순서

from → where → group → by → having → select → order by

select 에 컬럼을 넣으려면 group by 로 컬럼을 선언해줘야 사용 가능하다

where절과 having절은 모두 조건을 주지만 where절은 group by와 전혀 상관없는 조건을 줄 때 사용하고

having절은 group by로 묶인 컬럼들에 대한 조건을 선언해준다.

 

예제 1

--1. 부서별 인원수를 출력하세요
--[출력형식]
--부서명 인원수
--[출처] 조인연습 - hr계정|작성자 heaves1
SELECT d.DEPARTMENT_NAME , COUNT(e.EMPLOYEE_ID) 
FROM DEPARTMENTS d , EMPLOYEES e 
WHERE d.DEPARTMENT_ID = e.DEPARTMENT_ID 
GROUP BY DEPARTMENT_NAME 
ORDER BY COUNT(e.EMPLOYEE_ID) DESC

 

예제 2

--2. IT 부서에서 일하는 직원의 first_name, last_name, salary 를 출력하세요.
--출력결과는 salary 가 낮은 사람부터 출력하시요. 
--전체 결과는 아래 수행결과처럼 하나의 문자열로 결합되어서 나와야 하며 
--전체 결과 하나의 컬럼으로 출력되도록 작성하세요.
--[출처] 조인연습 - hr계정|작성자 heaves1
SELECT e.FIRST_NAME || e.LAST_NAME || '의 연봉은 ' || e.SALARY || '만원' AS 결과 
FROM EMPLOYEES e , DEPARTMENTS d 
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID AND d.DEPARTMENT_NAME = 'IT'
ORDER BY e.SALARY

 

예제 3

--3. 각 사원(employee)에 대해서 사번(employee_id), 이름(first_name), 업무명(job_title), 부서명(department_name)를 조회하시오. 
--단 도시명(city)이 ‘Seattle’인 지역(location)의 부서(department)에 근무하는 직원만 출력하시오.
--[출처] 조인연습 - hr계정|작성자 heaves1
SELECT e.EMPLOYEE_ID , e.FIRST_NAME , j.JOB_TITLE , d.DEPARTMENT_NAME 
FROM EMPLOYEES e , DEPARTMENTS d , LOCATIONS l , JOBS j 
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID 
AND e.JOB_ID = j.JOB_ID 
AND d.LOCATION_ID = l.LOCATION_ID 
AND l.CITY = 'Seattle'

 

조인의 종류

• 오라클 조인

• ANSI 조인 : 표준 조인

 

오라클 조인

1. Equijoin : 두개 이상의 테이블간에 컬럼 값들이 정확하게 일치하는 경우 사용 = 연산자를 이용해서 값들이 정확하게 일치하는 경우 조인 PK와 FK 관계를 이용(내부 조인)

2. non-equi join : 정확하게 일치하는 게 아니라 사이의 값을 가져오는 경우 사용 = 연산자가 아니라 다른 연산자를 이용해서 조인 emp의 sal 값이 salgrade테이블의 losal과 hisal 사이에 있는지 조건을 적용해서 해당 grade를 조회

   SELECT e.EMPNO , e.ENAME , e.SAL , s.GRADE 
   FROM EMP e , SALGRADE s 
   WHERE e.SAL >= s.LOSAL AND e.SAL <= s.HISAL

3. outer join join 조건을 정확하게 만족하지 못하는 경우에 모든 행들을 조회하는 경우 사용하는 조인 (+) 연산자를 이용해서 작업 select 컬럼1, 컬럼2… from 테이블1, 테이블2… where 테이블1.컬럼1 (+) = 테이블2.컬럼2 outer 조인은 어느 한쪽 테이블이 기준이 되어 다른 테이블의 연결되는 조건에 상관없이 기준이 되는 테이블이 모두 추출되는 조인 어느 한 테이블의 데이터를 모두 보고 싶은 경우 기준이 되는 반대편 컬럼에 (+)를 추가해서 조인 즉, 데이터가 부족한 부분의 컬럼에 + 를 추가한다.

   INSERT INTO emp(empno, ename, HIREDATE) VALUES (7777, 'jin', sysdate);
   
   INSERT INTO emp(empno, ename, HIREDATE) VALUES (8888, 'jbj', sysdate);
   
   COMMIT;
   
   SELECT * FROM EMP e
   
   SELECT d.DNAME , e.ENAME , e.HIREDATE 
   FROM EMP e , DEPT d 
   WHERE e.DEPTNO = d.DEPTNO (+)
   -- 부서 정보가 등록되지 않았지만 jin과 jbj가 모두 출력된다.
   
   -- 응용
   -- d.name이 null 값인 jbj와 jin을 신입사원으로 분류해서 카운팅하는 과정
   SELECT NVL(d.DNAME , '신입사원') 부서  , COUNT(e.EMPNO)  FROM EMP e , DEPT d
   WHERE d.DEPTNO (+) = e.DEPTNO
   GROUP BY d.DNAME

4. self 조인 ⇒ 동일한 테이블을 이용해서 조인 ⇒ 동일한 테이블을 from절에 두번 이상 명시행 하므로 반드시 alias를 추가해야 한다. ⇒ 동일한 테이블의 동일한 컬럼이라 하더라도 의미부여에 따라 전혀 다른 컬럼이 될 수 있음

   SELECT e.ENAME , e.SAL , e.HIREDATE , m.ENAME 관리자명 , m.EMPNO , m.MGR 
   FROM EMP e ,EMP m
   WHERE e.MGR = m.EMPNO

 

ANSI 조인

1. inner 조인 select alias.컬럼1,alias.컬럼2…. from 테이블1 inner join 테이블2 on 테이블1.컬럼1 = 테이블2.컬럼2 (조인조건) where…

   -- inner는 생략 가능
   SELECT d.DNAME , e.ENAME 
   FROM EMP e 
   	INNER JOIN DEPT d 
   	ON e.DEPTNO =d.DEPTNO
   
   SELECT d.DNAME , e.ENAME 
   FROM DEPT d 
   	INNER JOIN EMP e  
   	ON e.DEPTNO = d.DEPTNO
   
   -- 여러개도 조인 가능
   SELECT d.DNAME , e.ENAME , l.CITY 
   FROM EMP e 
   	JOIN DEPT d 
   	ON e.DEPTNO = d.DEPTNO 
   	JOIN LOCATIONS l 
   	ON d.LOC_CODE  = l.LOC_CODE

2. outer 조인 select alias.컬럼1,alias.컬럼2…. from 테이블1 left(right) outer join 테이블 2 on 테이블1.컬럼1 = 테이블2.컬럼2 where ….

   -- 왼쪽에 있는 테이블을 모두 출력한다
   -- 개념적으로 생각했을땐 이 방법이 이해하기 더 쉽다
   SELECT d.DNAME , e.ENAME 
   FROM EMP e 	
   	LEFT OUTER JOIN DEPT d 
   	ON e.DEPTNO = d.DEPTNO
   
   -- 예제
   SELECT NVL(d.DNAME , '신입사원') 부서  , COUNT(e.EMPNO)
   FROM EMP e
   	LEFT OUTER JOIN DEPT d 
   	ON d.DEPTNO = e.DEPTNO 
   GROUP BY d.DNAME 
   ORDER BY COUNT(e.EMPNO)

 

서브쿼리

 

• 다른 sql문에 삽입된 select문

• 서브쿼리는 괄호로 묶어야 한다.

• 비교를 위해 사용하는 연산자의 오른쪽에 서브쿼리를 정의

• 일반적으로는 서브쿼리가 먼저 실행되고 서브쿼리의 결과를 이용해서 메인쿼리가 실행

 

1. 단일행 서브쿼리 ⇒ 서브쿼리의 결과가 1행 1열 ⇒ 비교연산자(=,<,>,≤,≥,<>)를 이용해서 작업이 가능

   SELECT *
   FROM EMP e 
   WHERE SAL > (SELECT AVG(SAL) FROM EMP)
   
   SELECT *
   FROM EMP e 
   WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ename = 'SCOTT')
   
   -- 실습예제
   
   --1. 20번 부서의 최고 급여보다 급여가 많은 사원들의 목록
   SELECT *
   FROM EMP e , DEPT d 
   WHERE e.DEPTNO = d.DEPTNO 
   AND e.SAL > (SELECT  MAX(SAL) FROM EMP WHERE DEPTNO = 20)
   				
   --2. 7566번 사원보다 급여를 많이 받는 사원의 목록
   SELECT *
   FROM EMP e
   WHERE e.SAL > (SELECT SALFROM EMP e WHERE e.EMPNO = 7566)

2. 다중행 서브쿼리 ⇒ 서브쿼리의 결과가 1열 여러 행인 경우 ⇒ 비교연산자를 사용할 수 없다. ⇒ in, any, all

   -- sal이 2900 이상인 사원과 같은 부서에 근무하는 사원의 목록
   SELECT *
   FROM EMP e 
   WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE sal >= 2900)

3. 다중컬럼 서브쿼리 ⇒ 서브쿼리의 결과가 여러 행과 여러 개의 열이 있는 경우 두개 이상의 컬럼을 결과로 반환하는 경우 where (컬럼1, 컬럼2…) in (값1, 값2) 
4. SELECT * FROM EMP e WHERE (DEPTNO, SAL) IN (SELECT DEPTNO , MIN(SAL) FROM EMP GROUP BY DEPTNO)