Database · Lesson 02

SQL Basics

CREATE TABLE, INSERT, SELECT, UPDATE and DELETE statements.

Database2 min readFree lesson

SQL is a small language with a large reach. Five statements cover most day-to-day work: CREATE TABLE, INSERT, SELECT, UPDATE and DELETE.

Learning objectives

Create tables with sensible types and constraints.
Insert, read, update and delete rows.
Filter with WHERE, sort with ORDER BY and limit results.
Avoid the two classic accidents: updating or deleting everything.

1. CREATE TABLE

DROP TABLE IF EXISTS books;

CREATE TABLE books (
    id        INTEGER PRIMARY KEY AUTOINCREMENT,
    title     TEXT    NOT NULL,
    author    TEXT    NOT NULL,
    price     INTEGER DEFAULT 0,
    category  TEXT,
    isbn      TEXT    UNIQUE,
    published TEXT
);

SQLite types are flexible: INTEGER, TEXT, REAL, BLOB, NULL. Dates are usually stored as ISO text (2026-10-21) or Unix timestamps.

2. INSERT

INSERT INTO books (title, author, price, category, published) VALUES
    ('The Pragmatic Programmer', 'Hunt & Thomas', 30000, 'Programming', '1999-10-30'),
    ('Clean Code', 'Robert C. Martin', 35000, 'Programming', '2008-08-01'),
    ('Atomic Habits', 'James Clear', 14000, 'Self-help', '2018-10-16');

Name the columns explicitly. A table change later will not silently shift your values.

3. SELECT

SELECT * FROM books;
SELECT title, price FROM books;

SELECT title, author, price
FROM books
WHERE category = 'Programming'
ORDER BY price DESC
LIMIT 2;

Execution order is roughly FROM → WHERE → SELECT → ORDER BY → LIMIT. That is why an alias defined in SELECT cannot always be used in WHERE.

Filters worth knowing:

WHERE price >= 20000
WHERE price BETWEEN 10000 AND 30000
WHERE category IN ('Programming', 'Self-help')
WHERE title LIKE '%Code%'        -- % = any characters, _ = one character
WHERE isbn IS NULL               -- never use = NULL

4. UPDATE

UPDATE books
SET price = 32000
WHERE id = 2;

Without WHERE, every row changes. Before running an update, run the same WHERE in a SELECT and confirm it matches exactly the rows you expect.

5. DELETE

DELETE FROM books WHERE id = 3;

DELETE FROM books; empties the table. There is no undo outside a transaction.

6. Aggregates

SELECT COUNT(*) AS total FROM books;
SELECT AVG(price) AS average_price FROM books;
SELECT category, COUNT(*) AS n, MAX(price) AS dearest
FROM books
GROUP BY category;

COUNT, SUM, AVG, MIN and MAX collapse many rows into one. With GROUP BY, they produce one row per group.

7. Practical habits

Use a WHERE clause in every UPDATE and DELETE.
Prefer explicit column lists over SELECT * in application queries.
Keep a schema.sql in the repository so a fresh database can be rebuilt in one command.
Run queries in a scratch database first; production is not a playground.

Practice

Create the books table and answer the queries in the assignment. Keep the file so later lessons can reuse it.

Example files

Complete, runnable files used in this lesson. Copy them into your project and experiment.

queries.sqlSQL

-- ============================================
-- 강의02 예제: 도서 관리 데이터베이스
-- ============================================

-- ─────────────────────────────────────────
-- 1. 테이블 생성
-- ─────────────────────────────────────────
DROP TABLE IF EXISTS books;

CREATE TABLE books (
    id        INTEGER PRIMARY KEY AUTOINCREMENT, -- 도서 고유 번호
    title     TEXT    NOT NULL,                  -- 도서 제목
    author    TEXT    NOT NULL,                  -- 저자
    price     INTEGER DEFAULT 0,                 -- 가격
    category  TEXT,                              -- 분류 (예: '프로그래밍', '소설')
    isbn      TEXT    UNIQUE,                    -- ISBN (중복 불가)
    published TEXT                               -- 출판일
);

