SQLD 문제 풀이 9

1. 다음 중 SELF JOIN을 수행해야 할 경우로 가장 적절한 것은?

1) 한 테이블 내에서 두 칼럼이 연관 관계가 있다.
2) 두 테이블에 연관된 칼럼은 없으나 JOIN을 해야 한다.
3) 두 테이블에 공통 칼럼이 존재하고 두 테이블이 연관 관계가 있다.
4) 한 테이블 내에서 연관된 칼럼은 없으나 JOIN을 해야 한다. 

SELF JOIN 문법

SELECT <열 목록>
FRON <테이블> 별칭A
	INNER JOIN <테이블> 별칭B
WHERE 검색 조건

 

2. 아래와 같이 일자별 테이블이 존재할 때 아래 결과처럼 일자별 누적매출액을 SQL로 구하려고 한다. WINDOW FUNCITON을 사용하지 않고 일자별 누적매출액을 구하는 SQL로 옳은 것은?

--1번
SELECT 
	A.일자, SUM(A.매출액) AS 누적매출액
FROM 일자별매출 A
GROUP BY A.일자
ORDER BY A.일자;
-- 모든 결과가 1000이 나온다.

--2번
SELECT 
	B.일자, SUM(B.매출액) AS 누적매출액
FROM 일자별매출 A
JOIN 일자별매출 B ON (A.일자 >= B.일자)
GROUP BY B.일자
ORDER BY B.일자;
-- 누적 매출액이 반대로 된다 

--3번
SELECT A.일자
	, SUM(B.매출액) AS 누적매출액
    FROM 일자별매출 A
    JOIN 일자별매출 B ON (A.일자 >= B.일자)
GROUP BY A.일자
ORDER BY A.일자;

--4번
SELECT A.일자
	, (SELECT SUM(B.매출액) FROM 일자별매출 B WHERE B.일자 >= A.일자) AS 누적매출액
    FROM 일자별매출 A
GROUP BY A.일자
ORDER BY A.일자;
-- A.일자 >= B.일자로 바꿔야 정답

 

답 : 3번

WINDOW FUNCTION 

함수(컬럼) OVER (Partition by 컬럼 Order by 컬럼)

1) 함수 

• 그룹 내 순위(RANK) 관련 함수 : RANK, DENSE_RANK(동일 값 중복 순위 부여), ROW_NUMBER
• 그룹내 집계(AGGREGATE) 관련 함수 : SUM, MAX, MIN, AVG, COUNT
• 그룹 내 행 순서 관련 함수 : FIRST_VALUE, LAST_VALUE, LAG(이전 행의 값), LEAD(다음 행의 값) (오라클)
• 그룹 내 비율 관련 함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT

2) OVER : over은 윈도우 함수에서 꼭 들어가야 하며 over 내부에 partition by 절과 order by 절이 들어감

3) partition by : 전체 집합은 어떤 기준(컬럼)에 따라 나눌지를 결정하는 부분

4) order by : 어떤 항목(컬럼)을 기준으로 순위를 정할 지 결정하는 부분

 

	GROUP BY	WINDOW FUNCTION
기능	자르기 + 집약	자르기
특징	1. GROUP BY 구에 지정된 컬럼으로 데이터를 자르고 집계함수를 이용해 집약시킨다.	PARTITION BY구에 지정된 컬럼으로 데이터를 자른다.
차이점	행의 수가 줄어든다	행의 수가 유지된다.

 

3. 다음 중 아래의 SQL 수행 결과로 가장 적절한 것은?

SELECT COUNT(DISTINCT A||B)
FROM EMP
WHERE D = (SELECT D FROM DEPT WHERE E = 'i');

EMP 테이블

A	B	C	D
1	a	1	x
2	a	1	x
3	b	2	y

DEPT 테이블

D	E	F
x	i	5
y	m	6

결과

1a

2a

▶︎ count : 2

답 : 2

 

4. 아래는 서브쿼리에 대한 설명이다. 다음 중 올바른 것끼리 묶인 것은?

가) 서브쿼리는 단일 행(single row) 또는 복수 행(multi row) 비교연산자와 함께 사용할 수 있다.

나) 서브쿼리는 select절, from 절, having절, order by 절 등에서 사용 가능하다. * group by 절에는 사용 불가!!!

--select 절
SELECT A,B,(SELECT TOP 1 A FROM EMP)
FROM EMP;

--from 절
SELECT T1.A,
	T1.B,
    T2.A
FROM EMP T1, (SELECT TOP 1 A FROM EMP) T2
WHERE T1.A = T2.A; 

--order by절
--having 절
SELECT A
FROM EMP
GROUP BY A
HAVING A = (SELECT A FROM EMP WHERE A = 1)
ORDER BY (SELECT A FROM EMP WHERE A=1)

다) 서브쿼리의 결과가 복수 행(multi row)결과를 반환하는 경우에는 '=','<=','=>'등의 연산자와 함께 사용이 가능하다. ▶︎ 단일 행 일때만 가능. 복수 행 일때는 IN 등

라) 연관(correlated) 서브쿼리는 서브쿼리가 메인 쿼리 컬럼을 포함하고 있는 형태의 서브쿼리이다.

SELECT A
FROM EMP TAB1
WHERE A IN (SELECT F
			FROM DEPT TAB2
            WHERE TAB1.A = TAB2.F)

마) 다중 컬럼 서브쿼리는 서브쿼리의 결과로 여러 개의 컬럼이 반환되어 메인쿼리의 조건과 동시에 비교되는 것을 의미하며 oracle 및 sql server등의 DBMS에서 사용할 수 있다. ▶︎SQL Server 에서는 지원 안됨

SELECT A
FROM EMP TAB1
WHERE (A,B) IN (SELECT D, F
			FROM DEPT TAB2
                WHERE TAB1.A = TAB2.F)

 

5. 아래 테이블은 어느 회사의 사원들과 이들이 부양하는 가족에 대한 것으로 밑줄 친 칼럼은 기본키(Primary Key)를 표시한 것이다. 다음 중 '현재 부양하는 가족들이 없는 사원들의 이름을 구하라는 질의에 대해 아래 SQL 문장의 ㄱ, ㄴ 에 들어 갈 내용으로 가장 적절한 것은?

[테이블]

사원(사번, 이름, 나이)
가족(이름, 나이, 부양사번) - 부양사번은 사원 테이블의 사번을 참조하는 외래키이다.

[SQL문장]

SELECT 이름
FROM 사원
WHERE ㄱ (SELECT * FROM 가족 WHERE ㄴ)

ㄱ : NOT EXISTS

ㄴ : 사번 = 부양사번