-- 쿼리문의 구조
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;
, 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;
, 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;
'Developer Diary' 카테고리의 다른 글
| plakia.net 도메인 시작합니다 (0) | 2010.09.23 |
|---|---|
| PC 조립 완료 (1) (0) | 2010.07.11 |
| WIS 2010 참관 후기 (0) | 2010.05.29 |
| 한국정보기술연구원(KITRI) 이공계전문기술연수(안드로이드 과정) (0) | 2010.05.06 |
| 아시아경제 '한국 IT 노동자' 야근 실태…. (4) | 2010.04.21 |
