Everything has an expiration date
[Oracle] 20231027 [프로그램소스] - 20231027_01_SCOTT, TBL_INSA(중간 수행평가) 본문
[Oracle]/SQL (Program source)
[Oracle] 20231027 [프로그램소스] - 20231027_01_SCOTT, TBL_INSA(중간 수행평가)
Jelly-fish 2023. 10. 27. 17:53
20231027_01_SCOTT.sql
SELECT USER
FROM DUAL;
--==>> SCOTT
--○ TBL_EMP 테이블에서 수당(커미션, COMM) 이 가장 많은 사원의
-- 사원번호, 사원명, 부서번호, 직종명, 커미션 항목을 조회한다.
-- 중첩서브쿼리의 MAX(COMM) 을 이용한 풀이
SELECT EMPNO "사원번호", ENAME "사원명", DEPTNO "부서번호", JOB "직종명", COMM "커미션"
FROM TBL_EMP
WHERE COMM
IN
(
SELECT MAX(COMM)
FROM TBL_EMP
);
--==>> 7654 MARTIN 30 SALESMAN 1400
-- ALL() 을 이용한 풀이
SELECT EMPNO "사원번호", ENAME "사원명", DEPTNO "부서번호", JOB "직종명", COMM "커미션"
FROM TBL_EMP
WHERE COMM >= ALL(SELECT COMM
FROM TBL_EMP);
--==>> 조회결과 없음
SELECT EMPNO "사원번호", ENAME "사원명", DEPTNO "부서번호", JOB "직종명", COMM "커미션"
FROM TBL_EMP
WHERE COMM >= ALL(SELECT NVL(COMM, 0)
FROM TBL_EMP);
--==>> 7654 MARTIN 30 SALESMAN 1400
-- NVL() NULL 처리 함수를 사용하지 않으면 결과가 조회되지 않는 것을 확인할 수 있다.
-- 컬럼 안에 NULL 이 하나라도 포함되어 있으면 제대로 ALL() 이 기능하지 않는 것 같다.
-- 서브쿼리 내부에서 NULL 만 처리해 준다면 제대로 동작함을 확인할 수 있다.
SELECT EMPNO "사원번호", ENAME "사원명", DEPTNO "부서번호", JOB "직종명", COMM "커미션"
FROM TBL_EMP
WHERE COMM >= ALL(SELECT COMM
FROM TBL_EMP
WHERE COMM IS NOT NULL);
--==>> 7654 MARTIN 30 SALESMAN 1400
SELECT 사원번호, 사원명, 부서번호, 직종명, 커미션
FROM TBL_EMP
WHERE 수당(커미션, COMM)이 가장 많은 사원;
SELECT EMPNO, ENAME, DEPTNO, JOB, COMM
FROM TBL_EMP
WHERE COMM 이 가장 많은 사원;
-- 모든 직원들 중 최고 커미션
SELECT MAX(COMM)
FROM TBL_EMP;
--==>> 1400
-- 집계 함수 MAX() 를 사용함으로써 NULL 값이 자동적으로 제외되었음을 알 수 있다.
-- 집계 함수는 NULL 을 자동으로 제외시킨다. (COUNT, SUM, AVG, ...)
SELECT EMPNO, ENAME, DEPTNO, JOB, COMM
FROM TBL_EMP
WHERE COMM = (모든 직원들 중 최고 커미션);
SELECT EMPNO, ENAME, DEPTNO, JOB, COMM
FROM TBL_EMP
WHERE COMM = (SELECT MAX(COMM)
FROM TBL_EMP);
-- MAX() 를 쓰지 않는 풀이
SELECT EMPNO, ENAME, DEPTNO, JOB, COMM
FROM TBL_EMP
WHERE COMM >= ALL (SELECT COMM
FROM TBL_EMP);
--==>> 조회 결과 없음
-- 구문이 잘못된 것은 아님...
-- 그런데, MAX()함수를 쓰면 자연스럽게 NULL 을 연산 대상에서 제외시키는데 반해
-- 위와 같이 연산하면 서브쿼리의 COMM 안에 NULL이 들어있다.
/*
[COMM]
(null)
300
500
(null)
1400
(null)
(null)
(null)
(null)
0
(null)
(null)
(null)
(null)
10
10
(null)
(null)
(null)
*/
SELECT EMPNO, ENAME, DEPTNO, JOB, COMM
FROM TBL_EMP
WHERE COMM >= ALL (NULL, 300, 500, NULL, 1400, NULL, NULL, NULL, NULL, 0);
--==>> 조회 결과 없음.
-- NVL() 함수를 사용하여 NULL 을 0으로 변환하자 제대로 조회된다.
SELECT EMPNO, ENAME, DEPTNO, JOB, COMM
FROM TBL_EMP
WHERE COMM >= ALL (SELECT NVL(COMM, 0)
FROM TBL_EMP);
--==>> 7654 MARTIN 30 SALESMAN 1400
SELECT EMPNO, ENAME, DEPTNO, JOB, COMM
FROM TBL_EMP
WHERE COMM >= ALL (SELECT COMM
FROM TBL_EMP
WHERE COMM IS NOT NULL);
--> COMM이 NULL 이 아닌 집합만을 서브쿼리의 결과 값으로 적용하기 때문에 제대로 조회된다.
-- NULL 이 제거되면 다음과 같은 형태일 것이다.
SELECT EMPNO, ENAME, DEPTNO, JOB, COMM
FROM TBL_EMP
WHERE COMM >= ALL (300, 500, 1400, 0, 10, 10);
--==>> 7654 MARTIN 30 SALESMAN 1400
SELECT *
FROM TBL_EMP;
--○ DISTINCT() 중복 행(레코드)을 제거하는 함수
-- TBL_EMP 테이블에서 관리자로 등록된 사원의
-- 사원번호, 사원명, 직종명을 조회한다.
-- # 관리자로 등록된 사원의 정보 출력하는 쿼리문
SELECT EMPNO, ENAME, JOB
FROM TBL_EMP
WHERE EMPNO
IN
(
SELECT MGR
FROM TBL_EMP
)
ORDER BY EMPNO ASC;
--==>>
/*
7566 JONES MANAGER
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7902 FORD ANALYST
*/
-- # 관리자 사원번호 추출하는 쿼리문
SELECT MGR
FROM TBL_EMP
WHERE MGR IS NOT NULL
GROUP BY MGR
ORDER BY MGR ASC;
--==>>
/*
7566
7698
7782
7788
7839
7902
*/
SELECT EMPNO, ENAME, JOB
FROM TBL_EMP
WHERE 관리자로 등록된 사원;
SELECT EMPNO, ENAME, JOB
FROM TBL_EMP
WHERE 사원번호 = MGR로 등록된 사원;
SELECT MGR
FROM TBL_EMP;
/*
[MGR]
7902
7698
7698
7839
7698
7839
7839
7566
(null)
7698
7788
7698
7566
7782
7566
7566
7698
7698
7698
*/
SELECT EMPNO, ENAME, JOB
FROM TBL_EMP
WHERE EMPNO IN (7902, 7698, 7698, 7839, 7698, 7839, 7566, NULL, 7698, 7788, 7698, 7566, 7782, 7566, 7566, 7698, 7698, 7698);
--==>>
/*
7566 JONES MANAGER
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7902 FORD ANALYST
*/
SELECT EMPNO, ENAME, JOB
FROM TBL_EMP
WHERE EMPNO IN (SELECT MGR
FROM TBL_EMP);
-- MGR 번호에 중복이 너무 많다!
-- 그걸 직접 제거해 보자.
SELECT EMPNO, ENAME, JOB
FROM TBL_EMP
WHERE EMPNO IN (7902, 7698, 7839, 7566, 7788, 7782);
SELECT DISTINCT(MGR)
FROM TBL_EMP;
--==>>
/*
7839
(null)
7782
7698
7902
7566
7788
*/
-- 서브쿼리 내부의 매니저 번호 중복을 제거해 주게 되면
-- 훨씬 적은 횟수를 비교할 수 있겠다!
SELECT EMPNO, ENAME, JOB
FROM TBL_EMP
WHERE EMPNO IN (SELECT DISTINCT MGR
FROM TBL_EMP);
-- 직종들의 종류를 얻을 때도, 중복을 제거하여 종류만 얻어낼 수 있겠다!
SELECT DISTINCT(JOB)
FROM TBL_EMP;
--==>>
/*
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
*/
-- 부서번호의 종류를 얻을 때도 중복을 제거해 보자!!
SELECT DISTINCT(DEPTNO)
FROM TBL_EMP;
--==>>
/*
30
(null)
20
10
*/
--------------------------------------------------------------------------------
SELECT *
FROM TBL_SAWON;
--==>>
/*
1001 강혜성 9710171234567 2005-01-03 3000
1002 박가영 9511182234567 1999-11-23 4000
1003 박나영 9902082234567 2006-08-10 4000
1004 최혜인 9708112234567 2010-05-06 5000
1005 아이유 0502034234567 2015-10-19 1000
1006 이하이 0609304234567 2012-06-17 1000
1007 인순이 6510102234567 1999-08-22 2000
1008 선동열 6909101234567 1998-01-10 2000
1009 이이경 0505053234567 2011-05-06 1500
1010 선우용녀 6611112234567 2000-01-16 1300
1011 이윤수 9501061234567 2009-09-19 4000
1012 선우선 0606064234567 2011-11-11 2000
1013 남진 6511111234567 1999-11-11 2000
1014 이주형 9904171234567 2009-11-11 2000
1015 남궁진 0202023234567 2010-10-10 2300
*/
--○ TBL_SAWON 테이블 백업(데이터 위주) → 각 테이블 간의 관계나 제약조건 등은 제외한 상태
CREATE TABLE TBL_SAWONBACKUP
AS
SELECT *
FROM TBL_SAWON;
--==>> Table TBL_SAWONBACKUP이(가) 생성되었습니다.
-- TBL_SAWON 테이블의 데이터들만 백업을 수행
-- 즉, 다른 이름의 테이블로 저장해 둔 상황
--○ 데이터 수정
UPDATE TBL_SAWON
SET SANAME = '똘똘이';
COMMIT;
--==>> 15개 행 이(가) 업데이트되었습니다.
-- 커밋 완료.
SELECT *
FROM TBL_SAWON;
--==>>
/*
1001 똘똘이 9710171234567 2005-01-03 3000
1002 똘똘이 9511182234567 1999-11-23 4000
1003 똘똘이 9902082234567 2006-08-10 4000
1004 똘똘이 9708112234567 2010-05-06 5000
1005 똘똘이 0502034234567 2015-10-19 1000
1006 똘똘이 0609304234567 2012-06-17 1000
1007 똘똘이 6510102234567 1999-08-22 2000
1008 똘똘이 6909101234567 1998-01-10 2000
1009 똘똘이 0505053234567 2011-05-06 1500
1010 똘똘이 6611112234567 2000-01-16 1300
1011 똘똘이 9501061234567 2009-09-19 4000
1012 똘똘이 0606064234567 2011-11-11 2000
1013 똘똘이 6511111234567 1999-11-11 2000
1014 똘똘이 9904171234567 2009-11-11 2000
1015 똘똘이 0202023234567 2010-10-10 2300
*/
ROLLBACK;
SELECT *
FROM TBL_SAWONBACKUP;
--==>
/*
1001 강혜성 9710171234567 2005-01-03 3000
1002 박가영 9511182234567 1999-11-23 4000
1003 박나영 9902082234567 2006-08-10 4000
1004 최혜인 9708112234567 2010-05-06 5000
1005 아이유 0502034234567 2015-10-19 1000
1006 이하이 0609304234567 2012-06-17 1000
1007 인순이 6510102234567 1999-08-22 2000
1008 선동열 6909101234567 1998-01-10 2000
1009 이이경 0505053234567 2011-05-06 1500
1010 선우용녀 6611112234567 2000-01-16 1300
1011 이윤수 9501061234567 2009-09-19 4000
1012 선우선 0606064234567 2011-11-11 2000
1013 남진 6511111234567 1999-11-11 2000
1014 이주형 9904171234567 2009-11-11 2000
1015 남궁진 0202023234567 2010-10-10 2300
*/
CREATE TABLE TEST_TABLE
AS
SELECT *
FROM TBL_SAWON;
SELECT *
FROM TEST_TABLE;
SELECT *
FROM TEST_TABLE TT
INNER JOIN
TBL_SAWONBACKUP TB
ON TT.SANO = TB.SANO;
SELECT *
FROM TBL_SAWONBACKUP TB;
UPDATE TEST_TABLE TT
SET
SANAME
=
(
SELECT TB.SANAME
FROM TBL_SAWONBACKUP TB
WHERE TT.SANO = TB.SANO
);
SELECT *
FROM TEST_TABLE;
SELECT *
FROM TBL_SAWON;
UPDATE TBL_SAWON TS
SET
TS.SANAME
=
(
SELECT TB.SANAME
FROM TBL_SAWONBACKUP TB
WHERE TS.SANO = TB.SANO
);
SELECT *
FROM TBL_SAWON;
SELECT *
FROM TBL_SAWONBACKUP;
UPDATE TBL_SAWON
SET SANAME = '강혜성'
WHERE SANAME = '똘똘이'
AND
SANO = 1001; -- 이렇게 일일이 변경하는 것은 무리다!
UPDATE TBL_SAWON
SET SANAME = (TBL_SAWONBACKUP 테이블 각각의 SANAME)
WHERE SANAME = '똘똘이'; -- 내가 빠뜨렸던 조건이므로 다시 보자...
-- 만약, 일부의 데이터만 똘똘이일 경우, 이 조건을 적용해 주지 않았을 때
-- 다른 데이터들도 모두 데이터가 변경될 수가 있겠다.
UPDATE TBL_SAWON
SET SANAME = ( SELECT SANAME
FROM TBL_SAWONBACKUP
WHERE SANO = TBL_SAWON.SANO)
WHERE SANAME = '똘똘이';
--==>> 15개 행 이(가) 업데이트되었습니다.
SELECT *
FROM TBL_SAWON;
COMMIT;
[중간 수행평가 - 2차 제출물]
TBL_INSA(4팀_김지민).sql
SELECT USER
FROM DUAL;
--==>> SCOTT
--○ 테이블 생성 (SCOTT.TBL_INSA)
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)
);
--==>> Table TBL_INSA이(가) 생성되었습니다.
--※ 세션 기본값 설정
ALTER SESSION SET NLS_DATE_FORMAT ='YYYY-MM-DD';
--==>> Session이(가) 변경되었습니다.
--○ 데이터 입력
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);
--==>> 1 행 이(가) 삽입되었습니다. * 60
--○ 커밋
COMMIT;
--==>> 커밋 완료.
SELECT *
FROM TBL_INSA;
------------------------------------------------------------------------------------------------------------
-- 김지민_4팀
-- 01. TBL_INSA 테이블 전체자료 조회
SELECT *
FROM TBL_INSA;
--==>>
/*
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
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
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
1035 정영희 831010-2153252 2002-05-16 인천 개발부 사원 1050000 140000
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
1048 이기상 790604-1415141 2001-06-07 전남 개발부 대리 2050000 106000
1049 이미성 830908-2456548 2000-04-07 인천 010-6654-8854 개발부 사원 1300000 130000
1050 이미인 810403-2828287 2003-06-07 경기 011-8585-5252 홍보부 대리 1950000 103000
*/
--★ 까먹었어...
-- 02. SCOTT 사용자 소유 테이블 목록 확인(2가지 구문 활용)
SELECT *
FROM ALL_TABLES
WHERE OWNER = 'SCOTT';
SELECT *
FROM USER_TABLES;
--==>>
/*
TBL_EMP
DEPT
EMP
BONUS
SALGRADE
TBL_EXAMPLE2
EMP4
EMP5
TBL_DEPT
TBL_SAWON
TBL_WATCH
TBL_FILES
TBL_BOARD
TBL_JUMUN
TBL_JUMUN_TEST_TABLE
TBL_JUMUNBACKUP2
TBL_SAWONBACKUP
TBL_JUMUNBACKUP
TEST_TABLE
TBL_INSA
*/
--03. TBL_INSA 테이블 구조 확인
DESC TBL_INSA;
--==>>
/*
이름 널? 유형
-------- -------- ------------
NUM NOT NULL NUMBER(5)
NAME NOT NULL VARCHAR2(20)
SSN NOT NULL VARCHAR2(14)
IBSADATE NOT NULL DATE
CITY VARCHAR2(10)
TEL VARCHAR2(15)
BUSEO NOT NULL VARCHAR2(15)
JIKWI NOT NULL VARCHAR2(15)
BASICPAY NOT NULL NUMBER(10)
SUDANG NOT NULL NUMBER(10)
*/
-- 04. TBL_INSA 테이블의 이름(NAME), 기본급(BASICPAY) 조회
SELECT NAME, BASICPAY
FROM TBL_INSA;
--==>>
/*
홍길동 2610000
이순신 1320000
이순애 2550000
김정훈 1954200
한석봉 1420000
이기자 2265000
장인철 1250000
김영년 950000
나윤균 840000
김종서 2540000
유관순 1020000
정한국 880000
조미숙 1601000
황진이 1100000
이현숙 1050000
이상헌 2350000
엄용수 950000
이성길 880000
박문수 2300000
유영희 880000
홍길남 875000
이영숙 1960000
김인수 2500000
김말자 1900000
우재옥 1100000
김숙남 1050000
김영길 2340000
이남신 892000
김말숙 920000
정정해 2304000
지재환 2450000
심심해 880000
김미나 1020000
이정석 1100000
정영희 1050000
이재영 960400
최석규 2350000
손인수 2000000
고순정 2010000
박세열 2100000
문길수 2300000
채정희 1020000
양미옥 1100000
지수환 1060000
홍원신 960000
허경운 2650000
산마루 2100000
이기상 2050000
이미성 1300000
이미인 1950000
권영미 2260000
권옥경 1020000
김싱식 960000
정상호 980000
정한나 1000000
전용재 1950000
이미경 2520000
김신제 1950000
임수봉 890000
김신애 900000
*/
--05. TBL_INSA 테이블의 이름(NAME), 기본급(BASICPAY), 수당(SUDANG), 기본급+수당 조회
SELECT NAME "이름", BASICPAY "기본급", SUDANG "수당", BASICPAY + SUDANG "기본급+수당"
FROM TBL_INSA;
--==>>
/*
홍길동 2610000 200000 2810000
이순신 1320000 200000 1520000
이순애 2550000 160000 2710000
김정훈 1954200 170000 2124200
한석봉 1420000 160000 1580000
이기자 2265000 150000 2415000
장인철 1250000 150000 1400000
김영년 950000 145000 1095000
나윤균 840000 220400 1060400
김종서 2540000 130000 2670000
유관순 1020000 140000 1160000
정한국 880000 114000 994000
조미숙 1601000 103000 1704000
황진이 1100000 130000 1230000
이현숙 1050000 104000 1154000
이상헌 2350000 150000 2500000
엄용수 950000 210000 1160000
이성길 880000 123000 1003000
박문수 2300000 165000 2465000
유영희 880000 140000 1020000
홍길남 875000 120000 995000
이영숙 1960000 180000 2140000
김인수 2500000 170000 2670000
김말자 1900000 170000 2070000
우재옥 1100000 160000 1260000
김숙남 1050000 150000 1200000
김영길 2340000 170000 2510000
이남신 892000 110000 1002000
김말숙 920000 124000 1044000
정정해 2304000 124000 2428000
지재환 2450000 160000 2610000
심심해 880000 108000 988000
김미나 1020000 104000 1124000
이정석 1100000 160000 1260000
정영희 1050000 140000 1190000
이재영 960400 190000 1150400
최석규 2350000 187000 2537000
손인수 2000000 150000 2150000
고순정 2010000 160000 2170000
박세열 2100000 130000 2230000
문길수 2300000 150000 2450000
채정희 1020000 200000 1220000
양미옥 1100000 210000 1310000
지수환 1060000 220000 1280000
홍원신 960000 152000 1112000
허경운 2650000 150000 2800000
산마루 2100000 112000 2212000
이기상 2050000 106000 2156000
이미성 1300000 130000 1430000
이미인 1950000 103000 2053000
권영미 2260000 104000 2364000
권옥경 1020000 105000 1125000
김싱식 960000 108000 1068000
정상호 980000 114000 1094000
정한나 1000000 104000 1104000
전용재 1950000 200000 2150000
이미경 2520000 160000 2680000
김신제 1950000 180000 2130000
임수봉 890000 102000 992000
김신애 900000 102000 1002000
*/
--06. TBL_INSA 테이블의 이름(NAME), 출신도(CITY), 부서명(BUSEO) 조회. 별칭(ALIAS) 사용.
SELECT NAME "이름", CITY "출신도", BUSEO "부서명"
FROM TBL_INSA;
--==>>
/*
홍길동 서울 기획부
이순신 경기 총무부
이순애 인천 개발부
김정훈 전북 영업부
한석봉 서울 총무부
이기자 인천 개발부
장인철 제주 개발부
김영년 서울 홍보부
나윤균 경기 인사부
김종서 부산 영업부
유관순 서울 영업부
정한국 강원 홍보부
조미숙 경기 홍보부
황진이 인천 개발부
이현숙 경기 총무부
이상헌 경기 개발부
엄용수 인천 개발부
이성길 전북 개발부
박문수 서울 인사부
유영희 전남 자재부
홍길남 경기 개발부
이영숙 전남 기획부
김인수 서울 영업부
김말자 서울 기획부
우재옥 서울 영업부
김숙남 경기 영업부
김영길 서울 총무부
이남신 제주 인사부
김말숙 서울 총무부
정정해 부산 총무부
지재환 서울 기획부
심심해 전북 자재부
김미나 서울 영업부
이정석 경기 기획부
정영희 인천 개발부
이재영 서울 자재부
최석규 인천 홍보부
손인수 부산 영업부
고순정 경기 영업부
박세열 경북 인사부
문길수 충남 자재부
채정희 경기 개발부
양미옥 서울 영업부
지수환 서울 영업부
홍원신 전북 영업부
허경운 경남 총무부
산마루 서울 영업부
이기상 전남 개발부
이미성 인천 개발부
이미인 경기 홍보부
권영미 서울 영업부
권옥경 경기 기획부
김싱식 전북 자재부
정상호 강원 홍보부
정한나 서울 영업부
전용재 인천 영업부
이미경 경기 자재부
김신제 인천 기획부
임수봉 서울 개발부
김신애 서울 개발부
*/
--07. 서울 사람의 이름(NAME), 출신도(CITY), 부서명(BUSEO), 직위(JIKWI) 조회
SELECT NAME "이름", CITY "출신도", BUSEO "부서명", JIKWI "직위"
FROM TBL_INSA
WHERE CITY LIKE '서울';
--==>>
/*
홍길동 서울 기획부 부장
한석봉 서울 총무부 사원
김영년 서울 홍보부 사원
유관순 서울 영업부 사원
박문수 서울 인사부 과장
김인수 서울 영업부 부장
김말자 서울 기획부 대리
우재옥 서울 영업부 사원
김영길 서울 총무부 과장
김말숙 서울 총무부 사원
지재환 서울 기획부 부장
김미나 서울 영업부 사원
이재영 서울 자재부 사원
양미옥 서울 영업부 사원
지수환 서울 영업부 사원
산마루 서울 영업부 대리
권영미 서울 영업부 과장
정한나 서울 영업부 사원
임수봉 서울 개발부 사원
김신애 서울 개발부 사원
*/
--08. 출신도가 서울 사람이면서 → WHERE 구문
기본급이 150만원 이상인 사람 → WHERE 구문
조회 (NAME, CITY, BASICPAY, SSN)
SELECT NAME, CITY, BASICPAY, SSN
FROM TBL_INSA
WHERE CITY LIKE '서울'
AND
BASICPAY >= 1500000;
--==>>
/*
홍길동 서울 2610000 771212-1022432
박문수 서울 2300000 780710-1985632
김인수 서울 2500000 731211-1214576
김말자 서울 1900000 830225-2633334
김영길 서울 2340000 801216-1898752
지재환 서울 2450000 771115-1687988
산마루 서울 2100000 780505-1234567
권영미 서울 2260000 790303-2155554
*/
--09. 출신도가 '인천' 이면서, 기본급이 100만원이상 ~ 200만원 미만인 경우만 모든정보 조회.
SELECT *
FROM TBL_INSA
WHERE CITY LIKE '인천'
AND
BASICPAY BETWEEN 1000000 AND 1999999;
--==>>
/*
1014 황진이 810707-2574812 2002-02-15 인천 010-3214-5467 개발부 사원 1100000 130000
1035 정영희 831010-2153252 2002-05-16 인천 개발부 사원 1050000 140000
1049 이미성 830908-2456548 2000-04-07 인천 010-6654-8854 개발부 사원 1300000 130000
1056 전용재 800605-1456987 2004-08-13 인천 010-7549-8654 영업부 대리 1950000 200000
1058 김신제 800709-1321456 2003-08-08 인천 010-2415-5444 기획부 대리 1950000 180000
*/
--10. 출신도가 서울 사람이거나 부서가 개발부인 자료 조회 (NAME, CITY, BUSEO)
SELECT NAME, CITY, BUSEO
FROM TBL_INSA
WHERE CITY LIKE '서울'
OR
BUSEO LIKE '개발부';
--==>>
/*
홍길동 서울 기획부
이순애 인천 개발부
한석봉 서울 총무부
이기자 인천 개발부
장인철 제주 개발부
김영년 서울 홍보부
유관순 서울 영업부
황진이 인천 개발부
이상헌 경기 개발부
엄용수 인천 개발부
이성길 전북 개발부
박문수 서울 인사부
홍길남 경기 개발부
김인수 서울 영업부
김말자 서울 기획부
우재옥 서울 영업부
김영길 서울 총무부
김말숙 서울 총무부
지재환 서울 기획부
김미나 서울 영업부
정영희 인천 개발부
이재영 서울 자재부
채정희 경기 개발부
양미옥 서울 영업부
지수환 서울 영업부
산마루 서울 영업부
이기상 전남 개발부
이미성 인천 개발부
권영미 서울 영업부
정한나 서울 영업부
임수봉 서울 개발부
김신애 서울 개발부
*/
-- 11. 출신도가 서울, 경기인 사람만 조회 (NAME, CITY, BUSEO). IN 연산자 사용.
SELECT NAME, CITY, BUSEO
FROM TBL_INSA
WHERE CITY IN ('서울', '경기');
--==>>
/*
홍길동 서울 기획부
이순신 경기 총무부
한석봉 서울 총무부
김영년 서울 홍보부
나윤균 경기 인사부
유관순 서울 영업부
조미숙 경기 홍보부
이현숙 경기 총무부
이상헌 경기 개발부
박문수 서울 인사부
홍길남 경기 개발부
김인수 서울 영업부
김말자 서울 기획부
우재옥 서울 영업부
김숙남 경기 영업부
김영길 서울 총무부
김말숙 서울 총무부
지재환 서울 기획부
김미나 서울 영업부
이정석 경기 기획부
이재영 서울 자재부
고순정 경기 영업부
채정희 경기 개발부
양미옥 서울 영업부
지수환 서울 영업부
산마루 서울 영업부
이미인 경기 홍보부
권영미 서울 영업부
권옥경 경기 기획부
정한나 서울 영업부
이미경 경기 자재부
임수봉 서울 개발부
김신애 서울 개발부
*/
--12. 부서가 '개발부' 이거나 '영업부'인 사원의 모든정보 조회. IN 연산자 사용.
SELECT *
FROM TBL_INSA
WHERE BUSEO IN ('개발부', '영업부');
--==>>
/*
1003 이순애 770922-2312547 1999-02-25 인천 010-4231-1236 개발부 부장 2550000 160000
1004 김정훈 790304-1788896 2000-10-01 전북 019-5236-4221 영업부 대리 1954200 170000
1006 이기자 780505-2978541 2002-02-11 인천 010-3214-5357 개발부 과장 2265000 150000
1007 장인철 780506-1625148 1998-03-16 제주 011-2345-2525 개발부 대리 1250000 150000
1010 김종서 751010-1122233 1997-08-08 부산 011-3214-5555 영업부 부장 2540000 130000
1011 유관순 801010-2987897 2000-07-07 서울 010-8888-4422 영업부 사원 1020000 140000
1014 황진이 810707-2574812 2002-02-15 인천 010-3214-5467 개발부 사원 1100000 130000
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
1021 홍길남 801010-1111111 2001-09-07 경기 011-9999-7575 개발부 사원 875000 120000
1023 김인수 731211-1214576 1995-02-23 서울 영업부 부장 2500000 170000
1025 우재옥 801103-1654442 2000-10-01 서울 010-4563-2587 영업부 사원 1100000 160000
1026 김숙남 810907-2015457 2002-08-28 경기 010-2112-5225 영업부 사원 1050000 150000
1033 김미나 780505-2999999 1998-06-07 서울 011-2444-4444 영업부 사원 1020000 104000
1035 정영희 831010-2153252 2002-05-16 인천 개발부 사원 1050000 140000
1038 손인수 791009-2321456 1999-11-15 부산 010-6542-7412 영업부 대리 2000000 150000
1039 고순정 800504-2000032 2003-12-28 경기 010-2587-7895 영업부 대리 2010000 160000
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
1047 산마루 780505-1234567 2001-07-15 서울 018-0505-0505 영업부 대리 2100000 112000
1048 이기상 790604-1415141 2001-06-07 전남 개발부 대리 2050000 106000
1049 이미성 830908-2456548 2000-04-07 인천 010-6654-8854 개발부 사원 1300000 130000
1051 권영미 790303-2155554 2000-06-04 서울 011-5555-7548 영업부 과장 2260000 104000
1055 정한나 820506-2425153 2004-06-07 서울 016-2424-4242 영업부 사원 1000000 104000
1056 전용재 800605-1456987 2004-08-13 인천 010-7549-8654 영업부 대리 1950000 200000
1059 임수봉 810809-2121244 2001-10-10 서울 011-4151-4154 개발부 사원 890000 102000
1060 김신애 810809-2111111 2001-10-10 서울 011-4151-4444 개발부 사원 900000 102000
*/
--13. 급여(BASICPAY + SUDANG)가 250만원 이상인 사람 조회. → WHERE 구문
-- 단, 필드명은 한글로 출력. → 별칭(ALIAS)
-- (NAME, BASICPAY, SUDANG, BASICPAY+SUDANG);
SELECT NAME "이름", BASICPAY "기본급", SUDANG "수당", BASICPAY+SUDANG "급여"
FROM TBL_INSA
WHERE BASICPAY + SUDANG >= 2500000;
--==>>
/*
홍길동 2610000 200000 2810000
이순애 2550000 160000 2710000
김종서 2540000 130000 2670000
이상헌 2350000 150000 2500000
김인수 2500000 170000 2670000
김영길 2340000 170000 2510000
지재환 2450000 160000 2610000
최석규 2350000 187000 2537000
허경운 2650000 150000 2800000
이미경 2520000 160000 2680000
*/
--14. 주민번호를 기준으로 남자(성별 자릿수가 1, 3)만 조회.
-- ( 이름(NAME), 주민번호(SSN) )
-- 단, SUBSTR() 함수 이용;
SELECT NAME "이름", SSN "주민번호"
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('1', '3');
--==>>
/*
홍길동 771212-1022432
이순신 801007-1544236
김정훈 790304-1788896
한석봉 811112-1566789
장인철 780506-1625148
나윤균 810810-1552147
김종서 751010-1122233
정한국 760909-1333333
이상헌 781010-1666678
엄용수 820507-1452365
이성길 801028-1849534
박문수 780710-1985632
홍길남 801010-1111111
김인수 731211-1214576
우재옥 801103-1654442
김영길 801216-1898752
이남신 810101-1010101
지재환 771115-1687988
이정석 820505-1325468
최석규 770129-1456987
박세열 790509-1635214
문길수 721217-1951357
지수환 820305-1475286
홍원신 690906-1985214
허경운 760105-1458752
산마루 780505-1234567
이기상 790604-1415141
김싱식 800715-1313131
정상호 810705-1212141
전용재 800605-1456987
김신제 800709-1321456
*/
-- 15. 주민번호를 기준으로 80년대 태어난 사람만 조회.
-- ( 이름(NAME), 주민번호(SSN) );
SELECT NAME, SSN
FROM TBL_INSA
WHERE SUBSTR(SSN, 1, 1) LIKE '8' -- 80년대 이면서...
AND
SUBSTR(SSN, 8, 1) IN ('1', '2'); -- 2000년대 이전 출생자.
--==>>
/*
이순신 801007-1544236
한석봉 811112-1566789
김영년 821011-2362514
나윤균 810810-1552147
유관순 801010-2987897
황진이 810707-2574812
이현숙 800606-2954687
엄용수 820507-1452365
이성길 801028-1849534
유영희 800304-2741258
홍길남 801010-1111111
이영숙 800501-2312456
김말자 830225-2633334
우재옥 801103-1654442
김숙남 810907-2015457
김영길 801216-1898752
이남신 810101-1010101
김말숙 800301-2020202
심심해 810206-2222222
이정석 820505-1325468
정영희 831010-2153252
고순정 800504-2000032
채정희 810709-2000054
양미옥 830504-2471523
지수환 820305-1475286
이미성 830908-2456548
이미인 810403-2828287
권옥경 820406-2000456
김싱식 800715-1313131
정상호 810705-1212141
정한나 820506-2425153
전용재 800605-1456987
김신제 800709-1321456
임수봉 810809-2121244
김신애 810809-2111111
*/
-- 나이 계산하기 (한국식)
SELECT CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '2') -- 1990년도 출생자라면...
THEN EXTRACT(YEAR FROM SYSDATE) - 1900 - (TO_NUMBER(SUBSTR(SSN, 1, 2))) + 1
WHEN SUBSTR(SSN, 8, 1) IN ('3', '4') -- 2000년도 출생자라면...
THEN EXTRACT(YEAR FROM SYSDATE) - 2000 - (TO_NUMBER(SUBSTR(SSN, 1, 2))) + 1
ELSE -1 -- 알 수 없음.
END "현재 나이"
, NAME
-- 나이 계산
SELECT EXTRACT(YEAR FROM SYSDATE) - 1900 - (TO_NUMBER(SUBSTR(SSN, 1, 2)) + 1)
FROM TBL_INSA;
--16. 서울 사람 중에서 70년대 태어난 사람만 조회. SUBSTR() 함수 이용.
SELECT *
FROM TBL_INSA
WHERE CITY LIKE '서울'
AND
SUBSTR(SSN, 1, 1) = '7' -- 70년대생이면서...
AND
SUBSTR(SSN, 8, 1) IN ('1', '2'); -- 2000년대 이전 출생자.
--==>>
/*
1001 홍길동 771212-1022432 1998-10-11 서울 011-2356-4528 기획부 부장 2610000 200000
1019 박문수 780710-1985632 1999-12-10 서울 017-4747-4848 인사부 과장 2300000 165000
1023 김인수 731211-1214576 1995-02-23 서울 영업부 부장 2500000 170000
1031 지재환 771115-1687988 2001-01-21 서울 019-5552-7511 기획부 부장 2450000 160000
1033 김미나 780505-2999999 1998-06-07 서울 011-2444-4444 영업부 사원 1020000 104000
1036 이재영 701126-2852147 2003-08-10 서울 011-9999-9999 자재부 사원 960400 190000
1047 산마루 780505-1234567 2001-07-15 서울 018-0505-0505 영업부 대리 2100000 112000
1051 권영미 790303-2155554 2000-06-04 서울 011-5555-7548 영업부 과장 2260000 104000
*/
-- 17. 서울 사람 중에서 70년대 태어난 남자만 조회. SUBSTR() 함수 이용.
SELECT *
FROM TBL_INSA
WHERE CITY LIKE '서울'
AND
SUBSTR(SSN, 1, 1) = '7' -- 70년대생이면서...
AND
SUBSTR(SSN, 8, 1) = '1'; -- 2000년대 이전 남자는 주민번호 7번째 자리 1
--==>>
/*
1001 홍길동 771212-1022432 1998-10-11 서울 011-2356-4528 기획부 부장 2610000 200000
1019 박문수 780710-1985632 1999-12-10 서울 017-4747-4848 인사부 과장 2300000 165000
1023 김인수 731211-1214576 1995-02-23 서울 영업부 부장 2500000 170000
1031 지재환 771115-1687988 2001-01-21 서울 019-5552-7511 기획부 부장 2450000 160000
1047 산마루 780505-1234567 2001-07-15 서울 018-0505-0505 영업부 대리 2100000 112000
*/
--18. 서울 사람이면서 김씨만 조회
-- 단, 성씨가 한 글자라는 가정.
-- ( 이름, 출신도 )
-- SUBSTR() 함수 이용.;
--SELECT NAME
--FROM TBL_INSA
--WHERE NAME LIKE '김%'
-- AND
-- CITY LIKE '서울';
SELECT *
FROM TBL_INSA
WHERE CITY LIKE '서울'
AND
SUBSTR(NAME, 1, 1) = '김';
--==>>
/*
1008 김영년 821011-2362514 2002-04-30 서울 016-2222-4444 홍보부 사원 950000 145000
1023 김인수 731211-1214576 1995-02-23 서울 영업부 부장 2500000 170000
1024 김말자 830225-2633334 1999-08-28 서울 011-5248-7789 기획부 대리 1900000 170000
1027 김영길 801216-1898752 2000-10-18 서울 019-8523-1478 총무부 과장 2340000 170000
1029 김말숙 800301-2020202 2000-09-08 서울 016-3535-3636 총무부 사원 920000 124000
1033 김미나 780505-2999999 1998-06-07 서울 011-2444-4444 영업부 사원 1020000 104000
1060 김신애 810809-2111111 2001-10-10 서울 011-4151-4444 개발부 사원 900000 102000
*/
-- 19. 2000년도에 입사한 사람 조회. (이름, 출신도, 입사일).
SELECT NAME "이름", CITY "출신도", IBSADATE "입사일"
FROM TBL_INSA
WHERE SUBSTR(IBSADATE, 1, 1) LIKE '2';
--==>>
/*
이순신 경기 2000-11-29
김정훈 전북 2000-10-01
한석봉 서울 2004-08-13
이기자 인천 2002-02-11
김영년 서울 2002-04-30
나윤균 경기 2003-10-10
유관순 서울 2000-07-07
황진이 인천 2002-02-15
이상헌 경기 2001-11-29
엄용수 인천 2000-08-28
이성길 전북 2004-08-08
유영희 전남 2003-10-10
홍길남 경기 2001-09-07
이영숙 전남 2003-02-25
우재옥 서울 2000-10-01
김숙남 경기 2002-08-28
김영길 서울 2000-10-18
이남신 제주 2001-09-07
김말숙 서울 2000-09-08
지재환 서울 2001-01-21
심심해 전북 2000-05-05
이정석 경기 2005-09-26
정영희 인천 2002-05-16
이재영 서울 2003-08-10
고순정 경기 2003-12-28
박세열 경북 2000-09-10
문길수 충남 2001-12-10
채정희 경기 2003-10-17
양미옥 서울 2003-09-24
지수환 서울 2004-01-21
홍원신 전북 2003-03-16
산마루 서울 2001-07-15
이기상 전남 2001-06-07
이미성 인천 2000-04-07
이미인 경기 2003-06-07
권영미 서울 2000-06-04
권옥경 경기 2000-10-10
정한나 서울 2004-06-07
전용재 인천 2004-08-13
김신제 인천 2003-08-08
임수봉 서울 2001-10-10
김신애 서울 2001-10-10
*/
--20. 2000년 10월에 입사한 사람 조회. (이름, 출신도, 입사일).
SELECT NAME "이름", CITY "출신도", IBSADATE "입사일"
FROM TBL_INSA
WHERE TO_CHAR(IBSADATE, 'YYYY-MM') = '2000-10';
--==>>
/*
김정훈 전북 2000-10-01
우재옥 서울 2000-10-01
김영길 서울 2000-10-18
권옥경 경기 2000-10-10
*/
--21. 주민번호를 기준으로 직원의 나이 조회.
-- 단, 모든 직원이 1900년대에 태어났다는 가정. (이름, 주민번호, 나이)
-- 나이 계산하기 (한국식, 1900년대 출생자)
SELECT NAME "이름"
, SSN "주민번호"
, EXTRACT(YEAR FROM SYSDATE) - 1900 - (TO_NUMBER(SUBSTR(SSN, 1, 2))) + 1 "나이"
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('1', '2');
--==>>
/*
홍길동 771212-1022432 47
이순신 801007-1544236 44
이순애 770922-2312547 47
김정훈 790304-1788896 45
한석봉 811112-1566789 43
이기자 780505-2978541 46
장인철 780506-1625148 46
김영년 821011-2362514 42
나윤균 810810-1552147 43
김종서 751010-1122233 49
유관순 801010-2987897 44
정한국 760909-1333333 48
조미숙 790102-2777777 45
황진이 810707-2574812 43
이현숙 800606-2954687 44
이상헌 781010-1666678 46
엄용수 820507-1452365 42
이성길 801028-1849534 44
박문수 780710-1985632 46
유영희 800304-2741258 44
홍길남 801010-1111111 44
이영숙 800501-2312456 44
김인수 731211-1214576 51
김말자 830225-2633334 41
우재옥 801103-1654442 44
김숙남 810907-2015457 43
김영길 801216-1898752 44
이남신 810101-1010101 43
김말숙 800301-2020202 44
정정해 790210-2101010 45
지재환 771115-1687988 47
심심해 810206-2222222 43
김미나 780505-2999999 46
이정석 820505-1325468 42
정영희 831010-2153252 41
이재영 701126-2852147 54
최석규 770129-1456987 47
손인수 791009-2321456 45
고순정 800504-2000032 44
박세열 790509-1635214 45
문길수 721217-1951357 52
채정희 810709-2000054 43
양미옥 830504-2471523 41
지수환 820305-1475286 42
홍원신 690906-1985214 55
허경운 760105-1458752 48
산마루 780505-1234567 46
이기상 790604-1415141 45
이미성 830908-2456548 41
이미인 810403-2828287 43
권영미 790303-2155554 45
권옥경 820406-2000456 42
김싱식 800715-1313131 44
정상호 810705-1212141 43
정한나 820506-2425153 42
전용재 800605-1456987 44
이미경 780406-2003214 46
김신제 800709-1321456 44
임수봉 810809-2121244 43
김신애 810809-2111111 43
*/
--22. 주민번호 기준으로 현재 나이대가 20대인 사람만 조회.
SELECT *
FROM
TBL_INSA INSA
INNER JOIN
(
SELECT EXTRACT(YEAR FROM SYSDATE) - 1900 - (TO_NUMBER(SUBSTR(SSN, 1, 2))) + 1 "나이"
, SSN "주민번호"
FROM TBL_INSA
) T
ON INSA.SSN = T.주민번호
WHERE T.나이 BETWEEN 20 AND 29;
--==>> 조회 결과 없음. 다 40대예요~!!!
--23. 주민번호 기준으로 5월 생만 조회.
-- 단, SUBSTR() 함수 이용.
SELECT *
FROM TBL_INSA
WHERE TO_NUMBER(SUBSTR(SSN, 3, 2)) = 5;
--==>>
/*
1006 이기자 780505-2978541 2002-02-11 인천 010-3214-5357 개발부 과장 2265000 150000
1007 장인철 780506-1625148 1998-03-16 제주 011-2345-2525 개발부 대리 1250000 150000
1017 엄용수 820507-1452365 2000-08-28 인천 010-3254-2542 개발부 사원 950000 210000
1022 이영숙 800501-2312456 2003-02-25 전남 017-5214-5282 기획부 대리 1960000 180000
1033 김미나 780505-2999999 1998-06-07 서울 011-2444-4444 영업부 사원 1020000 104000
1034 이정석 820505-1325468 2005-09-26 경기 011-3697-7412 기획부 사원 1100000 160000
1039 고순정 800504-2000032 2003-12-28 경기 010-2587-7895 영업부 대리 2010000 160000
1040 박세열 790509-1635214 2000-09-10 경북 016-4444-7777 인사부 대리 2100000 130000
1043 양미옥 830504-2471523 2003-09-24 서울 016-8548-6547 영업부 사원 1100000 210000
1047 산마루 780505-1234567 2001-07-15 서울 018-0505-0505 영업부 대리 2100000 112000
1055 정한나 820506-2425153 2004-06-07 서울 016-2424-4242 영업부 사원 1000000 104000
*/
--
--24. 주민번호 기준으로 5월 생만 조회.
-- 단, TO_CHAR() 함수 이용.
--① 주민번호 앞 6자리를 서브스트링으로 가져옵니다.
--② 앞에 문자 '19'를 붙여 ①과 문자열 결합합니다.
--③ 그것을 DATE 형식으로 바꿉니다.
--④ 그것을 다시 문자 형식으로 바꾸기 위해 TO_CHAR() 함수를 사용했습니다.
SELECT *
FROM TBL_INSA
WHERE TO_CHAR(TO_DATE(CONCAT(19, SUBSTR(SSN, 1, 6)), 'YYYY-MM-DD'), 'MM') = '05';
--==>>
/*
1006 이기자 780505-2978541 2002-02-11 인천 010-3214-5357 개발부 과장 2265000 150000
1007 장인철 780506-1625148 1998-03-16 제주 011-2345-2525 개발부 대리 1250000 150000
1017 엄용수 820507-1452365 2000-08-28 인천 010-3254-2542 개발부 사원 950000 210000
1022 이영숙 800501-2312456 2003-02-25 전남 017-5214-5282 기획부 대리 1960000 180000
1033 김미나 780505-2999999 1998-06-07 서울 011-2444-4444 영업부 사원 1020000 104000
1034 이정석 820505-1325468 2005-09-26 경기 011-3697-7412 기획부 사원 1100000 160000
1039 고순정 800504-2000032 2003-12-28 경기 010-2587-7895 영업부 대리 2010000 160000
1040 박세열 790509-1635214 2000-09-10 경북 016-4444-7777 인사부 대리 2100000 130000
1043 양미옥 830504-2471523 2003-09-24 서울 016-8548-6547 영업부 사원 1100000 210000
1047 산마루 780505-1234567 2001-07-15 서울 018-0505-0505 영업부 대리 2100000 112000
1055 정한나 820506-2425153 2004-06-07 서울 016-2424-4242 영업부 사원 1000000 104000
*/
--25. 출신도 내림차순으로 정렬하고, 출신도가 같으면 기본급 내림차순 정렬 조회.
SELECT *
FROM TBL_INSA
ORDER BY CITY DESC, BASICPAY DESC;
--==>>
/*
1041 문길수 721217-1951357 2001-12-10 충남 016-4444-5555 자재부 과장 2300000 150000
1007 장인철 780506-1625148 1998-03-16 제주 011-2345-2525 개발부 대리 1250000 150000
1028 이남신 810101-1010101 2001-09-07 제주 016-1818-4848 인사부 사원 892000 110000
1004 김정훈 790304-1788896 2000-10-01 전북 019-5236-4221 영업부 대리 1954200 170000
1053 김싱식 800715-1313131 1999-12-12 전북 011-7585-7474 자재부 사원 960000 108000
1045 홍원신 690906-1985214 2003-03-16 전북 011-7777-7777 영업부 사원 960000 152000
1032 심심해 810206-2222222 2000-05-05 전북 016-8888-7474 자재부 사원 880000 108000
1018 이성길 801028-1849534 2004-08-08 전북 018-1333-3333 개발부 사원 880000 123000
1048 이기상 790604-1415141 2001-06-07 전남 개발부 대리 2050000 106000
1022 이영숙 800501-2312456 2003-02-25 전남 017-5214-5282 기획부 대리 1960000 180000
1020 유영희 800304-2741258 2003-10-10 전남 011-9595-8585 자재부 사원 880000 140000
1003 이순애 770922-2312547 1999-02-25 인천 010-4231-1236 개발부 부장 2550000 160000
1037 최석규 770129-1456987 1998-10-15 인천 011-7777-7777 홍보부 과장 2350000 187000
1006 이기자 780505-2978541 2002-02-11 인천 010-3214-5357 개발부 과장 2265000 150000
1056 전용재 800605-1456987 2004-08-13 인천 010-7549-8654 영업부 대리 1950000 200000
1058 김신제 800709-1321456 2003-08-08 인천 010-2415-5444 기획부 대리 1950000 180000
1049 이미성 830908-2456548 2000-04-07 인천 010-6654-8854 개발부 사원 1300000 130000
1014 황진이 810707-2574812 2002-02-15 인천 010-3214-5467 개발부 사원 1100000 130000
1035 정영희 831010-2153252 2002-05-16 인천 개발부 사원 1050000 140000
1017 엄용수 820507-1452365 2000-08-28 인천 010-3254-2542 개발부 사원 950000 210000
1001 홍길동 771212-1022432 1998-10-11 서울 011-2356-4528 기획부 부장 2610000 200000
1023 김인수 731211-1214576 1995-02-23 서울 영업부 부장 2500000 170000
1031 지재환 771115-1687988 2001-01-21 서울 019-5552-7511 기획부 부장 2450000 160000
1027 김영길 801216-1898752 2000-10-18 서울 019-8523-1478 총무부 과장 2340000 170000
1019 박문수 780710-1985632 1999-12-10 서울 017-4747-4848 인사부 과장 2300000 165000
1051 권영미 790303-2155554 2000-06-04 서울 011-5555-7548 영업부 과장 2260000 104000
1047 산마루 780505-1234567 2001-07-15 서울 018-0505-0505 영업부 대리 2100000 112000
1024 김말자 830225-2633334 1999-08-28 서울 011-5248-7789 기획부 대리 1900000 170000
1005 한석봉 811112-1566789 2004-08-13 서울 018-5211-3542 총무부 사원 1420000 160000
1025 우재옥 801103-1654442 2000-10-01 서울 010-4563-2587 영업부 사원 1100000 160000
1043 양미옥 830504-2471523 2003-09-24 서울 016-8548-6547 영업부 사원 1100000 210000
1044 지수환 820305-1475286 2004-01-21 서울 011-5555-7548 영업부 사원 1060000 220000
1033 김미나 780505-2999999 1998-06-07 서울 011-2444-4444 영업부 사원 1020000 104000
1011 유관순 801010-2987897 2000-07-07 서울 010-8888-4422 영업부 사원 1020000 140000
1055 정한나 820506-2425153 2004-06-07 서울 016-2424-4242 영업부 사원 1000000 104000
1036 이재영 701126-2852147 2003-08-10 서울 011-9999-9999 자재부 사원 960400 190000
1008 김영년 821011-2362514 2002-04-30 서울 016-2222-4444 홍보부 사원 950000 145000
1029 김말숙 800301-2020202 2000-09-08 서울 016-3535-3636 총무부 사원 920000 124000
1060 김신애 810809-2111111 2001-10-10 서울 011-4151-4444 개발부 사원 900000 102000
1059 임수봉 810809-2121244 2001-10-10 서울 011-4151-4154 개발부 사원 890000 102000
1010 김종서 751010-1122233 1997-08-08 부산 011-3214-5555 영업부 부장 2540000 130000
1030 정정해 790210-2101010 1999-10-17 부산 019-6564-6752 총무부 과장 2304000 124000
1038 손인수 791009-2321456 1999-11-15 부산 010-6542-7412 영업부 대리 2000000 150000
1040 박세열 790509-1635214 2000-09-10 경북 016-4444-7777 인사부 대리 2100000 130000
1046 허경운 760105-1458752 1999-05-04 경남 017-3333-3333 총무부 부장 2650000 150000
1057 이미경 780406-2003214 1998-02-11 경기 016-6542-7546 자재부 부장 2520000 160000
1016 이상헌 781010-1666678 2001-11-29 경기 010-4526-1234 개발부 과장 2350000 150000
1039 고순정 800504-2000032 2003-12-28 경기 010-2587-7895 영업부 대리 2010000 160000
1050 이미인 810403-2828287 2003-06-07 경기 011-8585-5252 홍보부 대리 1950000 103000
1013 조미숙 790102-2777777 1998-06-07 경기 019-6666-4444 홍보부 대리 1601000 103000
1002 이순신 801007-1544236 2000-11-29 경기 010-4758-6532 총무부 사원 1320000 200000
1034 이정석 820505-1325468 2005-09-26 경기 011-3697-7412 기획부 사원 1100000 160000
1015 이현숙 800606-2954687 1999-07-26 경기 016-2548-3365 총무부 사원 1050000 104000
1026 김숙남 810907-2015457 2002-08-28 경기 010-2112-5225 영업부 사원 1050000 150000
1052 권옥경 820406-2000456 2000-10-10 경기 010-3644-5577 기획부 사원 1020000 105000
1042 채정희 810709-2000054 2003-10-17 경기 011-5125-5511 개발부 사원 1020000 200000
1021 홍길남 801010-1111111 2001-09-07 경기 011-9999-7575 개발부 사원 875000 120000
1009 나윤균 810810-1552147 2003-10-10 경기 019-1111-2222 인사부 사원 840000 220400
1054 정상호 810705-1212141 1999-10-16 강원 016-1919-4242 홍보부 사원 980000 114000
1012 정한국 760909-1333333 1999-10-16 강원 018-2222-4242 홍보부 사원 880000 114000
*/
--26. 서울 사람 중에서 기본급+수당(→급여) 내림차순으로 정렬.
-- ( 이름, 출신도, 기본급+수당 )
SELECT NAME "이름", CITY "출신도", BASICPAY + SUDANG "급여"
FROM TBL_INSA
WHERE CITY LIKE '서울'
ORDER BY 급여 DESC;
--==>>
/*
홍길동 서울 2810000
김인수 서울 2670000
지재환 서울 2610000
김영길 서울 2510000
박문수 서울 2465000
권영미 서울 2364000
산마루 서울 2212000
김말자 서울 2070000
한석봉 서울 1580000
양미옥 서울 1310000
지수환 서울 1280000
우재옥 서울 1260000
유관순 서울 1160000
이재영 서울 1150400
김미나 서울 1124000
정한나 서울 1104000
김영년 서울 1095000
김말숙 서울 1044000
김신애 서울 1002000
임수봉 서울 992000
*/
--27. 여자 중 부서 오름차순으로 정렬하고, 부서가 같으면 기본급 내림차순 정렬.
-- ( 이름, 주민번호, 부서, 기본급 );
SELECT NAME "이름", SSN "주민번호", BUSEO "부서", BASICPAY "기본급"
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('2', '4')
ORDER BY 부서 ASC, 기본급 DESC;
--==>>
/*
이순애 770922-2312547 개발부 2550000
이기자 780505-2978541 개발부 2265000
이미성 830908-2456548 개발부 1300000
황진이 810707-2574812 개발부 1100000
정영희 831010-2153252 개발부 1050000
채정희 810709-2000054 개발부 1020000
김신애 810809-2111111 개발부 900000
임수봉 810809-2121244 개발부 890000
이영숙 800501-2312456 기획부 1960000
김말자 830225-2633334 기획부 1900000
권옥경 820406-2000456 기획부 1020000
권영미 790303-2155554 영업부 2260000
고순정 800504-2000032 영업부 2010000
손인수 791009-2321456 영업부 2000000
양미옥 830504-2471523 영업부 1100000
김숙남 810907-2015457 영업부 1050000
김미나 780505-2999999 영업부 1020000
유관순 801010-2987897 영업부 1020000
정한나 820506-2425153 영업부 1000000
이미경 780406-2003214 자재부 2520000
이재영 701126-2852147 자재부 960400
유영희 800304-2741258 자재부 880000
심심해 810206-2222222 자재부 880000
정정해 790210-2101010 총무부 2304000
이현숙 800606-2954687 총무부 1050000
김말숙 800301-2020202 총무부 920000
이미인 810403-2828287 홍보부 1950000
조미숙 790102-2777777 홍보부 1601000
김영년 821011-2362514 홍보부 950000
*/
-- 28. 남자 중 나이를 기준으로 오름차순 정렬하여 조회.
-- 나이구하기
--SELECT EXTRACT(YEAR FROM SYSDATE) - 1900 - (TO_NUMBER(SUBSTR(SSN, 1, 2))) + 1 "나이"
--FROM TBL_INSA
-- 너 남자니?
--WHERE SUBSTR(SSN, 8, 1) IN ('1', '3')
-- INNER JOIN 으로 구성.
SELECT *
FROM
TBL_INSA INSA
INNER JOIN
(
SELECT EXTRACT(YEAR FROM SYSDATE) - 1900 - (TO_NUMBER(SUBSTR(SSN, 1, 2))) + 1 "나이"
, SSN "주민번호"
FROM TBL_INSA
)T
ON INSA.SSN = T.주민번호
WHERE SUBSTR(SSN, 8, 1) IN ('1', '3')
ORDER BY T.나이 ASC;
-- 나이를 기준으로 오름차순 정렬하여 조회
--==>>
/*
1017 엄용수 820507-1452365 2000-08-28 인천 010-3254-2542 개발부 사원 950000 210000 42 820507-1452365
1044 지수환 820305-1475286 2004-01-21 서울 011-5555-7548 영업부 사원 1060000 220000 42 820305-1475286
1034 이정석 820505-1325468 2005-09-26 경기 011-3697-7412 기획부 사원 1100000 160000 42 820505-1325468
1054 정상호 810705-1212141 1999-10-16 강원 016-1919-4242 홍보부 사원 980000 114000 43 810705-1212141
1005 한석봉 811112-1566789 2004-08-13 서울 018-5211-3542 총무부 사원 1420000 160000 43 811112-1566789
1009 나윤균 810810-1552147 2003-10-10 경기 019-1111-2222 인사부 사원 840000 220400 43 810810-1552147
1028 이남신 810101-1010101 2001-09-07 제주 016-1818-4848 인사부 사원 892000 110000 43 810101-1010101
1058 김신제 800709-1321456 2003-08-08 인천 010-2415-5444 기획부 대리 1950000 180000 44 800709-1321456
1002 이순신 801007-1544236 2000-11-29 경기 010-4758-6532 총무부 사원 1320000 200000 44 801007-1544236
1056 전용재 800605-1456987 2004-08-13 인천 010-7549-8654 영업부 대리 1950000 200000 44 800605-1456987
1053 김싱식 800715-1313131 1999-12-12 전북 011-7585-7474 자재부 사원 960000 108000 44 800715-1313131
1027 김영길 801216-1898752 2000-10-18 서울 019-8523-1478 총무부 과장 2340000 170000 44 801216-1898752
1025 우재옥 801103-1654442 2000-10-01 서울 010-4563-2587 영업부 사원 1100000 160000 44 801103-1654442
1021 홍길남 801010-1111111 2001-09-07 경기 011-9999-7575 개발부 사원 875000 120000 44 801010-1111111
1018 이성길 801028-1849534 2004-08-08 전북 018-1333-3333 개발부 사원 880000 123000 44 801028-1849534
1004 김정훈 790304-1788896 2000-10-01 전북 019-5236-4221 영업부 대리 1954200 170000 45 790304-1788896
1048 이기상 790604-1415141 2001-06-07 전남 개발부 대리 2050000 106000 45 790604-1415141
1040 박세열 790509-1635214 2000-09-10 경북 016-4444-7777 인사부 대리 2100000 130000 45 790509-1635214
1047 산마루 780505-1234567 2001-07-15 서울 018-0505-0505 영업부 대리 2100000 112000 46 780505-1234567
1016 이상헌 781010-1666678 2001-11-29 경기 010-4526-1234 개발부 과장 2350000 150000 46 781010-1666678
1007 장인철 780506-1625148 1998-03-16 제주 011-2345-2525 개발부 대리 1250000 150000 46 780506-1625148
1019 박문수 780710-1985632 1999-12-10 서울 017-4747-4848 인사부 과장 2300000 165000 46 780710-1985632
1031 지재환 771115-1687988 2001-01-21 서울 019-5552-7511 기획부 부장 2450000 160000 47 771115-1687988
1001 홍길동 771212-1022432 1998-10-11 서울 011-2356-4528 기획부 부장 2610000 200000 47 771212-1022432
1037 최석규 770129-1456987 1998-10-15 인천 011-7777-7777 홍보부 과장 2350000 187000 47 770129-1456987
1012 정한국 760909-1333333 1999-10-16 강원 018-2222-4242 홍보부 사원 880000 114000 48 760909-1333333
1046 허경운 760105-1458752 1999-05-04 경남 017-3333-3333 총무부 부장 2650000 150000 48 760105-1458752
1010 김종서 751010-1122233 1997-08-08 부산 011-3214-5555 영업부 부장 2540000 130000 49 751010-1122233
1023 김인수 731211-1214576 1995-02-23 서울 영업부 부장 2500000 170000 51 731211-1214576
1041 문길수 721217-1951357 2001-12-10 충남 016-4444-5555 자재부 과장 2300000 150000 52 721217-1951357
1045 홍원신 690906-1985214 2003-03-16 전북 011-7777-7777 영업부 사원 960000 152000 55 690906-1985214
*/
-- 29. 서울 지역 사람들 중에서 입사일이 빠른 사람을 먼저 볼 수 있도록 조회.
SELECT *
FROM TBL_INSA
WHERE CITY LIKE '서울'
ORDER BY IBSADATE ASC;
--==>>
/*
1023 김인수 731211-1214576 1995-02-23 서울 영업부 부장 2500000 170000
1033 김미나 780505-2999999 1998-06-07 서울 011-2444-4444 영업부 사원 1020000 104000
1001 홍길동 771212-1022432 1998-10-11 서울 011-2356-4528 기획부 부장 2610000 200000
1024 김말자 830225-2633334 1999-08-28 서울 011-5248-7789 기획부 대리 1900000 170000
1019 박문수 780710-1985632 1999-12-10 서울 017-4747-4848 인사부 과장 2300000 165000
1051 권영미 790303-2155554 2000-06-04 서울 011-5555-7548 영업부 과장 2260000 104000
1011 유관순 801010-2987897 2000-07-07 서울 010-8888-4422 영업부 사원 1020000 140000
1029 김말숙 800301-2020202 2000-09-08 서울 016-3535-3636 총무부 사원 920000 124000
1025 우재옥 801103-1654442 2000-10-01 서울 010-4563-2587 영업부 사원 1100000 160000
1027 김영길 801216-1898752 2000-10-18 서울 019-8523-1478 총무부 과장 2340000 170000
1031 지재환 771115-1687988 2001-01-21 서울 019-5552-7511 기획부 부장 2450000 160000
1047 산마루 780505-1234567 2001-07-15 서울 018-0505-0505 영업부 대리 2100000 112000
1059 임수봉 810809-2121244 2001-10-10 서울 011-4151-4154 개발부 사원 890000 102000
1060 김신애 810809-2111111 2001-10-10 서울 011-4151-4444 개발부 사원 900000 102000
1008 김영년 821011-2362514 2002-04-30 서울 016-2222-4444 홍보부 사원 950000 145000
1036 이재영 701126-2852147 2003-08-10 서울 011-9999-9999 자재부 사원 960400 190000
1043 양미옥 830504-2471523 2003-09-24 서울 016-8548-6547 영업부 사원 1100000 210000
1044 지수환 820305-1475286 2004-01-21 서울 011-5555-7548 영업부 사원 1060000 220000
1055 정한나 820506-2425153 2004-06-07 서울 016-2424-4242 영업부 사원 1000000 104000
1005 한석봉 811112-1566789 2004-08-13 서울 018-5211-3542 총무부 사원 1420000 160000
*/
--30. 성씨가 김씨가 아닌 사람 조회.
-- 단, 성씨는 한 글자라고 가정.
-- ( 이름, 출신도, 기본급 );
SELECT NAME "이름", CITY "출신도", BASICPAY "기본급"
FROM TBL_INSA
WHERE SUBSTR(NAME, 1, 1) != '김';
--==>>
/*
홍길동 서울 2610000
이순신 경기 1320000
이순애 인천 2550000
한석봉 서울 1420000
이기자 인천 2265000
장인철 제주 1250000
나윤균 경기 840000
유관순 서울 1020000
정한국 강원 880000
조미숙 경기 1601000
황진이 인천 1100000
이현숙 경기 1050000
이상헌 경기 2350000
엄용수 인천 950000
이성길 전북 880000
박문수 서울 2300000
유영희 전남 880000
홍길남 경기 875000
이영숙 전남 1960000
우재옥 서울 1100000
이남신 제주 892000
정정해 부산 2304000
지재환 서울 2450000
심심해 전북 880000
이정석 경기 1100000
정영희 인천 1050000
이재영 서울 960400
최석규 인천 2350000
손인수 부산 2000000
고순정 경기 2010000
박세열 경북 2100000
문길수 충남 2300000
채정희 경기 1020000
양미옥 서울 1100000
지수환 서울 1060000
홍원신 전북 960000
허경운 경남 2650000
산마루 서울 2100000
이기상 전남 2050000
이미성 인천 1300000
이미인 경기 1950000
권영미 서울 2260000
권옥경 경기 1020000
정상호 강원 980000
정한나 서울 1000000
전용재 인천 1950000
이미경 경기 2520000
임수봉 서울 890000
*/
--31. 출신도가 서울, 부산, 대구 이면서
-- 전화번호에 5 또는 7이 포함된 데이터를 조회하되
-- 부서명의 마지막 부는 출력되지 않도록함. (개발부 → 개발)
-- ( 이름, 출신도, 부서명, 전화번호 );
-- #1 출신도가 서울, 부산, 대구
SELECT NAME "이름", CITY "출신도", BUSEO "부서", TEL "전화번호"
FROM TBL_INSA
WHERE CITY IN ('서울', '부산', '대구');
-- #2 전화번호에 5 또는 7이 포함
SELECT TEL
FROM TBL_INSA
WHERE SUBSTR(TEL, 5, 9) LIKE '%5%'
OR
SUBSTR(TEL, 5, 9) LIKE '%7%';
-- #3 부서명의 마지막 부는 출력되지 않도록함. (개발부 → 개발)
SELECT SUBSTR(BUSEO, 1, 2) "부서"
FROM TBL_INSA;
-- #4 최종 결과
SELECT NAME "이름", CITY "출신도", SUBSTR(BUSEO, 1, 2) "부서명", TEL "전화번호"
FROM TBL_INSA
WHERE CITY IN ('서울', '부산', '대구')
AND
SUBSTR(TEL, 5, 9) LIKE '%5%'
OR
SUBSTR(TEL, 5, 9) LIKE '%7%';
--==>>
/*
홍길동 서울 기획 011-2356-4528
이순신 경기 총무 010-4758-6532
한석봉 서울 총무 018-5211-3542
이기자 인천 개발 010-3214-5357
김종서 부산 영업 011-3214-5555
황진이 인천 개발 010-3214-5467
박문수 서울 인사 017-4747-4848
홍길남 경기 개발 011-9999-7575
김말자 서울 기획 011-5248-7789
우재옥 서울 영업 010-4563-2587
김영길 서울 총무 019-8523-1478
김말숙 서울 총무 016-3535-3636
정정해 부산 총무 019-6564-6752
지재환 서울 기획 019-5552-7511
심심해 전북 자재 016-8888-7474
이정석 경기 기획 011-3697-7412
최석규 인천 홍보 011-7777-7777
손인수 부산 영업 010-6542-7412
고순정 경기 영업 010-2587-7895
박세열 경북 인사 016-4444-7777
양미옥 서울 영업 016-8548-6547
지수환 서울 영업 011-5555-7548
홍원신 전북 영업 011-7777-7777
산마루 서울 영업 018-0505-0505
권영미 서울 영업 011-5555-7548
권옥경 경기 기획 010-3644-5577
김싱식 전북 자재 011-7585-7474
전용재 인천 영업 010-7549-8654
이미경 경기 자재 016-6542-7546
임수봉 서울 개발 011-4151-4154
김신애 서울 개발 011-4151-4444
*/
--32. 전화번호가 있으면 '-'을 제거하여 조회하고,
-- 없으면 '전화번호없음'으로 조회.
--SELECT TEL
--FROM TBL_INSA;
-- 전화번호 쪼개서 '-' 없애기
--SELECT SUBSTR(TEL, 0, 3) || SUBSTR(TEL, 5, 4) || SUBSTR(TEL, 10, 4) "전화번호"
--FROM TBL_INSA;
SELECT NVL(T.전화번호, '전화번호없음') "전화번호"
FROM
(
SELECT SUBSTR(TEL, 0, 3) || SUBSTR(TEL, 5, 4) || SUBSTR(TEL, 10, 4) "전화번호"
FROM TBL_INSA
) T;
--==>>
/*
01123564528
01047586532
01042311236
01952364221
01852113542
01032145357
01123452525
01622224444
01911112222
01132145555
01088884422
01822224242
01966664444
01032145467
01625483365
01045261234
01032542542
01813333333
01747474848
01195958585
01199997575
01752145282
전화번호없음
01152487789
01045632587
01021125225
01985231478
01618184848
01635353636
01965646752
01955527511
01688887474
01124444444
01136977412
전화번호없음
01199999999
01177777777
01065427412
01025877895
01644447777
01644445555
01151255511
01685486547
01155557548
01177777777
01733333333
01805050505
전화번호없음
01066548854
01185855252
01155557548
01036445577
01175857474
01619194242
01624244242
01075498654
01665427546
01024155444
01141514154
01141514444
*/
추가문제. (기본 문제 풀이가 모두 끝난 후 작성한다.)
HR계정의 EMPLOYEES 테이블에서 커미션 받는 사람의 수와
안받는 사람의 수를 조회한다.
출력형태 ---------------
구분 인원수
------------------------
커미션받는사원 XXX
커미션없는사원 XXX
모든사원 XXX
--33. TBL_INSA 테이블에서 BASICPAY + SUDANG 이
-- 100만원 미만, 100만원 이상~200만원 미만,
-- 200만원 이상인 직원들의 수 조회.
--SELECT BASICPAY + SUDANG "급여"
--FROM TBL_INSA;
--테스트
--SELECT ROUND(BASICPAY + SUDANG, -6) "급여"
-- , BASICPAY + SUDANG "이게 진짜 급여"
--FROM TBL_INSA;
SELECT T.급여
, COUNT(*) "해당급여를받는직원수"
FROM
(
SELECT ROUND(BASICPAY + SUDANG, -6) "급여"
, SSN "주민번호"
FROM TBL_INSA
)T
GROUP BY T.급여;
--==>>
/*
1000000 31
3000000 10
2000000 19
*/
--34. TBL_INSA 테이블에서 주민번호를 가지고 생년월일의 ★년도별★ 직원수 조회.
-- 생년월일 'YYYY-MM-DD' 형식으로 구하기
--SELECT CONCAT('19', SUBSTR(SSN, 1, 6)) "생년월일"
--FROM TBL_INSA
--WHERE SUBSTR(SSN, 8, 1) IN ('1', '2');
-- 아 년도별이지... 아 죄송해요...ㅠㅠ
--SELECT BIRTH.생년월일, COUNT(*)
--FROM
--(
-- SELECT CONCAT('19', SUBSTR(SSN, 1, 6)) "생년월일"
-- FROM TBL_INSA
-- WHERE SUBSTR(SSN, 8, 1) IN ('1', '2')
--) BIRTH
--GROUP BY BIRTH.생년월일;
-- 생년 'YYYY' 구하기
SELECT CONCAT('19', SUBSTR(SSN, 1, 2)) "생년"
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('1', '2');
-- 생년 별 직원 수 구하기.
SELECT BIRTH.생년
, COUNT(*) "직원수"
FROM
(
SELECT CONCAT('19', SUBSTR(SSN, 1, 2)) "생년"
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('1', '2')
) BIRTH
GROUP BY BIRTH.생년;
--==>>
/*
생년 직원수
1970 1
1977 4
1980 14
1976 2
1983 4
1982 6
1969 1
1979 7
1978 7
1975 1
1972 1
1981 11
1973 1
*/
--35. 주민번호를 기준으로 월별 오름차순, 월이 같으면 년도 내림차순 조회.
-- (이름, 주민번호);
-- 월, 년도 구하기
--SELECT TO_NUMBER(SUBSTR(SSN, 3, 2)) "월"
-- , TO_NUMBER(SUBSTR(SSN, 1, 2)) "년도"
-- , SSN "주민번호" -- 테이블 결합 조건으로 쓰고 싶어서 넣었어요...
--FROM TBL_INSA;
-- (이름, 주민번호)
SELECT TI.NAME, TI.SSN
FROM TBL_INSA TI
INNER JOIN
(
SELECT TO_NUMBER(SUBSTR(SSN, 3, 2)) "월"
, TO_NUMBER(SUBSTR(SSN, 1, 2)) "년도"
, SSN "주민번호"
FROM TBL_INSA
)B
ON TI.SSN = B.주민번호
ORDER BY B.월 ASC, B.년도 DESC;
-- 주민번호를 기준으로 월별 오름차순, 월이 같으면 년도 내림차순 조회.
--==>>
/*
이남신 810101-1010101
조미숙 790102-2777777
최석규 770129-1456987
허경운 760105-1458752
김말자 830225-2633334
심심해 810206-2222222
정정해 790210-2101010
지수환 820305-1475286
유영희 800304-2741258
김말숙 800301-2020202
김정훈 790304-1788896
권영미 790303-2155554
권옥경 820406-2000456
이미인 810403-2828287
이미경 780406-2003214
양미옥 830504-2471523
엄용수 820507-1452365
정한나 820506-2425153
이정석 820505-1325468
고순정 800504-2000032
이영숙 800501-2312456
박세열 790509-1635214
산마루 780505-1234567
김미나 780505-2999999
이기자 780505-2978541
장인철 780506-1625148
전용재 800605-1456987
이현숙 800606-2954687
이기상 790604-1415141
채정희 810709-2000054
정상호 810705-1212141
황진이 810707-2574812
김신제 800709-1321456
김싱식 800715-1313131
박문수 780710-1985632
나윤균 810810-1552147
임수봉 810809-2121244
김신애 810809-2111111
이미성 830908-2456548
김숙남 810907-2015457
이순애 770922-2312547
정한국 760909-1333333
홍원신 690906-1985214
정영희 831010-2153252
김영년 821011-2362514
이성길 801028-1849534
홍길남 801010-1111111
이순신 801007-1544236
유관순 801010-2987897
손인수 791009-2321456
이상헌 781010-1666678
김종서 751010-1122233
한석봉 811112-1566789
우재옥 801103-1654442
지재환 771115-1687988
이재영 701126-2852147
김영길 801216-1898752
홍길동 771212-1022432
김인수 731211-1214576
문길수 721217-1951357
*/
--36. 입사일을 기준으로 월별 오름차순, 월이 같으면 년도 내림차순 조회.
-- 단, 모든 정보 조회.
-- (주의. 입사일은 자료형이 DATE이다.);
-- 입사일 IBSADATE
--SELECT IBSADATE
--FROM TBL_INSA;
-- 입사월, 입사년도 구하기.
--SELECT TO_CHAR(IBSADATE, 'MM') "입사월"
-- , TO_CHAR(IBSADATE, 'YYYY') "입사년도"
-- , SSN "주민번호"
--FROM TBL_INSA;
-- 단, 모든 정보 조회.
SELECT TI.*
FROM TBL_INSA TI
INNER JOIN
(
SELECT TO_CHAR(IBSADATE, 'MM') "입사월"
, TO_CHAR(IBSADATE, 'YYYY') "입사년도"
, SSN "주민번호"
FROM TBL_INSA
)IBSA
ON TI.SSN = IBSA.주민번호
ORDER BY IBSA.입사월 ASC, IBSA.입사년도 DESC;
-- 입사일을 기준으로 월별 오름차순, 월이 같으면 년도 내림차순 조회.
--==>>
/*
1044 지수환 820305-1475286 2004-01-21 서울 011-5555-7548 영업부 사원 1060000 220000
1031 지재환 771115-1687988 2001-01-21 서울 019-5552-7511 기획부 부장 2450000 160000
1022 이영숙 800501-2312456 2003-02-25 전남 017-5214-5282 기획부 대리 1960000 180000
1006 이기자 780505-2978541 2002-02-11 인천 010-3214-5357 개발부 과장 2265000 150000
1014 황진이 810707-2574812 2002-02-15 인천 010-3214-5467 개발부 사원 1100000 130000
1003 이순애 770922-2312547 1999-02-25 인천 010-4231-1236 개발부 부장 2550000 160000
1057 이미경 780406-2003214 1998-02-11 경기 016-6542-7546 자재부 부장 2520000 160000
1023 김인수 731211-1214576 1995-02-23 서울 영업부 부장 2500000 170000
1045 홍원신 690906-1985214 2003-03-16 전북 011-7777-7777 영업부 사원 960000 152000
1007 장인철 780506-1625148 1998-03-16 제주 011-2345-2525 개발부 대리 1250000 150000
1008 김영년 821011-2362514 2002-04-30 서울 016-2222-4444 홍보부 사원 950000 145000
1049 이미성 830908-2456548 2000-04-07 인천 010-6654-8854 개발부 사원 1300000 130000
1035 정영희 831010-2153252 2002-05-16 인천 개발부 사원 1050000 140000
1032 심심해 810206-2222222 2000-05-05 전북 016-8888-7474 자재부 사원 880000 108000
1046 허경운 760105-1458752 1999-05-04 경남 017-3333-3333 총무부 부장 2650000 150000
1055 정한나 820506-2425153 2004-06-07 서울 016-2424-4242 영업부 사원 1000000 104000
1050 이미인 810403-2828287 2003-06-07 경기 011-8585-5252 홍보부 대리 1950000 103000
1048 이기상 790604-1415141 2001-06-07 전남 개발부 대리 2050000 106000
1051 권영미 790303-2155554 2000-06-04 서울 011-5555-7548 영업부 과장 2260000 104000
1033 김미나 780505-2999999 1998-06-07 서울 011-2444-4444 영업부 사원 1020000 104000
1013 조미숙 790102-2777777 1998-06-07 경기 019-6666-4444 홍보부 대리 1601000 103000
1047 산마루 780505-1234567 2001-07-15 서울 018-0505-0505 영업부 대리 2100000 112000
1011 유관순 801010-2987897 2000-07-07 서울 010-8888-4422 영업부 사원 1020000 140000
1015 이현숙 800606-2954687 1999-07-26 경기 016-2548-3365 총무부 사원 1050000 104000
1056 전용재 800605-1456987 2004-08-13 인천 010-7549-8654 영업부 대리 1950000 200000
1018 이성길 801028-1849534 2004-08-08 전북 018-1333-3333 개발부 사원 880000 123000
1005 한석봉 811112-1566789 2004-08-13 서울 018-5211-3542 총무부 사원 1420000 160000
1058 김신제 800709-1321456 2003-08-08 인천 010-2415-5444 기획부 대리 1950000 180000
1036 이재영 701126-2852147 2003-08-10 서울 011-9999-9999 자재부 사원 960400 190000
1026 김숙남 810907-2015457 2002-08-28 경기 010-2112-5225 영업부 사원 1050000 150000
1017 엄용수 820507-1452365 2000-08-28 인천 010-3254-2542 개발부 사원 950000 210000
1024 김말자 830225-2633334 1999-08-28 서울 011-5248-7789 기획부 대리 1900000 170000
1010 김종서 751010-1122233 1997-08-08 부산 011-3214-5555 영업부 부장 2540000 130000
1034 이정석 820505-1325468 2005-09-26 경기 011-3697-7412 기획부 사원 1100000 160000
1043 양미옥 830504-2471523 2003-09-24 서울 016-8548-6547 영업부 사원 1100000 210000
1021 홍길남 801010-1111111 2001-09-07 경기 011-9999-7575 개발부 사원 875000 120000
1028 이남신 810101-1010101 2001-09-07 제주 016-1818-4848 인사부 사원 892000 110000
1029 김말숙 800301-2020202 2000-09-08 서울 016-3535-3636 총무부 사원 920000 124000
1040 박세열 790509-1635214 2000-09-10 경북 016-4444-7777 인사부 대리 2100000 130000
1020 유영희 800304-2741258 2003-10-10 전남 011-9595-8585 자재부 사원 880000 140000
1009 나윤균 810810-1552147 2003-10-10 경기 019-1111-2222 인사부 사원 840000 220400
1042 채정희 810709-2000054 2003-10-17 경기 011-5125-5511 개발부 사원 1020000 200000
1059 임수봉 810809-2121244 2001-10-10 서울 011-4151-4154 개발부 사원 890000 102000
1060 김신애 810809-2111111 2001-10-10 서울 011-4151-4444 개발부 사원 900000 102000
1025 우재옥 801103-1654442 2000-10-01 서울 010-4563-2587 영업부 사원 1100000 160000
1027 김영길 801216-1898752 2000-10-18 서울 019-8523-1478 총무부 과장 2340000 170000
1004 김정훈 790304-1788896 2000-10-01 전북 019-5236-4221 영업부 대리 1954200 170000
1052 권옥경 820406-2000456 2000-10-10 경기 010-3644-5577 기획부 사원 1020000 105000
1012 정한국 760909-1333333 1999-10-16 강원 018-2222-4242 홍보부 사원 880000 114000
1030 정정해 790210-2101010 1999-10-17 부산 019-6564-6752 총무부 과장 2304000 124000
1054 정상호 810705-1212141 1999-10-16 강원 016-1919-4242 홍보부 사원 980000 114000
1001 홍길동 771212-1022432 1998-10-11 서울 011-2356-4528 기획부 부장 2610000 200000
1037 최석규 770129-1456987 1998-10-15 인천 011-7777-7777 홍보부 과장 2350000 187000
1016 이상헌 781010-1666678 2001-11-29 경기 010-4526-1234 개발부 과장 2350000 150000
1002 이순신 801007-1544236 2000-11-29 경기 010-4758-6532 총무부 사원 1320000 200000
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
1053 김싱식 800715-1313131 1999-12-12 전북 011-7585-7474 자재부 사원 960000 108000
1019 박문수 780710-1985632 1999-12-10 서울 017-4747-4848 인사부 과장 2300000 165000
*/
--37. 전체인원수, 남자인원수, 여자인원수를 동시 조회.
-- #1 전체 인원수 구하기
SELECT COUNT(*)
FROM TBL_INSA;
-- #2 남자 인원수 구하기
SELECT COUNT(*)
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('1', '3');
-- #3 여자 인원수 구하기
SELECT COUNT(*)
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('2', '4');
-- #4 상단 서브 쿼리 스칼라 서브 쿼리 사용.
-- 전체인원수, 남자인원수, 여자인원수를 동시 조회.
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 DUAL;
--==>>
/*
전체인원수 남자인원수 여자인원수
60 31 29
*/
--38. 개발부, 영업부, 총무부 인원수 조회. COUNT(), DECODE() 함수 이용.
---- 아래와 같은 형식으로 구성해야 함...
--DECODE(BUSEO, '개발부', 개발부인원수, '영업부', 영업부인원수, '총무부', 총무부인원수);
--
--
--
--
---- #1 개발부 인원수 조회
--SELECT COUNT(*)
--FROM TBL_INSA
--WHERE BUSEO LIKE '개발부';
--
--
---- #2 영업부 인원수 조회
--SELECT COUNT(*)
--FROM TBL_INSA
--WHERE BUSEO LIKE '영업부';
--
--
---- #3 총무부 인원수 조회
--SELECT COUNT(*)
--FROM TBL_INSA
--WHERE BUSEO LIKE '총무부';
-- #4 최종 결과
-- 인원수를 나타내는 쿼리문을 하나로 통합. CROSS JOIN (92)
SELECT DISTINCT TI.BUSEO "부서명"
, DECODE(TI.BUSEO, '개발부', G.개발부인원수
, '영업부', Y.영업부인원수
, '총무부', C.총무부인원수, NULL) "부서별인원수"
-- G.개발부인원수, Y.영업부인원수, C.총무부인원수
FROM
TBL_INSA TI
,
(
SELECT COUNT(*) "개발부인원수"
FROM TBL_INSA
WHERE BUSEO LIKE '개발부'
) G
,
(
SELECT COUNT(*) "영업부인원수"
FROM TBL_INSA
WHERE BUSEO LIKE '영업부'
) Y
,
(
SELECT COUNT(*) "총무부인원수"
FROM TBL_INSA
WHERE BUSEO LIKE '총무부'
) C
-- NULL 은 결과에서 제외.
WHERE DECODE(TI.BUSEO, '개발부', G.개발부인원수
, '영업부', Y.영업부인원수
, '총무부', C.총무부인원수, NULL) IS NOT NULL
ORDER BY 1 ASC; -- 그냥 오름차순...
--39. 서울 사람의 남자 인원수 조회.
SELECT COUNT(*) "서울남자인원수"
FROM TBL_INSA
WHERE CITY LIKE '서울'
AND
SUBSTR(SSN, 8, 1) IN ('1', '3');
--==>> 9
--40. 부서가 영업부이고, 남자 인원수, 여자 인원수 조회. COUNT(), DECODE() 함수 이용.
-- #1 영업부 남자 인원수
SELECT COUNT(*) "영업부남자인원수"
FROM TBL_INSA
WHERE BUSEO LIKE '영업부'
AND
SUBSTR(SSN, 8, 1) IN ('1', '3');
-- #2 영업부 여자 인원수
SELECT COUNT(*) "영업부여자인원수"
FROM TBL_INSA
WHERE BUSEO LIKE '영업부'
AND
SUBSTR(SSN, 8, 1) IN ('2', '4');
SELECT DISTINCT CASE WHEN SUBSTR(TI.SSN, 8, 1) IN ('1', '3')
THEN '남자'
WHEN SUBSTR(TI.SSN, 8, 1) IN ('2', '4')
THEN '여자'
ELSE '알수없음'
END "성별"
, DECODE(SUBSTR(TI.SSN, 8, 1), '1', YM.영업부남자인원수
, '2', YW.영업부여자인원수
, NULL) "영업부남녀인원수"
FROM TBL_INSA TI
,
(
SELECT COUNT(*) "영업부남자인원수"
FROM TBL_INSA
WHERE BUSEO LIKE '영업부'
AND
SUBSTR(SSN, 8, 1) IN ('1', '3')
) YM
,
(
SELECT COUNT(*) "영업부여자인원수"
FROM TBL_INSA
WHERE BUSEO LIKE '영업부'
AND
SUBSTR(SSN, 8, 1) IN ('2', '4')
) YW;
--==>>
/*
성별 영업부남녀인원수
남자 8
여자 8
*/
-- 취소취소
---- 주민번호 8번째 자리가 1이면 남자, 2이면 여자.
--SELECT DECODE(SUBSTR(SSN, 8, 1), '1' OR '3', 남자)
-- , 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 LIKE '영업부'
--GROUP BY SUBSTR(SSN, 8, 1)
--ORDER BY SUBSTR(SSN, 8, 1) ASC;
--
--
--
--SELECT DECODE(SUBSTR(SSN, 8, 1), '1', '남자')
--FROM TBL_INSA;
--41. 개발부, 영업부, 총무부 인원수 조회. 단, 지역은 '서울'로 한정.
-- #1 서울 개발부 인원수 조회
SELECT COUNT(*)
FROM TBL_INSA
WHERE BUSEO LIKE '개발부'
AND
CITY LIKE '서울';
--==>> 2
-- #2 서울 영업부 인원수 조회
SELECT COUNT(*)
FROM TBL_INSA
WHERE BUSEO LIKE '영업부'
AND
CITY LIKE '서울';
--==>> 9
-- #3 서울 총무부 인원수 조회
SELECT COUNT(*)
FROM TBL_INSA
WHERE BUSEO LIKE '총무부'
AND
CITY LIKE '서울';
--==>> 3
-- #4 위의 쿼리문을 서브 상단 쿼리로 이용.
SELECT
(
SELECT COUNT(*)
FROM TBL_INSA
WHERE BUSEO LIKE '개발부'
AND
CITY LIKE '서울'
) "서울개발부인원수"
,
(
SELECT COUNT(*)
FROM TBL_INSA
WHERE BUSEO LIKE '영업부'
AND
CITY LIKE '서울'
) "서울영업부인원수"
,
(
SELECT COUNT(*)
FROM TBL_INSA
WHERE BUSEO LIKE '총무부'
AND
CITY LIKE '서울'
) "서울총무부인원수"
FROM DUAL;
--==>>
/*
서울개발부인원수 서울영업부인원수 서울총무부인원수
2 9 3
*/
--42. 서울 사람의 남자와 여자의 기본급합 조회.
--SELECT
--FROM TBL_INSA
--WHERE SUBSTR(SSN, 8, 1) IN ('1', '3');
--SELECT CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '3')
-- THEN '남자'
-- WHEN SUBSTR(SSN, 8, 1) IN ('2', '4')
-- THEN '여자'
-- ELSE '알수없음'
-- END "성별"
--FROM TBL_INSA
-- 취소취소취소
--SELECT GD.성별
--FROM TBL_INSA TI
--INNER JOIN
--(
-- SELECT CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '3')
-- THEN '남자'
-- WHEN SUBSTR(SSN, 8, 1) IN ('2', '4')
-- THEN '여자'
-- ELSE '알수없음'
-- END "성별"
-- , SSN "주민번호"
-- FROM TBL_INSA
--) GD
--ON TI.SSN = GD.주민번호
--GROUP BY GD.성별;
-- 서울 사람의 남자와 여자의 기본급합 조회.
-- #1 서울 사람의 남자 기본급합
SELECT SUM(BASICPAY)
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('1', '3')
AND
CITY LIKE '서울';
-- #2 서울 사람의 여자 기본급합
SELECT SUM(BASICPAY)
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('2', '4')
AND
CITY LIKE '서울';
-- #3 상단 서브 쿼리(스칼라 서브 쿼리)로 구성.
SELECT (
SELECT SUM(BASICPAY)
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('1', '3')
AND
CITY LIKE '서울'
) "서울남자기본급합"
,
(
SELECT SUM(BASICPAY)
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('2', '4')
AND
CITY LIKE '서울'
) "서울여자기본급합"
FROM DUAL;
--==>>
/*
서울남자기본급합 서울여자기본급합
17880000 12920400
*/
--43. 남자와 여자의 기본급 평균값 조회. AVG(), DECODE() 함수 이용.
-- #1 남자의 기본급 평균값 (소수점 두 번째 자리까지 표현)
SELECT ROUND(AVG(BASICPAY), 2)
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('1', '3');
--==>> 1676167.74
-- #2 여자의 기본급 평균값 (소수점 두 번째 자리까지 표현)
SELECT ROUND(AVG(BASICPAY), 2)
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('2', '4');
--==>> 1428634.48
-- #3 최종 결과
-- 두 쿼리문을 외부(메인)쿼리문에 INNER JOIN 한다.
-- 남자와 여자의 기본급 평균값 조회. AVG(), DECODE() 함수 이용.
-- DECODE() 를 다음과 같이 구성해야함.
-- DECODE(SUBSTR(SSN, 8, 1), '1', 서울남자평균기본급, '2', 서울여자평균기본급)
SELECT DISTINCT
CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '3')
THEN '남자'
WHEN SUBSTR(SSN, 8, 1) IN ('2', '4')
THEN '여자'
ELSE '알수없음'
END "성별"
, DECODE(SUBSTR(SSN, 8, 1), '1', SMP.서울남자평균기본급, '2', SWP.서울여자평균기본급) "서울성별기본급"
FROM TBL_INSA TI
,
(
SELECT ROUND(AVG(BASICPAY), 2) "서울남자평균기본급"
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('1', '3')
) SMP
,
(
SELECT ROUND(AVG(BASICPAY), 2) "서울여자평균기본급"
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('2', '4')
) SWP;
--==>>
/*
성별 서울성별기본급
남자 1676167.74
여자 1428634.48
*/
--44. 개발부의 남자, 여자 기본급 평균값 조회.
-- #1 개발부, 성별로 그룹화
SELECT BUSEO
, CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '3')
THEN '남자'
WHEN SUBSTR(SSN, 8, 1) IN ('2', '4')
THEN '여자'
ELSE '알수없음'
END "성별"
, AVG(BASICPAY) "기본급 평균값"
FROM TBL_INSA
GROUP BY BUSEO, SUBSTR(SSN, 8, 1)
HAVING BUSEO LIKE '개발부';
--==>>
/*
BUSEO 성별 기본급 평균값
개발부 여자 1384375
개발부 남자 1392500
*/
--45. 부서별 남자와 여자 인원수 구하기
-- #1 부서, 성별로 그룹화.
SELECT BUSEO "부서명"
, 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 BUSEO, SUBSTR(SSN, 8, 1)
ORDER BY BUSEO ASC;
--==>>
/*
부서명 성별 인원수
개발부 남자 6
개발부 여자 8
기획부 남자 4
기획부 여자 3
영업부 남자 8
영업부 여자 8
인사부 남자 4
자재부 남자 2
자재부 여자 4
총무부 남자 4
총무부 여자 3
홍보부 남자 3
홍보부 여자 3
*/
--46. 지역별 남자와 여자 인원수 구하기
SELECT CITY "출신도"
, 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 CITY, SUBSTR(SSN, 8, 1)
ORDER BY CITY ASC;
--==>>
/*
출신도 성별 인원수
강원 남자 2
경기 남자 5
경기 여자 8
경남 남자 1
경북 남자 1
부산 남자 1
부산 여자 2
서울 남자 9
서울 여자 11
인천 남자 4
인천 여자 5
전남 남자 1
전남 여자 2
전북 남자 4
전북 여자 1
제주 남자 2
충남 남자 1
*/
--47. 입사년도별 남자와 여자 인원수 구하기
SELECT *
FROM TBL_INSA;
SELECT EXTRACT(YEAR FROM IBSADATE) "입사년도"
, 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 EXTRACT(YEAR FROM IBSADATE), SUBSTR(SSN, 8, 1)
ORDER BY EXTRACT(YEAR FROM IBSADATE) ASC;
--==>>
/*
입사년도 성별 인원수
1995 남자 1
1997 남자 1
1998 남자 3
1998 여자 3
1999 남자 5
1999 여자 5
2000 남자 6
2000 여자 6
2001 남자 7
2001 여자 2
2002 여자 5
2003 남자 3
2003 여자 7
2004 남자 4
2004 여자 1
2005 남자 1
*/
--48. 영업부, 총무부 인원만을 가지고 입사년도별 남자와 여자 인원수 구하기
SELECT
EXTRACT(YEAR FROM IBSADATE) "입사년도"
, CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '3')
THEN '남자'
WHEN SUBSTR(SSN, 8, 1) IN ('2', '4')
THEN '여자'
ELSE '알수없음'
END "성별"
-- , BUSEO "부서가 맞는지 확인"
, COUNT(*) "인원수"
FROM TBL_INSA
WHERE BUSEO IN ('영업부', '총무부')
GROUP BY EXTRACT(YEAR FROM IBSADATE), SUBSTR(SSN, 8, 1)
ORDER BY EXTRACT(YEAR FROM IBSADATE) ASC;
--==>>
/*
입사년도 성별 인원수
1995 남자 1
1997 남자 1
1998 여자 1
1999 남자 1
1999 여자 3
2000 남자 4
2000 여자 3
2001 남자 1
2002 여자 1
2003 남자 1
2003 여자 2
2004 남자 3
2004 여자 1
*/
--49. 서울 사람중 부서별 남자와 여자인원수, 남자와 여자 급여합 조회.
SELECT BUSEO "부서명"
, CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '3')
THEN '남자'
WHEN SUBSTR(SSN, 8, 1) IN ('2', '4')
THEN '여자'
ELSE '알수없음'
END "성별"
, COUNT(*) "인원수"
, SUM(BASICPAY + SUDANG) "급여합"
FROM TBL_INSA
WHERE CITY LIKE '서울'
GROUP BY BUSEO, SUBSTR(SSN, 8, 1)
ORDER BY 부서명 ASC, 성별 ASC;
--==>>
/*
부서명 성별 인원수 급여합
개발부 여자 2 1994000
기획부 남자 2 5420000
기획부 여자 1 2070000
영업부 남자 4 7422000
영업부 여자 5 7062000
인사부 남자 1 2465000
자재부 여자 1 1150400
총무부 남자 2 4090000
총무부 여자 1 1044000
홍보부 여자 1 1095000
*/
--50. 부서별 인원수 출력. 인원수가 10 이상인 경우만.
SELECT BUSEO "부서명"
, COUNT(*) "인원수"
FROM TBL_INSA
GROUP BY BUSEO
HAVING COUNT(*) >= 10;
--==>>
/*
부서명 인원수
개발부 14
영업부 16
*/
--51. 부서별 남,여 인원수 출력. 여자인원수가 5명 이상인 부서만 조회.
-- #1 부서들 중, 여자의 인원 수가 5명 이상인 부서명 찾기.
SELECT INWON.부서명, INWON.성별, COUNT(INWON.성별) "인원수"
FROM
(
SELECT BUSEO "부서명"
, CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '3')
THEN '남자'
WHEN SUBSTR(SSN, 8, 1) IN ('2', '4')
THEN '여자'
ELSE '알수없음'
END "성별"
FROM TBL_INSA
) INWON
GROUP BY INWON.부서명, INWON.성별
HAVING INWON.성별 = '여자'
AND
COUNT(INWON.성별) >= 5;
--==>>
/*
부서명 성별 인원수
영업부 여자 8
개발부 여자 8
*/
-- #2 위의 쿼리를 다시 서브쿼리로 삼아서, 부서명만 SELECT 하도록 처리.
SELECT TAKEBUSEO.부서명
FROM
(
SELECT INWON.부서명, INWON.성별, COUNT(INWON.성별) "인원수"
FROM
(
SELECT BUSEO "부서명"
, CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '3')
THEN '남자'
WHEN SUBSTR(SSN, 8, 1) IN ('2', '4')
THEN '여자'
ELSE '알수없음'
END "성별"
FROM TBL_INSA
) INWON
GROUP BY INWON.부서명, INWON.성별
HAVING INWON.성별 = '여자'
AND
COUNT(INWON.성별) >= 5
) TAKEBUSEO;
--==>>
/*
부서명
영업부
개발부
*/
-- #3 최종적으로, 여자 인원수가 5명 이상인 부서별 남, 여 인원수 조회.
-- 부서별 남,여 인원수 출력. 여자인원수가 5명 이상인 부서만 조회.
SELECT INWON.부서명, INWON.성별, COUNT(INWON.성별) "인원수"
FROM
(
SELECT BUSEO "부서명"
, CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '3')
THEN '남자'
WHEN SUBSTR(SSN, 8, 1) IN ('2', '4')
THEN '여자'
ELSE '알수없음'
END "성별"
FROM TBL_INSA
WHERE BUSEO
IN
(
SELECT TAKEBUSEO.부서명
FROM
(
SELECT INWON.부서명, INWON.성별, COUNT(INWON.성별) "인원수"
FROM
(
SELECT BUSEO "부서명"
, CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '3')
THEN '남자'
WHEN SUBSTR(SSN, 8, 1) IN ('2', '4')
THEN '여자'
ELSE '알수없음'
END "성별"
FROM TBL_INSA
) INWON
GROUP BY INWON.부서명, INWON.성별
HAVING INWON.성별 = '여자'
AND
COUNT(INWON.성별) >= 5
) TAKEBUSEO
)
) INWON
GROUP BY INWON.부서명, INWON.성별;
--==>>
/*
부서명 성별 인원수
영업부 여자 8
개발부 남자 6
영업부 남자 8
개발부 여자 8
*/
--52. 이름, 성별, 나이 조회
-- 성별: 주민번호 활용 1,3 → 남자, 2,4 → 여자 (DECODE() 사용)
-- 나이: 주민번호 활용
-- #1 이름 구하기
SELECT NAME "이름"
FROM TBL_INSA;
-- #2 성별 구하기 DECODE() 사용.
SELECT DECODE(SUBSTR(SSN, 8, 1), '1', '남자', '3', '남자', '2', '여자', '4', '여자')
FROM TBL_INSA;
-- #3 나이 구하기
SELECT CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '2')
THEN
EXTRACT(YEAR FROM SYSDATE) - 1900 - TO_NUMBER(SUBSTR(SSN, 1, 2)) + 1
WHEN SUBSTR(SSN, 8, 1) IN ('3', '4')
THEN
EXTRACT(YEAR FROM SYSDATE) - 2000 - TO_NUMBER(SUBSTR(SSN, 1, 2)) + 1
END "나이"
FROM TBL_INSA;
-- #4 하나로 통합.
-- 이름, 성별, 나이 조회
SELECT NAME "이름"
, DECODE(SUBSTR(SSN, 8, 1), '1', '남자', '3', '남자', '2', '여자', '4', '여자') "성별"
, CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '2')
THEN
EXTRACT(YEAR FROM SYSDATE) - 1900 - TO_NUMBER(SUBSTR(SSN, 1, 2)) + 1
WHEN SUBSTR(SSN, 8, 1) IN ('3', '4')
THEN
EXTRACT(YEAR FROM SYSDATE) - 2000 - TO_NUMBER(SUBSTR(SSN, 1, 2)) + 1
END "나이"
FROM TBL_INSA;
--==>>
/*
이름 성별 나이
홍길동 남자 47
이순신 남자 44
이순애 여자 47
김정훈 남자 45
한석봉 남자 43
이기자 여자 46
장인철 남자 46
김영년 여자 42
나윤균 남자 43
김종서 남자 49
유관순 여자 44
정한국 남자 48
조미숙 여자 45
황진이 여자 43
이현숙 여자 44
이상헌 남자 46
엄용수 남자 42
이성길 남자 44
박문수 남자 46
유영희 여자 44
홍길남 남자 44
이영숙 여자 44
김인수 남자 51
김말자 여자 41
우재옥 남자 44
김숙남 여자 43
김영길 남자 44
이남신 남자 43
김말숙 여자 44
정정해 여자 45
지재환 남자 47
심심해 여자 43
김미나 여자 46
이정석 남자 42
정영희 여자 41
이재영 여자 54
최석규 남자 47
손인수 여자 45
고순정 여자 44
박세열 남자 45
문길수 남자 52
채정희 여자 43
양미옥 여자 41
지수환 남자 42
홍원신 남자 55
허경운 남자 48
산마루 남자 46
이기상 남자 45
이미성 여자 41
이미인 여자 43
권영미 여자 45
권옥경 여자 42
김싱식 남자 44
정상호 남자 43
정한나 여자 42
전용재 남자 44
이미경 여자 46
김신제 남자 44
임수봉 여자 43
김신애 여자 43
*/
53. 서울 사람 중에서 기본급이 200만원 이상인 사람 조회.
( 이름, 기본급 );
SELECT NAME "이름", BASICPAY "기본급"
FROM TBL_INSA
WHERE CITY LIKE '서울'
AND
BASICPAY >= 2000000;
--==>>
/*
이름 기본급
홍길동 2610000
박문수 2300000
김인수 2500000
김영길 2340000
지재환 2450000
산마루 2100000
권영미 2260000
*/
--54. 입사월별 인원수 구하기. (월, 인원수) COUNT, GROUP BY, TO_CHAR 사용
-- 출력형태 ----------
-- 월 인원수
-- 1월 10명
-- 2월 25명
-- #1 입사월 구해보기 (TO_CHAR() 사용.)
SELECT TO_NUMBER(TO_CHAR(IBSADATE, 'MM')) "입사월"
FROM TBL_INSA;
-- #2-1 그룹화하여 인원수 구하기
-- #2-2 입사월 형태 바꾸기 (1월, 2월, ...)
SELECT TO_NUMBER(TO_CHAR(IBSADATE, 'MM')) || '월' "입사월"
, COUNT(*) "인원수"
FROM TBL_INSA
GROUP BY TO_NUMBER(TO_CHAR(IBSADATE, 'MM')) || '월'
ORDER BY 입사월 ASC;
--==>>
/*
입사월 인원수
10월 14
11월 3
12월 4
1월 2
2월 6
3월 2
4월 2
5월 3
6월 6
7월 3
8월 9
9월 6
*/
--55. 이름, 생년월일, 기본급, 수당을 조회.
-- 생년월일은 주민번호 기준 (2000-10-10 형식으로 출력)
-- 기본급은 \1,000,000 형식으로 출력
--
--
--
--
--SELECT NAME "이름"
--FROM TBL_INSA;
--
--SELECT TO_DATE('19' || SUBSTR(SSN, 1, 6), 'YYYY-MM-DD')
--FROM TBL_INSA;
--
--SELECT TO_CHAR(BASICPAY, '9,999,999')
--FROM TBL_INSA;
SELECT NAME "이름"
, TO_DATE('19' || SUBSTR(SSN, 1, 6), 'YYYY-MM-DD') "생년월일"
, TO_CHAR(BASICPAY, '9,999,999') "기본급"
, 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 950,000 145000
나윤균 1981-08-10 840,000 220400
김종서 1975-10-10 2,540,000 130000
유관순 1980-10-10 1,020,000 140000
정한국 1976-09-09 880,000 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 950,000 210000
이성길 1980-10-28 880,000 123000
박문수 1978-07-10 2,300,000 165000
유영희 1980-03-04 880,000 140000
홍길남 1980-10-10 875,000 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 892,000 110000
김말숙 1980-03-01 920,000 124000
정정해 1979-02-10 2,304,000 124000
지재환 1977-11-15 2,450,000 160000
심심해 1981-02-06 880,000 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 960,400 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 960,000 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 960,000 108000
정상호 1981-07-05 980,000 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 890,000 102000
김신애 1981-08-09 900,000 102000
*/
--56. 이름, 출신도, 기본급을 조회하되 출신도 내림차순 출력(1차 정렬 기준).
-- 출신도가 같으면 기본급 오름차순 출력(2차 정렬 기준).
SELECT NAME "이름", CITY "출신도", BASICPAY "기본급"
FROM TBL_INSA
ORDER BY 출신도 DESC, 기본급 ASC;
--==>>
/*
이름 출신도 기본급
문길수 충남 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 NAME "이름", TEL "전화번호"
FROM TBL_INSA
WHERE TEL IS NOT NULL;
--==>>
/*
이름 전화번호
홍길동 011-2356-4528
이순신 010-4758-6532
이순애 010-4231-1236
김정훈 019-5236-4221
한석봉 018-5211-3542
이기자 010-3214-5357
장인철 011-2345-2525
김영년 016-2222-4444
나윤균 019-1111-2222
김종서 011-3214-5555
유관순 010-8888-4422
정한국 018-2222-4242
조미숙 019-6666-4444
황진이 010-3214-5467
이현숙 016-2548-3365
이상헌 010-4526-1234
엄용수 010-3254-2542
이성길 018-1333-3333
박문수 017-4747-4848
유영희 011-9595-8585
홍길남 011-9999-7575
이영숙 017-5214-5282
김말자 011-5248-7789
우재옥 010-4563-2587
김숙남 010-2112-5225
김영길 019-8523-1478
이남신 016-1818-4848
김말숙 016-3535-3636
정정해 019-6564-6752
지재환 019-5552-7511
심심해 016-8888-7474
김미나 011-2444-4444
이정석 011-3697-7412
이재영 011-9999-9999
최석규 011-7777-7777
손인수 010-6542-7412
고순정 010-2587-7895
박세열 016-4444-7777
문길수 016-4444-5555
채정희 011-5125-5511
양미옥 016-8548-6547
지수환 011-5555-7548
홍원신 011-7777-7777
허경운 017-3333-3333
산마루 018-0505-0505
이미성 010-6654-8854
이미인 011-8585-5252
권영미 011-5555-7548
권옥경 010-3644-5577
김싱식 011-7585-7474
정상호 016-1919-4242
정한나 016-2424-4242
전용재 010-7549-8654
이미경 016-6542-7546
김신제 010-2415-5444
임수봉 011-4151-4154
김신애 011-4151-4444
*/
--58. 근무년수가 10년 이상인 사람 조회. (이름, 입사일)
SELECT NAME "이름", IBSADATE "입사일"
FROM TBL_INSA
WHERE EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM IBSADATE) >= 10;
--==>>
/*
이름 입사일
홍길동 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 (EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM IBSADATE))
BETWEEN
5 AND 10;
--==>> 조회 결과 없음.
--61. 김씨, 이씨, 박씨만 조회 (이름, 부서). SUBSTR() 함수 이용.
SELECT NAME "이름", BUSEO "부서"
FROM TBL_INSA
WHERE SUBSTR(NAME, 1, 1) IN ('김', '이', '박');
--==>>
/*
이름 부서
이순신 총무부
이순애 개발부
김정훈 영업부
이기자 개발부
김영년 홍보부
김종서 영업부
이현숙 총무부
이상헌 개발부
이성길 개발부
박문수 인사부
이영숙 기획부
김인수 영업부
김말자 기획부
김숙남 영업부
김영길 총무부
이남신 인사부
김말숙 총무부
김미나 영업부
이정석 기획부
이재영 자재부
박세열 인사부
이기상 개발부
이미성 개발부
이미인 홍보부
김싱식 자재부
이미경 자재부
김신제 기획부
김신애 개발부
*/
--62. 입사일을 "년-월-일 요일" 형식으로 남자만 조회 (이름, 주민번호, 입사일)
--SELECT TO_CHAR(IBSADATE, 'YYYY') || '년-' ||
-- TO_CHAR(IBSADATE, 'MM') || '월-' ||
-- TO_CHAR(IBSADATE, 'DD') || '일-' ||
-- TO_CHAR(IBSADATE, 'DAY') "입사일"
--
--FROM TBL_INSA;
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. 부서별 직위별 급여합 구하기. (부서, 직위, 급여합)
-- #1 부서별 급여합
SELECT BUSEO "부서명"
, SUM(BASICPAY + SUDANG) "급여합"
FROM TBL_INSA
GROUP BY BUSEO;
-- #2 직위별 급여합
SELECT JIKWI "직위명"
, SUM(BASICPAY + SUDANG) "급여합"
FROM TBL_INSA
GROUP BY JIKWI;
SELECT BP.부서명, BP.급여합, JP.직위명, JP.급여합
FROM
(
SELECT BUSEO "부서명"
, SUM(BASICPAY + SUDANG) "급여합"
FROM TBL_INSA
GROUP BY BUSEO
) BP
INNER JOIN
(
SELECT JIKWI "직위명"
, BUSEO "부서명"
, SUM(BASICPAY + SUDANG) "급여합"
FROM TBL_INSA
GROUP BY JIKWI, BUSEO
) JP
ON BP.부서명 = JP.부서명
ORDER BY BP.부서명 ASC, JP.직위명 ASC;
--==>>
/*
부서명 급여합 직위명 급여합_1
개발부 21403000 과장 4915000
개발부 21403000 대리 3556000
개발부 21403000 부장 2710000
개발부 21403000 사원 10222000
기획부 14145000 대리 6340000
기획부 14145000 부장 5420000
기획부 14145000 사원 2385000
영업부 28060200 과장 2364000
영업부 28060200 대리 10806200
영업부 28060200 부장 5340000
영업부 28060200 사원 9550000
인사부 6757400 과장 2465000
인사부 6757400 대리 2230000
인사부 6757400 사원 2062400
자재부 9356400 과장 2450000
자재부 9356400 부장 2680000
자재부 9356400 사원 4226400
총무부 13036000 과장 4938000
총무부 13036000 부장 2800000
총무부 13036000 사원 5298000
홍보부 9477000 과장 2537000
홍보부 9477000 대리 3757000
홍보부 9477000 사원 3183000
*/
--SELECT *
--FROM
--(
-- SELECT BUSEO "부서명"
-- , SUM(BASICPAY + SUDANG) "급여합"
-- FROM TBL_INSA
-- GROUP BY BUSEO
--) BP
--INNER JOIN
--(
-- SELECT JIKWI "직위명"
-- , BUSEO "부서명"
-- , SUM(BASICPAY + SUDANG) "급여합"
-- FROM TBL_INSA
-- GROUP BY JIKWI, BUSEO
--) JP
--ON BP.부서명 = JP.부서명;
--64. 부서별 직위별 인원수, 급여합, 급여평균 구하기. (부서, 직위, 급여합)
SELECT DISTINCT BP.부서명, BP.부서별인원수, BP.부서별급여합, BP.부서별급여평균
, JP.직위명, JP.직위별인원수, JP.직위별급여합, JP.직위별급여평균
FROM
(
SELECT BUSEO "부서명"
, SUM(BASICPAY + SUDANG) "부서별급여합"
, COUNT(*) "부서별인원수"
, ROUND(AVG(BASICPAY + SUDANG), 0) "부서별급여평균"
FROM TBL_INSA
GROUP BY BUSEO
) BP
INNER JOIN
(
SELECT JIKWI "직위명"
, BUSEO "부서명"
, SUM(BASICPAY + SUDANG) "직위별급여합"
, COUNT(*) "직위별인원수"
, ROUND(AVG(BASICPAY + SUDANG), 0) "직위별급여평균"
FROM TBL_INSA
GROUP BY JIKWI, BUSEO
) JP
ON BP.부서명 = JP.부서명
ORDER BY BP.부서명 ASC, JP.직위명 ASC;
--==>>
/*
부서명 부서별인원수 부서별급여합 부서별급여평균 직위명 직위별인원수 직위별급여합 직위별급여평균
개발부 14 21403000 1528786 과장 2 4915000 2457500
개발부 14 21403000 1528786 대리 2 3556000 1778000
개발부 14 21403000 1528786 부장 1 2710000 2710000
개발부 14 21403000 1528786 사원 9 10222000 1135778
기획부 7 14145000 2020714 대리 3 6340000 2113333
기획부 7 14145000 2020714 부장 2 5420000 2710000
기획부 7 14145000 2020714 사원 2 2385000 1192500
영업부 16 28060200 1753763 과장 1 2364000 2364000
영업부 16 28060200 1753763 대리 5 10806200 2161240
영업부 16 28060200 1753763 부장 2 5340000 2670000
영업부 16 28060200 1753763 사원 8 9550000 1193750
인사부 4 6757400 1689350 과장 1 2465000 2465000
인사부 4 6757400 1689350 대리 1 2230000 2230000
인사부 4 6757400 1689350 사원 2 2062400 1031200
자재부 6 9356400 1559400 과장 1 2450000 2450000
자재부 6 9356400 1559400 부장 1 2680000 2680000
자재부 6 9356400 1559400 사원 4 4226400 1056600
총무부 7 13036000 1862286 과장 2 4938000 2469000
총무부 7 13036000 1862286 부장 1 2800000 2800000
총무부 7 13036000 1862286 사원 4 5298000 1324500
홍보부 6 9477000 1579500 과장 1 2537000 2537000
홍보부 6 9477000 1579500 대리 2 3757000 1878500
홍보부 6 9477000 1579500 사원 3 3183000 1061000
*/
--65. 부서별 직위별 인원수를 구하되 인원수가 5명 이상인 경우만 조회.
-- #1 인원수가 5명 이상인 부서 찾기
SELECT BUSEO "부서명", COUNT(*) "부서별인원수"
FROM TBL_INSA
GROUP BY BUSEO
HAVING COUNT(*) >= 5;
-- #2 부서가 위와 같은 직원들 중 직위별로 인원수 구하기
SELECT JIKWI "직위명", BUSEO "부서명", COUNT(*) "직위별인원수"
FROM TBL_INSA
GROUP BY JIKWI, BUSEO;
-- #3 두 테이블 결합 INNER JOIN
SELECT BT.부서명, BT.부서별인원수, JT.직위명, JT.직위별인원수
FROM
(
SELECT BUSEO "부서명", COUNT(*) "부서별인원수"
FROM TBL_INSA
GROUP BY BUSEO
HAVING COUNT(*) >= 5
) BT
INNER JOIN
(
SELECT JIKWI "직위명", BUSEO "부서명", COUNT(*) "직위별인원수"
FROM TBL_INSA
GROUP BY JIKWI, BUSEO
) JT
ON BT.부서명 = JT.부서명
ORDER BY BT.부서명 ASC;
--==>>
/*
부서명 부서별인원수 직위명 직위별인원수
개발부 14 부장 1
개발부 14 과장 2
개발부 14 사원 9
개발부 14 대리 2
기획부 7 사원 2
기획부 7 부장 2
기획부 7 대리 3
영업부 16 대리 5
영업부 16 과장 1
영업부 16 사원 8
영업부 16 부장 2
자재부 6 부장 1
자재부 6 사원 4
자재부 6 과장 1
총무부 7 사원 4
총무부 7 과장 2
총무부 7 부장 1
홍보부 6 대리 2
홍보부 6 과장 1
홍보부 6 사원 3
*/
--66. 2000년에 입사한 여사원 조회. (이름, 주민번호, 입사일)
SELECT NAME "이름", SSN "주민번호", IBSADATE "입사일"
FROM TBL_INSA
WHERE EXTRACT(YEAR FROM IBSADATE) = 2000
AND
SUBSTR(SSN, 8, 1) IN ('2', '3');
--==>>
/*
이름 주민번호 입사일
유관순 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)
ORDER BY 성씨 ASC;
--==>>
/*
성씨 인원수
고 1
권 2
김 12
나 1
문 1
박 2
산 1
손 1
심 1
양 1
엄 1
우 1
유 2
이 14
임 1
장 1
전 1
정 5
조 1
지 2
채 1
최 1
한 1
허 1
홍 3
황 1
*/
--68. 출신도(CITY)별 성별 인원수 조회.
SELECT CITY "출신도"
, 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 CITY, SUBSTR(SSN, 8, 1)
ORDER BY CITY ASC;
--==>>
/*
출신도 성별 인원수
강원 남자 2
경기 남자 5
경기 여자 8
경남 남자 1
경북 남자 1
부산 남자 1
부산 여자 2
서울 남자 9
서울 여자 11
인천 남자 4
인천 여자 5
전남 남자 1
전남 여자 2
전북 남자 4
전북 여자 1
제주 남자 2
충남 남자 1
*/
-- 인원수 60명 맞는지 확인.
--SELECT SUM(T.인원수)
--FROM
--(
-- SELECT CITY "출신도"
-- , 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 CITY, SUBSTR(SSN, 8, 1)
-- ORDER BY CITY ASC
--) T;
--69. 부서별 남자인원수가 5명 이상인 부서와 남자인원수 조회.
SELECT BUSEO
, CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '3')
THEN '남자'
WHEN SUBSTR(SSN, 8, 1) IN ('2', '4')
THEN '여자'
ELSE '알수없음'
END "성별"
, COUNT(SUBSTR(SSN, 8, 1)) "인원수"
FROM TBL_INSA
GROUP BY BUSEO, SUBSTR(SSN, 8, 1);
-- 남자 인원수가 5 이상인 것 찾기.
--SELECT BUSEO
-- , 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
--WHERE SUBSTR(SSN, 8, 1) IN ('1', '3')
--GROUP BY BUSEO, SUBSTR(SSN, 8, 1)
--HAVING COUNT(*) >= 5;
-- 부서명만 뽑기
SELECT T.BUSEO
FROM
(
SELECT BUSEO
, 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
WHERE SUBSTR(SSN, 8, 1) IN ('1', '3')
GROUP BY BUSEO, SUBSTR(SSN, 8, 1)
HAVING COUNT(*) >= 5
)T;
-- 위의 식의 WHERE 절에 조건으로 넣기.
SELECT BUSEO "부서명"
, CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '3')
THEN '남자'
WHEN SUBSTR(SSN, 8, 1) IN ('2', '4')
THEN '여자'
ELSE '알수없음'
END "성별"
, COUNT(SUBSTR(SSN, 8, 1)) "인원수"
FROM TBL_INSA
WHERE BUSEO
IN
(
SELECT T.BUSEO
FROM
(
SELECT BUSEO
, 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
WHERE SUBSTR(SSN, 8, 1) IN ('1', '3')
GROUP BY BUSEO, SUBSTR(SSN, 8, 1)
HAVING COUNT(*) >= 5
)T
)
GROUP BY BUSEO, SUBSTR(SSN, 8, 1);
--==>>
/*
부서명 성별 인원수
영업부 남자 8
개발부 여자 8
개발부 남자 6
영업부 여자 8
*/
--70. 입사년도별 인원수 조회.
SELECT EXTRACT(YEAR FROM IBSADATE) "입사년도"
, COUNT(*) "인원수"
FROM TBL_INSA
GROUP BY EXTRACT(YEAR FROM IBSADATE);
--==>>
/*
입사년도 인원수
1997 1
2005 1
1999 10
2001 9
1998 6
2000 12
1995 1
2004 5
2003 10
2002 5
*/
-- 60명이 맞는지 확인.
--SELECT SUM(T.인원수)
--FROM
--(
-- SELECT EXTRACT(YEAR FROM IBSADATE) "입사년도"
-- , COUNT(*) "인원수"
-- FROM TBL_INSA
-- GROUP BY EXTRACT(YEAR FROM IBSADATE)
--) T;
--71. 전체인원수, 2000년, 1999년, 1998년도에 입사한 인원을 다음의 형식으로 조회.
-- 출력형태 ---------------
-- 전체 2000 1999 1998
-- 60 x x x
-- #1 전체 인원수 구하기
SELECT COUNT(*)
FROM TBL_INSA;
-- #2 2000년도 인원 구하기
SELECT COUNT(*) "2000년도인원수"
FROM TBL_INSA
WHERE EXTRACT(YEAR FROM IBSADATE) = 2000;
-- #3 1999년도 인원 구하기
SELECT COUNT(*) "1999년도인원수"
FROM TBL_INSA
WHERE EXTRACT(YEAR FROM IBSADATE) = 1999;
-- #4 1998년도 인원 구하기
SELECT COUNT(*) "1998년도인원수"
FROM TBL_INSA
WHERE EXTRACT(YEAR FROM IBSADATE) = 1998;
-- #5 서브 상단 쿼리로 구성하기!!!!
SELECT (
SELECT COUNT(*)
FROM TBL_INSA
) "전체"
,
(
SELECT COUNT(*) "2000년도인원수"
FROM TBL_INSA
WHERE EXTRACT(YEAR FROM IBSADATE) = 2000
) "2000"
,
(
SELECT COUNT(*) "1999년도인원수"
FROM TBL_INSA
WHERE EXTRACT(YEAR FROM IBSADATE) = 1999
) "1999"
,
(
SELECT COUNT(*) "1998년도인원수"
FROM TBL_INSA
WHERE EXTRACT(YEAR FROM IBSADATE) = 1998
) "1998"
FROM DUAL;
--==>>
/*
전체 2000 1999 1998
60 12 10 6
*/
--72. 아래 형식으로 지역별 인원수 조회.
-- 출력형태 -----------------
-- 전체 서울 인천 경기
-- 60 x x x
-- #1 전체 인원수 구하기
SELECT COUNT(*)
FROM TBL_INSA;
-- 서울 인천 경기
-- #2 서울 인원 구하기
SELECT COUNT(*) "서울인원수"
FROM TBL_INSA
WHERE CITY LIKE '서울';
-- #3 인천 인원 구하기
SELECT COUNT(*) "인천인원수"
FROM TBL_INSA
WHERE CITY LIKE '인천';
-- #4 경기 인원 구하기
SELECT COUNT(*) "경기인원수"
FROM TBL_INSA
WHERE CITY LIKE '경기';
-- #5 서브 상단 쿼리로 구성하기!!!!
/*
출력형태 -----------------
전체 서울 인천 경기
60 x x x
*/
SELECT (
SELECT COUNT(*)
FROM TBL_INSA
) "전체"
,
(
SELECT COUNT(*) "서울인원수"
FROM TBL_INSA
WHERE CITY LIKE '서울'
) "서울"
,
(
SELECT COUNT(*) "인천인원수"
FROM TBL_INSA
WHERE CITY LIKE '인천'
) "인천"
,
(
SELECT COUNT(*) "경기인원수"
FROM TBL_INSA
WHERE CITY LIKE '경기'
) "경기"
FROM DUAL;
--==>>
/*
전체 서울 인천 경기
60 20 9 13
*/
--73. 기본급(BASICPAY)이 평균 이하인 사원 조회. (이름, 기본급). AVG() 함수. 서브쿼리.
-- #1 기본급 평균 구하기 (정수부)
SELECT ROUND(AVG(BASICPAY), 0)
FROM TBL_INSA;
--==>> 1556527
-- #2 평균 이하인 사원 구하기
SELECT NAME "이름", BASICPAY "기본급"
FROM TBL_INSA
WHERE BASICPAY
<=
(
SELECT ROUND(AVG(BASICPAY), 0)
FROM TBL_INSA
);
--==>>
/*
이름 기본급
이순신 1320000
한석봉 1420000
장인철 1250000
김영년 950000
나윤균 840000
유관순 1020000
정한국 880000
황진이 1100000
이현숙 1050000
엄용수 950000
이성길 880000
유영희 880000
홍길남 875000
우재옥 1100000
김숙남 1050000
이남신 892000
김말숙 920000
심심해 880000
김미나 1020000
이정석 1100000
정영희 1050000
이재영 960400
채정희 1020000
양미옥 1100000
지수환 1060000
홍원신 960000
이미성 1300000
권옥경 1020000
김싱식 960000
정상호 980000
정한나 1000000
임수봉 890000
김신애 900000
*/
-- 74. 기본급 상위 10%만 조회. (이름, 기본급)
-- #1. 상위 10%가 몇 명인지 구하기
SELECT (COUNT(*) - COUNT(*) * ((100 - 10) / 100))
FROM TBL_INSA;
-- #2 기본급순위 구하기
SELECT NAME, BASICPAY
, RANK() OVER (ORDER BY BASICPAY DESC) "기본급순위"
FROM TBL_INSA;
-- #3 기본급 상위 10%인 사원이름, 기본급 구하기
SELECT T.NAME "사원이름"
, T.BASICPAY "기본급"
FROM
(
SELECT NAME, BASICPAY
, RANK() OVER (ORDER BY BASICPAY DESC) "기본급순위"
FROM TBL_INSA
)T
WHERE T.기본급순위 <= (SELECT (COUNT(*) - COUNT(*) * ((100 - 10) / 100))
FROM TBL_INSA);
--==>>
/*
사원이름 기본급
허경운 2650000
홍길동 2610000
이순애 2550000
김종서 2540000
이미경 2520000
김인수 2500000
*/
--75. 기본급 순위가 5순위까지만 조회. (모든 정보)
-- #1 기본급 상위 10%인 사원이름 구하기
SELECT T.NAME "사원이름"
FROM
(
SELECT NAME, BASICPAY
, RANK() OVER (ORDER BY BASICPAY DESC) "기본급순위"
FROM TBL_INSA
)T
WHERE T.기본급순위 <= (SELECT (COUNT(*) - COUNT(*) * ((100 - 10) / 100))
FROM TBL_INSA);
-- #2 모든 정보 출력하기
SELECT *
FROM TBL_INSA
WHERE NAME
IN
(
SELECT T.NAME "사원이름"
FROM
(
SELECT NAME, BASICPAY
, RANK() OVER (ORDER BY BASICPAY DESC) "기본급순위"
FROM TBL_INSA
)T
WHERE T.기본급순위 <= (SELECT (COUNT(*) - COUNT(*) * ((100 - 10) / 100))
FROM TBL_INSA)
);
--==>>
/*
NUM NAME SSN IBSADATE CITY TEL BUSEO JIKWI BASICPAY SUDANG
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
1023 김인수 731211-1214576 1995-02-23 서울 영업부 부장 2500000 170000
*/
--76. 입사일이 빠른 순서로 5순위까지만 조회. (모든 정보)
-- #1 입사일 빠른 순으로 순위 정하기
SELECT RANK() OVER(ORDER BY IBSADATE ASC) "입사일순위"
, IBSADATE "입사일"
, NAME "이름"
, SSN "주민번호"
FROM TBL_INSA;
-- #2 위에서 구한 순위, 이름, 주민번호를 이용하여
-- 입사일 순위가 1~5위까지인 사원들을 조회하기
SELECT TI.*
FROM TBL_INSA TI
INNER JOIN
(
SELECT RANK() OVER(ORDER BY IBSADATE ASC) "입사일순위"
, IBSADATE "입사일"
, NAME "이름"
, SSN "주민번호"
FROM TBL_INSA
) T
ON TI.NAME = T.이름
AND
TI.SSN = T.주민번호
WHERE T.입사일순위 <= 5;
--==>>
/*
NUM NAME SSN IBSADATE CITY TEL BUSEO JIKWI BASICPAY SUDANG
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. 평균 급여보다 많은 급여를 받는 직원 정보 조회. (모든 정보)
-- #1 평균 급여 구하기 (정수부)
SELECT ROUND(AVG(BASICPAY + SUDANG), 0) "평균급여"
FROM TBL_INSA;
--==>> 1703917
-- #2 평균 급여보다 더 많은 급여를 받는 직원 정보 모두 조회하기
SELECT TI.*
, (TI.BASICPAY + TI.SUDANG) "급여"
FROM TBL_INSA TI
WHERE (TI.BASICPAY + TI.SUDANG)
>
(
SELECT ROUND(AVG(BASICPAY + SUDANG), 0) "평균급여"
FROM TBL_INSA
);
--==>>
/*
NUM NAME SSN IBSADATE CITY TEL BUSEO JIKWI BASICPAY SUDANG 급여
1001 홍길동 771212-1022432 1998-10-11 서울 011-2356-4528 기획부 부장 2610000 200000 2810000
1003 이순애 770922-2312547 1999-02-25 인천 010-4231-1236 개발부 부장 2550000 160000 2710000
1004 김정훈 790304-1788896 2000-10-01 전북 019-5236-4221 영업부 대리 1954200 170000 2124200
1006 이기자 780505-2978541 2002-02-11 인천 010-3214-5357 개발부 과장 2265000 150000 2415000
1010 김종서 751010-1122233 1997-08-08 부산 011-3214-5555 영업부 부장 2540000 130000 2670000
1013 조미숙 790102-2777777 1998-06-07 경기 019-6666-4444 홍보부 대리 1601000 103000 1704000
1016 이상헌 781010-1666678 2001-11-29 경기 010-4526-1234 개발부 과장 2350000 150000 2500000
1019 박문수 780710-1985632 1999-12-10 서울 017-4747-4848 인사부 과장 2300000 165000 2465000
1022 이영숙 800501-2312456 2003-02-25 전남 017-5214-5282 기획부 대리 1960000 180000 2140000
1023 김인수 731211-1214576 1995-02-23 서울 영업부 부장 2500000 170000 2670000
1024 김말자 830225-2633334 1999-08-28 서울 011-5248-7789 기획부 대리 1900000 170000 2070000
1027 김영길 801216-1898752 2000-10-18 서울 019-8523-1478 총무부 과장 2340000 170000 2510000
1030 정정해 790210-2101010 1999-10-17 부산 019-6564-6752 총무부 과장 2304000 124000 2428000
1031 지재환 771115-1687988 2001-01-21 서울 019-5552-7511 기획부 부장 2450000 160000 2610000
1037 최석규 770129-1456987 1998-10-15 인천 011-7777-7777 홍보부 과장 2350000 187000 2537000
1038 손인수 791009-2321456 1999-11-15 부산 010-6542-7412 영업부 대리 2000000 150000 2150000
1039 고순정 800504-2000032 2003-12-28 경기 010-2587-7895 영업부 대리 2010000 160000 2170000
1040 박세열 790509-1635214 2000-09-10 경북 016-4444-7777 인사부 대리 2100000 130000 2230000
1041 문길수 721217-1951357 2001-12-10 충남 016-4444-5555 자재부 과장 2300000 150000 2450000
1046 허경운 760105-1458752 1999-05-04 경남 017-3333-3333 총무부 부장 2650000 150000 2800000
1047 산마루 780505-1234567 2001-07-15 서울 018-0505-0505 영업부 대리 2100000 112000 2212000
1048 이기상 790604-1415141 2001-06-07 전남 개발부 대리 2050000 106000 2156000
1050 이미인 810403-2828287 2003-06-07 경기 011-8585-5252 홍보부 대리 1950000 103000 2053000
1051 권영미 790303-2155554 2000-06-04 서울 011-5555-7548 영업부 과장 2260000 104000 2364000
1056 전용재 800605-1456987 2004-08-13 인천 010-7549-8654 영업부 대리 1950000 200000 2150000
1057 이미경 780406-2003214 1998-02-11 경기 016-6542-7546 자재부 부장 2520000 160000 2680000
1058 김신제 800709-1321456 2003-08-08 인천 010-2415-5444 기획부 대리 1950000 180000 2130000
*/
--
--78. '이순애' 직원의 급여보다 더 많은 급여를 받는 직원 조회. (모든 정보)
-- 단, 이순애 직원의 급여가 변하더라도 작성된 쿼리문은 기능 수행이 가능하도록 조회.
-- #1 이순애씨의 급여 구하기
SELECT (TI.BASICPAY + TI.SUDANG) "순애씨급여"
FROM TBL_INSA TI
WHERE NAME = '이순애';
-- #2 순애씨보다 더 많은 급여를 받는 직원 정보 모두 조회하기
SELECT TI2.*
, (TI2.BASICPAY + TI2.SUDANG) "급여"
FROM TBL_INSA TI2
WHERE (TI2.BASICPAY + TI2.SUDANG)
>
(
SELECT (TI.BASICPAY + TI.SUDANG) "순애씨급여"
FROM TBL_INSA TI
WHERE NAME = '이순애'
);
--==>>
/*
NUM NAME SSN IBSADATE CITY TEL BUSEO JIKWI BASICPAY SUDANG 급여
1001 홍길동 771212-1022432 1998-10-11 서울 011-2356-4528 기획부 부장 2610000 200000 2810000
1046 허경운 760105-1458752 1999-05-04 경남 017-3333-3333 총무부 부장 2650000 150000 2800000
*/
--79. 총무부의 평균 급여보다 많은 급여를 받는 직원들의 이름, 부서명 조회.
-- #1 총무부 평균 급여 구하기 (정수부)
SELECT ROUND(AVG(BASICPAY + SUDANG), 0) "총무부평균급여"
FROM TBL_INSA
WHERE BUSEO LIKE '총무부';
--==>> 1862286
-- #2 총무부 평균 급여보다 더 많은 급여를 받는 직원 정보 모두 조회하기
SELECT TI2.*
, (TI2.BASICPAY + TI2.SUDANG) "급여"
FROM TBL_INSA TI2
WHERE (TI2.BASICPAY + TI2.SUDANG)
>
(
SELECT ROUND(AVG(BASICPAY + SUDANG), 0) "총무부평균급여"
FROM TBL_INSA
WHERE BUSEO LIKE '총무부'
);
--==>>
/*
NUM NAME SSN IBSADATE CITY TEL BUSEO JIKWI BASICPAY SUDANG 급여
1001 홍길동 771212-1022432 1998-10-11 서울 011-2356-4528 기획부 부장 2610000 200000 2810000
1003 이순애 770922-2312547 1999-02-25 인천 010-4231-1236 개발부 부장 2550000 160000 2710000
1004 김정훈 790304-1788896 2000-10-01 전북 019-5236-4221 영업부 대리 1954200 170000 2124200
1006 이기자 780505-2978541 2002-02-11 인천 010-3214-5357 개발부 과장 2265000 150000 2415000
1010 김종서 751010-1122233 1997-08-08 부산 011-3214-5555 영업부 부장 2540000 130000 2670000
1016 이상헌 781010-1666678 2001-11-29 경기 010-4526-1234 개발부 과장 2350000 150000 2500000
1019 박문수 780710-1985632 1999-12-10 서울 017-4747-4848 인사부 과장 2300000 165000 2465000
1022 이영숙 800501-2312456 2003-02-25 전남 017-5214-5282 기획부 대리 1960000 180000 2140000
1023 김인수 731211-1214576 1995-02-23 서울 영업부 부장 2500000 170000 2670000
1024 김말자 830225-2633334 1999-08-28 서울 011-5248-7789 기획부 대리 1900000 170000 2070000
1027 김영길 801216-1898752 2000-10-18 서울 019-8523-1478 총무부 과장 2340000 170000 2510000
1030 정정해 790210-2101010 1999-10-17 부산 019-6564-6752 총무부 과장 2304000 124000 2428000
1031 지재환 771115-1687988 2001-01-21 서울 019-5552-7511 기획부 부장 2450000 160000 2610000
1037 최석규 770129-1456987 1998-10-15 인천 011-7777-7777 홍보부 과장 2350000 187000 2537000
1038 손인수 791009-2321456 1999-11-15 부산 010-6542-7412 영업부 대리 2000000 150000 2150000
1039 고순정 800504-2000032 2003-12-28 경기 010-2587-7895 영업부 대리 2010000 160000 2170000
1040 박세열 790509-1635214 2000-09-10 경북 016-4444-7777 인사부 대리 2100000 130000 2230000
1041 문길수 721217-1951357 2001-12-10 충남 016-4444-5555 자재부 과장 2300000 150000 2450000
1046 허경운 760105-1458752 1999-05-04 경남 017-3333-3333 총무부 부장 2650000 150000 2800000
1047 산마루 780505-1234567 2001-07-15 서울 018-0505-0505 영업부 대리 2100000 112000 2212000
1048 이기상 790604-1415141 2001-06-07 전남 개발부 대리 2050000 106000 2156000
1050 이미인 810403-2828287 2003-06-07 경기 011-8585-5252 홍보부 대리 1950000 103000 2053000
1051 권영미 790303-2155554 2000-06-04 서울 011-5555-7548 영업부 과장 2260000 104000 2364000
1056 전용재 800605-1456987 2004-08-13 인천 010-7549-8654 영업부 대리 1950000 200000 2150000
1057 이미경 780406-2003214 1998-02-11 경기 016-6542-7546 자재부 부장 2520000 160000 2680000
1058 김신제 800709-1321456 2003-08-08 인천 010-2415-5444 기획부 대리 1950000 180000 2130000
*/
--80. 총무부 직원들의 평균 수당보다 더 많은 수당을 받는 직원 정보 조회.
-- #1 총무부 평균 수당 구하기 (정수부)
SELECT ROUND(AVG(SUDANG), 0) "총무부평균수당"
FROM TBL_INSA
WHERE BUSEO LIKE '총무부';
--==>> 147429
-- #2 총무부 평균 수당보다 더 많은 수당을 받는 직원 정보 모두 조회하기
SELECT TI2.*
FROM TBL_INSA TI2
WHERE TI2.SUDANG
>
(
SELECT ROUND(AVG(SUDANG), 0) "총무부평균수당"
FROM TBL_INSA
WHERE BUSEO LIKE '총무부'
);
--==>>
/*
NUM NAME SSN IBSADATE CITY TEL BUSEO JIKWI BASICPAY SUDANG
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. 직원 전체 평균 급여보다 많은 급여를 받는 직원의 수 조회.
-- #1 전체평균급여 구하기
SELECT ROUND(AVG(BASICPAY + SUDANG), 0) "전체평균급여"
FROM TBL_INSA;
-- #2 전체 평균급여보다 많은 급여를 받는 직원 정보 조회하기.
SELECT *
FROM TBL_INSA TI
WHERE (TI.BASICPAY + TI.SUDANG)
>
(
SELECT ROUND(AVG(BASICPAY + SUDANG), 0) "전체평균급여"
FROM TBL_INSA
);
-- #3 인원수 구하기
SELECT COUNT(*) "평균급여많은직원수"
FROM TBL_INSA TI
WHERE (TI.BASICPAY + TI.SUDANG)
>
(
SELECT ROUND(AVG(BASICPAY + SUDANG), 0) "전체평균급여"
FROM TBL_INSA
);
--==>>
/*
평균급여많은직원수
27
*/
--82. '홍길동' 직원과 같은 부서의 직원 정보 조회.
-- 단, 홍길동 직원의 부서가 바뀌더라도 작성된 쿼리문은 기능 수행이 가능하도록 조회.
-- #1 홍길동씨의 부서명 찾기
SELECT BUSEO "부서명"
FROM TBL_INSA
WHERE NAME LIKE '홍길동';
--==>> 기획부
-- #2 같은 부서인 직원 조회하기.
SELECT *
FROM TBL_INSA
WHERE BUSEO
IN
(
SELECT BUSEO "부서명"
FROM TBL_INSA
WHERE NAME LIKE '홍길동'
);
--==>>
/*
부서명
기획부
*/
--
--83. '김신애' 직원과 같은 부서, 직위를 가진 직원 정보 조회.
-- 단, 김신애 직원의 부서 및 직위가 바뀌더라도 작성된 쿼리문은 기능 수행이 가능하도록 조회.
SELECT BUSEO, JIKWI
FROM TBL_INSA
WHERE NAME LIKE '김신애';
--==>>
/*
BUSEO JIKWI
개발부 사원
*/
SELECT *
FROM TBL_INSA
WHERE (BUSEO, JIKWI)
IN
(
SELECT BUSEO, JIKWI
FROM TBL_INSA
WHERE NAME LIKE '김신애'
);
--==>>
/*
NUM NAME SSN IBSADATE CITY TEL BUSEO JIKWI BASICPAY SUDANG
1060 김신애 810809-2111111 2001-10-10 서울 011-4151-4444 개발부 사원 900000 102000
1059 임수봉 810809-2121244 2001-10-10 서울 011-4151-4154 개발부 사원 890000 102000
1049 이미성 830908-2456548 2000-04-07 인천 010-6654-8854 개발부 사원 1300000 130000
1042 채정희 810709-2000054 2003-10-17 경기 011-5125-5511 개발부 사원 1020000 200000
1035 정영희 831010-2153252 2002-05-16 인천 개발부 사원 1050000 140000
1021 홍길남 801010-1111111 2001-09-07 경기 011-9999-7575 개발부 사원 875000 120000
1018 이성길 801028-1849534 2004-08-08 전북 018-1333-3333 개발부 사원 880000 123000
1017 엄용수 820507-1452365 2000-08-28 인천 010-3254-2542 개발부 사원 950000 210000
1014 황진이 810707-2574812 2002-02-15 인천 010-3214-5467 개발부 사원 1100000 130000
*/
--84. 부서별 기본급이 가장 높은 사람 조회. (이름, 부서, 기본급)
-- 단, 사원들의 기본급이 변경되더라도 작성된 쿼리문은 기능 수행이 가능하도록 조회.
-- 부서별 기본급 조회.
SELECT BUSEO,BASICPAY
FROM TBL_INSA
GROUP BY BUSEO,BASICPAY
ORDER BY BUSEO ASC;
-- 부서별 최대 기본급 조회
SELECT T.BUSEO, MAX(T.BASICPAY)
FROM
(
SELECT BUSEO,BASICPAY
FROM TBL_INSA
GROUP BY BUSEO,BASICPAY
ORDER BY BUSEO ASC
)T
GROUP BY T.BUSEO;
-- 부서별 최대 기본급을 갖는 사원을 찾기
-- (이름, 부서, 기본급)
SELECT TI.NAME "이름", TI.BUSEO "부서명", TI.BASICPAY "기본급"
FROM TBL_INSA TI
WHERE (TI.BUSEO, TI.BASICPAY)
IN
(SELECT T.BUSEO, MAX(T.BASICPAY)
FROM
(
SELECT BUSEO,BASICPAY
FROM TBL_INSA
GROUP BY BUSEO,BASICPAY
ORDER BY BUSEO ASC
)T
GROUP BY T.BUSEO
);
--==>>
/*
이름 부서명 기본급
허경운 총무부 2650000
이순애 개발부 2550000
김종서 영업부 2540000
홍길동 기획부 2610000
이미경 자재부 2520000
박문수 인사부 2300000
최석규 홍보부 2350000
*/
--85. 남, 여별 기본급 순위 조회.
-- 남여 기본급 조회하기
SELECT CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '3')
THEN '남자'
WHEN SUBSTR(SSN, 8, 1) IN ('2', '4')
THEN '여자'
ELSE '알수없음'
END "성별"
, BASICPAY "기본급"
FROM TBL_INSA
GROUP BY SUBSTR(SSN, 8, 1), BASICPAY;
-- 남여 기본급 순위 매기기
SELECT T.성별, T.기본급, RANK() OVER(ORDER BY T.기본급 DESC) "기본급순위"
FROM
(
SELECT CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '3')
THEN '남자'
WHEN SUBSTR(SSN, 8, 1) IN ('2', '4')
THEN '여자'
ELSE '알수없음'
END "성별"
, BASICPAY "기본급"
FROM TBL_INSA
GROUP BY SUBSTR(SSN, 8, 1), BASICPAY
)T
ORDER BY T.성별 ASC, 기본급순위 ASC;
--==>>
/*
성별 기본급 기본급순위
남자 2650000 1
남자 2610000 2
남자 2540000 4
남자 2500000 6
남자 2450000 7
남자 2350000 8
남자 2340000 9
남자 2300000 11
남자 2100000 14
남자 2050000 15
남자 1954200 19
남자 1950000 20
남자 1420000 24
남자 1320000 25
남자 1250000 27
남자 1100000 28
남자 1060000 30
남자 980000 34
남자 960000 36
남자 950000 37
남자 892000 41
남자 880000 43
남자 875000 45
남자 840000 46
여자 2550000 3
여자 2520000 5
여자 2304000 10
여자 2265000 12
여자 2260000 13
여자 2010000 16
여자 2000000 17
여자 1960000 18
여자 1950000 20
여자 1900000 22
여자 1601000 23
여자 1300000 26
여자 1100000 28
여자 1050000 31
여자 1020000 32
여자 1000000 33
여자 960400 35
여자 950000 37
여자 920000 39
여자 900000 40
여자 890000 42
여자 880000 43
*/
--86. 지역(CITY)별로 급여(기본급+수당) 1순위 직원만 조회.
-- 지역, 급여 조회하기
SELECT CITY "지역"
, BASICPAY + SUDANG "급여"
FROM TBL_INSA;
-- 그룹화 해서 최대 급여를 받는 직원 구하기
SELECT T.지역, MAX(T.급여)
FROM
(
SELECT CITY "지역"
, BASICPAY + SUDANG "급여"
FROM TBL_INSA
)T
GROUP BY T.지역;
-- 지역별 최대 급여를 받는 직원을 찾기
SELECT *
FROM TBL_INSA TI
WHERE (TI.CITY, (TI.BASICPAY + TI.SUDANG))
IN
(
SELECT T.지역, MAX(T.급여)
FROM
(
SELECT CITY "지역"
, BASICPAY + SUDANG "급여"
FROM TBL_INSA
)T
GROUP BY T.지역
);
--==>>
/*
NUM NAME SSN IBSADATE CITY TEL BUSEO JIKWI BASICPAY SUDANG
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;
-- 부서별로 가장 많은 인원수 구함
SELECT MAX(T.인원수)
FROM
(
SELECT BUSEO "부서명"
, COUNT(*) "인원수"
FROM TBL_INSA
GROUP BY BUSEO
)T;
-- 가장 많은 인원수를 갖는 부서 조회
SELECT BUSEO "부서명"
, COUNT(*) "인원수"
FROM TBL_INSA
GROUP BY BUSEO
HAVING COUNT(*)
IN
(
SELECT MAX(T.인원수)
FROM
(
SELECT BUSEO "부서명"
, COUNT(*) "인원수"
FROM TBL_INSA
GROUP BY BUSEO
)T
);
--==>>
/*
부서명 인원수
영업부 16
*/
--88. 지역(CITY)별 인원수가 가장 많은 지역 및 인원수 조회.
-- 지역별 인원수 확인
SELECT CITY "지역명"
, COUNT(*) "인원수"
FROM TBL_INSA
GROUP BY CITY;
-- 지역별로 가장 많은 인원수 구함
SELECT MAX(T.인원수)
FROM
(
SELECT CITY "지역명"
, COUNT(*) "인원수"
FROM TBL_INSA
GROUP BY CITY
)T;
-- 가장 많은 인원수를 갖는 지역 조회
SELECT CITY "지역명"
, COUNT(*) "인원수"
FROM TBL_INSA
GROUP BY CITY
HAVING COUNT(*)
IN
(
SELECT MAX(T.인원수)
FROM
(
SELECT CITY "지역명"
, COUNT(*) "인원수"
FROM TBL_INSA
GROUP BY CITY
)T
);
--==>>
/*
지역명 인원수
서울 20
*/
89. 지역(CITY)별 평균 급여(BASICPAY + SUDANG)가
가장 높은 지역 및 평균급여 조회.
-- 지역별 평균급여 확인
SELECT CITY "지역명"
, ROUND(AVG(BASICPAY + SUDANG), 0) "평균급여"
FROM TBL_INSA
GROUP BY CITY;
-- 지역별로 가장 많은 평균급여 구함
SELECT MAX(T.평균급여)
FROM
(
SELECT CITY "지역명"
, ROUND(AVG(BASICPAY + SUDANG), 0) "평균급여"
FROM TBL_INSA
GROUP BY CITY
)T;
-- 가장 많은 평균급여를 갖는 지역 조회
SELECT CITY "지역명"
, ROUND(AVG(BASICPAY + SUDANG), 0) "평균급여"
FROM TBL_INSA
GROUP BY CITY
HAVING ROUND(AVG(BASICPAY + SUDANG), 0)
IN
(
SELECT MAX(T.평균급여)
FROM
(
SELECT CITY "지역명"
, ROUND(AVG(BASICPAY + SUDANG), 0) "평균급여"
FROM TBL_INSA
GROUP BY CITY
)T
);
--==>>
/*
지역명 평균급여
경남 2800000
*/
90. 여자 인원수가 가장 많은 부서 및 인원수 조회.
-- 아 더 하고 싶어요..ㅠㅠㅠㅠ....................
/*
CASE WHEN SUBSTR(SSN, 8, 1) IN ('1', '3')
THEN '남자'
WHEN SUBSTR(SSN, 8, 1) IN ('2', '4')
THEN '여자'
ELSE '알수없음'
END "성별"
*/
-- #1 부서별 여자 인원수 구하기
SELECT BUSEO
, COUNT(*)
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('2', '4')
GROUP BY BUSEO, SUBSTR(SSN, 8, 1);
-- #2 부서별 최대 여자 인원수 구하기
SELECT MAX(T.여자인원수)
FROM
(
SELECT BUSEO "부서명"
, COUNT(*) "여자인원수"
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('2', '4')
GROUP BY BUSEO, SUBSTR(SSN, 8, 1)
) T;
-- # 부서별 남녀 상관없이 인원수 구하기
SELECT BUSEO
,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 BUSEO, SUBSTR(SSN, 8, 1);
---------------------------
-- # 최대 여자 인원수를 갖는 부서, 여자인원수, 성별 조회하기
SELECT T.부서명, T.인원수, T.성별
FROM
(
SELECT BUSEO "부서명"
,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 BUSEO, SUBSTR(SSN, 8, 1)
) T
GROUP BY T.부서명, T.인원수, T.성별
HAVING T.인원수
IN
(
SELECT MAX(T.여자인원수)
FROM
(
SELECT BUSEO "부서명"
, COUNT(*) "여자인원수"
FROM TBL_INSA
WHERE SUBSTR(SSN, 8, 1) IN ('2', '4')
GROUP BY BUSEO, SUBSTR(SSN, 8, 1)
) T
)
AND
T.성별 = '여자';
-----
--==>>
/*
부서명 인원수 성별
영업부 8 여자
개발부 8 여자
*/
--91. 지역별 인원수 순위 조회.
SELECT CITY "지역명"
, COUNT(*) "지역별인원수"
FROM TBL_INSA
GROUP BY CITY;
SELECT T.지역명, T.지역별인원수
, RANK() OVER(ORDER BY T.지역별인원수 DESC) "지역별인원수순위"
FROM
(
SELECT CITY "지역명"
, COUNT(*) "지역별인원수"
FROM TBL_INSA
GROUP BY CITY
)T;
--==>>
/*
지역명 지역별인원수 지역별인원수순위
서울 20 1
경기 13 2
인천 9 3
전북 5 4
전남 3 5
부산 3 5
제주 2 7
강원 2 7
경남 1 9
경북 1 9
충남 1 9
*/
--92. 지역별 인원수 순위 조회하되 5순위까지만 출력.
SELECT T.지역명, T.지역별인원수
, RANK() OVER(ORDER BY T.지역별인원수 DESC) "지역별인원수순위"
FROM
(
SELECT CITY "지역명"
, COUNT(*) "지역별인원수"
FROM TBL_INSA
GROUP BY CITY
)T;
SELECT T2.*
FROM
(
SELECT T.지역명, T.지역별인원수
, RANK() OVER(ORDER BY T.지역별인원수 DESC) "지역별인원수순위"
FROM
(
SELECT CITY "지역명"
, COUNT(*) "지역별인원수"
FROM TBL_INSA
GROUP BY CITY
)T
)T2
WHERE T2.지역별인원수순위 <= 5;
--==>>
/*
지역명 지역별인원수 지역별인원수순위
서울 20 1
경기 13 2
인천 9 3
전북 5 4
부산 3 5
전남 3 5
*/
--
--93. 이름, 부서, 출신도, 기본급, 수당, 기본급+수당, 세금, 실수령액 조회
-- 단, 세금: 총급여가 250만원 이상이면 2%, 200만원 이상이면 1%, 나머지 0.
-- 실수령액: 총급여-세금
-- 요구하는 것 중 바로 출력 가능한 것들 조회.
SELECT NAME, BUSEO, CITY, BASICPAY, SUDANG, (BASICPAY + SUDANG)
FROM TBL_INSA;
-- 세금 구하기
-- 1%를 부과할 경우 (200만원 이상, 250만원 미만)
SELECT (BASICPAY + SUDANG) - (BASICPAY + SUDANG) * ((100 - 1) / 100)
FROM TBL_INSA;
-- 2%를 부과할 경우 (250만원 이상)
SELECT (BASICPAY + SUDANG) - (BASICPAY + SUDANG) * ((100 - 2) / 100)
FROM TBL_INSA;
SELECT CASE WHEN (BASICPAY + SUDANG) >= 2500000
THEN (BASICPAY + SUDANG) - (BASICPAY + SUDANG) * ((100 - 2) / 100)
WHEN (BASICPAY + SUDANG) >= 2000000
THEN (BASICPAY + SUDANG) - (BASICPAY + SUDANG) * ((100 - 1) / 100)
ELSE 0
END AS "세금"
FROM TBL_INSA;
-- 실수령액 테스트
SELECT (TI.BASICPAY + TI.SUDANG) - T.세금
FROM
TBL_INSA TI
INNER JOIN
(
SELECT CASE WHEN (BASICPAY + SUDANG) >= 2500000
THEN (BASICPAY + SUDANG) - (BASICPAY + SUDANG) * ((100 - 2) / 100)
WHEN (BASICPAY + SUDANG) >= 2000000
THEN (BASICPAY + SUDANG) - (BASICPAY + SUDANG) * ((100 - 1) / 100)
ELSE 0
END AS "세금"
, SSN "주민번호"
FROM TBL_INSA
)T
ON TI.SSN = T.주민번호;
---------------
-- 세금까지 합치기
SELECT NAME "이름"
, BUSEO "부서명"
, CITY "지역명"
, BASICPAY "기본급"
, SUDANG "수당"
, (BASICPAY + SUDANG) "총급여"
, CASE WHEN (BASICPAY + SUDANG) >= 2500000
THEN (BASICPAY + SUDANG) - (BASICPAY + SUDANG) * ((100 - 2) / 100)
WHEN (BASICPAY + SUDANG) >= 2000000
THEN (BASICPAY + SUDANG) - (BASICPAY + SUDANG) * ((100 - 1) / 100)
ELSE 0
END AS "세금"
FROM TBL_INSA;
-- 실수령액 계산하기 위해, 위의 쿼리문을 서브 쿼리로 지정.
SELECT LASTT.*
, (TI.BASICPAY + TI.SUDANG) - LASTT.세금 "실수령액"
FROM
TBL_INSA TI
INNER JOIN
(
SELECT NAME "이름"
, BUSEO "부서명"
, CITY "지역명"
, BASICPAY "기본급"
, SUDANG "수당"
, (BASICPAY + SUDANG) "총급여"
, CASE WHEN (BASICPAY + SUDANG) >= 2500000
THEN (BASICPAY + SUDANG) - (BASICPAY + SUDANG) * ((100 - 2) / 100)
WHEN (BASICPAY + SUDANG) >= 2000000
THEN (BASICPAY + SUDANG) - (BASICPAY + SUDANG) * ((100 - 1) / 100)
ELSE 0
END AS "세금"
, SSN "주민번호"
FROM TBL_INSA
) LASTT
ON TI.SSN = LASTT.주민번호;
--==>>
/*
이름 부서명 지역명 기본급 수당 총급여 세금 주민번호 실수령액
홍길동 기획부 서울 2610000 200000 2810000 56200 771212-1022432 2753800
이순신 총무부 경기 1320000 200000 1520000 0 801007-1544236 1520000
이순애 개발부 인천 2550000 160000 2710000 54200 770922-2312547 2655800
김정훈 영업부 전북 1954200 170000 2124200 21242 790304-1788896 2102958
한석봉 총무부 서울 1420000 160000 1580000 0 811112-1566789 1580000
이기자 개발부 인천 2265000 150000 2415000 24150 780505-2978541 2390850
장인철 개발부 제주 1250000 150000 1400000 0 780506-1625148 1400000
김영년 홍보부 서울 950000 145000 1095000 0 821011-2362514 1095000
나윤균 인사부 경기 840000 220400 1060400 0 810810-1552147 1060400
김종서 영업부 부산 2540000 130000 2670000 53400 751010-1122233 2616600
유관순 영업부 서울 1020000 140000 1160000 0 801010-2987897 1160000
정한국 홍보부 강원 880000 114000 994000 0 760909-1333333 994000
조미숙 홍보부 경기 1601000 103000 1704000 0 790102-2777777 1704000
황진이 개발부 인천 1100000 130000 1230000 0 810707-2574812 1230000
이현숙 총무부 경기 1050000 104000 1154000 0 800606-2954687 1154000
이상헌 개발부 경기 2350000 150000 2500000 50000 781010-1666678 2450000
엄용수 개발부 인천 950000 210000 1160000 0 820507-1452365 1160000
이성길 개발부 전북 880000 123000 1003000 0 801028-1849534 1003000
박문수 인사부 서울 2300000 165000 2465000 24650 780710-1985632 2440350
유영희 자재부 전남 880000 140000 1020000 0 800304-2741258 1020000
홍길남 개발부 경기 875000 120000 995000 0 801010-1111111 995000
이영숙 기획부 전남 1960000 180000 2140000 21400 800501-2312456 2118600
김인수 영업부 서울 2500000 170000 2670000 53400 731211-1214576 2616600
김말자 기획부 서울 1900000 170000 2070000 20700 830225-2633334 2049300
우재옥 영업부 서울 1100000 160000 1260000 0 801103-1654442 1260000
김숙남 영업부 경기 1050000 150000 1200000 0 810907-2015457 1200000
김영길 총무부 서울 2340000 170000 2510000 50200 801216-1898752 2459800
이남신 인사부 제주 892000 110000 1002000 0 810101-1010101 1002000
김말숙 총무부 서울 920000 124000 1044000 0 800301-2020202 1044000
정정해 총무부 부산 2304000 124000 2428000 24280 790210-2101010 2403720
지재환 기획부 서울 2450000 160000 2610000 52200 771115-1687988 2557800
심심해 자재부 전북 880000 108000 988000 0 810206-2222222 988000
김미나 영업부 서울 1020000 104000 1124000 0 780505-2999999 1124000
이정석 기획부 경기 1100000 160000 1260000 0 820505-1325468 1260000
정영희 개발부 인천 1050000 140000 1190000 0 831010-2153252 1190000
이재영 자재부 서울 960400 190000 1150400 0 701126-2852147 1150400
최석규 홍보부 인천 2350000 187000 2537000 50740 770129-1456987 2486260
손인수 영업부 부산 2000000 150000 2150000 21500 791009-2321456 2128500
고순정 영업부 경기 2010000 160000 2170000 21700 800504-2000032 2148300
박세열 인사부 경북 2100000 130000 2230000 22300 790509-1635214 2207700
문길수 자재부 충남 2300000 150000 2450000 24500 721217-1951357 2425500
채정희 개발부 경기 1020000 200000 1220000 0 810709-2000054 1220000
양미옥 영업부 서울 1100000 210000 1310000 0 830504-2471523 1310000
지수환 영업부 서울 1060000 220000 1280000 0 820305-1475286 1280000
홍원신 영업부 전북 960000 152000 1112000 0 690906-1985214 1112000
허경운 총무부 경남 2650000 150000 2800000 56000 760105-1458752 2744000
산마루 영업부 서울 2100000 112000 2212000 22120 780505-1234567 2189880
이기상 개발부 전남 2050000 106000 2156000 21560 790604-1415141 2134440
이미성 개발부 인천 1300000 130000 1430000 0 830908-2456548 1430000
이미인 홍보부 경기 1950000 103000 2053000 20530 810403-2828287 2032470
권영미 영업부 서울 2260000 104000 2364000 23640 790303-2155554 2340360
권옥경 기획부 경기 1020000 105000 1125000 0 820406-2000456 1125000
김싱식 자재부 전북 960000 108000 1068000 0 800715-1313131 1068000
정상호 홍보부 강원 980000 114000 1094000 0 810705-1212141 1094000
정한나 영업부 서울 1000000 104000 1104000 0 820506-2425153 1104000
전용재 영업부 인천 1950000 200000 2150000 21500 800605-1456987 2128500
이미경 자재부 경기 2520000 160000 2680000 53600 780406-2003214 2626400
김신제 기획부 인천 1950000 180000 2130000 21300 800709-1321456 2108700
임수봉 개발부 서울 890000 102000 992000 0 810809-2121244 992000
김신애 개발부 서울 900000 102000 1002000 0 810809-2111111 1002000
*/
--94. 부서별 평균 급여를 조회하되, A, B, C 등급으로 나눠서 출력.
-- 200만원 초과 - A등급
-- 150~200만원 - B등급
-- 150만원 미만 - C등급
-- 각 부서별로 평균 급여 구하기
SELECT BUSEO
, ROUND(AVG(BASICPAY + SUDANG)) "평균급여"
FROM TBL_INSA
GROUP BY BUSEO;
-- 등급 매기기
SELECT T.*
, CASE WHEN T.평균급여 > 2000000
THEN 'A등급'
WHEN T.평균급여 >= 1500000
THEN 'B등급'
WHEN T.평균급여 < 1500000
THEN 'C등급'
ELSE '알수없음'
END "평균급여등급"
FROM
(
SELECT BUSEO "부서명"
, ROUND(AVG(BASICPAY + SUDANG)) "평균급여"
FROM TBL_INSA
GROUP BY BUSEO
)T
ORDER BY 평균급여등급 ASC;
--==>>
/*
부서명 평균급여 평균급여등급
기획부 2020714 A등급
개발부 1528786 B등급
영업부 1753763 B등급
홍보부 1579500 B등급
인사부 1689350 B등급
자재부 1559400 B등급
총무부 1862286 B등급
*/
--
--95. 기본급+수당이 가장 많은 사람의 이름, 기본급+수당 조회.
-- MAX() 함수, 하위 쿼리 이용.
SELECT *
FROM TBL_INSA;
SELECT MAX(BASICPAY + SUDANG) "최대급여"
FROM TBL_INSA;
SELECT NAME "이름", BASICPAY + SUDANG "기본급+수당"
FROM TBL_INSA
WHERE (BASICPAY + SUDANG)
IN
(
SELECT MAX(BASICPAY + SUDANG) "최대급여"
FROM TBL_INSA
);
--==>>
/*
이름 기본급+수당
홍길동 2810000
*/
----------------------------------------------------------------------------
-- 다 풀었어요~!!!!!!!!!!!!!!!!!!!!!!!!
-- 와~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'[Oracle] > SQL (Program source)' 카테고리의 다른 글
| [Oracle] 20231031 [프로그램소스] - 20231031_01_HR (0) | 2023.11.01 |
|---|---|
| [Oracle] 20231030 [프로그램소스] - 20231030_01_HR, TBL_INSA(중간 수행평가) (0) | 2023.10.30 |
| [Oracle] 20231026 [프로그램소스] - 231026_01_SCOTT, 231026_02_HR, 231026_03_SCOTT (0) | 2023.10.26 |
| [Oracle] 20231025 [프로그램소스] - 20231025_01_scott (0) | 2023.10.25 |
| [Oracle] 20231024 [프로그램소스 - 그룹화] - 20231024_01_scott (0) | 2023.10.24 |