[문제] Day65-1 - oracle 예제 문제

※ 순차적으로 실습해보기!

 

# 4

Q1. employees 테이블에서 department_id가 110인 것들의 hire_date, hire_date에서 6개월 더한 값을 출력하기 
A.

SELECT hire_date, ADD_MONTHS(hire_date, 6) FROM employees WHERE department_id = 110;

 

// Tip - ADD_MONTHS : 주어진 날짜에 숫자 만큼의 달을 추가하는 함수 

Q2. 현재 날짜와 시간, 그리고 다가오는 월요일을 조회하기 
A.

SELECT SYSDATE, NEXT_DAY(SYSDATE, '월') FROM dual;

 

// Tip - SYSDATE : 주어진 날짜를 기준으로 돌아오는 가장 최근 요일의 날짜를 반환해주는 함수 
윈도우용 오라클과 리눅스용 오라클의 날짜 표기 방식이 다르다. 
리눅스는 아래와 같이 표기한다.

SELECT SYSDATE, NEXT_DAY(SYSDATE, ‘MON’) NEXT_DAY FROM dual;

Q3. 특정 날짜 14년12월1일을 기준으로 돌아오는 월요일을 조회하기
A.

SELECT NEXT_DAY('14/12/01', '월') FROM dual;

 
Q4. 현재날짜와 이번 달의 마지막 날을 출력하기
A.

SELECT SYSDATE, LAST_DAY(SYSDATE) FROM dual;

 
Q5. 현재 날짜와 시간을 출력,

     정오를 기준으로 정오가 넘었으면 다음 날짜를 출력하고 아니면 오늘 날짜를 출력, 무조건 당일 출력하기 
A.

SELECT SYSDATE, ROUND(SYSDATE), TRUNC(SYSDATE) FROM dual;

하루의 반에 해당되는 시간은 낮(정오) 12:00:00 이다. 

날짜의 ROUND는 주어진 날짜가 이 시간을 넘어설 경우에 다음 날짜로 출력하고 

이 시간이 안될 경우에는 당일로 출력.

반면 날짜의 TRUNC 함수는 무조건 당일로 출력한다.

Q6. employees 테이블에서 department_id가 110인 것들의 first_name과 salary*12의 값에 $ 기호를 붙이고

     전체 숫자 폭은 소수점 포함 8자리이고 천의 자리에 ‘,’를 찍고

     소수점 이하 2자리로 출력하는데 컬럼 이름은 “SAL”이라고 하자. 
A.

SELECT first_name, TO_CHAR(salary*12, '$999,999.99') "SAL" FROM employees WHERE department_id = 110;

Q7. 문자 3을 숫자로 출력하기 
A.

SELECT TO_NUMBER('3') FROM dual;

Q8. 문자 ‘14/12/25’를 날짜로 출력하기 
A.

SELECT TO_DATE('14/12/25') FROM dual;

Q9. 숫자 2와 문자 ‘2’를 더하기 
A.

SELECT 2 + '2' FROM dual; 
SELECT 2 + TO_NUMBER('2') FROM dual;

Q10. employees 테이블에서 commission_pct를 출력하는데 commission_pct 값이 null이면 0으로 대체하여 출력하기 
A.

select nvl(commission_pct, 0) from employees;

// Tip - NVL() 함수 / NVL2() 함수 : NULL값을 치환해주는 함수

- NVL(조사할 컬럼, NULL 일 경우 치환할 값) 
- NVL2(조사 대상 컬럼, NULL 아닐 때 치환할 값, NULL 일 경우 치환할 값) 

ex) NVL(sal, 0) -> sal 컬럼의 값이 null일 경우 null 대신 0으로 치환하세요. 
     NVL(sal, 100) -> sal 컬럼의 값이 null일 경우 null 대신 100으로 치환하세요. 

ex) 치환 값이 문자일 경우 
     NVL(position, ‘사원’) -> position 값이 null일 경우 ‘사원’으로 치환하세요. 
     NVL(hirdate, ‘2014/05/01’) -> hiredate 값이 없을 경우 ‘2014/05/01’ 날짜로 치환하세요. 

 

 

#5

Q1. employees 테이블에서 department_id가 50미만인 것들 중에서 DECODE 문장을 이용하여

     first_name, department_id, department_id가 10이면 ‘Team-1’,

     20이면 ‘Team-2’,

     30이면 ‘Team-3’ 

     위의 3가지가 아닌 나머지는 ‘N/A’를 출력하는데 컬럼 이름은 “TEAM”으로 하기

A.

SELECT first_name, department_id,
decode(department_id, 10, 'Team-1',
           20, 'Team-2',
           30, 'Team-3',
           'N/A') "TEAM"
