카테고리 없음

데이터분석 > 3주차 > 파트 02 > 0827~0828

나는야석사 2024. 8. 30. 22:36

숫자

5. 다양한 데이터 타입 다루기

  • 숫자
  • 문자
  • 이진
  • Array
  • Key-Value

숫자

  • Bit(M) : Bit 뒤에 오는 괄호 안에 있는 개수만큼의 비트를 저장할 수 있음. !<=M<=64
  • TinyInt : Signed 범위 : -127~127, Unsigned 범위 : 0~255
  • Bool, Boolean : True, False
  • Float : 4 바이트
  • Dounle : 8 바이트
SELECT IF(1, 'true', 'fasle')

true 반환

0만 fasle 반환하고 나머지 수는 다 true 반환, 음수도 마찬가지임

SELECT IF(2=True, 'true', 'fasle')

2=1 로 쓴 것과 동일함

True를 False로 써도, 2=0으로 쓴 것과 동일하기 때문에 FALSE 반환

1=True 혹은 0=False로 쓰면 둘 다 True 반환

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int unsigned NOT NULL,
  `category` varchar(40) NOT NULL,
  `name` varchar(50) NOT NULL,
  `price` int unsigned NOT NULL,
  `sales_yn` BOOL NOT NULL,
  PRIMARY KEY (`product_id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `products` (`product_id`, `category`, `name`, `price`, `sales_yn`) VALUES
  (0, '키즈', '어린이칫솔', 1500, true),
  (1, '스포츠', '손목보호대', 10000, true),
  (2, '주방용품', '밥그릇', 2000, false),
  (3, '디지털', '마우스', 15000, true);
  
  
SELECT *
FROM products
WHERE sales_yn=True

CREATE TABLE IF NOT EXISTS 'products' (
  'product_id' int(6) NOT NULL,
  'category' varchar(40) NOT NULL,
  'name' varchar(10) NOT NULL,
  'price' tinyint unsigned NOT NULL,
  'sales_yn' BOOL NOT NULL,
  PRIMARY KEY ('product_id')
);

INSERT INTO 'products' ('product_id', 'category', 'name', 'price', 'sales_yn') VALUES
  (0, '키즈', '어린이칫솔', 1500, true),
  (1, '스포츠', '손목보호대', 10000, true),
  (2, '주방용품', '밥그릇', 2000, false),
  (3, '디지털', '마우스', 15000, true);

이 price 테이블에서 int -> tiny int로 바꿀 시 tinyint는 0~255까지의 범위만 포함하기 때문에 빌드가 되지 않음

price의 값을 소수점 아래가 존재하는 실수형으로 바꾸면, 알아서 반올림이 됨

문자

  • CHAR : 고정된 길이의 문자열
    • 길이 : 0~255
    • 선언된 값보다 짧은 문자열이 들어오면, 빈 문자열로 나머지 길이를 채움
  • VARCHAR : 변동 가능한 길이의 문자열
    • 길이 : 0~65535
  • TEXT : 변동 가능한 길이의 문자열
    • 길이 : 최대 65535(길이 설정 불가)
    • 기본값 지정 불가
  • TINYTEXT : 작은 TEXT
    • 길이 최대 255
  • MEDIUMTEXT : 중간 크기 TEXT
    • 길이 최대 16777215
  • LONGTEXT : 큰 크기 TEXT 
    • 길이 최대 4294967295
  • ENUM : 최초에 지정해 둔 리스트에 포함되는 값만 저장
    • 효율적인 데이터 젖아 가능
    • 유연성, 확장성 낮음
    • 훨씬 적은 저장소 용량으로 동일한 데이터를 저장할 수 있다는 장점이 있음
    • 데이터가 들어왔을 때 컬럼에 해당되는 내용(문자열)이 저장되지 않고 카테고리화 해서 0, 1 처럼 표현됨
  • SET : 최초에 지정해 둔 리스트에 포함되는 값들을 중복으로 저장
    • 64개까지
CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int unsigned NOT NULL,
  `category` varchar(40) NOT NULL,
  `name` char(4) NOT NULL,
  `price` int unsigned NOT NULL,
  PRIMARY KEY (`product_id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `products` (`product_id`, `category`, `name`, `price`) VALUES
  (0, '키즈', '어린이칫솔', 1500),
  (1, '스포츠', '손목보호대', 10000),
  (2, '주방용품', '밥그릇', 2000),
  (3, '디지털', '마우스', 15000);

공백이 아닌 문자열이 길이를 초과했을 때 에러가 발생함!

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int unsigned NOT NULL,
  `category` varchar(40) NOT NULL,
  `name` char(4) NOT NULL,
  `price` int unsigned NOT NULL,
  PRIMARY KEY (`product_id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `products` (`product_id`, `category`, `name`, `price`) VALUES
  (0, '키즈', '어린', 1500),
  (1, '스포츠', '손목', 10000),
  (2, '주방용품', '밥', 2000),
  (3, '디지털', '마', 15000);

이렇게 4비트 안쪽이면 에러가 나지 않음

varchar로 설정했을 경우 공백을 추가했을 때 length함수를 이용하면 공백이 추가되어 길이가 표현되는 것을 볼 수 있음

varchar로 설정한 경우 공백까지 저장됨!

이진

바이너리 파일 : 데이터 저장/활용 위해 0과 1의 형식으로 인코딩해둔 파일

  • BLOB(Binary Large Object)
    • 길이 최대 65535(길이 설정 불가)
    • 기본값 지정 불가
  • TINYBLOB(작은 BLOB)
    • 길이 최대 255
  • MEDIUMBLOB(중간 크기 BLOB)
    • 길이 최대 166777215
  • LONGBLOB(큰 BLOB)
    • 길이 최대 4294967295
  • BINARY(Binary Strings(고정된 길이))
    • 길이 0~255
    • 선언된 값보다 짧은 문자열이 들어오면, 빈 문자열로 나머지 길이를 채움
    • Byte 기준으로 길이를 설정
  • VARBINARY(Binary Strings(변동 가능한 길이))
    • 길이 0~255

기존의 char, varchar의 길이 설정 : char(5), varchar(5) -> character 기준 4글자면 4, 5글자면 5

binary, varbinary는 괄호 안에 들어가는 숫자가 Byte 기준임

한글은 한 글자에 3바이트

binary(6)인 경우 '키즈 ' -> 한글 두 개, 공백 하나 7바이트라서 오류남

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int unsigned NOT NULL,
  `category` varchar(40) NOT NULL,
  `name` binary(20) NOT NULL,
  `price` int unsigned NOT NULL,
  `size` ENUM('XS', 'S', 'M', 'L', 'XL'),
  PRIMARY KEY (`product_id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `products` (`product_id`, `category`, `name`, `price`, `size`) VALUES
  (0, '키즈', '키즈  ', 1500, 'S'),
  (1, '스포츠', '손목', 10000, 'M'),
  (2, '주방용품', '밥', 2000, 'L'),
  (3, '디지털', '밥', 15000, 'XL');

SELECT *
from products

배열(Array)

  • 배열 : 데이터가 저장된 리스트
    • ['a', 'b', 'c'], [1, 2, 3] 등
  • 원소 : 배열에 저장된 각 데이터
  • JSON 타입으로 배열 저장
  • 기본값 설정 불가

1. JSON_ARRAY : 입력을 JSON 배열로 반환하는 함수 - select, insert에서 주로 사용됨

2. JSON_TYPE : JSON 데이터의 타입을 반환하는 함수

 

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int unsigned NOT NULL,
  `category` varchar(40) NOT NULL,
  `name` varchar(50) NOT NULL,
  `price` int unsigned NOT NULL,
  `options` JSON NULL,
  PRIMARY KEY (`product_id`)
);

INSERT INTO `products` (`product_id`, `category`, `name`, `price`, `options`) VALUES
  (0, '키즈', '어린이칫솔', 1500, JSON_ARRAY("빨강", "파랑")),
  (1, '스포츠', '손목보호대', 10000, JSON_ARRAY("S", "M", "L")),
  (2, '주방용품', '밥그릇', 2000, JSON_ARRAY("소", "중", "대")),
  (3, '디지털', '마우스', 15000, NULL);

options 컬럼에 JSON 타입 추가

 

SELECT options, JSON_TYPE(options)
FROM products

array 형태로 함수가 들어간 것 확인

 

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int unsigned NOT NULL,
  `category` varchar(40) NOT NULL,
  `name` varchar(50) NOT NULL,
  `price` int unsigned NOT NULL,
  `options` JSON NULL,
  PRIMARY KEY (`product_id`)
);

