A_TBL이라는 테이블이 있습니다. 이 테이블에는 con, con2, ... 등의 컬럼이 있는데, 그 중 con이라는 컬럼 안에는 '<table><tr><td><font>...</table>'처럼 HTML 태그가 들어 있습니다. 컬럼의 HTML 태그만 제외하고 SELECT 되도록 udf_StripHTML 사용자 함수를 생성해 보겠습니다.

CREATE FUNCTION
[dbo].[udf_StripHTML]
(@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE
@Start INT
DECLARE
@End INT
DECLARE
@Length INT
SET
@Start = CHARINDEX('<',@HTMLText)
SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
SET @Length = (@End - @Start) + 1
WHILE @Start > 0
AND @End > 0
AND @Length > 0
BEGIN
SET
@HTMLText = STUFF(@HTMLText,@Start,@Length,'')
SET @Start = CHARINDEX('<',@HTMLText)
SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
SET @Length = (@End - @Start) + 1
END
RETURN
LTRIM(RTRIM(@HTMLText))
END
GO

사용예제: html 파싱이 필요한 컬럼을 udf_StripHTML(...) 괄호 안에 기입

select writeid, writernm, deptnm, canm, title,
dbo.udf_StripHTML(con), dbo.udf_StripHTML(con2),
dbo.udf_StripHTML(pre), dbo.udf_StripHTML(mid),
dbo.udf_StripHTML(res), dbo.udf_StripHTML(self1),
dbo.udf_StripHTML(self2), pointssum, workdate
from A_TBL order by workdate asc

→ HTML 코드만 쏙 빠진 채 오리지널 데이터만 SELECT 될 것입니다.
SQL 2000 버전에서는 사용 불가. 2005, 2008 버전에서만 사용 가능.

 
저작자 표시 비영리 동일 조건 변경 허락
신고
환경 : Oracle 10g, Oracle SQL Developer

예를 들어 DSN(데이터 소스 네임)이 board이고,

유저 아이디가 admin, 패스워드가 admin 이라면, ASP에서 아래와 같이 DB를 연동한다.

set conn = Server.CreateObject("ADODB.Connection")

conn.Open "Provider=OraOLEDB.Oracle;Data Source=board;User ID=admin;Password=admin;Persist Security Info=True;"

★ 잠깐? 프로바이더는 어떤 DB를 사용하는가에 따르다.

현재 쓰고 있는 DB에 맞게 프로바이더를 뽑아 내고 싶다면, 일단 바탕화면에서 [새로만들기]로 임의의 "텍스트"파일을 만들고,

확장자를 ".udl"로 바꿔준 다음에 해당 파일에 오른쪽 마우스 클릭, [속성]에 들어간다.

[공급자] 탭을 선택하면 리스트가 주욱 뜨는데 이곳에서 원하는 프로바이더를 [선택]한다. 그리고, [다음]을 누르고

[연결]탭으로 이동한다. 알맞은 데이터 소스 네임과 아이디, 패스워드를 입력하고 [연결테스트]를 실시한 후 올바르게 연결되었

다면 "성공" 메시지 창이 뜰 것이다. [확인] 누르고 [속성]창은 닫는다. 그리고 해당 파일을 메모장으로 열면 원했던 "프로바이더" 문

을 뽑아낼 수 있다. ^^
저작자 표시 비영리 동일 조건 변경 허락
신고
-- 쿼리문의 구조
SELECT first_name
            , last_name
FROM employees;

--

SELECT employee_id
            , last_name
            , salary
FROM employees
WHERE employee_id = 100;

--

SELECT *
FROM employees
WHERE employee_id = 100;

-- 테이블 컬럼 확인
DESC employees;

-- emp last_name이 'Austin', 'Chen'인 사원의 정보를 얻어보자.
-- 출력 결과> 사번, last_name, job_id, salary
SELECT employee_id
            , last_name
            , job_id
            , salary
FROM employees
WHERE last_name = 'Austin' OR last_name = 'Chen';

-- emp

SELECT employee_id
            , last_name
            , job_id
            , salary
FROM employees
-- WHERE salary >= 10000 AND salary <= 15000;
WHERE salary BETWEEN 10000 AND 15000;

-- emp last_name이 'Austin', 'Chen'인 사원의 정보를 얻어보자.
-- 출력 결과> 사번, last_name, job_id, salary
SELECT employee_id
            , last_name
            , job_id
            , salary
FROM employees
-- WHERE last_name = 'Austin' OR last_name = 'Chen';
WHERE last_name IN('Austin', 'Chen');

-- emp에서 입사 일자가 97년인 사원의 정보를 얻어오자.
SELECT employee_id
          , last_name
          , hire_date
FROM employees
WHERE hire_date LIKE '97%';

-- emp에서 이름이 'Au' 사원의 정보를 얻어보자.
SELECT employee_id
            , last_name
            , hire_date
FROM employees
WHERE last_name LIKE 'A%';

-- emp 에서 모든 사원의 정보를 얻어보자.
-- 단, 월급순(내림차순)이다.
-- 출력결과 > 사번, 이름(라), 월급
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary ASC;

-- 전체 사원의 평균 월급
SELECT AVG(salary)
FROM employees;

-- 

SELECT AVG(salary)
FROM employees
WHERE salary>10000;

--

-- 부서번호가 50인 사원의 평균 월급을 구하라.

SELECT AVG(salary)
FROM employees
WHERE department_id = 50;

-- 각 부서의 평균 월급을 구하라.
-- 출력 결과 > 부서번호, 평균 월급
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC;

-- 

SELECT TO_CHAR(hire_date, 'YYYY'), AVG(salary)
FROM employees
GROUP BY TO_CHAR(hire_date, 'YYYY')
HAVING AVG(salary) >= 10000;

-- 부서 번호가 30, 50이고 월급이 5000 이하인 사원의 정보를 구하자.
-- 출력결과 > 사원번호, 이름(라), 부서번호, 월급
SELECT employee_id, last_name, department_id, salary
FROM employees
WHERE (department_id = 30 OR department_id = 50) AND salary <= 5000;

-- 부서번호가 10 또는 20인 부서의 최대 월급을 구하자.
-- 출력결과 > 부서번호, 최대월급
SELECT department_id, MAX(salary)
FROM employees
WHERE department_id IN (10, 20)
GROUP BY department_id;

-- 위와 똑같음
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING department_id IN (10, 20);

--

-- 직무 ID 별 최대 월급을 구하자. MAX(salary) 사용.
-- 출력 결과 > 직무ID , 최대월급
SELECT job_id, MAX(salary)
FROM employees
GROUP BY job_id
ORDER BY job_id ASC;

-- 직무 ID가 'SA_REP' 거나 'SA_MAN' 이고,
-- 입사일자가 98년인 사원의 정보를 구하자.
-- 출력 결과 > 사원번호, 이름(라), 직무ID, 입사일자
SELECT employee_id, last_name, job_id, hire_date
FROM employees
WHERE (job_id = 'SA_REP' OR job_id = 'SA_MAN') AND --hire_date LIKE '98%';
-- TO_CHAR(hire_date, 'YYYY') = '1998';
EXTRACT (YEAR FROM hire_date) = '1998';

-- 단일 행 함수(문자, 숫자, 날짜, NULL, 변환, 기타)
SELECT CAST('22-OCT-1997' AS TIMESTAMP WITH LOCAL TIME ZONE) 
   FROM dual;

--

SELECT job_id,
  CASE job_id
        WHEN  'SA_MAN' THEN
        'Sales Dept'
        WHEN 'SA_REP' THEN
        'Sales Dept'
        ELSE
        'Another'
  END CASE
FROM jobs
WHERE job_id LIKE 'S%';

-- 사원번호가 150, 170인 사원의 정보를 얻어보자.
-- 출력결과 > 사번, 이름, 월급, commission_pct, 매니저ID
SELECT employee_id, last_name, salary, commission_pct, manager_id
FROM employees
-- WHERE employee_id = 150 OR employee_id = 170;
WHERE employee_id IN(150, 170);

-- 사원의 월별 입사자 수를 얻어보자.
-- 출력결과> 월, 입사자 수
SELECT TO_CHAR(hire_date, 'mm') AS hd, count(*)   -- 별칭(애칭)을 hd를 사용
FROM employees
GROUP BY TO_CHAR(hire_date, 'mm');
ORDER BY hd;

-- 주민등록번호 770525-1567423을
-- 770525-1******으로 나타내 보자.
--SELECT RPAD('770525-1567423', 8, '*')
SELECT CONCAT(SUBSTR('770525-1567423', 1, 8), '******')
FROM DUAL;

--???
SELECT RPAD('770525-1567423', 20, '*')
FROM DUAL;

-- 각 사원의 월급 등급을 알아보자.
-- 출력 결과 > 사원번호, 이름, 월급, 등급(10단계)
SELECT employee_id,
            last_name,
            salary,            
WIDTH_BUCKET(salary, 2100, 24000, 10) AS GRADE
FROM employees
ORDER BY GRADE DESC;

-- NULL과의 연산
SELECT NULL + 10
FROM DUAL;

-- 직원들의 예상 월급을 구하라.
-- 출력결과>사번,이름,월급,commision_pct,예상월급
SELECT employee_id,
              last_name,
              salary,
              salary + salary * commission_pct --NVL(TO_CHAR(commission_pct), 0) AS "commision"
FROM employees;

-- 분기별 입사 인원 수를 구하라.
-- 출력결과 > 분기, 입사 인원 수
SELECT CEIL(EXTRACT (MONTH FROM hire_date)/3) AS quater,
            , COUNT(*)
FROM employees
GROUP BY CEIL(EXTRACT (MONTH FROM hire_date)/3);

-- 사원의 근무기간을 구하라.
-- 출력결과 > 사원번호, 이름, 입사일자, 근무기간(년), 근무기간(월)
SELECT employee_id,
              last_name,
              hire_date,
              FLOOR(months_between(SYSDATE, hire_date)/12) AS "근무기간(년)",
              FLOOR(MOD(months_between(SYSDATE, hire_date), 12)) AS "근무기간(월)"
FROM employees;


SELECT ADD_MONTHS(SYSDATE, 12)
FROM dual;

--

SELECT --employee_id
                CAST(employee_id AS CHAR(6))
FROM employees;

--
SELECT TO_CHAR(SYSDATE, 'YYY-MM-DD HH24:MI:SS')
FROM DUAL;

-- 오늘 + 1년
SELECT SYSDATE + 365 AS PLUSDATA
FROM DUAL;

SELECT TO_DATE('2008-01-01') + 365 AS PLUSDATA
FROM DUAL;

-- 분기별 입사 인원수를 구하자.
-- 출력 결과 > 분기, 입사인원
SELECT CEIL(EXTRACT(MONTH FROM hire_date)/3) AS quater,
            COUNT(*)
FROM employees
GROUP BY CEIL(EXTRACT(MONTH FROM hire_date)/3)
ORDER BY quater;

-- TO_CHAR(hire_date)를 사용해서 위와 같은 결과 값 출력
SELECT TO_CHAR(hire_date, 'Q') AS quater
FROM employees
--GROUP BY TO_CHAR(hire_date, 'D')
ORDER BY quater;

-- job_id가 'SA_MAN'인 사원은 'Sales_Manager',
-- 'SA_REP'인 사원은 'Sales_Representative'
-- 기타 사원은 'Other'로 표시하여라(DECODE 사용)
-- 출력결과 > 사번, 이름, job_id, job_id fullname
SELECT employee_id as "사번",
             last_name as "이름",
            job_id,
DECODE(job_id, 'SA_MAN', 'Sales_Manager', 'SA_REP', 'Sales_Representative', 'Other') AS AAA
FROM employees WHERE job_id LIKE 'S%'
ORDER BY job_id ASC;

-- 사원의 월급 랭크를 만들어 보자.
-- 5000미만이면 l, 5000이상이면 M, 10000이상이면 H
-- 출력 결과 > 사원번호, 이름, 월급, 등급
SELECT employee_id,
              last_name,
              salary,
    CASE employee_id
            WHEN salary<5000 THEN 'L'
            WHEN salary<10000 THEN 'M'
            ELSE 'H'
    END CASE
FROM employees;

-- 부서별 인원 수를 구해보자.
SELECT department_id, COUNT(*)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;

SELECT job_id, SUM(salary)
FROM employees
GROUP BY job_id;


-- job_id별 최대 월급을 산출하라.
-- 단, 최대 월급이 10000 이상인 그룹만 표시하라.
SELECT DISTINCT JOB_ID
            , MAX(salary) OVER(PARTITION BY JOB_ID ORDER BY JOB_ID DESC) as "Salary Over"
FROM employees;

-- INLINE VIEW (테이블 안에 테이블을 한번 더 정의. FROM 절 안에 쿼리가 포함되어 있는 형태)
SELECT *
FROM(
SELECT DISTINCT JOB_ID, MAX(salary) OVER(PARTITION BY JOB_ID ORDER BY job_id DESC) as mscx
FROM employees)
WHERE mscx >= 10000;

-- RANK(), ORDER BY 절이 항상 필요
SELECT employee_id
            , salary
            , RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) as rnk
