Pulling Data Out With SELECT

Goal

You connect to PostgreSQL yourself, pick out only the rows you want with SELECT, WHERE, DISTINCT, and ORDER BY, and learn to save the results as views.

Why it matters

SQL is declarative, not imperative. Instead of "open this file and read and compare line by line", you write "give me the rows that satisfy this condition", and the database decides how to find them based on the statistics at that moment. So being good at SQL is the ability to describe exactly the set you want, not the ability to know fast algorithms.

What to watch especially in this lab is NULL. NULL is closer to "unknown" than to "no value", so it is never true when compared with any value. This one property spreads through queries, aggregation, and joins, so it is good to confirm it by hand now.

There is also a reason to save results as views. A view does not keep a copy of the result but gives a name to the query itself, so when the source changes, the view's result changes along with it. This property is also why the grading script can compare the views you created against the source.

Steps

1. Count the total number of rows in the customers table and save only that number to /root/rowcount.txt.
2. Create a view containing only the customers whose is_active is true, named v_active_customers. Keep the columns as in the original.
3. Create a view containing only the customers whose country is KR and whose tier is gold, named v_kr_gold.
4. Create a view containing only the customers whose email is empty, named v_missing_email.
5. From products, take the category values without duplicates into a view named v_categories. The only column must be category.
6. Create a view containing the products whose is_active is true and whose price is 200000 or more, named v_expensive.
7. Create a view containing the orders whose ordered_at is at or after TIMESTAMPTZ '2025-07-01 00:00:00+09' (equal values included), named v_recent_orders.
8. For customers whose country is KR and whose is_active is true, create a view containing their id, name, and tier, in this order, named v_kr_report. Sort by tier ascending, and by id ascending when they are equal.

Notes

• Connect with psql -h 127.0.0.1 -U lab -d labdb (the password is lab; using the environment variable PGPASSWORD is convenient).
• You can list tables with \dt and see a table's column structure with \d customers.
• To extract only the values into a file, the form psql -tAc "select ..." > 파일 is convenient (the Korean word means "file").
• Common mistake 1: WHERE email = NULL raises no error and just returns 0 rows.
• Common mistake 2: when recreating a view, use CREATE OR REPLACE VIEW, but if the number or names of the columns change, you must first run DROP VIEW.

Count the customers

Count the total number of rows in the customers table and save only that number to /root/rowcount.txt.

By combining the aggregate function that counts rows with psql's options that print only the result (-t, -A), you can leave just the number in the file.

Create a view of active customers

Create a view containing only the customers whose is_active is true, named v_active_customers. Keep the columns as in the original.

A boolean column can be used in a condition as it is, without adding = true. A view has the form CREATE VIEW name AS SELECT ...

Apply two conditions together

Create a view containing only the customers whose country is KR and whose tier is gold, named v_kr_gold.

Both conditions must be satisfied, so join them with AND. String comparison is case-sensitive.

Find customers without an email

Create a view containing only the customers whose email is empty, named v_missing_email.

NULL is never true when compared with any value. You need a dedicated operator instead of the equals sign.

Extract the list of categories

From products, take the category values without duplicates into a view named v_categories. The only column must be category.

Put the keyword that removes duplicates right after SELECT. The result must have one column, so do not include other columns.

Find expensive products that are on sale

Create a view containing the products whose is_active is true and whose price is 200000 or more, named v_expensive.

You must apply both the price condition and the on-sale condition. Since it is 200,000 won or more, the boundary value is included.

Pick orders after a specific point in time

Create a view containing the orders whose ordered_at is at or after TIMESTAMPTZ '2025-07-01 00:00:00+09' (equal values included), named v_recent_orders.

ordered_at is a type that includes the time zone. You must also state the time zone in the comparison value so that you get the same result regardless of the server settings.

Finish with a view for a report

For customers whose country is KR and whose is_active is true, create a view containing their id, name, and tier, in this order, named v_kr_report. Sort by tier ascending, and by id ascending when they are equal.

Pick only the columns you need and match the aliases. When there are two sort keys, list them in ORDER BY separated by a comma.