190402

-- 오라클의 논리 연산자
-- AND
-- OR
-- NOT
 
-- 80번 부서에 소속된 사원 중에서 급여를 5000달러 이상 받는 사원의 이름과 급여, 직종, 부서 아이디를 조회하기
SELECT FIRST_NAME, SALARY, JOB_ID, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80
AND SALARY >= 5000;
 
-- 90번 부서에 소속된 사원 중에서 관리자 아이디가 100인 사원의 이름, 급여, 직종을 조회하기
SELECT FIRST_NAME, SALARY, JOB_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90
AND MANAGER_ID = 100;
 
-- 커미션을 받는 사원 중에서 소속 부서가 지정되지 않는 사원의 이름, 급여, 커미션을 조회하기
SELECT FIRST_NAME, SALARY, COMMISSION_PCT
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL 
AND DEPARTMENT_ID IS NULL;
 
-- 커미션이 0.3 이상이고, 직종이 SA_MAN이며, 이름이 'A'로 시작하는 사원의 이름, 커미션, 직종을 조회
SELECT FIRST_NAME, COMMISSION_PCT, JOB_ID
FROM EMPLOYEES
WHERE COMMISSION_PCT >= 0.3
AND JOB_ID = 'SA_MAN'
AND FIRST_NAME LIKE 'A%';
 
-- 소속 부서가 80번 부서이고 급여를 5000달러 초과 10000 미만 받는 사원의 이름, 급여 조회하기
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80
AND SALARY BETWEEN 5000 AND 10000;
 
-- 소속 부서가 60이거나 90인 사원의 이름, 소속 부서를 조회하기
SELECT FIRST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (60, 90);
 
-- 급여를 3000달러 이하로 받는 사원과 급여를 15000달러 이상으로 받는 사원의 이름, 급여 조회하기
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
where SALARY <=3000
OR SALARY >= 15000;
 
-- 소속 부서가 50번 부서에 소속된 사원 중에서
-- 급여를 3000달러 이하로 받거나 급여를 15000달러 이상으로 받는 사원의 이름, 급여, 부서 아이디를 조회
SELECT FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50 
AND (SALARY <= 3000 OR SALARY >= 15000);
 
-- 소속 부서가 50번이거나 80번인 사원의 이름, 소속 부서를 조회하기
SELECT FIRST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (50, 80);
 
-- 소속 부서가 50번, 80번 외의 부서에서 근무하는 사원의 이름, 소속 부서를 조회하기
SELECT FIRST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID NOT IN (50, 80);
 
-- 조회된 데이터의 정렬
-- ORDER BY 절을 사용하면 조회된 데이터를 정렬할 수 있다.
-- ORDER BY 절은 SELECT문의 가장 끝에 위치한다.
-- SELECT
-- FROM 
-- [WHERE]
-- [ORDER BY 기준 컬럼명 [ASC <--기본값 | DESC]];
 
-- 사원 테이블에서 아이디, 이름, 급여를 조회하고, 급여를 기준으로 내림차순 정렬하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY 
FROM EMPLOYEES
ORDER BY SALARY DESC;
 
-- 80번 부서에 소속된 사원들의 이름, 입사일을 조회하고, 입사일을 기준으로 오름차순 정렬하기
SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80
ORDER BY HIRE_DATE ASC;
 
-- 오름차순: 날짜는 옛날 날짜부터, 문자는 알파벳, 가나다 순으로 표시, NULL값은 맨 마지막에 표시
 
-- 80번 부서에 소속된 사원 중에서 이름에 'e'를 포함하고 있고, 급여를 5000달러 미만으로 받는 사원들의
-- 아이디, 이름, 급여를 조회하고, 급여 순으로 내림차순 정렬하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80 AND (FIRST_NAME LIKE '%e%' OR FIRST_NAME LIKE '%E%') AND SALARY < 10000
ORDER BY SALARY DESC;
 
-- 커미션을 받는 사원의 아이디, 이름, 급여, 커미션, 보너스(급여*커미션)을 조회하고
-- 보너스 순으로 오름차순 정렬하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, COMMISSION_PCT, SALARY*COMMISSION_PCT AS BONUS
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL
ORDER BY 5 ASC;
 
