[SQL] 일단 시작해보기 - 가장 자주 쓰는 DML 위주

1. 개요

SQL이란?

- Structured Query Language : 구조적 질의 언어

- 관계형 데이터베이스 시스템 (RDBMS)의 자료를 관리, 처리를 위해 고안된 언어

- IBM에서 최초 개발(1970년대)되어 관계형 모델 이론에서 파생됨

- SQL 표준으로 ANSI SQL이 정립됨, 각 DBMS는 ANSI SQL을 기반으로 서로 근소한 차이를 지님

SQL 문법의 종류

- DDL : Data Definition Language, 데이터 정의 언어, 각 릴레이션을 정의함. (CREATE, ALTER, DROP...)

- DML : Data Manipulation Language, 데이터 조작 언어, 데이터의 관리(추가/수정/삭제)를 위한 언어 (SELECT, INSERT, UPDATE, ...)

- DCL : Data Control Language, 데이터 제어 언어, 사용자 관리, 사용자별 릴레이션, 사용자별 데이터 관리 접근 권한을 다루기위한 언어. (GRANT, REVOKE)

SQL의 언어적 특징

- 대소문자에 무관하다 (DBMS나 서버 환경에 따라 구분하기도 하다.)

- SQL 명령어는 세미콜론(;)으로 마쳐야 한다.

- 고유값은 작은 따옴표로 감싼다. (SELECT * FROM EMP WHERE NAME = 'James';)

- 객체는 백틱으로 감싼다. (SELECT `COST`, `TYPE` FROM `INVOICE`;)

- 한 줄 주석은 문장 앞에 --를 붙이고 여러 줄 주석은 /* */ 로 감싼다.

SQL이 왜 필요한가?

- 데이터를 저장하는 방법으로 In-Memory, File I/O, RDBMS 방식이 있다. In-Memory 방식은 프로그램의 동작 시간에만 데이터가 저장되기 때문에 쉽게 휘발되는 단점이 있다. 파일을 읽는 방식은 데이터의 수명을 늘렸으나 필요한 데이터를 가져오고 조작하는데 비효율 적이다. (파일 읽기 시 전체 파일을 읽어야 해 파일의 크기가 커질 수록 느려지고, 파일 손상에 취약하며 여러 파일의 동시에 동작함에 있어 복잡한 처리가 요구된다.) 반면 RDBMS 방식은 하나의 CSV 파일 혹은 엑셀 시트를 한 개의 테이블로서 저장할 수 있어 한 번에 여러 개의 테이블을 읽기 때문에 SQL을 활용해 데이터를 불러오고 조작하고 관리하기에 수월하다.

RDBMS란?

- 관계형 데이터베이스 시스템, ex) MySQL, Oracle, SQLite, PostgreSQL

- SQL을 사용해 원하는 데이터를 가져오거나 삽입할 수 있다.

- SQL은 구조화된 쿼리 언어로 RDBMS에서 주로 사용된다.

- NoSQL은 데이터 구조가 고정되지 않은 데이터베이스를 의미하며 테이블을 사용하지 않고 다른 방식으로 데이터를 저장한다. ex) MongoDB

필수 프로그램 설치 :

- DBeaser: https://dbeaver.io/download/

공부할 내용 : 주로 DML

- select : 테이블과 필드 가져오기, select 필드 from 테이블
- where : 조건문, =, !=, between and, in, like '%'
- 통계량 : count(), min(), max(), round(), avg(), sum()
- group by : 범주
- order by : 정렬, desc, asc
- Alias : 별칭 - 테이블명, 필드명
- join : 동일한 키 값의 테이블 합치기, inner join ... on ..., left join ... on ...
- union : 동일한 필드 합치기, () union all ()
- 서브쿼리 : 쿼리문 속 쿼리문, where 절 내, select 절 내, inner join 내 (...)
- with 절 : 서브 쿼리 절을 하나의 with으로 간략화, with 테이블명1 as (서브쿼리1), 테이블명2 as (서브쿼리2)
- 기타 : substring, substring_index, case when... then... else... end

2. Select 쿼리문

어떤 테이블에서 어떤 필드의 데이터를 가져올지를 의미합니다.

테이블과 필드 :