FROM employees WHERE department_id < 50;

// Tip - DECODE 함수

ex1) A가 B일 경우 ‘1’을 출력하는 경우 (마지막 null은 생략 가능)

DECODE(A, B, ‘1’, null)

 

ex2) A가 B일 경우 ‘1’을 출력하고 아닐 경우 ‘2’를 출력하는 경우

DECODE(A, B, ‘1’, ‘2’)

 

 

ex3) A가 B일 경우 ‘1’을 출력하고 A가 C일 경우 ‘2’를 출력하고 둘 다 아닐 경우 ‘3’을 출력하는 경우

DECODE(A, B, ‘1’, C, ‘2’, ‘3’)

 

ex4) A가 B일 경우 중에서 C가 D를 만족하면 ‘1’을 출력하고 C가 D가 아닐 경우 NULL을 출력하는 경우

     (DECODE 함수 안에 DECODE 함수가 중첩되는 경우)

DECODE(A, B, DECODE(C, D, ‘1’, null)) ?

 

ex5) A가 B일 경우 중에서 C가 D를 만족하면 ‘1’을 출력하고 C가 D가 아닐 경우 ‘2’를 출력하는 경우

DECODE(A, B, DECODE(C, D, ‘1’, ‘2’))

Q2. employees 테이블에서 department_id가 50미만인 것들 중 

     CASE 문장을 이용하여 first_name, department_id, department_id가 10이면 ‘Team-1’,

     20이면 ‘Team-2’,

     30이면 ‘Team-3’ 

     위의 3가지가 아닌 경우 ‘N/A’를 출력하는데 컬럼 이름은 “TEAM”으로 하기

A.

SELECT first_name, department_id,
           CASE department_id
                      WHEN 10 THEN 'Team-1'
                      WHEN 20 THEN 'Team-2'
                      WHEN 30 THEN 'Team-3'
                      ELSE 'N/A'
           END "TEAM"
FROM employees
WHERE department_id < 50;

 

// Tip - CASE문

CASE 조건 WHEN 결과1 THEN 출력1
           [WHEN 결과2 THEN 출력2]
           ELSE 출력3
END “컬럼명”

 

Q3. employees 테이블에서 first_name, department_id와 CASE문을 이용하여 

     department_id가 10이상 50이하면 ‘Team-1’,

     department_id가 60이상 100이하이면 ‘Team-2’,

     department_id가 110이상 150이하면 ‘Team-3’, 

    위의 3가지가 아닌 경우 ‘N/A’라 하고 컬럼 이름은 “TEAM”이라 하기

A.

SELECT first_name, department_id,
           CASE
                      WHEN department_id BETWEEN 10 AND 50 THEN 'Team-1'
                      WHEN department_id BETWEEN 60 AND 100 THEN 'Team-2'
                      WHEN department_id BETWEEN 110 AND 150 THEN 'Team-3'
                      ELSE 'N/A'
           END "TEAM"
FROM employees;

 

Q4. employees 테이블에서 모든 줄의 수와 commission_pct의 값이 있는 줄의 수 출력하기

A.

SELECT COUNT(*), COUNT(commission_pct) FROM employees;

// Tip : 그룹 함수는 *를 쓰면 모든 행을 대상으로 작업을 하고

         특정 컬럼 이름을 지정하면 해당 컬럼에 값이 있는 경우만 계산을 한다.

 

Q5. employees 테이블에서 모든 줄의 수와 모든 salary의 합을 출력하기

A.

SELECT COUNT(*), SUM(salary) FROM employees;

 

Q6. employees 테이블에서 모든 줄의 수와 모든 salary의 합과 모든 salary의 평균 출력하기

A.

SELECT COUNT(*), SUM(salary), AVG(salary) FROM employees;
SELECT COUNT(*), SUM(salary), AVG(NVL(salary, 0)) FROM employees;

 

Q7. employees 테이블에서 salary 최대값, salary 최소값, hire_date 최대값, hire_date 최소값을 출력하기

A.

SELECT MAX(salary), MIN(salary), MAX(hire_date), MIN(hire_date) FROM employees;

 

Q8. employees 테이블에서 department_id, 줄의 수, salary의 합을 department_id로 그룹화하기

A.

SELECT department_id, COUNT(*), SUM(salary) FROM employees GROUP BY department_id;

 

Q9. employees 테이블에서 department_id, job_id, 줄수, salary의 합을 

     department_id, job_id로 그룹화하고 department_id와 job_id로 오름차순 정렬한다.

A.

SELECT department_id, job_id, COUNT(*), SUM(salary) FROM employees GROUP BY department_id, job_id ORDER BY 1, 2;

