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.
Notes
Chapter 22: Control Flow (IF-ELSE, WHILE)
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.
1. IF-ELSE Statements
The IF-ELSE statement in SQL Server is used to conditionally execute one or more SQL statements based on a specified condition.
Basic IF-ELSE Structure:
Example :
An IF-ELSE statement
DECLARE
@OrderTotal
DECIMAL(10, 2)
SET
@OrderTotal =
150.00
IF
@OrderTotal >
100.00
BEGIN
PRINT 'Free Shipping Eligible'
END
In this example, an IF-ELSE statement determines whether an order is eligible for free shipping
2. WHILE Loop
The WHILE loop in SQL Server allows you to execute a block of SQL statements repeatedly as long as a specified condition is true.
Basic WHILE Loop Syntax:
Example :
content WHILE loop
DECLARE
@Counter
INT
SET
@Counter =
1
WHILE
@Counter <=
5
BEGIN
PRINT 'Iteration ' +
CAST
(
@Counter
AS
VARCHAR(10)
)
SET
@Counter = @Counter + 1
END
This WHILE loop iterates five times and prints a message for each iteration.
3.Real-world Use of Control Flow
Inventory Management System:
- Stock Replenishment: Use IF-ELSE statements to determine when to reorder products based on inventory levels.
- Inventory Count: Implement a WHILE loop to count and update inventory quantities in batches.
Customer Relationship Management:
- Customer Segmentation: Apply IF-ELSE statements to categorize customers into different segments based on their purchase history and preferences.
- Email Campaigns: Use a WHILE loop to send personalized emails to a targeted group of customers.
4. Real-world View Usage:
- Order History: Create a view that combines data from "Orders," "Products," and "Customers" tables to provide a comprehensive order history for customers.
- Sales Dashboard: Build views to summarize and visualize sales data, making it easier for managers to track performance.
- Employee Directory: Use views to present employee details while hiding sensitive information like Social Security numbers.
- Salary Reports: Create views for generating salary reports, ensuring that only authorized personnel can access compensation data.
4. Key Takeaways:
- IF-ELSE statements in SQL Server allow you to execute different code blocks based on conditional expressions.
- WHILE loops enable repetitive execution of SQL statements while a specified condition is true.
- Real-world scenarios benefit from control flow for decision-making and iterative processes.
5. Exercise Questions:
- Write a SQL script that calculates the total cost of items in a shopping cart, applying a 10% discount if the total is above $100.
- Create a WHILE loop to simulate a simple game where a player accumulates points until they reach a certain score.
- Use IF-ELSE statements to categorize employees into "Junior," "Intermediate," and "Senior" levels based on their years of experience.
- Develop a WHILE loop that processes a queue of customer support tickets, sending automated responses until the queue is empty.
- Explain the advantages of using control flow statements like IF-ELSE and WHILE loops in SQL for decision-making and repetitive tasks.
- Discuss potential performance considerations when using control flow statements, especially in SQL Server, for handling large datasets and
Variables in Microsoft SQL Server allow you to store and manipulate data within a query or batch of queries. In this chapter, we'll explore the declaration and usage of variables, real-world applications, and provide examples.