FROM employees; -- department_id 별로 묶어서 순위를 출력(PARTITION BY department_id)


-- 부서별 월급이 1~5등인 사원의 정보를 보자. INLINE VIEW로 작성.
-- 단, 사원이 5명 이상인 부서로 한한다.
-- 출력결과 > 사번, 이름, 월급, 등급
SELECT *
FROM
          (SELECT employee_id
                      , last_name
                      , salary
                      , RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) as rnk --부서별로 등급이 어떻게 되는지?
                      , COUNT(*) OVER(PARTITION BY grade) as cnt --속한 부서의 인원이 몇 명인지?
          FROM employees)
WHERE rnk <= 5 AND cnt >=5; -- 사원수(cnt)가 5명 이상,  부서별 월급이 1~5등(rnk)

-- 각 사원의 월급 등급을 부서 평균과 비교하여 다음과 같이 등급을 정하자. 평균보다 작으면 L, 평균과 같으면 S, 크면 H
-- 출력결과 > 사번, 이름, 월급, 등급
SELECT employee_id
            , last_name
            , salary
            , RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) as rnk
            --, ROUND(AVG(salary) OVER (PARTITION BY department_id), 0) as avg
            , CASE
              WHEN salary < AVG(salary) OVER (PARTITION BY department_id)  THEN
                        'L'
              WHEN salary = AVG(salary) OVER (PARTITION BY department_id) THEN
                        'S'
              WHEN salary > AVG(salary) OVER (PARTITION BY department_id) THEN
                        'H'
            END as 등급