INSERT INTO `products` (`product_id`, `category`, `name`, `price`, `options`) VALUES
  (0, '키즈', '어린이칫솔', 1500, '["빨강", "파랑"]'),
  (1, '스포츠', '손목보호대', 10000, JSON_ARRAY("S", "M", "L")),
  (2, '주방용품', '밥그릇', 2000, JSON_ARRAY("소", "중", "대")),
  (3, '디지털', '마우스', 15000, NULL);

JSON_ARRAY가 아니더라도 '[]'형태로 추가 가능

 

ARRAY 안에 원소가 array로 가능 -> Nested Array

 

3. JSON_EXTRACT -> Array 내부에 접근 가능

SELECT JSON_EXTRACT(options, '$') as all_elements
FROM products
# JSON 타입의 options 컬럼에서 모든 값을 뽑아내는 것

SELECT JSON_EXTRACT(options, '$[0]') as first_elements
FROM products
# options 컬럼에서 첫번째 값을 뽑아내는 것

$는 key를 지정하는 것

$만 쓰면 모든 원소를 가져옴

$[0] 각 첫번째 원소만 가져옴

 

Key-value : Key와 Value로 이루어진 데이터

  • Key는 데이터를 찾을 수 있는 기준
  • Value는 Key에 대응되는 데이터
    • 1. {'이름':'홍길동', '부서':'개발팀'}
    • 2. {'색상' : ['빨강', '파랑'], '사이즈':['S', 'M', 'L']}
  • 2. Json 타입으로 Key-value를 저장

