choice blog

-- hr 표준함수


-- 문제1) EMPLOYEES 테이블에서 King의 정보를 소문자로 검색하고 사원번호, 성명, 담당업무(소문자로),부서번호를 출력하라.

SELECT employee_id, LOWER(last_name), LOWER(job_id), department_id  FROM employees
WHERE LOWER(last_name) = 'king';


-- 문제2) EMPLOYEES 테이블에서 King의 정보를 대문자로 검색하고 사원번호, 성명, 담당업무(대문자로),부서번호를 출력하라.

SELECT employee_id, UPPER(last_name), UPPER(job_id),  department_id  FROM employees
WHERE UPPER(last_name) = 'KING';


-- 문제3) DEPARTMENTS 테이블에서 (부서번호와 부서이름), 부서이름과 위치번호를 합하여 출력하도록 하라.

SELECT department_id|| ' ' || department_name, department_name|| ' ' || location_id FROM departments;

SELECT CONCAT(department_id, department_name),
    CONCAT(CONCAT(department_name,' ' ), location_id)
FROM departments;


-- 문제4) EMPLOYEES 테이블에서 이름의 첫 글자가 ‘K’ 보다 크고 ‘Y’보다 적은 사원의 정보를 
-- 사원번호, 이름, 업무, 급여, 부서번호를 출력하라. 
-- 단 이름순으로 정렬하여라.

SELECT employee_id, first_name, job_id, salary, department_id FROM employees
WHERE SUBSTR(first_name, 1, 1) > 'K' AND  SUBSTR(first_name, 1, 1) < 'Y'
ORDER BY first_name; -- 오름차순


-- 문제5) EMPLOYEES 테이블에서 20번 부서 중 이름의 길이 및 급여의 자릿수를 
-- 사원번호, 이름, 이름의 자릿수(LENGTH), 급여, 급여의 자릿수를 출력하라.
-- LENGTHB는 BYTE 혼동하지 말자!

SELECT employee_id, first_name, LENGTH(first_name), salary,  LENGTH(salary)
FROM employees
WHERE department_id = 20;


-- 문제6) EMPLOYEES 테이블에서 이름 중 ‘e’자의 위치를 출력하라.

SELECT first_name, INSTR(first_name,'e',1,1) FROM employees;


-- 문제7) 다음의 쿼리를 실행하고 결과를 출력하고 확인하라.
SELECT  ROUND(4567.678),      -- 4568     
        ROUND(4567.678, 0),   -- 4568    
        ROUND(4567.678, 2),   -- 4567.68      
        ROUND(4567.678, -2)   -- 4600    
FROM dual;

-- ROUND(4567.678): 반올림(소수점 없음)
-- ROUND(4567.678, 0): 소수점 없음 
-- ROUND(4567.678, 2) : 소수점 2번째까지 반올림
-- ROUND(4567.678, -2) : 정수 두자릿수 짤림 00으로 그리고 반올림


-- 문제8) EMPLOYEES 테이블에서 부서번호가 80인 사람의 급여를 30으로 나눈 나머지를 구하여 출력하라.

SELECT first_name, department_id, salary, MOD(salary, 30) 
FROM employees
WHERE department_id = 80;


-- 문제9) EMPLOYEES 테이블에서 30번 부서 중 이름과 담당 업무를 연결하여 출력하여라. 

SELECT first_name || ' ' || job_id
FROM employees
WHERE department_id = 30;


-- 문제10) EMPLOYEES 테이블에서 현재까지 근무일 수가 몇주 몇일 인가를 출력하여라. 
-- 단 근무 일수가 많은 사람 순으로 출력하여라.

SELECT first_name, hire_date, 
TRUNC(SYSDATE - hire_date) AS "총근무 일수",
TRUNC((SYSDATE - hire_date) / 7) AS "총근무주 일수",
TRUNC(MOD((SYSDATE - hire_date) , 7)) AS "남은 일수"
FROM employees; 


-- 문제11) EMPLOYEES 테이블에서 부서 50에서 급여 앞에 $를 삽입하고 3자리마다 ,를 출력하라