- 테이블 : 테이블은 엑셀 시트와 동일합니다. 만약 orders라는 엑셀시트를 DB에 저장하면 테이블명 역시 ORDERS가 됩니다.

- 필드 : order_no, created_at... 처럼 테이블의 열에 해당되는 데이터의 특성을 필드라고 합니다.

-- 테이블 전체 보기
show tables; 

-- Select 절
SELECT * FROM orders; -- order 테이블 보기
SELECT order_no, created_at, user_id, email from orders; -- 필드 보기

3. Where 절

- select ... from ... 으로 가져온 테이블에 조건을 추가합니다.

- 같음, 같지 않음 : '=', '!='

- 크거나 작음 : >=, >, <=, <

- 논리연산자 : AND, OR

- 범위 : BETWEEN AND

- 포함 : IN

- 패턴 : LIKE, '%' (특정 문자열)

-- Where 절
SELECT * FROM orders
WHERE payment_method = 'kakaopay';

SELECT * FROM point_users pu
WHERE point >= 5000;

SELECT * FROM orders
WHERE course_title = '앱개발 종합반' AND payment_method = 'CARD';

SELECT * FROM orders
WHERE created_at BETWEEN '2020-07-13' and '2020-07-15';

SELECT * FROM checkins
WHERE week in (1, 3);

기타 :

- limit : 일부 데이터만 가져오기

- distinct : 중복 제거

- count : 갯수 세기

-- 이외 유용한 문법들 
SELECT * FROM orders
WHERE payment_method = 'kakaopay'
limit 5;

SELECT * FROM orders 
limit 5;

SELECT DISTINCT(payment_method) FROM orders; -- 중복 제거

SELECT COUNT(*) FROM orders
WHERE payment_method ='kakaopay'; -- 갯수 세기

SELECT COUNT(DISTINCT(name)) FROM users; -- 종류 갯수 세기

-- 퀴즈
select email from users
where name = '남**';

SELECT * FROM  users
WHERE created_at BETWEEN '2020-07-12' AND '2020-07-14' 
AND email LIKE '%@gmail.com';

SELECT COUNT(*) FROM  users 
WHERE created_at BETWEEN '2020-07-12' AND '2020-07-14' 
AND email LIKE '%@gmail.com';

SELECT * FROM orders
WHERE email LIKE '%@naver.com'
AND course_title = '웹개발 종합반'
AND payment_method = 'kakaopay';

4. 통계량, GROUP BY (범주), ORDER BY(정렬)

어떤 범주에대한 통계량을 산출 할 때 'grouop by 필드명' 을 활용합니다. "~별 통계량을 구하시오." 는 식의 질문을 작성할 수 있습니다.

통계량에는 다음과 같은 것들이 자주 사용됩니다.

- 갯수 : COUNT(조건), 조건에는 전체(*), 필드명, case when ... then ... end, distinct 필드명 등이 들어갑니다.

- 최대 최소 : min, max

- 반올림 : round(반올림할 데이터, 반올림할 자리수)

- 합 : sum

- 평균 : avg

-- 통계량(의미있는 정보)
-- group by (범주형 통계량)
SELECT name, count(*) FROM users
GROUP BY name; -- 범주 별로 갯수 구하기

SELECT week, count(*) FROM checkins
GROUP BY week;

SELECT week, min(likes) FROM checkins 
GROUP BY week; -- 범주 별로 최솟값 구하기

SELECT week, max(likes) FROM checkins
GROUP BY week; -- 최댓값

SELECT week, ROUND(AVG(likes), 2) FROM checkins
GROUP BY week; -- 반올림, 평균, 유효숫자

SELECT week, SUM(likes) FROM checkins 
GROUP BY week; -- 범주별 합

테이블의 정렬 순서를 어떤 필드의 오름차순 혹은 내림차순으로 지정할 수 있습니다.

기본값으로 오름차순(asc)이며 내림차순으로 정렬하고 싶을 때에는 desc를 써줍니다.

또한 절차적 언어인 SQL의 실행 순서는 조건 > 범주 > 정렬 순서입니다.

-- order by (정렬)
SELECT name, count(*) FROM users 
GROUP BY name
ORDER BY count(*) DESC;
-- order by는 맨 마지막에 작성. 
-- 필드명을 기준으로 내림차순 desc, 오름차순 asc (기본값)

