PostgreSQL Bootcamp 강의를 Udemy에서 수강하고 정리하느라 블로그 작성이 힘들었다.
그 동안 메모장에 작성해 정리했던 글을 블로그에 올려보고자 한다.
SQL Bootcamp Course With PostgreSQL
SELECT * FROM table; → 테이블 전체 선택
DISTINCT - 고유하거나 중복되지 않는 값만 조회하고 싶을 때 사용
ex) SELECT DISTINCT column FROM table
ex2) SELECT DISTINCT release_year FROM film; - 이 film 테이블에 고유한 출시년도는 몇 개인가?에 빠르게 대답 가능, 괄호 넣는 것도 가능
COUNT - 특정 쿼리 조건에 맞는 입력 행의 개수를 구함, 특정 열을 지정해 COUNT 적용도 가능하고 COUNT(*)라고 입력해도 됨
ex) SELECT COUNT(DISTINCT name) FROM table;
ex2) SELECT DISTINCT amount FROM payment; -> 고유한 결제 금액으로는 어떤 것이 있는가(중복 제외)
ex3) SELECT COUNT(DISTINCT amount) FROM payment;
SELECT WHERE문(Part 1,2)
WHERE - 열에 조건을 지정해 그에 맞는 행이 반복되도록 함, WHERE절은 SELECT문의 FROM절 바로 뒤에 옴
- Comparison Operators
= Equal
Greater than
< Less than
= Gretaer than or equal to
<= Less than or equal to
<> or != Not equal to
ex) SELECT name, choice FROM table WHERE name="David" AND choice="Red" 두 조건 모두 참인 행 하나가 반환됨
ex2) Nancy Thomas라는 고객의 이메일 주소는 무엇인가?
SELECT email FROM customer
WHERE first_name='Nancy' AND last_name='Thomas';
ex3) 한 고객이 영화 "Outlaw Hanky"의 내용을 궁금해함 - 설명 가능함?
SELECT description FROM film
WHERE title='Outlaw Hanky';
ex4) 한 고객이 영화 반납을 연체해 259 Ipoh Drive 주소로 우편을 보냈고 이 주소에 살고 있는 고객의 전화번호를 찾을 수 있는가?
SELECT phone FROM address
WHERE address='259 Ipoh Drive';
ORDER BY
결과를 특정 순서로 표시하고 싶을때 사용
기본구문 : SELECT column_1, column_2 FROM table ORDER BY column_1 ASC/DESC
ASC : 오름차순, DESC : 내림차순
ex) SELECT company,name,sales FROM table ORDER BY company,sales
ex2) SELECT first_name,last_name FROM customer
ORDER BY store_id DESC,first_name ASC
LIMIT
쿼리에서 반환되는 행의 개수를 제한하는 함수, 테이블의 모든 행을 반환하는 것이 아니라 상위 몇 개의 행만 표시해 테이블 레이아웃을 파악하려고 할 때 유용함, ORDER BY문과 사용시 유용
쿼리 요청의 가장 아래로 내려가며 가장 마지막에 실행되는 명령
SELECT * FROM payment
ORDER BY payment_date DESC
LIMIT 5;
payment 테이블에서 가장 최근의 행 또는 결제 5개는 무엇인가에 대한 답
SELECT * FROM payment
WHERE amount != 0.00
ORDER BY payment_date DESC
LIMIT 5;
결제액이 0이 아닌 가장 최근 결제 5건의 모든 정보를 조회할 수 있음
ex) 처음으로 결제를 생성한 고객 10명의 고객 ID는?
SELECT customer_id FROM payment
ORDER BY payment_date ASC
LIMIT 10
ex2) 상영시간이 가장 짧은 영화 5편의 제목은?
SELECT title,length FROM film
ORDER BY length ASC
LIMIT 5;
length도 SQL 키워드이기 때문에 사용시 주의해야됨
예시의 경우 FROM이 대문자이기 때문에 length는 SQL 명령이 아니라 열 이름으로 인식됨
ex3) 고객이 볼 수 있는 상영시간 50분 이하의 영화는 몇 편인가?(ORDER BY도 필요X)
예상 결과는 37편, SELECT문으로 정보를 불러올 수 있어야됨
SELECT COUNT(title) FROM film
WHERE length <= 50;
-> ()안에 * 입력도 가능
BETWEEN
값을 값 범위와 비교할 때 사용함, 양쪽의 엔드포인트를 모두 포함함
datetime은 0:00부터 시작하는 것을 고려해야 함
SELECT * FROM payment
WHERE amount BETWEEN 8 AND 9;
개수는 COUNT 추가
끝에 있는 datetime을 수정하면 시간이 0:00으로 당겨진다. 14일 24:00(X) -> 이 점 때문에 BETWEEN 문에서 주의해야함
IN
ex) SELECT color FROM table WHERE color IN('red','blue')
ex2) SELECT * FROM customer
WHERE first_name IN ('John','Jake','Julie')
옵션을 구분하기 위해 괄호,쉼표를 사용하고 옵션들은 해당 열의 일반 구문을 충족해야 한다
숫자 목록인 경우 숫자, 문자열 목록인 경우 작은 따옴표를 사용해 문자열을 입력해야 함
정반대의 결과를 바란다면 NOT IN을 입력함
LIKE, ILIKE
단순 키워드가 아닌 문자열 데이터를 이용한 패턴 매칭 이용에 관한 내용
LIKE 연산자를 사용하면 문자열 데이터에 대한 패턴 매칭을 수행할 수 있음
이때 와일드카드 문자를 이용함
퍼센트 기호는 글자나 문자의 순서에 관계 없이 사용되는 와일드카드 문자임
LIKE는 대소문자를 구분O, ILIKE는 대소문자 구별X, 언제든 1개 이상의 와일드카드 문자 추가 가능
ex) SELECT COUNT(*) FROM customer
WHERE first_name LIKE 'J%'
ex2) SELECT COUNT(*) FROM customer
WHERE first_name LIKE 'J%' AND last_name LIKE 'S%'
ex3) SELECT * FROM customer
WHERE first_name LIKE '%er' -> 이름 어딘가에 er이 있는 사람을 찾는 것
ex4) SELECT * FROM customer
WHERE firstname LIKE '_her%' -> 밑줄에는 문자 하나만 허용됨
ex5) SELECT * FROM customer
WHERE first_name NOT LIKE '_her%' -> NOT 추가 가능한데 반대 결과를 얻는 연산자로 이것 빼고 전부를 검색하라고 할 수 있음
퍼센트 기호와 밑줄을 사용한다는 것을 기억해라, 퍼센트 기호는 긴 문자에 사용하고 밑줄은 문자 하나로 교체된다.
GROUP BY
데이터가 카테고리별로 어떻게 분포되어 있는지 파악하기 위해 데이터를 집계하고 함수를 적용하는 SQL문임
집계함수
평균값 반환 - AVG
반환되는 값이나 행의 개수를 보여줌 - COUNT
최댓값,최솟값 반환 - MAX, MIN
집계함수는 SELECT절, HAVING절에서만 호출
COUNT는 행의 개수만 반환한다는 걸 유의해야함
집계함수는 하나의 열에 대해서만 집계 함수를 호출할 수 있음
다른 열을 호출하기 위해선 GROUP BY 문을 이용해야함, 일정 카테고리 별로 열을 집계함
GROUP BY는 FROM문 바로 뒤 WHERE문 바로 뒤에 와야 한다.
실제 SELECT문에서는 열에 집계 함수가 적용되거나 열 자체가 GROUP BY 호출 대상이다. -> 여기서 실수 가장 많이 함
GROUP BY문을 사용하고 특정 열만을 선택하는 경우 GROUP BY문에 반드시 그 열을 포함시켜야함
ex) SELECT company,division,SUM(sales)라는 쿼리가 있는데 테이블에 열이 3개라고 가정
company, division, sales 열 존재
company와 division열을 선택했다면 여러 열을 묶을 수 있으며 묶는 순서만 정하면 됨
sales는 GROUP BY문에 없고 sales에 집계 함수가 적용되어 있어야 한다는 의미
- 주의할 것은 WHERE문에는 집계 함수를 입력해서는 안된다
따라서 sales에 WHERE문을 이용해 필터링하려고 해서는 안됨, 추후 HAVING문을 이용해 결과를 필터링하는 방법을 배울 것
SELECT company,division,SUM(sales) FROM finance_table WHERE divisions IN('marketing','transport')라고 입력해
먼저 division을 필터링한 후 GROUP BY company,division을 실행한다.
company,division은 GROUP BY에 입력하지만 SUM(sales)는 집계 함수가 적용되기 때문에 제외된다.
ORDER BY SUM(sales)라고 입력해야함 - 판매액 합계 순으로 정렬
LIMIT 5 같은 것을 추가해 판매액 상위 5개 또는 하위 5개 같은 것을 표시할 수 있음
ORDER BY - 같은 열을 기준으로 정렬 할 수 있음
ex)SELECT DATE(payment_date),SUM(amount) FROM payment
GROUP BY DATE(payment_date)
ORDER BY SUM(amount) DESC
DATE - 타임스탬프 정보 중 날짜 부분만 추출하는 것
GROUP BY 연습문제
staff id가 1과 2인 두 명의 직원이 있다. 가장 많은 결제를 처리한 고객에게 보너스를 주려고 한다.
대부분은 처리된 결제건수를 말함(결제 총액X)
각 직원이 처리한 결제건수는 몇 건이며 누가 보너슿를 받게 되나?
SELECT staff_id,COUNT(*) FROM payment
GROUP BY staff_id
본사에서 교체 비용과 영화의 MPAA 등급 사이의 관계에 관한 연구를 수행하고 있다.
여기서 등급은 G,PG,R 등이다. MPAA 등급별 평균 교체 비용은 얼마인가?
정확한 결과를 보기 위해 AVG열을 확장해야 할 수 있다.
SELECT rating,ROUND(AVG(replacement_cost)) FROM film
GROUP BY rating
총 지출액 또는 총 사용을 기준으로 상위 고객 5명의 고객 ID는 무엇인가?
금액은 실제 달러 금액이다.
SELECT customer_id,SUM(amount) FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC
LIMIT 5
HAVING
집계가 이미 수행된 '이후에' 자료를 필터링하기 때문에 GROUP BY 호출 뒤에 위치함
ex) SELECT company, SUM(sales) FROM finance_table GROUP BY company
SELECT company,SUM(sales) FROM finance_table WHERE company != 'Google' GROUP BY company HAVING SUM(sales) > 1000
SELECT customer_id,SUM(amount) FROM payment
WHERE customer_id NOT IN (184,87,477)
GROUP BY customer_id
SUM(amount)는 WHERE문으로 필터링할 수 x
SUM(amount)는 GROUP BY를 호출하기 전까지 실행되지 않기 때문
SELECT customer_id,SUM(amount) FROM payment
GROUP BY customer_id
HAVING SUM(amount) > 100
SELECT store_id,COUNT(customer_id) FROM CUSTOMER
GROUP BY store_id
HAVING COUNT(customer_id) > 300
HAVING 연습문제
플래티넘 서비스를 시작하려고 한다.
결제 거래 건수가 40건 이상인 고객에게 플래티넘 지위를 할당하려고 한다.
플래티넘 자격이 있는 고객 ID는 누구인가?
SELECT customer_id, COUNT() FROM payment
GROUP BY customer_id
HAVING COUNT() >= 40;
직원 ID 2와의 결제 거래에서 100달러를 초과하여 사용한 고객의 고객 ID는 무엇인가?
-> 결제 거래에서 100달러보다 많이 쓴 사람의 고객 ID를 조회하되 직원 ID가 2인 직원과의 결제 거래만 골라내는 것
SELECT customer_id, SUM(amount) FROM payment
WHERE staff_id=2
GROUP BY customer_id
HAVING SUM(amount) > 100
1차 평가문제
- ID가 2인 직원에게서 최소 110달러를 쓴 고객의 ID를 찾으십시오.
답은 187과 148번 고객입니다.
SELECT customer_id,SUM(amount) FROM payment
WHERE staff_id=2
GROUP BY customer_id
HAVING SUM(amount) >= 110
- J로 시작하는 영화는 몇 편입니까?
답은 20입니다.
SELECT COUNT(*) FROM film
WHERE title LIKE 'J%'
- 이름이 ‘E’로 시작하는 동시에 주소 ID가 500 미만인 고객 중, ID 번호가 가장 높은 고객은 누구입니까?
답은 Eddie Tomlin입니다.
SELECT first_name,last_name FROM customer
WHERE first_name LIKE 'E%'
AND address_id <500
ORDER BY customer_id DESC
LIMIT 1;
JOIN
가장 중심적 기능은 여러 테이블의 정보를 결합하는 것
AS절로 먼저 시작하는데 별칭을 만드는데 매우 유용함 -> 열 이름을 원하는대로 바꿀 수 있음
JOIN의 활용으로는 예를 들어 INNER JOIN, OUTER JOIN, FULL JOIN, FULL OUTER JOIN, UNIONS 등이 있음
AS
열이나 결과에 별칭을 부여하는데 별칭은 대체 이름을 의미함
테이블에서 열을 선택하는 대신 SELECT column AS를 입력한 후 Data Output에 표시할 새 이름을 결정함, 열에 적용되는 함수에도 사용가능함
ex) SELECT customer_id FROM payment -> 열 이름 customer_id가 Data Output에 표시됨
ex2) SELECT amount AS rental_price FROM payment를 입력하면 Data Output에 amount열이 보고되지 않고 우리가 결정한 별칭, 다른 이름인 rental_price라고 되어 있음
ex3) SELECT SUM(amount) AS net_revenue FROM payment를 입력하면 총계라고만 출력되는 대신 payment 테이블의 순매출액 총계라는 별칭을 사용해 정보를 명확하게 표시할 수 있음
AS 연산자는 쿼리의 맨 마지막에 실행됨
- WHERE 연산자나 GROUP BY 호출 같은 것에선 별칭을 사용할 수 없음, 별칭은 SELECT문에서만 사용 가능함
GROUP BY문에서 적용할때
ex) SELECT customer_id,SUM(amount) SELECT customer_id,SUM(amount) AS total_spent
FROM payment -> FROM payment
GROUP BY customer_id GROUP BY customer_id
라고 바꿔주면 Data Output에 total_spent라고 나옴
ex) SELECT customer_id,SUM(amount) AS total_spent
FROM payment
GROUP BY customer_id
HAVING total_spent > 100(X)
이 구문을 실행하면 오류가 발생하는데 total_spent라는 열이 존재하지 않는 메시지가 나옴
total_spent는 Data Output의 제일 마지막에 존재
별칭은 맨 마지막에 할당되기 때문에 WHERE문이나 HAVING절 같은 곳에서는 사용X
뒤로 다시 돌아가 원래 열 이름이나 원래 함수 중 하나를 선택해야함
ex2) SELECT customer_id,SUM(amount) AS total_spent
FROM payment
GROUP BY customer_id
HAVING SUM(amount) > 100(O) -> WHERE이나 HAVING절에서 별칭으로는 필터링이 안됨, 원래 열 이름을 사용해야함
GROUP BY와 HAVING SUM을 지우고 amount만 적어보는 경우
ex3) SELECT customer_id,amount
FROM payment
WHERE amount > 2(O)
ex4) SELECT customer_id,amount AS new_name
FROM payment
WHERE new_name > 2(X)
INNER JOIN
두 테이블 모두에서 매칭되는 레코드를 출력할 수 있음, 즉 두 테이블 모두에서 발생하는 레코드만 조회하는 것
ex) SELECT * FROM TableA
INNER JOIN TableB
ON TableA.col_match=TableB.col_match
예시에서는 먼저 SELECT 입력 후 간편하게 모든 열 선택한 다음 INNER JOIN에 Table B를 입력 후 무엇을 매칭할 것인지 지정
ex) SELECT * FROM payment
INNER JOIN customer
ON payment.customer_id = customer.customer_id
ex2) SELECT payment_id,payment.customer_id,first_name
FROM payment
INNER JOIN customer
ON payment.customer_id=customer.customer_id
FROM과 INNER JOIN 부분에서의 순서는 중요하지 않음