SELECT first_name, department_id, salary, TO_CHAR(salary, '$999,999,999')
FROM employees
WHERE department_id = 50;

-- scott

-- 1 emp 테이블에서 사원번호, 사원이름, 월급을 출력하시오 .

SELECT empno, ename, sal FROM emp;


-- 2 emp 테이블에서 사원이름과 월급을 출력하는데 컬럼명은 이름, 월급으로 변경하여 출력하시오. 

SELECT ename "이 름", sal "월 급" FROM emp;


-- 3 emp 테이블에서 사원번호 사원이름 월급 연봉을 구하고 각각 컬럼명을 사원번호, 사원이름, 월급으로 변경하여 출력하시오.

SELECT empno 사원번호, ename 사원이름, sal 월급, sal*12 연봉 FROM emp;


-- 4 emp 테이블에서 업무(job)를 중복되지 않게 표시하시오.

SELECT DISTINCT job FROM emp;


-- 5 emp Table의 사원명과 업무로 연결 해서 표시하고 컬러명은 Employee and Job로 표시하시오.

SELECT '(' || ename || ',' || job || ')' AS "Emplyee and Job" FROM emp;


--6) emp 테이블에서 사원번호가 7698 인 사원의 이름, 업무, 급여를 출력하시오.

SELECT ename, job, sal FROM emp 
WHERE empno = 7698;


-- 7) emp 테이블에서 사원이름이 SMITH인 사람의 이름과 월급, 부서번호를 구하시오.

SELECT ename, sal, deptno FROM emp 
WHERE ename = 'SMITH';


-- 8) 월급이 2500이상 3500미만인 사원의 이름, 입사일, 월급을 구하시오.

SELECT ename, hiredate,sal FROM emp 
WHERE sal >= 2500 AND sal < 3500;


-- 9) 급여가 2000에서 3000사이에 포함되지 않는 사원의 이름, 업무, 급여를 출력하시오.

SELECT ename, job, sal FROM emp 
WHERE sal NOT BETWEEN 2000 AND 3000;


-- 10) 81년 05월 01일과 81년 12월 03일 사이에 입사한 사원의 이름, 급여, 입사일을 출력하시오.

SELECT ename, sal, hiredate FROM emp 
WHERE hiredate BETWEEN '81/05/01' AND '81/12/03';


-- 11) emp테이블에서 사원번호가 7566,7782,7934인 사원을 제외한 사람들의 사원번호,이름,  월급을 출력하시오.

SELECT empno, ename, sal FROM emp 
WHERE empno NOT IN (7566,7782,7934);


-- 12) 부서번호 30(deptno)에서 근무하며 월 2,000달러 이하를 받는 81년05월01일 이전에 입사한 사원의 이름, 급여, 부서번호, 입사일을 출력하시오.

SELECT ename, sal, deptno, hiredate FROM emp 
WHERE deptno = 30 AND sal <= 2000 AND hiredate < '81/05/01';  


-- 13) emp테이블에서 급여가 2,000와 5,000 사이고 부서번호가 10 또는 30인 사원의 이름과 급여,부서번호를 나열하시오.

SELECT ename, sal, deptno FROM emp 
WHERE (sal BETWEEN 2000 AND 5000) AND (deptno = 10 OR deptno= 30);
   
SELECT ename, sal, deptno FROM emp
WHERE (sal BETWEEN 2000 AND 5000) AND deptno IN (10,30);             


-- 14) 업무가 SALESMAN 또는 MANAGER이면서 급여가 1,600, 2,975 또는 2,850이 아닌 모든 사원의 이름, 업무 및 급여를 표시하시오.

SELECT ename, job, sal FROM emp
WHERE (job IN ('SALESMAN','MANAGER')) AND (sal NOT IN (1600,2975,2850)) ;


-- 15) emp테이블에서 사원이름 중 S가 포함되지 않은 사람들 중 부서번호가 20인 사원들의 이름과 부서번호를 출력하시오.

SELECT ename,deptno FROM emp 
WHERE ename NOT LIKE ('%S%') AND deptno = 20 ;