FROM employees;

-- NTILE
SELECT salary,
             NTILE(10) OVER(ORDER BY salary ASC) as ntile
FROM employees;

-- 부서별로 입사 순위가 빠른 순서로 순위 1,2위를 얻어보자.
-- 출력결과> 부서번호, 이름, 사원번호, 순위
SELECT *
FROM(
          SELECT department_id
                      , last_name
                      , employee_id
                      , RANK() OVER(PARTITION BY department_id ORDER BY hire_date DESC) as rnk
          FROM employees)
where rnk IN(1,2);

-- job_id별로 월급이 낮은 20%의 사람의 정보를 얻어보자.
-- 출력결과> job_id, 사번, 이름, 월급, 등급(%)
SELECT *
FROM(
        SELECT job_id
                    , employee_id
                    , last_name
                    , salary
                    , ROUND(PERCENT_RANK() OVER(ORDER BY salary DESC), 2) as RANK
        FROM employees)
WHERE RANK <= 0.2;


-- ROW_NUMBER(), 1부터 10까지 출력(INLINE VIEW)
SELECT *
FROM(
        SELECT employee_id
                    , department_id
                    , salary
                    , ROW_NUMBER() OVER(ORDER BY salary DESC) AS rn
        FROM employees
        WHERE department_id = 80)