SELECT * FROM checkins 
ORDER BY likes DESC;

SELECT payment_method, COUNT(*)  FROM orders
GROUP BY payment_method 
ORDER BY count(*) DESC;

SELECT payment_method, count(*) FROM orders
WHERE  course_title = '웹개발 종합반'
GROUP BY payment_method 
ORDER BY count(*) DESC;
-- 조건 > 범주 > 정렬 순서로 작성해야함.

자주 발생하는 에러 :

- GROUP BY를 사용해 범주형 데이터를 산출할 경우 필드에 통계량을 명시해 주어야 목적에 맞는 데이터를 확인할 수 있습니다. 따라서 범주에 해당되는 필드와 통계량을 반드시 명시해 주어야 합니다.

-- 자주 발생하는 에러
select * from orders o 
group by payment_method; -- 통계량이 불포함됨

select count(*) from orders o 
group by payment_method -- 범주값이 불포함됨

연습, 퀴즈 :

-- 연습
select * from users
order by email desc; -- 문자열 기준 정렬

select * from users
order by name desc;

select * from users
order by created_at; -- 날짜 기준 정렬

-- 퀴즈
SELECT payment_method, count(*) FROM orders
WHERE course_title = '앱개발 종합반'
GROUP BY payment_method
ORDER BY count(*) DESC;

SELECT name, count(*) FROM users
WHERE email LIKE '%@gmail.com'
GROUP BY name
ORDER BY COUNT(*) DESC;

SELECT course_id, ROUND(AVG(likes), 2)  FROM checkins
GROUP BY course_id;

5. 별칭 (Alias)

테이블명과 필드명을 재정의해 별칭으로 간략화 할 수 있습니다.

- 테이블명의 별칭 : 실제 테이블 이름 뒤에 한 두글자의 약어를 사용합니다.

- 필드명의 별칭 : 필드명 뒤 AS와 함께 작성합니다.

아래의 예시에서 테이블명 orders 는 o로 축약되었습니다.

- orders의 필드명을 참조할 시 "."을 사용합니다. ex) o.course_title

- payment_method의 경우 유일한 필드명이기 때문에 별칭으로 참조하지 않아도 괜찮습니다.

또한 갯수를 세는 통계량인 COUNT(*)는 AS 뒤 cnt로 별칭이 지정되었습니다. 만약 필드명의 별칭을 지정하지 않은 경우 수식 자체가 곧 필드명이 되어 의미 파악이 어려울 수 있습니다.

-- 별칭 (Alias) - 필드명, 테이블명 (한글자, 두글자로 적음)
SELECT payment_method, COUNT(*) AS cnt FROM orders o
WHERE o.course_title = '앱개발 종합반'
GROUP BY payment_method

5. 테이블 간 연결하기 (JOIN, UNION)

Join 결합은 두 테이블의 공통된 정보(key 값)을 기준으로 열 방향으로 결합 시킵니다.

- 테이블의 연결에는 key 값이 필요함. (엑셀의 vlookup과 동일)

- 종류 : left join, inner join , ...

- 문법 : LEFT JOIN 테이블명 ON 키값

결합의 목적 :

만약 A 테이블에 없으나 B 테이블에 있는 필드값이 필요할 때 활용된다. 이 때, A와 B는 Key값으로 연관관계를 지니고 있다.

LEFT JOIN : A 테이블 기준으로 B 테이블을 결합합니다. 따라서 A 테이블에 없는 B 테이블의 정보는 null 로 채워집니다.

INNER JOIN : A 테이블과 B 테이블이 공통적으로 가진 정보에대해서만 결합을 진행합니다.

-- 테이블 연결하기 (join)
select * from users u
left join point_users pu on u.user_id = pu.user_id;
-- pu 정보가 없는 u 테이블의 유저의 경우 null로 채워짐

SELECT * FROM users u 
inner join point_users pu on u.user_id = pu.user_id;
-- pu 정보를 지니고 있는 user에 대해서만 결합을 진행 (교집합)

-- 연습
SELECT * from orders o 
inner join users u on o.user_id = u.user_id ;

SELECT * FROM checkins c 
inner join users u on c.user_id = u.user_id 

