[Lecture] DataBase - Day 4

🖥 DataBase - Day 4

👉🏻 SQL 질의문 작성

1

1. select last_name, lower(last_name) lower적용,
        upper(last_name) upper적용, email,
    initcap(email) initcap적용
    from hr.employees;
2. select job_id, substr(job_id, 1, 2) 적용결과
    from hr.employees;
3. select job_id, replace(job_id, 'ACCOUNT', 'ACCNT') 적용결과
    from hr.employees;
4. select first_name, lpad(first_name, 12, '*') 적용결과
    from hr.employees;
5. select first_name, rpad(first_name, 12, '*') 적용결과
    from hr.employees;
6. select job_id, ltrim(job_id, 'F') 적용결과, rtrim(job_id, 'T') 적용결과
    from hr.employees;
7. select hire_date,
    round(hire_date, 'MM') 달_적용결과,
    round(hire_date, 'YY') 연_적용결과,
    trunc(hire_date, 'MM') 달_적용결과,
    trunc(hire_date, 'YY') 연_적용결과
    from hr.employees
    where employee_id between 100 and 106;
8. select to_date('2023/10/01', 'YYYY/MM/DD')-to_date('2023/09/25', 'YYYY/MM/DD')
    as "2023/10/01 - 2023/09/25",
        to_date('2023/09/25', 'YYYY/MM/DD')+20 "20일 후"
        from dual;
9. select sysdate, hire_date,
    months_between(sysdate, hire_date) 적용결과
    from hr.employees
    where employee_id=100;
10. select hire_date,
    add_months(hire_date, 3) plus,
    add_months(hire_date, -3) sub
    from hr.employees
    where employee_id between 100 and 106;
11. select hire_date,
    next_day(hire_date, 'Fri') 적용결과
    from hr.employees;
12. select hire_date,
    last_day(hire_date) 적용결과
    from hr.employees;
13. select to_number('123') 숫자변환결과 from dual;
14. select employee_id, salary, commission_pct,
    salary*commission_pct
    from hr.employees
    where employee_id between 141 and 150
    order by employee_id;
15. select employee_id, salary, commission_pct,
    salary*nvl(commission_pct, 1)
    from hr.employees
    where employee_id between 141 and 150
    order by employee_id;
16. select first_name, last_name, department_id,
    salary 원래급여,
    case department_id
        when 60 then salary*1.1
        else salary
    end as 조정된급여,
    case department_id
        when 60 then '10%인상'
        else '미인상'
    end as 인상여부
    from hr.employees;
17. select employee_id, first_name, last_name, salary,
    case
        when salary>=9000 then '상위급여'
        when salary between 6000 and 8999 then '중위급여'
        else '하위급여'
    end as 급여등급
    from hr.employees
    where job_id='IT_PROG';

2

1. select 제목, substr(제목, 1, 2) 제목첫두글자 from 영화;
2. select 감독, replace(감독, '훈', '휜') 이름바꾼감독 from 영화;
3. select 제목, lpad(제목, 10, '*') 제목 from 영화;
4. select 제목, rpad(제목, 10, '*') 제목 from 영화;
5. select 제목, 감독, ltrim(감독, '이') ltrim결과, rtrim(감독, '훈') rtrim결과 from 영화;
6. select 제목, 평점, round(평점, 1) 반올림, trunc(평점, 1) 절삭 from 영화;
7. select 제목, to_date(개봉일, 'YYYY.MM.DD') 개봉일, 
        to_date(개봉일, 'YYYY.MM.DD')-1 개봉전날 from 영화
        where 개봉일 like '2023%' order by 개봉일 desc;
8. select 제목, to_date(개봉일, 'YYYY.MM.DD') 개봉일, 
       months_between(sysdate, to_date(개봉일, 'YYYY.MM.DD')) 개월수
    from 영화;
9. select 제목, to_date(개봉일, 'YYYY.MM.DD') 개봉일, 
       sysdate-to_date(개봉일, 'YYYY.MM.DD') 일수
    from 영화;
10. select 제목, to_date(개봉일, 'YYYY.MM.DD') 개봉일,
    add_months(to_date(개봉일, 'YYYY.MM.DD'), 2) 일수
    from 영화;
11. select 제목, to_date(개봉일, 'YYYY.MM.DD') 개봉일,
    next_day(to_date(개봉일, 'YYYY.MM.DD'), 'fri') "가장 가까운 금요일"
    from 영화;
12. select 제목, to_date(개봉일, 'YYYY.MM.DD') 개봉일,
    last_day(to_date(개봉일, 'YYYY.MM.DD')) 개봉일마지막날짜
    from 영화;
13. select 제목, to_date(개봉일, 'YYYY.MM.DD') 개봉일,
    round(to_date(개봉일, 'YYYY.MM.DD'), 'MM') 월기준반올림,
    trunc(to_date(개봉일, 'YYYY.MM.DD'), 'YY') 연기준절삭
    from 영화;
14. select 제목, to_date(개봉일, 'YYYY.MM.DD') 개봉일,
        to_char(to_date(개봉일, 'YYYY.MM.DD'), 'YY') YY,
        to_char(to_date(개봉일, 'YYYY.MM.DD'), 'MM') MM,
        to_char(to_date(개봉일, 'YYYY.MM.DD'), 'MON') MON
    from 영화;
15. select 제목, to_date(개봉일, 'YYYY.MM.DD') 개봉일,
        to_char(to_date(개봉일, 'YYYY.MM.DD'), 'YYYY-MM-DD') as "YYYY-MM-DD"
    from 영화;
16. select 제목, 평점 from 영화
    where 평점 is null;
17. select 제목, 평점, nvl(평점, 1) 치환값 from 영화
    where 평점 is null;