GROUP BY, HAVING SQL 예제 1, 2명 이상의 학생, 성적평균 80점이상인 학과, 학과코드, 학과명, 학생수

▣ GROUP BY, HAVING 예제 1

2013년 62번

정답 : 2번

 

-- 2013년 정보시스템감리사 DB 62번
-- 문제) 2명이상의 학생을 갖는 학과에 대해, 성적평균이 80점 이상인 학과의 학과코드, 학과명, 학생수를 검색하시오.

CREATE TABLE STU_62
(
  SNO INT
, SNAME VARCHAR(20) NOT NULL
, ADDRESS VARCHAR(20)
, SCORE INT
, DNO INT
, PRIMARY KEY (SNO)
)
;

INSERT INTO STU_62 VALUES (100, 'HONG', 'Seoul', 95, 100);
INSERT INTO STU_62 VALUES (300, 'LEE', 'Busan', 90, 200);
INSERT INTO STU_62 VALUES (200, 'KIM', 'jeju', 85, 100);
INSERT INTO STU_62 VALUES (500, 'HONG', 'Busan', 95, 300);
INSERT INTO STU_62 VALUES (400, 'SON', 'Seoul', 80, 300);

SELECT * FROM STU_62;

CREATE TABLE DEP_62
(
  DNO INT
, DNAME VARCHAR(20) NOT NULL
, PRIMARY KEY (DNO)
)
;

INSERT INTO DEP_62 VALUES (100, 'computer');
INSERT INTO DEP_62 VALUES (200, 'eletronics');
INSERT INTO DEP_62 VALUES (300, 'MIS');

SELECT * FROM DEP_62;

ALTER TABLE STU_62
ADD FOREIGN KEY (DNO) REFERENCES DEP_62(DNO);

SELECT * FROM STU_62 S, DEP_62 D
WHERE S.DNO = D.DNO;



-- 1) 보기
SELECT D.DNO, D.DNAME, COUNT(*)
FROM STU_62 S, DEP_62 D
WHERE S.DNO = D.DNO AND AVG(SCORE) >= 80
GROUP BY D.DNO, D.DNAME
HAVING COUNT(*) >= 2;

-- AVG(SCORE) >= 80 에러 발생 -> 그룹 function 조건 잘못 사용

--  WHERE 절에서 집계함수 사용 비교 불가

-- 2) 보기
SELECT D.DNO, D.DNAME, COUNT(*)
FROM STU_62 S, DEP_62 D
WHERE S.DNO = D.DNO 
GROUP BY D.DNO, D.DNAME
HAVING COUNT(*) >= 2 AND AVG(SCORE) >= 80;

-- 정답 

-- 3) 보기
SELECT D.DNO, D.DNAME, COUNT(*)
FROM STU_62 S, DEP_62 D
WHERE S.DNO = D.DNO AND 
 S.DNO = (SELECT DNO FROM STU_62 S
      GROUP BY DNO
      HAVING AVG(SCORE) >= 80)
GROUP BY D.DNO, D.DNAME;

-- WHERE절의 서브쿼리 리턴 결과가 1개 이상이여서 조건식에서 에러 발생

-- 4) 보기
SELECT D.DNO, D.DNAME, COUNT(*)
FROM STU_62 S, DEP_62 D
WHERE S.DNO = D.DNO AND 
 D.DNO IN (SELECT DNO FROM STU_62 S
      GROUP BY DNO
       HAVING AVG(SCORE) >= 80)
GROUP BY D.DNO, D.DNAME;

-- DNO 칼럼 정의 이슈, 2명이상 학생을 갖는 학과 조건 미흡