23.10.10. Class 복습 겸 문제

[문제1] employees 테이블에서 employee_id, last_name과 first_name은 연결해서 표시하고(공백으로 구분) 열 별칭은 화면예 처럼 보고서 작성해 주세요.

<화면결과>

Emp#	Employee Name
100	King Steven
101	Kochhar Neena
102	De Haan Lex
103	Hunold Alexander
104	Ernst Bruce
...	...

SELECT employee_id as "Emp#", last_name||' '||first_name as "Emplyoee Name"
FROM hr.employees ;

 

[문제2] employees 테이블에서 컬럼중에 last_name, job_id를 연결해서 표시하고 (쉼표와 공백으로 구분) 열 별칭은 화면예 처럼 보고서 작성하세요.

<화면결과>

Employee and Title

Abel, SA_REP

Ande, SA_REP

....

SELECT last_name||', '||job_id as "Employee and Title"
FROM hr.employees ;

 

[문제3] departments 테이블에 있는 데이터에서 department_name , manager_id 칼럼을 가지고 화면 결과처럼 출력하는 쿼리 문장을 만드세요.

<화면결과>

Department and Manager

Administration Department's Manager Id: 200

Marketing Department's Manager Id: 201

....

SELECT department_name||' Department''s Manager Id: '|| manager_id as "Department and Manager"
FROM hr.departments ;

SELECT department_name||q'[ Department's Manager Id: ]'|| manager_id as "Department and Manager"
FROM hr.departments ;

 

[문제4] 2006년도 입사한 사원의 employee_id, last_name, hire_date를 출력해주세요.

(단, last_name을 기준으로 오름차순정렬 해주세요.)

SELECT employee_id, last_name, hire_date
FROM hr.employees
WHERE hire_date BETWEEN '06/01/01' AND '06/12/31'
ORDER BY last_name ASC ;

 

[문제5] 80번 department_id 사원중에 commission_pct 값이 0.2 이고 job_id는 SA_MAN인 사원의 employee_id, last_name, salary를 출력해주세요.
(단, last_name을 기준으로 오름차순정렬해 주세요.)

SELECT employee_id, last_name, salary
FROM hr.employees
WHERE department_id =80  
AND commission_pct = 0.2 
AND job_id = 'SA_MAN'
ORDER BY last_name ASC ;

 

[문제6] salary가 5000 ~ 12000의 범위에 속하지 않는 모든 사원의 last_name 및 salary를 출력해주세요.

(단, salary을 기준으로 내림차순 정렬하세요.)

SELECT last_name, salary
FROM hr.employees
WHERE salary NOT BETWEEN 5000 AND 12000
ORDER BY salary DESC ;

 

[문제7] employees 테이블에 last_name 컬럼의 값 중에  "J" 또는 "A" 또는 "M"으로 시작하는 사원들의 last_name, last_name의 길이를 표시하는 query(select문) 를 작성합니다. 사원들의 last_name 기준으로 내림차순 정렬해 주세요. 

SELECT last_name, length(last_name)
FROM hr.employees
WHERE last_name LIKE 'J%'
OR last_name LIKE 'A%'
OR last_name LIKE 'M%'
ORDER BY last_name DESC ;

SELECT last_name, length(last_name)
FROM hr.employees
WHERE substr(last_name, 1, 1) = 'J'
OR substr(last_name, 1, 1) = 'A'
OR substr(last_name, 1, 1) = 'M'
ORDER BY last_name DESC ;

SELECT last_name, length(last_name)
FROM hr.employees
WHERE instr(last_name, 'J', 1, 1) = 1
OR instr(last_name, 'A', 1, 1) = 1
OR instr(last_name, 'M', 1, 1) = 1
ORDER BY last_name DESC ;

 

[문제8] employees테이블에서 department_id(부서코드)가 50번 사원들 중에 last_name에 두번째 위치에 "a"글자가 있는 사원들을 조회하세요. 

SELECT *
FROM hr. employees 
WHERE department_id = 50 
AND last_name LIKE '_a%';

SELECT *
FROM hr. employees 
WHERE department_id = 50 
AND substr(last_name, 2, 1) = 'a' ;

SELECT *
FROM hr. employees 
WHERE department_id = 50 
AND instr(last_name, 'a', 1, 1) = 2; -- ex) aaron로 되어 있다면 instr(last_name, 'a', 2,1) =2 로 작성하기

 

[문제9] 주민번호 뒷 6자리부분을 아래화면과 같이 출력해주세요.

주민번호_1	주민번호_2
210101-1234567	210101-1******

SELECT 
    '210101-1234567' 주민번호_1, 
    rpad(substr('210101-1234567', 1, 8), 14, '*') 주민번호_2
FROM dual ;

-- 첫 번째 순서
SELECT 
    '210101-1234567' 주민번호_1,
    rpad('210101-1234567', 14, '*') 주민번호_2
FROM dual ;

-- 두 번째 순서
SELECT 
    '210101-1234567' 주민번호_1, 
    substr('210101-1234567', 1, 8) "substr",
    rpad(substr('210101-1234567', 1, 8), 14, '*') 주민번호_2
FROM dual ;

-- 세 번째 순서
SELECT 
    '210101-1234567' 주민번호_1, 
    substr('210101-1234567', 1, 8) "substr",
    instr('210101-1234567', '-', 1, 1) "instr" -- 위치
FROM dual ;

-- 네 번째 순서
SELECT 
    '210101-1234567' 주민번호_1, 
    substr('210101-1234567', 1, 8) "substr", -- 표시
    instr('210101-1234567', '-', 1, 1) "instr", -- 자릿수 위치
    rpad(substr('210101-1234567', 1, instr('210101-1234567', '-', 1, 1)+1), 14, '*') 주민번호_2 ,
    rpad(substr('210101-1234567', 1, 8), 14, '*') 주민번호_3
FROM dual ;

 

tip)

-문제를 풀 때, 

SELECT

FROM 

WHERE

ORDER BY 

먼저 작성을 하고 나서 채워나가는 것을 습관화하자!

 

- SELECT * 

FROM hr.employees; 

처럼 hr. 을 쓰는 습관화 하자!

각 클라이언트에서 쓰는 별칭이 다르기때문에 쓰는 것을 습관화 하는 것을 좋다!

 

- LIKE '%'

- substr(컬럼(문자열), 시작점, 추출 갯수) = 문자

- instr(컬럼명, 시작 위치, 몇번째로 찾는 위치)= 찾을 문자 

 

- 실무적으로 투입이 되었을 경우, 개발자가 쿼리문을 작성한 것을 보고 진행해야 하는 경우가 있다.

그럴때에는 해당 쿼리문을 한 문장씩 실행시켜서 어떤 것을 뜻하는지 알아봐야 한다.

"문제 9번이 진행하면 된다."