WHERE rn BETWEEN 1 AND 10;

-- JOIN
SELECT employee_id
            , department_id
FROM employees
WHERE department_id IN(80, 90);

SELECT department_id
            , department_name
FROM departments
WHERE department_id IN(80, 90);

-- 비명시적 JOIN (하나의 쿼리문으로 두 개의 테이블에 있는 데이터를 한꺼번에 가져올 수 있다)
SELECT employee_id
            , e.department_id
            , department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id         -- employees에 있는 department_id와 departments에 있는 department_id를 연결한다.
AND e.department_id IN(80, 90);

-- 부서의 정보를 얻어보자
-- 출력결과> 부서번호, 부서이름, 부서위치(도시이름-LOCATIONS)
SELECT department_id
            , department_name
            , city
FROM departments d, locations l
WHERE d.location_id = l.location_id;

-- 명시적 JOIN
SELECT d.department_id
            , d.department_name
            , city
FROM departments d JOIN locations l
ON d.location_id = l.location_id;   -- ON은 employees와 department을 JOIN하기 위한 조건을 정의한다.

-- 사원번호가 132, 150번인 사원에 대하여 월급 정보를 얻어보자.
-- 출력결과> 사번, 월급, 최소 월급, 최대 월급(JOBS)
SELECT employee_id
            , salary
            , j.min_salary
            , j.max_salary
