CONA BLOG

SUBQUERY(서브쿼리)

대표적으로는 SELECT, FROM, WHERE절에서 사용 가능

이번 포스팅에서는 FROM, WHERE에 대한 서브쿼리를 소개할 예정입니다.

table <crimes>

FROM절 서브쿼리

FROM절 서브쿼리 작성 예시) 가상의 테이블을 만든다고 생각

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

AVG() 사용할 때, NULL 카운트가 안 되는 점을 고려해서 SQL문을 작성해야 함

WHERE절 서브쿼리

서브쿼리의 결과가 딱 하나일 경우 (주로 MIN, MAX 사용)

SELECT *
FROM crimes
WHERE date = (SELECT MIN(date) FROM crimes)

서브쿼리의 결과가 여러 개인 경우

SELECT *
FROM crimes
WHERE date IN (SELECT DISTINCT date
		FROM crimes
                ORDER BY date DESC
                LIMIT 5
                )

HACKER RANK

Top Earners - https://www.hackerrank.com/challenges/earnings-of-employees/problem?h_r=internal-search

문제 풀이는 더보기 클릭

-- salary * months = earnings 값이 제일 큰 사람

SELECT months * salary AS earnings
	, count(employee_id)
FROM Employee
WHERE months * salary = (SELECT MAX(months * salary) FROM employee)
GROUP BY earnings

SELECT months * salary AS earnings
	,COUNT(*)
FROM Employee
GROUP BY earnings
HAVING earnings = (SELECT MAX(months * salary) FROM Employee)

LEETCODE

184. Department Highest Salary - https://leetcode.com/problems/department-highest-salary/

문제 풀이는 더보기 클릭

SELECT d.name AS department
    , e.name AS employee
    , e.salary
FROM employee AS e
    INNER JOIN (SELECT departmentid
			, MAX(salary) AS max_salary
                FROM employee
                GROUP BY departmentid
                ) AS dh ON e.departmentid = dh.departmentid
                	AND e.salary = dh.max_salary
    INNER JOIN department AS d ON d.id = e.departmentid

< 기초 SQL 시리즈 >

2020/07/14 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 기초 SQL - 1부

2020/07/14 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 기초 SQL - 2부

2020/07/14 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 기초 SQL - 3부 完

< 중급 SQL 시리즈 >

2020/07/15 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 중급 SQL - 1부

2020/07/15 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 중급 SQL - 2부

2020/07/16 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 중급 SQL - 3부

2020/07/16 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 중급 SQL - 4부 完

이 포스팅은 해당 강좌를 수강하며 작성하였습니다.

https://www.inflearn.com/course/%EB%8D%B0%EC%9D%B4%ED%84%B0-%EB%B6%84%EC%84%9D-%EA%B3%A0%EA%B8%89-SQL/dashboard

DML (Data Manipulation Language)

SELECT / INSERT, UPDATE, DELETE

1. SELECT

SELECT 조회하고 싶은 컬럼명 FROM 테이블명

2. INSERT

행(column) 단위 데이터 삽입

INSERT INTO 테이블명 (column 명) VALUES (각 column에 삽입할 value)

테이블 전체에 데이터 삽입

• DESC 테이블명
• 테이블의 각 column에 대한 데이터 타입을 확인할 수 있는 명령어

사용예시)

INSERT INTO Salary (Id, name, salary, date) VALUES ('1', 'A', '500', '2020-07-10')
INSERT INTO Salary (Id, name, salary, date) VALUES ('2', 'B', '900', '2020-07-11')

3. UPDATE 

UPDATE 테이블명 SET column = value

4. DELETE

DELETE FROM 테이블명

조건에 맞는 행(row) 삭제

DELETE FROM 테이블명 WHERE 조건식

LEEDCODE

문제 풀이는 더보기 클릭

627 Swap Salary - https://leetcode.com/problems/swap-salary/submissions/

UPDATE salary
SET sex = CASE
		WHEN sex = 'f' THEN 'm'
		WHEN sex = 'm' THEN 'f'
		ELSE 'Other'
	END

