Lesson 0: Introduction to Databases and SQL

This lesson is a starting point for those new to SQL. We proceed in the order of concept, practice, and terminology review.

What Is a Database?

Why Not Just Use a Spreadsheet?

Imagine managing a customer list in a spreadsheet at work. It works fine at first. But over time:

• When you reach 50,000 customers, the file gets heavy and takes 30 seconds just to open
• The marketing team and CS team cannot edit the same file simultaneously
• If someone accidentally breaks a formula, the entire dataset is ruined
• To find "VIP customers over 30 who ordered this month," you need to apply multiple filters

A database fundamentally solves these problems:

Websites, online stores, banks, hospitals, games -- behind almost every piece of software that handles data, there is a database.

Why Split into Multiple Tables?

In a database, instead of putting all data in one big spreadsheet, data is stored split across multiple tables. Let's compare using TechShop data:

Bad example -- everything in one table:

If 정준호's email changes? You'd have to update both orders ORD-001 and ORD-002. If there are 100 orders, you'd have to update 100 places.

Good example -- separated tables:

customers table:

orders table:

If the email changes, you update only one place in the customers table. Since customer_id in the orders table points to id in the customers table, the relationship is automatically maintained.

This is the core of a relational database (RDBMS):

• Reduces duplication -- customer information is stored in only one place
• Maintains consistency -- only one place needs to be updated
• Enables flexible combinations -- tables can be joined as needed

What Is SQL?

SQL (Structured Query Language, pronounced "ess-queue-ell" or "sequel") is the language for communicating with databases.

When you want to say "Show me the names and emails of customers with VIP grade," in SQL you write:

SELECT name, email
FROM customers
WHERE grade = 'VIP';

SELECT (show me) FROM (from where) WHERE (condition) -- it reads like an English sentence. SQL is the closest to natural language among programming languages.

What SQL Can Do

These four operations are called CRUD (Create, Read, Update, Delete) and form the core of SQL.

How Is SQL Executed?

When you first encounter SQL, you might wonder "where do I type this?" The flow is simple:

1. You type a SQL statement in a SQL tool (DBeaver, DB Browser, etc.)
2. The tool sends the SQL to the database engine
3. The engine parses and executes the SQL, then returns the result
4. The result is displayed on screen in table format

Why Learn SQL?

• Universality -- Used across almost all databases: MySQL, PostgreSQL, SQLite, Oracle, etc. Learn it once, use it anywhere
• Demand -- Developers, data analysts, marketers, PMs -- nearly every role in the IT industry requires SQL
• Efficiency -- Tasks that take 30 minutes in a spreadsheet can be done in 1 second with a single SQL statement
• History -- A proven technology that has been in use for over 50 years since the 1970s. It is unlikely to disappear

Key takeaway: Learning SQL means learning how to ask questions of your data.

Try It Yourself -- Your First Query

This tutorial uses the database of a fictional e-commerce store called TechShop. It is a 10-year-old online shop selling computers and peripherals, containing realistic data including customers, products, orders, payments, shipping, reviews, and more. The full structure can be found at Database Schema.

You don't need to know the syntax yet. Copy the query below into your SQL tool and run it:

-- Retrieve 3 customers
SELECT id, name, email, grade
FROM customers
LIMIT 3;

This means "show me the id, name, email, and grade columns from the customers table, limited to 3 rows." If you got a result, congratulations -- you just ran your first SQL query!

Breaking Down the Result

Let's review the result from the first query and define our terminology.

Data Types

Each column has a defined kind of data it can store:

NULL -- "No Value"

There is one more important concept in databases: NULL.

NULL means "the value is missing." It is different from 0 or an empty string (''):

In TechShop's customers table:

• Customers with NULL birth_date -- customers who did not enter their date of birth (about 15%)
• Customers with NULL gender -- customers who did not select a gender (about 10%)
• Customers with NULL last_login_at -- customers who have never logged in

You will learn how to properly handle NULL in Lesson 6: Handling NULL.

Connecting Tables -- PK and FK

Earlier we said "tables are separated and connected by relationships." Let's see how they connect.

Primary Key

The id column in the result we just queried had the numbers 1, 2, 3. This is the primary key (PK). It is a column that uniquely identifies each row.

• id = 2 points to only one person, 김민재
• Even if there are multiple people named "김민재," they can be precisely distinguished by id
• No two rows can have the same id, and no row can lack an id

Foreign Key

The customer_id in the orders table points to id in the customers table. This is the foreign key (FK).

Orders 1 and 2 with customer_id = 1 belong to 정준호. The fact that one customer can have multiple orders is called a 1:N (one-to-many) relationship.

PK and FK Summary

Summary

In the next lesson, we will learn SELECT in earnest. Building on the first query you just tried, you will master how to retrieve data freely.

Problem 1

Which of the following is NOT an appropriate reason to use a database?

• (A) It can quickly search through millions of records
• (B) Multiple people can read and write data simultaneously
• (C) File size is always smaller than a spreadsheet
• (D) Complex conditions can be extracted precisely with SQL

Problem 2

Which of the following is NOT one of the four basic SQL operations (CRUD)?

• (A) SELECT
• (B) INSERT
• (C) SORT
• (D) DELETE

Problem 3

In the following table, how many rows and how many columns are there?

• (A) 3 rows, 4 columns
• (B) 4 rows, 4 columns
• (C) 3 rows, 3 columns
• (D) 12 rows, 1 column

Problem 4

The id column of the customers table is a primary key (PK). Which of the following is NOT a characteristic of a primary key?

• (A) It uniquely identifies each row
• (B) It cannot have a NULL value
• (C) Multiple primary keys can be created in one table
• (D) It cannot have duplicate values

Problem 5

The customer_id in the orders table references the id in the customers table. What is customer_id?

• (A) Primary Key
• (B) Foreign Key
• (C) Data Type
• (D) Table Name

Problem 6

One customer can place multiple orders, and each order belongs to only one customer. What is this type of relationship called?

• (A) 1:1 relationship
• (B) 1:N relationship
• (C) N:N relationship
• (D) No relationship

Problem 7

In the customers table, some customers have a NULL birth_date column. What does NULL mean?

• (A) The date of birth is 0000-00-00
• (B) The date of birth is an empty string ('')
• (C) There is no date of birth value (not entered)
• (D) The date of birth is today's date

Problem 8

Which of the following is NOT an appropriate reason for splitting data into multiple tables in a relational database?

• (A) To reduce data duplication
• (B) Because consistency is maintained by updating only one place
• (C) To use more storage space
• (D) To flexibly combine tables as needed

Problem 9

In the customers table, the data type of id is INTEGER and the data type of name is TEXT. Which value cannot be stored in an INTEGER column?

• (A) 42
• (B) 0
• (C) -7
• (D) '김민재'

Problem 10

What is the correct interpretation of the SQL statement SELECT name, email FROM customers WHERE grade = 'VIP';?

• (A) It deletes the customers table
• (B) It retrieves the name and email of customers with VIP grade from the customers table
• (C) It changes all customer grades to VIP in the customers table
• (D) It adds a VIP customer to the customers table

Scoring Guide

Check your next step based on the number of correct answers:

Problem Areas:

Next: Lesson 1: SELECT Basics