FROM employees e JOIN jobs j
ON employee_id IN(132,150)
WHERE e.job_id = j.job_id;

-- 사원번호(employees)가 164, 170인 사원의 job_title(jobs), department_name(departments)을 얻어보자.
-- 출력결과> 사번, job_title, department_name (비명시적)
SELECT employee_id
            , job_title
            , department_name
FROM  employees e, jobs j, departments d
WHERE e.job_id = j.job_id AND e.department_id = d.department_id
AND employee_id IN(164,170);


-- 명시적 JOIN으로 바꿔서 출력
SELECT employee_id
            , job_title
            , department_name
FROM employees e JOIN jobs j
  ON e.job_id = j.job_id
JOIN departments d
  ON e.department_id = d.department_id
AND employee_id IN(164, 170);


-- OUTER JOIN(null 데이터 출력을 위한 JOIN)
-- 1999년 1월부터 6월까지 입사한 인원의 정보를 얻어보자.
SELECT employee_id
            , last_name
            , hire_date
FROM employees
WHERE TO_CHAR(hire_date, 'YYYY-MM') BETWEEN '1999-01' AND '1999-06'
ORDER BY hire_date ASC;

-- 위 예제에 부서 이름 출력
SELECT employee_id
            , last_name
            , hire_date
            , department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id
WHERE TO_CHAR(hire_date, 'YYYY-MM') BETWEEN '1999-01' AND '1999-06'
ORDER BY hire_date ASC;

-- 위 예제를 비명시적으로 바꿈
SELECT employee_id
            , last_name
            , hire_date
            , department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)    -- (+)를 추가
AND TO_CHAR(hire_date, 'YYYY-MM') BETWEEN '1999-01' AND '1999-06'
ORDER BY hire_date ASC;


-- 부서와 부서 관리자 정보를 얻어보자.
-- 단, 부서의 관리자가 없는 부서의 정보도 얻자. OUTER JOIN으로 해야함.
-- 출력결과> 부서이름, 부서관리자 아이디, 부서관리자 이름
SELECT department_name
            , d.manager_id
            , last_name
FROM employees e RIGHT OUTER JOIN departments d   -- ON에서 지정한 컬럼 데이터에 NULL이 있는 쪽을 LEFT/RIGHT로 지정함.
ON e.employee_id = d.manager_id;
--ON e.manager_id = d.manager_id;


-- 비명시적
SELECT department_name
            , d.manager_id
            , last_name
FROM departments d, employees e
WHERE d.manager_id = e.employee_id(+);

-- 각 사원의 매니저 이름을 얻어보자. (SELF JOIN. 자기 자신을 JOIN)
-- 출력결과> 사원번호, 이름, 매니저의 사번, 매니저 이름
SELECT a1.employee_id
            , a1.last_name
            , a2.manager_id
            , a2.last_name