-- ─────────────────────────────────────────
-- 2. 더미 데이터 삽입
-- ─────────────────────────────────────────
INSERT INTO books (title, author, price, category, published) VALUES
    ('클린 코드',               '로버트 마틴',      30000, '프로그래밍', '2008-08-01'),
    ('리팩터링',                '마틴 파울러',      35000, '프로그래밍', '2018-11-20'),
    ('자바스크립트 완벽 가이드', '데이비드 플래너건', 45000, '프로그래밍', '2020-07-26'),
    ('파이썬 입문',             '귀도 반 로썸',     28000, '프로그래밍', '2019-03-15'),
    ('채식주의자',              '한강',             14000, '소설',       '2007-10-30'),
    ('82년생 김지영',           '조남주',           14800, '소설',       '2016-10-14'),
    ('어린 왕자',               '생텍쥐페리',       12000, '소설',       '1943-04-06'),
    ('사피엔스',                '유발 하라리',      22000, '역사',       '2011-01-01'),
    ('총, 균, 쇠',              '재레드 다이아몬드', 26000, '역사',       '1997-03-01'),
    ('코스모스',                '칼 세이건',        25000, '과학',       '1980-10-01');

-- ─────────────────────────────────────────
-- 3. 다양한 SELECT 쿼리
-- ─────────────────────────────────────────

-- 3-1. 전체 도서 목록
SELECT * FROM books;

-- 3-2. 제목과 가격만 조회
SELECT title, price FROM books;

-- 3-3. 프로그래밍 카테고리 도서만 조회
SELECT title, author, price
FROM books
WHERE category = '프로그래밍';

-- 3-4. 가격이 20000원 이상인 도서 (저렴한 순)
SELECT title, price
FROM books
WHERE price >= 20000
ORDER BY price ASC;

-- 3-5. 제목에 '코드'가 들어간 도서
SELECT title, author
FROM books
WHERE title LIKE '%코드%';

-- 3-6. 가장 비싼 도서 TOP 3
SELECT title, author, price
FROM books
ORDER BY price DESC
LIMIT 3;

-- 3-7. 2010년 이후 출판된 도서 (날짜 문자열 비교)
SELECT title, published
FROM books
WHERE published >= '2010-01-01'
ORDER BY published DESC;

-- ─────────────────────────────────────────
-- 4. UPDATE 예제
-- ─────────────────────────────────────────

-- '클린 코드' 가격을 32000원으로 인상
UPDATE books SET price = 32000 WHERE title = '클린 코드';

-- 소설 카테고리 전체 가격 10% 인상
UPDATE books
SET price = CAST(price * 1.1 AS INTEGER)
WHERE category = '소설';

-- ─────────────────────────────────────────
-- 5. DELETE 예제
-- ─────────────────────────────────────────

-- 특정 도서 삭제 (어린 왕자)
DELETE FROM books WHERE title = '어린 왕자';

-- 현재 남아있는 도서 확인
SELECT id, title, category, price FROM books ORDER BY id;

Assignment

Assignment 02 — Books table and queries

Write queries.sql that creates a books table, fills it with ten real books and answers eight questions.

Setup

DROP TABLE IF EXISTS books;

CREATE TABLE books (
    id        INTEGER PRIMARY KEY AUTOINCREMENT,
    title     TEXT    NOT NULL,
    author    TEXT    NOT NULL,
    price     INTEGER DEFAULT 0,
    category  TEXT,
    isbn      TEXT    UNIQUE,
    published TEXT
);

Insert at least ten rows across at least three categories.

Queries

Every column of every book.
Only title, author and price.
All Programming books, cheapest first.
Books costing 20,000 or more, most expensive first.
Books whose title contains Code.
Books published between 2010 and 2020.
The number of books and the average price per category.
The three most expensive books.

Mutations

Increase the price of one specific book by 10%.
Delete one book by id.
Show the table again to prove both changes worked.

Rules

Every UPDATE and DELETE must have a WHERE clause.
Use explicit column names in INSERT.
Add -- comments naming each query.

Done when

