Part 03. SQL 기본 및 활용

-- 모든 테이블 검색 SELECT * FROM tabs; -- 전체 사원 검색 SELECT * FROM emp; -- 특정 사원 검색 SELECT empno,ename FROM emp WHERE empno=1000;

-- 급여 순서대로 정렬하기 SELECT * FROM emp ORDER BY sal; -- 급여 역순서대로 정렬하기 SELECT * FROM emp ORDER BY sal DESC; -- 두개의 기준으로 정렬하기 -- 1.급여가 높은순서대로 정렬 -- 2.사원번호가 빠른 순으로 정렬 SELECT empno, sal FROM emp ORDER BY sal DESC, empno;

-- DISTINCT 중복제거 SELECT DISTINCT deptno FROM EMP ORDER BY deptno;

-- 칼럼 별칭 (Alias) SELECT DISTINCT deptno AS "부서번호" FROM EMP ORDER BY deptno;

-- 부서(10)에서 급여가 2000$ 이상 SELECT * FROM dept; SELECT * FROM emp WHERE deptno=10 AND sal >= 2000;

-- LIKE 예시 (1) -- 이름이 T로 시작하는 모든 단어 검색하기 SELECT * FROM emp WHERE ename LIKE 'T%'; -- 이름이 TEST로 시작하는 모든 단어 검색 SELECT * FROM emp WHERE ename LIKE 'TEST%'; -- 이름이 TEST로 시작하는 단어(1글자 추가됨) SELECT * FROM emp WHERE ename LIKE 'TEST_'; -- 이름이 TEST로 시작하는 단어(2글자 추가됨) SELECT * FROM emp WHERE ename LIKE 'TEST__';

-- 급여가 $1500 ~ $2000 SELECT * FROM emp WHERE sal BETWEEN 1500 AND 2000; -- NOT BETWEEN 문 SELECT * FROM emp WHERE sal NOT BETWEEN 1500 AND 2000;

-- 모든 직업의 종류를 출력하기 SELECT DISTINCT job FROM emp; -- 직업이 Clert(점원)이나 Manager(매니저) 인 직원을 검색 SELECT * FROM emp WHERE job IN ('CLERK', 'MANAGER');

-- 매니저(mgr)가 배정되어 있지 않은 직원 검색 SELECT * FROM emp WHERE mgr IS NULL; -- 매니저(mgr)가 배정되어 있는 직원 검색 SELECT * FROM emp WHERE mgr IS NOT NULL; -- NVL 함수 (빈값을 치환한다) SELECT mgr, NVL(mgr, 0) FROM emp; -- NVL2 함수 (빈값을 2가지 방법으로 치환한다) -- mgr이 빈값이면 0을 보여주고 -- mgr이 빈값이 아니면 1을 보여준다 SELECT mgr, NVL2(mgr, 1, 0) FROM emp; -- NULLIF -- mgr이 1000일 때 null을 출력한다 -- mgr이 null일 때도 null을 출력한다 -- 나머지 경우는 원래 값을 출력한다 SELECT mgr, NULLIF(mgr, 1000) FROM emp;

-- 부서정보 검색 SELECT * FROM DEPT; -- 부서별로 급여 총액을 알고 싶다! SELECT deptno, SUM(sal) FROM emp GROUP BY deptno; -- GROUP BY문의 조건추가 -- HAVING -- 부서의 급여 총액이 $20,000 이상인 부서를 검색한다 SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) > 20000; -- GROUP BY 집계함수 -- COUNT 함수: 개수 구하기 -- COUNT(*)는 null을 포함한다. SELECT COUNT(*) FROM emp; -- COUNT(칼럼명)는 null을 제외한다. -- 직원 중에서 매니저가 배정된 직원만 검색한다. SELECT COUNT(mgr) FROM emp; -- GROUP BY 사용 예제 (교재 119쪽) -- 부서별 급여의 평균을 구한다 SELECT deptno, AVG(sal) FROM emp GROUP BY deptno; -- 부서별, 관리자별 급여의 평균을 구한다 (높은순으로) SELECT deptno, mgr, AVG(sal) FROM emp GROUP BY deptno, mgr ORDER BY AVG(sal) DESC; -- 직업별로 그룹을 지어서 검색한다 -- 모든 직업 검색 (DISTINCT 중복제거) SELECT DISTINCT job FROM emp; -- 직업별로 급여의 합계와 평균 SELECT job, SUM(sal), AVG(sal) FROM emp GROUP BY job; -- 직업별로 평균 급여가 $2000 이상인 직업 SELECT job, SUM(sal), AVG(sal) FROM emp GROUP BY job HAVING AVG(sal) >= 2000; -- 사원 번호가 1000 ~ 1003번까지 검색해서 -- 부서별로 급여의 합계와 평균을 계산한다 SELECT deptno, SUM(sal), AVG(sal) FROM emp WHERE empno BETWEEN 1000 AND 1003 GROUP BY deptno;