FROM employees a1 RIGHT OUTER JOIN employees a2
ON a2.manager_id = a1.employee_id;

-- 사원(EMP), 부서(DEPT), 급여등급(SALGRADE)
-- 1. 사원 테이블에서 사원번호가 7369, 7698 번인 사원번호와 이름을 출력하세요?
SELECT empno
           , ename
FROM emp
WHERE empno IN(7369, 7698);

-- 2. 사원 테이블에서 사원번호가 7369, 7698 번인 아닌 사원번호와 이름을 출력하세요?
SELECT empno
           , ename
FROM emp
WHERE empno != 7369 AND empno != 7698;

-- 3. 사원 테이블에서 급여(SAL)가 3000에서 5000사이인 사원 정보를 다 출력하세요?
SELECT *
FROM emp
WHERE sal >= 3000 AND sal <= 5000;

-- 4. 사원 테이블에서 고용일자(HIREDATE)가 1981년 12월 1일 이후 고용된 사원 정보를 다 출력하세요?
SELECT *
FROM emp
WHERE TO_CHAR(hiredate, 'YYYY-MM-DD') >= '1981-12-01'
ORDER BY hiredate ASC;

-- 5. 사원 테이블에서 직업(JOB)이 SALESMAN 중에서 사원번호의 최대값을 출력하세요?
SELECT MAX(empno), job
FROM emp
WHERE job = 'SALESMAN'
GROUP BY job;

-- 6. 사원 테이블에서 각 사원에 부서명을 아래 예제처럼 출력하세요
-- (사원, 부서 테이블 조인 시 부서가 없는 사원은 출력 안함)
-- ex) 정렬은 부서명(DNAME), 사원명(ENAME) 오름차순 
SELECT dname
            , empno
            , ename
FROM emp e JOIN dept d
ON e.deptno = d.deptno
ORDER BY dname ASC;
 
-- 7. 사원 테이블에서 각 사원에 부서명을 아래 예제처럼 출력하세요
-- (사원, 부서 테이블 조인 시 부서가 없는 사원도 출력)
-- ex) 정렬은 부서명(DNAME), 사원명(ENAME) 오름차순
SELECT dname
            , empno
            , ename
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno = d.deptno
ORDER BY dname ASC;

-- 8. 부서 위치가 'DALLAS', 'CHICAGO' 곳에 근무하는 사원 정보 아래 
-- 예제처럼 출력하세요
-- ex) 정렬은 부서위치(LOC) 내림차순, 사원명(ENAME) 오름차순
SELECT loc
            , empno
            , ename
FROM emp e JOIN dept d
ON e.deptno = d.deptno
WHERE d.loc IN('DALLAS', 'CHICAGO')
ORDER BY LOC DESC;
 
-- 9. 부서별 최고 급여(SAL) 금액을 아래 예제처럼 출력하세요
-- ex) 부서 없는 사원은 제외
SELECT d.deptno
            , MAX(sal)
FROM emp e JOIN dept d
ON e.deptno = d.deptno
GROUP BY d.deptno
ORDER BY deptno ASC;
 
-- 10. 부서별 최고 급여(SAL) 금액을 받는 사원 정보를 아래 예제처럼 출력하세요
-- ex) 부서 없는 사원은 제외
SELECT DISTINCT deptno
                          , MAX_SAL
                          , empno
                          , ename
                          , job
FROM emp,
(SELECT DISTINCT MAX(sal)
 OVER(PARTITION BY deptno ORDER BY deptno ASC) AS MAX_SAL
 FROM emp)
WHERE deptno IS NOT NULL AND MAX_SAL=sal
ORDER BY deptno;
 
-- 전체 사원에 대한 평균 월급을 구하자.
SELECT AVG(salary)
FROM employees;
-- 전체 사원 중에서 평균 월급보다 많이 받는 사원을 구하자. (서브쿼리)
SELECT employee_id
            , last_name
            , salary
FROM employees
WHERE salary >  (SELECT AVG(salary)
                            FROM employees
                            );