1. JSON_OBJECT

key value 입력값을 json 객체로 반환하는 함수

insert, select 구문에서 주로 사용

Key- value 순으로 입력

info라는 컬럼을 json 타입으로 지정

json_object로 값을 집어넣음

off가 첫 번째 key가 됨

두 번째 인자는 json array

off라는 key에 대해 일,월 두 값이 들어있는 것이 value

subsitute가 두번째 키,

민수가 두 번째 키에 대응하는 value

INSERT INTO 'managers_v2' ('id', 'name', 'managning', 'info') VALUES
(0, '영희', '스포츠', JSON_OBJECT('off', JSON_ARRAY('일', '월'), 'subsitute', '민수'))...

 

2. JSON_EXTRACT

array에서 인덱싱 시 $ 사용

key value 에서는 $.off(key 이름)

 

CREATE TABLE IF NOT EXISTS `managers_v2` (
  `id` int unsigned NOT NULL,
  `name` varchar(40) NOT NULL,
  `managing` varchar(50) NOT NULL,
  `off` varchar(50) NOT NULL,
  `substitute` int unsigned NOT NULL,
  PRIMARY KEY (`id`)
);


INSERT INTO `managers_v2` (`id`, `name`, `managing`, `off`, `substitute`) VALUES
  (0, '영희', '스포츠', '토,일', 1),
  (1, '철수', '주방용품', '일,월', 2),
  (2, '민수', '디지털', '화,토', 3),
  (3, '길순', '키즈', '금,토', 0);

무슨 요일에 쉬는지 의미하는 off와 대체자를 뜻하는 subsitute를 하나의 key value로 묶으면 아래와 같이 됨

CREATE TABLE IF NOT EXISTS 'managers_v2' (
  'id' int unsigned NOT NULL,
  'name' varchar(40) NOT NULL,
  'managing' varchar(50) NOT NULL,
  'info' JSON NULL,
  PRIMARY KEY ('id')
);


