데이터 놀이터

1. 문제 Generate the following two result sets: Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses). For example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S). Query the number of ocurrences of each occupation in OCCUPATIONS. Sort the occurrences in ..

1. 문제 Table: Trips +-------------+----------+ | Column Name | Type | +-------------+----------+ | id | int | | client_id | int | | driver_id | int | | city_id | int | | status | enum | | request_at | date | +-------------+----------+ id is the primary key (column with unique values) for this table. The table holds all taxi trips. Each trip has a unique id, while client_id and driver_id are forei..

1. 문제 Table: Stadium +---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | visit_date | date | | people | int | +---------------+---------+ visit_date is the column with unique values for this table. Each row of this table contains the visit date and visit id to the stadium with the number of people during the visit. As the id increases, the date increases..

1. 문제 Table: Activity +--------------+---------+ | Column Name | Type | +--------------+---------+ | player_id | int | | device_id | int | | event_date | date | | games_played | int | +--------------+---------+ (player_id, event_date) is the primary key (combination of columns with unique values) of this table. This table shows the activity of players of some games. Each row is a record of a pla..

mysql에서는 REGEXP와 REGEXP_SUBSTR을 활용하여 정규 표현식을 사용할 수 있다. 1. REGEXP WHERE 절에서 REGEXP 뒤에 원하는 정규식을 쓰고 활용할 수 있다. -- seoul로 시작하는 도시명 WHERE city REGEXP '^seoul' -- seoul로 시작하지 않는 도시명 WHERE city NOT REGEXP '^seoul' 2. REGEXP_SUBSTR 문자열 중에서 원하는 부분을 정규식으로 뽑아낼 수 있는 문법이다. REGEXP_SUBSTR(문자열, 정규식)으로 문법이 이루어져 있으며 해당 문자열에서 정규식에 맞는 부분만 추출할 수 있다. REPLACE를 활용하면 원하는 부분만 깔끔하게 찾아낼 수 있다. -- city=~이 포함된 문자열에서 seoul만 뽑아내..

1. 문제 Table: RequestAccepted +----------------+---------+ | Column Name | Type | +----------------+---------+ | requester_id | int | | accepter_id | int | | accept_date | date | +----------------+---------+ (requester_id, accepter_id) is the primary key (combination of columns with unique values) for this table. This table contains the ID of the user who sent the request, the ID of the user who ..

SELECT * FROM tips WHERE (day, total_bill) in (SELECT day , MAX(total_bill) FROM tips GROUP BY day) 1. 날짜 포멧에 BETWEEN을 활용할 경우 포멧이 시, 분, 초까지 표현하는 데이터라면 년,월,일로만 작성할 경우에 마지막 날짜 정보가 포함되지 않고 출력된다. 따라서, 시, 분, 초까지로 범위를 모두 써주어야 한다. 예시 -- 틀린 예시 (모든 범위가 포함되지 않음) WHERE order_delivered_carrier_date BETWEEN '2017-01-01' AND '2017-01-31' -- 올바른 예시 WHERE order_delivered_carrier_date BETWEEN '2017-01-01 00:00:0..

1. Window 함수란? Window 함수란, 행과 관련된 행 간의 연산을 수행하는 함수이다. GROUP BY를 이용한 집계함수와 기능적으로는 유사하나, 집계 기준 별로 결과물이 출력되지 않고 각각의 row 별로 결과물이 출력된다는 점에서 차이가 있다. -- GROUP BY를 활용한 집계함수 mysql> SELECT country, SUM(profit) AS country_profit FROM sales GROUP BY country ORDER BY country; +---------+----------------+ | country | country_profit | +---------+----------------+ | Finland | 1610 | | India | 1350 | | USA | 457..

1. 문제 Table: Logs +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | num | varchar | +-------------+---------+ In SQL, id is the primary key for this table. id is an autoincrement column. Find all numbers that appear at least three times consecutively. Return the result table in any order. The result format is in the following example. Example 1: Input: Log..

1. 문제 Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark. Ketty doesn't want the NAMES of those students who received a grade lower than 8. The report must be in descending order by grade -- i.e. higher grades are entered first. If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alph..