필수로 알아야할 13가지 SQL 문법

Structured Query Language(SQL)은 관계형 데이터베이스를 관리하고 조작하기 위해 설계된 프로그래밍 언어입니다. 이는 데이터 분석가와 데이터 과학자가 대규모 데이터셋에서 통찰을 추출하기 위해 널리 사용됩니다.

 

SQL은 데이터를 필터링, 정렬, 그룹화, 집계하는 등 다양한 데이터 조작 작업을 수행할 수 있는 강력한 도구입니다. 이 글에서는 데이터 과학 작업의 90%를 수행할 수 있는 13가지 필수 SQL 문법에 대해 다룹니다. 이러한 문법은 이해하기 쉽고 구현하기 쉬우며, SQL 작업의 기초를 다지는 데에 도움을 줍니다.

1. SELECT

SELECT 문은 데이터베이스에서 하나 이상의 테이블에서 데이터를 조회하는 데 사용됩니다. WHERE, ORDER BY, GROUP BY와 같은 다양한 함수와 함께 사용하여 데이터를 필터링, 정렬, 그룹화하는 데 익숙해져야 합니다.

SELECT column1, column2, column3
FROM table_name
WHERE condition;

위 예제에서 column1, column2, column3는 조회하려는 열의 이름이며, table_name은 데이터를 포함한 테이블의 이름입니다. WHERE 절은 선택 사항이지만, 쿼리가 데이터를 조회하기 위해 충족해야 하는 조건을 지정하는 데 사용됩니다.

예제

고객 테이블에서 18세 이상인 모든 고객을 조회

SELECT *
FROM customers
WHERE age >= 18;

2. JOIN

JOIN 문은 데이터베이스의 두 개 이상의 테이블에서 데이터를 결합할 때 사용됩니다. 필요한 경우 적합한 JOIN 유형(예: INNER, LEFT, RIGHT, FULL OUTER)을 지정해야 합니다.

INNER JOIN

INNER JOIN은 두 테이블의 열에서 일치하는 행만 반환합니다.

SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id;

위 예제에서 orders 테이블과 customers 테이블은 customer_id 열을 사용하여 조인됩니다. 결과 테이블에는 두 테이블의 customer_id 열에서 일치하는 데이터가 있는 경우에만 order_id와 customer_name 열이 포함됩니다.

LEFT JOIN

LEFT JOIN은 왼쪽 테이블의 모든 행과 오른쪽 테이블에서 일치하는 행을 반환합니다. 오른쪽 테이블에 일치하는 데이터가 없으면 결과에 NULL 값이 포함됩니다.

SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

위 예제에서 customers 테이블은 왼쪽 테이블이고 orders 테이블은 오른쪽 테이블입니다. 두 테이블은 customer_id 열을 사용하여 조인됩니다. 결과 테이블에는 customers 테이블의 모든 행이 포함되고, orders 테이블에 일치하는 데이터가 없는 경우 order_id 열은 NULL 값을 가집니다.

RIGHT JOIN

RIGHT JOIN은 오른쪽 테이블의 모든 행과 왼쪽 테이블에서 일치하는 행을 반환합니다. 왼쪽 테이블에 일치하는 데이터가 없으면 결과에 NULL 값이 포함됩니다.

SELECT customers.customer_name, orders.order_id
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;

위 예제에서 orders 테이블은 오른쪽 테이블이고, customers 테이블은 왼쪽 테이블입니다. 두 테이블은 customer_id 열을 사용하여 조인됩니다. 결과 테이블에는 orders 테이블의 모든 행이 포함되고, customers 테이블에 일치하는 데이터가 없는 경우 customer_name 열은 NULL 값을 가집니다.

OUTER JOIN

OUTER JOIN은 두 테이블 중 하나 또는 두 테이블의 모든 행을 반환하며, 일치하지 않는 행도 포함합니다. LEFT OUTER JOIN과 RIGHT OUTER JOIN 두 가지 유형이 있습니다.

LEFT OUTER JOIN 예제

SELECT customers.customer_name, orders.order_id
FROM customers
LEFT OUTER JOIN orders
ON customers.customer_id = orders.customer_id;

위 예제에서 customers 테이블은 왼쪽 테이블이고 orders 테이블은 오른쪽 테이블입니다. 결과 테이블에는 customers 테이블의 모든 행이 포함되며, orders 테이블에 일치하는 데이터가 없는 경우 order_id 열은 NULL 값을 가집니다.

