SQL이란?

이번 글에서는 Supabase와 PostgreSQL의 개념과 역할을 설명하고, SQL을 활용하여 데이터베이스와 테이블을 생성하고 데이터를 다루는 방법을 실습해 보겠습니다. 특히 Supabase를 활용해 간단히 데이터베이스를 생성하고, 테이블을 만들고 데이터를 삽입하는 과정을 설명합니다. 또한 실습 문제와 퀴즈를 통해 관계형 데이터베이스의 주요 특징과 SQL 구문을 이해해보겠습니다.

1. Supabase와 PostgreSQL의 개념과 역할

Supabase란 무엇인가요?

Supabase는 오픈 소스 Firebase 대안으로 많이 사용되는 백엔드 서비스입니다. PostgreSQL을 기반으로 하여 인증, 실시간 데이터 업데이트, 파일 저장 등 다양한 기능을 제공합니다. Supabase의 주요 목적은 프론트엔드 개발자가 쉽게 백엔드 서비스를 구축할 수 있도록 도와주는 것입니다. Supabase는 데이터베이스 관리와 API 자동 생성 기능을 통해 개발자들이 복잡한 서버 구축 없이 데이터를 손쉽게 다룰 수 있게 해줍니다.

PostgreSQL이란 무엇인가요?

PostgreSQL은 객체 관계형 데이터베이스 관리 시스템(ORDBMS)으로, 안정성과 확장성을 제공하는 오픈 소스 데이터베이스입니다. SQL 표준을 준수하며, 강력한 트랜잭션 처리와 다양한 데이터 타입을 지원하여 복잡한 데이터를 관리하기 적합합니다. Supabase는 이러한 PostgreSQL을 기반으로 하여 서버 측 데이터베이스 기능을 제공하며, 개발자들이 API를 자동으로 생성하고 SQL 쿼리를 쉽게 사용할 수 있게 합니다.

2. SQL의 개념과 특징

SQL이란?

SQL (Structured Query Language)은 관계형 데이터베이스 관리 시스템에서 데이터를 정의, 조작, 관리하기 위한 표준 언어입니다. SQL을 사용하면 데이터베이스의 데이터를 쉽게 조회하고 수정할 수 있으며, 데이터베이스의 구조(스키마)를 정의하고 제어할 수 있습니다. SQL은 관계형 데이터베이스에서 데이터 무결성을 유지하고 복잡한 데이터를 효율적으로 관리하는 데 매우 적합한 언어입니다.

SQL 구문의 특징

SQL 구문은 크게 DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language)로 구분됩니다.

• DDL: 데이터베이스의 구조를 정의합니다. 예: CREATE, ALTER, DROP
• DML: 데이터베이스의 데이터를 조작합니다. 예: SELECT, INSERT, UPDATE, DELETE
• DCL: 데이터에 대한 접근 권한을 제어합니다. 예: GRANT, REVOKE

SQL 구문은 사람이 읽기 쉬운 문법을 사용하여 데이터베이스 작업을 쉽게 수행할 수 있도록 만들어졌으며, 구조화된 데이터 처리를 가능하게 합니다.

3. SQL 실습 문제

Supabase를 이용한 데이터베이스와 테이블 생성

이번 실습에서는 Supabase를 이용하여 데이터베이스와 테이블을 생성하고 데이터를 삽입해 보겠습니다.

1. Supabase로 접속하여 프로젝트를 생성하세요.
2. 데이터베이스 my_database를 만드는 SQL문을 작성하세요.

CREATE DATABASE my_database;

Accommodation 테이블 생성 및 데이터 추가

accommodation 테이블을 생성하고 데이터를 추가하는 실습을 진행합니다. 아래 조건에 맞도록 테이블을 생성하세요.

• 테이블 생성 조건:
  • zip_code 삭제
  • total_visit (총 방문 횟수) 추가
  • monthly_visit (월간 방문 횟수) 추가

