[LG헬로비전 DX DATA SCHOOL 4기] MySQL(1) (250103)

// 실습 코드
-- LIKE : 부분적으로 일치하는 컬럼을 조회할 때 사용함.

-- 이름이 "J"로 시작하는 고객만 찾기.(J% : J로 시작, %J : J로 끝남, %J% : J를 포함)
SELECT * FROM sakila.customer WHERE first_name LIKE 'J%';

-- active 상태가 1이고, 이메일이 존재하는 고객만 조회하기.
SELECT * FROM sakila.customer WHERE active=1 AND email IS NOT NULL;

-- create_date가 "2006-03-01"이후인 고객만 조회하기.
SELECT * FROM sakila.customer WHERE create_date > '2006-03-01';

-- Q) 이름이 'Lee'가 포함된 고객의 이름(first_name)목록을 조회하는 쿼리
SELECT * FROM sakila.customer WHERE first_name LIKE '%Lee%';

-- Q) 이메일 주소가 example 도메인을 가진 고객의 이름(first_name)을 조회하는 쿼리
SELECT * FROM sakila.customer WHERE email LIKE '%@example.com%';

-- Q) 이름(first_name)의 길이가 정확히 5자인 고객을 찾으세요.
SELECT * FROM sakila.customer WHERE first_name LIKE '_____';

// 실습 코드
-- IN 연산자 : 특정 열의 값이 여러 값 중 하나와 일치하는 지 확인할 때 사용됨.
-- customer_id가 1, 3, 4인 고객만 조회하기(IN 연산자 사용)
SELECT * FROM sakila.customer WHERE customer_id IN (1, 3, 4);

-- customer_id가 1, 3, 4인 고객만 조회하기(OR 연산자 사용)
SELECT * FROM sakila.customer WHERE customer_id=1 OR customer_id=3 OR customer_id=4;

-- first_name이 "John", "Lisa", "Emma"인 고객만 조회하기.
SELECT * FROM sakila.customer WHERE first_name IN ('John', 'Lisa', 'Emma');

-- Q) sakila.city 테이블에서 country_id 열이 103 또는 country_id 열이 86이면서 city열이 "Cheju", "Sunnyvale", "Dallas"인 데이터를 조회
SELECT * FROM sakila.city WHERE country_id IN (103, 86) AND city IN ("Cheju", "Sunnyvale", "Dalloas");

-- Q) sakila.customer 테이블에서 store_id가 1또는 2인 데이터 중에서 first_name이 "Mary" 또는 "John"인 데이터만 조회하세요.
-- 또한, last_update가 NULL이 아닌 데이터만 조회하고, last_update가 '2006-01-01' 이후인 데이터만 조회합니다.
SELECT * FROM sakila.customer WHERE store_id IN (1, 2)
AND first_name IN ('Mary', 'John')
AND last_update IS NOT NULL
AND last_update > '2006-01-01'
ORDER BY last_update DESC;

// 실습 코드
-- last_name 기준으로 오름차순 정렬
SELECT * FROM sakila.customer ORDER BY last_name ASC;

-- first_name 기준으로 내림차순 정렬
SELECT * FROM sakila.customer ORDER BY first_name DESC;

-- Q) last_name과 fist_name 기준으로 정렬
SELECT * FROM sakila.customer ORDER BY last_name ASC, first_name ASC;

-- Q) store_id 기준으로 오름차순 정렬하고, 그 안에서 last_update 기준으로 내림차순 정렬
SELECT * FROM sakila.customer ORDER BY store_id ASC, last_update DESC;

-- Q) fisrt_name이 "Mary"인 데이터만 last_name 기준으로 오름차순 정렬
SELECT * FROM sakila.customer WHERE first_name="Mary" ORDER BY last_name ASC;

-- Q) 활성 상태(active)를 기준으로 정렬하되, 같은 활성 상태에서는 이름(fisrt_name)을 오름차순으로 정렬하세요.
SELECT * FROM sakila.customer ORDER BY active DESC, first_name ASC;

-- Q) 이름(first_name)에 'a'가 포함된 고객만 검색한 후 가입일(create_date) 기준으로 오름차순 정렬하세요.
SELECT * FROM sakila.customer WHERE first_name LIKE '%a%' ORDER BY create_date ASC;

-- Q) 이름(first_name)의 길이를 짧은 순서대로 정렬하세요.
SELECT customer_id, first_name, last_name, length(first_name) AS name_length FROM sakila.customer ORDER BY name_length ASC;

-- Q) 이름(first_name)이 'M'으로 시작하고 성(last_name)에 'e'가 포함된 고객을 검색하여 결과를 오름차순으로 정렬하세요.
SELECT customer_id, first_name, last_name FROM sakila.customer WHERE first_name LIKE 'M%' AND last_name LIKE '%e%' ORDER BY first_name ASC;

