🛠️ Fix names in Table
Table: Users
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| user_id | int |
| name | varchar |
+----------------+---------+
user_id is the primary key for this table.
This table contains the ID and the name of the user. The name consists of only lowercase and uppercase characters.
Write an SQL query to fix the names so that only the first character is uppercase and the rest are lowercase.
Return the result table ordered by user_id.
The query result format is in the following example.
Example 1:
Input:
Users table:
+---------+-------+
| user_id | name |
+---------+-------+
| 1 | aLice |
| 2 | bOB |
+---------+-------+
Output:
+---------+-------+
| user_id | name |
+---------+-------+
| 1 | Alice |
| 2 | Bob |
+---------+-------+✅ 해답
여기서는
name 값의 첫번째 글자들을 대문자로 바꾸고 나머지는 소문자로 바꾸며
이 값을 user_id 순서로 정렬하는 것이다.
이 문제를 해결하기 위해서는 몇가지 함수가 필요한데
일단 해답을 먼저 보자.
SELECT user_id,
CONCAT(UPPER(LEFT(name, 1)), (LOWER(SUBSTRING(name, 2))) AS name
FROM Users
ORDER BY user_id해답을 보니 몇가지가 아닌 것 같긴하지만 자세히 설명해보도록 하겠다.
- 일단
user_id를 가지고 온다. name값의 왼쪽에서 첫번째 글자를 가지고온다.
2-1. 그렇게 가져온 값을 대문자로 바꿔주고name값을 두번째 글자부터 가져온 다음
3-1. 그 값들을 소문자로 바꿔주고- 그것을 다 합쳐 name이라 이름 붙힌다.
- 마지막으로
user_id로 정렬한다.
자, 상당히 길어지겠지만 간단하게 하나씩 알아보겠다.
LEFT & RIGHT 함수
LEFT(값, N) / RIGHT(값, N)이 두 함수는 가지고 오려는 값의 왼쪽 또는 오른쪽에서 N번째 값을 가져온다.
SUBSTRING 함수
SUBSTRING(문자, 시작, 길이)이 함수는 해당 값에서 몇번째 글자부터 몇번째 글자까지 가져온다.
만약 SUBSTRING(sung, 1, 3)인 구문이면 출력 값은
sun이 되는 식이다.
UPPER & LOWER 함수
UPPER(값, N) / LOWER(값, N)이 두 함수는 LEFT & RIGHT 함수와 비슷하다.
값의 N번째의 글자들을 대문자 혹은 소문자로 변경하여 가져온다.
CONCAT 함수
CONCAT(문자 1, 문자 2, ...)CONCAT 함수는 둘 이상의 여러 입력한 인자의 문자들을 입력한 순서대로 합쳐서 반환해주는 함수이다.
여기까지 정리가 되었으면 위의 쿼리들이 충분히 이해가 갈 것이라 생각된다.
아, 그리고 ORDER BY 구문은 오름차순(ASC)가 기본이며 생략될 수 있고,
내림차순으로 정렬하고 싶다면 ORDER BY 정렬기준 DESC로 적용할 수 있다.
📈 Group Sold Products By the Date
Table Activities:
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| sell_date | date |
| product | varchar |
+-------------+---------+
There is no primary key for this table, it may contain duplicates.
Each row of this table contains the product name and the date it was sold in a market.Write an SQL query to find for each date the number of different products sold and their names.
Example 1:
Input:
Activities table:
+------------+------------+
| sell_date | product |
+------------+------------+
| 2020-05-30 | Headphone |
| 2020-06-01 | Pencil |
| 2020-06-02 | Mask |
| 2020-05-30 | Basketball |
| 2020-06-01 | Bible |
| 2020-06-02 | Mask |
| 2020-05-30 | T-Shirt |
+------------+------------+
Output:
+------------+----------+------------------------------+
| sell_date | num_sold | products |
+------------+----------+------------------------------+
| 2020-05-30 | 3 | Basketball,Headphone,T-shirt |
| 2020-06-01 | 2 | Bible,Pencil |
| 2020-06-02 | 1 | Mask |
+------------+----------+------------------------------+
Explanation:
For 2020-05-30, Sold items were (Headphone, Basketball, T-shirt), we sort them lexicographically and separate them by a comma.
For 2020-06-01, Sold items were (Pencil, Bible), we sort them lexicographically and separate them by a comma.
For 2020-06-02, the Sold item is (Mask), we just return it.✅ 해답
이번 문제에서는 내가 푼 해답과 Discussion에 소개된 다른 해답도 살펴보겠다.
일단 여기서는
sell_date를 기준으로 그룹핑하여 몇가지의 product가 팔렸는지 순위를 매기고 어떤 product가 팔렸는지 물품들을 나열하는 쿼리를 작성하면 된다.
My Answer
# sell_date와
SELECT sell_date,
# sell_date를 기준으로 내림차순으로 순위를 매긴 것을 num_sold라고 하고
RANK() OVER(ORDER BY sell_date DESC) AS num_sold,
# product 값들을 나열한 것을 products라고 이름 붙힌다.
product as products
# 이것들은 Activities 테이블에서 가져오며
FROM Activities
# sell_date를 기준으로 그룹핑하고
GROUP BY sell_date
# sell_date를 기준으로 오름차순 정렬해준다.
ORDER BY sell_date;나는 RANK() OVER() 함수를 사용했다.
이는 밑에서 소개하겠다.
Discussion 해답
# sell_date를 반환하고
SELECT sell_date
# 중복된 product 값을 세지 않은 것을 num_sold로 반환하고
, COUNT(DISTINCT product) num_sold
# 중복되지 않은 product의 값을 product값의 알파벳 기준으로
# ,를 붙혀 반환한다.
, GROUP_CONCAT(DISTINCT product ORDER BY product) products
# activities 테이블에서
FROM activities
# 위의 값들을 sell_date를 기준으로 그룹핑한다.
GROUP BY sell_date여기서는 나도 처음본 GROUP CONCAT 을 사용했다.
RANK() OVER()
RANK() OVER(ORDER BY / 순위를 매길 값 / 정렬 방식)
이를 간단히 소개하자면
RANK()의 괄호 안에는 아무 값이 들어가지 않고
OVER()의 괄호 안에 값이 들어가 그것을 기준으로 순위를 매겨주어 반환한다.
GROUP_CONCAT(함수)
GROUP CONCAT 이란 GROUP BY로 그룹화 한 데이터 확인 시,
특정 column에서 ,(comma)로 나열하여 조회하는 함수이다.
# 기본 형태
SELECT GROUP_CONCAT(COL2)
FROM 테이블명
GROUP BY COL1;
# GROUP_CONCAT 내에서 정렬하기
SELECT GROUP_CONCAT(COL2 ORDER BY COL2 DESC)
FROM 테이블명
GROUP BY COL1위의 쿼리를 예로 들자면 중복을 제거한 product 값을 product 순서로 즉, product 값을 알파벳 순서로 ,를 붙혀 가져옴으로
Basketball,Headphone,T-shirt 이렇게 값이 나올 수 있는 것이다.
🤒 Patients With a Condition
Table: Patients
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| patient_id | int |
| patient_name | varchar |
| conditions | varchar |
+--------------+---------+
patient_id is the primary key for this table.
'conditions' contains 0 or more code separated by spaces.
This table contains information of the patients in the hospital.Write an SQL query to report the patient_id, patient_name all conditions of patients who have Type I Diabetes. Type I Diabetes always starts with DIAB1 prefix
Return the result table in any order.
The query result format is in the following example.
Example 1:
Input:
Patients table:
+------------+--------------+--------------+
| patient_id | patient_name | conditions |
+------------+--------------+--------------+
| 1 | Daniel | YFEV COUGH |
| 2 | Alice | |
| 3 | Bob | DIAB100 MYOP |
| 4 | George | ACNE DIAB100 |
| 5 | Alain | DIAB201 |
+------------+--------------+--------------+
Output:
+------------+--------------+--------------+
| patient_id | patient_name | conditions |
+------------+--------------+--------------+
| 3 | Bob | DIAB100 MYOP |
| 4 | George | ACNE DIAB100 |
+------------+--------------+--------------+
Explanation: Bob and George both have a condition that starts with DIAB1.✅ 해답
여기서는
patient_id, patient_name의 conditions 값 중에서 DIAB1 접두어를 가지고 있는 값을 반환하는 쿼리를 작성하면 된다.
# Patients 테이블에서 모든 값을 가져오는데
SELECT *
FROM PATIENTS
# conditions 값이 (공백)DIAB1 를 포함하거나
WHERE CONDITIONS LIKE '% DIAB1%'
# DIAB1으로 시작해서 아무렇게나 끝나는 값을 반환해라.
OR CONDITIONS LIKE 'DIAB1%';여기서는 LIKE 구문에서의 Wildcard들이 적극적으로 쓰였다.
Example의 테이블들을 보면 conditions 값 중에 DIAB1 값이
뒤쪽에 공백과 같이 들어가 있는 값이 있으므로 여러 와일드카드들을 활용해 주었다.
다음에는 Day 4의 문제들을 알아보자.