SELECT * FROM enrolleds e 
inner join courses c on e.course_id  = c.course_id;

두 테이블 간의 결합을 활용해 하나의 테이블로 합친 후 유의미한 통계량을 구할 수 있습니다.

-- 결합 후 통계치 구하기
SELECT c1.course_id, c2.title, COUNT(*) as cnt FROM checkins c1
inner join courses c2 on c1.course_id = c2.course_id
GROUP BY title;

SELECT u.user_id, u.name, u.email, pu.point FROM point_users pu 
INNER JOIN users u ON pu.user_id = u.user_id 
ORDER BY pu.point DESC;

SELECT u.name, COUNT(*) as cnt FROM orders o 
INNER JOIN users u ON o.user_id = u.user_id 
WHERE o.email LIKE '%@naver.com'
GROUP BY u.name 
ORDER BY cnt DESC;
-- 실행순서 : from > join > where > group by > select

-- 퀴즈

-- 결제 수단 별 포인트 점수의 평균
SELECT
    o.payment_method, 
    ROUND(AVG(pu.point)) as avg_point  
FROM orders o 
INNER JOIN point_users pu ON o.user_id = pu.user_id
GROUP BY o.payment_method
ORDER BY avg_point DESC;

-- 성씨별 강의 등록을 하지 않은 사람들의 수 
SELECT 
    u.name, 
    COUNT(*) as cnt_name 
FROM enrolleds e 
INNER JOIN users u ON e.user_id = u.user_id 
WHERE e.is_registered = 0
GROUP BY u.name 
ORDER BY cnt_name DESC;

-- 강의별 아직 등록을 하지 않은 사용자의 수
SELECT 
    c.course_id, 
    c.title, 
    COUNT(*) as cnt_nonstart 
FROM courses c 
INER JOIN enrolleds e ON c.course_id = e.course_id 
WHERE e.is_registered = 0
GROUP BY c.course_id;

-- 강의와 주차별 등록정보 갯수
SELECT 
    c1.title, 
    c2.week, 
    COUNT(*) as cnt 
FROM courses c1 
INNER JOIN checkins c2 ON c1.course_id = c2.course_id 
GROUP BY c1.course_id, c2.week
ORDER BY c1.title, c2.week;

-- 강의와 주차 별 8월 1일 이후의 주문량 
SELECT 
    c1.title, 
    c2.week, 
    count(*) as cnt 
FROM courses c1
INNER JOIN checkins c2 ON c1.course_id = c2.course_id 
INNER JOIN orders o ON o.user_id = c2.user_id
WHERE o.created_at >= '2020-08-01'
GROUP BY c1.title, c2.week 
ORDER BY c1.title, c2.week ;

-- left join은 순서가 중요하다. ps -> u
-- 성씨별 강의 시작을 하지 않은 사람들
SELECT 
    u.name, 
    COUNT(*) as cnt 
FROM users u 
LEFT JOIN point_users pu ON u.user_id = pu.user_id 
WHERE pu.point_user_id IS NULL 
GROUP BY u.name ;

-- 성씨별 강의 시작한 사람들 -> NULL을 활용한다. 
SELECT
    u.name, 
    COUNT(*) as cnt 
FROM users u 
LEFT JOIN point_users pu ON u.user_id = pu.user_id 
WHERE pu.point_user_id IS NOT NULL 
GROUP BY u.name;

-- COUNT는 NULL인 행을 세지 않음
-- 7월 10일과 19일 사이에 가입한 사용자 수와 포인트 점수의 비율 
SELECT 
	COUNT(pu.point_user_id) AS pnt_user_cnt,
	COUNT(u.user_id) AS tot_user_cnt,
	ROUND(COUNT(pu.point_user_id) / COUNT(u.user_id), 2)  AS ratio
FROM users u 
left join point_users pu on u.user_id = pu.user_id 
WHERE u.created_at BETWEEN '2020-07-10' AND '2020-07-20';

또 다른 결합인 Union은 동일한 필드를 가지고 있는 두 테이블을 행 방향으로 결합합니다.

( ... ) union all ( ... ) 의 형태로 사용됩니다.

-- Union : 필드가 동일한 경우 하나로 합치기