RIGHT OUTER JOIN 예제

SELECT customers.customer_name, orders.order_id
FROM customers
RIGHT OUTER JOIN orders
ON customers.customer_id = orders.customer_id;

위 예제에서 orders 테이블은 오른쪽 테이블이고 customers 테이블은 왼쪽 테이블입니다. 결과 테이블에는 orders 테이블의 모든 행이 포함되며, customers 테이블에 일치하는 데이터가 없는 경우 customer_name 열은 NULL 값을 가집니다.

 

참고: 일부 데이터베이스는 RIGHT OUTER JOIN을 지원하지 않을 수 있습니다. 이 경우, LEFT OUTER JOIN을 사용하고 테이블의 순서를 바꾸어 동일한 결과를 얻을 수 있습니다.

3. WHERE

WHERE 문은 지정된 조건에 따라 데이터를 필터링하는 데 사용됩니다. 특정 기준을 충족하는 데이터만 조회하려면 WHERE 문을 능숙하게 사용하는 것이 중요합니다.

아래는 테이블에서 데이터를 필터링하기 위해 WHERE 문을 사용하는 예제입니다.

SELECT name, department, salary
FROM employees
WHERE department = 'Sales' AND salary > 50000;

이 쿼리는 "Sales" 부서에서 근무하며 급여가 $50,000 이상인 모든 직원의 이름, 부서, 급여를 결과로 반환합니다.

4. GROUP BY

GROUP BY 문은 하나 이상의 열을 기준으로 데이터를 그룹화하며, COUNT, SUM, AVG와 같은 집계 함수를 사용하여 그룹화된 데이터의 요약 정보를 계산할 수 있습니다. GROUP BY를 활용하여 카테고리별로 데이터를 분석하는 방법을 숙달하는 것이 중요합니다.

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

이 쿼리는 모든 부서와 각 부서의 평균 급여를 반환합니다. 각 부서의 평균 급여는 해당 부서 직원들의 모든 급여 합계를 직원 수로 나눈 값으로 계산됩니다. GROUP BY 절은 부서별로 직원을 그룹화하는 데 사용되며, AVG 함수는 각 부서의 평균 급여를 계산하는 데 사용됩니다.

Department	Avg Salary
Sales	65,000
Marketing	55,000
Engineering	80,000

위 예제에서, Sales 부서는 평균 급여가 $65,000, Marketing 부서는 $55,000, Engineering 부서는 $80,000임을 확인할 수 있습니다.

5. HAVING

HAVING 문은 GROUP BY로 그룹화된 데이터를 특정 조건에 따라 필터링하는 데 사용됩니다. 그룹화된 데이터를 추가로 필터링하는 방법을 숙달하는 것이 중요합니다.

아래는 HAVING 절을 사용하는 SQL 예제입니다.

SELECT customer_id, SUM(quantity) AS total_quantity
FROM orders
GROUP BY customer_id
HAVING SUM(quantity) >= 50;

이 쿼리는 모든 고객과 그들이 주문한 총 제품 수량을 반환하지만, 총 수량이 50단위 이상인 고객만 결과에 포함됩니다. GROUP BY 절은 고객별로 주문을 그룹화하는 데 사용되고, SUM 함수는 각 고객이 주문한 제품의 총 수량을 계산하는 데 사용됩니다. 마지막으로 HAVING 절은 결과를 필터링하여 조건을 만족하는 데이터만 반환합니다.

6. 윈도우 함수 (Window Function)

SQL에서 윈도우 함수는 현재 행과 관련된 행 집합에 대해 계산을 수행하는 데 사용됩니다. 이러한 함수는 지정된 조건이나 파티션에 기반한 테이블의 일부 행(윈도우)에 적용됩니다. 아래는 SQL에서 윈도우 함수를 사용하는 몇 가지 예제입니다.

1. ROW_NUMBER()

ROW_NUMBER() 함수는 각 파티션 내에서 각 행에 고유한 순차 번호를 할당합니다.

SELECT column1, column2, ..., ROW_NUMBER() OVER (ORDER BY column1) AS row_num
FROM table_name;

이 쿼리는 결과 집합에 row_num이라는 추가 열을 포함하며, 이 열은 column1의 순서에 따라 각 행에 순차적으로 번호를 부여합니다.