The file runs top to bottom with no errors.
Query 7 returns one row per category.
Running the file twice recreates the same database (thanks to DROP TABLE IF EXISTS).

Show the reference solution

Try the assignment yourself first — comparing your attempt with a working solution is where most of the learning happens.

queries.sqlSQL

-- ============================================
-- 과제02 정답: 쇼핑몰 상품 DB 조회
-- ============================================

-- 테이블 및 데이터 준비
DROP TABLE IF EXISTS products;

CREATE TABLE products (
    id       INTEGER PRIMARY KEY AUTOINCREMENT,
    name     TEXT    NOT NULL,
    price    INTEGER NOT NULL,
    category TEXT    NOT NULL,
    stock    INTEGER DEFAULT 0,
    rating   REAL    DEFAULT 0.0
);

INSERT INTO products (name, price, category, stock, rating) VALUES
    ('삼성 노트북',    1200000, '전자제품', 15, 4.5),
    ('애플 맥북',      2000000, '전자제품',  8, 4.8),
    ('LG 모니터',       450000, '전자제품', 20, 4.3),
    ('로지텍 마우스',    45000, '전자제품', 100, 4.1),
    ('기계식 키보드',   120000, '전자제품', 35, 4.6),
    ('나이키 운동화',   150000, '의류',      30, 4.4),
    ('아디다스 티셔츠',  45000, '의류',      80, 4.0),
    ('청바지',          89000, '의류',       60, 4.2),
    ('파이썬 입문서',   28000, '도서',       40, 4.7),
    ('자바스크립트 완벽 가이드', 45000, '도서', 25, 4.5),
    ('클린 코드',       32000, '도서',       55, 4.9),
    ('프라이팬',        35000, '주방',       70, 4.3),
    ('전기밥솥',       180000, '주방',       25, 4.6),
    ('믹서기',          55000, '주방',        0, 3.8);

-- ─────────────────────────────────────────
-- 쿼리 1. 가격 범위 필터
-- 50,000원 이상 200,000원 이하 상품을 가격 오름차순 정렬
-- BETWEEN을 사용하면 이상/이하 범위를 간결하게 표현 가능
-- ─────────────────────────────────────────
SELECT name, price, category
FROM products
WHERE price BETWEEN 50000 AND 200000  -- 50,000 ~ 200,000 범위 필터
ORDER BY price ASC;                   -- 가격 오름차순 정렬

-- ─────────────────────────────────────────
-- 쿼리 2. 카테고리 + 재고 복합 필터
-- 전자제품 중 재고 20개 이상인 상품
-- AND로 두 조건을 동시에 적용
-- ─────────────────────────────────────────
SELECT name, price, stock
FROM products
WHERE category = '전자제품'  -- 카테고리가 전자제품
  AND stock >= 20;           -- 재고 20개 이상

-- ─────────────────────────────────────────
-- 쿼리 3. 품절 상품 조회
-- stock이 0인 상품 → 재고 없음 = 품절
-- ─────────────────────────────────────────
SELECT name, category
FROM products
WHERE stock = 0;  -- 재고가 정확히 0인 상품

-- ─────────────────────────────────────────
-- 쿼리 4. 평점 필터 + 정렬 + 개수 제한
-- 평점 4.5 이상을 내림차순 정렬 후 상위 3개
-- ORDER BY + LIMIT 조합으로 순위 조회
-- ─────────────────────────────────────────
SELECT name, rating, price
FROM products
WHERE rating >= 4.5        -- 평점 4.5 이상
ORDER BY rating DESC       -- 높은 평점 먼저
LIMIT 3;                   -- 상위 3개만

-- ─────────────────────────────────────────
-- 쿼리 5. 이름 패턴 검색
-- '스'가 포함된 상품명 검색
-- LIKE '%스%': 앞뒤에 무엇이 오든 '스'가 들어가면 매칭
-- ─────────────────────────────────────────
SELECT name, price, category
FROM products
WHERE name LIKE '%스%';  -- 이름에 '스'가 포함된 상품

Learn this lesson with a mentor

Work through the Build Your Homepage series on your own, or join a guided cohort at our Pitampura campus and online.

Enroll now Stage overview