-- 직무별 최대 급여를 받는 사원의 정보를 얻자.
-- 부서별 최대월급을 받는 사람이 한 명이 아니라, 복수의 사람이 출력되면 WHERE 절에서 서브쿼리를 작성해야 한다.
-- 출력결과 > 사번, 이름, 월급

SELECT job_id
            , MAX(salary)
FROM employees
GROUP BY job_id;
------------------------------ 일단 이 정도, 그 다음에

SELECT employee_id
            , last_name
            , salary
FROM employees
WHERE (job_id, salary) IN(SELECT job_id
                                                      , MAX(salary)
                                          FROM employees
                                          GROUP BY job_id
                                          );
----------- 이렇게 서브쿼리를 작성해서, 직무별 최대 급여를 받는 사원의 정보를 얻었다.


-- 부서별로 입사일이 가장 오래된 사원의 정보를 얻자.
-- 출력결과 > 사번, 이름, 부서, 입사일자
SELECT job_id
            , MIN(hire_date)
FROM employees
GROUP BY job_id;

SELECT employee_id
            , last_name
            , department_id
            , hire_date
FROM employees
WHERE (department_id, hire_date) IN(SELECT department_id
                                                                          , MIN(hire_date)
                                                              FROM employees
                                                              GROUP BY department_id
                                                              );

-- 위 예제를 상관하위질의로 바꿔라.
SELECT employee_id
            , last_name
            , department_id
            , hire_date
FROM employees e1
WHERE hire_date = (SELECT MIN(hire_date)
                                  FROM employees e2
                                  WHERE e2.department_id = e1.department_id
                                  );

-- 위 예제를 JOIN으로 바꿔라.
SELECT employee_id
            , last_name
            , e.department_id
            , hire_date
FROM employees e JOIN(SELECT department_id
                                                    , MIN(hire_date) as mnhrd
                                         FROM employees
                                         GROUP BY department_id
                                         ) d
ON e.department_id = d.department_id
WHERE e.hire_date = d.mnhrd;


-- JOIN
SELECT employee_id
            , last_name
            , e.job_id
            , salary
FROM employees e JOIN(SELECT job_id
                                                    , MAX(salary) as mxsal
                                         FROM employees
                                         GROUP BY job_id
                                         ) v
ON e.job_id = v.job_id
WHERE e.salary = v.mxsal;

-- 상관하위질의
SELECT employee_id
            , last_name
            , job_id
            , salary
FROM employees e1
WHERE salary = (SELECT MAX(salary)
                            FROM employees e2
                            WHERE e2.job_id = e1.job_id
                           );


-- 부서별 평균 급여보다 적게 받는 사원의 정보를 얻어보자.
-- 출력결과 > 사번, 이름, 부서이름, 급여
-------------------------------------------------------------- JOIN으로 작성
SELECT employee_id
            , last_name
            , e.department_id
            , salary
FROM employees e JOIN(SELECT department_id
                                                    , AVG(salary) as agsal
                                         FROM employees
                                         GROUP BY department_id
                                         ) v
ON e.department_id = v.department_id
JOIN departments d
ON e.department_id = d.department_id
WHERE e.salary < v.agsal;

-------------------------------------------------------------- 상관하위질의
SELECT employee_id
            , last_name
            , department_id
            , salary
FROM employees e1
WHERE salary < (SELECT AVG(salary) as agsal
                            FROM employees e2
                            WHERE e1.department_id = e2.department_id
                           );
-------------------------------------------------------------- 

-- JOIN
SELECT employee_id
            , last_name
            , e.job_id
            , salary
FROM employees e JOIN(SELECT job_id
                                                    , MAX(salary) as mxsal
                                         FROM employees
                                         GROUP BY job_id
                                         ) v
ON e.job_id = v.job_id
WHERE e.salary = v.mxsal;


SELECT employee_id
            , last_name
            , job_id
            , salary
FROM employees e1
WHERE salary = (SELECT MAX(salary)
                            FROM employees e2
                            WHERE e2.job_id = e1.job_id
                           );
 
-- SEQUENCE
INSERT INTO dept(deptno
                         , dname)
VALUES(dept_id_seq.NEXTVAL
              , 'CHA')

