[Spring반] 중간시험 32 - SQL 응용 시험 2 : 문제 4~6번

이번에도 세 문제 정도 SQL 문제를 풀어보려고 한다.

---

 

 

 

 

SQL 응용 시험 두 번째

 

문제 4번

DML, DDL, DCL에 해당하는 문법을 각각 2가지 이상 기술하시오.

예) TCL : COMMIT, ROLLBACK, SAVEPOINT

 

 

1. DML (Data Manipulation Language)

• 데이터베이스에서 데이터를 조작(조회, 삽입, 수정, 삭제)하는 데 사용하는 문법

1. SELECT : 데이터를 조회
2. INSERT : 데이터를 삽입
3. UPDATE : 데이터를 수정
4. DELETE : 데이터를 삭제

 

2. DDL (Data Definition Language)

• 데이터베이스의 구조(테이블, 뷰, 스키마 등)를 정의하거나 수정하는 데 사용하는 문법

1. CREATE : 새로운 테이블, 뷰, 스키마 등을 생성
2. ALTER : 기존 객체(테이블, 뷰 등)의 구조를 변경
3. DROP : 기존 객체를 삭제
4. TRUNCATE : 테이블의 모든 데이터를 삭제하고 초기화

 

3. DCL (Data Control Language)

• 데이터베이스의 접근 권한과 보안을 제어하는 데 사용하는 문법

1. GRANT : 특정 사용자에게 권한 부여
2. REVOKE : 특정 사용자로부터 권한 회수

 

요약

• DML : SELECT, INSERT, UPDATE, DELETE
• DDL : CREATE, ALTER, DROP, TRUNCATE
• DCL : GRANT, REVOKE

---

 

 

 

 

문제 5번

아래의 SQL구문은 EMPLOYEES 테이블에서 JOB_ID별 평균 연봉이 13000을 초과하는 JOB_ID와 평균연봉을 출력하려고 합니다. 하지만 아래 쿼리는 오류가 발생합니다. 원인과 올바르게 수정한 조치사항을 각각 기술하시오. [연봉 단위 = '만' ]

 

■ 실행 계정 : hr --실행해본 쿼리 
SELECT JOB_ID , AVG(SALARY) AS 평균연봉
  FROM EMPLOYEES 
WHERE AVG(SALARY) > 13000 
GROUP BY JOB_ID ; 

 

--출력 결과 

 

--출력하고자 하는 결과

---

 

 

 

 

 

 

문제 원인 분석

ORA-00934: group function is not allowed here 오류는 WHERE 절에서 집계 함수(AVG)를 사용할 수 없기 때문에 발생한 오류이다.
집계 함수는 그룹화된 데이터에 대해 연산을 수행하므로, 이를 필터링하려면 WHERE 절이 아니라 HAVING 절을 사용해야 한다.

 

해결 방법

HAVING 절은 GROUP BY로 그룹화된 데이터에 조건을 적용할 때 사용되므로, 쿼리를 다음과 같이 수정해야 한다.

 

 

 

수정 사항 설명

1. 집계 함수는 HAVING 절에서 사용
   • WHERE 절은 집계 함수가 적용되기 전에 조건을 필터링
   • 반면, HAVING 절은 GROUP BY로 그룹화된 결과에 대해 조건을 필터링
   • 따라서 AVG(SALARY) > 13000 조건은 HAVING에서 처리해야 함
2. GROUP BY와 HAVING 사용
   • GROUP BY는 JOB_ID로 데이터를 그룹화
   • HAVING은 AVG(SALARY)가 13,000을 초과하는 그룹만 필터링

 

수정된 쿼리를 실행하면 원하는 결과인 JOB_ID별 평균 연봉이 13,000을 초과하는 데이터를 확인할 수 있다.

 

결론

오류를 해결하려면 WHERE 대신 HAVING을 사용해서 집계 함수 조건을 필터링해야 한다.

---

 

 

 

 

문제 6번

조인을 이용하여 아래 조건에 맞게 쿼리를 작성하시오. (오라클방식, ANSI 조인방식 둘 중 아무거나 사용해도 됨)

 