INSERT INTO `managers_v2` ('id', 'name', 'managing', 'info') VALUES
  (0, '영희', '스포츠', JSON_OBJECT('off', JSON_ARRAY('일', '월'), 'substitute', '민수')),
  (1, '철수', '주방용품', JSON_OBJECT('off', JSON_ARRAY('화', '수'), 'substitute', '길순')),
  (2, '민수', '디지털', JSON_OBJECT('off', JSON_ARRAY('목', '금'), 'substitute', '철수')),
  (3, '길순', '키즈', JSON_OBJECT('off', JSON_ARRAY('금', '토'), 'substitute', '영희'));

홀수 번째 input은 key가 되고 짝수번째 input은 앞의 key에 해당하는 value에 해당됨

SELECT *
FROM managers_v2

SELECT JSON_EXTRACT(info, '$.off') as off
FROM managers_v2

info라는 key value 컬럼에서 off라는 key에 대응하는 value를 출력함

SELECT JSON_EXTRACT(info, '$.off[0]') as off
FROM managers_v2
# 인덱싱 가능

3. JSON_INSERT

key value 쌍 삽입 시 사용

managers_v2라는 테이블에 info 컬럼

4. JSON_REPLACE

UPDATE managers_v2 set info = JSON_INSERT(info, '$.new', JSON_ARRAY(1,2,3,4));
# managers_v2 테이블의 info 컬럼에 새로운 key value를 삽입하는 것
# new 컬럼을 만들고 1, 2, 3, 4 값을 value로 넣는 것

UPDATE managers_v2 set info = JSON_REPLACE(info, '$.new', 1)
# 이미 있는 컬럼 값을 바꾸는 함수
# new라는 키에 대한 값들을 모두 1로 바꾸는 함수

 

 

UPDATE managers_v2 set info = JSON_INSERT(info, '$.education_time', JSON_ARRAY(5, 10))

education_time이라는 key에 대응되는 value를 업데이트

UPDATE managers_v2 set info = JSON_REPLACE(info, '$.education_time', JSON_ARRAY(0, 10))

 

특정 값만 업데이트

UPDATE managers_v2 set info = JSON_REPLACE(info, '$.education_time[0]', 10)

 

key에 대응되는 value로 array 뿐만 아니라 key-value가 올 수도 있음

 

매니저 별로 의무 교육 과목, 의무 교육 시간이 다르다고 가정

CREATE TABLE IF NOT EXISTS `managers_v2` (
  `id` int unsigned NOT NULL,
  `name` varchar(40) NOT NULL,
  `managing` varchar(50) NOT NULL,
  `info` JSON NULL,
  PRIMARY KEY (`id`)
);


INSERT INTO `managers_v2` ('id', 'name', 'managing', 'info') VALUES
  (0, '영희', '스포츠', JSON_OBJECT('off', JSON_ARRAY('일', '월'), 'substitute', '민수', 
  'education_time', JSON_OBJECT('데이터', 3, '소방', 2) )),
  (1, '철수', '주방용품', JSON_OBJECT('off', JSON_ARRAY('화', '수'), 'substitute', '길순', 
  'education_time', JSON_OBJECT('인사', 2, '데이터', 3))),
  (2, '민수', '디지털', JSON_OBJECT('off', JSON_ARRAY('목', '금'), 'substitute', '철수', 
  'education_time', JSON_OBJECT('보안', 3, '회계', 1))),
  (3, '길순', '키즈', JSON_OBJECT('off', JSON_ARRAY('금', '토'), 'substitute', '영희', 
  'education_time', JSON_OBJECT('인공지능', 1, '소방', 1)));

SELECT JSON_EXTRACT(info, '$.education_time')
from managers_v2

 

education_time에 대응되는 value인 안쪽의 key value 반환

 

SELECT name, JSON_EXTRACT(JSON_EXTRACT(info, '$.education_time'), '$.데이터')
from managers_v2

한 번 더 JSON_EXTRACY 실행 시 안쪽에 있는 key value 추출 가능

문제 설명

다음은 어느 쇼핑몰의 상품 정보를 담은 PRODUCTS 테이블입니다. PRODUCTS 테이블은 아래와 같은 구조로 되어있으며, PRODUCT_ID, PRODUCT_NAME, CATEGORY_ID, UNIT_PRICE, STOCK은 각각 상품의 id, 상품명, 상품이 속한 카테고리의 id, 단가, 재고량을 나타냅니다.

