SQL SELECT 실습

CREATE TABLE s_emp
(id             NUMBER(7) primary key,
 name           VARCHAR2(25),
 mailid         VARCHAR2(8),
 start_date     DATE,
 manager_id     NUMBER(7),
 title          VARCHAR2(25),
 dept_id        NUMBER(7),
 salary         NUMBER(11, 2),
 commission_pct NUMBER(4, 2)
);

INSERT INTO s_emp VALUES (
  1, '박구곤', 'sky98',
   to_date('03-MAR-1990','dd-mon-yyyy'), NULL, '사장',
   118, 5000, NULL);
INSERT INTO s_emp VALUES (
   2, '손명성', '2xxsig',
   to_date('08-MAR-1990','dd-mon-yyyy'), 1, '기획부장',
   110, 3000, NULL);
INSERT INTO s_emp VALUES (
   3, '이은선', 'ch21175',
   to_date('17-JUN-1991','dd-mon-yyyy'), 1, '영업부장',
   102, 3500, NULL);
INSERT INTO s_emp VALUES (
   4, '서의홍', 'mquickto',
   to_date('07-APR-1990','dd-mon-yyyy'), 1, '총무부장',
   101, 3000, NULL);
INSERT INTO s_emp VALUES (
   5, '김심선', 'aropebur',
   to_date('04-MAR-1990','dd-mon-yyyy'), 1, '인사부장',
   118, 3200, NULL);
INSERT INTO s_emp VALUES (
   6, '전지영', 'murguhar',
   to_date('18-JAN-1991','dd-mon-yyyy'), 2, '과장',
   110, 2400, NULL);
INSERT INTO s_emp VALUES (
   7, '김정미', 'enchu',
   to_date('14-MAY-1990','dd-mon-yyyy'), 2, '과장',
   111, 2400, NULL);
INSERT INTO s_emp VALUES (
   8, '신기웅', 'bbiri',
   to_date('07-APR-1990','dd-mon-yyyy'), 2, '과장',
   112, 2100, NULL);
INSERT INTO s_emp VALUES (
   9, '이상헌', 'acatchpo',
   to_date('09-FEB-1992','dd-mon-yyyy'), 2, '과장',
   113, 2300, NULL);
INSERT INTO s_emp VALUES (
   10, '최순철', 'mhavel',
   to_date('27-FEB-1991','dd-mon-yyyy'), 2, '과장',
   113, 2300, NULL);
INSERT INTO s_emp VALUES (
   11, '최정선', 'cmagee',
   to_date('14-MAY-1990','dd-mon-yyyy'), 3, '영업대표이사',
   102, 2450, 10);
INSERT INTO s_emp VALUES (
   12, '안은경', 'hgiljum',
   to_date('18-JAN-1992','dd-mon-yyyy'), 3, '영업대표이사',
   103, 2500, 12.5);
INSERT INTO s_emp VALUES (
   13, '김석우', 'ysedeghi',
   to_date('18-FEB-1991','dd-mon-yyyy'), 3, '영업대표이사',
   104, 2650, 10);
INSERT INTO s_emp VALUES (
   14, '박성규', 'mnguyen',
   to_date('22-JAN-1992','dd-mon-yyyy'), 3, '영업대표이사',
   105, 2650, 15);
INSERT INTO s_emp VALUES (
   15, '신종필', 'adumas',
   to_date('09-OCT-1991','dd-mon-yyyy'), 3, '영업대표이사',
   106, 2400, 17.5);
INSERT INTO s_emp VALUES (
   16, '이기철', 'emaduro',
   to_date('07-FEB-1992','dd-mon-yyyy'), 6, '사원',
   110, 2400, NULL);
INSERT INTO s_emp VALUES (
   17, '구원상', 'gsmith',
   to_date('08-MAR-1990','dd-mon-yyyy'), 6, '사원',
   110, 940, NULL);
INSERT INTO s_emp VALUES (
   18, '서정주', 'anozaki',
   to_date('09-FEB-1991','dd-mon-yyyy'), 7, '사원',
   111, 1200, NULL);
INSERT INTO s_emp VALUES (
   19, '안창환', 'vpatel',
   to_date('06-AUG-1991','dd-mon-yyyy'), 7, '사원',
   111, 795, NULL);
INSERT INTO s_emp VALUES (
   20, '윤정숙', 'cnewman',
   to_date('21-JUL-1991','dd-mon-yyyy'), 8, '사원',
   112, 750, NULL);
INSERT INTO s_emp VALUES (
   21, '장현철','amarkari',
   to_date('26-MAY-1991','dd-mon-yyyy'), 8, '사원',
   112, 850, NULL);
