데이터베이스 언어의 분류

• 데이터베이스 언어는 크게 DDL, DML, DCL(TCL)로 분류한다.

• DDL(Data Definition Language, 데이터 정의어)

  • 데이터베이스를 정의하는 언어이며, 데이터베이스 스키마, 테이블, 뷰 등을 생성, 수정, 삭제하는 역할을 담당한다.
  • 데이터베이스의 전체적인 구조를 만드는 언어이다.
  • SQL에선 CREATE, ALTER, DROP, TRUNCATE 등이 있다.

• DML (Data Manipulation Language, 데이터 조작어)

  • 데이터베이스 사용자가 응용 프로그램이나 질의어를 통하여 저장된 데이터를 실질적으로 처리하는데 사용하는 언어 이다.
  • 데이터베이스 사용자와 데이터베이스 관리 시스템 간의 인터페이스를 제공한다.
  • SQL에선 SELECT, INSERT, UPDATE, DELETE 등이 있다.

• DCL(Data Control Language, 데이터 제어어)

  • 데이터베이스를 제어하는 언어 이다.
  • 데이터의 보안, 무결성, 회복, 권한 등을 정의하는데 사용한다
  • TCL (Transaction Control Language, 트랜잭션 제어 언어)과 DCL로 구분하기도 한다.
  • SQL에선 GRANT, REVOKE, COMMIT, ROLLBACK 등이 있다

• SQL 예약어를 대문자로 표시하고 끝에 세미콜론(선택사항)을 붙이면 코드 가독성이 좋아진다.

SELECT

• 테이블의 데이터를 조회하는 명령어이다.
• SELECT 컬럼명 FROM 테이블명;
• 컬럼명을 *으로 쓰면 모든 컬럼을 조회한다는 의미이다.
• 컬럼명들 사이에 ,를 넣으면 테이블에 있는 여러 개의 컬럼을 조회할 수 있다.
• 산술 연산과 같이 테이블이 필요하지 않은 경우 from 테이블명을 생략할 수 있다.
• PostgreSQL의 SELECT에서 가능한 모든 문법은 다음과 같다.

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]
    [ HAVING condition ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ]
    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]

• DISTINCT: 중복된 데이터를 제외하고 조회할 때 사용, 어떤 컬럼에서 고유한 값을 찾고 싶을 때 유용하다.

• SELECT DISTINCT 컬럼명 FROM 테이블명;

• SELECT DISTINCT(컬럼명) FROM 테이블명; DISTINCT 바로 뒤에 컬럼명에 괄호를 씌워서 사용할 수도 있다. 괄호를 쓰나 안쓰나 결과는 같다.

• COUNT 함수: 특정 쿼리 조건에 맞는 입력 행의 개수를 구하는데 사용한다.

  • 함수이기 때문에 괄호를 사용해야 한다.
  • 특정 컬럼을 지정하거나 COUNT(*)로 모든 컬럼을 지정할 수 있다. 둘 다 결과는 동일하다.
  • SELECT COUNT(컬럼) FROM 테이블명;
  • 단순히 테이블의 레코드 개수를 세어서 반환하기 때문에 다른 명령어와 함께 사용하는 경우가 많다.
  • 특히 DISTINCT와 많이 사용된다.
  • SELECT COUNT(DISTINCT 컬럼명) FROM 테이블명;

• WHERE 문: 컬럼에 조건을 지정하여 그에 맞는 행이 반환 되도록 한다.

  • 조건에 맞는 필터 기능을 수행한다.
  • FROM 절 바로 뒤에 위치한다
  • SELECT column1, column2 FROM table WHERE conditions;
  • SELECT name, choice FROM table WHERE name = ‘David' AND choice = 'RED'

• ORDER BY문: 조회된 데이터를 기준에 맞게 정렬해준다.

  • SELECT column1, column2, … FROM table ORDER BY column_1 ASC / DESC
  • ASC: 오름차순, DESC: 내림차순, 지정하지 않으면 보통 ASC를 사용한다
  • 보통 SQL문 가장 끝에 위치하지만 LIMIT 보다는 앞에 위치한다.
  • ORDER BY를 여러 컬럼으로 지정하여 사용할 수도 있다.
  • SELECT store_id, first_name, last_name FROM customer ORDER BY store_id ASC, first_name DESC;

• LIMIT: 쿼리로 반환 되는 행의 개수를 제한하는 명령어

  • 쿼리 요청의 가장 아래로 내려가며 가장 마지막에 실행된다.
  • SELECT * FROM table LIMIT 5;
  • SELECT customer_id FROM payment ORDER BY payment_date ASC limit 10;

• BETWEEN 연산자: 값을 값 범위와 비교할 때 사용

  • WHERE문에 조건을 더하기 위해 많이 사용된다.
  • value ≥ low AND value ≤ high
  • value BETWEEN low AND high 위와 같은 의미 이다. (low이상, high이하)
  • value < low or value > high
  • value NOT BETWEEN low AND high NOT을 이용한 반대 범위 지정(low 미달, high 초과)
  • SELECT COUNT(*) FROM film WHERE rating = 'R' AND replacement_cost BETWEEN 5 AND 15;
  • BETWEEN으로 date 범위를 필터링하는 경우 시간에 유의해야 한다.
  • BETWEEN '2007-02-01' AND '2007-02-14’ 일 때 실제 시간 범위는 2007년 2월 1일 0시 ~ 2007년 2월 14일 0시 이다. 따라서 2007년 2월 14일 10시 같은 데이터는 조회 되지 않는다.