2. SUM()

SUM() 함수는 각 파티션 내에서 특정 열의 합계를 계산합니다.

SELECT column1, column2, ..., SUM(column3) OVER (PARTITION BY column1) AS column3_sum
FROM table_name;

이 쿼리는 결과 집합에 column3_sum이라는 추가 열을 포함하며, 이 열은 각 파티션에서 column1 값에 따라 column3의 합계를 계산합니다.

3. RANK()

RANK() 함수는 특정 열의 값에 따라 각 파티션 내에서 각 행에 순위를 할당합니다.

SELECT column1, column2, ..., RANK() OVER (PARTITION BY column1 ORDER BY column3 DESC) AS rank_num
FROM table_name;

이 쿼리는 결과 집합에 rank_num이라는 추가 열을 포함하며, 이 열은 각 파티션에서 column3 값을 기준으로 내림차순 정렬된 순위를 나타냅니다.

4. AVG()

AVG() 함수는 각 파티션 내에서 특정 열의 평균을 계산합니다.

SELECT column1, column2, ..., AVG(column3) OVER (PARTITION BY column1) AS column3_avg
FROM table_name;

이 쿼리는 결과 집합에 column3_avg라는 추가 열을 포함하며, 이 열은 각 파티션에서 column1 값에 따라 column3의 평균을 계산합니다.

참고

윈도우 함수의 문법은 사용하는 데이터베이스 관리 시스템(DBMS)에 따라 다를 수 있습니다. 사용하려는 DBMS의 문서를 참고하여 정확한 문법을 확인하는 것이 좋습니다.

7. UNION

SQL에서 UNION 연산자는 두 개 이상의 SELECT 문의 결과를 하나의 결과 집합으로 결합하는 데 사용됩니다. 각 SELECT 문은 동일한 열 개수와 호환 가능한 데이터 유형을 가져야 합니다. 결과 집합에서는 중복된 행이 자동으로 제거됩니다.

SELECT name, city
FROM customers
WHERE city = 'New York'
UNION
SELECT name, city
FROM employees
WHERE city = 'New York';

이 쿼리는 뉴욕에 거주하는 모든 사람(고객과 직원 포함)의 목록을 반환합니다. 첫 번째 SELECT 문은 뉴욕에 거주하는 고객을 조회하며, 두 번째 SELECT 문은 뉴욕에 거주하는 직원을 조회합니다. UNION 연산자는 이 두 SELECT 문의 결과를 결합하고 중복된 행을 제거합니다.

8. CREATE

CREATE 문은 새로운 데이터베이스 테이블, 뷰 또는 기타 데이터베이스 객체를 생성하는 데 사용됩니다. 새로운 테이블, 뷰, 기타 객체를 생성하는 방법을 숙달하는 것이 중요합니다.

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100),
  phone VARCHAR(20)
);

이 쿼리는 "customers"라는 이름의 새 테이블을 생성하며, 네 개의 열("id", "name", "email", "phone")을 포함합니다.

• id: 정수형으로 설정되며, 테이블의 기본 키로 지정됩니다.
• name: 최대 50자까지 저장할 수 있는 문자열로 설정됩니다.
• email: 최대 100자까지 저장할 수 있는 문자열로 설정됩니다.
• phone: 최대 20자까지 저장할 수 있는 문자열로 설정됩니다.

9. INSERT

INSERT 문은 데이터베이스 테이블에 새 데이터를 삽입하는 데 사용됩니다. 테이블에 데이터를 추가하는 방법을 숙달하는 것이 중요합니다.

INSERT INTO students (id, name, major, gpa)
VALUES (1234, 'John Doe', 'Computer Science', 3.5);

이 쿼리는 다음 값으로 새 행을 "students" 테이블에 삽입합니다:

• id: 1234
• name: John Doe
• major: Computer Science
• gpa: 3.5

INSERT 문은 데이터를 삽입하려는 테이블의 이름을 지정하고, 삽입할 열 목록을 작성한 뒤, VALUES 키워드를 사용해 해당 열에 삽입할 값을 순서대로 지정합니다.

10. UPDATE

UPDATE 문은 데이터베이스 테이블의 기존 데이터를 수정하는 데 사용됩니다. 테이블의 하나 이상의 열 값을 업데이트하는 방법을 숙달하는 것이 중요합니다.

