20211102 데이터베이스04

실습문제

1. 회원의 레벨 별 인원을 조회하시오.

/* 회원등급레벨 tb_member_level , 회원 인원수(tb_member) */
SELECT
	l.level_name AS '회원레벨'
	,COUNT(m.m_id) AS '인원수'
FROM
	tb_member AS m
	INNER join
	tb_member_level AS l
	on m.m_level = l.level_num
GROUP BY l.level_name;

 

2. 회원 별 구매이력 중 구매금액이 가장 높은 금액의 상품명을 추출하여 회원아이디와 이메일과 함께 조회 하시오.

SELECT
	DISTINCT m.m_id AS '회원아이디'
	,m.m_email AS '이메일'
	,g.g_name AS '상품명'
	,MAX(g.g_price * o.o_amount) AS '주문금액'
FROM
	tb_member AS m
	INNER join
	tb_order AS o
	on
	m.m_id = o.o_id
	INNER join
	tb_goods AS g
	on
	o.o_g_code = g.g_code
GROUP BY m.m_id
ORDER BY 회원아이디 ASC;

from ->where -> group by ->having -> select -> order by 순으로 조회한다.

 

---

정규화

 

정규화 : 데이터의 일관성, 최소한의 데이터 중복, 최대한의 데이터 유연성을 위한 방법이며 데이터를 분해하는 과정.
 즉, 이상현상이 발생하는 릴레이션을 분해하는 과정.

 

이상현상 : 불필요한 데이터 중복으로 인해 릴레이션에 대한 데이터 삽입 수정 삭제 연산을 수행할 때 발생할 수 있는 부작용

 

삽입이상 :  릴레이션에 새 데이터를 삽입하려면 불필요한 데이터도 함께 삽입해야하는 문제	아이디만 삽입하고자 하면 이상이 발생한다.
갱신이상 :  릴레이션의 중복된 튜플들 중 일부만 수정하여 데이터가 불일치하게 되는 모순이 발생되는 문제	홍01의 등급을 수정했을 때, 등급이 상이.
삭제이상 : 릴레이션에서 튜플을 삭제하면 꼭 필요한 데이터까지 손실되는 연쇄 삭제 현상이 발생하는 문제	이벤트 정보를 삭제하면 고객정보까지 삭제된다.

 

함수적 종속성 : 

- 속성들 간의 관련성

- 함수 종속성을 이용하여, 릴레이션을 연관성이 있는 속성들로만 구성되도록 분해하여 이상현상이 발생하지 않는 릴레이션으로 만들어가는 과정

 

함수종속 : X가 Y를 함수적으로 결정한다

- 릴레이션 내의 모든 튜플을 대상으로 하나의 X값에 대한 Y값이 항상 하나

- Y가 X에 함수적으로 종속되어 있다 와 같은 의미

- X → Y로 표현 (X는 결정자, Y는 종속자)

 

 

 

함수종속 관계 판단 : 

고객아이디로 고객이름과 등급을 결정한다.

 

고객아이디 → 고객이름  / 고객아이디 → 등급

or

고객아이디 → (고객이름, 등급)

 

 

 

 

 

 

 

- 속성 자체의 특성과 의미를 기반으로 함수 종속성을 판단

- 속성 값은 계속 변할 수 있으므로 현재 릴레이션에 포함된 속성 값만으로 판단하면 안됨.

- 일반적으로 기본키와 후보키는 릴레이션의 다른 모든 속성들을 함수적으로 결정함

- 기본키나 후보키가 아니어도 다른 속성 값을 유일하게 결정하는 속성은 함수 종속 관계에서 결정자가 될 수 있음.

 

고객아이디가 고객이름을 결정 o

(고객아이디, 이벤트번호) 가 고객이름 결정 o

 

(고객아이디, 이벤트번호) 가 당첨여부 결정 o

 

 

완전함수종속 : 

- 릴레이션에서 속성 집합 Y가 속성 집합 X에 함수적으로 종속되어 있지만,
- 속성 집합 X의 전체가 아닌 일부분에는 종속되지 않음을 의미