// Tip : GROUP BY 절을 사용할 때의 주의 사항

         SELECT 절에 사용된 그룹함수 이외의 컬럼이나 표현식은 반드시 GROUP BY절에 사용되어야 한다.

ex) SELECT 절에는 job_id가 있는데 GROUP BY 절에는 job_id가 없어서 에러 

SELECT department_id, job_id, COUNT(*), SUM(salary) FROM employees GROUP BY department_id;

② GROUP BY 절에 사용된 컬럼은 SELECT 절에 사용되지 않아도 된다.

③ GROUP BY 절에는 반드시 컬럼명이 사용되어야 하며 컬럼 Alias는 사용하면 안된다.

 

Q10. employees 테이블에서 department_id, 줄 수, salary의 합을 department_id로 그룹화하여 

       salary의 합이 20000 초과인 것을 출력.

A.

SELECT department_id, COUNT(*), SUM(salary) FROM employees HAVING SUM(salary) > 20000 GROUP BY department_id;

// Tip : 조건을 주고 검색을 할 때 WHERE 절을 사용한다. 

          그러나 모든 조건을 WHERE로 처리하는 것은 아니다.

ex)

SELECT department_id, count(*), SUM(salary) from employees WHERE SUM(salary) > 20000 GROUP BY department_id;

위의 예시는 salary의 총 합계가 20000 이상인 부서의 부서 번호와 인원 수, 급여 합계를 출력하기 위한 쿼리이다.

이 중 중요한 부분은

WHERE SUM(salary) > 20000

인데 총 급여가 20000 이상이라는 의미이긴 하지만 WHERE 절은 그룹 함수가 올 수 없다. 

그래서 아래의 에러 메시지 부분에서 ‘그룹 함수는 허가되지 않습니다.’라는 메시지가 나온다. 

만약 위의 예처럼 그룹 함수를 조건으로 사용하고 싶을 경우에는 WHERE 대신에 HAVING 절을 사용하면 된다. 

 

그룹함수를 비교 조건으로 사용하려면 반드시 HAVING 절을 사용해야 한다. 

HAVING 절의 위치는 GROUP BY절의 이전, 이후 모두 가능하다. 

 

그리고 그룹함수를 사용하는 SQL이라 하더라도 조건이 그룹함수가 아닌 일반 조건인 경우 

WHERE 문장을 쓸 수 있다. 

단, GROUP 함수 부분에만 WHERE를 사용하면 안 된다.

 

# 6

Q1. employees 테이블에서 department_id가 50 초과인 department_id, job_id, 줄 수, salary의 합을 

     department_id와 job_id 합의 소계로 그룹화 하기 

A.

SELECT department_id, job_id, COUNT(*), SUM(salary)  
FROM employees WHERE department_id > 50 GROUP BY ROLLUP(department_id, job_id);

 

//Tip : ROLLUP() 함수 / CUBE() 함수 
- ROLLUP : 주어진 데이터들의 소계를 구해주는 함수

- CUBE : 주어진 데이터들의 전체 총계까지 구해주는 함수

 

Q1과 비교

① 

SELECT department_id,SUM(salary)  
FROM employees WHERE department_id > 50 GROUP BY department_id;

SELECT department_id,SUM(salary)  
FROM employees WHERE department_id > 50 GROUP BY ROLLUP(department_id);

SELECT department_id,SUM(salary)  
FROM employees WHERE department_id > 50 GROUP BY CUBE(department_id);

②

SELECT department_id, job_id, COUNT(*), SUM(salary)  
FROM employees WHERE department_id > 50 GROUP BY department_id, job_id;

SELECT department_id, job_id, COUNT(*), SUM(salary)  
FROM employees WHERE department_id > 50 GROUP BY ROLLUP(department_id, job_id);

SELECT department_id, job_id, COUNT(*), SUM(salary)  
FROM employees WHERE department_id > 50 GROUP BY CUBE(department_id, job_id);

Q2. employees 테이블에서 department_id가 50 초과인 department_id, job_id, 줄 수, salary의 합을

     department_id, job_id의 합의 전체 총계로 그룹화 하기
A.

SELECT department_id, job_id, COUNT(*), SUM(salary)  
FROM employees WHERE department_id > 50 GROUP BY CUBE(department_id, job_id);

 

Q3. UNION함수를 이용하여 employees 테이블에서 department_id가 100초과인 것들의 department_id, 줄 수,

     그리고 이 결과를 1로 하고 department_id로 그룹화,

      employees 테이블에서 department_id가 100초과인 것들의 manager_id, 줄 수,

     그리고 이 결과를 2로 한 것을 manager_id로 그룹화 한 것을 출력하기 
