[필기정리] Day64 - oracle 예제 문제

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

 

# 1

Q1. EMPLOYEES 테이블의 구조 보기

A.

DESC EMPLOYEES;

 

Q2. EMPLOYEES 테이블에 있는 모든 데이터 보기

A.

SELECT * FROM EMPLOYEES;

 

Q3. EMPLOYEES 테이블에 있는 employee_id, first_name, last_name 컬럼 보기

A.

SELECT employee_id, first_name, last_name FROM employees;

 

Q4. employees 테이블에서 employee_id, first_name과 ‘Very Good~~’ 라는 문자열 출력하기

A.

SELECT employee_id, first_name, 'Very Good~~' FROM employees;

 

Q5. employees 테이블에서 employee_id를 “EMPNO”라는 별명으로 first_name을

     “ENAME”이라는 별명으로 조회하기. 각기 다른 방법 사용.

A.

SELECT employee_id "EMPNO", first_name AS "ENAME" FROM employees;

 

Q6. employees 테이블에서 first_name과 last_name 조회하기

A.

SELECT first_name, last_name FROM employees;

 

Q7. employees 테이블에서 first_name과 last_name을 연결해서 조회하기

A.

SELECT first_name || last_name FROM employees;

 

Q8. employees 테이블에서 first_name과 last_name사이에 공백을 넣고 연결하기.

A. 

SELECT first_name || ' ' || last_name FROM employees;

 

Q9. employees 테이블에서 department_id를 조회하기

A.

SELECT department_id FROM employees;

 

Q10. employees 테이블에서 department_id를 조회하는 데 중복된 값은 제거하고 조회하기

A. 

SELECT DISTINCT department_id FROM employees;

 

Q11. employees 테이블에서 first_name을 조회하는데 department_id가 10인 것만 조회하기

A.

SELECT first_name FROM employees WHERE department_id=10;

 

Q12. employees 테이블에서 first_name, salary 컬럼을 조회하는데 salary가 15000 이상인 것만 조회하기

A.

SELECT first_name, salary FROM employees WHERE salary >= 15000;

 

Q13. employees 테이블에서 first_name, salary, department_id를 조회하는데 first_name이 Lex인 것을 조회

A.

SELECT first_name, salary, department_id FROM employees WHERE first_name='Lex';

 

Q14. employees 테이블에서 first_name, salary, hire_date를 조회하는데 hire_date가 ‘01/01/13’인 것을 조회하기

A.

SELECT first_name, salary, hire_date FROM employees WHERE hire_date='01/01/13';

 

Q15. employees 테이블에서 department_id가 10인 것의 first_name, salary, salary-100 출력하기

A.

SELECT first_name, salary, salary-100 FROM employees WHERE department_id=10;

 

Q16. employees 테이블에 있는 department_id가 10인 것을 조회하는데 first_name, salary, salary/12 출력하기

A.

SELECT first_name, salary, salary/12 FROM employees where department_id=10;

 

Q17. BETWEEN 연산자를 사용하여 employees 테이블에서

       salary가 14000과 17000 사이인 사람들의 first_name, salary 출력하기

A.

SELECT first_name, salary FROM employees WHERE salary BETWEEN 14000 AND 17000;

// Tip : BETWEEN은 범위에 해당되는 값을 찾을 때 사용하는데 크거나 같고 작거나 같은 값을 찾는다.

          따라서 ‘>=’와 ‘<=’ 논리 연산자로 변환이 가능하다.

 

Q18. employees 테이블에서 first_name이 ‘Neena’, ‘Lex’, ‘John’인 사람들의 first_name, last_name, salary 조회하기

A. 

SELECT first_name, last_name, salary FROM employees WHERE first_name IN('Neena', 'Lex', 'John');

 

Q19. employees 테이블에서 first_name이 L로 시작하는 것의 first_name, last_name, salary 조회하기

A.

SELECT first_name, last_name, salary FROM employees WHERE first_name like 'L%';

 

Q20. employees 테이블에서 first_name이 첫 번째 글자는 L로 시작하고, 가운데 글자는 모르고,

       세 번째 글자가 x로 끝나는 것의 first_name, last_name, salary 조회하기

A. 

SELECT first_name, last_name, salary FROM employees WHERE first_name like 'L_x';

 

21. employees 테이블에서 salary가 13000 이상 15000 이하인 것들의

     first_name, salary, commission_pct 출력하기

A. 

SELECT first_name, salary, commission_pct FROM employees WHERE salary BETWEEN 13000 AND 15000;

 

