UPDATE Salary Set Salary = Salary + 100 WHERE Id =2;
: update에도 where로 조건절을 해줄수 있음
DELETE
DELETE FROM Salary;
DELETE FROM Salary WHERE Id = 2 ;
: DELETE에도 where로 조건절을 해줄수 있음
CASE WHEN a THEN B ;
매일매일 범죄가 얼마나 일어나는지 구하자.
SELECT daily_stats.week
, AVG(daily_stats.incidents_daily)
FROM (
SELECT week
,date
,COUNT(incident_id) AS incidents_daily
FROM crimes
GROUP BY week, date
) daily_stats #서브쿼리 테이블 이름 만들어주기?
GROUP BY daily_stats.week
WHERE 절 서브쿼리
최초 발생일 확인
: SELECT FROM crimes WHRERE date = (SELECT MIN(date) FROM crimes)
최근 5일 동안 발생했던 레코드를 확인
SELECT FROM crimes WHERE date IN (SELECT DATE FROM crimes ORDER BY date DESC LIMIT 5)
인프런 SQL 고급 강의 듣고 - LeetCode 문제풀이 627, 196번
Input:
Salary table:
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5500 |
| 4 | D | f | 500 |
+----+------+-----+--------+
Output:
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
| 1 | A | f | 2500 |
| 2 | B | m | 1500 |
| 3 | C | f | 5500 |
| 4 | D | m | 500 |
+----+------+-----+--------+
Explanation:
(1, A) and (3, C) were changed from 'm' to 'f'.
(2, B) and (4, D) were changed from 'f' to 'm'.
나의 답안 : UPDATE Salary
SET sex = CASE WHEN sex= 'f' THEN 'm'
ELSE 'f' END
Input:
Person table:
+----+------------------+
| id | email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
Output:
+----+------------------+
| id | email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+
Explanation: john@example.com is repeated two times. We keep the row with the smallest Id = 1.
나의 답안 :
(1)
DELETE FROM Person WHERE id NOT IN(
SELECT sub.min_id FROM(
SELECT email, MIN(id) AS min_id
FROM Person
GROUP BY email
) sub)
UPDATE
UPDATE Salary Set Salary = Salary + 100 WHERE Id =2; : update에도 where로 조건절을 해줄수 있음
DELETE
DELETE FROM Salary; DELETE FROM Salary WHERE Id = 2 ; : DELETE에도 where로 조건절을 해줄수 있음 CASE WHEN a THEN B ;
매일매일 범죄가 얼마나 일어나는지 구하자.
SELECT daily_stats.week , AVG(daily_stats.incidents_daily) FROM ( SELECT week ,date ,COUNT(incident_id) AS incidents_daily FROM crimes GROUP BY week, date ) daily_stats #서브쿼리 테이블 이름 만들어주기? GROUP BY daily_stats.week
WHERE 절 서브쿼리
최초 발생일 확인 : SELECT FROM crimes WHRERE date = (SELECT MIN(date) FROM crimes) 최근 5일 동안 발생했던 레코드를 확인 SELECT FROM crimes WHERE date IN (SELECT DATE FROM crimes ORDER BY date DESC LIMIT 5) 인프런 SQL 고급 강의 듣고 - LeetCode 문제풀이 627, 196번
Input: Salary table: +----+------+-----+--------+ | id | name | sex | salary | +----+------+-----+--------+ | 1 | A | m | 2500 | | 2 | B | f | 1500 | | 3 | C | m | 5500 | | 4 | D | f | 500 | +----+------+-----+--------+ Output: +----+------+-----+--------+ | id | name | sex | salary | +----+------+-----+--------+ | 1 | A | f | 2500 | | 2 | B | m | 1500 | | 3 | C | f | 5500 | | 4 | D | m | 500 | +----+------+-----+--------+ Explanation: (1, A) and (3, C) were changed from 'm' to 'f'. (2, B) and (4, D) were changed from 'f' to 'm'.
나의 답안 : UPDATE Salary SET sex = CASE WHEN sex= 'f' THEN 'm' ELSE 'f' END
Input: Person table: +----+------------------+ | id | email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | | 3 | john@example.com | +----+------------------+ Output: +----+------------------+ | id | email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | +----+------------------+ Explanation: john@example.com is repeated two times. We keep the row with the smallest Id = 1.
나의 답안 : (1) DELETE FROM Person WHERE id NOT IN( SELECT sub.min_id FROM( SELECT email, MIN(id) AS min_id FROM Person GROUP BY email ) sub)