1. [MySQL] 한 줄로 출력

    테이블과 결과

    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 …
    read more

  2. [MySQL] 하루 단위로 7일 동안의 매출 평균

    테이블과 결과

    Input: 
Customer table:
+-------------+--------------+--------------+-------------+
| customer_id | name         | visited_on   | amount      |
+-------------+--------------+--------------+-------------+
| 1           | Jhon         | 2019-01-01   | 100         |
| 2           | Daniel       | 2019-01-02   | 110         |
| 3           | Jade         | 2019-01-03   | 120         |
| 4           | Khaled       | 2019-01-04   | 130         |
| 5           | Winston      | 2019-01-05   | 110         | 
| 6           | Elvis        | 2019-01-06   | 140         | 
| 7 …
    read more

  3. [MySQL] JOIN ON 조건 >=

    테이블과 결과

    Input: 
Buses table:
+--------+--------------+
| bus_id | arrival_time |
+--------+--------------+
| 1      | 2            |
| 2      | 4            |
| 3      | 7            |
+--------+--------------+
Passengers table:
+--------------+--------------+
| passenger_id | arrival_time |
+--------------+--------------+
| 11           | 1            |
| 12           | 5            |
| 13           | 6            |
| 14           | 7            |
+--------------+--------------+
Output: 
+--------+----------------+
| bus_id | passengers_cnt |
+--------+----------------+
| 1      | 1              |
| 2      | 0              |
| 3      | 3              |
+--------+----------------+
Explanation: 
- Passenger 11 arrives at time 1.
- Bus 1 arrives at time …
    read more

  4. [MySQL] JOIN ON 조건 IN()

    테이블과 결과

    Input: 
Person table:
+----+----------+--------------+
| id | name     | phone_number |
+----+----------+--------------+
| 3  | Jonathan | 051-1234567  |
| 12 | Elvis    | 051-7654321  |
| 1  | Moncef   | 212-1234567  |
| 2  | Maroua   | 212-6523651  |
| 7  | Meir     | 972-1234567  |
| 9  | Rachel   | 972-0011100  |
+----+----------+--------------+
Country table:
+----------+--------------+
| name     | country_code |
+----------+--------------+
| Peru     | 051          |
| Israel   | 972          |
| Morocco  | 212          |
| Germany  | 049          |
| Ethiopia | 251 …
    read more

  6. [MySQL] 중복제거 카운트

    테이블과 결과

    Input: 
Activity table:
+---------+------------+---------------+---------------+
| user_id | session_id | activity_date | activity_type |
+---------+------------+---------------+---------------+
| 1       | 1          | 2019-07-20    | open_session  |
| 1       | 1          | 2019-07-20    | scroll_down   |
| 1       | 1          | 2019-07-20    | end_session   |
| 2       | 4          | 2019-07-20    | open_session  |
| 2       | 4          | 2019-07-21    | send_message  |
| 2       | 4          | 2019-07-21    | end_session   |
| 3 …
    read more

  7. [MySQL] 컬럼 한 줄 밀어서 JOIN

    테이블

    Weather

    id recordDate temperature
    1 2015-01-01 10
    2 2015-01-02 25
    3 2015-01-03 20
    4 2015-01-04 30

    문제

    • 전날 보다 온도가 오른 튜플만 뽑으시오

    답

    SELECT w2.id
FROM Weather w1, Weather w2
WHERE w1.recordDate = DATE_SUB …
    read more

  8. [MySQL] N번 째로 높은 값

    테이블과 결과

    Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
Output: 
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

    문제

    • Write an SQL query to report the second highest salary from the Employee table. If there is no second highest salary, the query should report null.

    답

    SELECT …
    read more

  9. [MySQL] N번 째로 높은 값 FUNCTION

    테이블과 결과

    Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
n = 2
Output: 
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

    문제

    • Write an SQL query to report the nth highest salary from the Employee table. If there is no nth highest salary, the query should report …
    read more

  10. [MySQL] NOT IN

    테이블

    Customers 테이블

    id name
    1 Joe
    2 Henry
    3 Sam
    4 Max

    Orders

    id customerId
    1 3
    2 1

    문제

    • 아직 주문을 하지 않은 고객만 뽑으시오

    답

    SELECT c.name AS Customers 
FROM Customers …
    read more

links

social