IBM DB2 SQL Advanced Operations | Generated by AI

Home PDF

I’ll teach you some advanced SQL operations in IBM DB2. Let’s dive into nested queries, complex conditions, and advanced grouping techniques.

Nested Queries (Subqueries)

Subqueries are queries embedded within other queries, often used in WHERE, HAVING, or FROM clauses.

In WHERE Clause:

SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

This returns employees with salary above the average.

Correlated Subquery:

SELECT d.dept_name, e.last_name
FROM departments d
WHERE EXISTS (
    SELECT 1 
    FROM employees e 
    WHERE e.dept_id = d.dept_id 
    AND e.salary > 100000
);

This finds departments that have at least one employee earning over $100,000.

In FROM Clause (Derived Tables):

SELECT dept_name, avg_salary
FROM (
    SELECT d.dept_name, AVG(e.salary) as avg_salary
    FROM departments d
    JOIN employees e ON d.dept_id = e.dept_id
    GROUP BY d.dept_name
) AS dept_stats
WHERE avg_salary > 75000;

Complex WHERE Conditions

Using CASE in WHERE:

SELECT product_id, product_name, price
FROM products
WHERE 
    CASE 
        WHEN category = 'Electronics' THEN price < 1000
        WHEN category = 'Furniture' THEN price < 500
        ELSE price < 200
    END;

Multiple Conditions with Combinations:

SELECT *
FROM orders
WHERE 
    (status = 'Pending' AND created_date > CURRENT DATE - 7 DAYS)
    OR
    (status = 'Processing' AND created_date > CURRENT DATE - 14 DAYS)
    OR
    (customer_id IN (SELECT customer_id FROM premium_customers));

Using BETWEEN and IN:

SELECT order_id, customer_id, total_amount
FROM orders
WHERE 
    order_date BETWEEN '2024-01-01' AND '2024-03-31'
    AND
    shipping_method IN ('Express', 'Priority')
    AND
    total_amount > 1000;

Advanced GROUP BY Operations

GROUP BY with ROLLUP:

SELECT 
    COALESCE(region, 'All Regions') as region,
    COALESCE(country, 'All Countries') as country,
    SUM(sales) as total_sales
FROM sales_data
GROUP BY ROLLUP(region, country);

This produces subtotals for each group level and a grand total.

GROUP BY with CUBE:

SELECT 
    COALESCE(year, 0) as year,
    COALESCE(quarter, 0) as quarter,
    COALESCE(product_line, 'All Products') as product_line,
    SUM(revenue) as total_revenue
FROM sales_data
GROUP BY CUBE(year, quarter, product_line);

This generates all possible combinations of the specified dimensions.

GROUPING SETS:

SELECT 
    product_category,
    region,
    SUM(sales) as total_sales
FROM sales_data
GROUP BY GROUPING SETS(
    (product_category, region),
    (product_category),
    (region),
    ()
);

This specifies multiple grouping combinations in a single query.

Common Table Expressions (CTEs)

WITH regional_sales AS (
    SELECT region, SUM(amount) as total_sales
    FROM orders
    GROUP BY region
),
top_regions AS (
    SELECT region
    FROM regional_sales
    ORDER BY total_sales DESC
    FETCH FIRST 3 ROWS ONLY
)
SELECT o.order_id, o.customer_id, o.amount
FROM orders o
JOIN top_regions tr ON o.region = tr.region
WHERE o.amount > 1000;

Advanced JOINS

Self JOIN:

SELECT e.employee_id, e.last_name as employee, m.last_name as manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

Multiple JOINS with Complex Conditions:

SELECT 
    c.customer_name,
    p.product_name,
    o.order_date,
    SUM(od.quantity * od.unit_price) as line_total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
WHERE 
    o.order_date > '2024-01-01'
    AND p.category IN ('Electronics', 'Appliances')
    AND c.country = 'USA'
GROUP BY c.customer_name, p.product_name, o.order_date
HAVING SUM(od.quantity * od.unit_price) > 500
ORDER BY line_total DESC;

Window Functions

SELECT 
    dept_id,
    employee_id,
    salary,
    AVG(salary) OVER (PARTITION BY dept_id) as dept_avg_salary,
    salary - AVG(salary) OVER (PARTITION BY dept_id) as diff_from_avg,
    RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as salary_rank
FROM employees;

Would you like me to elaborate on any of these techniques or provide more specific examples for a particular DB2 scenario?


Back 2025.03.23 Donate