[Database] SQL Assignment 3

 

 

 

 

 

 

 

 

1. 현재 날짜를 표시하는 질의를 작성하고 열 레이블을 Data로 지정하십시오.

SELECT sysdate as "Date" 
FROM DUAL

-- from 절을 꼭 써야 하니까, dummy table로 DUAL 제공 

 

 

 

2. 각 사원에 대해 사원 번호, 이름 , 급여 및 15% 인상된 급여를 정수로 표시하십시오. 인상된 급여 열의 레이블을 New Salary 로 지정하십시오.

SELECT employee_id, last_name, salary, (salary * 1.15) AS "New Salary"
FROM employees

 

 

 

3. 이름이 J, A또는 M으로 시작하는 모든 사원의 이름(첫 글자는 대문자로, 나머지 글자는 소문자로 표시) 및 이름 길이를 표시하는 질의를 작성하고 각 열에 적합한 레이블을 지정하십시오. 결과를 사원의 이름에 따라 정렬하십시오.

SELECT INITCAP(last_name), LENGTH(last_name) AS "name length"
FROM employees
WHERE last_name LIKE 'J%' OR last_name LIKE 'A%' OR last_name LIKE 'M%'
ORDER BY last_name;

 

// SUBSTR 활용한 방법

SELECT INITCAP(last_name), LENGTH(last_name) AS "name length"
FROM employees
WHERE SUBSTR(last_name,1,1) IN ('J', 'A', 'M')
ORDER BY last_name

 

 

4. 각 사원의 이름을 표시하고 근무 달 수(입사일로부터 현재까지의 달 수)를 계산하여 열 레이블을 MONTHS_WORKED로 지정하십시오.  결과는 정수로 반올림하여 표시하고 근무 달 수를 기준으로 정렬하십시오.SELECT last_name, ROUND(MONTHS_BETWEEN(sysdate, hire_date)) AS "MONTHS_WORKED"
FROM employees
ORDER BY ROUND(MONTHS_BETWEEN(sysdate, hire_date))
-- 다른 DBMS 사용 시, 별칭 주는게 안 먹는 경우가 있다. 
-- 그냥 ROUND(MONTHS_BETWEEN(sysdate, hire_date))로 정렬해도 okay

 

 

 

5. 각 사원에 대해 다음 항목을 생성하는 질의를 작성하십시오. <employee last name> earn <salary> monthly but wants <3 times salary> 열 레이블을 Dream Salaries로 지정하십시오.

SELECT last_name, last_name || ' earn ' || salary || ' monthly but wants ' || salary*3 AS "Dream Salaries" 
FROM employees 

 

 

 

6.모든 사원의 이름과 급여를 표시하는 질의를 작성하십시오. 급여는 15자 길이로 왼쪽에 $기호가 채워진 형식으로 표기하고 열 레이블을 SALARY로 지정하십시오.

SELECT last_name, LPAD(salary, 15, '$') "SALARY"
FROM employees

 

 

 

7.사원의 이름, 입사일 및 급여 검토일을 표시하십시오. 급여 검토일은 여섯 달이 경과한 후 첫 번째 월요일입니다. 열 레이블을 REVIEW로 지정하고 날짜는“Monday, the Thirty-First of July, 2000”과 같은 형식으로 표시되도록 지정하십시오.

SELECT last_name, hire_date, NEXT_DAY(ADD_MONTHS(hire_date, 6), 'Monday') "REVIEW"
FROM employees

 

 

 

8. 이름, 입사일 및 업무 시작 요일을 표시하고 열 레이블을 DAY로 지정하십시오. Monday를 시작으로 해서 요일을 기준으로 결과를 정렬하십시오.

SELECT last_name, hire_date, TO_CHAR(hire_date, 'DAY') "DAY"
FROM employees
ORDER BY TO_CHAR(hire_date - 1, 'D')

 

-- 'd'='D'는 요일을 숫자로 변환
-- hire_date = 수요일 -> 수요일에 해당하는 4 

-- sunday가 1, 월요일이 2, 따라서 전날을 기준으로 (-1) 정렬

-- 월요일이 일요일이 되고, 일요일은 토요일 즉 제일 큰 값이 됨

 

 

 

9.사원의 이름과 커미션을 표시하는 질의를 작성하십시오. 커미션을 받지 않는 사원일 경우“No Commission”을 표시하십시오. 열 레이블은 COMM으로 지정하십시오.

SELECT last_name, NVL(TO_CHAR(commission_pct), 'No Commission') "COMM"
FROM employees

 

-- NVL, Null-value - 첫번째 params가 null이면, 두번째 params 출력 (null이 아니면 원래 값 출력) 

-- 왜 TO_CHAR? - 하나의 attribute는 동일한 type 만 출력이 가능하니까, type을 맞춰주기 위해서 

 

 

 

10.사원의 이름을 표시하고 급여 총액을 별표(*)로 나타내는 질의를 작성하십시오. 각 별표는 1,000달러를 나타냅니다. 급여를 기준으로 데이터를 내림차순으로 정렬하고 열 레이블을 EMPLOYEES_AND_THEIR_SALARIES로 지정하십시오.

SELECT last_name, RPAD('*', TRUNC(salary/1000), '*') AS "EMPLOYEES_AND_THEIR_SALARIES"
FROM employees
ORDER BY salary DESC

 

-- RPAD - 출력할 공간을 잡아둔 다음, 출력 공간이 잡힌 나머지 공간을 채워주기 위한 함수 -> 오른쪽을 별표로 채우자

-- 총 칸의 개수가 TRUNC(salary/1000) - 앞이든 뒤든 싹 다 '*' 로 채우는 것이 목적 

-- 일단 '*'를 찍고, 나머지 뒷 칸들도 다 TRUNC(salary/1000) size 만큼 '*' 을 채우자.

 

 

 

11. DECODE 함수를 사용하여 다음 데이터에 따라 JOB_ID 열의 값을 기준으로 모든 사원의 등급을 표시하는 질의를 작성하십시오.

 

업무             등급

AD_PRES    A

ST_MAN      B

IT_PROG     C

SA_REP       D

ST_CLERK   E

기타               0

 

SELECT last_name, job_id,
DECODE(job_id, 'AD_PRES', 'A',
     'ST_MAN', 'B',
     'IT_PROG', 'C',
     'SA_REP', 'D',
     'ST_CLERK', 'E',
     '0') AS "GRADE"

FROM employees

 

 

 

12. 11번 문제의 명령문을 CASE 구문을 사용하여 재작성하십시오.

SELECT last_name, job_id,
    CASE job_id 
    WHEN 'AD_PRES' THEN 'A'
    WHEN 'ST_MAN' THEN 'B'
    WHEN 'IT_PROG' THEN 'C'
    WHEN 'SA_REP' THEN 'D'
    WHEN 'ST_CLERK' THEN 'E'
    ELSE '0'
END AS "GRADE"
FROM employees

 

 

 

 

 

* 추가 예제 

SELECT last_name, salary,
    DECODE(TRUNC(salary/5000) - 1, 
    -1, '어휴~~~', 
    0, '어휴~~~', 
    1, '오케이', 
    'Good!') AS "MESSAGE"
FROM employees



SELECT last_name, salary,
    CASE WHEN salary < 5000 THEN '어휴~~~' 
    WHEN salary < 10000 THEN '오케이' 
    ELSE 'Good!'
END AS "MESSAGE"
FROM employees

-- DECODE는 동등 조건만 가능하다 (크다, 작다 표현이 X)
-- CASE문은 대소 비교 표현 가능, 이때 CASE WHEN salary 이렇게 변경