CodeBlanks Logo Icon CodeBlanks
Login Register

Searched CASE

Write the searched form of CASE.

Searched CASE

Searched CASE takes an independent condition in each WHEN. It is the most flexible CASE form.

SELECT name,
  CASE WHEN price < 50 THEN 'ucuz'
       WHEN price < 200 THEN 'orta'
       ELSE 'pahalı' END AS tier
FROM products;

This query produces three price tiers.

name, CASE WHEN price < 50 THEN 'ucuz' WHEN price < 200 … is selected from products.

Searched CASE — details

In searched CASE each WHEN holds a separate boolean condition. The first true condition's THEN runs.

Conditions are tried from top to bottom, so WHEN price < 50 must come before WHEN price < 100, otherwise the first one never runs.

Practice Searched CASE

Write a three-tier CASE WHEN price < 50 THEN 'ucuz' WHEN price < 200 THEN 'orta' ELSE 'pahalı' END.

Starter code

query.sql
-- three tiers
SELECT name,
  CASE

Sample solution

query.sql
SELECT name,
  CASE WHEN price < 50 THEN 'ucuz'
       WHEN price < 200 THEN 'orta'
       ELSE 'pahalı' END AS tier
FROM products;

Expected output

name    tier
Kalem   ucuz
Telefon orta
Laptop  pahalı

Questions and answers

Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.

1 Add WHEN

Question 
CASE ____ price < 50 THEN 'ucuz'
Answer 
CASE WHEN price < 50 THEN 'ucuz'

Explanation: In CASE each condition starts with WHEN and gives its result with THEN.

2 What does WHEN take in searched CASE?

  • A condition Correct answer
  • A value
  • A column
  • A table

Explanation: In searched CASE WHEN takes a condition.

3 Searched CASE: match the terms

Term Meaning
searched conditional
WHEN condition
THEN result
ELSE default

All lessons in Intermediate

1 INNER JOIN in depth

  1. INNER JOIN logic
  2. Choosing the join column
  3. Three-table INNER JOIN
  4. Self JOIN
  5. Filtering joins
  6. Summary - INNER JOIN

2 LEFT and RIGHT JOIN

  1. LEFT JOIN
  2. RIGHT JOIN
  3. Unmatched rows
  4. JOINs with NULL
  5. LEFT vs RIGHT
  6. Summary - LEFT and RIGHT JOIN

3 FULL and CROSS JOIN

  1. FULL OUTER JOIN
  2. CROSS JOIN
  3. When to CROSS JOIN
  4. Comma JOIN
  5. Choosing a JOIN type
  6. Summary - FULL and CROSS JOIN

4 Project: Library database

  1. Library design
  2. Books and authors
  3. List loans with LEFT JOIN
  4. Find overdue loans
  5. Member summary

5 GROUP BY

  1. What is GROUP BY?
  2. Single-column grouping
  3. Multi-column grouping
  4. GROUP BY with aggregates
  5. Ordering grouped results
  6. Summary - GROUP BY

6 HAVING

  1. What is HAVING?
  2. HAVING vs WHERE
  3. HAVING with aggregates
  4. Multiple conditions
  5. HAVING with aliases
  6. Summary - HAVING

7 Aggregate functions

  1. COUNT
  2. SUM
  3. AVG
  4. MIN and MAX
  5. NULL and aggregates
  6. Summary - Aggregates

8 Project: Sales report

  1. Sales design
  2. Daily revenue
  3. Best sellers
  4. Customer segments
  5. Low performers

9 Subquery

  1. What is a subquery?
  2. Scalar subquery
  3. Subquery with IN
  4. ANY and ALL
  5. Correlated subquery
  6. Summary - Subquery

10 EXISTS

  1. What is EXISTS?
  2. NOT EXISTS
  3. EXISTS vs IN
  4. EXISTS performance
  5. Multiple EXISTS
  6. Summary - EXISTS

11 UNION and INTERSECT

  1. UNION
  2. UNION ALL
  3. INTERSECT
  4. EXCEPT
  5. Set rules
  6. Summary - UNION and INTERSECT

12 CASE

  1. What is CASE?
  2. Searched CASE
  3. Simple CASE
  4. The ELSE branch
  5. CASE with aggregates
  6. Summary - CASE

13 VIEW

  1. What is a VIEW?
  2. CREATE VIEW
  3. Querying a VIEW
  4. DROP VIEW
  5. Updatable VIEWs
  6. Summary - VIEW

14 INDEX basics

  1. What is an INDEX?
  2. CREATE INDEX
  3. Composite INDEX
  4. DROP INDEX
  5. When to index
  6. Summary - INDEX

15 Project: Blog database

  1. Blog design
  2. Post list
  3. Tag search
  4. Author stats
  5. Search index

16 Normalization

  1. What is normalization?
  2. First normal form
  3. Second normal form
  4. Third normal form
  5. BCNF
  6. Summary - Normalization

17 Data integrity

  1. What is integrity?
  2. Entity integrity
  3. Referential integrity
  4. Domain integrity
  5. Integrity with triggers
  6. Summary - Data integrity

18 Transaction basics

  1. What is a transaction?
  2. BEGIN and COMMIT
  3. ROLLBACK
  4. SAVEPOINT
  5. ACID
  6. Summary - Transaction

19 ALTER and DROP

  1. ALTER TABLE
  2. Adding a column
  3. Dropping a column
  4. Changing a column type
  5. DROP TABLE
  6. Summary - ALTER and DROP

20 Intermediate final

  1. Multi-JOIN report
  2. Aggregate analysis
  3. Subquery challenge
  4. Transaction scenario