--

 CREATE SEQUENCE dept_id_seq
 START WITH 50
 INCREMENT BY 10
 MAXVALUE 999;
 
 


-- 1. 사원 테이블에서 각 사원에 급여(SAL) 등급을 아래 예제처럼 출력하세요.
SELECT empno
            , ename
            , sal
            , grade
FROM emp e JOIN salgrade s
ON e.sal < s.hisal AND e.sal > s.losal
ORDER BY grade ASC;

-- 2. 사원 테이블에서 평균 급여(SAL) 보다 높은 사원 정보를 아래 예제처럼 출력하세요.
SELECT empno
            , ename
            , job
            , sal
FROM emp
WHERE sal >  (SELECT AVG(sal)
                            FROM emp
                            )
ORDER BY sal DESC;

-- 3. 사원 테이블에서 부서별 평균 급여(SAL) 보다 높은 사원 정보를 아래 예제처럼 출력하세요.
-- 정렬은 급여 내림차순
SELECT dname
            , empno
            , ename
            , job
            , sal
FROM emp e JOIN(SELECT deptno
                                          , AVG(sal) as agsal
                                         FROM emp
                                         GROUP BY deptno
                                         ) v
ON e.deptno = v.deptno
JOIN dept d
ON e.deptno = d.deptno
WHERE e.sal > v.agsal
ORDER BY sal DESC;  

-- 4. 사원 테이블에서 각 사원에  급여(SAL) 순위 점수 별로 인원수를 아래 예제처럼 출력하세요.
-- 급여순위점수(SALGRADE) 테이블 JOIN  
SELECT DISTINCT grade
                              , losal
                              , hisal
                              , COUNT(*) OVER(PARTITION BY grade) as cnt
FROM emp e JOIN salgrade s
ON e.sal > s.losal AND e.sal < s.hisal
ORDER BY grade ASC;


-- 5. 부서명이 'RESEARCH' 이거나 부서위치가 'NEW YORK' 사원 정보를 아래 예제처럼 출력하세요
SELECT dname
            , loc
            , empno
            , ename
FROM emp e JOIN dept d
ON e.deptno = d.deptno
WHERE dname = 'RESEARCH' OR loc = 'NEW YORK'
ORDER BY dname ASC;

-- 6. 사원 테이블에서 EMPNO, MGR, SAL 세 개의 컬럼을 단순 숫자 의미로 가정 할 경우 세 개의 값 중 최대값(MAX_VALUE), 최소값(MIN_VALUE) 을 아래 예제처럼 출력하세요
-- ex) EMPNO, MGR, SAL 컬럼 중 널 값이 존재할 경우 0으로 치환
-- 정렬은 최대값 내림차순
SELECT empno
              , mgr
              , sal
              , GREATEST(TO_NUMBER(NVL(EMPNO,0))
              , TO_NUMBER(NVL(MGR,0))
              , TO_NUMBER(NVL(SAL,0))) MAX_VALUE
              , LEAST(TO_NUMBER(NVL(EMPNO,0)),TO_NUMBER(NVL(MGR,0)),TO_NUMBER(NVL(SAL,0))) MIN_VALUE
FROM emp
ORDER BY 4 DESC;


-- 7. 부서 테이블에서 DNAME 컬럼의 길이가 6자리 이상이면은 5자리까지 데이터만 보여주고 ‘..’ 뒤에 붙여준다. 아래 예제처럼 출력하세요
SELECT dname
            , CONCAT(SUBSTR(dname, 1, 5), '..')
FROM dept;


-- 8. 사원 테이블에서 고용일자(HIREDATE) 해당 년 월의 마지막 날짜(HIREDATE_MONTH_LASTDAY), 고용일자부터 2006/08/05 
-- 일자까지 근무일(WORK_DAY)을 아래 예제처럼 출력하세요
SELECT empno
            , hiredate
            , LAST_DAY(hiredate)
            , TO_DATE('2006/08/05','YYYY/MM/DD') - hiredate WORK_DAY
FROM emp; 
저작자 표시 비영리 동일 조건 변경 허락
신고

+ Recent posts

티스토리 툴바