SELECT EMPLOYEE_ID , LAST_NAME, SALARY
FROM EMPLOYEES e
WHERE LAST_NAME = 'Smith'
AND SALARY = 8000
;
-- 1. employees 테이블에서 department_id가 100이고
-- job_id가 FI_MGR인 사람의 모든 데이터 조회
SELECT *
FROM EMPLOYEES e
WHERE DEPARTMENT_ID = 100
AND JOB_ID = 'FI_MGR'
;
-- 2. employees 테이블에서 first_nmae이 Guy인 사람의
-- employee_id, first_name, last_name, job_id를 조회
SELECT FIRST_ID, EMPLOYEE_ID , LAST_NAME , JOB_ID
FROM EMPLOYEES e
WHERE FIRST_NAME ='Guy'
;
-- 3. employees 테이블에서 department_id가 50이고, manager_id가 121인 사람의
-- employee_id, first_name, last_name, job_id 조회
SELECT EMPLOYEE_ID , FIRST_NAME , LAST_NAME , JOB_ID, DEPARTMENT_ID
FROM EMPLOYEES e
WHERE DEPARTMENT_ID = 50
AND MANAGER_ID = 121
;
-- as : 별칭, 알리아스
SELECT EMPLOYEE_ID AS "ID", SALARY "SAL"
FROM EMPLOYEES e
;
-- distinct : 중복 제거
SELECT DISTINCT JOB_ID
FROM EMPLOYEES e
;
-- >, <, >=, <=
SELECT *
FROM EMPLOYEES e
WHERE SALARY = 5000
-- or
SELECT EMPLOYEE_ID, DEPARTMENT_ID, MANAGER_ID, LAST_NAME
FROM EMPLOYEES e
WHERE DEPARTMENT_ID = 50
AND MANAGER_ID = 200
;
-- not
SELECT EMPLOYEE_ID, LAST_NAME, DEPARTMENT_ID
FROM EMPLOYEES e
WHERE NOT (DEPARTMENT_ID = 50)
;
-- not -> <>
SELECT EMPLOYEE_ID, LAST_NAME, DEPARTMENT_ID
FROM EMPLOYEES e
WHERE DEPARTMENT_ID <> 50
;
-- not -> !=
SELECT EMPLOYEE_ID, LAST_NAME, DEPARTMENT_ID
FROM EMPLOYEES e
WHERE NOT DEPARTMENT_ID != 50
;
-- salary 4000~8000
SELECT *
FROM EMPLOYEES e
WHERE SALARY >= 4000
AND SALARY <= 8000
;
-- salary 4000~8000 -> between A and b
SELECT *
FROM EMPLOYEES e
WHERE SALARY BETWEEN 4000 AND 8000
;
-- 급여가 6500 또는 7700 또는 13000인 사람 출력
-- employee_id, first_name, salary
SELECT EMPLOYEE_ID, FIRST_NAME , SALARY
FROM EMPLOYEES e
WHERE SALARY = 6500
OR SALARY = 7700
OR SALARY = 13000
;
-- in() : or와 동일
SELECT EMPLOYEE_ID, FIRST_NAME , SALARY
FROM EMPLOYEES e
WHERE SALARY IN (6500, 7700, 13000)
;
-- 1. employees 테이블에서 employee_id, first_name, last_name, job_id를 출력하는데,
-- job_id가 ST_MAN이고, manager_id가 100이고, salary가 5000이상인 사람.
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID
FROM EMPLOYEES e
WHERE JOB_ID = 'ST_MAN'
AND MANAGER_ID = 100
AND SALARY >= 5000
;
-- 2. EMPLOYEES 테이블에서 EMPLOYEE_ID, first_name, last_name, JOB_ID, MANAGER_ID, SALARY 를 조회
-- DEPARTMENT_ID가 10 또는 30 또는 100 또는 90에 속하고, 급여(SALARY)가 5000에서 10000사이 이고, 매니저(MANAGER_ID)가 100이 아닌 사람을 조회
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY
FROM EMPLOYEES e
WHERE DEPARTMENT_ID IN (10, 30, 90, 100)
AND SALARY BETWEEN 5000 AND 10000
AND MANAGER_ID != 100
;
-- like -> D로 시작하는 사람
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES e
WHERE FIRST_NAME LIKE 'D%'
;
-- like -> d로 끝나는 사람
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES e
WHERE FIRST_NAME LIKE '%d'
;
-- like -> __a%
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES e
WHERE FIRST_NAME LIKE '__a%'
;
-- is null
SELECT *
FROM EMPLOYEES e
WHERE DEPARTMENT_ID is null
;
SELECT *
FROM EMPLOYEES e
WHERE COMMISSION_PCT IS NULL
;
-- is not null
SELECT *
FROM EMPLOYEES e
WHERE COMMISSION_PCT IS NOT NULL
;
-- order by asc : 오름차순 *중요 (asc 생략 가능)
SELECT *
FROM EMPLOYEES e
ORDER BY FIRST_NAME, LAST_NAME
;
-- order by desc -> 내림차순
SELECT *
FROM EMPLOYEES e
ORDER BY SALARY DESC
;
-- 합계 -> sum
SELECT sum(SALARY)
FROM EMPLOYEES e
;
-- count()
SELECT COUNT(COMMISSION_PCT)
FROM EMPLOYEES e
;
SELECT COUNT(FIRST_NAME), COUNT(DISTINCT FIRST_NAME)
FROM EMPLOYEES e
;
-- 평균 : avg()
SELECT AVG(SALARY)
FROM EMPLOYEES e
WHERE DEPARTMENT_ID = 80
;
-- max() : 최대값
SELECT MAX(HIRE_DATE)
FROM EMPLOYEES e
;
-- min() : 최솟값
SELECT min(HIRE_DATE)
FROM EMPLOYEES e
;
-- abs() : 절대값
SELECT ABS(-33)
FROM dual
;
-- round() : 반올림
SELECT ROUND(0.123), ROUND(0.5678)
FROM dual
;
-- trunc() : 절사
SELECT trunc(1234.7567) trunc
FROM dual
;
-- 소수점 뒤에 자리 살리기
SELECT trunc(1234.7567, 2) trunc
FROM dual
;
-- 소수점 앞에 자리 0처리
SELECT trunc(1234.7567, -2) trunc
FROM dual
;
-- 1. 연봉이 12000 초과되는 직원들의 LAST_NAME 및 연봉을 조회한다.
-- 단, 급여 오름차순으로 조회
SELECT LAST_NAME, SALARY
FROM EMPLOYEES e
WHERE SALARY > 12000
ORDER BY SALARY
;
-- 2. 사원번호가 176 인 사람의 LAST_NAME과 부서 번호를 조회한다.
SELECT LAST_NAME, DEPARTMENT_ID
FROM EMPLOYEES e
WHERE EMPLOYEE_ID = 176
;
-- 3. 연봉이 5000 에서 12000의 범위 이외인 사람들의 LAST_NAME 및 연봉을 조회힌다.
SELECT LAST_NAME, SALARY
FROM EMPLOYEES e
WHERE NOT SALARY BETWEEN 5000 AND 12000
;
-- 4. 20 번 및 50 번 부서에서 근무하는 모든 사원들의
-- LAST_NAME, DEPARTMENT_ID를 조회하고, LAST_NAME 알파벳순으로 정렬
SELECT LAST_NAME, DEPARTMENT_ID
FROM EMPLOYEES e
WHERE DEPARTMENT_ID in(20, 50)
ORDER BY LAST_NAME
;
-- 5. 20 번 및 50 번 부서에 근무하며,
-- 연봉이 5000 ~ 12,000 사이인 사원들의 LAST_NAME 및 연봉을 조회한다.
SELECT LAST_NAME, SALARY
FROM EMPLOYEES e
WHERE DEPARTMENT_ID in(20, 50)
AND SALARY BETWEEN 5000 AND 12000
;
-- 6. LAST_NAME 첫 글자가 A 인 사원들의 LAST_NAME 을 조회한다.
SELECT LAST_NAME
FROM EMPLOYEES e
WHERE LAST_NAME LIKE 'A%'
;
-- 7. 매니저가 없는 사람들의 LAST_NAME 및 JOB_ID 를 조회한다.
SELECT LAST_NAME, JOB_ID
FROM EMPLOYEES e
WHERE MANAGER_ID IS NULL
;
-- 문자열 연결 -> concat(char1, char2)
SELECT CONCAT('hello', 'bye'), CONCAT ('good', 'bad'), 'good' || 'bad'
FROM dual
;
-- initcap : 첫글자를 대문자로
SELECT INITCAP('good morning')
FROM dual
;
-- upper(char), lower(char) ; /대소문자
SELECT LOWER('GOOD'), UPPER('good')
FROM dual
;
-- LAPD(값, 총문자 길이, 채움문자)
SELECT LPAD('good', 6), LPAD('good', 7, '#'), LPAD('good', 8, 'A')
FROM dual
;
-- Rpad(값, 총문자 길이, 채움문자)
SELECT RPAD('good', 6), LPAD('good', 7, '#'), LPAD('good', 8, 'A')
FROM dualt
;
-- sysdate
SELECT SYSDATE
FROM dual
;
-- 7개월을 더한다 -> add_months()
SELECT ADD_MONTHS(sysate, 7)
FROM dual
;
-- 현재 달의 마지막 날짜 ->
SELECT LAST_DAY(SYSDATE)
FROM dual
;
SELECT SYSDATE + (INTERVAL'1' YEAR),
SYSDATE + (INTERVAL'1' MONTH),
SYSDATE + (INTERVAL'1' DAY),
SYSDATE + (INTERVAL'1' HOUR),
SYSDATE + (INTERVAL'1' MINUTE),
SYSDATE + (INTERVAL'1' SECOND)
FROM dual
;
-- to_char() 문자열 변환
SELECT TO_CHAR(SYSDATE, 'yyyy/mm/dd')
FROM dual
;
SELECT TO_CHAR(SYSDATE, 'yyyymmdd'),
TO_CHAR(SYSDATE, 'yyyy/mm/dd'),
TO_CHAR(SYSDATE, 'yyyy-mm-dd'),
TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:MI:SS')
FROM dual
;
-- 날짜형으로 변환 : to_date()
SELECT TO_DATE('2024/07/30', 'yyyy/mm/dd')
FROM dual
;
-- nvl() : 널값을 다른 데이터로 변경하는 함수
SELECT FIRST_NAME, LAST_NAME, COMMISSION_PCT, NVL(COMMISSION_PCT, 0) commission
FROM EMPLOYEES e
;
-- switch문의 역할을 하는 함수 : decode()
SELECT *
FROM DEPARTMENTS d
;
SELECT DEPARTMENT_ID, DECODE(DEPARTMENT_ID, 20, 'MA', 60, 'IT', 90, 'EX', 'ETC')
FROM DEPARTMENTS d
;
-- case - when
SELECT FIRST_NAME, DEPARTMENT_ID,
CASE WHEN DEPARTMENT_ID = 20 THEN 'MA'
WHEN DEPARTMENT_ID = 60 THEN 'IT'
WHEN DEPARTMENT_ID = 90 THEN 'EX'
ELSE 'ETC'
END "departments"
FROM EMPLOYEES e
;
-- distinct 중복 제거
-- group by 데이터를 그룹핑해서 그 결과를 가져오는 경우 사용, 집계 함수와 짝울 아루어 사용
SELECT DISTINCT DEPARTMENT_ID
FROM EMPLOYEES e
;
SELECT DEPARTMENT_ID
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID
;
-- 부서별 급여 합계
SELECT DISTINCT DEPARTMENT_ID, SUM(SALARY)
FROM EMPLOYEES e
;
SELECT DEPARTMENT_ID, SUM(SALARY)
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID
;
SELECT EMPLOYEE_ID, SALARY, DEPARTMENT_ID
FROM EMPLOYEES e
ORDER BY DEPARTMENT_ID
;
SELECT DEPARTMENT_ID, EMPLOYEE_ID
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID
;
-- 그룹할 게 없어 의미 없음
SELECT EMPLOYEE_ID
FROM EMPLOYEES e
GROUP BY EMPLOYEE_ID
;
-- 부서별 사원수와 급여의 합, 평균 급여
SELECT DEPARTMENT_ID, SUM(SALARY), AVG(SALARY), COUNT(EMPLOYEE_ID), COUNT(SALARY)
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID
;
-- 부서별 직급별(job_ID) 사원수와 평균 급여
SELECT DEPARTMENT_ID, JOB_ID , SUM(SALARY), AVG(SALARY), COUNT(DEPARTMENT_ID)
FROM EMPLOYEES e
WHERE DEPARTMENT_ID = 80
GROUP BY DEPARTMENT_ID, JOB_ID
ORDER BY DEPARTMENT_ID, JOB_ID
;
-- having 절
-- where절에서는 집계함수를 사용할 수 없다
-- having절은 집계함수를 가지고 조건 비교를 할 때 사용
SELECT DEPARTMENT_ID, COUNT(EMPLOYEE_ID)
FROM EMPLOYEES e
WHERE DEPARTMENT_ID IS NOT NULL
-- AND COUNT(EMPLOYEE_ID)>=10 // where절에서 사용할 수 없음
GROUP BY DEPARTMENT_ID
HAVING COUNT(EMPLOYEE_ID) >= 10
ORDER BY DEPARTMENT_ID
;
문제 연습 코드
-- 1. EMPLOYEES 테이블에서 King의 정보를 소문자로 검색하고
-- 사원번호,성명, 담당업무(소문자로),부서번호를 출력하라.
SELECT EMPLOYEE_ID, LAST_NAME, LOWER(JOB_ID), DEPARTMENT_ID
FROM EMPLOYEES e
WHERE LOWER(LAST_NAME) = 'king'
;
-- 2. EMPLOYEES 테이블에서 King의 정보를 대문자로 검색하고
-- 사원 번호, 성명, 담당업무(대문자로),부서번호를 출력하라.
SELECT EMPLOYEE_ID, LAST_NAME, UPPER(JOB_ID), DEPARTMENT_ID
FROM EMPLOYEES e
WHERE UPPER(LAST_NAME) = 'KING'
;
-- 3. DEPARTMENTS 테이블에서 부서번호와 부서이름,
-- 위치번호를 합하여 출력하도록 하라.(||사용)
SELECT DEPARTMENT_ID || DEPARTMENT_NAME || LOCATION_ID
FROM DEPARTMENTS d
;
-- 4. EMPLOYEES 테이블에서 30번 부서 중 사원번호 이름과
-- 담당 아이디를 연결하여 출력하여라. (concat 사용)
SELECT CONCAT(CONCAT(EMPLOYEE_ID, LAST_NAME), MANAGER_ID)
FROM EMPLOYEES e
WHERE DEPARTMENT_ID = 30
;
-- 5. SALARY+SALARY*nvl(COMMISSION_PCT ,0)이 10000이상이면, 'good',
-- 5000 이상이면, 'average', 1이상 5000미만이면 'bad', 0이면 no good 로 평가하고
-- EMPLOYEE_ID ,FIRST_NAME , SALARY ,COMMISSION_PCT, SALARY+SALARY*nvl(COMMISSION_PCT ,0) 평가를 출력해라.
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, SALARY+SALARY*NVL(COMMISSION_PCT, 0) AS sal,
CASE WHEN SALARY+SALARY*NVL(COMMISSION_PCT, 0) >= 10000 THEN 'good'
WHEN SALARY+SALARY*NVL(COMMISSION_PCT, 0) >= 5000 THEN 'average'
WHEN SALARY+SALARY*NVL(COMMISSION_PCT, 0) >= 1 THEN 'bad'
WHEN SALARY+SALARY*NVL(COMMISSION_PCT, 0) = 0 THEN 'no good'
END AS grade
FROM EMPLOYEES e
ORDER BY sal
;
-- 1) EMPLOYEES 테이블에서 job_id가 'SA'로 시작하는 사람에 대하여
-- 급여의 평균, 최고액, 최저액, 합계를 구하여 출력하여라.
SELECT AVG(SALARY), MAX(SALARY), MIN(SALARY), SUM(SALARY)
FROM EMPLOYEES e
WHERE JOB_ID LIKE 'SA%'
;
-- 2) EMPLOYEES 테이블에 등록되어 있는 인원수, 커미션이 NULL이 아닌 인원수, 연봉평균,
-- 등록되어 있는 부서의 수를 구하여 출력하라.
SELECT COUNT(*), COUNT(COMMISSION_PCT), AVG(SALARY), COUNT(DISTINCT DEPARTMENT_ID)
FROM EMPLOYEES e
;
-- 3) EMPLOYEES 테이블에서 부서별로 인원수, 평균 급여, 최저급여, 최고 급여, 급여의 합을 구하여 출력
SELECT DEPARTMENT_ID, COUNT(*), AVG(SALARY), MIN(SALARY), MAX(SALARY), SUM(SALARY)
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID
;
-- 4) EMPLOYEES테이블에서 각 부서별로 인원수, 급여의 평균, 최저급여, 최고급여, 급여의 합을 구하여
-- 급여의 합이 많은 순으로 출력하라
SELECT DEPARTMENT_ID, COUNT(*), AVG(SALARY), MIN(SALARY), MAX(SALARY), SUM(SALARY) AS totalsal
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID
ORDER BY totalsal DESC
;
-- 5) EMPLOYEES 테이블에서 부서별, 업무별 그룹하여 결과를
-- 부서번호, 업무, 인원수, 급여의 평균, 급여의 합을 구하여 출력하여라.
SELECT DEPARTMENT_ID, JOB_ID, COUNT(*), AVG(SALARY), SUM(SALARY)
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID, JOB_ID
;
-- 6) EMPLOYEES 테이블에서 부서 인원이 4명보다 많은 부서의
-- 부서번호, 인원수, 급여의 합을 구하여 출력하여라.
SELECT DEPARTMENT_ID, COUNT(EMPLOYEE_ID), SUM(SALARY)
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID
HAVING COUNT(*)>4
;
-- 7) EMPLOYEES 테이블에서 급여의 최대값이 10000이상인 부서에 대해서
-- 부서번호, 평균 급여, 급여의 합을 구하여 출력하여라.
SELECT DEPARTMENT_ID, AVG(SALARY), SUM(SALARY)
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID
HAVING max(SALARY) >= 10000
;
-- 8) EMPLOYEES 테이블에서 업무별 급여의 평균이 10000 이상인 업무에 대해서
-- 업무명, 평균 급여, 급여의 합을 구하여 출력하라.
SELECT JOB_ID, AVG(SALARY), SUM(SALARY)
FROM EMPLOYEES e
GROUP BY JOB_ID
HAVING AVG(SALARY) >= 10000
;
-- 9) 각 JOB_ID 별, 최대 연봉, 최소 연봉, 연봉 총 합 및 평균 연봉을 JOB_ID 내림차순으로 조회
SELECT JOB_ID, MAX(SALARY), MIN(SALARY), SUM(SALARY), AVG(SALARY)
FROM EMPLOYEES e
GROUP BY JOB_ID
ORDER BY JOB_ID DESC
;
-- 10) 동일한 직업(JOB_ID)을 가진 사원들의 총 수를 조회한다.
SELECT JOB_ID, COUNT(JOB_ID)
FROM EMPLOYEES e
GROUP BY JOB_ID
;
-- 11) 매니저의 사번 및 그 매니저가 관리하는 직원들 중 최소 연봉을 받는 사원의 연봉을 조회한다.
-- 매니저가 없는 사람들은 제외, 최소 연봉 기준 역순으로 조회한다.
SELECT MANAGER_ID, MIN(SALARY)
FROM EMPLOYEES e
WHERE MANAGER_ID IS NOT NULL
GROUP BY MANAGER_ID
ORDER BY MIN(SALARY) DESC
;