-- 16) emp테이블에서 이름에 A와 E가 있는 모든 사원의 이름을 표시하시오.

SELECT ename FROM emp 
WHERE ename LIKE '%A%' AND ename LIKE '%E%';


-- 17) emp테이블에서 관리자가 없는 모든 사원의 이름과 업무를 표시하시오.

SELECT ename, job FROM emp 
WHERE NVL(mgr,0) = 0;
 
SELECT ename, job FROM emp 
WHERE mgr IS NULL;


-- 18) emp테이블에서 커미션 항목이 입력된 사원들의 이름과 급여, 커미션을 구하시오.

SELECT ename, sal, comm FROM emp 
WHERE NVL(comm,0) > 0;

SELECT ename, sal, comm FROM emp 
WHERE comm IS NOT NULL AND comm <> 0;
   
SELECT ename, sal, comm FROM emp 
WHERE comm IS NOT NULL AND comm != 0;    

-- hr 스키마

-- 문제1) EMPLOYEES 테이블에서 급여가 3000이상인 사원의 사원번호, 이름, 담당업무, 급여를 출력하라.

SELECT employee_id, first_name, job_id, salary FROM employees 
WHERE salary >= 3000; 


-- 문제2) EMPLOYEES 테이블에서 담당 업무가 ST_MAN인 사원의 사원번호, 성명, 담당업무, 급여, 부서번호를 출력하라.

SELECT employee_id, first_name, job_id, salary, department_id FROM employees 
WHERE job_id = 'ST_MAN';


-- 문제3) EMPLOYEES 테이블에서 입사일자가 2006년 1월 1일 이후에 입사한 사원의 
-- 사원번호, 성명, 담당업무, 급여, 입사일자, 부서번호를 출력하라.

SELECT employee_id, first_name, job_id, salary, hire_date, department_id FROM employees 
WHERE hire_date > '06/01/01';

SELECT employee_id, first_name, job_id, salary, hire_date, department_id FROM employees 
WHERE hire_date > TO_DATE('060101','YYMMDD');


-- 문제4) EMPLOYEES 테이블에서 급여가 3000에서 5000사이의 사원의 성명, 담당업무, 급여, 부서번호를 출력하라.

SELECT first_name, job_id, salary, department_id FROM employees 
WHERE salary >= 3000 AND salary <= 5000;

SELECT first_name, job_id, salary, department_id FROM employees 
WHERE salary BETWEEN 3000 AND 5000;


-- 문제5) EMPLOYEES 테이블에서 사원번호가 145,152,203인 사원의 사원번호, 성명, 담당업무, 급여, 입사일자를 출력하라

SELECT employee_id, first_name, job_id, salary, hire_date FROM employees 
WHERE employee_id IN (145 , 152 , 203);


-- 문제6) EMPLOYEES 테이블에서 입사일자가 05년도에 입사한 사원의 사원번호, 성명, 담당업무, 급여, 입사일자, 부서번호를 출력하라.

SELECT employee_id, first_name, job_id, salary, hire_date, department_id FROM employees 
WHERE hire_date LIKE ('05%');


-- 문제7) EMPLOYEES 테이블에서 보너스가 없는 사원의 사원번호, 성명, 담당업무, 급여, 입사일자, 보너스, 부서번호를 출력하라.

SELECT employee_id, first_name, job_id, salary, hire_date, commission_pct, department_id FROM employees 
WHERE NVL(commission_pct,0)  =  0 ;

SELECT employee_id, first_name, job_id, salary, hire_date, commission_pct, department_id FROM employees 
WHERE commission_pct IS NULL ;


-- 문제8) EMPLOYEES 테이블에서 급여가 1100이상이고 JOB이 ST_MAN인 사원의 사원번호, 성명, 담당업무, 급여, 입사일자, 부서번호를 출력하라

SELECT employee_id, first_name, job_id, salary, hire_date, department_id FROM employees 
WHERE salary >= 1100 AND job_id = 'ST_MAN';


