CONA BLOG

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

조건문 CASE

예시 1) 카테고리 id 값이 1이면 음료, 2면 조미료, 그 외의 카테고리는 기타로 조회

SELECT CASE
		WHEN categoryid = 1 THEN '음료'
        	WHEN categoryid = 2 THEN '조미료'
        	ELSE '기타'
       END
FROM Products

예시 2) 카테고리 별 평균 가격 조회

SELECT CASE
		WHEN categoryid = 1 THEN '음료'
       		WHEN categoryid = 2 THEN '소스'
            	ELSE '이외'
       END AS "new category"
       , AVG(Price)
FROM Products
GROUP BY "new category"

CASE를 활용한 테이블 피봇

예시 1) CategoryId = 1인 상품의 가격에 대해서 평균 값을 알고 싶은 경우

SELECT AVG(CASE WHEN categoryId = 1 THEN price ELSE NULL END) "category1 price"
FROM Products

예시 2) 카테고리 별로 평균 가격을 조회

SELECT AVG(CASE WHEN categoryId = 1 THEN price ELSE NULL END) "category1 price" 
	,AVG(CASE WHEN categoryId = 2 THEN price ELSE NULL END) "category2 price" 
        ,AVG(CASE WHEN categoryId = 3 THEN price ELSE NULL END) "category3 price" 
FROM Products

이렇게 평균값을 조회할 수 있는데, 결과값이 가로로 펼쳐져 있는 것을 pivoting 이라 한다.

예시 이미지는 더보기에 링크 참고

HACKER RANK

1. Type of Triangle

풀이과정은 아래 더보기 클릭

더보기

제일 쉬운 정사각형을 잡아내는 것 부터 시작하면 수월함

 

SELECT CASE
			WHEN a = b AND b = c THEN 'Equilateral'
			ELSE 'Other'
			END, a, b, c
FROM Triangles

 

이 질의문으로 먼저 정사각형이 잘 조회되고 있는지 3 변의 길이와 비교하며 확인

 

 

 

이등변 삼각형은 정삼각형과 겹치는 조건이 있기 때문에 Not A Triangle을 먼저 작성해주는 것이 좋음

즉, a = b AND b = c THEN 'Equilateral' 이라고 했을 때, a = b OR b = c OR a = c THEN 'Isosceles' 를 이등변 삼각형의 조건으로 넘겨주면 이등변 삼각형도 정삼각형에 포함된다.

 

그걸 방지하기 위해서 순서를 Equilateral을 먼저 조회로 필터링을 마치고, 그 다음에 Isosceles를 조회해주어야 한다.

CASE 절의 순서도 매우 중요!!

 

SELECT CASE
		WHEN a = b AND b = c THEN 'Equilateral'
            	WHEN a = b OR b = c OR a = c THEN 'Isosceles'
                ELSE 'Other'
       END, a, b, c
FROM Triangles

 

결과 값을 확인해보니 테이블에 20 20 40 이 이등변 삼각형에 포함된 것을 확인할 수 있다.

수식에 의하면 20 + 20 > 40 이 성립하지 않기 때문에 Not A Triangle에 포함되어야 하는 삼각형임을 알 수 있다.

 

이런 현상을 방지하기 위해서는 위에서 언급했듯이 CASE 문의 순서를 조절하여 삼각형이 아닌 것 부터 걸러주어야 한다.

 

<답>

 

SELECT CASE	
	        WHEN a = b AND b = c THEN 'Equilateral'
	        WHEN a + b <= c OR b + c <= a OR a + c <= b THEN 'Not A Triangle'
        	WHEN a = b OR b = c OR a = c THEN 'Isosceles'
        	ELSE 'Scalene'
       END
FROM Triangles

 

LEETCODE

1179. Reformat Department Table

문제 주소 - https://leetcode.com/problems/reformat-department-table/

풀이는 더보기 클릭

SELECT  id
        , SUM(CASE WHEN month = 'Jan' THEN revenue ELSE NULL END) Jan_Revenue
        , SUM(CASE WHEN month = 'Feb' THEN revenue ELSE NULL END) Feb_Revenue
        , SUM(CASE WHEN month = 'Mar' THEN revenue ELSE NULL END) Mar_Revenue
        , SUM(CASE WHEN month = 'Apr' THEN revenue ELSE NULL END) Apr_Revenue
        , SUM(CASE WHEN month = 'May' THEN revenue ELSE NULL END) May_Revenue
        , SUM(CASE WHEN month = 'Jun' THEN revenue ELSE NULL END) Jun_Revenue
        , SUM(CASE WHEN month = 'Jul' THEN revenue ELSE NULL END) Jul_Revenue
        , SUM(CASE WHEN month = 'Aug' THEN revenue ELSE NULL END) Aug_Revenue
        , SUM(CASE WHEN month = 'Sep' THEN revenue ELSE NULL END) Sep_Revenue
        , SUM(CASE WHEN month = 'Oct' THEN revenue ELSE NULL END) Oct_Revenue
        , SUM(CASE WHEN month = 'Nov' THEN revenue ELSE NULL END) Nov_Revenue
        , SUM(CASE WHEN month = 'Dec' THEN revenue ELSE NULL END) Dec_Revenue
