SQL 문제 풀이 | 응용 SQL

07. SQL 응용

Chapter 02. 응용 SQL

 

 

#01 - 20년 3회 

다음 조건을 만족하면서 과목별 점수의 평균이 90 이상인 과목이름, 최소점수, 최대점수를 구하는 SQL 문을 작성하시오.

 

• 대소문자를 구분하지 않습니다.
• WHERE 구문을 사용하지 않습니다.
• GROUP BY, HAVING 구문을 반드시 사용합니다.
• 세미콜론(;)은 생략 가능합니다.
• 별칭(AS)를 사용해야 합니다.

 

[ 성적 테이블 ]

과목코드	과목이름	학점	점수
1000	컴퓨터과학	A+	95
2000	운영체제	B+	85
1000	컴퓨터과학	B+	85
2000	운영체제	B	80

 

[ 결과 ]

과목이름	최소점수	최대점수
컴퓨터과학	85	95

 

 

 

 

A.

SELECT 과목이름, MIN(점수) AS 최소점수, MAX(점수) AS 최대점수
  FROM 성적
 GROUP BY 과목코드
HAVING AVG(점수) >= 90

 

 

 

해설.

과목별 점수 평균을 구하기 위해서는 GROUP BY 절과 HAVING 절을 이용한다.

최소점수를 구하기 위해서는 MIN 함수, 최대점수를 구하기 위해서는 MAX 함수를 이용한다.

 

 

 

 

#02 - 20년 4회 

다음 조건을 만족하면서 학과별로 튜플 수가 얼마인지 구하는 SQL 문을 작성하시오.

 

• 대소문자를 구분하지 않습니다.
• WHERE 구문을 사용하지 않습니다.
• GROUP BY를 사용합니다.
• 세미콜론(;)은 생략 가능합니다.
• 별칭(AS)를 사용해야 합니다. (별칭 사용 시 별칭은 작은 따옴표를 써야 합니다.)
• 집계 함수를 사용해야 합니다.

 

[ 학생 테이블 ]

학과	학생
전기	이순신
컴퓨터	안중근
컴퓨터	윤봉길
전자	이봉창
전자	강우규

 

[ 결과 ]

학과	학과별튜플수
전기	1
컴퓨터	2
전자	2

 

 

 

A.

SELECT 학과, COUNT(학과) AS 학과별튜플수
  FROM 학생
 GROUP BY 학과

 

 

해설.

• 학과에 대한 집계를 하기 위해서는 GROUP BY 절에 학과를 지정한다.
• 학과별 튜플 개수를 구하기 위해서는 COUNT 함수를 사용해야 하며, COUNT 함수에는 행의 개수를 판단할 속성을 파라미터로 넣는다.
• COUNT(학과), COUNT(*) 모두 행의 개수를 판단할 수 있으므로 둘 중 아무거나 사용해도 무방하다.

 

 

#03 - 21년 1회 

다음 SQL 실행 결과를 숫자만 쓰시오.

 

[ 급여 테이블 ]

EMPNO	SAL
100	1000
200	3000
300	1500

 

SELECT COUNT(*)
  FROM 급여
 WHERE EMPNO > 100 AND SAL >= 3000 OR EMPNO =200;

 

 

 

 

A. 1

 

 

 

 

해설.

• EMPNO > 100 이므로 EMPNO가 100 초과인 200, 300이 해당하며 AND 조건에 따라 SAL >= 3000인 200만 해당한다.
• OR 조건에 따라 ENPNO가 200인 행도 추가되지만 앞에 조건과 동일한 결과이므로 결국 EMPNO가 200이면서 SAL이 3000인 행 1개만 남게된다.

EMPNO	SAL
200	3000

 

COUNT(*)

1

 

 

 

Q. AND와 OR이 같이 있을 때 우선순위는 어떻게 되는가?

A. 결론부터 말하자면 AND > OR 순이다. AND의 우선순위가 먼저이기 때문에 AND 연산이 수행되고 OR 연산이 적용된다.


위에 코드
WHERE EMPNO > 100 AND SAL >= 3000 OR EMPNO =200 은 
AND를 먼저하든 OR을 먼저하든 결과가 똑같은 양상이 되지만

만약 
WHERE EMPNO > 100 AND SAL >= 3000 OR EMPNO =100 이었다면

① AND를 먼저할 경우
EMPNO가 100 초과이면서 SAL이 3000 이상인 튜플 200 1개와 
EMPNO가 100 인 튜플 1개로
총 2개의 튜플이 된다

② OR를 먼저할 경우
EMPNO가 100 초과인 튜플 200,300 2개 중 
SAL이 3000 이상이거나 EMPNO가 100인 튜플 200,100 2개에서 겹치는 200
총 1개의 튜플이 된다

이렇게 쿼리 결과가 달라질 수 있기 때문에 
AND와 OR의 연산자 우선순위를 기억해놓는 것이 중요하다

 

 

 

 

 

#04 - 22년 2회

다음 복수의 행을 출력하는 SQL 문을 사용하여 제품 테이블에서 H라는 제조사의 모든 제품의 단가보다 비싼 단가의 상품명, 단가, 제조사를 출력하는 SQL 구문이다. 빈칸 (  )에 들어갈 내용은?

SELECT 상품명, 단가, 제조사
  FROM 제품
 WHERE 단가 > (   )
              (SELECT 단가
                 FROM 제품
                WHERE 제조사='H');

 

 

 

 

A. ALL

 

 

# 다중 행 연산자

※ IN, ANY, ALL, EXISTS

1. IN 
리턴값 중 조건에 해당하는 값 있으면 참

2. ANY
서브쿼리에 의해 리턴되는 각각의 값과 조건을 비교하여 하나 이상을 만족하면 참