EMPLOYEES 테이블과 DEPARTMENTS 테이블에서 동시에 데이터를 가져오기 위해 조인을 하려고 합니다. 두 테이블은 모두 DEPARTMENT_ID 컬럼을 가지고 이를 이용해 관계를 가지고 있습니다. 우리가 가져오고자 하는 컬럼 정보는 아래와 같습니다.
[ EMPLOYEES => EMPLOYEE_ID , EMAIL , SALARY ] 
[ DEPARTMENTS => DEPARTMENT_NAME , LOCATION_ID ] 
단, 직원의 연봉(SALARY)이 14000원 이상인 대상만 출력하도록 쿼리를 작성해주세요. (출력 순서는 상관없습니다)

 

--출력결과 

---

 

 

 

다음은 오라클에서 조인을 사용하여 문제 조건을 만족하는 쿼리를 작성한 예이다. ANSI 조인 방식을 사용했고, 직원의 연봉이 14,000 이상인 직원만 출력되도록 작성했다.

 

작성된 쿼리 (ANSI 조인 방식)

 

 

설명

1. EMPLOYEES와 DEPARTMENTS 테이블 조인
   • EMPLOYEES 테이블의 DEPARTMENT_ID와 DEPARTMENTS 테이블의 DEPARTMENT_ID를 ON 절로 연결
   • 두 테이블의 공통 키를 이용하여 데이터를 결합
2. 필터 조건
   • WHERE 절에서 E.SALARY >= 14000 조건을 추가하여 연봉 14,000 이상인 직원만 선택
3. 컬럼 선택
   • SELECT 절에서 필요한 컬럼만 선택: EMPLOYEE_ID, EMAIL, SALARY는 EMPLOYEES에서, DEPARTMENT_NAME와 LOCATION_ID는 DEPARTMENTS에서 가져옴
4. 별칭 사용
   • 테이블에 별칭(E, D)을 부여하여 쿼리 가독성을 높임

---

 

오라클 전통 조인 방식 (대안)

• 이 방식은 전통적인 오라클 조인 방식으로, WHERE 절에서 조인 조건과 필터 조건을 모두 설정해.

---

 

 

 

6-2. 아래 오라클 방식의 쿼리를 ANSI 방식으로 변경해 주시오. 

--오라클 방식 쿼리
SELECT A.DEPARTMENT_ID 
     , A.DEPARTMENT_NAME 
     , A.MANAGER_ID 
     , B.FIRST_NAME 
     , B.LAST_NAME 
   FROM DEPARTMENTS A 
     , EMPLOYEES B 
  WHERE A.MANAGER_ID = B.EMPLOYEE_ID(+)
     AND A.LOCATION_ID = 1700 ;

---

 

 

---

 

 

아래는 주어진 오라클 방식의 OUTER JOIN 쿼리를 ANSI 조인 방식으로 변환한 쿼리이다. (+) 연산자는 ANSI 방식에서 LEFT OUTER JOIN으로 표현된다.

 

ANSI 방식 쿼리

 

 

 

변경 사항 설명

1. OUTER JOIN 변환
   • 오라클 방식에서 B.EMPLOYEE_ID(+)는 ANSI 방식의 LEFT OUTER JOIN으로 변환됨
   • LEFT OUTER JOIN은 DEPARTMENTS 테이블의 모든 데이터를 유지하고, EMPLOYEES 테이블에 없는 데이터는 NULL로 채움
2. JOIN 조건
   • A.MANAGER_ID = B.EMPLOYEE_ID 조건은 ON 절에 작성
3. 추가 조건 유지
   • A.LOCATION_ID = 1700 조건은 WHERE 절에 유지

 

결과적으로 동일한 의미

변환 후의 ANSI 방식 쿼리는 기존 오라클 방식과 동일한 결과를 반환한다. ANSI 방식은 더 읽기 쉽고 표준화된 방식으로 권장된다.

---

 

 

 

 

학습을 마치고

이번에는 실습 문제도 세 문제나 넘게 풀어보았는데 이렇게 풀 수 있다는 것만으로도 스스로가 대견스러웠다. 불과 6개월 전까지만 해도 무슨 소리를 하는지 도통 알아들을 수 없었는데 말이다.

6개월 후에는 난 얼마나 발전해 있을지 기대가 된다. 이제 남은 두 문제도 마저 풀어볼 것이다.