< 기초 SQL 시리즈 >

2020/07/14 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 기초 SQL - 1부

2020/07/14 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 기초 SQL - 2부

2020/07/14 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 기초 SQL - 3부 完

< 중급 SQL 시리즈 >

2020/07/15 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 중급 SQL - 1부

2020/07/15 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 중급 SQL - 2부

2020/07/16 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 중급 SQL - 3부

2020/07/16 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 중급 SQL - 4부 完

이 포스팅은 해당 강좌를 수강하며 작성하였습니다.

https://www.inflearn.com/course/%EB%8D%B0%EC%9D%B4%ED%84%B0-%EB%B6%84%EC%84%9D-%EA%B3%A0%EA%B8%89-SQL/dashboard

집합연산 UNION / UNION ALL

중복된 값을 제거하는 것이 UNION의 default

만약에 중복되는 값이 없는 테이블 연산이면 UNION ALL을 사용하는 것이 좋다

주의할 점 : A UNION B 를 수행할 때 A에는 ORDER BY 사용 못함

UNION  결과 => 중복 제거

UNION ALL 결과 => 중복 허용

JOIN 연산 사용 예시

-- product 테이블에서 Price가 5이하 또는 200 이상인 상품들만 출력하세요

SELECT *
FROM Products
WHERE price <= 5

UNION

SELECT *
FROM Products
WHERE price >= 200

FULL OUTER JOIN (MySQL에선 지원 안되지만 LEFT와 RIGHT를 사용해서 구현)

고객정보는 있지만 주문 정보는 없을 때

주문 정보는 있지만 고객 정보는 없을 때 를 모두 조회하고 싶다면 LEFT 조인과 RIGHT 조인을 UNION 해준다

SELECT *
FROM Customers
	LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
    
UNION
  
SELECT *
FROM Customers
	RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID

HACKER RANK

정답 확인은 더보기 클릭

1. Symmetric Pairs - https://www.hackerrank.com/challenges/symmetric-pairs/problem?h_r=internal-search

/*
20 20 (2번 나와야함 모두)
UNION
20 21
22 23
*/

-- 20 20 케이스 도출
SELECT x, y
FROM Functions
WHERE x = y
GROUP BY x, y
HAVING count(*) = 2

UNION

SELECT a.x
		, a.y
FROM Functions AS a
		INNER JOIN Functions AS b ON a.x = b.y AND a.y = b.x
WHERE a.x < a.y
ORDER BY x

< 기초 SQL 시리즈 >

2020/07/14 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 기초 SQL - 1부

2020/07/14 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 기초 SQL - 2부

2020/07/14 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 기초 SQL - 3부 完

< 중급 SQL 시리즈 >

2020/07/15 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 중급 SQL - 1부

2020/07/15 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 중급 SQL - 2부

2020/07/16 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 중급 SQL - 3부

2020/07/16 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 중급 SQL - 4부 完

이 포스팅은 해당 강좌를 수강하며 작성하였습니다.

https://www.inflearn.com/course/%EB%8D%B0%EC%9D%B4%ED%84%B0-%EB%B6%84%EC%84%9D-%EC%A4%91%EA%B8%89-SQL

JOIN

참고 사이트는 더보기 클릭

INNER JOIN

카티션 곱의 결과로 조인할 때 사용했던 칼럼이 중복 조회됨

즉, users.id = orders.userid 로 조인을 했다면 결과 값이 users 테이블의 id와 orders 테이블의 userid 두 개가 조회된다.

(Customers와 Orders 테이블을 예시로 들면 무조건 한 번 이상의 주문이 있어야 조회가 된다)

/*
Old version

SELECT *
FROM Users, Orders
WHERE Users.Id = Orders.userId
*/

SELECT *
FROM Users
	INNER JOIN Orders ON Users.Id = Orders.userId

-- 결과 값은 똑같지만 INNER JOIN 키워드 사용 권장