INSERT INTO s_emp VALUES (
   22, '장혜숙', 'echang',
   to_date('30-NOV-1990','dd-mon-yyyy'), 9, '사원',
   113, 800, NULL);
INSERT INTO s_emp VALUES (
   23, '강연범', 'rpatel',
   to_date('17-OCT-1990','dd-mon-yyyy'), 9, '사원',
   106, 795, NULL);
INSERT INTO s_emp VALUES (
   24, '오은경', 'bdancs',
   to_date('17-MAR-1991','dd-mon-yyyy'), 10, '사원',
   113, 860, NULL);
INSERT INTO s_emp VALUES (
   25, '이용호', 'sschwart',
   to_date('09-MAY-1991','dd-mon-yyyy'), 10, '사원',
   113, 1100, NULL);
COMMIT;

SELECT * FROM s_emp;

==============================================================================
SELECT * FROM s_emp;


-- 1번
-- 112, 118부서를 대상으로 과장과 사원의 이름, 직급, 급여, 부서ID를 조회하되, 급여는 1500 이상 4000 이하인 직원만 출력되도록 하시오. 
-- 단, 급여가 많은 사람부터 화면에 출력합니다.

SELECT NAME 이름, TITLE 직급, SALARY 급여, DEPT_ID 부서ID
FROM S_EMP
WHERE SALARY BETWEEN 1500 AND 4000
AND DEPT_ID IN('112', '118')
AND TITLE IN ('사원','과장')
ORDER BY SALARY DESC;

--2번
--관리자가 없는 직원의 이름, 직급, 급여를 조회하시오.(힌트. manager_id 컬럼이 자신의 상사가 누구인지 알 수 있는 컬럼입니다.)

SELECT NAME 이름, TITLE 직급, SALARY 급여, DEPT_ID 부서ID
FROM S_EMP
WHERE MANAGER_ID IS NULL;

--3번
--직급이 부장이면서 급여가 3000 이상인 직원의 이름, 직급, 급여를 조회하시오. 

SELECT NAME 이름, TITLE 직급, SALARY 급여
FROM S_EMP
WHERE TITLE LIKE '__부장'
AND SALARY >=3000;

--4번
--직급이 부장과 과장인 사람들을 대상을 이름, 직급, 급여를 조회하시오. 
--단, 112, 113, 117, 118 부서에 소속되어 있는 사원 중에서 급여가 2300 ~ 3000 사이인 직원만 조회되도록 하시오.
--부서순으로 출력합니다.

SELECT NAME 이름, TITLE 직급, SALARY 급여
FROM S_EMP
WHERE (TITLE LIKE '%부장' OR TITLE LIKE '%과장')
AND DEPT_ID IN (112, 113, 117, 118)
AND SALARY BETWEEN 2300 AND 3000
ORDER BY DEPT_ID;

--5번
--직급이 부장 혹은 과장이면서 03월에 입사한 직원의 이름, 직급, 급여, 입사일을 조회하시오

SELECT NAME 이름, TITLE 직급, SALARY 급여, TO_CHAR(START_DATE, 'YYYY-MM') 입사일
FROM S_EMP
WHERE (TITLE LIKE '%부장' OR TITLE LIKE '%과장')
AND TO_CHAR(START_DATE, 'MM') LIKE '03';


--6번
-- 직원의 연봉에 1000 을 더한 값을 조회하시오. 
-- 연봉은 급여에 16을 곱하면 됩니다. 단, 연봉은 ALIAS를 지정하여 화면에 출력시 "직원연봉" 으로 출력되도록 합니다.

SELECT E.SALARY*16+1000 직원연봉, E.NAME 이름, E.SALARY 급여 
FROM S_EMP E;

--7번
--출력결과가 다음과 같도록 조회하시오. 
--직원 급여 
--박구곤 사장의 급여 5,000 
--손명성 기획부장의 급여 3,000 
--이은선 영업부장의 급여 3,500 
--서의홍 총무부장의 급여 3,000 
--김심선 인사부장의 급여 3,200 
--... 
--... 

SELECT NAME || ' ' || TITLE || '의 급여 :' || TO_CHAR(SALARY, '9,999') AS 직원정보
FROM S_EMP; 


--8번
--커미션을 받으면서 자신을 관리하는 관리자가 있는 직원을 대상으로 급여가 2500 이상인 직원을 조회하시오. 
--화면에 출력 시 직원의 연봉이 낮은 사람부터 출력합니다.
  
SELECT * 
FROM S_EMP
WHERE COMMISSION_PCT IS NOT NULL
AND MANAGER_ID IS NOT NULL
AND SALARY >=2500
ORDER BY SALARY;