Database #3 SQL : 논리연산자 & 내장함수

190402

Oracle의 논리연산자와 내장함수에 대해서 배워보도록 하겠습니다.

---

* SQL에서 --은 주석처리입니다.

* --가 없는 행은 소스라고 보시면 되겠습니다.

< 논리연산자 >

-- <오라클의 논리연산자>
 
-- and : 제시된 모든 조건식이 true면 true를 반환한다.
-- or : 제시된 모든 조건식 중 하나라도 true면 true를 반환한다.
-- not 제시된 조건식이 true이면 false로 false이면 true를 반환한다.
-- where절에서 논리연산자를 사용하면 하나 이상의 조건에 부합되는 행을 선택할 수 있다.
 
-- 80부서에 소속된 사원중에서 급여를 5000달러 이상 받는 사원의 이름과 급여, 직종, 부서아이디를 조회하기
select first_name, salary, job_id, department_id
from EMPLOYEES
where salary >= 5000
AND DEPARTMENT_ID = 80;
 
-- 90번 부서에 소속된 사원중에서 관리자 아이디가 100인 사원의 이름, 급여, 직종을 조회하기
SELECT first_name || ' ' || last_name full_name, salary, job_id
from EMPLOYEES
where DEPARTMENT_ID = 90 
and MANAGER_ID = 100;
 
-- 커미션을 받는 사원중에서 소속부서가 지정되지 않은 사원의 이름, 급여, 커미션을 조회하기
SELECT first_name || ' ' || last_name full_name, salary, commission_pct
from EMPLOYEES
where COMMISSION_PCT is not null 
and DEPARTMENT_ID is not null;
 
-- 커미션이 0.3이상이고, 직종이 SA_MAN이며, 이름이 'A'로 시작하는 사원의 이름, 커미션, 직종을 조회하기
SELECT first_name || ' ' || last_name full_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 || ' ' || last_name full_name, salary
from EMPLOYEES
where DEPARTMENT_ID = 80 
and salary > 5000
and salary < 10000;
 
-- 소속부서가 60이거나 90인 사원의 이름, 소속부서를 조회하기
select first_name || ' ' || last_name full_name, department_id
from EMPLOYEES
where DEPARTMENT_ID in (60, 90);
-- =where department_id = 60              departmetn_id = 60 or 90; 으로 사용될 수 없다.
--  or department_id = 90;
 
-- 급여를 3000달러 이하로 받는 사원과 급여를 15000달러 이상으로 받는 사원의 이름, 급여를 조회하기
select first_name || ' ' || last_name full_name, salary
from EMPLOYEES
where salary <= 3000
OR salary >= 15000;
 
-- 소속부서가 10번 부서에 소속된 사원 중에서
-- 급여를 3000달러 이하로 받거나 급여를 15000달러 이상으로 받는 사원의 이름, 급여, 부서아이디를 조회하기
select first_name || ' ' || last_name full_name, salary, department_id
from EMPLOYEES
where DEPARTMENT_ID = 50
and (SALARY <= 3000
or SALARY >= 15000);
 
-- 소속부서가 50번이거나 80번인 사원의 이름 소속부서를 조회하기
SELECT first_name || ' ' || last_name full_name, department_id
from EMPLOYEES
where DEPARTMENT_ID in (50, 80);
 
-- 소속부서가 50번, 80번 외의 부서에서 근무하는 사원의 이름, 소속부서를 조회하기
select first_name || ' ' || last_name full_name, department_id
from EMPLOYEES
where DEPARTMENT_ID not in (50, 80);
 
-- 조회된 데이터의 정렬
-- order by 절을 사용하면 조회된 데이터를 정렬할 수 있다.
-- order by 절은 select문의 가장 끝에 위치한다.
-- select문에서 where절과 order by절은 생략가능하다.
-- order by에서는 select 절의 별칭을 사용할 수 있다.
-- 두 가지 이상의 정렬기준을 적용해서 정렬할 수 있다.
 
-- <select문의 형식>
-- select *
-- from 테이블명
-- where 조건식
-- group by
-- having 
-- order by 컬럼명 asc/desc   <-- 지정된 컬럼의 값들을 기준으로 오름차순/내림차순 정렬한다.
 
-- 사원테이블에서 아이디, 이름, 급여를 조회하고, 급여를 기준으로 내림차순 정렬하기
select first_name || ' ' || last_name full_name, salary
from EMPLOYEES
order by salary desc;
 
-- 데이터 정렬 기준(오름차순)
-- 숫자는 작은 값부터 표시
-- 날짜는 옛날 날짜부터 표시
-- 문자는 알파벳, 가나순으로 표시
-- null값은 마지막에 표시
 
-- 80번 부서에 소속된 사원들의 이름, 입사일을 조회하고, 입사일을 기준으로 오름차순 정렬하기
select first_name || ' ' || last_name full_name, hire_date
from EMPLOYEES
where DEPARTMENT_ID = 80
order by hire_date asc;
 
-- 80번 부서에 소속된 사원중에서 이름에 'e'를 포함하고 있고, 급여를 10000달러 이상으로 받는 사원들의
-- 아이디, 이름, 급여를 조회하고, 급여순으로 내림차순 정렬하기
select employee_id, first_name || ' ' || last_name full_name, salary
from EMPLOYEES
where DEPARTMENT_ID = 80
and first_name like '%e%'
and salary >= 10000
order by salary desc;
 
