SQL과 함께하는 3일 차: 데이터 삽입, 스마트하게 쿼리하기, 실제 데이터셋 작업

Source: Dev.to

Day three was all about reinforcing the basics, not rushing forward, but getting comfortable with SQL by practicing it repeatedly on real data.

I spent most of the day working through SQLZoo exercises, inserting data into PostgreSQL, and querying a realistic dataset generated with Mockaroo. This helped bridge the gap between theory and actual usage.

테이블에 데이터 삽입

I practiced inserting data into tables and learned a very useful PostgreSQL feature: importing SQL files directly.

Using the psql terminal, you can run SQL files with:

\i path_to_file/file_name.sql

To test this, I downloaded a 1,000‑row dataset from Mockaroo, adjusted table parameters and constraints to match the data, and imported it into my database. Seeing a large dataset populate my tables instantly made everything feel much more real.

SELECT 로 데이터 조회 (다시, 하지만 더 나은 방식)

I continued practicing the now‑familiar SELECT statement, but with more structure and intention.

ORDER BY 로 결과 정렬

SELECT * FROM users ORDER BY first_name;

By default, sorting is ascending (ASC). To reverse the order:

SELECT * FROM users ORDER BY first_name DESC;

DISTINCT 로 고유값 가져오기

SELECT DISTINCT country FROM users;

WHERE 와 논리 연산자로 필터링

You can combine conditions using logical operators:

• AND
• OR

비교 연산자

• = (equal)
• “ (less than, greater than)
• =
• <> (not equal)

These operators are useful for filtering ages, dates, and numeric values.

LIMIT 와 OFFSET 로 결과 제한

SELECT * FROM users LIMIT 10 OFFSET 20;

• LIMIT controls how many rows are returned.
• OFFSET skips a specified number of rows.

LIMIT 대신 FETCH 사용

PostgreSQL also supports the SQL‑standard FETCH syntax:

SELECT * FROM users FETCH FIRST 6 ROWS ONLY;

IN 과 BETWEEN 으로 검색

Using IN

SELECT * FROM users WHERE country IN ('Nigeria', 'Ghana', 'Kenya');

Using BETWEEN

SELECT * FROM users WHERE dob BETWEEN '1990-01-01' AND '2000-12-31';

LIKE 와 ILIKE 로 패턴 매칭

• LIKE – case‑sensitive pattern matching
• ILIKE – case‑insensitive pattern matching

Wildcards:

• % – matches any number of characters
• _ – matches exactly one character

These are handy for querying email patterns and other text fields.

GROUP BY 로 결과 그룹화

SELECT
    country_of_birth,
    COUNT(country_of_birth) AS "Number of Users"
FROM users
GROUP BY country_of_birth
ORDER BY COUNT(country_of_birth);

This query shows how many users belong to each country, illustrating the power of aggregation.

마무리 생각

Day three reinforced an important lesson: mastery comes from repetition. Working with a real dataset made every query feel purposeful, and practicing the same concepts in different ways helped solidify my understanding. SQL is starting to feel less like syntax and more like a way of thinking.

Tomorrow I’ll be learning and practicing even more.