4일차_HAVING, INSA 테이블 생성 및 관련 문제

---------------------------------
SELECT 컬럼리스트,...
 FROM 테이블,...
 WHERE 조건식
 GROUP BY 기준컬럼, ...
 HAVING 조건식
 ORDER BY 기준컬럼,...

-------------------------
HAVING 구문

1. 그룹 또는 집계에 대한 검색 조건을 지정한다. HAVING은 SELECT 문하고만 사용될 수 있으며, 일반적으로 GROUP BY 절에 사용된다. GROUP BY를 사용하지 않으면 HAVING은 WHERE 절 처럼 실행된다.

employees 테이블에서 부서별(department_id)로 그룹을 만든 후 출력하되 각 그룹별 직원수 출력. 단, 직원수가 5명 이상인 경우만 출력.
SELECT department_id, COUNT(*) AS 그룹별직원수 FROM employees
 --WHERE 조건으로는 직원수를 확인할 수 없다.
 GROUP BY department_id
 HAVING COUNT(*) >= 5
 ORDER BY 그룹별직원수 DESC;

출력결과)
department_id 그룹별직원수
-----------------------------
50  45
80  34
30  6
100  6
60  5

 

employees 테이블에서 직무별(job_id) 직원수 출력. 단, 직원수가 5명 이상인 경우만 출력.
SELECT job_id, COUNT(*) AS 그룹별직원수 FROM employees
 GROUP BY job_id
 HAVING COUNT(*) >= 5;

문제) employees 테이블에서 직무별(job_id) 직원의 평균급여 출력. 단, 평균급여가 10000 이상인 경우만 출력.
SELECT job_id, ROUND(AVG(salary)) AS 그룹별평균급여
 FROM employees
 GROUP BY job_id
 HAVING AVG(salary) >= 10000
 ORDER BY 그룹별평균급여 DESC;

문제) employees 테이블에서 job_id별 직원의 수를 출력. 단, 직원의 수가 10명 이상인 경우만 출력하고, job_id가 'CLERK'로 끝나는 경우는 제외한다.
SELECT job_id, COUNT(*) AS 그룹별직원수 FROM employees
 WHERE job_id NOT LIKE '%CLERK'
 GROUP BY job_id
 HAVING COUNT(*) >= 10; 

출력결과)
job_id 그룹별직원수
-----------------------
SA_REP 30

문제) employees 테이블에서 job_id별 직원의 평균급여(AVG함수)를 출력. 단, 평균급여가 10000 이상(HAVING구문)이고, 직무(job_id)가 'MAN'으로 끝나는 경우(WHERE 구문)만 출력.

문제) employees 테이블에서 입사년도(hire_date)별 직원들의 평균급여(AVG함수) 출력. 단, 평균급여가 10000 이상인 경우(HAVING 구문)만 출력.

 

 

 

--------------------------------------------------
사용자 계정(scott) 생성

1. 관리자로 로그인.

2. 계정 생성
CREATE USER scott IDENTIFIED BY 암호지정;

--계정 확인
SELECT * FROM dba_users;

3. 기본 테이블스페이스 지정
ALTER USER scott DEFAULT TABLESPACE USERS;

4. 권한 부여
GRANT CONNECT, RESOURCE, CREATE VIEW TO scott;

5. 새로운 계정으로 로그인 시도.

-------------------------------------------------
INSA 테이블 생성 및 데이터 입력

1. SCOTT 계정으로 로그인
2. SQL 파일 실행 (insa.sql 파일을 적당한 경로(D:\)에 복사한 후 실행할 것)
-> @D:\insa.sql
3. 실행 결과 확인.
-> SELECT * FROM insa;

-----------------------------------------------------
여기서부터는 Scott 계정으로 로그인한 후 작업합니다.
-----------------------------------------

1. INSA 테이블 전체자료 출력
SELECT * FROM insa;
 
2. SCOTT 사용자 소유 테이블 목록 확인
SELECT * FROM tab;

3. INSA 테이블 구조 확인
DESC 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)

