SELECT USER
FROM DUAL;
CREATE TABLE TBL_INSA
( NUM NUMBER(5) NOT NULL
, NAME VARCHAR2(20) NOT NULL
, SSN VARCHAR2(14) NOT NULL
, IBSADATE DATE NOT NULL
, CITY VARCHAR2(10)
, TEL VARCHAR2(15)
, BUSEO VARCHAR2(15) NOT NULL
, JIKWI VARCHAR2(15) NOT NULL
, BASICPAY NUMBER(10) NOT NULL
, SUDANG NUMBER(10) NOT NULL
, CONSTRAINT TBL_INSA_NUM_PK PRIMARY KEY(NUM)
);
ALTER SESSION SET NLS_DATE_FORMAT ='YYYY-MM-DD';
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1001, '홍길동', '771212-1022432', '1998-10-11', '서울', '011-2356-4528', '기획부', '부장', 2610000, 200000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1002, '이순신', '801007-1544236', '2000-11-29', '경기', '010-4758-6532', '총무부', '사원', 1320000, 200000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1003, '이순애', '770922-2312547', '1999-02-25', '인천', '010-4231-1236', '개발부', '부장', 2550000, 160000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1004, '김정훈', '790304-1788896', '2000-10-01', '전북', '019-5236-4221', '영업부', '대리', 1954200, 170000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1005, '한석봉', '811112-1566789', '2004-08-13', '서울', '018-5211-3542', '총무부', '사원', 1420000, 160000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1006, '이기자', '780505-2978541', '2002-02-11', '인천', '010-3214-5357', '개발부', '과장', 2265000, 150000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1007, '장인철', '780506-1625148', '1998-03-16', '제주', '011-2345-2525', '개발부', '대리', 1250000, 150000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1008, '김영년', '821011-2362514', '2002-04-30', '서울', '016-2222-4444', '홍보부', '사원', 950000 , 145000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1009, '나윤균', '810810-1552147', '2003-10-10', '경기', '019-1111-2222', '인사부', '사원', 840000 , 220400);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1010, '김종서', '751010-1122233', '1997-08-08', '부산', '011-3214-5555', '영업부', '부장', 2540000, 130000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1011, '유관순', '801010-2987897', '2000-07-07', '서울', '010-8888-4422', '영업부', '사원', 1020000, 140000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1012, '정한국', '760909-1333333', '1999-10-16', '강원', '018-2222-4242', '홍보부', '사원', 880000 , 114000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1013, '조미숙', '790102-2777777', '1998-06-07', '경기', '019-6666-4444', '홍보부', '대리', 1601000, 103000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1014, '황진이', '810707-2574812', '2002-02-15', '인천', '010-3214-5467', '개발부', '사원', 1100000, 130000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1015, '이현숙', '800606-2954687', '1999-07-26', '경기', '016-2548-3365', '총무부', '사원', 1050000, 104000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1016, '이상헌', '781010-1666678', '2001-11-29', '경기', '010-4526-1234', '개발부', '과장', 2350000, 150000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1017, '엄용수', '820507-1452365', '2000-08-28', '인천', '010-3254-2542', '개발부', '사원', 950000 , 210000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1018, '이성길', '801028-1849534', '2004-08-08', '전북', '018-1333-3333', '개발부', '사원', 880000 , 123000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1019, '박문수', '780710-1985632', '1999-12-10', '서울', '017-4747-4848', '인사부', '과장', 2300000, 165000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1020, '유영희', '800304-2741258', '2003-10-10', '전남', '011-9595-8585', '자재부', '사원', 880000 , 140000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1021, '홍길남', '801010-1111111', '2001-09-07', '경기', '011-9999-7575', '개발부', '사원', 875000 , 120000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1022, '이영숙', '800501-2312456', '2003-02-25', '전남', '017-5214-5282', '기획부', '대리', 1960000, 180000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1023, '김인수', '731211-1214576', '1995-02-23', '서울', NULL , '영업부', '부장', 2500000, 170000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1024, '김말자', '830225-2633334', '1999-08-28', '서울', '011-5248-7789', '기획부', '대리', 1900000, 170000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1025, '우재옥', '801103-1654442', '2000-10-01', '서울', '010-4563-2587', '영업부', '사원', 1100000, 160000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1026, '김숙남', '810907-2015457', '2002-08-28', '경기', '010-2112-5225', '영업부', '사원', 1050000, 150000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1027, '김영길', '801216-1898752', '2000-10-18', '서울', '019-8523-1478', '총무부', '과장', 2340000, 170000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1028, '이남신', '810101-1010101', '2001-09-07', '제주', '016-1818-4848', '인사부', '사원', 892000 , 110000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1029, '김말숙', '800301-2020202', '2000-09-08', '서울', '016-3535-3636', '총무부', '사원', 920000 , 124000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1030, '정정해', '790210-2101010', '1999-10-17', '부산', '019-6564-6752', '총무부', '과장', 2304000, 124000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1031, '지재환', '771115-1687988', '2001-01-21', '서울', '019-5552-7511', '기획부', '부장', 2450000, 160000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1032, '심심해', '810206-2222222', '2000-05-05', '전북', '016-8888-7474', '자재부', '사원', 880000 , 108000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1033, '김미나', '780505-2999999', '1998-06-07', '서울', '011-2444-4444', '영업부', '사원', 1020000, 104000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1034, '이정석', '820505-1325468', '2005-09-26', '경기', '011-3697-7412', '기획부', '사원', 1100000, 160000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1035, '정영희', '831010-2153252', '2002-05-16', '인천', NULL , '개발부', '사원', 1050000, 140000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1036, '이재영', '701126-2852147', '2003-08-10', '서울', '011-9999-9999', '자재부', '사원', 960400 , 190000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1037, '최석규', '770129-1456987', '1998-10-15', '인천', '011-7777-7777', '홍보부', '과장', 2350000, 187000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1038, '손인수', '791009-2321456', '1999-11-15', '부산', '010-6542-7412', '영업부', '대리', 2000000, 150000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1039, '고순정', '800504-2000032', '2003-12-28', '경기', '010-2587-7895', '영업부', '대리', 2010000, 160000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1040, '박세열', '790509-1635214', '2000-09-10', '경북', '016-4444-7777', '인사부', '대리', 2100000, 130000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1041, '문길수', '721217-1951357', '2001-12-10', '충남', '016-4444-5555', '자재부', '과장', 2300000, 150000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1042, '채정희', '810709-2000054', '2003-10-17', '경기', '011-5125-5511', '개발부', '사원', 1020000, 200000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1043, '양미옥', '830504-2471523', '2003-09-24', '서울', '016-8548-6547', '영업부', '사원', 1100000, 210000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1044, '지수환', '820305-1475286', '2004-01-21', '서울', '011-5555-7548', '영업부', '사원', 1060000, 220000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1045, '홍원신', '690906-1985214', '2003-03-16', '전북', '011-7777-7777', '영업부', '사원', 960000 , 152000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1046, '허경운', '760105-1458752', '1999-05-04', '경남', '017-3333-3333', '총무부', '부장', 2650000, 150000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1047, '산마루', '780505-1234567', '2001-07-15', '서울', '018-0505-0505', '영업부', '대리', 2100000, 112000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1048, '이기상', '790604-1415141', '2001-06-07', '전남', NULL , '개발부', '대리', 2050000, 106000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1049, '이미성', '830908-2456548', '2000-04-07', '인천', '010-6654-8854', '개발부', '사원', 1300000, 130000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1050, '이미인', '810403-2828287', '2003-06-07', '경기', '011-8585-5252', '홍보부', '대리', 1950000, 103000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1051, '권영미', '790303-2155554', '2000-06-04', '서울', '011-5555-7548', '영업부', '과장', 2260000, 104000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1052, '권옥경', '820406-2000456', '2000-10-10', '경기', '010-3644-5577', '기획부', '사원', 1020000, 105000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1053, '김싱식', '800715-1313131', '1999-12-12', '전북', '011-7585-7474', '자재부', '사원', 960000 , 108000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1054, '정상호', '810705-1212141', '1999-10-16', '강원', '016-1919-4242', '홍보부', '사원', 980000 , 114000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1055, '정한나', '820506-2425153', '2004-06-07', '서울', '016-2424-4242', '영업부', '사원', 1000000, 104000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1056, '전용재', '800605-1456987', '2004-08-13', '인천', '010-7549-8654', '영업부', '대리', 1950000, 200000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1057, '이미경', '780406-2003214', '1998-02-11', '경기', '016-6542-7546', '자재부', '부장', 2520000, 160000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1058, '김신제', '800709-1321456', '2003-08-08', '인천', '010-2415-5444', '기획부', '대리', 1950000, 180000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1059, '임수봉', '810809-2121244', '2001-10-10', '서울', '011-4151-4154', '개발부', '사원', 890000 , 102000);
INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
VALUES (1060, '김신애', '810809-2111111', '2001-10-10', '서울', '011-4151-4444', '개발부', '사원', 900000 , 102000);
COMMIT;
SELECT *
FROM TBL_INSA;
SELECT *
FROM TBL_INSA;
02. SCOTT 사용자 소유 테이블 목록 확인(2가지 구문 활용)
SELECT *
FROM USER_TABLES;
SELECT NAME, BASICPAY
FROM TBL_INSA;
SELECT NAME, BASICPAY, SUDANG, BASICPAY + SUDANG
FROM TBL_INSA;
SELECT NAME 이름, CITY 출신도, BUSEO 부서명
FROM TBL_INSA;
SELECT NAME, CITY, BUSEO, JIKWI
FROM TBL_INSA
WHERE CITY = '서울';
SELECT NAME, CITY, BASICPAY, SSN
FROM TBL_INSA
WHERE CITY = '서울'
AND BASICPAY >= 1500000;
SELECT *
FROM TBL_INSA
WHERE CITY = '인천'
AND BASICPAY BETWEEN 1000000 AND 2000000;
SELECT NAME, CITY, BUSEO
FROM TBL_INSA
WHERE CITY = '서울'
OR BUSEO = '개발부';
SELECT NAME, CITY, BUSEO
FROM TBL_INSA
WHERE CITY IN ('서울', '경기');
SELECT *
FROM TBL_INSA
WHERE BUSEO IN ('개발부', '영업부');
SELECT NAME 이름, BASICPAY 기본급, SUDANG 수당, BASICPAY+SUDANG 급여
FROM TBL_INSA
WHERE BASICPAY+SUDANG >= 2500000;
SELECT NAME, SSN
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('1', '3');
SELECT NAME, SSN
FROM TBL_INSA
WHERE SUBSTR(SSN, 1, 1) = '8';
SELECT *
FROM TBL_INSA
WHERE SUBSTR(SSN, 1, 1) = '7'
AND CITY = '서울';
17. 서울 사람 중에서 70년대 태어난 남자만 조회. SUBSTR() 함수 이용.
SELECT *
FROM TBL_INSA
WHERE SUBSTR(SSN, 1, 1) = '7'
AND CITY = '서울'
AND SUBSTR(SSN, 8, 1) IN ('1', '3');
SELECT *
FROM TBL_INSA
WHERE CITY = '서울'
AND SUBSTR(NAME, 1, 1) = '김';
SELECT NAME 이름, CITY 출신도, IBSADATE 입사일
FROM TBL_INSA
WHERE SUBSTR(TO_CHAR(IBSADATE), 1,2) = '20';
20. 2000년 10월에 입사한 사람 조회. (이름, 출신도, 입사일).
SELECT NAME 이름, CITY 출신도, IBSADATE 입사일
FROM TBL_INSA
WHERE TO_CHAR(IBSADATE, 'YYYY-MM') = '2000-10';
SELECT NAME 이름, CITY 출신도
, TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - (TO_NUMBER(SUBSTR(SSN, 1,2)) + 1899) 나이
FROM TBL_INSA;
SELECT *
FROM TBL_INSA
WHERE TRUNC(((TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - (TO_NUMBER(SUBSTR(SSN, 1,2)) + 1899)) / 10)) = 2;
SELECT *
FROM TBL_INSA
WHERE SUBSTR(SSN, 4, 1) = '5';
SELECT *
FROM TBL_INSA
WHERE TO_CHAR(SSN) LIKE '___5%';
SELECT *
FROM TBL_INSA
ORDER BY CITY DESC, BASICPAY DESC;
SELECT *
FROM TBL_INSA
WHERE CITY = '서울'
ORDER BY BASICPAY + SUDANG DESC;
SELECT *
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('2', '4')
ORDER BY BUSEO, BASICPAY DESC;
SELECT *
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('1', '3')
ORDER BY SSN DESC;
SELECT *
FROM TBL_INSA
WHERE CITY = '서울'
ORDER BY IBSADATE;
SELECT *
FROM TBL_INSA
WHERE SUBSTR(NAME,1,1) != '김';
SELECT NAME 이름, CITY 출신도, SUBSTR(BUSEO, 1, 2) 부서명, TEL
FROM TBL_INSA
WHERE CITY IN ('서울', '부산', '대구')
AND (TEL LIKE ('%5%') OR TEL LIKE('%7%'));
SELECT NUM, NAME, SSN, IBSADATE, CITY
, NVL((SUBSTR(TEL,1,3) || SUBSTR(TEL,5,4) || SUBSTR(TEL, 10)), '전화번호없음')
FROM TBL_INSA;
SELECT NUM, NAME, SSN, IBSADATE, CITY
, NVL(REPLACE(TEL,'-'), '전화번호없음')
FROM TBL_INSA;
추가문제. (기본 문제 풀이가 모두 끝난 후 작성한다.)
HR계정의 EMPLOYEES 테이블에서 커미션 받는 사람의 수와
안받는 사람의 수를 조회한다.
출력형태
구분 인원수
커미션받는사원 XXX
커미션없는사원 XXX
모든사원 XXX
SELECT TRUNC(((BASICPAY + SUDANG) / 1000000), 0) * 100, COUNT(*)
FROM TBL_INSA
GROUP BY TRUNC(((BASICPAY + SUDANG) / 1000000), 0) * 100
ORDER BY TRUNC(((BASICPAY + SUDANG) / 1000000), 0) * 100;
SELECT SUBSTR(SSN, 1, 2), COUNT(*)
FROM TBL_INSA
GROUP BY SUBSTR(SSN, 1, 2)
ORDER BY SUBSTR(SSN, 1, 2);
SELECT NAME 이름, SSN 주민번호
FROM TBL_INSA
ORDER BY SUBSTR(SSN, 3, 2), SUBSTR(SSN, 1, 2) DESC;
SELECT *
FROM TBL_INSA
ORDER BY TO_CHAR(IBSADATE, 'MM'), TO_CHAR(IBSADATE, 'YYYY') DESC;
SELECT (SELECT COUNT(*)
FROM TBL_INSA) 전체인원수
, (SELECT COUNT(*)
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('1', '3')) 남자인원수
, (SELECT COUNT(*)
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('2', '4')) 여자인원수
FROM TBL_INSA
WHERE ROWNUM = 1;
SELECT CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '3') THEN '남자'
WHEN SUBSTR(SSN, 8, 1) IN ('2', '4') THEN '여자'
ELSE '전체인원수' END , COUNT(*)
FROM TBL_INSA
GROUP BY ROLLUP(SUBSTR(SSN, 8, 1))
ORDER BY COUNT(*) DESC;
38. 개발부, 영업부, 총무부 인원수 조회. COUNT(), DECODE() 함수 이용.
SELECT BUSEO, COUNT(*)
FROM TBL_INSA
WHERE BUSEO IN ('개발부', '영업부', '총무부')
GROUP BY BUSEO;
SELECT BUSEO, COUNT(*)
FROM TBL_INSA
WHERE BUSEO IN ('개발부', '영업부', '총무부')
GROUP BY BUSEO;
SELECT COUNT(*)
FROM TBL_INSA
WHERE CITY = '서울'
AND SUBSTR(SSN, 8, 1) IN ('1', '3');
SELECT DECODE(SUBSTR(SSN, 8, 1), '1', '남자', '2', '여자'), COUNT(*)
FROM TBL_INSA
WHERE BUSEO = '영업부'
GROUP BY SUBSTR(SSN, 8, 1);
SELECT BUSEO, COUNT(*)
FROM TBL_INSA
WHERE BUSEO IN ('개발부', '영업부', '총무부')
AND CITY = '서울'
GROUP BY BUSEO;
SELECT CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '3') THEN '남자'
ELSE '여자' END, SUM(BASICPAY)
FROM TBL_INSA
WHERE CITY = '서울'
GROUP BY SUBSTR(SSN, 8, 1);
SELECT DECODE(SUBSTR(SSN, 8, 1), '1', '남자', '2', '여자'), AVG(BASICPAY)
FROM TBL_INSA
WHERE BUSEO = '개발부'
GROUP BY SUBSTR(SSN, 8, 1);
SELECT CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '3') THEN '남자'
ELSE '여자' END, AVG(BASICPAY)
FROM TBL_INSA
WHERE BUSEO = '개발부'
GROUP BY SUBSTR(SSN, 8, 1);
SELECT BUSEO
, CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '3') THEN '남자'
WHEN SUBSTR(SSN, 8, 1) IN ('2', '4') THEN '여자'
END, COUNT(*)
FROM TBL_INSA
GROUP BY BUSEO, SUBSTR(SSN, 8, 1)
ORDER BY BUSEO, SUBSTR(SSN, 8, 1);
SELECT CITY
, CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '3') THEN '남자'
WHEN SUBSTR(SSN, 8, 1) IN ('2', '4') THEN '여자'
END, COUNT(*)
FROM TBL_INSA
GROUP BY CITY, SUBSTR(SSN, 8, 1)
ORDER BY CITY, SUBSTR(SSN, 8, 1);
SELECT TO_CHAR(IBSADATE,'YYYY')
, CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '3') THEN '남자'
WHEN SUBSTR(SSN, 8, 1) IN ('2', '4') THEN '여자'
END, COUNT(*)
FROM TBL_INSA
GROUP BY TO_CHAR(IBSADATE,'YYYY'), SUBSTR(SSN, 8, 1)
ORDER BY TO_CHAR(IBSADATE,'YYYY'), SUBSTR(SSN, 8, 1);
SELECT TO_CHAR(IBSADATE,'YYYY')
, CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '3') THEN '남자'
WHEN SUBSTR(SSN, 8, 1) IN ('2', '4') THEN '여자'
END, COUNT(*)
FROM TBL_INSA
WHERE BUSEO IN ('영업부', '총무부')
GROUP BY TO_CHAR(IBSADATE,'YYYY'), SUBSTR(SSN, 8, 1)
ORDER BY TO_CHAR(IBSADATE,'YYYY'), SUBSTR(SSN, 8, 1);
SELECT BUSEO
, CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '3') THEN '남자'
WHEN SUBSTR(SSN, 8, 1) IN ('2', '4') THEN '여자'
END, COUNT(*), SUM(BASICPAY)
FROM TBL_INSA
WHERE CITY = '서울'
GROUP BY BUSEO, SUBSTR(SSN, 8, 1)
ORDER BY BUSEO, SUBSTR(SSN, 8, 1);
SELECT E.부서, E.숫자
FROM(
SELECT BUSEO 부서, COUNT(*) 숫자
FROM TBL_INSA
GROUP BY BUSEO
) E
WHERE E.숫자 >= 10;
51. 부서별 남,여 인원수 출력. 여자인원수가 5명 이상인 부서만 조회.
SELECT F.BUSEO, M.숫자, F.숫자
FROM
(
SELECT BUSEO, COUNT(*) 숫자
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('1', '3')
GROUP BY BUSEO
) M JOIN
(
SELECT BUSEO,COUNT(*) 숫자
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('2', '4')
GROUP BY BUSEO
) F ON M.BUSEO = F.BUSEO;
SELECT NAME
, DECODE(SUBSTR(SSN, 8, 1), '1', '남자', '3', '남자', '2', '여자', '4', '여자')
, EXTRACT(YEAR FROM SYSDATE) - SUBSTR(SSN, 1, 2) - 1899
FROM TBL_INSA;
SELECT NAME, BASICPAY
FROM TBL_INSA
WHERE CITY = '서울'
AND BASICPAY >= 2000000;
월 인원수
1월 10명
2월 25명
SELECT TO_NUMBER(TO_CHAR(IBSADATE, 'MM')) || '월', TO_CHAR(COUNT(*)) || '명'
FROM TBL_INSA
GROUP BY TO_NUMBER(TO_CHAR(IBSADATE, 'MM'))
ORDER BY TO_NUMBER(TO_CHAR(IBSADATE, 'MM'));
SELECT NAME, 생년월일, 기본급, 수당
FROM TBL_INSA;
SELECT NAME 이름
, CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '2')
THEN TO_CHAR(TO_NUMBER(SUBSTR(SSN, 1, 2)) + 1900) || '-' || SUBSTR(SSN, 3, 2) || '-' || SUBSTR(SSN, 5, 2)
WHEN SUBSTR(SSN, 8, 1) IN ('3', '4')
THEN TO_CHAR(TO_NUMBER(SUBSTR(SSN, 1, 2)) + 2000) || '-' || SUBSTR(SSN, 3, 2) || '-' || SUBSTR(SSN, 5, 2)
END 생년월일
, '\' || SUBSTR(BASICPAY, 1, 1) || ',' || SUBSTR(BASICPAY, 2, 3) || ',' || SUBSTR(BASICPAY, 5, 3)
, SUDANG
FROM TBL_INSA;
/*
홍길동 1977-12-12 \2,610,000 200000
이순신 1980-10-07 \1,320,000 200000
이순애 1977-09-22 \2,550,000 160000
김정훈 1979-03-04 \1,954,200 170000
한석봉 1981-11-12 \1,420,000 160000
이기자 1978-05-05 \2,265,000 150000
장인철 1978-05-06 \1,250,000 150000
김영년 1982-10-11 \9,500,00 145000
나윤균 1981-08-10 \8,400,00 220400
김종서 1975-10-10 \2,540,000 130000
유관순 1980-10-10 \1,020,000 140000
정한국 1976-09-09 \8,800,00 114000
조미숙 1979-01-02 \1,601,000 103000
황진이 1981-07-07 \1,100,000 130000
이현숙 1980-06-06 \1,050,000 104000
이상헌 1978-10-10 \2,350,000 150000
엄용수 1982-05-07 \9,500,00 210000
이성길 1980-10-28 \8,800,00 123000
박문수 1978-07-10 \2,300,000 165000
유영희 1980-03-04 \8,800,00 140000
홍길남 1980-10-10 \8,750,00 120000
이영숙 1980-05-01 \1,960,000 180000
김인수 1973-12-11 \2,500,000 170000
김말자 1983-02-25 \1,900,000 170000
우재옥 1980-11-03 \1,100,000 160000
김숙남 1981-09-07 \1,050,000 150000
김영길 1980-12-16 \2,340,000 170000
이남신 1981-01-01 \8,920,00 110000
김말숙 1980-03-01 \9,200,00 124000
정정해 1979-02-10 \2,304,000 124000
지재환 1977-11-15 \2,450,000 160000
심심해 1981-02-06 \8,800,00 108000
김미나 1978-05-05 \1,020,000 104000
이정석 1982-05-05 \1,100,000 160000
정영희 1983-10-10 \1,050,000 140000
이재영 1970-11-26 \9,604,00 190000
최석규 1977-01-29 \2,350,000 187000
손인수 1979-10-09 \2,000,000 150000
고순정 1980-05-04 \2,010,000 160000
박세열 1979-05-09 \2,100,000 130000
문길수 1972-12-17 \2,300,000 150000
채정희 1981-07-09 \1,020,000 200000
양미옥 1983-05-04 \1,100,000 210000
지수환 1982-03-05 \1,060,000 220000
홍원신 1969-09-06 \9,600,00 152000
허경운 1976-01-05 \2,650,000 150000
산마루 1978-05-05 \2,100,000 112000
이기상 1979-06-04 \2,050,000 106000
이미성 1983-09-08 \1,300,000 130000
이미인 1981-04-03 \1,950,000 103000
권영미 1979-03-03 \2,260,000 104000
권옥경 1982-04-06 \1,020,000 105000
김싱식 1980-07-15 \9,600,00 108000
정상호 1981-07-05 \9,800,00 114000
정한나 1982-05-06 \1,000,000 104000
전용재 1980-06-05 \1,950,000 200000
이미경 1978-04-06 \2,520,000 160000
김신제 1980-07-09 \1,950,000 180000
임수봉 1981-08-09 \8,900,00 102000
김신애 1981-08-09 \9,000,00 102000
*/
--56. 이름, 출신도, 기본급을 조회하되 출신도 내림차순 출력(1차 정렬 기준).
-- 출신도가 같으면 기본급 오름차순 출력(2차 정렬 기준).
SELECT NAME, CITY, BASICPAY
FROM TBL_INSA
ORDER BY CITY DESC, BASICPAY;
/*
문길수 충남 2300000
이남신 제주 892000
장인철 제주 1250000
이성길 전북 880000
심심해 전북 880000
김싱식 전북 960000
홍원신 전북 960000
김정훈 전북 1954200
유영희 전남 880000
이영숙 전남 1960000
이기상 전남 2050000
엄용수 인천 950000
정영희 인천 1050000
황진이 인천 1100000
이미성 인천 1300000
전용재 인천 1950000
김신제 인천 1950000
이기자 인천 2265000
최석규 인천 2350000
이순애 인천 2550000
임수봉 서울 890000
김신애 서울 900000
김말숙 서울 920000
김영년 서울 950000
이재영 서울 960400
정한나 서울 1000000
김미나 서울 1020000
유관순 서울 1020000
지수환 서울 1060000
양미옥 서울 1100000
우재옥 서울 1100000
한석봉 서울 1420000
김말자 서울 1900000
산마루 서울 2100000
권영미 서울 2260000
박문수 서울 2300000
김영길 서울 2340000
지재환 서울 2450000
김인수 서울 2500000
홍길동 서울 2610000
손인수 부산 2000000
정정해 부산 2304000
김종서 부산 2540000
박세열 경북 2100000
허경운 경남 2650000
나윤균 경기 840000
홍길남 경기 875000
채정희 경기 1020000
권옥경 경기 1020000
김숙남 경기 1050000
이현숙 경기 1050000
이정석 경기 1100000
이순신 경기 1320000
조미숙 경기 1601000
이미인 경기 1950000
고순정 경기 2010000
이상헌 경기 2350000
이미경 경기 2520000
정한국 강원 880000
정상호 강원 980000
*/
--57. 전화번호가 NULL이 아닌것만 조회. (이름, 전화번호)
SELECT *
FROM TBL_INSA
WHERE TEL IS NOT NULL;
/*
1001 홍길동 771212-1022432 1998-10-11 서울 011-2356-4528 기획부 부장 2610000 200000
1002 이순신 801007-1544236 2000-11-29 경기 010-4758-6532 총무부 사원 1320000 200000
1003 이순애 770922-2312547 1999-02-25 인천 010-4231-1236 개발부 부장 2550000 160000
1004 김정훈 790304-1788896 2000-10-01 전북 019-5236-4221 영업부 대리 1954200 170000
1005 한석봉 811112-1566789 2004-08-13 서울 018-5211-3542 총무부 사원 1420000 160000
1006 이기자 780505-2978541 2002-02-11 인천 010-3214-5357 개발부 과장 2265000 150000
1007 장인철 780506-1625148 1998-03-16 제주 011-2345-2525 개발부 대리 1250000 150000
1008 김영년 821011-2362514 2002-04-30 서울 016-2222-4444 홍보부 사원 950000 145000
1009 나윤균 810810-1552147 2003-10-10 경기 019-1111-2222 인사부 사원 840000 220400
1010 김종서 751010-1122233 1997-08-08 부산 011-3214-5555 영업부 부장 2540000 130000
1011 유관순 801010-2987897 2000-07-07 서울 010-8888-4422 영업부 사원 1020000 140000
1012 정한국 760909-1333333 1999-10-16 강원 018-2222-4242 홍보부 사원 880000 114000
1013 조미숙 790102-2777777 1998-06-07 경기 019-6666-4444 홍보부 대리 1601000 103000
1014 황진이 810707-2574812 2002-02-15 인천 010-3214-5467 개발부 사원 1100000 130000
1015 이현숙 800606-2954687 1999-07-26 경기 016-2548-3365 총무부 사원 1050000 104000
1016 이상헌 781010-1666678 2001-11-29 경기 010-4526-1234 개발부 과장 2350000 150000
1017 엄용수 820507-1452365 2000-08-28 인천 010-3254-2542 개발부 사원 950000 210000
1018 이성길 801028-1849534 2004-08-08 전북 018-1333-3333 개발부 사원 880000 123000
1019 박문수 780710-1985632 1999-12-10 서울 017-4747-4848 인사부 과장 2300000 165000
1020 유영희 800304-2741258 2003-10-10 전남 011-9595-8585 자재부 사원 880000 140000
1021 홍길남 801010-1111111 2001-09-07 경기 011-9999-7575 개발부 사원 875000 120000
1022 이영숙 800501-2312456 2003-02-25 전남 017-5214-5282 기획부 대리 1960000 180000
1024 김말자 830225-2633334 1999-08-28 서울 011-5248-7789 기획부 대리 1900000 170000
1025 우재옥 801103-1654442 2000-10-01 서울 010-4563-2587 영업부 사원 1100000 160000
1026 김숙남 810907-2015457 2002-08-28 경기 010-2112-5225 영업부 사원 1050000 150000
1027 김영길 801216-1898752 2000-10-18 서울 019-8523-1478 총무부 과장 2340000 170000
1028 이남신 810101-1010101 2001-09-07 제주 016-1818-4848 인사부 사원 892000 110000
1029 김말숙 800301-2020202 2000-09-08 서울 016-3535-3636 총무부 사원 920000 124000
1030 정정해 790210-2101010 1999-10-17 부산 019-6564-6752 총무부 과장 2304000 124000
1031 지재환 771115-1687988 2001-01-21 서울 019-5552-7511 기획부 부장 2450000 160000
1032 심심해 810206-2222222 2000-05-05 전북 016-8888-7474 자재부 사원 880000 108000
1033 김미나 780505-2999999 1998-06-07 서울 011-2444-4444 영업부 사원 1020000 104000
1034 이정석 820505-1325468 2005-09-26 경기 011-3697-7412 기획부 사원 1100000 160000
1036 이재영 701126-2852147 2003-08-10 서울 011-9999-9999 자재부 사원 960400 190000
1037 최석규 770129-1456987 1998-10-15 인천 011-7777-7777 홍보부 과장 2350000 187000
1038 손인수 791009-2321456 1999-11-15 부산 010-6542-7412 영업부 대리 2000000 150000
1039 고순정 800504-2000032 2003-12-28 경기 010-2587-7895 영업부 대리 2010000 160000
1040 박세열 790509-1635214 2000-09-10 경북 016-4444-7777 인사부 대리 2100000 130000
1041 문길수 721217-1951357 2001-12-10 충남 016-4444-5555 자재부 과장 2300000 150000
1042 채정희 810709-2000054 2003-10-17 경기 011-5125-5511 개발부 사원 1020000 200000
1043 양미옥 830504-2471523 2003-09-24 서울 016-8548-6547 영업부 사원 1100000 210000
1044 지수환 820305-1475286 2004-01-21 서울 011-5555-7548 영업부 사원 1060000 220000
1045 홍원신 690906-1985214 2003-03-16 전북 011-7777-7777 영업부 사원 960000 152000
1046 허경운 760105-1458752 1999-05-04 경남 017-3333-3333 총무부 부장 2650000 150000
1047 산마루 780505-1234567 2001-07-15 서울 018-0505-0505 영업부 대리 2100000 112000
1049 이미성 830908-2456548 2000-04-07 인천 010-6654-8854 개발부 사원 1300000 130000
1050 이미인 810403-2828287 2003-06-07 경기 011-8585-5252 홍보부 대리 1950000 103000
1051 권영미 790303-2155554 2000-06-04 서울 011-5555-7548 영업부 과장 2260000 104000
1052 권옥경 820406-2000456 2000-10-10 경기 010-3644-5577 기획부 사원 1020000 105000
1053 김싱식 800715-1313131 1999-12-12 전북 011-7585-7474 자재부 사원 960000 108000
1054 정상호 810705-1212141 1999-10-16 강원 016-1919-4242 홍보부 사원 980000 114000
1055 정한나 820506-2425153 2004-06-07 서울 016-2424-4242 영업부 사원 1000000 104000
1056 전용재 800605-1456987 2004-08-13 인천 010-7549-8654 영업부 대리 1950000 200000
1057 이미경 780406-2003214 1998-02-11 경기 016-6542-7546 자재부 부장 2520000 160000
1058 김신제 800709-1321456 2003-08-08 인천 010-2415-5444 기획부 대리 1950000 180000
1059 임수봉 810809-2121244 2001-10-10 서울 011-4151-4154 개발부 사원 890000 102000
1060 김신애 810809-2111111 2001-10-10 서울 011-4151-4444 개발부 사원 900000 102000
*/
--58. 근무년수가 10년 이상인 사람 조회. (이름, 입사일)
SELECT NAME, IBSADATE
FROM TBL_INSA
WHERE IBSADATE + (INTERVAL'10'YEAR) <= SYSDATE;
/*
홍길동 1998-10-11
이순신 2000-11-29
이순애 1999-02-25
김정훈 2000-10-01
한석봉 2004-08-13
이기자 2002-02-11
장인철 1998-03-16
김영년 2002-04-30
나윤균 2003-10-10
김종서 1997-08-08
유관순 2000-07-07
정한국 1999-10-16
조미숙 1998-06-07
황진이 2002-02-15
이현숙 1999-07-26
이상헌 2001-11-29
엄용수 2000-08-28
이성길 2004-08-08
박문수 1999-12-10
유영희 2003-10-10
홍길남 2001-09-07
이영숙 2003-02-25
김인수 1995-02-23
김말자 1999-08-28
우재옥 2000-10-01
김숙남 2002-08-28
김영길 2000-10-18
이남신 2001-09-07
김말숙 2000-09-08
정정해 1999-10-17
지재환 2001-01-21
심심해 2000-05-05
김미나 1998-06-07
이정석 2005-09-26
정영희 2002-05-16
이재영 2003-08-10
최석규 1998-10-15
손인수 1999-11-15
고순정 2003-12-28
박세열 2000-09-10
문길수 2001-12-10
채정희 2003-10-17
양미옥 2003-09-24
지수환 2004-01-21
홍원신 2003-03-16
허경운 1999-05-04
산마루 2001-07-15
이기상 2001-06-07
이미성 2000-04-07
이미인 2003-06-07
권영미 2000-06-04
권옥경 2000-10-10
김싱식 1999-12-12
정상호 1999-10-16
정한나 2004-06-07
전용재 2004-08-13
이미경 1998-02-11
김신제 2003-08-08
임수봉 2001-10-10
김신애 2001-10-10
*/
--59. 주민번호를 기준으로 75~82년생 조회. (이름, 주민번호, 출신도).
SUBSTR() 함수, BEWTEEN AND 구문, TO_NUMBER() 함수 이용.
SELECT NAME, SSN, CITY
FROM TBL_INSA
WHERE TO_NUMBER(SUBSTR(SSN, 1, 2)) BETWEEN 75 AND 82;
/*
홍길동 771212-1022432 서울
이순신 801007-1544236 경기
이순애 770922-2312547 인천
김정훈 790304-1788896 전북
한석봉 811112-1566789 서울
이기자 780505-2978541 인천
장인철 780506-1625148 제주
김영년 821011-2362514 서울
나윤균 810810-1552147 경기
김종서 751010-1122233 부산
유관순 801010-2987897 서울
정한국 760909-1333333 강원
조미숙 790102-2777777 경기
황진이 810707-2574812 인천
이현숙 800606-2954687 경기
이상헌 781010-1666678 경기
엄용수 820507-1452365 인천
이성길 801028-1849534 전북
박문수 780710-1985632 서울
유영희 800304-2741258 전남
홍길남 801010-1111111 경기
이영숙 800501-2312456 전남
우재옥 801103-1654442 서울
김숙남 810907-2015457 경기
김영길 801216-1898752 서울
이남신 810101-1010101 제주
김말숙 800301-2020202 서울
정정해 790210-2101010 부산
지재환 771115-1687988 서울
심심해 810206-2222222 전북
김미나 780505-2999999 서울
이정석 820505-1325468 경기
최석규 770129-1456987 인천
손인수 791009-2321456 부산
고순정 800504-2000032 경기
박세열 790509-1635214 경북
채정희 810709-2000054 경기
지수환 820305-1475286 서울
허경운 760105-1458752 경남
산마루 780505-1234567 서울
이기상 790604-1415141 전남
이미인 810403-2828287 경기
권영미 790303-2155554 서울
권옥경 820406-2000456 경기
김싱식 800715-1313131 전북
정상호 810705-1212141 강원
정한나 820506-2425153 서울
전용재 800605-1456987 인천
이미경 780406-2003214 경기
김신제 800709-1321456 인천
임수봉 810809-2121244 서울
김신애 810809-2111111 서울
*/
--60. 근무년수가 5~10년인 사람 조회. (이름, 입사일)
SELECT NAME, IBSADATE
FROM TBL_INSA
WHERE SYSDATE - (INTERVAL'10'YEAR) <= IBSADATE
AND SYSDATE - (INTERVAL'5'YEAR) >= IBSADATE;
--== 조회결과없음
--61. 김씨, 이씨, 박씨만 조회 (이름, 부서). SUBSTR() 함수 이용.
SELECT NAME, BUSEO
FROM TBL_INSA
WHERE SUBSTR(NAME,1,1) IN ('김', '이', '박');
/*
이순신 총무부
이순애 개발부
김정훈 영업부
이기자 개발부
김영년 홍보부
김종서 영업부
이현숙 총무부
이상헌 개발부
이성길 개발부
박문수 인사부
이영숙 기획부
김인수 영업부
김말자 기획부
김숙남 영업부
김영길 총무부
이남신 인사부
김말숙 총무부
김미나 영업부
이정석 기획부
이재영 자재부
박세열 인사부
이기상 개발부
이미성 개발부
이미인 홍보부
김싱식 자재부
이미경 자재부
김신제 기획부
김신애 개발부
*/
--62. 입사일을 "년-월-일 요일" 형식으로 남자만 조회 (이름, 주민번호, 입사일)
SELECT NAME, SSN, TO_CHAR(IBSADATE, 'YYYY') || '년-' || TO_CHAR(IBSADATE, 'MM') || '월-'
|| TO_CHAR(IBSADATE, 'DD') || '일 ' || TO_CHAR(IBSADATE, 'DAY')
FROM TBL_INSA
WHERE SUBSTR(SSN,8,1) IN ('1', '3');
/*
홍길동 771212-1022432 1998년-10월-11일 일요일
이순신 801007-1544236 2000년-11월-29일 수요일
김정훈 790304-1788896 2000년-10월-01일 일요일
한석봉 811112-1566789 2004년-08월-13일 금요일
장인철 780506-1625148 1998년-03월-16일 월요일
나윤균 810810-1552147 2003년-10월-10일 금요일
김종서 751010-1122233 1997년-08월-08일 금요일
정한국 760909-1333333 1999년-10월-16일 토요일
이상헌 781010-1666678 2001년-11월-29일 목요일
엄용수 820507-1452365 2000년-08월-28일 월요일
이성길 801028-1849534 2004년-08월-08일 일요일
박문수 780710-1985632 1999년-12월-10일 금요일
홍길남 801010-1111111 2001년-09월-07일 금요일
김인수 731211-1214576 1995년-02월-23일 목요일
우재옥 801103-1654442 2000년-10월-01일 일요일
김영길 801216-1898752 2000년-10월-18일 수요일
이남신 810101-1010101 2001년-09월-07일 금요일
지재환 771115-1687988 2001년-01월-21일 일요일
이정석 820505-1325468 2005년-09월-26일 월요일
최석규 770129-1456987 1998년-10월-15일 목요일
박세열 790509-1635214 2000년-09월-10일 일요일
문길수 721217-1951357 2001년-12월-10일 월요일
지수환 820305-1475286 2004년-01월-21일 수요일
홍원신 690906-1985214 2003년-03월-16일 일요일
허경운 760105-1458752 1999년-05월-04일 화요일
산마루 780505-1234567 2001년-07월-15일 일요일
이기상 790604-1415141 2001년-06월-07일 목요일
김싱식 800715-1313131 1999년-12월-12일 일요일
정상호 810705-1212141 1999년-10월-16일 토요일
전용재 800605-1456987 2004년-08월-13일 금요일
김신제 800709-1321456 2003년-08월-08일 금요일
*/
63. 부서별 직위별 급여합 구하기. (부서, 직위, 급여합)
SELECT BUSEO, JIKWI, SUM(BASICPAY)
FROM TBL_INSA
GROUP BY BUSEO, JIKWI
ORDER BY BUSEO, JIKWI;
/*
개발부 과장 4615000
개발부 대리 3300000
개발부 부장 2550000
개발부 사원 8965000
기획부 대리 5810000
기획부 부장 5060000
기획부 사원 2120000
영업부 과장 2260000
영업부 대리 10014200
영업부 부장 5040000
영업부 사원 8310000
인사부 과장 2300000
인사부 대리 2100000
인사부 사원 1732000
자재부 과장 2300000
자재부 부장 2520000
자재부 사원 3680400
총무부 과장 4644000
총무부 부장 2650000
총무부 사원 4710000
홍보부 과장 2350000
홍보부 대리 3551000
홍보부 사원 2810000
*/
--64. 부서별 직위별 인원수, 급여합, 급여평균 구하기. (부서, 직위, 급여합)
SELECT BUSEO, JIKWI, COUNT(*), SUM(BASICPAY), TRUNC(AVG(BASICPAY))
FROM TBL_INSA
GROUP BY BUSEO, JIKWI
ORDER BY BUSEO, JIKWI;
/*
개발부 과장 2 4615000 2307500
개발부 대리 2 3300000 1650000
개발부 부장 1 2550000 2550000
개발부 사원 9 8965000 996111
기획부 대리 3 5810000 1936666
기획부 부장 2 5060000 2530000
기획부 사원 2 2120000 1060000
영업부 과장 1 2260000 2260000
영업부 대리 5 10014200 2002840
영업부 부장 2 5040000 2520000
영업부 사원 8 8310000 1038750
인사부 과장 1 2300000 2300000
인사부 대리 1 2100000 2100000
인사부 사원 2 1732000 866000
자재부 과장 1 2300000 2300000
자재부 부장 1 2520000 2520000
자재부 사원 4 3680400 920100
총무부 과장 2 4644000 2322000
총무부 부장 1 2650000 2650000
총무부 사원 4 4710000 1177500
홍보부 과장 1 2350000 2350000
홍보부 대리 2 3551000 1775500
홍보부 사원 3 2810000 936666
*/
--65. 부서별 직위별 인원수를 구하되 인원수가 5명 이상인 경우만 조회.
SELECT E.부서, E.직위, E.숫자
FROM(
SELECT BUSEO 부서, JIKWI 직위, COUNT(*) 숫자
FROM TBL_INSA
GROUP BY BUSEO, JIKWI
) E
WHERE E.숫자 >= 5;
/*
영업부 사원 8
영업부 대리 5
개발부 사원 9
*/
66. 2000년에 입사한 여사원 조회. (이름, 주민번호, 입사일)
SELECT NAME, SSN, IBSADATE
FROM TBL_INSA
WHERE TO_CHAR(IBSADATE, 'YYYY') = 2000
AND SUBSTR(SSN, 8, 1) IN ('2', '4');
/*
유관순 801010-2987897 2000-07-07
김말숙 800301-2020202 2000-09-08
심심해 810206-2222222 2000-05-05
이미성 830908-2456548 2000-04-07
권영미 790303-2155554 2000-06-04
권옥경 820406-2000456 2000-10-10
*/
--67. 성씨가 한 글자(김, 이, 박 등)라는 가정하에 성씨별 인원수 조회 (성씨, 인원수)
SELECT SUBSTR(NAME, 1, 1), COUNT(*)
FROM TBL_INSA
GROUP BY SUBSTR(NAME, 1, 1);
/*
이 14
김 12
정 5
우 1
최 1
손 1
채 1
장 1
임 1
지 2
고 1
전 1
조 1
문 1
산 1
황 1
박 2
심 1
허 1
홍 3
한 1
나 1
유 2
엄 1
양 1
권 2
*/
--68. 출신도(CITY)별 성별 인원수 조회.
SELECT CITY
, CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '3') THEN '남자'
WHEN SUBSTR(SSN, 8, 1) IN ('2', '4') THEN '여자'
END, COUNT(*)
FROM TBL_INSA
GROUP BY CITY, SUBSTR(SSN, 8, 1)
ORDER BY CITY, SUBSTR(SSN, 8, 1);
/*
강원 남자 2
경기 남자 5
경기 여자 8
경남 남자 1
경북 남자 1
부산 남자 1
부산 여자 2
서울 남자 9
서울 여자 11
인천 남자 4
인천 여자 5
전남 남자 1
전남 여자 2
전북 남자 4
전북 여자 1
제주 남자 2
충남 남자 1
*/
--69. 부서별 남자인원수가 5명 이상인 부서와 남자인원수 조회.
SELECT E.부서, E.숫자
FROM(
SELECT BUSEO 부서, COUNT(*) 숫자
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('1', '3')
GROUP BY BUSEO
) E
WHERE E.숫자 >= 5;
/*
영업부 8
개발부 6
*/
--70. 입사년도별 인원수 조회.
SELECT TO_CHAR(IBSADATE,'YYYY'), COUNT(*)
FROM TBL_INSA
GROUP BY TO_CHAR(IBSADATE,'YYYY')
ORDER BY TO_CHAR(IBSADATE,'YYYY');
/*
1995 1
1997 1
1998 6
1999 10
2000 12
2001 9
2002 5
2003 10
2004 5
2005 1
*/
71. 전체인원수, 2000년, 1999년, 1998년도에 입사한 인원을 다음의 형식으로 조회.
출력형태 ---------------
전체 2000 1999 1998
60 x x x
SELECT SUM(E.숫자),
CASE WHEN E.년도 = '2000' THEN TO_CHAR(E.숫자) AS 이천
END
FROM(
SELECT TO_CHAR(IBSADATE, 'YYYY') 년도, COUNT(*) 숫자
FROM TBL_INSA
WHERE TO_CHAR(IBSADATE, 'YYYY') IN ('2000', '1999', '1998')
GROUP BY TO_CHAR(IBSADATE, 'YYYY')
) E;
SELECT DECODE(E.년도, '2000', TO_CHAR(E.숫자)), DECODE(E.년도, '1999', TO_CHAR(E.숫자))
FROM(
SELECT TO_CHAR(IBSADATE, 'YYYY') 년도, COUNT(*) 숫자
FROM TBL_INSA
WHERE TO_CHAR(IBSADATE, 'YYYY') IN ('2000', '1999', '1998')
GROUP BY TO_CHAR(IBSADATE, 'YYYY')
) E;
SELECT (SELECT COUNT(*)
FROM TBL_INSA
WHERE TO_CHAR(IBSADATE, 'YYYY') = '2000')
+ (SELECT COUNT(*)
FROM TBL_INSA
WHERE TO_CHAR(IBSADATE, 'YYYY') = '1999')
+ (SELECT COUNT(*)
FROM TBL_INSA
WHERE TO_CHAR(IBSADATE, 'YYYY') = '1998') 전체
,(SELECT COUNT(*)
FROM TBL_INSA
WHERE TO_CHAR(IBSADATE, 'YYYY') = '2000') 이천
,(SELECT COUNT(*)
FROM TBL_INSA
WHERE TO_CHAR(IBSADATE, 'YYYY') = '1999') 일구구구
,(SELECT COUNT(*)
FROM TBL_INSA
WHERE TO_CHAR(IBSADATE, 'YYYY') = '1998') 일구구팔
FROM TBL_INSA
WHERE ROWNUM = 1;
72. 아래 형식으로 지역별 인원수 조회.
출력형태 -----------------
전체 서울 인천 경기
60 x x x
--73. 기본급(BASICPAY)이 평균 이하인 사원 조회. (이름, 기본급). AVG() 함수. 서브쿼리.
SELECT NAME, BASICPAY
FROM TBL_INSA
WHERE BASICPAY <= (SELECT TRUNC(AVG(BASICPAY))
FROM TBL_INSA)
ORDER BY BASICPAY;
/*
나윤균 840000
홍길남 875000
심심해 880000
이성길 880000
정한국 880000
유영희 880000
임수봉 890000
이남신 892000
김신애 900000
김말숙 920000
엄용수 950000
김영년 950000
김싱식 960000
홍원신 960000
이재영 960400
정상호 980000
정한나 1000000
채정희 1020000
권옥경 1020000
김미나 1020000
유관순 1020000
김숙남 1050000
이현숙 1050000
정영희 1050000
지수환 1060000
양미옥 1100000
이정석 1100000
우재옥 1100000
황진이 1100000
장인철 1250000
이미성 1300000
이순신 1320000
한석봉 1420000
*/
--74. 기본급 상위 10%만 조회. (이름, 기본급)
SELECT E.이름, E.기본급
FROM(
SELECT NAME 이름, BASICPAY 기본급, RANK() OVER(ORDER BY BASICPAY DESC) 순위
FROM TBL_INSA
) E
WHERE E.순위 <= (SELECT COUNT(*)
FROM TBL_INSA) / 10;
/*
허경운 2650000
홍길동 2610000
이순애 2550000
김종서 2540000
이미경 2520000
김인수 2500000
*/
75. 기본급 순위가 5순위까지만 조회. (모든 정보)
SELECT NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG
FROM(
SELECT NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG, RANK() OVER(ORDER BY BASICPAY DESC) 순위
FROM TBL_INSA
) E
WHERE E.순위 <= 5;
/*
1046 허경운 760105-1458752 1999-05-04 경남 017-3333-3333 총무부 부장 2650000 150000
1001 홍길동 771212-1022432 1998-10-11 서울 011-2356-4528 기획부 부장 2610000 200000
1003 이순애 770922-2312547 1999-02-25 인천 010-4231-1236 개발부 부장 2550000 160000
1010 김종서 751010-1122233 1997-08-08 부산 011-3214-5555 영업부 부장 2540000 130000
1057 이미경 780406-2003214 1998-02-11 경기 016-6542-7546 자재부 부장 2520000 160000
*/
76. 입사일이 빠른 순서로 5순위까지만 조회. (모든 정보)
SELECT NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG
FROM(
SELECT NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG, RANK() OVER(ORDER BY IBSADATE) 순위
FROM TBL_INSA
) E
WHERE E.순위 <= 5;
/*
1023 김인수 731211-1214576 1995-02-23 서울 영업부 부장 2500000 170000
1010 김종서 751010-1122233 1997-08-08 부산 011-3214-5555 영업부 부장 2540000 130000
1057 이미경 780406-2003214 1998-02-11 경기 016-6542-7546 자재부 부장 2520000 160000
1007 장인철 780506-1625148 1998-03-16 제주 011-2345-2525 개발부 대리 1250000 150000
1013 조미숙 790102-2777777 1998-06-07 경기 019-6666-4444 홍보부 대리 1601000 103000
1033 김미나 780505-2999999 1998-06-07 서울 011-2444-4444 영업부 사원 1020000 104000
*/
--77. 평균 급여보다 많은 급여를 받는 직원 정보 조회. (모든 정보)
SELECT *
FROM TBL_INSA
WHERE BASICPAY > (SELECT AVG(BASICPAY)
FROM TBL_INSA)
ORDER BY BASICPAY;
/*
1013 조미숙 790102-2777777 1998-06-07 경기 019-6666-4444 홍보부 대리 1601000 103000
1024 김말자 830225-2633334 1999-08-28 서울 011-5248-7789 기획부 대리 1900000 170000
1058 김신제 800709-1321456 2003-08-08 인천 010-2415-5444 기획부 대리 1950000 180000
1056 전용재 800605-1456987 2004-08-13 인천 010-7549-8654 영업부 대리 1950000 200000
1050 이미인 810403-2828287 2003-06-07 경기 011-8585-5252 홍보부 대리 1950000 103000
1004 김정훈 790304-1788896 2000-10-01 전북 019-5236-4221 영업부 대리 1954200 170000
1022 이영숙 800501-2312456 2003-02-25 전남 017-5214-5282 기획부 대리 1960000 180000
1038 손인수 791009-2321456 1999-11-15 부산 010-6542-7412 영업부 대리 2000000 150000
1039 고순정 800504-2000032 2003-12-28 경기 010-2587-7895 영업부 대리 2010000 160000
1048 이기상 790604-1415141 2001-06-07 전남 개발부 대리 2050000 106000
1040 박세열 790509-1635214 2000-09-10 경북 016-4444-7777 인사부 대리 2100000 130000
1047 산마루 780505-1234567 2001-07-15 서울 018-0505-0505 영업부 대리 2100000 112000
1051 권영미 790303-2155554 2000-06-04 서울 011-5555-7548 영업부 과장 2260000 104000
1006 이기자 780505-2978541 2002-02-11 인천 010-3214-5357 개발부 과장 2265000 150000
1019 박문수 780710-1985632 1999-12-10 서울 017-4747-4848 인사부 과장 2300000 165000
1041 문길수 721217-1951357 2001-12-10 충남 016-4444-5555 자재부 과장 2300000 150000
1030 정정해 790210-2101010 1999-10-17 부산 019-6564-6752 총무부 과장 2304000 124000
1027 김영길 801216-1898752 2000-10-18 서울 019-8523-1478 총무부 과장 2340000 170000
1037 최석규 770129-1456987 1998-10-15 인천 011-7777-7777 홍보부 과장 2350000 187000
1016 이상헌 781010-1666678 2001-11-29 경기 010-4526-1234 개발부 과장 2350000 150000
1031 지재환 771115-1687988 2001-01-21 서울 019-5552-7511 기획부 부장 2450000 160000
1023 김인수 731211-1214576 1995-02-23 서울 영업부 부장 2500000 170000
1057 이미경 780406-2003214 1998-02-11 경기 016-6542-7546 자재부 부장 2520000 160000
1010 김종서 751010-1122233 1997-08-08 부산 011-3214-5555 영업부 부장 2540000 130000
1003 이순애 770922-2312547 1999-02-25 인천 010-4231-1236 개발부 부장 2550000 160000
1001 홍길동 771212-1022432 1998-10-11 서울 011-2356-4528 기획부 부장 2610000 200000
1046 허경운 760105-1458752 1999-05-04 경남 017-3333-3333 총무부 부장 2650000 150000
*/
--78. '이순애' 직원의 급여보다 더 많은 급여를 받는 직원 조회. (모든 정보)
-- 단, 이순애 직원의 급여가 변하더라도 작성된 쿼리문은 기능 수행이 가능하도록 조회.
SELECT *
FROM TBL_INSA
WHERE BASICPAY > (SELECT BASICPAY
FROM TBL_INSA
WHERE NAME = '이순애');
/*
1001 홍길동 771212-1022432 1998-10-11 서울 011-2356-4528 기획부 부장 2610000 200000
1046 허경운 760105-1458752 1999-05-04 경남 017-3333-3333 총무부 부장 2650000 150000
*/
--79. 총무부의 평균 급여보다 많은 급여를 받는 직원들의 이름, 부서명 조회.
SELECT NAME, BUSEO
FROM TBL_INSA
WHERE BASICPAY > (SELECT AVG(BASICPAY)
FROM TBL_INSA
WHERE BUSEO = '총무부')
ORDER BY BUSEO;
/*
이상헌 개발부
이순애 개발부
이기상 개발부
이기자 개발부
홍길동 기획부
지재환 기획부
김신제 기획부
김말자 기획부
이영숙 기획부
산마루 영업부
김인수 영업부
김종서 영업부
권영미 영업부
전용재 영업부
김정훈 영업부
손인수 영업부
고순정 영업부
박세열 인사부
박문수 인사부
이미경 자재부
문길수 자재부
정정해 총무부
김영길 총무부
허경운 총무부
이미인 홍보부
최석규 홍보부
*/
--80. 총무부 직원들의 평균 수당보다 더 많은 수당을 받는 직원 정보 조회.
SELECT *
FROM TBL_INSA
WHERE SUDANG > (SELECT AVG(SUDANG)
FROM TBL_INSA
WHERE BUSEO = '총무부');
/*
1001 홍길동 771212-1022432 1998-10-11 서울 011-2356-4528 기획부 부장 2610000 200000
1002 이순신 801007-1544236 2000-11-29 경기 010-4758-6532 총무부 사원 1320000 200000
1003 이순애 770922-2312547 1999-02-25 인천 010-4231-1236 개발부 부장 2550000 160000
1004 김정훈 790304-1788896 2000-10-01 전북 019-5236-4221 영업부 대리 1954200 170000
1005 한석봉 811112-1566789 2004-08-13 서울 018-5211-3542 총무부 사원 1420000 160000
1006 이기자 780505-2978541 2002-02-11 인천 010-3214-5357 개발부 과장 2265000 150000
1007 장인철 780506-1625148 1998-03-16 제주 011-2345-2525 개발부 대리 1250000 150000
1009 나윤균 810810-1552147 2003-10-10 경기 019-1111-2222 인사부 사원 840000 220400
1016 이상헌 781010-1666678 2001-11-29 경기 010-4526-1234 개발부 과장 2350000 150000
1017 엄용수 820507-1452365 2000-08-28 인천 010-3254-2542 개발부 사원 950000 210000
1019 박문수 780710-1985632 1999-12-10 서울 017-4747-4848 인사부 과장 2300000 165000
1022 이영숙 800501-2312456 2003-02-25 전남 017-5214-5282 기획부 대리 1960000 180000
1023 김인수 731211-1214576 1995-02-23 서울 영업부 부장 2500000 170000
1024 김말자 830225-2633334 1999-08-28 서울 011-5248-7789 기획부 대리 1900000 170000
1025 우재옥 801103-1654442 2000-10-01 서울 010-4563-2587 영업부 사원 1100000 160000
1026 김숙남 810907-2015457 2002-08-28 경기 010-2112-5225 영업부 사원 1050000 150000
1027 김영길 801216-1898752 2000-10-18 서울 019-8523-1478 총무부 과장 2340000 170000
1031 지재환 771115-1687988 2001-01-21 서울 019-5552-7511 기획부 부장 2450000 160000
1034 이정석 820505-1325468 2005-09-26 경기 011-3697-7412 기획부 사원 1100000 160000
1036 이재영 701126-2852147 2003-08-10 서울 011-9999-9999 자재부 사원 960400 190000
1037 최석규 770129-1456987 1998-10-15 인천 011-7777-7777 홍보부 과장 2350000 187000
1038 손인수 791009-2321456 1999-11-15 부산 010-6542-7412 영업부 대리 2000000 150000
1039 고순정 800504-2000032 2003-12-28 경기 010-2587-7895 영업부 대리 2010000 160000
1041 문길수 721217-1951357 2001-12-10 충남 016-4444-5555 자재부 과장 2300000 150000
1042 채정희 810709-2000054 2003-10-17 경기 011-5125-5511 개발부 사원 1020000 200000
1043 양미옥 830504-2471523 2003-09-24 서울 016-8548-6547 영업부 사원 1100000 210000
1044 지수환 820305-1475286 2004-01-21 서울 011-5555-7548 영업부 사원 1060000 220000
1045 홍원신 690906-1985214 2003-03-16 전북 011-7777-7777 영업부 사원 960000 152000
1046 허경운 760105-1458752 1999-05-04 경남 017-3333-3333 총무부 부장 2650000 150000
1056 전용재 800605-1456987 2004-08-13 인천 010-7549-8654 영업부 대리 1950000 200000
1057 이미경 780406-2003214 1998-02-11 경기 016-6542-7546 자재부 부장 2520000 160000
1058 김신제 800709-1321456 2003-08-08 인천 010-2415-5444 기획부 대리 1950000 180000
*/
--81. 직원 전체 평균 급여보다 많은 급여를 받는 직원의 수 조회.
SELECT COUNT(*)
FROM TBL_INSA
WHERE BASICPAY > (SELECT AVG(BASICPAY)
FROM TBL_INSA);
-- 27
--82. '홍길동' 직원과 같은 부서의 직원 정보 조회.
단, 홍길동 직원의 부서가 바뀌더라도 작성된 쿼리문은 기능 수행이 가능하도록 조회.
SELECT *
FROM TBL_INSA
WHERE BUSEO = (SELECT BUSEO
FROM TBL_INSA
WHERE NAME = '홍길동');
/*
1001 홍길동 771212-1022432 1998-10-11 서울 011-2356-4528 기획부 부장 2610000 200000
1022 이영숙 800501-2312456 2003-02-25 전남 017-5214-5282 기획부 대리 1960000 180000
1024 김말자 830225-2633334 1999-08-28 서울 011-5248-7789 기획부 대리 1900000 170000
1031 지재환 771115-1687988 2001-01-21 서울 019-5552-7511 기획부 부장 2450000 160000
1034 이정석 820505-1325468 2005-09-26 경기 011-3697-7412 기획부 사원 1100000 160000
1052 권옥경 820406-2000456 2000-10-10 경기 010-3644-5577 기획부 사원 1020000 105000
1058 김신제 800709-1321456 2003-08-08 인천 010-2415-5444 기획부 대리 1950000 180000
*/
--83. '김신애' 직원과 같은 부서, 직위를 가진 직원 정보 조회.
단, 김신애 직원의 부서 및 직위가 바뀌더라도 작성된 쿼리문은 기능 수행이 가능하도록 조회.
SELECT *
FROM TBL_INSA
WHERE BUSEO = (SELECT BUSEO
FROM TBL_INSA
WHERE NAME = '홍길동')
AND JIKWI = (SELECT JIKWI
FROM TBL_INSA
WHERE NAME = '홍길동');
/*
1001 홍길동 771212-1022432 1998-10-11 서울 011-2356-4528 기획부 부장 2610000 200000
1031 지재환 771115-1687988 2001-01-21 서울 019-5552-7511 기획부 부장 2450000 160000
*/
--84. 부서별 기본급이 가장 높은 사람 조회. (이름, 부서, 기본급)
단, 사원들의 기본급이 변경되더라도 작성된 쿼리문은 기능 수행이 가능하도록 조회.
SELECT NAME, BUSEO, BASICPAY
FROM TBL_INSA
WHERE (BUSEO, BASICPAY) IN (SELECT BUSEO,MAX(BASICPAY)
FROM TBL_INSA
GROUP BY BUSEO);
/*
허경운 총무부 2650000
이순애 개발부 2550000
김종서 영업부 2540000
홍길동 기획부 2610000
박문수 인사부 2300000
이미경 자재부 2520000
최석규 홍보부 2350000
*/
--85. 남, 여별 기본급 순위 조회.
SELECT SUBSTR(SSN, 8, 1), RANK() OVER(ORDER BY BASICPAY DESC) 남녀별 순위
FROM TBL_INSA
GROUP BY SUBSTR(SSN, 8, 1);
(SELECT NAME 남자이름, RANK() OVER(ORDER BY BASICPAY DESC) 남자순위
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('1', '3'));
(SELECT NAME 여자이름, RANK() OVER(ORDER BY BASICPAY DESC) 여자순위
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('2', '4'));
SELECT *
FROM
((SELECT NAME 남자이름, RANK() OVER(ORDER BY BASICPAY DESC) 남자순위
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('1', '3'))) A
UNOIN ALL
((SELECT NAME 여자이름, RANK() OVER(ORDER BY BASICPAY DESC) 여자순위
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('2', '4'))) B;
--86. 지역(CITY)별로 급여(기본급+수당) 1순위 직원만 조회.
SELECT *
FROM TBL_INSA
WHERE (CITY, BASICPAY + SUDANG) IN (SELECT CITY, MAX(BASICPAY + SUDANG)
FROM TBL_INSA
GROUP BY CITY);
/*
1001 홍길동 771212-1022432 1998-10-11 서울 011-2356-4528 기획부 부장 2610000 200000
1003 이순애 770922-2312547 1999-02-25 인천 010-4231-1236 개발부 부장 2550000 160000
1004 김정훈 790304-1788896 2000-10-01 전북 019-5236-4221 영업부 대리 1954200 170000
1007 장인철 780506-1625148 1998-03-16 제주 011-2345-2525 개발부 대리 1250000 150000
1010 김종서 751010-1122233 1997-08-08 부산 011-3214-5555 영업부 부장 2540000 130000
1040 박세열 790509-1635214 2000-09-10 경북 016-4444-7777 인사부 대리 2100000 130000
1041 문길수 721217-1951357 2001-12-10 충남 016-4444-5555 자재부 과장 2300000 150000
1046 허경운 760105-1458752 1999-05-04 경남 017-3333-3333 총무부 부장 2650000 150000
1048 이기상 790604-1415141 2001-06-07 전남 개발부 대리 2050000 106000
1054 정상호 810705-1212141 1999-10-16 강원 016-1919-4242 홍보부 사원 980000 114000
1057 이미경 780406-2003214 1998-02-11 경기 016-6542-7546 자재부 부장 2520000 160000
*/
--87. 부서별 인원수가 가장 많은 부서 및 인원수 조회.
SELECT BUSEO, COUNT(*)
FROM TBL_INSA
GROUP BY BUSEO
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM TBL_INSA
GROUP BY BUSEO);
--== 영업부 16
--88. 지역(CITY)별 인원수가 가장 많은 지역 및 인원수 조회.
SELECT CITY, COUNT(*)
FROM TBL_INSA
GROUP BY CITY
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM TBL_INSA
GROUP BY CITY);
--== 서울 20
--89. 지역(CITY)별 평균 급여(BASICPAY + SUDANG)가
-- 가장 높은 지역 및 평균급여 조회.
SELECT CITY, AVG(BASICPAY + SUDANG)
FROM TBL_INSA
GROUP BY CITY
HAVING AVG(BASICPAY + SUDANG) = (SELECT MAX(AVG(BASICPAY + SUDANG))
FROM TBL_INSA
GROUP BY CITY);
--== 경남 2800000
--90. 여자 인원수가 가장 많은 부서 및 인원수 조회.
SELECT BUSEO, COUNT(*)
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('2', '4')
GROUP BY BUSEO
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('2', '4')
GROUP BY BUSEO);
/*
개발부 8
영업부 8
*/
--91. 지역별 인원수 순위 조회.
SELECT CITY, RANK() OVER(ORDER BY COUNT(*) DESC)
FROM TBL_INSA
GROUP BY CITY;
/*
서울 1
경기 2
인천 3
전북 4
전남 5
부산 5
제주 7
강원 7
경남 9
경북 9
충남 9
*/
--92. 지역별 인원수 순위 조회하되 5순위까지만 출력.
SELECT A.도시, A.순위
FROM(
SELECT CITY 도시, RANK() OVER(ORDER BY COUNT(*) DESC) 순위
FROM TBL_INSA
GROUP BY CITY) A
WHERE A.순위 <= 5;
/*
서울 1
경기 2
인천 3
전북 4
부산 5
전남 5
*/
--93. 이름, 부서, 출신도, 기본급, 수당, 기본급+수당, 세금, 실수령액 조회
-- 단, 세금: 총급여가 250만원 이상이면 2%, 200만원 이상이면 1%, 나머지 0.
-- 실수령액: 총급여-세금
SELECT E.* , E.총급여 - E.세금
FROM(
SELECT NAME, BUSEO, CITY, BASICPAY, SUDANG, BASICPAY + SUDANG 총급여,
CASE WHEN BASICPAY + SUDANG >= 2500000 THEN (BASICPAY + SUDANG) * 0.02
WHEN BASICPAY + SUDANG >= 2000000 THEN (BASICPAY + SUDANG) * 0.01
ELSE 0 END AS 세금
FROM TBL_INSA
) E;
/*
홍길동 기획부 서울 2610000 200000 2810000 56200 2753800
이순신 총무부 경기 1320000 200000 1520000 0 1520000
이순애 개발부 인천 2550000 160000 2710000 54200 2655800
김정훈 영업부 전북 1954200 170000 2124200 21242 2102958
한석봉 총무부 서울 1420000 160000 1580000 0 1580000
이기자 개발부 인천 2265000 150000 2415000 24150 2390850
장인철 개발부 제주 1250000 150000 1400000 0 1400000
김영년 홍보부 서울 950000 145000 1095000 0 1095000
나윤균 인사부 경기 840000 220400 1060400 0 1060400
김종서 영업부 부산 2540000 130000 2670000 53400 2616600
유관순 영업부 서울 1020000 140000 1160000 0 1160000
정한국 홍보부 강원 880000 114000 994000 0 994000
조미숙 홍보부 경기 1601000 103000 1704000 0 1704000
황진이 개발부 인천 1100000 130000 1230000 0 1230000
이현숙 총무부 경기 1050000 104000 1154000 0 1154000
이상헌 개발부 경기 2350000 150000 2500000 50000 2450000
엄용수 개발부 인천 950000 210000 1160000 0 1160000
이성길 개발부 전북 880000 123000 1003000 0 1003000
박문수 인사부 서울 2300000 165000 2465000 24650 2440350
유영희 자재부 전남 880000 140000 1020000 0 1020000
홍길남 개발부 경기 875000 120000 995000 0 995000
이영숙 기획부 전남 1960000 180000 2140000 21400 2118600
김인수 영업부 서울 2500000 170000 2670000 53400 2616600
김말자 기획부 서울 1900000 170000 2070000 20700 2049300
우재옥 영업부 서울 1100000 160000 1260000 0 1260000
김숙남 영업부 경기 1050000 150000 1200000 0 1200000
김영길 총무부 서울 2340000 170000 2510000 50200 2459800
이남신 인사부 제주 892000 110000 1002000 0 1002000
김말숙 총무부 서울 920000 124000 1044000 0 1044000
정정해 총무부 부산 2304000 124000 2428000 24280 2403720
지재환 기획부 서울 2450000 160000 2610000 52200 2557800
심심해 자재부 전북 880000 108000 988000 0 988000
김미나 영업부 서울 1020000 104000 1124000 0 1124000
이정석 기획부 경기 1100000 160000 1260000 0 1260000
정영희 개발부 인천 1050000 140000 1190000 0 1190000
이재영 자재부 서울 960400 190000 1150400 0 1150400
최석규 홍보부 인천 2350000 187000 2537000 50740 2486260
손인수 영업부 부산 2000000 150000 2150000 21500 2128500
고순정 영업부 경기 2010000 160000 2170000 21700 2148300
박세열 인사부 경북 2100000 130000 2230000 22300 2207700
문길수 자재부 충남 2300000 150000 2450000 24500 2425500
채정희 개발부 경기 1020000 200000 1220000 0 1220000
양미옥 영업부 서울 1100000 210000 1310000 0 1310000
지수환 영업부 서울 1060000 220000 1280000 0 1280000
홍원신 영업부 전북 960000 152000 1112000 0 1112000
허경운 총무부 경남 2650000 150000 2800000 56000 2744000
산마루 영업부 서울 2100000 112000 2212000 22120 2189880
이기상 개발부 전남 2050000 106000 2156000 21560 2134440
이미성 개발부 인천 1300000 130000 1430000 0 1430000
이미인 홍보부 경기 1950000 103000 2053000 20530 2032470
권영미 영업부 서울 2260000 104000 2364000 23640 2340360
권옥경 기획부 경기 1020000 105000 1125000 0 1125000
김싱식 자재부 전북 960000 108000 1068000 0 1068000
정상호 홍보부 강원 980000 114000 1094000 0 1094000
정한나 영업부 서울 1000000 104000 1104000 0 1104000
전용재 영업부 인천 1950000 200000 2150000 21500 2128500
이미경 자재부 경기 2520000 160000 2680000 53600 2626400
김신제 기획부 인천 1950000 180000 2130000 21300 2108700
임수봉 개발부 서울 890000 102000 992000 0 992000
김신애 개발부 서울 900000 102000 1002000 0 1002000
*/
--94. 부서별 평균 급여를 조회하되, A, B, C 등급으로 나눠서 출력.
200만원 초과 - A등급
150~200만원 - B등급
150만원 미만 - C등급
SELECT BUSEO, CASE WHEN AVG(BASICPAY) < 1500000 THEN 'C등급'
WHEN AVG(BASICPAY) < 2000000 THEN 'B등급'
ELSE 'C등급' END
FROM TBL_INSA
GROUP BY BUSEO;
MAX() 함수, 하위 쿼리 이용.
SELECT NAME, BASICPAY + SUDANG
FROM TBL_INSA
WHERE BASICPAY + SUDANG = (SELECT MAX(BASICPAY + SUDANG)
FROM TBL_INSA);