CREATE TABLE accommodation (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    address TEXT NOT NULL,
    city VARCHAR(100) NOT NULL,
    country VARCHAR(100) NOT NULL,
    phone_number VARCHAR(20),
    email VARCHAR(100),
    website VARCHAR(255),
    price_per_night DECIMAL(10, 2),
    rating DECIMAL(3, 2),
    description TEXT,
    total_visit INT DEFAULT 0, -- 추가된 총 방문 횟수
    monthly_visit INT DEFAULT 0, -- 추가된 월간 방문 횟수
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

예시 데이터 삽입

다음으로, 생성한 accommodation 테이블에 10개의 예시 데이터를 추가해 보겠습니다.

INSERT INTO accommodation (name, address, city, country, phone_number, email, website, price_per_night, rating, description, total_visit, monthly_visit, created_at, updated_at) VALUES
('Sunrise Hotel', '123 Sunny St', 'Sunville', 'Sunnyland', '123-456-7890', 'contact@sunrisehotel.com', 'www.sunrisehotel.com', 120.00, 4.5, 'A beautiful hotel with a sunny disposition.', 500, 50, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
('Moonlight Inn', '456 Moon Blvd', 'Mooncity', 'Moonland', '234-567-8901', 'info@moonlightinn.com', 'www.moonlightinn.com', 90.00, 4.2, 'Experience the tranquility of the moon.', 400, 40, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
('Starry Lodge', '789 Star Rd', 'Starville', 'Starland', '345-678-9012', 'stay@starrylodge.com', 'www.starrylodge.com', 110.00, 4.7, 'A lodge with a starry ambiance.', 450, 45, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
('Ocean Breeze Resort', '101 Ocean Dr', 'Seaside', 'Waterland', '456-789-0123', 'reservations@oceanbreezeresort.com', 'www.oceanbreezeresort.com', 150.00, 4.8, 'Relax with the ocean breeze.', 600, 60, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
('Mountain Peak Hotel', '202 Mountain Ave', 'Hilltown', 'Mountainland', '567-890-1234', 'bookings@mountainpeakhotel.com', 'www.mountainpeakhotel.com', 130.00, 4.6, 'Stay at the peak of comfort.', 550, 55, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
('Forest Retreat', '303 Forest Ln', 'Treeville', 'Forestland', '678-901-2345', 'contact@forestretreat.com', 'www.forestretreat.com', 100.00, 4.3, 'A peaceful retreat in the forest.', 300, 30, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
('Desert Oasis', '404 Desert Rd', 'Sandtown', 'Desertland', '789-012-3456', 'info@desertoasis.com', 'www.desertoasis.com', 140.00, 4.5, 'An oasis of comfort in the desert.', 500, 50, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
('Lakeview Hotel', '505 Lakeview St', 'Laketown', 'Lakeland', '890-123-4567', 'stay@lakeviewhotel.com', 'www.lakeviewhotel.com', 115.00, 4.4, 'Enjoy a view of the lake.', 320, 32, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
('City Central Inn', '606 Central Ave', 'Metropolis', 'Cityland', '901-234-5678', 'reservations@citycentralinn.com', 'www.citycentralinn.com', 125.00, 4.1, 'Stay in the heart of the city.', 470, 47, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
('Countryside B&B', '707 Country Rd', 'Countryside', 'Countryside', '012-345-6789', 'contact@countrysidebnb.com', 'www.countrysidebnb.com', 85.00, 4.0, 'A cozy bed and breakfast in the countryside.', 280, 28, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

4. 데이터 조회를 위한 SQL 실습

1. 총 방문 횟수의 평균 조회

총 방문 횟수의 평균을 구하는 SQL문을 작성합니다. 결과 컬럼 이름은 avg_total_visit로 지정합니다.

SELECT AVG(total_visit) AS avg_total_visit FROM accommodation;

2. 평점 상위 3개 숙소 조회

평점이 가장 높은 3개의 숙소의 name, email, website를 조회하는 SQL문을 작성합니다.

SELECT name, email, website FROM accommodation ORDER BY rating DESC LIMIT 3;

3. 태국에 있는 숙소의 최고 및 최저 평점 조회

태국(Thailand)에 위치한 숙소의 최고 평점과 최저 평점을 조회하는 SQL문을 작성합니다. 컬럼 이름은 각각 max_rating, min_rating으로 지정합니다.

SELECT MAX(rating) AS max_rating, MIN(rating) AS min_rating FROM accommodation WHERE country = 'Thailand';

5. 관계형 데이터베이스와 SQL 퀴즈 정리

관계형 데이터베이스의 특징

관계형 데이터베이스의 주요 특징은 다음과 같습니다:

• 데이터는 테이블 형태로 저장된다: 각 데이터는 행(Row)과 열(Column)로 구성된 테이블에 저장됩니다.
• 데이터베이스는 SQL을 사용하여 데이터를 관리한다: SQL은 관계형 데이터베이스에서 데이터를 정의하고 관리하는 표준 언어입니다.
• 데이터는 관계를 통해 연결된다: 테이블 간의 관계는 기본 키(Foreign Key) 등을 통해 연결되어 있습니다.
• 데이터는 키-값 쌍으로 저장되지 않는다: 키-값 쌍은 주로 NoSQL에서 사용되는 방식입니다.

중복된 값을 제거하는 SQL문

• 중복된 값을 제거하기 위해서는 SELECT DISTINCT를 사용합니다.

SELECT DISTINCT 컬럼명 FROM 테이블명;

쿼리 결과를 오름차순으로 정렬하는 SQL문

• 결과를 오름차순으로 정렬하려면 ORDER BY 컬럼명 ASC를 사용합니다.

SELECT 열명 FROM 테이블명 ORDER BY 컬럼명 ASC;

특정 평점 이상의 숙소 조회

• 예를 들어, 평점이 4.8 이상인 숙소를 조회하고 이름을 오름차순으로 정렬하는 쿼리는 다음과 같습니다.

SELECT name FROM accommodation WHERE rating >= 4.80 ORDER BY name ASC;

• 결과: Digital Nomad Loft, Work & Play Retreat

모든 로우 삭제의 문제점

• DELETE FROM accommodation; 명령은 테이블의 모든 데이터를 삭제합니다. 조건 없이 모든 데이터를 삭제하므로 안전 장치가 필요하며, 테이블을 삭제하는 것이 아니라 데이터를 삭제한다는 점을 유의해야 합니다.

테이블의 모든 로우 개수 확인

• 테이블의 모든 로우 개수를 확인하기 위해서는 SELECT COUNT(*) FROM 테이블명을 사용합니다.

SELECT COUNT(*) FROM accommodation;

모든 도시의 갯수 확인

• 테이블에 존재하는 모든 고유한 도시의 개수를 확인하려면 SELECT COUNT(DISTINCT city)를 사용합니다.

SELECT COUNT(DISTINCT city) FROM accommodation;

PostgreSQL의 데이터 타입 관련 퀴즈

• VARCHAR(x) 데이터 타입은 최대 x 길이의 문자열을 저장할 수 있으며, 가변 길이입니다. x 이상의 문자열은 저장할 수 없습니다.

PostgreSQL serial 데이터 타입

• serial 데이터 타입은 자동 증가하는 정수를 생성하며, 보통 기본 키로 사용됩니다. 음수 값을 저장하지 않습니다.

SQL 문법 오류 찾기

• 잘못된 SQL 문법 예시: REMOVE FROM accommodation WHERE id = 1;는 존재하지 않습니다. 데이터를 삭제하려면 DELETE를 사용해야 합니다.

DELETE FROM accommodation WHERE id = 1;

이번 글에서는 Supabase와 PostgreSQL의 개념을 이해하고, SQL을 활용하여 테이블을 생성하고 데이터를 조작하는 방법을 실습했습니다. 또한 관계형 데이터베이스의 주요 특징과 SQL 쿼리의 기본 개념을 퀴즈 형식으로 정리했습니다. 실습과 퀴즈를 통해 데이터베이스의 작동 방식을 보다 깊이 이해할 수 있었기를 바랍니다.