GROUP BY, HAVING SQL 예제 4, 3명 이상의 사원이 근무하는 부서에서 연봉이 4만불 이상 사원수 검색

▣ GROUP BY, HAVING SQL 예제 4, 3명 이상의 사원이 근무하는 부서에서 연봉이 4만불 이상 사원수 검색

2017년 59번

정답 : 3번

▶ 부서의 사원이 3명 이상인 부서 조회
SELECT Dno
FROM EMPLOYEE
GROUP BY Dno
HAVING COUNT(*) > 2

 

▶ 위의 해당하는 데이터에서 연봉(Salary)이 40000 이상인 사원수를 조회
SELECT Dnumber, Dname, COUNT(*)
FROM DEPARTMENT, EMPLOYEE
WHERE Dnumber = Dno AND Salary >= 40000 

 

▶ 사원수를 집계해야 하므로 Dname 수준까지 그룹핑. Group by Dnumber, Dname

 

1)번 GROUP BY 절에 Dname이 빠져 구문 오류

2)번 COUNT(*) > 2 조건이 WHERE 절에 기술되어 구문 오류 발생

4)번 서브쿼리에서부서원이 3명이상이지만,

WHERE SALARY >= 40000 조건에 의해

4만불 이상의 사원수만 집계되어서, 문제의 질의에 부합하지 못함

문제는 '3명 이상의 사원이 근무하는 부서에서 연봉이 4만불 이상 사원수 검색'임

---

--정보시스템감리사 2017년 59번

DROP TABLE TB_EMP_59 ;
DROP TABLE TB_DEPT_59 ;

CREATE TABLE TB_EMP_59
(
  NAME VARCHAR2(50) NOT NULL
, SSN NUMBER
, SALARY NUMBER
, SEX VARCHAR2(2) NULL
, DNO NUMBER
, CONSTRAINT TB_EMP_59_PK PRIMARY KEY(SSN)
)
;


CREATE TABLE TB_DEPT_59
(
  DNAME VARCHAR2(50)
, DNUMBER NUMBER
, MGR_SSN NUMBER
, CONSTRAINT TB_DEPT_59_PK PRIMARY KEY(DNUMBER)
)
;

ALTER TABLE TB_EMP_59
ADD CONSTRAINT TB_EMP_59_FK FOREIGN KEY (DNO) REFERENCES TB_DEPT_59(DNUMBER);

INSERT INTO TB_DEPT_59 VALUES ('데이터개발팀', 101, 1);
INSERT INTO TB_DEPT_59 VALUES ('데이터플랫폼팀', 102, 1);
INSERT INTO TB_DEPT_59 VALUES ('데이터사이언스팀', 103, 2);

COMMIT;

INSERT INTO TB_EMP_59 VALUES ('이경오', 1, 75000, '남', 101);
INSERT INTO TB_EMP_59 VALUES ('김연경', 2, 80000, '여', 102);
INSERT INTO TB_EMP_59 VALUES ('김철수', 3, 45000, '남', 101);
INSERT INTO TB_EMP_59 VALUES ('박상진', 4, 35000, '남', 101);
INSERT INTO TB_EMP_59 VALUES ('박은정', 5, 30000, '여', 101);
INSERT INTO TB_EMP_59 VALUES ('김다연', 6, 45000, '여', 102);
INSERT INTO TB_EMP_59 VALUES ('박수진', 7, 45000, '여', 102);
INSERT INTO TB_EMP_59 VALUES ('김성수', 8, 60000, '남', 102);
INSERT INTO TB_EMP_59 VALUES ('전현무', 9, 45000, '남', 102);
INSERT INTO TB_EMP_59 VALUES ('박나래', 10, 50000, '여', 103);

COMMIT;

SELECT * FROM TB_EMP_59;

SELECT * FROM TB_DEPT_59;

-- 보기 1번
SELECT DNUMBER, DNAME, COUNT(*)
FROM TB_DEPT_59, TB_EMP_59
WHERE DNUMBER = DNO AND
  SALARY >= 40000 AND
  DNO IN ( SELECT DNO
   FROM TB_EMP_59
   GROUP BY DNO HAVING COUNT(*) > 2)
GROUP BY DNUMBER;

 

-- GROUP BY 절에 DNAME이 빠져 있어서, 구문오류발생

-- 보기 2번
SELECT DNUMBER, DNAME, COUNT(*)
FROM TB_DEPT_59, TB_EMP_59
WHERE DNUMBER = DNO AND
  SALARY >= 40000 AND
  COUNT(*) > 2
GROUP BY DNUMBER, DNAME;

 

-- COUNT(*) > 2 조건이 WHERE 절에 기술되어 구문 오류 발생

-- 보기 3번
SELECT DNUMBER, DNAME, COUNT(*)
FROM TB_DEPT_59, TB_EMP_59
WHERE DNUMBER = DNO AND
  SALARY >= 40000 AND
  DNO IN ( SELECT DNO
   FROM TB_EMP_59
   GROUP BY DNO HAVING COUNT(*) > 2)
GROUP BY DNUMBER, DNAME;

 

-- 정답

* 세부 내역 확인 SQL

SELECT DNUMBER, DNAME, NAME, SALARY
FROM TB_DEPT_59, TB_EMP_59
WHERE DNUMBER = DNO AND
  SALARY >= 40000 AND
  DNO IN ( SELECT DNO
   FROM TB_EMP_59
   GROUP BY DNO HAVING COUNT(*) > 2)
ORDER BY DNUMBER DESC;

-- 보기 4번
SELECT DNUMBER, DNAME, COUNT(*)
FROM TB_DEPT_59, TB_EMP_59
WHERE DNUMBER = DNO AND
  DNO IN ( SELECT DNO
   FROM TB_EMP_59 WHERE SALARY >= 40000
   GROUP BY DNO HAVING COUNT(*) > 2
GROUP BY DNUMBER, DNAME;

 

-- 서브쿼리에서부서원이 3명이상이지만,

WHERE SALARY >= 40000 조건에 의해

4만불 이상의 사원수만 집계되어서, 문제의 질의에 부합하지 못함

문제는 '3명 이상의 사원이 근무하는 부서에서 연봉이 4만불 이상 사원수 검색'임