4. insa 테이블의 이름(name), 기본급(basicpay) 출력
SELECT name, basicpay FROM insa;

5. insa 테이블의 이름(name), 기본급(basicpay), 수당(sudang), 기본급+수당 출력
SELECT name, basicpay, sudang, basicpay+sudang AS 급여 FROM insa;

6. 이름(name), 출신도(city), 부서명(buseo) 출력. 별명 사용.

7. 서울 사람의 이름(name), 출신도(city), 부서명(buseo), 직위(jikwi) 출력. WHERE 구문 추가.

8.출신도가 서울 사람이면서 -->WHERE 구문
 기본급이 150만원 이상인 사람 -->WHERE 구문
 출력 (name, city, basicpay, ssn)

9.출신도가 '인천' 이면서, 기본급이 100만원~200만원인 경우만 출력.
SELECT * FROM insa
 WHERE city='인천' AND
  basicpay BETWEEN 1000000 AND 2000000;

 
10.출신도가 서울 사람이거나 부서가 개발부인 자료 출력 (name, city, buseo)

 
11.출신도가 서울, 경기인 사람만 출력 (name, city, buseo). IN 연산자 사용.

12.부서가 '개발부', '영업부'인 사람만 출력. IN 연산자 사용.
SELECT * FROM insa
 WHERE buseo IN ('개발부', '영업부');

13. 급여(basicpay + sudang)가 250만원 이상인 사람. --> WHERE 구문
단 필드명은 한글로 출력. -->별칭
(name, basicpay, sudang, basicpay+sudang)
 

14. 주민번호를 기준으로 남자(성별란이 1, 3)만 출력. (이름, 주민번호(ssn)).
SUBSTR() 함수 이용.

 
15. 주민번호를 기준으로 80년대 태어난 사람만 출력. (이름, 주민번호(ssn)).
SUBSTR() 함수 이용.
SELECT * FROM insa
 WHERE SUBSTR(ssn, 1, 1) = '8';

16. 서울 사람 중에서 70년대 태어난 사람만 출력. SUBSTR() 함수 이용.

17. 서울 사람 중에서 70년대 태어난 남자만 출력. SUBSTR() 함수 이용.
SELECT * FROM insa
 WHERE city = '서울'
  AND SUBSTR(ssn, 1, 1) = '7'
  AND SUBSTR(ssn, 8, 1) IN ('1', '3');
  
 
18. 서울 사람이면서 김씨만 출력(성씨가 1자라는 가정). (이름, 출신도).
SUBSTR() 함수 이용.
SELECT name, city FROM insa
 WHERE city = '서울'
  AND SUBSTR(name, 1, 1) = '김';

19. 2000년도에 입사한 사람 출력. (이름, 출신도, 입사일).
SUBSTR() 또는 TO_CHAR() 함수 이용.

20. 2000년 10월에 입사한 사람 출력. (이름, 출신도, 입사일).

21. 주민번호를 기준으로 직원의 나이 구하기(단, 모든 직원이 1900년대에 태어났다는 가정). (이름, 주민번호, 나이)

시간 간격 계산
일 기준 -> 날짜 - 날짜
월 기준 -> MONTHS_BETWEEN()
년 기준 -> 일기준 계산 결과 / 365 또는 월기준 계산 결과 / 12

기준년도 - 생년월일중에서 년도 + 1

22. 주민번호 기준으로 현재 나이대가 30대인 사람만 출력.

23. 주민번호 기준으로 5월달생만 출력. SUBSTR() 함수 이용.

24. 주민번호 기준으로 5월달생만 출력.
주민번호의 생년월일 부분만을 추출->날짜형 자료로 변환->월 부분만 추출.
SELECT * FROM insa
  WHERE TO_CHAR(TO_DATE(SUBSTR(ssn, 1, 6)), 'MM') = 5;

 
