Other articles

1. [MySQL] 1대 다 LEFT JOIN

   테이블과 결과

   Input: 
   Visits
   +----------+-------------+
   | visit_id | customer_id |
   +----------+-------------+
   | 1        | 23          |
   | 2        | 9           |
   | 4        | 30          |
   | 5        | 54          |
   | 6        | 96          |
   | 7        | 54          |
   | 8        | 54          |
   +----------+-------------+
   visit_id is the primary key for this table.
   This table contains information about the customers who visited the mall.
   
   Transactions
   +----------------+----------+--------+
   | transaction_id | visit_id | amount |
   +----------------+----------+--------+
   | 2              | 5        | 310 …

   read more

2. [MySQL] ALL

   테이블

   orders

   order_number	customer_number
   1	1
   2	2
   3	3
   4	3

   문제

   • 가장 주문 횟수가 많은 고객을 뽑으시오

   답

   -- 1위 한 명만
   SELECT o.customer_number
   FROM orders o 
   GROUP BY o.customer_number 
   ORDER BY COUNT …

   read more

3. [MySQL] 첫 글자만 대문자로

   테이블과 결과

   Input: 
   Users table:
   +---------+-------+
   | user_id | name  |
   +---------+-------+
   | 1       | aLice |
   | 2       | bOB   |
   +---------+-------+
   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.
   
   Output: 
   +---------+-------+
   | user_id | name  |
   +---------+-------+
   | 1       | Alice |
   | 2       | Bob …

   read more

4. [MySQL] FULL OUTER JOIN

   테이블과 결과

   Input: 
   Employees table:
   +-------------+----------+
   | employee_id | name     |
   +-------------+----------+
   | 2           | Crew     |
   | 4           | Haven    |
   | 5           | Kristian |
   +-------------+----------+
   Salaries table:
   +-------------+--------+
   | employee_id | salary |
   +-------------+--------+
   | 5           | 76071  |
   | 1           | 22517  |
   | 4           | 63539  |
   +-------------+--------+
   Output: 
   +-------------+
   | employee_id |
   +-------------+
   | 1           |
   | 2           |
   +-------------+
   Explanation: 
   Employees 1, 2, 4, and 5 are working at this company.
   The name of employee 1 is missing …

   read more

5. [MySQL] 가장 가까운 좌표(x, y) 구하기

   테이블과 결과

   Input: 
   Point2D table:
   +----+----+
   | x  | y  |
   +----+----+
   | -1 | -1 |
   | 0  | 0  |
   | -1 | -2 |
   +----+----+
   Output: 
   +----------+
   | shortest |
   +----------+
   | 1.00     |
   +----------+
   Explanation: The shortest distance is 1.00 from point (-1, -1) to (-1, 2).
   

   문제

   The distance between two points p1(x1, y1) and p2(x2, y2) is …

   read more

6. [MySQL] 그룹별 최고 금액 보유자 뽑기

   테이블과 결과

   Input: 
   Employee table:
   +----+-------+--------+--------------+
   | id | name  | salary | departmentId |
   +----+-------+--------+--------------+
   | 1  | Joe   | 70000  | 1            |
   | 2  | Jim   | 90000  | 1            |
   | 3  | Henry | 80000  | 2            |
   | 4  | Sam   | 60000  | 2            |
   | 5  | Max   | 90000  | 1            |
   +----+-------+--------+--------------+
   Department table:
   +----+-------+
   | id | name  |
   +----+-------+
   | 1  | IT    |
   | 2  | Sales |
   +----+-------+
   Output: 
   +------------+----------+--------+
   | Department | Employee | Salary |
   +------------+----------+--------+
   | IT         | Jim      | 90000  |
   | Sales      | Henry …

   read more

7. [MySQL] 그룹별 랭킹상위 N명 구하기

   테이블과 결과

   Input: 
   Employee table:
   +----+-------+--------+--------------+
   | id | name  | salary | departmentId |
   +----+-------+--------+--------------+
   | 1  | Joe   | 85000  | 1            |
   | 2  | Henry | 80000  | 2            |
   | 3  | Sam   | 60000  | 2            |
   | 4  | Max   | 90000  | 1            |
   | 5  | Janet | 69000  | 1            |
   | 6  | Randy | 85000  | 1            |
   | 7  | Will  | 70000  | 1            |
   +----+-------+--------+--------------+
   Department table:
   +----+-------+
   | id | name  |
   +----+-------+
   | 1  | IT    |
   | 2  | Sales |
   +----+-------+
   Output …

   read more

8. [MySQL] 그룹별 순서 매기기

   그룹별 순서 매기기

   SELECT 
       pf.*
       ,CASE
           WHEN @grp = pf.painpoint_id
               THEN @rownum:= @rownum + 1
           ELSE @rownum := 1
       END AS feebackOrder
       ,(@grp:= pf.painpoint_id) AS dum
   FROM painpoint_feedback pf, (SELECT @rownum:= 0, @grp:= '') r
   ORDER BY pf.painpoint_id 
   ;
   

   read more

9. [MySQL] 경기결과 승점 계산

   테이블과 결과

   Input: 
   Teams table:
   +-----------+--------------+
   | team_id   | team_name    |
   +-----------+--------------+
   | 10        | Leetcode FC  |
   | 20        | NewYork FC   |
   | 30        | Atlanta FC   |
   | 40        | Chicago FC   |
   | 50        | Toronto FC   |
   +-----------+--------------+
   Matches table:
   +------------+--------------+---------------+-------------+--------------+
   | match_id   | host_team    | guest_team    | host_goals  | guest_goals  |
   +------------+--------------+---------------+-------------+--------------+
   | 1          | 10           | 20            | 3           | 0            |
   | 2          | 30           | 10            | 2           | 2            |
   | 3          | 10           | 50            | 5           | 1            |
   | 4          | 20 …

   read more