UPDATE students
SET major = 'Mathematics', gpa = 3.7
WHERE id = 1234;

이 쿼리는 다음과 같은 작업을 수행합니다:

• major 열의 값을 'Mathematics'로 업데이트
• gpa 열의 값을 3.7로 업데이트
• WHERE 절을 사용하여 ID가 1234인 행만 업데이트

UPDATE 문은 업데이트하려는 테이블의 이름을 지정한 후, SET 키워드를 사용하여 업데이트할 열과 새 값을 나열합니다. 특정 행만 업데이트하려면 WHERE 절을 사용하여 조건을 지정해야 합니다.

11. DELETE

DELETE 문은 데이터베이스 테이블에서 하나 이상의 행을 삭제하는 데 사용됩니다. 테이블에서 데이터를 제거하는 방법을 숙달하는 것이 중요합니다.

DELETE FROM students
WHERE id = 1234;

이 쿼리는 "students" 테이블에서 ID가 1234인 행을 제거합니다.

• DELETE FROM은 데이터를 삭제하려는 테이블의 이름을 지정합니다.
• WHERE 절은 삭제할 행을 지정하는 조건을 나타냅니다. 이 경우, ID가 1234인 행을 삭제하도록 설정했습니다.

12. DROP

DROP 문은 데이터베이스 테이블이나 기타 데이터베이스 객체를 삭제하는 데 사용됩니다. 불필요한 테이블이나 기타 객체를 데이터베이스에서 제거하는 방법을 숙달하는 것이 중요합니다.

1. DROP TABLE

기존 테이블과 해당 테이블의 모든 데이터 및 인덱스를 삭제합니다.

DROP TABLE table_name;

2. DROP INDEX

테이블에서 기존 인덱스를 삭제합니다.

DROP INDEX index_name ON table_name;

3. DROP VIEW

기존의 뷰(View)를 삭제합니다.

DROP VIEW view_name;

4. DROP PROCEDURE

기존의 저장 프로시저(Stored Procedure)를 삭제합니다.

DROP PROCEDURE procedure_name;

주의 사항

• DROP 문은 지정된 객체와 관련된 모든 데이터 및 인덱스를 영구적으로 삭제합니다.
• 삭제된 데이터나 객체는 복구할 수 없으므로, 삭제 작업을 실행하기 전에 데이터를 반드시 백업해야 합니다.
• 사용하는 데이터베이스 관리 시스템(DBMS)에 따라 DROP 문의 정확한 문법이 다를 수 있으므로, 사용하는 DBMS의 문서를 참조하는 것이 좋습니다.

13. ALTER

ALTER 문은 데이터베이스 테이블이나 기타 데이터베이스 객체의 구조를 수정하는 데 사용됩니다. 열 추가 및 삭제, 데이터 유형 변경, 제약 조건 설정 등 다양한 작업을 수행하는 방법을 숙달하는 것이 중요합니다.

1. ALTER TABLE

기존 테이블의 구조를 수정합니다. 열을 추가하거나 삭제하고, 데이터 유형을 변경하거나 제약 조건을 설정하는 데 사용됩니다.

ALTER TABLE table_name
ADD column_name data_type [constraint],
MODIFY column_name data_type [constraint],
DROP column_name,
ADD CONSTRAINT constraint_name constraint_definition,
DROP CONSTRAINT constraint_name;

테이블에 새로운 열 추가

ALTER TABLE customers
ADD phone_number VARCHAR(15);

기존 열 삭제

ALTER TABLE customers
DROP COLUMN phone_number;

2. ALTER INDEX

기존 인덱스의 구조를 수정합니다. 열을 추가하거나 제거하고, 인덱스 유형을 변경할 수 있습니다.

ALTER INDEX index_name
ADD column_name,
DROP column_name;

3. ALTER VIEW

기존 뷰(View)의 정의를 수정합니다. 생성 시 사용된 SELECT 문을 변경할 수 있습니다.

ALTER VIEW view_name
AS select_statement;

참고

• ALTER 문의 정확한 문법은 사용하는 데이터베이스 관리 시스템(DBMS)에 따라 다를 수 있습니다.
• 구조를 수정하는 작업은 데이터 무결성에 영향을 미칠 수 있으므로, 변경 전에 데이터베이스 구조와 데이터에 미치는 영향을 철저히 검토해야 합니다.