• IN 연산자: 어떤 값이 목록에 포함됐는지 확인할 때 사용한다.

  • SELECT color FROM table WHERE color IN ('red', 'blue');
  • SELECT color FROM table WHERE color NOT IN ('red', 'blue'); - NOT 연산자 사용
  • SELECT * FROM payment where amount not in (0.99, 1.98, 1.99);

• LIKE, ILIKE: 문자열 데이터에 대한 패턴 매칭을 수행하기 위한 명령어이다.
  LIKE는 대소문자를 구분하지만 ILIKE는 구분하지 않는다.

  • 와일드 카드 연산자를 사용할 수 있다.
  • 퍼센트 기호(%): 어떤 문자열과도 일치
  • 밑줄 기호(_): 어떤 문자와도 일치, _의 개수에 따라 매칭되는 문자의 개수를 결정한다.
  • 대문자 ‘A’로 시작하는 이름: WHERE name LIKE 'A%'
  • 소문자 ‘a’로 끝나는 이름: WHERE name LIKE '%a'
  • select * from customer where first_name like 'J%' and last_name ilike 's%';
  • WHERE title LIKE ‘Mission Impossible _’
    • Mission Impossible 1, Mission Impossible 2, Mission Impossible 3 등이 모두 매칭된다.
  • 연속된 _을 쓸 수 있다. WHERE value LIKE 'Version#__': Version#10, Version#20 등이 모두 매칭된다.
  • 두 개를 조합하여 사용: WHERE name LIKE '_her%': Cheryl, Theresa, Sherri 등이 모두 매칭된다

GROUP BY 절

• 데이터가 카테고리 별로 어떻게 분포되어 있는지 파악하기 위해 데이터를 집계하고 함수를 적용하는 명령어 이다.

• 특정 컬럼을 기준으로 동일한 값끼리 그룹화하여 집계할 때 유용하다.

• 예를 들어 밑의 표에서 카테고리가 A인 레코드의 값의 평균(AVG(), 9)이나 개수(COUNT(), 2개)를 구해야 할 때 GROUP BY를 사용할 수 있다.

  카테고리	값
  A	10
  A	8
  B	6
  B	12

• 집계함수의 종류

  • AVG(): SELECT ROUND(AVG(replacement_cost), 2) FROM film;
  • COUNT(): SELECT COUNT(*) FROM film;
  • MAX(): SELECT MAX(replacement_cost) FROM film;
  • MIN(): SELECT MIN(replacement_cost) FROM film;
  • SUM(): SELECT SUM(replacement_cost) FROM film;

• SELECT category_col, 집계함수(data_col) FROM table GROUP BY category_col;

• GROUP BY절은 FROM문 바로 뒤 또는 WHERE문 바로 뒤에 위치해야 한다.

• SELECT문에서 특정 컬럼을 조회한다면 그 컬럼이 GROUP BY절에 포함되야 한다. 단 집계함수는 GROUP BY절에 포함되지 않아도 된다.

  • SELECT company, division, SUM(sales) FROM finance_table GROUP BY company, division;

• WHERE절에서는 집계함수를 쓸 수 없다. 대신 HAVING을 사용할 수 있다.

• 집계를 기반하여 정렬하려면 ORDER BY절에서 전체 함수를 참조해야 한다.

  • SELECT company, SUM(sales) FROM finance_table GROUP BY company ORDER BY SUM(sales);

• 가장 많은 금액을 사용한 고객ID는?

  • SELECT customer_id, SUM(amount) FROM payment GROUP BY customer_id ORDER BY SUM(amount) DESC;

• 결제 날짜 별(DATE함수 사용) 결제 금액 합계를 구하고, 합계 내림차순으로 정렬하기

  • SELECT DATE(payment_date), SUM(amount) FROM payment GROUP BY DATE(payment_date) ORDER BY SUM(amount) DESC;

• 직원id별로 처리한 결제 건수는 몇 건인가?

  • SELECT count(*), staff_id FROM payment group by staff_id;

• 영화 등급별 평균 교체 비용은?

  • SELECT rating, ROUND(AVG(replacement_cost), 2) FROM film GROUP BY rating;

• 결제 금액 합계 기준 가장 많이 지출한 고객 id 5개 찾기

  • SELECT customer_id, SUM(amount) FROM payment GROUP BY customer_id ORDER BY SUM(amount) DESC LIMIT 5;

HAVING 절