OUTER JOIN (LEFT, RIGHT)

< 테이블 예시 Users, Orders >

LEFT JOIN

- 한 번도 주문을 하지 않은 회원도 출력하고 싶을 경우에 사용

SELECT * 
FROM Users
	LEFT JOIN Orders ON Users.Id = Orders.userId

< left 조인 결과 테이블 >

한 번도 주문을 안한 회원을 조회하고 싶을 때

SELECT *
FROM Customers
	LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE OrderID IS NULL

RIGHT JOIN

대부분 LEFT 조인으로 해결함 (잘 쓰지 않음)

HACKER RANK / LEETCODE

해답은 더보기란을 클릭

1. Africa Cities - https://www.hackerrank.com/challenges/african-cities/problem?h_r=internal-search

SELECT City.Name
FROM City
	INNER JOIN Country ON City.Countrycode = Country.code
WHERE Continent = 'Africa'

2. Asian Population - https://www.hackerrank.com/challenges/asian-population/problem?h_r=internal-search

SELECT SUM(city.Population)
FROM Country
	INNER JOIN City ON Country.code = City.countrycode
WHERE Country.continent = 'Asia'

3. Average Population of Each Continent - https://www.hackerrank.com/challenges/average-population-of-each-continent/problem?h_r=internal-search

SELECT country.continent
	, FLOOR(AVG(city.population))
FROM Country
	INNER JOIN City ON Country.Code = City.Countrycode
GROUP BY country.continent

(LEETCODE) 183. Customers Who Never Order - https://leetcode.com/problems/customers-who-never-order/

 SELECT Customers.name AS "Customers"
 FROM Customers
 	LEFT JOIN Orders ON Customers.id = Orders.CustomerID
WHERE Orders.CustomerID IS NULL

SELF JOIN

주의할 점 : AS 즉, 별칭을 무조건 사용해서 같은 테이블을 구분해주어야 함.

자신의 테이블과 JOIN (상사를 찾을 때 씀) 

해답은 더보기를 클릭 (LEETCODE)

1. https://leetcode.com/problems/employees-earning-more-than-their-managers/

SELECT Employee.Name "Employee" 
        -- , Employee.Salary "employee_salary"
        -- , Manager.Name "manager_name"
        -- , Manager.Salary "manager_salary"
FROM Employee
    INNER JOIN Employee Manager ON Employee.managerid = Manager.id
WHERE Employee.Salary > Manager.Salary
    

2. https://leetcode.com/problems/rising-temperature/

SELECT today.id
FROM Weather AS today
	INNER JOIN Weather AS yesterday 
    	ON today.recorddate = DATE_ADD(yesterday.recorddate, INTERVAL 1 DAY)
WHERE today.Temperature > yesterday.Temperature

+) MySQL 시간 더하기, 빼기

DATE_ADD(기준 날짜, INTERVAL)

• SELECT DATE_ADD(NOW(), INTERVAL 1 SECOND)
• SELECT DATE_ADD(NOW(), INTERVAL 1 MINUTE)
• SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR)
• SELECT DATE_ADD(NOW(), INTERVAL 1 DAY)
• SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH)
• SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR)

DATE_SUB(기준 날짜, INTERVAL)

• SELECT DATE_SUB(NOW(), INTERVAL 1 SECOND)

< 기초 SQL 시리즈 >

2020/07/14 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 기초 SQL - 1부

2020/07/14 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 기초 SQL - 2부

2020/07/14 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 기초 SQL - 3부 完

< 중급 SQL 시리즈 >

2020/07/15 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 중급 SQL - 1부

2020/07/15 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 중급 SQL - 2부

2020/07/16 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 중급 SQL - 3부

2020/07/16 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 중급 SQL - 4부 完

이 포스팅은 해당 강좌를 수강하며 작성하였습니다.

https://www.inflearn.com/course/%EB%8D%B0%EC%9D%B4%ED%84%B0-%EB%B6%84%EC%84%9D-%EC%A4%91%EA%B8%89-SQL

