SQL | 실습으로 배우는 SQL 문법 - 2

더 다양한 예시를 살펴보겠습니다.

가장 높은 급여를 받는 직원 찾기 (MAX와 서브쿼리)

우선, 지금 있는 Employee 테이블에 salary 컬럼을 추가하고, 각 직원의 급여도 입력합니다.

mysql> ALTER TABLE Employee ADD COLUMN salary INT;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> UPDATE Employee SET salary = 70000 WHERE SSN = '101';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE Employee SET salary = 75000 WHERE SSN = '102';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE Employee SET salary = 80000 WHERE SSN = '103';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE Employee SET salary = 72000 WHERE SSN = '104';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE Employee SET salary = 88000 WHERE SSN = '105';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM Employee;
+-----+----------+-----------+------+---------+---------+--------+
| SSN | lastname | firstname | sex  | dept_id | city    | salary |
+-----+----------+-----------+------+---------+---------+--------+
| 101 | Smith    | Liam      | M    | D01     | Halifax |  70000 |
| 102 | Johnson  | Ava       | F    | D01     | Calgary |  75000 |
| 103 | Brown    | Ethan     | M    | D02     | Halifax |  80000 |
| 104 | Davis    | Mia       | F    | D03     | Calgary |  72000 |
| 105 | Taylor   | Lucas     | M    | D02     | Halifax |  88000 |
+-----+----------+-----------+------+---------+---------+--------+
5 rows in set (0.00 sec)

 

mysql> SELECT * FROM Employee WHERE salary = (SELECT MAX(salary) FROM Employee);
+-----+----------+-----------+------+---------+---------+--------+
| SSN | lastname | firstname | sex  | dept_id | city    | salary |
+-----+----------+-----------+------+---------+---------+--------+
| 105 | Taylor   | Lucas     | M    | D02     | Halifax |  88000 |
+-----+----------+-----------+------+---------+---------+--------+
1 row in set (0.00 sec)

SELECT MAX(salary) FROM Employee는 최고 급여를 찾는 서브쿼리입니다. WHERE salary = (...) 로 급여가 최대값인 직원만 필터링합니다. 

INNER JOIN

CREATE TABLE Driver (
  driver_lic CHAR(2) PRIMARY KEY,
  firstname VARCHAR(20),
  lastname VARCHAR(20)
);

CREATE TABLE Car (
  car_id INT PRIMARY KEY,
  make VARCHAR(30),
  model VARCHAR(50),
  driver_lic CHAR(2),
  FOREIGN KEY (driver_lic) REFERENCES Driver(driver_lic)
);

두 테이블을 생성했습니다. 운전자는 여러 자동차를 소유할 수 있으므로 1:N 관계가 되겠습니다.

-- 운전자
INSERT INTO Driver (driver_lic, firstname, lastname) VALUES
('A1', 'Liam', 'Smith'),
('B2', 'Ava', 'Johnson'),
('C3', 'Ethan', 'Brown'),
('D4', 'Hugh', 'Grant');

-- 자동차
INSERT INTO Car (car_id, make, model, driver_lic) VALUES
(101, 'Toyota', 'Corolla', 'A1'),
(102, 'Honda', 'Civic', 'B2'),
(103, 'Tesla', 'Model 3', 'C3'),
(104, 'Ford', 'Focus', 'A1');

예시 데이터를 입력해 보았습니다. 우리 회사 소유 차량을 운전하는 운전자들의 성(last name) 과 그들이 운전하는 자동차의 제조사(make), 모델명(model) 을 보여 주려면 어떻게 해야 할까요?

mysql> SELECT D.lastname, C.make, C.model FROM Driver D INNER JOIN Car C ON d.driver_lic = c.driver_lic;
+----------+--------+---------+
| lastname | make   | model   |
+----------+--------+---------+
| Smith    | Toyota | Corolla |
| Johnson  | Honda  | Civic   |
| Brown    | Tesla  | Model 3 |
| Smith    | Ford   | Focus   |
+----------+--------+---------+
4 rows in set (0.01 sec)

Hugh Grant는 프린트되지 않습니다.

New York에 위치한 부서에서 일하는 직원 찾기

mysql> SELECT * FROM Employee;
+-----+----------+-----------+------+---------+---------+--------+
| SSN | lastname | firstname | sex  | dept_id | city    | salary |
+-----+----------+-----------+------+---------+---------+--------+
| 101 | Smith    | Liam      | M    | D01     | Halifax |  70000 |
| 102 | Johnson  | Ava       | F    | D01     | Calgary |  75000 |
| 103 | Brown    | Ethan     | M    | D02     | Halifax |  80000 |
| 104 | Davis    | Mia       | F    | D03     | Calgary |  72000 |
| 105 | Taylor   | Lucas     | M    | D02     | Halifax |  88000 |
+-----+----------+-----------+------+---------+---------+--------+
5 rows in set (0.01 sec)

mysql> SELECT * FROM Department;
+---------+-------------+---------------+
| dept_id | dept_name   | dept_location |
+---------+-------------+---------------+
| D01     | Engineering | New York      |
| D02     | Marketing   | Chicago       |
| D03     | HR          | Boston        |
| D04     | Business    | Toronto       |
+---------+-------------+---------------+
4 rows in set (0.00 sec)