• HAVING절은 GROUP BY절로 선택된 그룹에 대한 탐색 조건을 지정한다. 즉 GROUP BY절에 대한 조건문이라고 할 수 있다. 따라서 HAVING은 GROUP BY가 있는 질의문에서만 사용이 가능하다.
• HAVING절은 집계가 이미 수행된 이후에 자료를 필터링하기 때문에 GROUP BY절 뒤에 위치한다.
• SELECT company, SUM(sales) FROM finance_table WHERE company != 'Google' GROUP BY company HAVING SUM(sales) > 1000;
  • WHERE 필터를 적용하고 나서 GROUP BY를 호출한 후에 HAVING 절이 적용되어 판매액 총액이 1000보다 큰 값을 조건으로 다시 필터링 된다.
• 결제 거래 건수가 40건 이상인 고객 ID 찾기
  • SELECT customer_id, COUNT() FROM payment GROUP BY customer_id HAVING COUNT() >= 40
• 직원 ID 2번과의 거래 중 100을 초과하여 사용한 고객의 ID 찾기
  • SELECT customer_id, SUM(amount) FROM payment WHERE staff_id = 2 GROUP BY customer_id HAVING SUM(amount) > 100

ON, WHERE, HAVING 절의 역할 구분

• ON: 조인에 대한 조건문
• WHERE: 레코드에 대한 조건문
• HAVING: GROUP BY절에 대한 조건문

중첩질의(Subquery, 서브쿼리)

• 중첩질의란 하나의 SQL 쿼리 내에서 다른 SQL 쿼리를 포함하는 형식의 질의를 말한다.
• 중첩질의 부분을 구별하기 위해 괄호를 사용한다.
• 중첩질의에 대해 AS로 별칭을 만들 수 있다. 여기서 AS를 생략할 수도 있다
• 내포된 질의를 내부 질의, 내포하고 있는 질의를 외부 질의라고 한다.
• SELECT문에서 서브쿼리는 FROM 절에 있는 것과 WHERE 절에 있는 것으로 구분할 수 있다.
• EXISTS 연산자는 외부 질의의 각 레코드에 대해 내부 질의의 결과에 레코드가 존재하면 true를 반환하고 없으면 false를 반환한다. NOT EXISTS는 반대로 동작한다.
• PostgreSQL에선 INTERSECT(교집합), EXCEPT(차집합), EXISTS, NOT EXISTS 연산자를 서브쿼리와 함께 쓸 수 있다.

-- FROM subquery
SELECT column1, column2
FROM (
    SELECT column1, column2
    FROM table1
) AS subquery;

-- products 테이블에서 특정 가격 범위에 속하는 상품들의 평균 가격을 계산하는 예시
SELECT price_range, AVG(price) AS average_price
FROM (
    SELECT 
        CASE
            WHEN price < 50 THEN 'Cheap'
            WHEN price >= 50 AND price < 100 THEN 'Moderate'
        ELSE 'Expensive'
        END AS price_range, price
    FROM products
) AS subquery
GROUP BY price_range;

-- 학과별로 소속 교수의 평균 연봉이 7천만원 미만인 학과 중에서 가장 높은 학과의 평균 연봉 구하기
SELECT MAX(sq.평균연봉) AS 평균연봉 
FROM (SELECT 소속학과, AVG(연봉) AS 평균연봉 FROM 교수 GROUP BY 소속학과) AS sq 
WHERE sq.평균연봉 <= 70000000

-- WHERE subquery
SELECT column1, column2, ...
FROM table1
WHERE column1 IN (SELECT subquery_column FROM table2 WHERE condition);

-- 생활과학과 소속 학생 중 수강신청을 하지 않은 학생의 학생번호 구하기
SELECT a.학생번호 
FROM 전공 AS a 
WHERE a.학과이름='생활과학과' 
AND NOT EXISTS (
    SELECT 학생번호 
    FROM 수강 AS b 
    WHERE a.학생번호 = b.학생번호
);

SQL문 기초 연습문제

1. ID가 2인 직원에게서 최소 110달러를 쓴 고객의 ID는?

SELECT customer_id, SUM(amount) 
FROM payment WHERE staff_id = 2 
GROUP BY customer_id 
HAVING SUM(amount) > 110;

2. J로 시작하는 영화는 몇 개인가?

SELECT COUNT(*) 
FROM film 
WHERE title LIKE 'J%';

3. 이름이 ‘E’로 시작하는 동시에 주소 ID가 500미만인 고객 중, ID 번호가 가장 높은 고객은?

SELECT first_name, last_name 
FROM customer 
WHERE first_name LIKE 'E%' AND address_id < 500 
ORDER BY customer_id DESC 
LIMIT 1;

4. 2012년 9월의 예약 건수 총합을 facid 별로 집계

SELECT facid, SUM(slots) AS total_slots
FROM cd.bookings
WHERE starttime BETWEEN '2012-09-01' AND '2012-10-01'
GROUP BY facid
ORDER BY SUM(slots)

5. facid 별로 예약 건수 총합이 1000건 이상인 것만 조회하고, facid 오름차순으로 정렬

SELECT facid, SUM(slots) AS total_slots
FROM cd.bookings
GROUP BY facid
HAVING SUM(slots) > 1000
ORDER BY facid

참고자료

• https://www.udemy.com/course/best-sql-2022/