Mastering Conditional Logic with CASE Statements

Table of Contents

Introduction to CASE Statements

SQL Example

SELECT
    employee_id,
    first_name,
    last_name,
    salary,
    CASE 
        WHEN salary < 30000 THEN 'Low'
        WHEN salary BETWEEN 30000 AND 59999 THEN 'Medium'
        WHEN salary >= 60000 THEN 'High'
        ELSE 'Undefined'
    END as salary_group
FROM
    employees;

T-SQL (Microsoft SQL Server) Example

SELECT
    order_id,
    order_date,
    customer_id,
    CASE 
        WHEN order_date < '2022-01-01' THEN 'Old Order'
        WHEN order_date BETWEEN '2022-01-01' AND '2022-12-31' THEN 'Current Year Order'
        ELSE 'Future Order'
    END as order_status
FROM
    orders;

PL/pgSQL (PostgreSQL) Example

SELECT 
    product_id,
    product_name,
    price,
    CASE
        WHEN price < 10 THEN 'Budget'
        WHEN price BETWEEN 10 AND 50 THEN 'Standard'
        ELSE 'Premium'
    END as price_category
FROM
    products;

Pseudocode in General Programming Logic

function classify_grade(grade):
    CASE
        WHEN grade >= 90 THEN RETURN 'A'
        WHEN grade >= 80 THEN RETURN 'B'
        WHEN grade >= 70 THEN RETURN 'C'
        WHEN grade >= 60 THEN RETURN 'D'
        ELSE RETURN 'F'
    END CASE

JavaScript Example

function getLevel(score) {
    switch(true) {
        case (score >= 90):
            return 'A';
        case (score >= 80):
            return 'B';
        case (score >= 70):
            return 'C';
        case (score >= 60):
            return 'D';
        default:
            return 'F';
    }
}

Java Example

public String classifyAge(int age) {
    String ageGroup;
    switch(age){
        case 0 -> 12 -> ageGroup = "Child";
        case 13 -> 19 -> ageGroup = "Teenager";
        case 20 -> 64 -> ageGroup = "Adult";
        default -> ageGroup = "Senior";
    }
    return ageGroup;
}

Summary

The CASE statement is used to implement conditional logic in SQL and various programming languages, allowing for branching based on different conditions. The examples provided here can be immediately applied to categorize data and make decisions within your projects.

Practical Implementation of Basic Syntax and Structure of CASE Statements

SQL Example

SELECT employee_id,
       first_name,
       last_name,
       salary,
       CASE
           WHEN salary < 3000 THEN 'Low'
           WHEN salary BETWEEN 3000 AND 7000 THEN 'Medium'
           ELSE 'High'
       END AS salary_level
FROM employees;

Java Example

public class Main {
    public static void main(String[] args) {
        int score = 88;
        String grade;

        grade = switch (score / 10) {
            case 10, 9 -> "A";
            case 8 -> "B";
            case 7 -> "C";
            case 6 -> "D";
            default -> "F";
        };

        System.out.println("Grade: " + grade);
    }
}

Python Example

def get_grade(score):
    if score >= 90:
        return 'A'
    elif score >= 80:
        return 'B'
    elif score >= 70:
        return 'C'
    elif score >= 60:
        return 'D'
    else:
        return 'F'

score = 88
grade = get_grade(score)
print(f"Grade: {grade}")

Pseudocode Example

score = 75
grade = ""

CASE
    WHEN score IS GREATER THAN 90 THEN
        grade = "A"
    WHEN score IS GREATER THAN 80 THEN
        grade = "B"
    WHEN score IS GREATER THAN 70 THEN
        grade = "C"
    WHEN score IS GREATER THAN 60 THEN
        grade = "D"
    ELSE
        grade = "F"
ENDCASE

PRINT "Grade: " + grade

Each example illustrates the use of CASE statements or its equivalent in different contexts for applying conditional logic.

Implementing CASE Statements in SQL

Practical Implementation

-- Selecting data and using CASE statements to apply conditional logic

SELECT 
    employee_id,
    first_name,
    last_name,
    salary,
    -- Using CASE to categorize employees based on their salaries
    CASE
        WHEN salary < 30000 THEN 'Low Salary'
        WHEN salary BETWEEN 30000 AND 70000 THEN 'Medium Salary'
        ELSE 'High Salary'
    END AS salary_category
FROM
    employees;

-- Using CASE in WHERE clause to filter data based on a condition
SELECT 
    order_id,
    customer_id,
    order_date,
    status,
    total_amount
FROM 
    orders
WHERE 
    CASE
        WHEN status = 'Shipped' THEN total_amount > 100
        ELSE total_amount > 50
    END;

-- Using CASE to apply transformations in the SELECT clause
SELECT 
    product_id,
    product_name,
    price,
    -- Discounted price based on the original price
    CASE
        WHEN price >= 100 THEN price * 0.9
        ELSE price
    END AS discounted_price
FROM 
    products;

