Recursive functions and Common Table Expressions (CTEs) are powerful tools in Microsoft SQL Server for working with hierarchical or recursive data structures. In this chapter, we will explore the concept of recursive queries, how to use CTEs to create them, and real-world examples.
Notes
Chapter 21: Conditional Logic and UNION
Conditional logic and the UNION operator are essential tools in Microsoft SQL Server for manipulating and combining data from multiple sources. In this chapter, we'll explore the usage of CASE statements, UNION, and real-world examples of conditional logic in SQL.
1. CASE Statements
The CASE statement in SQL Server is a versatile way to apply conditional logic within SQL queries. It allows you to define conditions and specify different actions based on those conditions.
Basic Structure of a CASE Statement:
Example :
Simple CASE statement
SELECT
ProductName,
Price,
CASE WHEN
Price <
50 THEN
'Affordable'
WHEN
Price >=
50
AND
Price <=
100 THEN
'Moderate'
ELSE
'Expensive'
END AS
PriceCategory
FROM
Products;
In this example, the CASE statement categorizes products based on their prices.
2. Using UNION
The UNION operator in SQL Server combines the result sets of two or more SELECT statements into a single result set. It is used to retrieve data from multiple tables with similar structures.
Basic UNION Syntax:
Example :
Using UNION to combine data from two tables
SELECT
EmployeeName
FROM
Employees
UNION SELECT
CustomerName
FROM
Customers;
This query combines employee and customer names into a single result set.
3. Real-world Applications of Conditional Logic
E-commerce System:
- Price Ranges: Use CASE statements to categorize products into price ranges for easy filtering and display.
- Customer Segmentation: Apply conditional logic to segment customers based on their purchase history for targeted marketing.
Financial System:
- Credit Approval: Evaluate credit applications using CASE statements to determine eligibility based on criteria such as credit score and income.
- Risk Assessment: Apply conditional logic to assess the risk associated with investment portfolios or loans.
4.Key Takeaways
- CASE statements provide a way to introduce conditional logic into SQL queries, enabling dynamic result generation.
- The UNION operator is used to combine data from multiple tables or result sets.
- Real-world scenarios benefit from conditional logic for categorization, segmentation, and decision-making.
5.Exercise Questions
- Write a query that categorizes products into "In Stock" and "Out of Stock" based on their available quantities using a CASE statement.
- Create a query that combines the names of employees and customers into a single result set using the UNION operator.
- Use a CASE statement to calculate a bonus for employees based on their performance scores, where scores above 90 receive a 10% bonus, scores between 80 and 90 receive a 5% bonus, and scores below 80 receive no bonus.
- Develop a query that combines data from multiple sales regions and categorizes them as "North," "South," "East," or "West" using a CASE statement.
- Explain the benefits of using CASE statements and UNION operators for data manipulation and integration in SQL.
- Discuss potential performance considerations when using CASE statements and UNION operators in SQL Server, especially in scenarios involving large datasets and complex logic.
Control flow statements, including IF-ELSE and WHILE loops, are valuable tools in Microsoft SQL Server for executing conditional logic and iterative processes within SQL queries. In this chapter, we'll explore the usage of IF-ELSE statements, WHILE loops, and their real-world applications.