-- Q) 전체 고객 중 활성 고객(active=1)이 차지하는 비율을 계산하고, 활성 고객 수와 비활성 고객 수를 함께 표시하세요.
SELECT
	sum(active) as active_customers,
    count(*) - sum(active) as inactive_customers,
    round(sum(active) / count(*) *100, 2) as active_percentage,
    sum(active) / count(*) as average_active_status
FROM sakila.customer;

-- Q) 이름(first_name)의 길이가 6이상인 고객을 검색하고, 이름 길이 기준으로 내림차순으로 정렬하세요.
SELECT customer_id, first_name, last_name, length(first_name) as name_length
FROM sakila.customer
WHERE length(first_name) >= 6
ORDER BY name_length DESC;

// 실습 코드
-- Q) 활성 상태(active)가 1(활성) 또는 0(비활성)인 고객의 수를 각각 구하세요.
SELECT active, count(*) as total_customers
FROM sakila.customer
GROUP BY active;

-- Q) 성(last_name)의 첫 글자별 고객 수 구하기(LEFT: 왼쪽에서 부터)
SELECT
	LEFT(last_name, 1) as first_letter,
    count(*) as customer_count
FROM sakila.customer
GROUP BY LEFT(last_name, 1)
ORDER BY first_name ASC;

-- Q) 이름(first_name)의 길이를 기준으로 고객 수를 구하세요ㅕ.
SELECT length(first_name) as name_length, count(*) as customer_count
FROM sakila.customer
GROUP BY length(first_name)
ORDER BY name_length ASC;

-- Q) 활성 상태(active)별로 이름(first_name)의 평균 길이를 구하세요.
SELECT active, avg(length(first_name)) as avg_name_length
FROM sakila.customer
GROUP BY active;

-- Q) 성(last_name)의 첫 글자별로 활성 고객(active=1)의 비율을 구하세요.
SELECT
	LEFT(last_name, 1) as first_letter, -- 성의 첫 글자
	count(*) as total_customers, -- 각 글자별 총 고객 수
	sum(active) as active_customers, -- 활성 고객 수
	round(sum(active) * 100.0 / count(*), 2) as active_rate -- 활성 고객 비율(%)
FROM sakila.customer
GROUP BY LEFT(last_name, 1)
ORDER BY active_rate desc; -- 활성 비율 순 정렬

-- Q) 활성 상태(active) 별로 고객 이름(first_name)의 최대 길이, 최소 길이, 평균 길이를 구하세요.
SELECT
	active, max(length(first_name)) as max_name_length,
    min(length(first_name)) as min_name_length,
    avg(length(first_name)) as avg_name_length
FROM sakila.customer
GROUP BY active;

-- Q) 각 store_id 별 고객 수가 2명 이상인 store 찾기 (* WHERE 절에는 조건을 걸 수 없다!)
SELECT store_id, count(*) as customer_count
FROM sakila.customer
GROUP BY store_id HAVING count(*) >= 2;
-- 이거 보다는 HAVING절을 사용하자
SELECT store_id, count(*) as customer_count
FROM sakila.customer
WHERE store_id <= 2 IS NOT NULL
GROUP BY store_id;

-- Q) address_id가 5이상인 고객 그룹만 가져오기.
SELECT
	address_id,
    count(*) as customer_count
FROM sakila.customer
GROUP BY address_id
HAVING address_id >= 5;

-- Q) store_id별 활성(active=1) 고객 수가 2명 이상이고, address_id가 평균 값이 5를 초과하는 경우만 가져오기.
SELECT
	store_id,
	count(customer_id) as active_customer_count,
    avg(address_id) as avg_address_id
FROM sakila.customer
WHERE active=1
GROUP BY store_id
HAVING count(customer_id) >= 2 AND avg(address_id) > 5;

-- Q) store_id별로 고객의 총 수가 2명 이상이면서, customer_id의 최대값이 5를 초과하는 경우만 가져오기.
SELECT
	store_id,
    count(customer_id) as total_customers,
    max(customer_id) as max_customer_id
FROM sakila.customer
GROUP BY store_id
HAVING count(customer_id) >= 2 AND max(customer_id) > 5;

// 실습 코드
-- Q) store_id별 활성 고객의 총 수가 2명 이상이고, address_id 평균이 5이상이며, 가장 최근 last_update 날짜가 특정 기준(예: '2006-02-15') 이후인 경우의 쿼리를 작성해보세요.
SELECT
	store_id,
    count(customer_id) as total_active_customers,
    avg(address_id) as avg_address_id,
    max(last_update) as last_update
FROM sakila.customer
WHERE active=1 AND last_update > '2006-02-15'
GROUP BY store_id
HAVING count(customer_id) >= 2 AND avg(address_id) > 5
ORDER BY total_active_customers DESC;

-- 튜플 변수 적용(데이터 셋을 여러개 불러와야 할 때 사용)
SELECT
	c.store_id,
    count(c.customer_id) as total_active_customers,
    avg(c.address_id) as avg_address_id,
    max(c.last_update) as last_update
FROM sakila.customer c
WHERE c.active=1 AND c.last_update > '2006-02-15'
GROUP BY c.store_id
HAVING count(c.customer_id) >= 2 AND avg(c.address_id) > 5
ORDER BY total_active_customers DESC;