Q22. employees 테이블에서 commission_pct가 NULL인 것들의 first_name, salary, commission_pct 조회하기

A.

SELECT first_name, salary, commission_pct FROM employees WHERE commission_pct IS NULL;

 

Q23. employees 테이블에서 commission_pct가 NULL이 아닌 것들의

       first_name, salary, commission_pct 조회하기

A. 

SELECT first_name, salary, commission_pct FROM employees WHERE commission_pct IS NOT NULL;

 

Q24. employees 테이블에서 salary가 13000 초과이고 department_id가 80인 것들의

       first_name, salary, department_id 조회하기

A. 

SELECT first_name, salary, department_id FROM employees WHERE salary > 13000 AND department_id = 80;

 

Q25. employees 테이블에서 salary가 15000 초과이거나 department_id가 80인 것들의

       first_name, salary, department_id 조회하기

A. 

SELECT first_name, salary, department_id FROM employees WHERE salary > 15000 OR department_id=80;

 

Q26. employees테이블에서 department_id가 90번 부서에 근무하면서

       salary가 10000 초과인 사람들의 FIRST_NAME, SALARY, DEPARTMENT_ID, HIRE_DATE 조회하기

A.

SELECT first_name, salary, department_id, hire_date FROM employees WHERE salary > 10000 AND department_id=90;

 

Q27. Q26의 결과에서 이름(first_name)을 기준으로 오름차순(ASC) 정렬하기

A.

SELECT first_name, salary, department_id, hire_date FROM employees WHERE salary > 10000 AND department_id=90 order by first_name asc;

 

Q28. Q26의 결과에서 입사날짜(hire_date)를 기준으로 오름차순 정렬하기

A.

SELECT first_name, salary, department_id, hire_date FROM employees WHERE salary > 10000 AND department_id=90 order by hire_date asc;

 

Q29. employees 테이블에서 salary가 10000 초과이고 department_id가 90인 것들을

       Salary 값을 기준으로 내림차순(DESC)으로 먼저 정렬한 후

       동일한 salary값이 있을 경우 hire_date 값으로 한 번 더 오름차순(ASC)으로 정렬하기. 

       보여줘야 되는 컬럼은 first_name, salary, department_id, hire_date이다.

A.

SELECT first_name, salary, department_id, hire_date FROM employees WHERE salary > 10000 AND department_id=90 order by salary desc, hire_date asc;

 

Q30. Q29를 정렬할 때 컬럼의 위치 값을 이용해 정렬하기

A. 

SELECT first_name, salary, department_id, hire_date FROM employees WHERE salary > 10000 AND department_id=90 order by 2 desc, 4 asc;

 

# 2

Q1. employees 테이블에서 salary가 10000 이상이고 department_id가 90인 것들의

     department_id, salary의 조회 값과 employees 테이블에서 salary가 10000 이상이고

     department_id가 80인 것들의 department_id, salary의 조회 값을 정렬하고 중첩된 값을 제거하여 합치기 
A.

SELECT department_id, salary FROM employees WHERE salary >= 10000 AND department_id = 90  
UNION  
SELECT department_id, salary FROM employees WHERE salary >= 10000 AND department_id = 80;

// Tip : 정렬이 되어서 출력. 

          정렬 기준은 우선 첫 번째 컬럼으로 먼저 정렬을 한 후

          동일한 값이 있을 경우는 두 번째 컬럼으로 한 번 더 정렬한다.

 

// Tip : 정렬을 하게 되면 당연히 연산에 부하가 생길 수 밖에 없다!

    ㄴ ex) 영화관 티켓 - 100명이 이름순으로 줄을 정렬해서 다시 서는 것

Q2. Q1을 정렬하지 않고 중첩된 값도 나누고 합치기 
A.

SELECT department_id, salary FROM employees WHERE salary > 10000 AND department_id = 90  
UNION ALL  
SELECT department_id, salary FROM employees WHERE salary > 10000 AND department_id = 80;

// Tip : UNION과 다르게 정렬되지도 않았고 중복된 데이터도 그대로 출력된다.

         일반적으로 UNION은 정렬되어야 하기 때문에 속도가 느리다는 단점이 있다.

Q3. employees 테이블에서 department_id가 70이상인 것들의 salary와

     employees 테이블에서 department_id가 70미만인 부서의 salary 값 중 공통된 금액 조회하기 
A.