COLUMN NAMETYPENULLABLE
PRODUCT_ID INTEGER FALSE
PRODUCT_NAME VARCHAR FALSE
CATEGORY_ID INTEGER FALSE
UNIT_PRICE NUMERIC FALSE
STOCK INTEGER FALSE
문제

상품을 보여줄 화면을 구상하는 UI 디자이너가 상품 명이 몇 글자인지 파악해 달라고 합니다. products 테이블에서 상품 명 길이의 분포를 예시와 같은 형식으로 조회하는 SQL문을 작성해 주세요. 이때 컬럼명은 순서대로 val1, val2로 지정하고, 결과는 상품 명 길이 순으로 보여주면 됩니다.

예시

예를 들어 PRODUCTS 테이블이 다음과 같다면

product_idproduct_namecategory_idunit_pricestock
1 Workout Shorts 1 28.88 22
2 3.5mm Silver Tibe 2 19.99 50
3 ABC Yoga Pants 1 35.99 13
4 Embroidery Floss 3 9.99 10000

SQL을 실행하면 다음과 같이 출력되어야 합니다.

val1val2
14 2
16 1
17 1 
SELECT LENGTH(product_name) as val1, count(*) as val2
FROM PRODUCTS
GROUP BY val1
ORDER BY val1

문제 설명

다음은 어느 쇼핑몰의 상품 정보를 담은 PRODUCTS 테이블입니다. PRODUCTS 테이블은 아래와 같은 구조로 되어있으며, PRODUCT_ID, PRODUCT_NAME, CATEGORY_ID, UNIT_PRICE, STOCK은 각각 상품의 id, 상품명, 상품이 속한 카테고리의 id, 단가, 재고량을 나타냅니다.

COLUMN NAMETYPENULLABLE
PRODUCT_ID INTEGER FALSE
PRODUCT_NAME VARCHAR FALSE
CATEGORY_ID INTEGER FALSE
UNIT_PRICE NUMERIC FALSE
STOCK INTEGER FALSE
문제

화면에 상품을 표시할 때 단가가 20이 넘는 상품의 이름 앞에는 [할인] 표시를 붙여서 표시하려고 합니다. 상품의 id, 화면에 표시될 상품의 이름, 단가를 상품의 id 순으로 조회하는 SQL 문을 작성해 주세요. 화면에 표시될 상품의 이름은 display_name이라는 이름으로 지정해 주세요.

예시

예를 들어 PRODUCTS 테이블이 다음과 같다면

product_idproduct_namecategory_idunit_pricestock
1 Workout Shorts 1 28.88 22
2 3.5mm Silver Tibe 2 19.99 50
3 ABC Yoga Pants 1 35.99 13
4 Embroidery Floss 3 9.99 10000

SQL을 실행하면 다음과 같이 출력되어야 합니다.

product_iddisplay_nameunit_price
1 [할인] Workout Shorts 28.88
2 3.5mm Silver Tibe 19.99
3 [할인] ABC Yoga Pants 35.99
4 Embroidery Floss 9.99 
SELECT product_id, CONCAT(IF(unit_price>20, '[할인] ', ''), product_name) as display_name, unit_price
FROM PRODUCTS
ORDER BY product_id

문제 설명

다음은 어느 쇼핑몰의 유저 정보를 담은 USERS 테이블입니다. USERS 테이블은 아래와 같은 구조로 되어있으며, USER_ID, USER_NAME, EMAIL, JOINED_AT은 각각 유저의 id, 유저의 이름, 이메일, 가입 시점을 나타냅니다.

COLUMN NAMETYPENULLABLE
USER_ID INTEGER FALSE
USER_NAME VARCHAR FALSE
EMAIL VARCHAR FALSE
JOINED_AT DATETIME FALSE
문제

모든 유저에 대해 유저의 id, 이메일, 이메일 아이디[1] 를 조회하는 SQL문을 작성해 주세요. 이때 이메일 아이디는 email_id라는 이름으로 지정하고, 결과는 유저의 id 순으로 나열해야 합니다.