Department 테이블에서 dept_location = 'New York' 인 부서를 찾고, 그 dept_id를 가진 직원들을 Employee 테이블에서 조회하면 됩니다.

mysql> SELECT E.* FROM Employee E JOIN Department D ON E.dept_id = D.dept_id WHERE D.dept_location = 'New York';
+-----+----------+-----------+------+---------+---------+--------+
| SSN | lastname | firstname | sex  | dept_id | city    | salary |
+-----+----------+-----------+------+---------+---------+--------+
| 101 | Smith    | Liam      | M    | D01     | Halifax |  70000 |
| 102 | Johnson  | Ava       | F    | D01     | Calgary |  75000 |
+-----+----------+-----------+------+---------+---------+--------+
2 rows in set (0.00 sec)

 

• JOIN으로 Employee와 Department를 연결
• WHERE D.dept_location = 'New York' 조건을 줘서
  → New York에 있는 부서에서 일하는 직원들만 선택

mysql> SELECT * FROM Employee WHERE dept_id IN (SELECT dept_id FROM Department WHERE dept_location = 'New York');
+-----+----------+-----------+------+---------+---------+--------+
| SSN | lastname | firstname | sex  | dept_id | city    | salary |
+-----+----------+-----------+------+---------+---------+--------+
| 101 | Smith    | Liam      | M    | D01     | Halifax |  70000 |
| 102 | Johnson  | Ava       | F    | D01     | Calgary |  75000 |
+-----+----------+-----------+------+---------+---------+--------+
2 rows in set (0.00 sec)

 

 

• 서브쿼리로 New York에 있는 부서의 ID들을 먼저 찾고,
• 그 dept_id를 가진 직원들만 조회

NOT IN

우리 회사 차량을 운전하지 않는 사람들을 리턴해 봅시다.

mysql> SELECT * FROM Driver WHERE driver_lic NOT IN (SELECT driver_lic FROM Car);
+------------+-----------+----------+
| driver_lic | firstname | lastname |
+------------+-----------+----------+
| D4         | Hugh      | Grant    |
+------------+-----------+----------+
1 row in set (0.00 sec)

 

Car 테이블에 있는 운전자 면허(driver_lic) 목록을 서브쿼리로 뽑고, 그 리스트에 포함되지 않은(driver_lic NOT IN) 사람들을 찾는 방식입니다.

GROUP BY, HAVING

mysql> SELECT * FROM Pet;
+--------+-------+---------+------------------+------------+----------+--------+
| pet_id | name  | species | breed            | birthdate  | owner_id | weight |
+--------+-------+---------+------------------+------------+----------+--------+
|      1 | Bori  | Dog     | Shih Tzu         | 2020-04-15 |        1 |      5 |
|      2 | Nabi  | Cat     | Korean Shorthair | 2019-07-23 |        1 |      3 |
|      3 | Coco  | Dog     | Poodle           | 2021-11-30 |        2 |      7 |
|      4 | Toto  | Parrot  | Cockatiel        | 2018-02-12 |        3 |      1 |
|      5 | Mango | Dog     | Golden Retriever | 2022-05-20 |        2 |     26 |
|      6 | Luna  | Cat     | Siamese          | 2021-10-01 |        3 |      4 |
|      7 | Bunny | Rabbit  | Netherland Dwarf | 2023-01-10 |        1 |      1 |
+--------+-------+---------+------------------+------------+----------+--------+
7 rows in set (0.00 sec)

위와 같은 데이터가 있습니다. GROUP BY랑 HAVING은 집계 함수와 함께 그룹별로 데이터를 분석할 때 자주 씁니다. Pet 테이블을 기반으로 예시를 보여드리겠습니다.

주인(owner_id)별로 반려동물 수 구하기

mysql> SELECT owner_id, COUNT(*) AS pet_count FROM Pet GROUP BY owner_id;
+----------+-----------+
| owner_id | pet_count |
+----------+-----------+
|        1 |         3 |
|        2 |         2 |
|        3 |         2 |
+----------+-----------+
3 rows in set (0.00 sec)

 

• GROUP BY owner_id: 각 주인별로 그룹화
• COUNT(*): 각 그룹(주인)이 소유한 반려동물 수를 센다

반려동물이 3마리 이상인 주인만 보기

mysql> SELECT owner_id, COUNT(*) AS pet_count FROM Pet GROUP BY owner_id HAVING pet_count >= 3;
+----------+-----------+
| owner_id | pet_count |
+----------+-----------+
|        1 |         3 |
+----------+-----------+
1 row in set (0.00 sec)

 

 

• HAVING: GROUP BY의 결과에 조건을 줄 때 사용
• 위 쿼리는 반려동물이 3마리 이상인 주인만 보여줌
• WHERE은 집계 전 조건 / HAVING은 집계 후 조건

종별 반려동물 총 몸무게 (SUM)

mysql> SELECT species, SUM(weight) AS total_weight FROM Pet GROUP BY species;
+---------+--------------+
| species | total_weight |
+---------+--------------+
| Cat     |            7 |
| Dog     |           38 |
| Parrot  |            1 |
| Rabbit  |            1 |
+---------+--------------+
4 rows in set (0.01 sec)