일반적으로 함수 종속은 완전 함수 종속을 의미함

 

부분함수종속:

릴레이션에서 속성 집합 Y가 속성 집합 X의 전체가 아닌 일부분에도 함수적으로 종속됨을 의미

 

함수 종속관계 판단(고려하지 않음) :

결정자와 종속자가 같거나, 결정자가 종속자를 포함하는 것처럼 당연한 함수 종속 관계는 고려하지 않음

 

정규화를 통한 릴레이션은 무손실 분해 : 

- 릴레이션은 의미적으로 동등한 릴레이션들로 분해되어야 하고 분해로 인한 정보의 손실이 발생하지 않아야함

- 분해된 릴레이션들을 자연 조인하면 분해 전의 릴레이션으로 복원 가능

 

정규형 :  릴레이션이 정규화된 정도

각 정규형마다 제약조건이 존재 (릴레이션 특성을 고려 적합한 정규형 선택)

: 정규형의 차수가 높아질수록 요구되는 제약조건이 많아지고 엄격해짐

 

정규화의 관계 :

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

---

서브쿼리 :

SQL 문 내에서 하위에 존재하는 쿼리 (SQL 문 안에 SQL)

1)

SELECT
	round(AVG(g.g_price),0) AS '평균단가'
FROM
	tb_goods AS g

2-1)

SELECT
	*
FROM
	tb_goods AS g
WHERE
	g.g_price > 178786;

2-2)

SELECT
	*
FROM
	tb_goods AS g
WHERE
	g.g_price > ( SELECT
							ROUND(AVG(g.g_price),0)
						from
							tb_goods AS g);

2-1과 2-2의 결과는 같다.

 

범위 지정을 한 후 ctrl + F9 를 누르면 지정된 범위의 결과가 나온다. 결과는 178,786

 

스칼라 서브쿼리 :  SELECT문에 있는 서브쿼리( 1행만 반환)

인라인 뷰 :

서브쿼리: 

 

 

 

 

 

 

다중행 서브쿼리

1)IN

SELECT
	*
FROM
	tb_member AS m
WHERE
	m.m_level IN (select
					l.level_num
						FROM
					tb_member_level AS l);

<- 위의 쿼리와 같은 결과를 보여준다.

 

 

 

 

 

 

 

 

 

2) ANY, SOME

 

SELECT
	*
FROM
	tb_member AS m
WHERE
	m.m_level = ANY (	SELECT l.level_num 
							FROM tb_member_level AS l
							WHERE l.level_num > 1);

m.m_level = 2 or 3 or 4

권한이 2,3,4 인 정보가 출력된다.

 

SELECT
	*
FROM
	tb_member AS m
WHERE
	m.m_level < ANY (	SELECT l.level_num 
							FROM tb_member_level AS l
							WHERE l.level_num > 1);

레벨 <2 or 레벨 < 3 or 레벨 < 4

 

SELECT
	*
FROM
	tb_member AS m
WHERE
	m.m_level > ANY (	SELECT l.level_num 
							FROM tb_member_level AS l
							WHERE l.level_num > 1);

레벨 >2 or 레벨 >3 or 레벨 4

 

3)

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

 

다중컬럼 서브쿼리

SELECT
	*
FROM
	tb_member AS m1
WHERE 
	(m1.m_id, m1.m_level) IN (	select
						m2.m_id	 AS m_id
						,m2.m_level AS m_level
				from
					tb_member AS m2
				where
					m2.m_level > 1);

 

union

SELECT
	m.m_id AS memberId
FROM
	tb_member AS m
UNION ALL
SELECT
	g.g_seller_id AS memberId
FROM
	tb_goods AS g
ORDER BY memberId;

 

실습문제 : 랭크

/*변수 선언*/
SET @rank := 0;

SELECT
	g.g_code
	,g.g_price
	,(@rank := @rank +1) AS '순위'
FROM
	tb_goods AS g
ORDER BY g.g_price DESC;