힌트: LOCATE 함수와 LEFT 함수를 같이 사용해야 합니다.

예시

예를 들어 USERS 테이블이 다음과 같다면

user_iduser_nameemailjoined_at
2 Axel Mckee axel@pcsql.com 2024-03-01 08:23:59
3 Bryce Velez bryce@pscql.com 2024-03-01 08:30:00
4 Zavier Owen zavior@pcsql.com 2024-03-02 21:12:00
6 Karlie Andrew karlie@pscql.com 2024-03-13 23:41:17

SQL을 실행하면 다음과 같이 출력되어야 합니다.

user_idemailemail_id
2 axel@pcsql.com axel
3 bryce@pscql.com bryce
4 zavior@pcsql.com zavior
6 karlie@pscql.com karlie

[1] 이메일 아이디는 이메일 주소에서 '@' 앞의 문자만 추출한 것을 의미합니다.

 

SELECT user_id, email, SUBSTRING(email, 1, LOCATE('@', email) - 1) AS email_id
FROM USERS
ORDER BY user_id

문제 설명

다음은 어느 쇼핑몰의 유저 정보를 담은 USERS 테이블입니다. USERS 테이블은 아래와 같은 구조로 되어있으며, USER_ID, USER_NAME, EMAIL, JOINED_AT은 각각 유저의 id, 유저의 이름, 이메일, 가입 시점을 나타냅니다.

COLUMN NAMETYPENULLABLE
USER_ID INTEGER FALSE
USER_NAME VARCHAR FALSE
EMAIL VARCHAR FALSE
JOINED_AT DATETIME FALSE
문제

일부 유저의 이메일 주소가 잘못 기입된 걸 발견했습니다. 이메일에 @pscql.com라고 되어있는 경우, 이를 @pcsql.com 로 변경하려고 합니다. 이메일이 잘못 기입된 유저에 대해 유저의 id, 잘못된 이메일 주소, 변경된 이메일 주소를 조회하는 SQL 문을 작성해 주세요. 이때 잘못된 이메일 주소의 필드명은 wrong_email, 변경된 이메일 주소의 필드명은 corrected_email으로 지정해 주세요.

예시

예를 들어 USERS 테이블이 다음과 같다면

user_iduser_nameemailjoined_at
2 Axel Mckee axel@pcsql.com 2024-03-01 08:23:59
3 Bryce Velez bryce@pscql.com 2024-03-01 08:30:00
4 Zavier Owen zavior@pcsql.com 2024-03-02 21:12:00
6 Karlie Andrew karlie@pscql.com 2024-03-13 23:41:17

SQL을 실행하면 다음과 같이 출력되어야 합니다.

user_idwrong_emailcorrected_email
3 bryce@pscql.com bryce@pcsql.com
6 karlie@pscql.com karlie@pcsql.com 
SELECT user_id, email as wrong_email, REPLACE(email, "pscql", "pcsql") as corrected_email
FROM USERS
WHERE email LIKE "%pscql.com"

 

문제 설명

다음은 어느 쇼핑몰의 상품 정보를 담은 PRODUCTS 테이블입니다. PRODUCTS 테이블은 아래와 같은 구조로 되어있으며, PRODUCT_ID, PRODUCT_NAME, CATEGORY_ID, UNIT_PRICE, STOCK은 각각 상품의 id, 상품명, 상품이 속한 카테고리의 id, 단가, 재고량을 나타냅니다.

COLUMN NAMETYPENULLABLE
PRODUCT_ID INTEGER FALSE
PRODUCT_NAME VARCHAR FALSE
CATEGORY_ID INTEGER FALSE
UNIT_PRICE NUMERIC FALSE
STOCK INTEGER FALSE
문제

모든 상품에 대해 상품의 id, 상품의 이름, 상품의 이름을 2번째 문자부터 5번째 문자까지 추출한 결과를 조회하는 SQL문을 작성해 주세요. 세 번째 필드의 이름은 extracted 로 지정하고, 결과는 상품의 id 순으로 나와야 합니다. 모든 상품의 이름은 5글자 이상입니다.

예제