-- Using nested CASE statements
SELECT 
    student_id,
    student_name,
    grade_level,
    -- Using nested CASE to provide detailed classification
    CASE
        WHEN grade_level = 'Freshman' THEN 
            CASE
                WHEN gpa >= 3.5 THEN 'Honors Freshman'
                ELSE 'Regular Freshman'
            END
        WHEN grade_level = 'Sophomore' THEN 
            CASE
                WHEN gpa >= 3.5 THEN 'Honors Sophomore'
                ELSE 'Regular Sophomore'
            END
        ELSE 'Upperclassman'
    END AS classification
FROM 
    students;

Practical Use Cases

The provided SQL statements show how to:

Select data and use CASE to create derived columns.
Filter records with CASE in the WHERE clause.
Transform data directly in the SELECT clause.
Implement nested CASE statements for more detailed logic.

You can use these patterns directly in your SQL queries to implement complex conditional logic.

Conditional Logic with CASE in Programming Languages

SQL Implementation

SELECT 
    order_id,
    customer_id,
    order_total,
    CASE
        WHEN order_total >= 100 THEN 'High Value'
        WHEN order_total >= 50 THEN 'Medium Value'
        ELSE 'Low Value'
    END AS order_value_category
FROM 
    orders;

Pseudocode Implementation

function categorizeOrderTotal(orderTotal)
    switch case
        case (orderTotal >= 100):
            return 'High Value'
        case (orderTotal >= 50):
            return 'Medium Value'
        default:
            return 'Low Value'

orders = [
    {order_id: 1, customer_id: 101, order_total: 120},
    {order_id: 2, customer_id: 102, order_total: 75},
    {order_id: 3, customer_id: 103, order_total: 45}
]

for each order in orders
    order_value_category = categorizeOrderTotal(order.order_total)
    print("Order ID: ", order.order_id, "  Value Category: ", order_value_category)

JavaScript Implementation

function categorizeOrderTotal(orderTotal) {
    switch (true) {
        case (orderTotal >= 100):
            return 'High Value';
        case (orderTotal >= 50):
            return 'Medium Value';
        default:
            return 'Low Value';
    }
}

const orders = [
    {order_id: 1, customer_id: 101, order_total: 120},
    {order_id: 2, customer_id: 102, order_total: 75},
    {order_id: 3, customer_id: 103, order_total: 45}
];

orders.forEach(order => {
    const orderValueCategory = categorizeOrderTotal(order.order_total);
    console.log(`Order ID: ${order.order_id}  Value Category: ${orderValueCategory}`);
});

Java Implementation

public class OrderValueCategorizer {

    public static String categorizeOrderTotal(int orderTotal) {
        switch (orderTotal) {
            case int n when n >= 100:
                return "High Value";
            case int n when n >= 50:
                return "Medium Value";
            default:
                return "Low Value";
        }
    }

    public static void main(String[] args) {
        int[][] orders = {
            {1, 101, 120},
            {2, 102, 75},
            {3, 103, 45}
        };

        for (int[] order : orders) {
            String orderValueCategory = categorizeOrderTotal(order[2]);
            System.out.println("Order ID: " + order[0] + "  Value Category: " + orderValueCategory);
        }
    }
}

C++ Implementation

#include <iostream>
#include <vector>
#include <tuple>
using namespace std;

string categorizeOrderTotal(int orderTotal) {
    switch (true) {
        case orderTotal >= 100:
            return "High Value";
        case orderTotal >= 50:
            return "Medium Value";
        default:
            return "Low Value";
    }
}

int main() {
    vector<tuple<int, int, int>> orders = {
        {1, 101, 120},
        {2, 102, 75},
        {3, 103, 45}
    };

    for (const auto& order : orders) {
        int orderId = get<0>(order);
        int orderTotal = get<2>(order);
        string orderValueCategory = categorizeOrderTotal(orderTotal);
        cout << "Order ID: " << orderId << "  Value Category: " << orderValueCategory << endl;
    }

    return 0;
}

PHP Implementation

<?php
function categorizeOrderTotal($orderTotal) {
    switch (true) {
        case ($orderTotal >= 100):
            return 'High Value';
        case ($orderTotal >= 50):
            return 'Medium Value';
        default:
            return 'Low Value';
    }
}

$orders = [
    ['order_id' => 1, 'customer_id' => 101, 'order_total' => 120],
    ['order_id' => 2, 'customer_id' => 102, 'order_total' => 75],
    ['order_id' => 3, 'customer_id' => 103, 'order_total' => 45]
];

foreach ($orders as $order) {
    $orderValueCategory = categorizeOrderTotal($order['order_total']);
    echo "Order ID: {$order['order_id']}  Value Category: {$orderValueCategory}n";
}
?>

Advanced Applications of CASE Statements

SQL

Nested CASE Statements:

SELECT 
    employee_id,
    department_id,
    CASE    
        WHEN department_id = 1 THEN 
            CASE 
                WHEN salary > 100000 THEN 'High Earner'
                ELSE 'Average Earner'
            END
        WHEN department_id = 2 THEN 'Finance'
        ELSE 'Other'
    END AS earnings_category
FROM employees;

CASE Statements in ORDER BY:

SELECT 
    employee_id,
    first_name,
    last_name,
    department_id
