21.02.09 TIL (D+22 Oracle Select)

학습 내용

- Select

 

 형식 : 
        SELECT ( 값, 컬럼명, 함수, SUB QUERY) 
        FROM   (테이블명, SUB QUERY)

SELECT 1 FROM DUAL;
SELECT * FROM EMP;

 

 - 현재 계정의 테이블 정보 모두 명세

SELECT * FROM TAB;

 

- ALIAS (컬럼명 설정)

SELECT EMPNO AS "사원 번호", SAL AS 월급, SAL * 12 "일년치 연봉" FROM EMP;

 

- DISTINCT : 중복행 삭제

SELECT DISTINCT JOB FROM EMP; -- 중복 제거

 

---

-  WHERE 조건절   -> if
    

 

 

   &&(AND), ||(OR), ( )

-- && == AND
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME = 'Shanta'
    AND LAST_NAME = 'Vollman';
    
-- || == OR
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME = 'Shanta'
    OR LAST_NAME = 'Vollman';

 

 비교연산자 ( > < >= <= = != <> )

- ex) 급여가 $9000 이상인 사원

SELECT first_name, SALARY
FROM employees
WHERE SALARY >= 9000;

 

- ex) 이름의 첫 스펠링이 J보다 큰 이름

SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE FIRST_NAME >= 'J';

 

 

NULL, IS NULL, IS NOT NULL

- ex) 매니저가 있는 사원

SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE MANAGER_ID IS NOT NULL;

- ALL(=AND), ANY(=OR)

SELECT *
FROM EMPLOYEES
WHERE FIRST_NAME = ALL('Julia', 'John'); -- 'Julia'이면서 'John'

SELECT *
FROM EMPLOYEES
WHERE FIRST_NAME = ANY('Julia', 'John'); -- 'Julia'이거나 'John'

 

- IN, NOT IN

 

SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE FIRST_NAME IN('Julia', 'John'); --'Julia' 또는 'John'인 사람

SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY NOT IN(8000,3200,6000); -- 세 급여 이외의 금액

 

- LIKE

SELECT FIRST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME LIKE 'G_ra_d'; -- _ 한글자

SELECT FIRST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME LIKE 'K%y';    -- % 글자 수에 관계없이 모두 허용

---

 ORDER BY == SORTING
    
    SELECT  COLUMN VALUE SUB QUERY
    FROM    TABLE SUB QUERY
    WHERE   IF
    ORDER BY COLUMN ASC(오름차순) DESC(내림차순)

 

SELECT ENAME, SAL
FROM EMP
ORDER BY SAL ASC;   -- SAL 오름차순

SELECT ENAME, SAL
FROM EMP
ORDER BY SAL DESC;  -- SAL 내림차순

 

년, 월, 일

SELECT EXTRACT(YEAR FROM TO_DATE('210623', 'YYMMDD')) AS 연도,
        EXTRACT(MONTH FROM TO_DATE('210623', 'YYMMDD')) AS 월,
            EXTRACT(DAY FROM TO_DATE('210623', 'YYMMDD')) AS 일,
    EXTRACT(DAY FROM SYSDATE) AS 일
FROM DUAL;    

 

시, 분, 초

SELECT EXTRACT(HOUR FROM CAST(TO_DATE('2021-06-23 19:42:02', 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP)) AS 시,
    EXTRACT(MINUTE FROM CAST(SYSDATE AS TIMESTAMP)) AS 분,
    EXTRACT(SECOND FROM CAST(SYSDATE AS TIMESTAMP)) AS 초
FROM DUAL;

---

    GROUP BY : 그룹으로 묶는 기능
    HAVING : 묶었을 때 조건

 

SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY department_id ASC;     -- 솔팅을 따로 해줘야한다.

 

   통계 - GROUP FUNCTION (그룹함수)

    COUNT, SUM, AVG, MAX, MIN

 

SELECT COUNT(SALARY), COUNT(*), SUM(SALARY), AVG(SALARY), MAX(SALARY), MIN(SALARY)
FROM EMPLOYEES
WHERE JOB_ID = 'IT_PROG';

SELECT JOB_ID, COUNT(*), SUM(SALARY), AVG(SALARY)
FROM EMPLOYEES
GROUP BY JOB_ID
ORDER BY JOB_ID;

SELECT JOB_ID, SUM(SALARY)
FROM EMPLOYEES
GROUP BY JOB_ID
HAVING SUM(SALARY) >= 100000;

 

 

ex) 급여가 5000이상 받는 사원만으로 합계를 내서 업무(JOB_ID)로 그룹화하여 급여의 합계가 20000을 초과하는 업무명을 구하라.

SELECT JOB_ID, SUM(SALARY), MIN(SALARY)
FROM EMPLOYEES
WHERE SALARY >= 5000
GROUP BY JOB_ID
HAVING SUM(SALARY) > 20000;