-- () UNION ALL ()
-- UNION은 ORDER BY를 다시 해주어야함 -> 서브쿼리

(
	select '7월' as month, c1.title, c2.week, count(*) as cnt from courses c1
	inner join checkins c2 on c1.course_id = c2.course_id
	inner join orders o on c2.user_id = o.user_id
	where o.created_at >= '2020-08-01'
	group by c1.title, c2.week
) UNION ALL (
	select '8월' as month, c1.title, c2.week, count(*) as cnt from courses c1
	inner join checkins c2 on c1.course_id = c2.course_id
	inner join orders o on c2.user_id = o.user_id
	where o.created_at >= '2020-08-01'
	group by c1.title, c2.week
) 


-- 숙제 
SELECT 
	e.enrolled_id, 
	e.user_id, 
	COUNT(*) as max_count 
FROM enrolleds e
inner join enrolleds_detail ed on e.enrolled_id = ed.enrolled_id 
WHERE ed.done = 1
GROUP BY e.enrolled_id, e.user_id
ORDER BY max_count DESC;

6. 서브쿼리

서브쿼리는 쿼리문 내 쿼리문을 작성할 때 사용됩니다. 가장 안쪽에 있는 서브쿼리부터 실행됩니다.

# 서브쿼리 : 쿼리문 내 쿼리
# 실행순서 : 서브쿼리 > 쿼리

select * from users u 
inner join orders o on u.user_id = o.user_id 
where o.payment_method = 'kakaopay';

# where 절에 들어 있는 subquery
select * from users 
where user_id in (
	select user_id from orders
	WHERE payment_method = 'kakaopay'
);

# select 절에 들어 있는 subquery
select 
	c.checkin_id, 
	c.user_id, 
	c.likes,
	(
		select avg(likes) from checkins
		WHERE user_id = c.user_id 
	) as avg_likes
from checkins c;


select avg(likes) from checkins
WHERE user_id = '4b8a10e6';

# inner join에 들어 있는 subquery - point와 like의 상관관계
SELECT 
	pu.user_id, 
	pu.point, 
	a.avg_likes
from point_users pu 
inner join (
				SELECT 
					user_id, 
					round(avg(likes), 1) as avg_likes 
				from checkins c 
				group by user_id 
			) a
on pu.user_id = a.user_id;

# 퀴즈 : 전체 평균 이상의 포인트 점수를 가진
select * from point_users pu 
where pu.point > (
	select round(avg(point)) from point_users
);

select * from point_users pu 
WHERE pu.point > (
	# 이씨 성을 가진 유저의 평균 점수
);

select round(avg(point)) from point_users
where user_id in (
	# 이씨 성을 가진 유저의 유저 아이디
);

SELECT user_id from users u 
where name = '이**';

# 서브쿼리 두 번 쓰기
select * from point_users pu 
WHERE pu.point > (
	select round(avg(point)) from point_users
	where user_id in (
		SELECT user_id from users u 
		where name = '이**'
	)
);

# inner join 과 서브쿼리 활용하기
select * from point_users pu1
WHERE pu1.point > (
	select avg(pu2.point) from point_users pu2
	inner join users u on pu2.user_id = u.user_id 
	where u.name = '이**'
);

# select 절에 들어가는 서브쿼리 연습
select c.course_id, round(avg(c.likes), 2) from checkins c 
group by c.course_id ;

SELECT
	c1.checkin_id,
	c1.course_id,
	c1.user_id,
	c1.likes,
	(
		select round(avg(c.likes), 1) from checkins c 
		WHERE c.course_id = c1.course_id 
	) as course_avg,
	c2.*
from checkins c1 
inner join courses c2 on c1.course_id = c2.course_id;


# 서브쿼리 연습 - from, inner join
# course_id 별, like 개수 구하기 

# 1. course_id 별 한 번이라도 체크인을 한 유저의 수 (중복된 유저의 갯수는 소거)
select c.course_id, count(distinct(c.user_id)) as cnt_checkins from checkins c 
group by c.course_id;

# 2. course_id 별 구매한 전체 인원 수 
select o.course_id, count(*) as cnt_total from orders o 
group by o.course_id;