FROM employees
ORDER BY 
    CASE 
        WHEN department_id = 1 THEN 'A'
        WHEN department_id = 2 THEN 'B'
        ELSE 'C'
    END;

CASE Statements in UPDATE:

UPDATE employees
SET salary = 
    CASE
        WHEN department_id = 1 THEN salary * 1.10
        WHEN department_id = 2 THEN salary * 1.05
        ELSE salary
    END;

Programming Language (e.g., Pseudocode)

Nested CASE-like Statements:

function getEarningsCategory(department_id, salary):
    if department_id == 1:
        if salary > 100000:
            return 'High Earner'
        else:
            return 'Average Earner'
    elif department_id == 2:
        return 'Finance'
    else:
        return 'Other'

Using in a Loop:

salaries = [150000, 55000, 80000, 92000]
categories = []

for salary in salaries:
    if salary > 100000:
        categories.append('High Earner')
    elif salary > 70000:
        categories.append('Middle Earner')
    else:
        categories.append('Low Earner')

Advanced Function for Conditional Logic in Programming

Function with Switch-case:

function earningsCategory(department_id, salary) {
    switch(department_id) {
        case 1:
            return salary > 100000 ? 'High Earner' : 'Average Earner';
        case 2:
            return 'Finance';
        default:
            return 'Other';
    }
}

Handling Multiple Conditions Together

Complex Conditional Logic:

SELECT 
    employee_id,
    department_id,
    CASE WHEN department_id = 1 AND salary > 100000 THEN 'High Earner in Sales'
         WHEN department_id = 2 AND salary > 100000 THEN 'High Earner in Finance'
         WHEN department_id = 3 AND salary > 100000 THEN 'High Earner in IT'
         ELSE 'Other'
    END AS earnings_category
FROM employees;

Use these advanced application examples to effectively implement complex conditional logic with CASE statements in both SQL and general programming languages.

Debugging and Best Practices for CASE Statements

SQL Example

-- Example of a CASE statement in a SQL query
SELECT 
    order_id,
    customer_id,
    order_status,
    CASE 
        WHEN order_status = 'Shipped' THEN 'Completed'
        WHEN order_status = 'Pending' THEN 'In Progress'
        ELSE 'Unknown Status'
    END AS order_status_desc
FROM 
    orders;

Debugging Tips (SQL)

Null Handling: Ensure to account for NULL values:

CASE 
    WHEN order_status IS NULL THEN 'No Status'
    WHEN order_status = 'Shipped' THEN 'Completed'
    -- other conditions
END

Order of Conditions: Place the most specific conditions before the more general ones.

CASE 
    WHEN order_status = 'Shipped' THEN 'Completed'
    WHEN order_status = 'Shipped' AND delivery_date IS NULL THEN 'Inspection Pending'
    -- other conditions
END

Syntax Errors: Ensure all keywords and conditions are correctly typed and error-free.

Programming Languages Example (JavaScript)

function orderStatusDescription(orderStatus) {
    switch(orderStatus) {
        case 'Shipped':
            return 'Completed';
        case 'Pending':
            return 'In Progress';
        default:
            return 'Unknown Status';
    }
}

Debugging Tips (Programming Languages)

Default Case: Always provide a default case for handling unexpected values.

switch(orderStatus) {
    case 'Shipped':
        //...
    default:
        console.log('Unexpected status:', orderStatus);
        //...
}

Check Input Types: Verify the type of the input being fed into the CASE (or switch) block.
```
if (typeof orderStatus !== 'string') {
    throw new Error('Invalid input type');
}
```

Consistency in Data: Ensure inputs are normalized (e.g., case sensitivity).

switch(orderStatus.toLowerCase()) {
    case 'shipped':
        return 'Completed';
    //...
}

Best Practices

Performance: For SQL, use appropriate indexes if CASE statements are part of your query’s WHERE clause.
Readability: Keep CASE blocks easily readable by keeping the logic straightforward and well-commented.
Modularity: For programming languages, encapsulate CASE logic in functions or methods for reusability and maintainability.

By following these practical implementations, debugging tips, and best practices, you should be able to effectively leverage CASE statements in both SQL and various programming languages.

Mastering SQL: Utilizing Subqueries and Temporary Tables

« Older Entries

Mastering Conditional Logic with CASE Statements

Introduction to CASE Statements

SQL Example

T-SQL (Microsoft SQL Server) Example

PL/pgSQL (PostgreSQL) Example

Pseudocode in General Programming Logic

JavaScript Example

Java Example

Summary

Practical Implementation of Basic Syntax and Structure of CASE Statements

SQL Example

Java Example

Python Example

Pseudocode Example

Implementing CASE Statements in SQL

Practical Implementation

Practical Use Cases

Conditional Logic with CASE in Programming Languages

SQL Implementation

Pseudocode Implementation

JavaScript Implementation

Java Implementation

C++ Implementation

PHP Implementation

Advanced Applications of CASE Statements

SQL

Programming Language (e.g., Pseudocode)

Advanced Function for Conditional Logic in Programming

Handling Multiple Conditions Together

Debugging and Best Practices for CASE Statements

SQL Example

Programming Languages Example (JavaScript)

Best Practices

Related Posts