기념 스크린샷

조건에 맞는 데이터 검색하기

1. 비교 연산자와 논리 연산자

비교 연산자 - 특정 칼럼이 특점 값을 가지는 데이터만 불러오기 위해서 사용

SELECT *
FROM Customers
WHERE Country = 'Germany'

숫자뿐만 아니라, 문자도 비교가 가능하다.

알파벳 "B" 이전에 시작하는 것만 조회하는 방법. 즉, 'A'로 시작하는 모든 데이터를 가져옴

SELECT *
FROM customers
WHERE customerName < "B"

논리 연산자

2. LIKE / IN / BETWEEN / IS NULL

LIKE

- country가 'Br'로 시작하는 row를 조회하고 싶은 경우

SELECT *
FROM customers
WHERE country LIKE 'Br%'

IN

- country가 프랑스 거나 독일인 사람을 조회

SELECT *
FROM customers
WHERE country IN ('Germany', 'France')

-- WHERE country = 'Germany' OR country = 'France' (IN과 같음)

BETWEEN

- customerID가 3과 5 사이에 들어가는 row 조회

SELECT *
FROM customers
WHERE customerID BETWEEN 3 AND 5

IS NULL

- 테이블 내에 데이터가 비어있는 부분을 검색하기 위한 예약어

SELECT *
FROM customers
WHERE customerID IS NULL

-- NULL, NaN (Not a Number) : 숫자도 아니고, 문자도 아니고 비어있다

3. LIKE 심화

% - 어떤 것이 와도 상관이 없다. 와일드카드(wildcard)

_ - 한 글자 와일드카드

일단, r 이 포함된 것은 모두 조회

SELECT * 
FROM Customers 
WHERE country LIKE '%r'

찾고 싶은 키워드가 명확하다면 LIKE 보다는 = 비교 연산자 사용 (속도가 빠른 장점)

SELECT *
FROM customers
WHERE country = 'Brazil'

글자 수 제한해서 조회 (B뒤에 5글자)

SELECT *
FROM customers
WHERE country LIKE 'B_____'

이스케이프(\) - 특수문자 조회하기 (두 자릿수 퍼센트 디스카운트)

SELECT *
FROM customers
WHERE discount LIKE '__\%'

+) DISTINCT

해당 칼럼의 중복 값을 제거하여 조회 (아래 예시에서는 중복되는 city 값은 딱 한 개만 출력된다.)

SELECT DISTINCT city
FROM station

HACKER RANK 퀴즈

1. Revising the Select Query 1

SELECT *
FROM city
WHERE population > 100000 AND countrycode = 'USA' 

2. Select By ID

SELECT *
FROM city
WHERE id = 1661

3. Weater Observation Station 6

SELECT city
FROM station
WHERE city LIKE 'a%' 
OR city LIKE 'e%' 
OR city LIKE 'i%' 
OR city LIKE 'o%' 
OR city LIKE 'u%'

4. Weather Observation Station 12

SELECT DISTINCT city
FROM station
where city NOT LIKE 'a%'
AND city NOT LIKE 'e%'
AND city NOT LIKE 'i%'
AND city NOT LIKE 'o%'
AND city NOT LIKE 'u%'
AND city NOT LIKE '%a'
AND city NOT LIKE '%e'
AND city NOT LIKE '%i'
AND city NOT LIKE '%o'
AND city NOT LIKE '%u'

< 기초 SQL 시리즈 >

2020/07/14 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 기초 SQL - 1부

2020/07/14 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 기초 SQL - 2부

2020/07/14 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 기초 SQL - 3부 完

이 포스팅은 해당 강좌를 수강하며 작성하였습니다.

https://www.inflearn.com/course/%EB%B0%B1%EB%AC%B8%EC%9D%B4%EB%B6%88%EC%97%AC%EC%9D%BC%ED%83%80-%EB%8D%B0%EC%9D%B4%ED%84%B0-%EB%B6%84%EC%84%9D-%EA%B8%B0%EC%B4%88-SQL