-- 내부 연산 순서: FROM WHERE SELECT ORDER BY 순으로 하므로, WHERE은 별칭 불가, ORDER BY는 가능.
-- 두 가지 이상의 정렬 기준 적용 가능
 
-- 80번 부서 사원의 급여, 이름을 조회하기
-- 급여와 이름 순으로 오름차순 정렬하기(급여가 동일할 때는 이름 순으로 정렬하기)
SELECT SALARY, FIRST_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80
ORDER BY SALARY, FIRST_NAME;
 
-- SELECT
-- FROM
-- WHERE
-- GROUP BY ~
-- HAVING ~
-- ORDER BY ~ (순서 꼭 지켜야!)
 
-- 오라클의 내장함수: 값을 입력받아서 적절한 작업을 수행하고 결과값을 반환하는 것.
--                 ㄴ구분: 단일행 함수: 단일 행에 대해서만 연산을 수행하고, 행 당 하나의 결과를 반환한다. 단일행 함수는 여러 번 중첩해서 사용할 수 있다. SELECT, WHERE, ORDER BY 절에서 사용할 수 있다.
--                                      ㄴ종류: 문자/숫자/날짜/변환 함수 - 날짜 <->문자, 숫자<->문자로 변환/기타 함수.
--                           다중행 함수(그룹 함수): 여러 행(행 그룹)을 조작해서 행 그룹 당 하나의 결과를 반환한다. 한 번만 중첩 가능. 통계치(합계, 평균, 분산, 표준편차, 최댓값, 최솟값, 개수)를 계산한다.
 
-- 문자 함수
-- 대소문자 변환 함수
-- UPPER(컬럼), LOWER(컬럼)
SELECT FIRST_NAME, UPPER(FIRST_NAME), LOWER(LAST_NAME)
FROM EMPLOYEES;
 
-- 문자열 추출
-- SUBSTR(컬럼, m [, n]): m번 째부터 n번 째까지 문자를 반환한다.
SELECT FIRST_NAME, SUBSTR(FIRST_NAME, 2)
FROM EMPLOYEES;
 
-- 지정된 문자열의 등장 위치 찾기
-- INSTR(컬럼, '문자'): 지정된 문자의 위치를 반환
SELECT INSTR('801010-1121211', '-')
FROM DUAL; -- 오라클의 빌트인 테이블, 1행 1열짜리 테이블. 특별한 테이블을 대상으로 수행하는 조회 작업이 아닐 때 사용된다.
 
-- 문자 채우기
-- LPAD(컬럼, 길이, '문자'), RPAD(컬럼, 길이, '문자')
-- 컬럼의 값이 지정된 길이보다 짧으면 부족한 길이만큼 문자를 채운다.
SELECT LPAD('ABC', 10, '*'), RPAD('ABC', 10, '*')
FROM DUAL;
 
-- 문자의 길이
-- LENGTH(컬럼)
SELECT FIRST_NAME, LENGTH(FIRST_NAME)
FROM EMPLOYEES;
 
-- 이름 4글자 이하인 사원의 이름과 글자수를 조회하기
SELECT FIRST_NAME, LENGTH(FIRST_NAME)
FROM EMPLOYEES
WHERE LENGTH(FIRST_NAME) <= 4;
 
-- 이름에 'C'나 'c'가 들어 있는 사원의 이름을 조회하기
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE LOWER(FIRST_NAME) LIKE '%c%';
 
-- 불필요한 공백 없애기
-- TRIM(컬럼): 불필요한 좌우 공백을 제거한다.
SELECT '            C B C              ', TRIM('             ABC        ') AS X
FROM DUAL;
 
-- 문자 바꾸기
-- REPLACE(문자, '찾을 문자', '변경할 문자')
SELECT REPLACE('자바 입문자를 위한 자바의 정석', '자바', '파이썬')
FROM DUAL;
 
-- 숫자 함수
-- 반올림한다
-- ROUND(숫자, 소수 자릿수) : 지정된 자리로 값을 반올림한다.
-- 버린다.
-- TRUNC(숫자, 소수 자릿수): 지정된 자리까지 남기고 값을 버린다.
-- 나머지를 반환한다.
-- MOD(숫자1, 숫자2): 숫자1을 숫자2로 나눈 나머지를 반환한다.
 