-- 커미션을 받는 사원중에서 사원의 아이디, 이름, 급여, 커미션, 보너스(급여*커미션)을 조회하고
-- 보너스순으로 오름차순 정렬하기
SELECT EMPLOYEE_ID, first_name || ' ' || last_name full_name, salary, COMMISSION_PCT, (SALARY*COMMISSION_PCT) bonus
from EMPLOYEES
where COMMISSION_PCT is not NULL        -- where에서는 별칭을 사용할 수 없지만,
order by bonus asc;                     -- order by는 마지막에 실행되기 때문에, 별칭을 사용할 수 있다.
 
-- 80번 부서 사원의 급여, 이름을 조회하기
-- 급여와 이름순으로 오름차순 정렬하기(급여가 동일할 때는 이름순으로 정렬)
select salary, first_name || ' ' || last_name full_name
from EMPLOYEES
where DEPARTMENT_ID = 80
order by salary asc, FIRST_NAME asc;
 

< 내장함수>

-- <오라클의 내장함수>
    -- 값을 입력받아서 적절한 작업을 수행하고, 결과값을 반환하는 것
    -- 구분
            -- 단일행 함수
                    -- 단일 행에 대해서만 연산을 수행하고, 행 당 하나의 결과를 반환한다.
                    -- 단일행 함수는 여러 번 중첩해서 사용할 수 있다.
                    -- select절, where절, order by절에서 사용할 수 있다.
                    
                    -- 종류
                            -- 문자함수 - 문자를 입력받아서 처리하고 결과를 반환한다.
                            -- 숫자함수 - 숫자를 입력받아서 처리하고 결과를 반환한다.
                            -- 날짜함수 - 날짜와 관련된 처리를 수행하고 결과를 반환한다.
                            -- 변환함수 - 날짜 <-> 문자, 숫자 <-> 문자로 변환한다.
                            -- 기타함수 - 기타
            -- 다중행 함수 ( 그룹함수 )
                    -- 여러 행(행 그룹)을 조작해서 행 그룹 당 하나의 결과를 반환한다.
                    -- 합계, 평균, 분산, 표준편차, 최대값, 최솟값, 갯수 등을 계산한다.
                    -- 통계작업에 활용된다.
                    
-- 문자함수
-- 대소문자 변환함수
-- upper(컬럼), lower(컬럼)
select first_name, upper(first_name), lower(first_name)
from EMPLOYEES;
 
--문자열 추출
--substr(컬럼, m [,n]) : m번째부터 n번째까지 문자를 반환한다.
select first_name, SUBSTR(first_name, 2)
from EMPLOYEES;
 
--지정된 문자의 등장위치 찾기
-- instr(컬럼, '문자') : 지정된 문자의 위치를 반환한다.
-- dual : 오라클의 빌트인 테이블, 1행1열짜리 테이블
-- 특별한 테이블을 대상으로 수행하는 조회작업이 아닐때 사용된다.
select instr('801010-1121211', '-')
from dual;
 
select *
from dual;
 
-- 문자채우기
-- 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(컬럼) : 불필요한 좌우공백을 제거한다. (tab은 공백문자 판별로 지워지지 않는다.)
select '      abc      ', trim('            abc    ') sss
from dual;
 
-- 문자바꾸기
-- replace(문자, '찾을문자', '변경할문자')
select replace('자바 입문자를 위한 자바의 정석', '자바', '파이썬') tt
from dual;
 
 
-- 숫자함수
-- 반올림한다.
-- round(숫자, [자릿수]) : 지정된 자리로 값을 반올림한다.
-- 버린다.
-- trunk(숫자, 자릿수) : 지정된 자리까지 남기고 나머지를 버린다.
-- 나머지를 반환한다.
-- 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(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;
 
--sysdate는 무결성에 유용하게 활용된다.
create table contact (
    name varchar2(100),
    tel varchar2(100),
    create_date date
);
 
insert into contact values('홍길동', '010-1111-1111', sysdate);
 
select *
from contact;
 
-- MONTHS_BETWEEN(날짜, 날짜) : 두 날짜사이의 개월 수 반환
-- ADD_MONTHS(날짜, 개월수) : 날짜에 개월수를 더한 날짜를 반환
 
-- 사원아이디, 이름, 입사일, 근무개월수 조회하기
SELECT employee_id, first_name, hire_date, TRUNC(MONTHS_BETWEEN(sysdate, hire_date)) months
from EMPLOYEES;
 
-- 200개월 이상 근무한 사원아이디, 이름, 입사일, 근무개월수 조회하기
SELECT employee_id, first_name, hire_date, TRUNC(MONTHS_BETWEEN(sysdate, hire_date)) months
from EMPLOYEES
where TRUNC(MONTHS_BETWEEN(sysdate, hire_date)) >= 200;
 
select ADD_MONTHS(sysdate, 5)
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;
 
-- 2시간 늘리기
select sysdate, sysdate + 2/24
from dual;
 
 
-- 변환 함수
-- to_char(날짜, '문자') : 날짜를 문자열로 반환한다.
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;

< to_char // to_date // to_number 의 순환구조 >

---

금일 배운 것을 토대한 과제 파일입니다.

190402_sql_quiz.txt

금일 설명 쿼리문과 과제 답안입니다.

190402.sql

190402_2.sql

190402_2_과제.sql