FROM Department
GROUP BY id

< 기초 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

강의를 듣기 전 준비해야 할 링크

LeetCode - https://leetcode.com/contest/

COUNT / SUM / AVG / MIN, MAX

COUNT, AVG에서 NULL은 집계되지 않는다.

SELECT COUNT(*)
FROM sample		//5

SELECT COUNT(Name)
FROM sample		//4

SELECT COUNT(DISTINCT Name)
FROM sample		//3

Products 테이블에서 모든 제품 가격의 합 구하기

SELECT SUM(Price)
FROM Products

Products 테이블에서 제품 가격의 평균 구하기

SELECT ROUNT(AVG(Price))
FROM Products

+) 평균을 구할 때 NULL을 0으로 변환하여 계산하는 방법

SELECT SUM(Visits) / COUNT(*)
FROM sample

Products 테이블에서 최저 가격과 최고 가격 조회

SELECT MIN(Price) 최저가격
	, MAX(Price) 최고가격
FROM Products

GROUP BY & HAVING

어떤 공급자가 보통 얼마의 상품을 판매하는지 TOP3 조회하기

SELECT SupplierID 공급자
	, ROUND(AVG(Price)) "평균 가격"
FROM Products
GROUP BY SupplierID
ORDER BY "평균 가격"
LIMIT 3

GROUP BY에서  WHERE와 HAVING의 차이

예를 들어서  WHERE 절로 가격이 100불 이상인 것을 조회한다고 했을 때, 명령어 진행 순서상

1. 가격이 100불 이상인 것 조회
2. GROUP BY 명령어 실행

SELECT SupplierID
	, CategoryID
	, AVG(Price)
FROM Products
WHERE Price >= 100
GROUP BY SupplierID, CategoryId

HAVING 절을 사용한다면

1. GROUP BY 명령어 실행 (그룹화)
2. 그룹화 된 결과에서 가격이 100불 이상인 것 조회

SELECT SupplierId
	, CategoryId
	, AVG(Price)
FROM Products
GROUP BY SupplierId, CategoryId
HAVING Price >= 100

HACKER RANK

1.Revising Aggregations - Averages

SELECT AVG(Population)
FROM City
GROUP BY District
HAVING District = "California"

2. Revising Aggregations - The Sum Function

SELECT SUM(Population)
FROM City
WHERE District = "California"

3. Revising Aggregations - The Count Function

SELECT COUNT(District)
FROM City
WHERE Population > 100000

4. Average Population

SELECT ROUND(AVG(Population))
FROM City

5. Population Density Difference

SELECT (MAX(Population)-MIN(Population))
FROM City

6. Weather Observation Station 4

SELECT (COUNT(*) - COUNT(DISTINCT City))
FROM Station

7. Top Earners

SELECT (salary * months)
	, COUNT(*)
FROM Employee
GROUP BY (salary * months)
ORDER BY (salary * months) DESC
LIMIT 1

< 기초 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

기념 스크린샷

데이터 순서 정렬하기

ORDER BY

ORDER BY의 사용 순서는 SELECT -> FROM -> WHERE -> ORDER BY으로 제일 마지막에 위치

mysql은 기본적으로 오름차순

SELECT *
FROM customers
ORDER BY customerid

customerid 내림차순(큰 것 부터)

SELECT *
FROM customers
ORDER BY customerid DESC

정렬하기 응용

가격이 20$ 이상이고, 가격이 높은 순으로 물건을 조회해라 (10개)

SELECT *
FROM products
WHERE price >= 20
ORDER BY price DESC
LIMIT 10

+) MySQL 소수점 처리

CEIL() - 올림

SELECT CEIL(5.5) => 6

FLOOR() - 내림

SELECT FLOOR(5.5) => 5

ROUND() - 반올림

ROUND(5.556901, 4) => 5.5569

+) MySQL 문자열 자르기

LEFT(컬럼명, 문자열 길이)

SELECT LEFT("20140323", 4) => 2014

RIGHT(컬럼명, 문자열 길이)

SELECT RIGHT("20140323", 4) => 0323

SUBSTRING(컬럼명, 시작 위치, 길이) = SUBSTR()

SUBSTR("20140323", 1, 4) => 2014

SUBSTR("20140323", 5) => 0323

HACKER RANK

1. Employee Names

SELECT name
FROM employee
ORDER BY name

2. Employee Salaries

SELECT name
FROM employee
WHERE salary >= 2000 AND months < 10
ORDER BY employee_id

3. Higher Than 75 Marks

SELECT name
FROM students
WHERE marks > 75
ORDER BY SUBSTR(name, -3), id

4. Weather Observation Station 15 

SELECT ROUND(long_w, 4)
FROM station
WHERE lat_n < 137.2345
ORDER BY lat_n DESC
LIMIT 1

< 기초 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

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

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