예를 들어 PRODUCTS 테이블이 다음과 같다면

product_idproduct_namecategory_idunit_pricestock
1 Workout Shorts 1 28.88 22
2 3.5mm Silver Tibe 2 19.99 50
3 ABC Yoga Pants 1 35.99 13
4 Embroidery Floss 3 9.99 10000

SQL을 실행하면 다음과 같이 출력되어야 합니다.

product_idproduct_nameextracted
1 Workout Shorts orko
2 3.5mm Silver Tibe .5mm
3 ABC Yoga Pants BC Y
4 Embroidery Floss mbro 
SELECT product_id, product_name, SUBSTRING(product_name, 2, 4) as extracted
FROM PRODUCTS
ORDER BY product_id

문제 설명

다음은 어느 쇼핑몰의 상품 이름을 저장하는 PRODUCT_NAMES 테이블입니다. PRODUCT_NAMES 테이블은 아래와 같은 구조로 되어있으며, PRODUCT_ID, PRODUCT_NAME은 각각 상품의 id, 상품명을 나타냅니다.

COLUMN NAMETYPENULLABLE
PRODUCT_ID INTEGER FALSE
PRODUCT_NAME VARCHAR FALSE
문제

PRODUCT_NAME 컬럼의 값은 제품명 (제품이 속한 카테고리)과 같은 형태로 들어있습니다. 각 상품별로 상품 id와 PRODUCT_NAME 컬럼의 값에서 제품이 속한 카테고리를 추출하는 SQL 문을 작성해 주세요. 이때 결과는 상품 id 순으로 나열하고, 추출한 카테고리의 이름은 CATEGORY으로 지정해 주세요.

주의: 제품명과 제품이 속한 카테고리는 모두 영문과 띄어쓰기로만 구성되어 있습니다.

예시

예를 들어 PRODUCT_NAMES 테이블이 다음과 같다면

PRODUCT_IDPRODUCT_NAME
1 Galaxy Book Pro (Category: Electronics)
2 Comfy Tee (Category: Clothing)
3 Chic Bracelet (Category: Accessories)

SQL을 실행하면 다음과 같이 출력되어야 합니다.

PRODUCT_IDCATEGORY
1 Category: Electronics
2 Category: Clothing
3 Category: Accessories 
SELECT PRODUCT_ID, 
SUBSTRING(PRODUCT_NAME, LOCATE('(', PRODUCT_NAME) + 1, 
LOCATE(')', PRODUCT_NAME) - LOCATE('(', PRODUCT_NAME) - 1) AS CATEGORY

FROM PRODUCT_NAMES