// 실습 코드
-- Q) 고객 테이블에서 다음 조건을 만족하는 SQL 쿼리를 작성하세요.
-- 1. customer_id가 짝수인 고객만 선택합니다.
-- 2. 중복된 email이 없도록 결과를 출력합니다.
-- 3. 활성 상태(active)가 1인 고객만 출력합니다.
-- 4. create_date가 2006년 이후인 고객만 포함합니다.
-- 5. 결과를 create_date 기준으로 내림차순 정렬합니다.
-- 6. customer_id의 평균 값이 3보다 큰 결과만 표시하세요. (조건 : HAVING절을 사용하세요, HINT : 짝수는 %연산자를 사용하세요.)

SELECT DISTINCT
	customer_id,
    first_name,
    last_name,
    email,
    active,
    create_date
FROM sakila.customer
WHERE create_date > '2006-01-01' AND active=1
GROUP BY customer_id
HAVING customer_id % 2 = 0 AND avg(customer_id) > 3
ORDER BY create_date DESC;

// 실습 코드
-- LEFT JOIN
SELECT
	c.customer_id,
    c.first_name,
    c.last_name,
    c.store_id,
    s.manager_staff_id,
    s.address_id
FROM sakila.customer c
LEFT JOIN sakila.store s
ON c.store_id = s.store_id
ORDER BY c.customer_id;

-- RIGHT JOIN
SELECT
	s.store_id,
    s.manager_staff_id,
    c.customer_id,
    c.first_name,
    c.last_name,
    c.store_id as customer_store_id
FROM sakila.store s
RIGHT JOIN sakila.customer c
ON s.store_id = c.store_id
ORDER BY c.customer_id;

-- INNER JOIN
SELECT
	c.customer_id,
    c.first_name,
    c.last_name,
    c.store_id as customer_store_id,
    s.store_id as store_store_id,
    s.manager_staff_id
FROM sakila.customer c
INNER JOIN sakila.store s
ON c.store_id = s.store_id
ORDER BY c.customer_id;

// 실습 코드
-- Q) sakila 데이터베이스를 기반으로 customer 테이블과 rental 테이블, inventory 테이블을 사용하여 다음 조건을 만족하는 쿼리를 작성하세요.
-- 1. customer 테이블에서 대여 기록이 있는 고객만 조회합니다. => customer_id, store_id (customer 테이블)
-- 2. 각 고객이 대여한 영화 수를 구합니다.	=> inventory_id, customer_id (rental 테이블)
-- 3. 고객이 속한 매장(store_id)도 함께 표시합니다.
-- 4. 대여한 영화 수가 5편 이상인 고객만 출력합니다.  
-- 5. 결과는 대여한 영화 수 기준으로 내림차순 정렬합니다.
-- * inventory_id, store_id (inventory 테이블)

SELECT
	c.customer_id,
    c.first_name,
    c.last_name,
    c.store_id,
    count(i.film_id) as number_of_rental
FROM sakila.customer c
INNER JOIN sakila.rental r
ON c.customer_id = r.customer_id
INNER JOIN sakila.inventory i
ON r.inventory_id = i.inventory_id
GROUP BY c.customer_id
HAVING count(i.film_id) >= 5
ORDER BY number_of_rental DESC;

정리

문법/연산자	설명
SELECT	데이터를 조회하는 기본 문법.
WHERE	조건을 만족하는 데이터만 필터링.
LIKE	문자열 패턴을 검색.
%	0개 이상의 임의 문자와 일치.
_	단일 문자와 일치.
IN	여러 값 중 하나와 일치하는 조건을 검색.
AND	두 조건을 모두 만족하는 데이터를 필터링.
OR	둘 중 하나 이상의 조건을 만족하는 데이터를 필터링.
NOT	조건을 부정.
ORDER BY	데이터를 정렬.
ASC	오름차순 정렬 (기본값).
DESC	내림차순 정렬.
GROUP BY	데이터를 그룹화하여 집계.
HAVING	그룹화된 데이터에 조건을 적용.
DISTINCT	중복된 데이터를 제거하고 고유한 결과만 반환.
IS NULL	값이 NULL인지 확인.
IS NOT NULL	값이 NULL이 아닌지 확인.
JOIN	여러 테이블을 연결.
LEFT JOIN	왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 일치하는 데이터 결합.
RIGHT JOIN	오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 일치하는 데이터 결합.
INNER JOIN	두 테이블 모두에서 일치하는 데이터만 결합.
COUNT	데이터의 개수를 계산.
SUM	데이터의 합계를 계산.
AVG	데이터의 평균을 계산.
MAX	데이터의 최대값을 반환.
MIN	데이터의 최소값을 반환.
ROUND	숫자를 반올림.
LEFT	문자열의 왼쪽에서 지정된 수만큼 문자 반환.
LENGTH	문자열의 길이를 반환.
% (모듈로 연산)	나머지 연산.