A.

SELECT department_id, COUNT(*), 1 FROM employees WHERE department_id > 100 GROUP BY department_id  
UNION 
SELECT manager_id, COUNT(*), 2 FROM employees WHERE department_id > 100 GROUP BY manager_id; 

Q4. Q3을 GROUPING SETS()함수로 변경하기
A.

SELECT department_id, manager_id, COUNT(*)  
FROM employees WHERE department_id > 100 GROUP BY GROUPING SETS(department_id, manager_id);

 
Q5. employees 테이블에서 department_id가 80 초과인 것들을 department_id로 그룹화해서

     department_id와 first_name을 ‘>>’ 기호로 가로로 나열하는데

     hire_date 순으로 오름차순 정렬하고 컬럼 이름을 “LISTAGG RESULT”라고 출력하기
A.

SELECT department_id, LISTAGG(first_name, '>>') WITHIN GROUP(ORDER BY hire_date) "LISTAGG RESULT"  
FROM employees WHERE department_id > 80 GROUP BY department_id;

// Tip - LISTAGG : 여러 행을 하나의 컬럼으로 가져오기 

SELECT LISTAGG(가져올 컬럼, 구분자) WITHIN GROUP (ORDER BY 컬럼) FROM 테이블 이름

ex)

SELECT LISTAGG(job_id, ', ') WITHIN GROUP (ORDER BY job_id) from jobs;

 

# 7

Q1. ddl_test라는 테이블을 만들기
     컬럼이름 no 정수3자리 
     컬럼이름 name 가변길이 10자리 
     컬럼이름 birth 날짜 기본값은 현재시간 
A.

CREATE TABLE ddl_test 
( no NUMBER(3), 
name VARCHAR2(10), 
birth DATE DEFAULT SYSDATE);

ex) 한글 이름으로 테이블과 컬럼 생성하기 

CREATE TABLE 한글테이블 
( 컬럼1 NUMBER, 
컬럼2 VARCHAR2(10), 
컬럼3 DATE);

Q2. employees2 테이블을 만들면서 employees 테이블 복사하기
A.

CREATE TABLE employees2 AS SELECT * FROM employees; 

Q3. employees3 테이블을 만들면서 employees 테이블의 department_id, first_name 컬럼만 복사하기
A.

CREATE TABLE employees3 AS SELECT department_id, first_name FROM employees;

Q4. employees4 테이블을 만들면서 employees 테이블의 구조만 복사하기
A.

CREATE TABLE employees4 AS SELECT * FROM employees WHERE 1 = 2;

Q5. 테이블명을 vtbl_1로 만들고  
     컬럼이름 no1 자료형 number, 
     컬럼이름 no2 자료형 number, 
     컬럼이름 no3 자료형 number 가상컬럼 num1과 num2를 더한 값 
A.

CREATE TABLE vtbl_1 ( no1 NUMBER, no2 NUMBER, no3 NUMBER GENERATED ALWAYS AS (no1 + no2) VIRTUAL);

Q6. vtbl_1 테이블에 1, 2, 3 데이터를 삽입한다.

A.

INSERT INTO vtbl_1 VALUES(1, 2, 3);

가상의 열에는 값을 임의로 넣을 수가 없어서 에러가 발생한다.

Q7. vtbl_1 테이블에 no1, no2 컬럼에 각각 1과 2를 삽입하고 전체 데이터 조회하기 
A.

INSERT INTO vtbl_1(no1, no2) VALUES(1, 2); 
SELECT * FROM vtbl_1;

Q8. vtbl_1 테이블의 no1의 값을 10으로 바꾸고 vtbl_1 테이블 전체 데이터 조회하기 
A.

UPDATE vtbl_1 SET no1=10; 
SELECT * FROM vtbl_1;

Q9. vtbl_1 테이블에 no1, no2 컬럼에 각각 3과 4를 삽입하고 vtbl_1 테이블 전체 데이터 조회하기 
A.

INSERT INTO vtbl_1 (no1, no2) VALUES(3, 4); 
SELECT * FROM vtbl_1;

10. vtbl_1 테이블에 no1, no2 컬럼에 각각 6과 6를 삽입하고 vtbl_1 테이블 전체 데이터 조회하기 
A.

INSERT INTO vtbl_1 (no1, no2) VALUES(6, 6); 
SELECT * FROM vtbl_1;

// Tip : 실습 후 다시 실습을 하는 경우 

drop table ddl_test; 
drop table 한글테이블; 
drop table employees2; 
drop table employees3; 
drop table employees4; 
drop table vtbl_1;