25. 출신도 내림차순으로 정렬하고, 출신도가 같으면 기본급 내림차순

 
26. 서울 사람 중에서 기본급+수당(->급여) 내림차순으로 정렬.
(이름, 출신도, 기본급+수당)

 
27. 여자 중 부서 오름차순으로 정렬하고, 부서가 같으면 기본급 내림차순 정렬. (이름, 주민번호, 부서, 기본급)
SELECT name, ssn, buseo, basicpay FROM insa
 WHERE SUBSTR(ssn, 8, 1) IN ('2', '4')
 ORDER BY buseo ASC, basicpay DESC;

28. 남자 중에서 나이를 기준으로 오름차순 정렬해서 출력.
SELECT * FROM insa
 WHERE SUBSTR(ssn, 8, 1) IN ('1', '3')
 ORDER BY TO_DATE(SUBSTR(ssn, 1, 6)) ASC;

29. 서울 지역 사람들 중에서 입사일이 빠른 사람을 먼저 출력.
SELECT * FROM insa
 WHERE city = '서울'
 ORDER BY ibsadate ASC;

30. 성씨가 김씨가 아닌 사람 출력. (이름, 출신도, 기본급).
LIKE 연산자 또는 SUBSTR() 함수 이용.
SELECT * FROM insa
 WHERE name NOT LIKE '김%';

31. 출신도가 서울, 부산, 대구 이면서
전화번호에 5 또는 7이 포함된 자료 출력하되
부서명의 마지막 부는 출력되지 않도록함.
(이름, 출신도, 부서명, 전화번호)
SELECT name, city, RTRIM(buseo, '부') AS buseo, tel FROM insa
 WHERE city IN ('서울', '부산', '대구')
  AND (tel LIKE '%5%' OR tel LIKE '%7%');

 
32. 전화번호가 있으면 '-'을 제거하고 출력하고, 없으면 '전화번호없음'을 출력
SELECT name, NVL2(tel, REPLACE(tel, '-', ''), '전화번호없음') AS tel FROM insa;

 

33. HR계정, employees 테이블에서 커미션(commission_pct) 받는 사람의 수와 안받는 사람(null)의 수를 출력
--COUNT(), NVL2(), GROUP BY 구문

안 받는 직원->72
받는 직원->35

34. insa 테이블에서 basicpay+sudang가 100만원 미만
, 100만원 이상~200만원 미만, 200만원 이상인 직원들의 수 출력.
--GROUP BY 구문

SELECT DECODE(TRUNC((basicpay+sudang)/1000000), 0, 'C그룹', 1, 'B그룹', 2, 'A그룹'), COUNT(*) AS 그룹당직원수
 FROM insa
 GROUP BY DECODE(TRUNC((basicpay+sudang)/1000000), 0, 'C그룹', 1, 'B그룹', 2, 'A그룹');

35. 주민번호를 가지고 생년월일의 년도별 직원수 출력.

36. 주민번호를 기준으로 월별 오름차순, 월이 같으면 년도 내림차순 출력.
(이름, 주민번호) . SUBSTR() 함수 이용.
SELECT name, ssn FROM insa
 ORDER BY SUBSTR(ssn, 3, 2) ASC
  , TO_CHAR(TO_DATE(SUBSTR(ssn, 1, 6)), 'YYYY') DESC

37. 입사일을 기준으로  월별 오름차순, 월이 같으면 년도 내림차순 출력.
주의. 입사일은 자료형이 DATE입니다.
주의. 2000년도 이후 입사자는 내림차순 지정시 뒤로 갈수 있으므로 주의할 것.

SUBSTR(원본문자열, 시작위치, 글자수)

SELECT * FROM insa
 ORDER BY TO_CHAR(ibsadate, 'MM') ASC
  , TO_CHAR(ibsadate, 'YYYY') DESC;

38. 전체인원수, 남자인원수, 여자인원수를 동시 출력.
SELECT COUNT(*) AS 전체직원수
        ,COUNT(DECODE(SUBSTR(ssn, 8, 1), '1', 1)) AS 남자직원수
        ,COUNT(DECODE(SUBSTR(ssn, 8, 1), '2', 1)) AS 여자직원수
  FROM insa;