SELECT salary FROM employees WHERE department_id >=70  
INTERSECT
SELECT salary FROM employees WHERE department_id < 70; 

// Tip : INTERSECT는 두 SQL 문장의 실행 결과에서 공통으로 있는 교집합 부분을 찾아내는 집합 연산자.

          INTERSECT는 정렬을 동반하게 되는 연산자이므로 많은 데이터를 대상으로 할 경우 속도가 느려진다.

Q4. employees 테이블의 salary를 출력하되 department_id가 100번인 부서의 salary를 제외하고 출력하기 

SELECT salary FROM employees  
MINUS  
SELECT salary FROM employees WHERE department_id = 100;

//Tip : MINUS는 큰 집합에서 작은 집합을 빼는 집합 연산자이다.

         EMPLOYEES 테이블의 SALARY를 출력하되

         DEPARTMENT_ID가 100번인 부서의 SALARY를 제외하고 출력한다.

         MINUS도 결과 값을 정렬한다. 데이터 양이 많을 경우 시간이 오래 걸린다.

         

         그리고 집합 연산자에서 주의 사항이 있는데,

         두 개 쿼리의 칼럼의 개수가 다르거나 데이터 형이 다르면 에러가 발생

Q5. employees 테이블에서 department_id가 100인 first_name과 first_name의 첫글자를 대문자로 출력하기 
A.

SELECT first_name, INITCAP(first_name) FROM employees WHERE department_id = 100;

Q6. employees테이블에서 department_id가 100인 first_name,

     소문자 first_name, 대문자 first_name을 조회하기
A.

SELECT first_name, LOWER(first_name), UPPER(first_name) FROM employees WHERE department_id = 100;

Q7. employees 테이블에서 department_id가 100인 first_name,

     first_name의 길이, first_name의 길이(바이트 기준)를 조회하기
A.

SELECT first_name, LENGTH(first_name), LENGTHB(first_name) FROM employees WHERE department_id = 100;

 

// Tip : LENGTH (문자열의 길이), LENGTHB(문자열의 바이트수)

Q8. employees 테이블에서 department_id가 100인 first_name, first_name에서 1번째 글자부터 3번째 글자까지,         

     first_name에서 오른쪽 끝에서 3번째 글자부터 2글자를 조회하기
A.

SELECT first_name, SUBSTR(first_name, 1, 3), SUBSTR(first_name, -3, 2) FROM employees WHERE department_id = 100;

// Tip : SUBSTR(‘문자열’ 또는 컬럼명, 시작위치, 골라낼 글자 수)

Q9. ‘서진수’ 출력 ‘서진수’ 두 글자 출력, ‘서진수’ 두 바이트 출력하기
A.

SELECT '서진수', SUBSTR('서진수', 1, 2), SUBSTRB('서진수', 1, 2) FROM dual;

// Tip : SUBSTRB함수는 SUBSTR 함수와 문법은 동일하며 차이점은 추출할 자릿수가 아니라 추출할 byte 수를 지정.

Q10. employees 테이블에서 department_id가 100인 것들의

       hire_date, hire_date에서 1번째 자리를 기준으로 2번째 ‘/’ 문자가 오는 위치를 찾기
A.

SELECT hire_date, INSTR(hire_date, '/', 1, 2) FROM employees WHERE department_id = 100;

// Tip : INSTR()는 주어진 문자열이나 컬럼에서 특정 글자가 있는 위치를 찾아주는 함수이다. 
          INSTR(‘문자열’ 또는 컬럼, 찾는 글자, 시작 위치, 몇 번째인지(기본값은 1))

 

 

#3

Q11. employees 테이블에서 department_id가 100인 것들의 hire_date, hire_date에서

       오른쪽 끝에서 1번째 자리를 기준으로 왼쪽으로 2번째 ‘/’ 문자가 오는 위치를 찾기 
A.

SELECT hire_date, INSTR(hire_date, '/', -1, 2) FROM employees WHERE department_id = 100;

Q12. employees 테이블에서 department_id가 100인 것들의 first_name, first_name을 10글자로 두고

       왼쪽에 남는 공간을 ‘*’ 로 채우고, first_name을 10글자로 두고 오른쪽에 남는 공간을 ‘*’로 채우기
A.

SELECT LPAD(first_name, 10, '*'), RPAD(first_name, 10, '*') FROM employees WHERE department_id = 100;

// Tip