3. ALL
서브쿼리에 의해 리턴되는 모든 값과 조건 값을 비교하여 모든 값을 만족해야만 참

4. EXISTS
메인 쿼리의 비교 조건이 서브쿼리의 결과 중에서 만족하는 값이 하나라도 존재하면 참

 

 

해설.

다중 행 연산자로 IN, ANY, SOME, ALL, EXISTS 를 사용한다.

다중 행 비교 연산자는 단일 행 비교 연산자 (<, >, =, <>)와 결합하여 사용할 수 있다.

 

서브쿼리로 도출된 모든 값이 조건을 만족해야하므로 ALL 연산자를 사용한다.

 

1. IN - 리턴되는 값 중 조건에 해당하는 값이 있으면 참

2. ANY(=SOME) - 서브쿼리 리턴 값 각각과 조건을 비교하여 하나 이상을 만족하면 참 

3. ALL - 서브쿼리 리턴 값 모두와 조건값을 비교하여 모든 값을 만족해야 참

4. EXISTS - 메인 쿼리 비교 조건이 서브쿼리 결과 중 만족하는 값이 하나라도 존재하면 참

 

 

 

#05 - 22년 2회, 23년 1회

다음 중 [성적] 테이블에서 과목별로 그룹을 묶었을 때 과목 평균이 90 이상인 과목, 최소점수, 최대점수를 조회하는 쿼리를 작성하시오.

 

[ 성적 테이블 ]

순서	과목	점수
1	데이터베이스	91
2	데이터베이스	92
3	네트워크	78
4	소프트웨어 공학	60
5	네트워크	89
6	소프트웨어 공학	91

 

 

[ 결과 ]

과목	최소점수	최대점수
데이터베이스	91	92

 

 

 

 

 

A. 

SELECT 과목, MIN(점수) AS 최소점수, MAX(점수) AS 최대점수
  FROM 성적
 GROUP BY 과목
HAVING AVG(점수) >= 90

 

 

 

 

해설.

MIN(점수), MAX(점수)는 각각 해당 컬럼에서 가장 작은 값과 가장 큰 값을 가져오는 집계함수이다. 

AS 키워드를 이용하여 각 컬럼에 별칭을 지정한다.

GROUP BY 과목 을 통해 과목 컬럼을 기준으로 그룹화한다.

과목 컬럼 값이 같은 행들이 하나의 그룹으로 묶이게 된다.

HAVING AVG(점수) >= 90 은 그룹화된 데이터 중 점수 컬럼의 평균이 90 이상인 데이터만 출력한다.

이때 GROUP BY 구문을 이용하여 그룹화된 데이터 중 HAVING 조건에 맞는 데이터만 추출한다.

 

 

 

 

---

 

 

 

예상 문제

 

#01. 다음은 급여 테이블이다. 부서명과 부서별 급여의 평균을 구하는 쿼리를 구하시오.

 

[ 급여 테이블 ]

이름	직책	부서	급여
문무왕	차장	마케팅	5,000
박혁거세	차장	전산	4,800
장보고	사원	마케팅	4,000
김유신	사원	마케팅	4,000

 

 

 

 

A.

SELECT 부서, AVG(급여) AS 급여 평균 
  FROM 급여
 GROUP BY 부서;

 

 

 

 

해설.

GROUP BY 절은 속성값을 그룹으로 분류하고자 할 때 사용한다.

부서별로 구분해야 하므로 GROUP BY 절에 '부서' 컬럼이 들어가야 하며

SELECT 절에서는 '부서'별 평균이 어떤 값인지 알아야 하므로 '부서'의 AVG(급여)가 포함되어야 한다.

 

 

 

 

 

#02. 다음은 급여 테이블이다. 부서와 직책별 급여의 합계를 구하는 쿼리를 구하시오.

 

[ 급여 테이블 ]

이름	직책	부서	급여
문무왕	차장	마케팅	5,000
박혁거세	차장	전산	4,800
장보고	사원	마케팅	4,000
김유신	사원	마케팅	4,000

 

 

 

 

A. 

SELECT 부서, 직책, SUM(급여)
  FROM 급여
 GROUP BY 부서, 직책;

 

 

 

 

해설.

GROUP BY 절은 속성값을 그룹으로 분류하고자 할 때 사용한다.

부서와 직책별로 구분해야 하므로 GROUP BY 절에는 '부서', '직책' 컬럼이 들어가야 하며,

SELECT 절에서는 '부서'와 '직책' 별 급여 합계 값이 어떤 값인지 알아야 하므로 SUM(급여)가 포함되어야 한다. 

 

 

 

#03. 다음은 학생 테이블의 일부이다. 평균 성적이 4.0을 초과하는 학생의 이름을 출력하는 쿼리를 작성하시오.

 

[ 학생 테이블 ]

이름	과목	성적
문무왕	프로그래밍	4.5
문무왕	알고리즘	4.5
장보고	알고리즘	3.5
장보고	자료구조	4.5

 

 

 

 

 

A. 

SELECT 이름
  FROM 학생
 GROUP BY 이름
HAVING SUM(성적) > 4.0

 

 

 

 

해설.

GROUP BY 절은 속성값을 그룹으로 분류하고자 할 때 사용한다.

이름에 따라 평균 성적을 구하려는 쿼리를 구하기 위해서는 GROUP BY 절에 '이름' 컬럼을 추가해야 한다.

HAVING 절은 GROUP BY에 의해 분류한 후 그룹에 대한 조건을 지정할 떄 사용한다.

평균 성적이 4.0을 초과하는지 확인하기 위해 HAVING 절에 평균 성적이 4.0보다 크다라는 조건의 쿼리를 작성한다.

이름만 출력해야 하므로 SELECT 절에는 '이름' 컬럼만 조회한다.