# 3-1. select 절에 서브쿼리 : course_id 별 like 개수와 전체 인원
# select ... (...) from ... 
select 
	c.course_id, 
	count(distinct(c.user_id)) as cnt_checkins, 
	(
		select count(*) from orders o 
		where o.course_id = c.course_id 
	) as cnt_total
from checkins c 
group by c.course_id;

# 3-2. from 절에 서브쿼리와 inner join : 새로 만든 테이블끼리 병합
# select ... from (...) inner join (...) on ...
select 
	a.course_id,
	a.cnt_checkins,
	b.cnt_total
from (
	select 
		c.course_id, 
		count(distinct(c.user_id)) as cnt_checkins 
	from checkins c 
	group by c.course_id
) a
inner join (
	select 
		o.course_id, 
		count(*) as cnt_total 
	from orders o 
	group by o.course_id
) b
on a.course_id = b.course_id;
 

# 3-3. title, ratio 추가하기
select 
	c2.title,
	a.cnt_checkins,
	b.cnt_total,
	(a.cnt_checkins / b.cnt_total) as ratio
	# , c2.* 로 inner join 확인
from (
	select 
		c.course_id, 
		count(distinct(c.user_id)) as cnt_checkins 
	from checkins c 
	group by c.course_id
) a
inner join (
	select 
		o.course_id, 
		count(*) as cnt_total 
	from orders o 
	group by o.course_id
) b
on a.course_id = b.course_id
inner join courses c2 
on c2.course_id = a.course_id

7. with 절

위의 서브쿼리를 활용해 산출한 여러 테이블들을 보면 해석하기에 매우 큰 어려움이 있습니다. 이때, 서브 쿼리를 하나의 테이블로서 별도로 관리할 수 있게 도와주는 것이 WITH 절입니다.

- WITH 테이블이름 AS ( 서브쿼리 )

# with 절 : 서브 쿼리 절을 하나의 with으로 간략화
with table1 as (
	select course_id, count(distinct(user_id)) as cnt_checkins from checkins
	group by course_id
), table2 as (
	select course_id, count(*) as cnt_total from orders
	group by course_id 
) select c.title,
       a.cnt_checkins,
       b.cnt_total,
       (a.cnt_checkins/b.cnt_total) as ratio
from table1 a
inner join table2 b 
on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id



select c.title,
       a.cnt_checkins,
       b.cnt_total,
       (a.cnt_checkins/b.cnt_total) as ratio
from
(
	select course_id, count(distinct(user_id)) as cnt_checkins from checkins
	group by course_id
) a
inner join
(
	select course_id, count(*) as cnt_total from orders
	group by course_id 
) b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id

8. SQL 실전

- 문자열 쪼개기 : SUBSTRING_INDEX(문자열, '기준문자', INDEX)

- 문자열 일부만 가져오기 : SUBSTRING(몇 번째에서, 몇 번째 까지)

- 범위 가져오기 : CASE WHEN 조건식 THEN 값 ELSE 예외값 END

# SQL 실전

# substring index : 문자열 쪼개기
select user_id, SUBSTRING_INDEX(email, '@', 1), SUBSTRING_INDEX(email, '@', -1)  from users;

# substring : 문자열 일부만 가져오기, 1부터 시작
SELECT SUBSTRING(created_at, 1, 10) as date, count(*) from orders o 
group by date; # 날짜별 데이터

# 범위 : case when ... then ... else ... end
SELECT pu.user_id, pu.point,
	case when pu.point > 10000 then 'well done'
	else 'harder!' end as msg
from point_users pu;

# 서브쿼리로 case문을 활용해 통계내기 : ,가 없음 주의!
SELECT pu.user_id, pu.point,
	(case when pu.point > 10000 then '1만 이상'
		when pu.point > 5000 then '5천 이상'
		else '5천 미만' end) as lv
from point_users pu;

# 서브쿼리, case when을 활용해 구간별 통계량 구하기 1
SELECT a.lv, count(*) AS cnt FROM 
	(SELECT 
		pu.user_id, 
		pu.point,
		(CASE WHEN pu.point > 10000 THEN '1만 이상'
			WHEN pu.point > 5000 THEN '5천 이상'
			ELSE '5천 미만' END) AS lv
	FROM point_users pu) a
GROUP BY a.lv