39. 개발부, 영업부, 총무부 인원수 출력. COUNT(), DECODE() 함수 이용.
개발부->14
영업부->16
총무부->7

40. 서울 사람의 남자 인원수 출력.
SELECT COUNT(*) AS 서울_남자직원수
 FROM insa
 WHERE city='서울' AND SUBSTR(ssn, 8, 1)='1';

41. 부서가 영업부이고, 남자인 경우, 여자인 경우 인원수 출력.  COUNT(), DECODE() 함수 이용.
SELECT COUNT(DECODE(SUBSTR(ssn, 8, 1), '1', 1)) AS 영업부_남자직원수
 ,COUNT(DECODE(SUBSTR(ssn, 8, 1), '2', 1)) AS 영업부_여자직원수
 FROM insa
 WHERE buseo='영업부'; 

42. 개발부, 영업부, 총무부 인원수 출력. 단, 지역은 '서울'로 한정.

43. 서울 사람의 남자와 여자의 기본급합 출력.

서울, 남자, 기본급합 출력
--> SELECT SUM(basicpay) AS 서울_남자_기본급합
 FROM insa
 WHERE city='서울' AND SUBSTR(ssn, 8, 1)='1';
--> SELECT SUM(DECODE(SUBSTR(ssn, 8, 1), '1', basicpay))
  AS 서울_남자_기본급합
 FROM insa
 WHERE city='서울';

SELECT SUM(DECODE(SUBSTR(ssn, 8, 1), '1', basicpay))
  AS 서울_남자_기본급합
 ,SUM(DECODE(SUBSTR(ssn, 8, 1), '2', basicpay))
  AS 서울_여자_기본급합
 FROM insa
 WHERE city='서울';

 

44. 남자와 여자의 기본급(basicpay) 평균값 출력. AVG(), DECODE() 함수 이용.

남자 평균->1676168
여자 평균->1428634

45. 개발부 남자와 개발부 여자의 기본급 평균값 출력. AVG(), DECODE() 함수 이용.

개발부 남자 평균->1392500
개발부 여자 평균->1384375

46. 부서별 남자와 여자 인원수 구하기

47. 지역별 남자와 여자 인원수 구하기

48. 입사년도별 남자와 여자 인원수 구하기

49. 영업부, 총무부 인원만을 가지고 입사년도별 남자와 여자 인원수 구하기

50. 서울 사람중 부서별 남자와 여자인원수, 남자와 여자 급여합 출력.
SELECT buseo
 , COUNT(*) AS 부서별직원수
 , COUNT(DECODE(SUBSTR(ssn, 8, 1), '1', 1)) AS 남자직원수
 , COUNT(DECODE(SUBSTR(ssn, 8, 1), '2', 1)) AS 여자직원수
 , SUM(DECODE(SUBSTR(ssn, 8, 1), '1', basicpay))
  AS 남자기본급합
 , SUM(DECODE(SUBSTR(ssn, 8, 1), '2', basicpay))
  AS 여자기본급합
 FROM insa
 WHERE city='서울'
 GROUP BY buseo;

51. 부서별 인원수 출력. 인원수가 10 이상인 경우만.
SELECT buseo
 , COUNT(*) AS 부서별직원수
 FROM insa
 GROUP BY buseo
 HAVING COUNT(*) >= 10;

52. 부서별 남,여 인원수 출력. 여자인원수가 5명 이상인 부서만 출력.
SELECT buseo
 , COUNT(*) AS 부서별직원수
 , COUNT(DECODE(SUBSTR(ssn, 8, 1), '1', 1)) AS 남자직원수
 , COUNT(DECODE(SUBSTR(ssn, 8, 1), '2', 1)) AS 여자직원수
 FROM insa
 GROUP BY buseo
 HAVING COUNT(DECODE(SUBSTR(ssn, 8, 1), '2', 1)) >= 5;

53. 부서별 평균 급여를 출력하되, A, B, C 등급으로 나눠서 출력.
200만원 초과 - A등급
150~200만원 - B등급
150만원 미만 - C등급