-- 문제9) EMPLOYEES 테이블에서 급여가 10000이상이거나 JOB이 ST_MAN인 사원의 사원번호, 성명, 담당업무, 급여, 입사일자, 부서번호를 출력하라

SELECT employee_id, first_name, job_id, salary, hire_date, department_id FROM employees 
WHERE salary >= 10000 OR job_id = 'ST_MAN';


-- 문제10) EMPLOYEES 테이블에서 JOB이 ST_MAN, SA_MAN, SA_REP가 아닌 사원의 사원번호, 성명, 담당업무, 급여, 부서번호를 출력하라

SELECT employee_id, first_name, job_id, salary, department_id FROM employees 
WHERE job_id NOT IN ('ST_MAN', 'SA_MAN', 'SA_REP'); 


-- 문제11) 업무가 AD_PRES이고 급여가 12000이상이거나 업무가 SA_MAN인 사원의 사원번호, 이름, 업무, 급여를 출력하라.

SELECT employee_id, first_name, job_id, salary FROM employees 
WHERE job_id = 'AD_PRES' AND salary >= 12000 OR job_id = 'SA_MAN';


-- 문제12) 업무가 AD_PRES 또는 SA_MAN이고 급여가 12000이상인 사원의 사원번호, 이름, 업무, 급여를 출력하라.

SELECT employee_id, first_name, job_id, salary FROM employees 
WHERE job_id IN ('AD_PRES' , 'SA_MAN') AND salary >= 12000;

-- 문제1) EMPLOYEES Table의 모든 자료를 출력하여라.

SELECT * FROM employees;


-- 문제2) EMPLOYEES Table의 컬럼들을 모두 출력하라.

DESC employees;


-- 문제3) EMPLOYEES Table에서 사원 번호, 이름, 급여, 담당업무를 출력하여라.

SELECT employee_id, first_name, salary, job_id FROM employees; 


-- 문제4) 모든 종업원의 급여를 $300증가 시키기 위해서 덧셈 연산자를 사용하고 결과에 SALARY+300을 디스플레이 합니다.

SELECT first_name, salary, salary+300 AS "SALARY+300" FROM employees;


-- 문제5) EMP 테이블에서 사원번호, 이름, 급여, 보너스, 보너스 금액을 출력하여라. 
-- (참고로 보너스는 월급 + (월급*커미션))

SELECT employee_id, first_name || last_name AS Name, salary, commission_pct, salary + (salary*commission_pct) as bouns FROM employees;


-- commission_pct을 Java로 가져올시 NullPointException 발생 !!  null -> 0 으로 바꿔서 가져와야함
-- NVL 함수 : NVL(대상컬럼, 대상컬럼이 NULL인 경우에 출력할 값) ★★★★★

SELECT employee_id, first_name, salary, NVL(commission_pct,0), salary+NVL(salary*commission_pct,0) FROM employees;


-- 문제6) EMPLOYEES 테이블에서 LAST_NAME을 이름으로 SALARY을 급여로 출력하여라.

SELECT last_name 이름, salary 급여 FROM employees;


-- 문제7) EMPLOYEES 테이블에서 LAST_NAME을 Name으로 SALARY * 12를 Annual Salary(연봉)로 출력하여라

SELECT last_name "Name", salary*12 "Annual Salary(연봉)" FROM employees;


-- 문제8) EMPLOYEES 테이블에서 이름과 업무를 연결하여 출력하여라. 

SELECT first_name || ' ' || last_name || ' : ' || job_id AS "이름 : 업무" FROM employees;


-- 문제9) EMPLOYEES 테이블에서 이름과 업무를 “KING is a PRESIDENT” 형식으로 출력하여라. 

SELECT last_name || ' is a ' || job_id FROM employees;


-- 문제10) EMPLOYEES 테이블에서 이름과 연봉을 “KING: 1 Year salary = 60000” 형식으로 출력하여라. 

SELECT last_name||': 1 Year salary = '|| salary*12 FROM employees;


-- 문제11) EMPLOYEES 테이블에서 JOB을 모두 출력하라.

SELECT DISTINCT job_id FROM employees;