# 서브쿼리, case when, with절을 활용해 구간별 통계량 구하기 2
with table1 as (
	SELECT pu.user_id, pu.point,
		(case when pu.point > 10000 then '1만 이상'
			when pu.point > 5000 then '5천 이상'
			else '5천 미만' end) as lv
	from point_users pu
) SELECT a.lv, count(*) as cnt FROM table1 a
GROUP BY a.lv;

# SQL 문법 복습 초급 퀴즈
# 퀴즈 1 평균 포인트 점수 이상, 이하의 각 경우에 메시지 표기
SELECT round(AVG(pu.point)) as avg_point FROM point_users pu;
SELECT 
	pu.point_user_id,
	pu.point,
	(
		case when pu.point > 10000 then '잘 하고 있어요!'
			else '열심히 합시다!' end
	) as msg
FROM point_users pu;

with table1 as (
	SELECT 
		pu.point_user_id,
		pu.point,
		(
			case when pu.point > (
				SELECT round(AVG(pu.point),1) as avg_point FROM point_users pu
			) then '잘 하고 있어요!'
				else '열심히 합시다!' end
		) as msg
	FROM point_users pu
) SELECT * from table1 a;

# 퀴즈2 : 이메일 도메인 별 유저 수 세기
select 
	(SUBSTRING_INDEX(u.email, '@', -1)) as domain,
	count(*) as cnt
from users u
group by domain;

# 퀴즈3 : 화이팅이 포함된 오늘의 다짐만 출력하기
select *
from checkins c 
where c.comment like '%화이팅%';



# SQL 문법 복습 중급 퀴즈
# 퀴즈1 : 수강등록정보(enrolled_id) 별 전체 강의 수와 들은 강의 수 출력해보기
select * from enrolleds e;

select enrolled_id, count(*) as total_cnt
from enrolleds_detail ed 
group by enrolled_id;

select ed.enrolled_id, count(ed.done) as done_cnt
from enrolleds_detail ed
where ed.done = 1
group by ed.enrolled_id;

# 두 데이터 합치기
with table1 as (
	select ed.enrolled_id, count(*) as done_cnt from enrolleds_detail ed 
	where ed.done = 1
	group by ed.enrolled_id 
), table2 as (
	select ed.enrolled_id, count(*) as total_cnt from enrolleds_detail ed 
	group by ed.enrolled_id 
) 
select 
	a.enrolled_id, 
	a.done_cnt, 
	b.total_cnt, 
	round(a.done_cnt / b.total_cnt, 2) as ratio
from table1 a
inner join table2 b on a.enrolled_id = b.enrolled_id;


# 더 간단히 해보기
select 
	ed.enrolled_id,
	sum(ed.done) as done_cnt,
	count(*) as total_cnt,
	round(sum(ed.done) / count(*), 2) as ratio
from enrolleds_detail ed 
group by ed.enrolled_id

'ETC' 카테고리의 다른 글

메모리의 구조 (0)	2023.05.12
오버헤드, 스택 오버 플로우 (1)	2023.05.12
[스프링 데이터베이스] H2와 두루두루 설정하기 (0)	2023.04.23
[기타] 비즈니스 매너 (0)	2023.04.20
웹 개발 시 유용한 사이트 모음 - 2 : Flask (0)	2023.03.29

dev_choi

[SQL] 일단 시작해보기 - 가장 자주 쓰는 DML 위주

1. 개요

2. Select 쿼리문

3. Where 절

4. 통계량, GROUP BY (범주), ORDER BY(정렬)

5. 별칭 (Alias)

5. 테이블 간 연결하기 (JOIN, UNION)

6. 서브쿼리

7. with 절

8. SQL 실전

'ETC' 카테고리의 다른 글

티스토리툴바

[SQL] 일단 시작해보기 - 가장 자주 쓰는 DML 위주

1. 개요

2. Select 쿼리문

3. Where 절

4. 통계량, GROUP BY (범주), ORDER BY(정렬)

5. 별칭 (Alias)

5. 테이블 간 연결하기 (JOIN, UNION)

6. 서브쿼리

7. with 절

8. SQL 실전

'ETC' 카테고리의 다른 글

'ETC' Related Articles

티스토리툴바