SELECT buseo
  , ROUND(AVG(basicpay+sudang))
  , CASE
      WHEN ROUND(AVG(basicpay+sudang))>2000000 THEN 'A등급'
      WHEN ROUND(AVG(basicpay+sudang)) BETWEEN 1500000 AND 2000000 THEN 'B등급'
      WHEN ROUND(AVG(basicpay+sudang))<1500000 THEN 'C등급'
    END AS 등급
  FROM insa
  GROUP  BY buseo;

 
54. 이름, 성별, 나이 출력
       성별: 주민번호 1,3->남자, 2,4->여자 (DECODE 사용)
       나이: 주민번호 이용해서

 
55. 서울 사람 중에서 기본급이 200만원 이상이 사람. (이름, 기본급)

 
56. 입사월별 인원수 구하기. (월, 인원수)   COUNT, GROUP BY, TO_CHAR 사용
        출력형태 ----------
        월      인원수
        1월    10명
        2월    25명

SELECT TO_NUMBER(TO_CHAR(ibsadate, 'MM'))||'월' AS 입사월
      , COUNT(*) AS 그룹별직원수
  FROM insa
  GROUP BY TO_CHAR(ibsadate, 'MM')
  ORDER BY TO_CHAR(ibsadate, 'MM');

57. 이름, 생년월일, 기본급, 수당을 출력.
       생년월일은 주민번호 기준 (2000-10-10 형식으로 출력)
       기본급은 \1,000,000 형식으로 출력

58. 이름, 출신도, 기본급을 출력하되 출신도 내림차순 출력(1차 정렬 기준).
출신도가 같으면 기본급 오름차순 출력(2차 정렬 기준).

 
 
59. 전화번호가 NULL이 아닌것만 출력. (이름, 전화번호)

 
60. 근무년수가 10년 이상인 사람 출력. (이름, 입사일)
SELECT name, ibsadate, FLOOR(MONTHS_BETWEEN(SYSDATE, ibsadate)) AS 근무개월수
  FROM insa
  WHERE FLOOR(MONTHS_BETWEEN(SYSDATE, ibsadate))>=120;

 
61. 주민번호를 기준으로 75~82년생 출력. (이름, 주민번호, 출신도).
SUBSTR() 함수, BEWTEEN AND 구문, TO_NUMBER() 함수 이용.
SELECT name, ssn, city
  FROM insa
  WHERE TO_NUMBER(SUBSTR(ssn, 1, 2)) BETWEEN 75 AND 82;

62. 근무년수가 5~10년인 사람 출력. (이름, 입사일)

 
63. 김씨, 이씨, 박씨만 출력 (이름, 부서). SUBSTR() 함수 이용.

64. 입사일을 "년-월-일 요일" 형식으로 남자만 출력 (이름, 주민번호, 입사일)

65. 부서별 직위별 급여합 구하기. (부서, 직위, 급여합)
SELECT buseo, jikwi, COUNT(*), SUM(basicpay), ROUND(AVG(basicpay))
  FROM insa
  GROUP BY buseo, jikwi
  ORDER BY buseo;

 
66. 부서별 직위별 인원수를 구하되 인원수가 5명 이상인 경우만 출력.
 

67. 2000년에 입사한 여사원. (이름, 주민번호, 입사일)

68. 성씨가 한 글자(김, 이, 박 등)라는 가정하에 성씨별 인원수 (성씨, 인원수)
 

69. 출신도(CITY)별 성별 인원수.

70. 부서별 남자인원수가 5명 이상인 부서와 남자인원수.

 
71. 입사년도별 인원수.

71. 전체인원수, 2000년, 1999년, 1998년도에 입사한 인원을 다음의 형식으로 출력.
        전체 2000 1999 1998
         60    x    x    x

72. 아래 형식으로 지역별 인원수 출력.
        전체 서울  인천  경기
         60    x     x     x

 지역명 인원수
 서울   XX
 인천   XX
 경기   XX

--------------------------------------------------------------