Filtering and Expressions

Goal

You apply BETWEEN, IN, LIKE, COALESCE, CASE, IS DISTINCT FROM, and LIMIT/OFFSET to real data and learn to describe exactly the set you want.

Why it matters

Writing a condition in SQL is defining a set. And in this set operation a third truth value that other languages lack gets involved. A comparison that involves NULL becomes neither true nor false but unknown, and WHERE lets through only rows that are true, so unknown is silently filtered out.

Because of this property, adding the row counts of city = '서울' and city <> '서울' does not give the total. This is because there are NULL rows that belong to neither. Bugs of this kind are found late because they raise no error and only the numbers are silently wrong. If you confirm it yourself in this lab, you will have the first place to suspect later when a report's numbers do not match.

Steps

1. Create a view containing the products whose price is between 50000 and 150000 inclusive, named v_mid_price.
2. Create a view containing the orders whose status is paid or shipped, named v_multi_status.
3. Create a view containing the products whose name contains the characters 프로 (Korean for "pro"), named v_pro_products.
4. Make a view containing only the customers' id and city columns, replacing city with 미상 (Korean for "unknown") when it is empty, named v_city_filled. The column names are id and city.
5. Create a view containing each order's id and its size class, named v_order_size. The columns are id and size; if total_amount is 1000000 or more the class is 대형 (large), if 300000 or more 중형 (medium), and otherwise 소형 (small).
6. Create a view containing the customers whose city is not 서울 (Seoul), named v_not_seoul. Customers whose city is empty must be included too.
7. Sort products by price descending and, when equal, by id ascending, and create a view containing page 3 of that sorting (20 rows per page), named v_page3.
8. For customers whose country is KR, whose is_active is true, whose signup_date is on or after 2024-07-01 (the same day included), and whose tier is gold or vip, create a view containing their id, name, tier, and signup_date, named v_target. Sort by signup_date descending and, when equal, by id ascending.

Notes

• Connect: PGPASSWORD=lab psql -h 127.0.0.1 -U lab -d labdb
• Even the column names and order of the views are graded, so match the aliases exactly.
• Common mistake 1: BETWEEN includes both end values. If you mistake it for "less than", the boundary rows go wrong.
• Common mistake 2: if you write step 6 as city <> '서울', customers with NULL are dropped. Count the rows to check.

Narrow down by price range

Create a view containing the products whose price is between 50000 and 150000 inclusive, named v_mid_price.

There is a keyword that writes a range condition at once. Check whether the end values are included.

Pick one of several values

Create a view containing the orders whose status is paid or shipped, named v_multi_status.

There is a keyword that expresses this as a list instead of using OR several times.

Find products whose name contains a specific string

Create a view containing the products whose name contains the characters 프로 (Korean for "pro"), named v_pro_products.

To find a partial match, you must attach the wildcard on both the front and the back.

Fill empty values with a default

Make a view containing only the customers' id and city columns, replacing city with 미상 (Korean for "unknown") when it is empty, named v_city_filled. The column names are id and city.

There is a function that returns a substitute value only when the value is NULL. Rows that have an original value must be left as they are.

Classify by condition

Create a view containing each order's id and its size class, named v_order_size. The columns are id and size; if total_amount is 1000000 or more the class is 대형 (large), if 300000 or more 중형 (medium), and otherwise 소형 (small).

CASE WHEN picks the first branch from the top that is true. If you write the larger ranges first, you can avoid overlap.

Flip it, NULL included

Create a view containing the customers whose city is not 서울 (Seoul), named v_not_seoul. Customers whose city is empty must be included too.

An ordinary inequality does not filter in rows that are NULL. There is an operator that compares NULL like an ordinary value.

Fetch page 3

Sort products by price descending and, when equal, by id ascending, and create a view containing page 3 of that sorting (20 rows per page), named v_page3.

You specify the number of rows to skip and the number of rows to fetch separately. If a page is 20 rows, how many rows must you skip for page 3?

A target list combining four conditions

For customers whose country is KR, whose is_active is true, whose signup_date is on or after 2024-07-01 (the same day included), and whose tier is gold or vip, create a view containing their id, name, tier, and signup_date, named v_target. Sort by signup_date descending and, when equal, by id ascending.

Join the conditions you used earlier with AND, pick only the needed columns in order, and specify two sort keys.