SELECT ROUND(3,2), ROUND(3,6)
FROM DUAL;
 
SELECT ROUND(3.1523, 1), ROUND(3.1523, 2)
FROM DUAL;
 
SELECT ROUND(10/3, 2) FROM DUAL;
 
SELECT ROUND(1234, 0), ROUND(1234, -1), ROUND(1234, -2), ROUND(1234,-3) FROM DUAL;
 
SELECT TRUNC(123.58, 1), ROUND(123.58, 1) FROM DUAL;
 
SELECT FIRST_NAME, SALARY, TRUNC(SALARY, -3)
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80;
 
-- 급여 금액별 사원 수 계산하기
SELECT TRUNC(SALARY, -3), COUNT(*)
FROM EMPLOYEES
GROUP BY TRUNC(SALARY, -3)
ORDER BY 1;
 
-- 날짜 관련 함수
-- SYSDATE: 현재 날짜와 시간 정보를 반환한다. 반환값은 DATE타입의 값이다(중요!)
SELECT SYSDATE
FROM DUAL;
 
-- MONTHS_BETWEEN(날짜, 날짜): 두 날짜 사이의 개월 수 반환
-- ADD_MONTHS(날짜, 개월 수): 날짜에 개월 수를 더한 날짜를 반환
 
-- 사원 아이디, 이름, 입사일, 근무 개월 수 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)) AS MONTHS
FROM EMPLOYEES;
 
-- 200개월 이상 근무한 사원의 사원 아이디, 이름, 입사일, 근무 개월 수 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)) AS MONTHS
FROM EMPLOYEES
WHERE TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)) >= 200;
 
SELECT ADD_MONTHS(SYSDATE, 6)
FROM DUAL;
 
-- 날짜 연산
-- 날짜 + 일수: 지정된 날짜에서 더해진 숫자만큼 이후의 날짜를 반환한다.
-- 날짜 - 숫자: 지정된 날짜에서 빼진 숫자만큼 이전의 날짜를 반환한다.
-- 날짜 - 날짜: 두 날짜 사이의 일수를 반환한다.
-- 날짜 + 숫자/24: 지정된 날짜에 시간이 더해진 날짜를 반환한다.
-- !날짜 + 날짜는 없음
 
-- 최근 3일 전에 입사한 사원 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE > (SYSDATE -3);
 
-- 3일 후, 3일 전
SELECT SYSDATE + 3, SYSDATE -3
FROM DUAL;
 
-- 사원들의 이름, 입사일, 근무일수 구하기
SELECT FIRST_NAME, HIRE_DATE, TRUNC(SYSDATE - HIRE_DATE) DAYS
FROM EMPLOYEES;
 
SELECT SYSDATE, SYSDATE + 2/24
FROM DUAL;
 
-- 변환함수
SELECT TO_CHAR(SYSDATE, 'YYYY') 년, TO_CHAR(SYSDATE, 'MM') 월, TO_CHAR(SYSDATE, 'DD') 일,
          TO_CHAR(SYSDATE, 'HH24') 시, TO_CHAR(SYSDATE, 'MI') 분, TO_CHAR(SYSDATE, 'SS') 초,
          TO_CHAR(SYSDATE, 'AM') 오전오후, TO_CHAR(SYSDATE, 'DAY') 요일
FROM DUAL;
 
-- 입사연도 조회하기
SELECT FIRST_NAME, HIRE_DATE, TO_CHAR(HIRE_DATE, 'YYYY') 입사연도
FROM EMPLOYEES;
 
-- 입사일이 오늘과 동일한 날짜에 입사한 사람의 이름, 입사일 조회하기
SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE (TO_CHAR(HIRE_DATE, 'MM-DD') = TO_CHAR(SYSDATE, 'MM-DD'));
 
-- 2008년에 입사한 사원의 아이디, 이름, 입사일 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYY') = '2008';
 