ORDER BY PRODUCT_ID
  • 정답
    객관식 1

    다음 중 SQL 테이블을 집합으로 생각할 때 옳지 않은 설명은 무엇인가요?

     
    ①테이블 하나에 집합 하나를 대응시킬 수 있다.
     
    ②SELECT, WHERE 문을 사용하여 집합의 일부를 선택할 수 있다.
     
    ③UNION 을 사용하면 두 테이블의 합집합을 구할 수 있다.
     
    ④테이블은 중복 데이터를 허용하지 않는다.V
     
    ⑤JOIN 연산을 사용하여 두 테이블의 교집합, 차집합을 구할 수 있다.
  • 정답
    객관식 2

    다음 중 SQL에서 *와 %에 대한 내용으로 적절하지 않은 것은 무엇인가요?

     
    ①SELECT * 사용 시, 불필요한 컬럼도 가져와 쿼리 성능이 저하될 수 있다.
     
    ②SELECT * 사용 시, 불필요한 컬럼도 가져와 쿼리 비용이 증가할 수 있다.
     
    ③LIKE % 구문은 인덱스를 사용하지 않는 경우가 있어 풀스캔을 야기할 수 있다.
     
    ④*는 명확히 어떤 컬럼을 사용 하는지 파악이 어렵게 만들어 코드의 가독성을 떨어뜨린다.
     
    ⑤%는 데이터 타입 변환 오류를 방지하기 위해 사용된다.V
  • 정답
    객관식 3

    다음 중 SQL에서 데이터 타입을 제대로 확인하지 않고 쿼리문을 작성했을 때 발생할 수 있는 문제가 아닌 것은 무엇인가요?

     
    ①불필요한 데이터 변환으로 성능 저하
     
    ②데이터 정렬이 예상과 다르게 될 수 있음 V
     
    ③JOIN 연산 시 데이터 타입 불일치로 인한 오류
     
    ④SELECT 문에서 데이터가 정확히 조회되지 않음
     
    ⑤데이터가 자동으로 정렬된 결과가 조회됨
  • 정답
    객관식 4

    다음 중 SQL에서 JOIN을 사용할 때 유의해야 할 점으로 적절하지 않은 것은 무엇인가요?

     
    ①JOIN 연산의 순서를 최적화하여 성능을 개선할 수 있다.
     
    ②JOIN을 사용할 때는 항상 서브쿼리를 사용하는 것이 좋다.V
     
    ③많은 JOIN을 사용하면 쿼리 성능에 영향을 줄 수 있다.
     
    ④JOIN 조건을 명확하게 지정하지 않으면 카테시안 곱이 발생할 수 있다.
     
    ⑤INNER JOIN은 두 테이블의 공통된 부분만 반환한다.
  • 정답
    객관식 5

    다음 중 SQL 코드의 가독성을 높이기 위한 방법으로 적절하지 않은 것은 무엇인가요?

     
    ①쿼리를 여러 줄로 나누어 작성한다.
     
    ②의미 있는 별칭(alias)을 사용한다.
     
    ③주석을 추가하여 각 코드의 내용을 설명한다.
     
    ④복잡한 쿼리를 하나의 큰 쿼리로 작성한다.V
     
    ⑤SQL 키워드를 대문자로 작성하여 구분한다.

0828

테이블을 집합으로 생각하기

JOIN 정리 시 벤다이어그램에 빗대어 설명했는데, 테이블도 집합에 빗대어 생각해도 됨

- 최대한 작게 만들어 놓고 JOIN 하기

SELECT @@profiling;

쿼리 동작 시간 확인

 

WHERE 절이 뒤쪽에 있는 것과, WHERE 절이 블록 안에 있는 것의 실행 시간을 비교하면, 첫 쿼리가 훨씬 느림

*, % 사용 지양하기

- 행을 제한적으로 조회

  • 1. LIMIT 걸고 조회하기
  • 2. 파티션이 있는 테이블인지 확인하고, 파티션을 필터 조건으로 걸고 조회하기

- 컬럼 제한적으로 조회

  • 3. 컬럼 수가 많은 테이블을 조회할 때 SELECT * 지양하기
  • 4. LIKE 사용 시 % 제한적으로 사용하기

데이터 타입 잘 확인하기

1. 비교 연산자를 쓸 때 타입을 확인하기 - 동일한 타입의 값으로 비교

2. WHERE 절에서 왼쪽 컬럼에 함수 적용 지양하기 - 인덱스 사용할 수 없기 때문

JOIN 시 유의할 점

연산량이 커서 JOIN 대상 테이블을 최대한 줄여놓고 시작해야함

1. JOIN 하는 테이블 간의 관계를 고려하기

  • 1:1 
    • 왼쪽 테이블의 행 하나가 오른쪽 테이블의 행 하나에 대응되는 경우
  • 1:n
    • 왼쪽 테이블의 행 하나가 오른쪽 테이블의 행 여러 개에 대응되는 경우
  • n:n
    • 왼쪽 행 하나가 오른쪽 테이블의 행 여러 개에 대응되고 오른쪽의 행 하나도 왼쪽 테이블의 행 여러 개에 대응되는 경우

2. 데이터 중복이 있는지 확인하기

큰 테이블 간 결합 시 중복이 있으면 쿼리 속도 매우 느려질 수 있음

 

3. 여러 가지 쿼리 방식을 고려하자

같은 결과라도 다양한 방식 가능

 

가독성 높이기

1. 서브쿼리보다는 WITH 구문이 가독성이 좋다

2. WITH 절을 사용할 때, 각 블록 이름을 잘 지정하자

3. 쿼리가 복잡해지면 중간중간 주석을 작성하자