-- 아이돌 멤버 테이블 생성 alter session set nls_date_format='YYYY-MM-DD'; CREATE TABLE IDOL ( IDX NUMBER PRIMARY KEY, IDOL_NAME VARCHAR2(100), IDOL_BIRTH DATE, IDOL_GROUP VARCHAR2(100) ); -- DROP TABLE IDOL; -- 자료 입력 -- 첫번째 자료 입력 INSERT INTO IDOL VALUES (1, '카리나', '2000-04-11', '에스파'); -- 두번째 자료 입력 INSERT INTO IDOL VALUES (2, '지젤', '2000-10-30', '에스파'); -- 세번째 자료 입력 INSERT INTO IDOL VALUES (3, '윈터', '2001-01-01', '에스파'); -- 네번째 자료 입력 INSERT INTO IDOL VALUES (4, '닝닝', '2002-10-23', '에스파'); -- BTS 자료 입력(멤버 7명) INSERT INTO IDOL VALUES (5, '진', '1992-12-04', 'BTS'); INSERT INTO IDOL VALUES (6, '슈가', '1993-03-09', 'BTS'); INSERT INTO IDOL VALUES (7, '제이홉', '1993-03-09', 'BTS'); INSERT INTO IDOL VALUES (8, 'RM', '1994-09-12', 'BTS'); INSERT INTO IDOL VALUES (9, '지민', '1995-10-13', 'BTS'); INSERT INTO IDOL VALUES (10, '뷔', '1995-12-30', 'BTS'); INSERT INTO IDOL VALUES (11, '정국', '1997-09-01', 'BTS'); -- 블랙핑크 INSERT INTO IDOL VALUES (12, '지수', '1995-01-03', '블랙핑크'); INSERT INTO IDOL VALUES (13, '제니', '1996-01-16', '블랙핑크'); INSERT INTO IDOL VALUES (14, '로제', '1997-02-11', '블랙핑크'); INSERT INTO IDOL VALUES (15, '리사', '1997-03-27', '블랙핑크'); -- 모든 자료 검색하기 SELECT * FROM IDOL; -- 2000년 이후에 태어난 아이돌 멤버를 검색하기 SELECT * FROM IDOL WHERE IDOL_BIRTH >= 2000; -- 에러 발생 (자료형이 다르다!!) -- 형변환을 합니다 SELECT * FROM IDOL WHERE IDOL_BIRTH >= TO_DATE('2000-01-01', 'YYYY-MM-DD'); -- 2000년 이전에 태어난 아이돌 멤버를 검색하기 SELECT * FROM IDOL WHERE IDOL_BIRTH < TO_DATE('2000-01-01', 'YYYY-MM-DD');

-- 내장형 함수 (교재 123쪽) DESC DUAL; -- 문자열 함수 SELECT ASCII('a'), -- 결과: 97 SUBSTR('ABC', 1, 2), -- 결과: AB LENGTH('ABC'), -- 결과: 3 RTRIM(' ABC '), -- 오른쪽 공백 제거 LTRIM('****ABC**', '*') -- 결과: 'ABC**' FROM DUAL; -- 날짜형 함수 SELECT SYSDATE, -- 현재 날짜 SYSTIMESTAMP, -- 현재 날짜와 시간 EXTRACT(YEAR FROM SYSDATE) -- 연도 정보 FROM DUAL; -- 숫자형 함수 SELECT ABS(-1), -- 절대값 반환 SIGN(10), -- 양수/음수 구분 MOD(4, 2), -- 나머지값 CEIL(10.9), -- 올림 FLOOR(10.1), -- 내림 ROUND(10.222, 1) FROM DUAL;

-- DECODE 문 SELECT empno, DECODE( empno, 1000, 'TRUE', 'FALSE' ) FROM emp; -- CASE문 SELECT CASE WHEN empno = 1000 THEN 'A' WHEN empno = 1001 THEN 'B' ELSE 'C' END FROM emp;

-- WITH 문 WITH viewData AS ( SELECT * FROM emp UNION ALL SELECT * FROM emp ) SELECT * FROM viewData WHERE empno=1000; -- 부서번호(depno)가 30인 것의 -- 임시 테이블을 만들고 조회하기 WITH W_EMP AS ( SELECT * FROM emp WHERE deptno=30 ) SELECT * FROM W_EMP;