- LPAD(‘문자열’ 또는 컬럼명, 자리수, ‘채울 문자’) : 왼쪽 공백에 특별한 문자로 채우기  
- RPAD(‘문자열’ 또는 컬럼명, 자리수, ‘채울문자’) : 오른쪽 공백에 특별한 문자로 채우기 

Q13. employees 테이블에서 department_id가 100인 것들의

       first_name, first_name의 왼쪽에서 ‘J’ 제거, first_name의 오른쪽에서 ‘l’ 제거하기

A.

SELECT first_name, LTRIM(first_name, 'J'), RTRIM(first_name,'l') FROM employees WHERE department_id = 100;

//Tip

- LTRIM(‘문자열’ 또는 컬럼명, ‘제거할 문자’) : 왼쪽에 지정된 문자가 있을 경우 제거
- RTRIM(‘문자열’ 또는 컬럼명, ‘제거할 문자’) : 오른쪽에 지정된 문자가 있을 경우 제거하는 함수

Q14. employees 테이블에서 department_id가 100인 것들의

       first_name, first_name 값 중에서 2번째 글자부터 연속 3글자를 모두 *표로 변경하기

A.

SELECT first_name, REPLACE(first_name, SUBSTR(first_name,2, 3), '***') FROM employees WHERE department_id = 100;

// Tip : REPLACE(‘문자열’ 또는 컬럼명, ‘문자1’, ‘문자2’)

         주어진 첫 번째 문자열이나 컬럼에서 문자1을 문자2로 바꾸는 함수.

Q15. 조회 123.456에서 소수점 이하 2자리에서 반올림 컬럼명 “R1”,

       123.456에서 소수점이하 0 에서 반올림 컬럼명 “R2”,

       123.456에서 소수점이하 -1번째에서 반올림 컬럼명 “R3”
A.

SELECT ROUND(123.456, 2) "R1", ROUND(123.456, 0) "R2", ROUND(123.456, -1) "R3" FROM dual;

// Tip : 오라클에서는 실제 테이블이나 데이터가 존재하지 않지만 

         쿼리를 통해서 임시로 테이블 구조와 데이터를 만들수 있는 dual이라는 더미 테이블이 있다.

 

// Tip : ROUND(숫자, 출력을 원하는 자리수) / TRUNC(숫자, 출력을 원하는 자리수)    
          ROUND() 함수는 주어진 숫자에서 반올림을 하는 함수이고, TRUNC() 함수는 버림을 한다. 

Q16. 조회 123.456에서 소수점이하 2자리에서 버림 컬럼명 “T1”,

       123.456 소수점이하 0 에서 버림 컬럼명 “T2”,

       123.456 소수점이하 -1번째에서 버림 컬럼명 “T3”

A.

SELECT TRUNC(123.456, 2) "T1", TRUNC(123.456, 0) "T2", TRUNC(123.456, -1) "T3" FROM dual;

Q17. 조회 10을 3으로 나눈 나머지 조회 컬럼명 “MOD”,

       0.3333 숫자가 가장 가까운 큰 수 출력 컬럼명 “CEIL”,

       1.3333 숫자에서 가장 가까운 작은 수 출력 컬럼명 “FLOOR”
A.

SELECT MOD(10, 3) "MOD", CEIL(0.3333) "CEIL", FLOOR(1.333) "FLOOR" FROM dual;

// Tip

- MOD : 나머지 값을 구하는 함수.

- CEIL : 주어진 숫자와 가장 가까운 큰 정수를 구하는 함수.

- FLOOR : 주어진 숫자와 가장 가까운 작은 정수를 구하는 함수.

Q18. 현재의 날짜와 시간을 출력하기 
A.

SELECT SYSDATE FROM dual;

// Tip - SYSDATE : 현재 날짜와 시간을 출력해 주는 함수

Q19. 세션에 있는 시간포맷을 ‘2014-12-30:01:07:52’로 바꾸기 
       그리고 현재의 날짜와 시간을 출력하기 
A.

ALTER SESSION SET NLS_DATE_FORMAT='RRRR-MM-DD:HH24:MI:SS'; 
SELECT SYSDATE FROM dual;

Q20. employees 테이블에서 department_id가 110인 것들의

       현재날짜와 시간, 현재날짜와 hire_date사이의 개월 수를 조회하기 
A.

SELECT SYSDATE, MONTHS_BETWEEN (SYSDATE, hire_date) FROM employees WHERE department_id = 110;

 

// Tip - MONTHS_BETWEEN : 두 날짜를 입력 받아서 두 날짜 사이의 개월 수를 출력하는 함수