-- 80번 부서에 소속된 사원들의 아이디, 이름, 입사일, 근무 개월 수, 근무 연수를 조회하기(소숫점 한 자리까지 표시하고 버린다).
SELECT EMPLOYEE_ID
        , FIRST_NAME
        , HIRE_DATE
        , TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)) AS 근무개월수
        , TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12) AS 근무연수
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80;
 
-- 급여가 12000달러를 초과하는 사원의 아이디, 이름, 급여를 조회하고, 이름순으로 오름차순 정렬하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > 12000
ORDER BY FIRST_NAME ASC;
 
-- 100번 사원이 관리자로 지정된 사원 중에서 급여를 15000달러 이상 지급받는 사원의 아이디, 이름, 
-- 소속 부서 아이디, 급여를 조회하고, 급여순으로 내림차순 정렬하기
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE MANAGER_ID = 100 AND SALARY >= 15000
ORDER BY SALARY DESC;
 
-- 20번 부서와 50번 부서에 소속된 사원 중에서 급여를 5000달러 이상 지급받는 사원의 아이디, 이름,
-- 소속 부서 아이디, 급여를 조회하고, 이름순으로 오름차순 정렬하기
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (20, 50) AND SALARY >= 5000
ORDER BY FIRST_NAME ASC;
 
-- 급여가 5000 ~ 12000 사이이고, 부서번호가 20번 또는 50인 사원의 이름과 급여를 조회하기
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > 5000 AND SALARY < 12000 AND DEPARTMENT_ID IN (20, 50); -- BETWEEN은 포함됨(이상 이하 개념)
 
-- 2008년 상반기에 입사한 사원의 아이디, 이름, 입사일을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYYMM') BETWEEN 200801 AND 200806;
 
-- 커미션을 받는 사원들의 사원 아이디, 이름, 급여, 커미션, 보너스를 조회하기
-- 보너스는 소숫점 한짜리까지 반올림해서 표현하고, 보너스를 기준으로 내림차순 정렬하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, COMMISSION_PCT AS 커미션, ROUND(COMMISSION_PCT*SALARY, 1) AS 보너스
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL
ORDER BY 보너스 DESC;
 
-- 80번부서에 근무하는 사원의 아이디, 이름, 급여, 15%인상된 급여를 조회하기
-- 인상된 급여는 소숫점 이하는 버린다.
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, TRUNC(SALARY*1.15, 0) AS "인상 급여"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80;
 
-- 1월달, 2월달, 3월달에 입사한 사원의 아이디, 이름, 입사일을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'MM') IN (1, 2, 3);
 
-- 이름이 'A', 'M', 'J'로 시작하는 사원의 이름을 알파벳순으로 정렬해서 조회하기(문자함수 사용)
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE UPPER(FIRST_NAME) LIKE 'A%' 
OR UPPER(FIRST_NAME) LIKE 'M%'
OR UPPER(FIRST_NAME) LIKE 'J%'
ORDER BY FIRST_NAME ASC;
 
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE SUBSTR(FIRST_NAME, 1, 1) IN ('A', 'M', 'J');
 
-- 사원의 이름과 급여를 '*'로 나타내는 SQL을 작성하시오, '*'하나는 1000달러를 나타낸다.
-- 결과 
-- King 24000 ********************
-- Abel 6000  ******
-- 응수 45000 **********************************************************
SELECT FIRST_NAME, SALARY, RPAD('*', SALARY/1000 ,'*') AS 별
FROM EMPLOYEES;
 
SELECT FIRST_NAME, SALARY, RPAD(SALARY/1000+LENGTH(SALARY) , LENGTH(SALARY/1000+LENGTH(SALARY)) ,'*')
FROM EMPLOYEES;
 
-- 이름에 'A'나'a'와 'C'나'c'를 모두 포함하고 있는 사원의 이름을 알파벳순으로 정렬해서 조회하기 
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE INSTR(LOWER(FIRST_NAME), 'a') > 0
AND INSTR(LOWER(FIRST_NAME), 'C') > 0;
 
--WHERE UPPER(FIRST_NAME) LIKE '%A%' 
-- OR UPPER(FIRST_NAME) LIKE '%C%'
-- ORDER BY FIRST_NAME ASC;
 
-- !문자도 크기 비교 가능