예제 쿼리들

예제 테이블은 다음 글을 읽고, 대~~~충 만들어라..

https://goldencrab.tistory.com/entry/%EC%98%A4%EB%9D%BC%ED%81%B4-%EC%83%98%ED%94%8C-%ED%85%8C%EC%9D%B4%EB%B8%94-%EB%B0%8F-%EB%8D%B0%EC%9D%B4%ED%84%B0

오라클 샘플 테이블 및 데이터

 

 

 

SQL Developer 단축키

F10 계획실행(PLAN) - 

 

형변환함수 3가지만 있음

to_date, to_char, to_number

 

예제 쿼리들.

 

SELECT NAME, SUBSTR(JUMIN, 1, 2) || '년' || SUBSTR(JUMIN, 3, 2) || '월' || SUBSTR (JUMIN, 5, 2) || '일' BIRTYDAY FROM STUDENT WHERE DEPTNO  = 101;  -- 문자열 1부터 시작함. 0 아님.

 

SELECT sysdate-1, sysdate, sysdate+1 FROM dual;

 

-- instr 특정 문자가 나오는 위치 (예 - 괄호가 나오는 위치)

select name, tel, instr(tel, ')') 위치

from studen

where depntnol = 201;

 

-- 1전공이 101인 학생들의 지역번호를 조회(instr, substr)

SELECT NAME, SUBSTR(TEL, 1, INSTR(TEL, ')')-1) AS AREA_CODE

FROM STUDENT

WHERE DEPTNO1 = 101;

 

SELECT LTRIM('oooracle', 'o') , RTRIM('oracleeee', 'e'), FROM dual;

 

SELECT TRIM(' apple '), length(' apple '), length(trim(' apple ')) from  dual;

 

SELECT LPAD('oracle', 12, '*'), RPAD('oracle', 12, '*') FROM dual;

 

SELECT REPLACE('oracle database 10g release 2', '10g', '19c') FROM dual;

 

SELECT round(0.12345678), round(0.12345678, 0) FROM dual;  -- 같은 의미로, 정수 만들때

 

SELECT round(0.12345678, 3), round(0.12345678, 4) FROM dual; -- 소수점 3자리, 4자리

 

-- 마이너스이면, 점 왼쪽 2자리에서 올려봐라. 여기선 3이라 반올림 못함

SELECT round(1234.12345678, -2) left_round, round(1234.12345678, 2) right_round FROM dual;

 

SELECT TRUNC(1234.12345678) zero1,
       TRUNC(1234.12345678, 0) zero2,     -- 위와 동일. 소수점들은 전부 날림
       TRUNC(1234.12345678, -2) left_trunc, -- 1200. 마침표 왼쪽으로 계산
       TRUNC(1234.12345678, 2) right_trunc,  -- 소수점 2자리만 남기고 없애버려라
       TRUNC(1234.12345678, 5) right_trunc2 -- 소수점 5자리만 남기고 없애버려라
FROM dual;

 

select * from v$nls_parameters;

 

- 날짜와 날짜의 합(가산)은 안된다.  즉, 빼기만 가능

SELECT SYSDATE - HIRE_DATE FROM employees;

 

 

-- 몇달 지났는지 계산법
SELECT MONTHS_BETWEEN(SYSDATE , HIRE_DATE)  FROM employees;

-- 소수점이 나오니까 round 를 함

SELECT ROUND(MONTHS_BETWEEN('2022-10-03' , '2021-09-28'))  FROM dual;

 

SELECT ADD_MONTHS(HIRE_DATE, 6)  FROM employees; -- 입사일후 6개월 후는???

 

SELECT LAST_DAY(SYSDATE) FROM dual;  -- 이번달은 마지막 날이 언제인가??

?

SELECT NEXT_DAY(sysdate, '월') FROM dual;  -- 다음 월요일은 언제 인가???

 

 

SELECT round(sysdate, 'year') FROM dual;  -- 다음 해는

 

 

 

SELECT FIRST_NAME , HIRE_DATE, ROUND(HIRE_DATE, 'MM')
FROM EMPLOYEES 
WHERE 1=1 
AND JOB_TITLE = 'Sales Representative'
AND MANAGER_ID = '46';

 

SELECT deptno, job, count(*) cnt FROM emp GROUP BY deptno, job ORDER BY cnt DESC;

실행순서 : from --> where --> group by --> having --> select --> order by

 

 

-- 부서별 사원수가 5보다 작은 부서는?...having 절에서는 alias 못쓰는게 select 에서 alias 가 만들어지기에...

SELECT DEPTNO, COUNT(*) CNT
FROM EMP e 
GROUP BY DEPTNO 
HAVING count(*) < 5;

 

-- 부서별 평균 급여가 2000 이상이 부서번호와 평균 급여
SELECT deptno, round(avg(sal)) avg_sal
FROM EMP e 
GROUP BY deptno
HAVING avg(sal) > 2000;

 

-- 1981년도 입사한 사원들의 deptno, job 별 급여 합계가 5000 이상인 deptno, job 을 
-- 급여 합계가 많은 순으로 출력하세요.
SELECT DEPTNO, JOB, SUM(SAL) SUM
FROM EMP e
WHERE TO_CHAR(HIREDATE, 'yy') = '81'
GROUP BY DEPTNO, JOB 
HAVING sum(sal) >= 5000
ORDER BY sum DESC;

 

--만약 HIREDATE 에 인덱스가 걸려 있다면...인덱스가 걸린 컬럼에 함수를 쓰면 인덱스를 못타니까...

SELECT DEPTNO, JOB, SUM(SAL) SUM
FROM EMP e
WHERE HIREDATE BETWEEN TO_DATE('19810101', 'yyyymmdd') AND TO_DATE('19811231', 'yyyymmdd')  
GROUP BY DEPTNO, JOB 
HAVING sum(sal) >= 5000
ORDER BY sum DESC;

 

 

 

SELECT deptno, sum(sal) 
FROM EMP e 
GROUP BY rollup(deptno)

위 문장을 다르게 표현하면..

SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO 
UNION
SELECT NULL, SUM(SAL) FROM EMP;

 

 

SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP e 
GROUP BY ROLLUP(DEPTNO, JOB)

====>

SELECT deptno, job, sum(sal)
FROM EMP e 
GROUP BY deptno, JOB 
UNION ALL 
SELECT DEPTNO, NULL JOB , sum(sal)
FROM EMP e2
GROUP BY DEPTNO 
UNION ALL 
SELECT NULL DEPTNO , NULL job, sum(sal)
FROM EMP e3 
ORDER BY deptno, job;

 

 

select emp.first_name, emp.department_id, dep.department_name

from employees emp, departments dep

where emp.department_id = dep.department_id;