Sample Table Structures:
| Employees Table: employee_id (INT) name (VARCHAR) salary (DECIMAL) country (VARCHAR) | Orders Table: order_id (INT) order_date (DATE) total_amount (DECIMAL) status (VARCHAR) |
| Customers Table: customer_id (INT) name (VARCHAR) email (VARCHAR) country (VARCHAR) | Products Table: product_id (INT) name (VARCHAR) price (DECIMAL) category (VARCHAR) |
Introduction to Conditional Statements
- Scenario: “Can you provide a list of employees who earn more than $50,000?”
SELECT * FROM employees WHERE salary > 50000;- Scenario: “Could you retrieve orders placed after January 1, 2023?”
SELECT * FROM orders WHERE order_date > '2023-01-01';- Scenario: “I need a list of customers from the USA. Can you get that for me?”
SELECT * FROM customers WHERE country = 'USA';Using WHERE Clause with Wildcard Characters
Scenario: “Do we have any employees whose names start with ‘J’? If so, who are they?”
SELECT * FROM employees WHERE name LIKE 'J%';Scenario: “Can you find products with ‘blue’ in their names?”
SELECT * FROM products WHERE name LIKE '%blue%';Scenario: “I need to contact customers with Gmail addresses. Can you fetch their details?”
SELECT * FROM customers WHERE email LIKE '%@gmail.com';Combining Conditional Statements and Wildcard Characters
Scenario: “What are the orders placed after January 1, 2023, with a total amount exceeding $500?”
SELECT * FROM orders WHERE order_date > '2023-01-01' AND total_amount > 500;Scenario: “Do we have any employees from the USA whose names start with ‘A’?”
SELECT * FROM employees WHERE name LIKE 'A%' AND country = 'USA';Scenario: “Find products with ‘red’ in their names and a price higher than $50.”
SELECT * FROM products WHERE name LIKE '%red%' AND price > 50;Advanced Conditional Statements
(For this section, let’s consider broader questions that might necessitate the use of CASE statements.)
- Scenario: “Could you categorize our customers into different tiers based on their total purchases?”
- Scenario: “Can you mark each order as ‘completed’ or ‘pending’ based on its status?”
- Scenario: “I’d like to categorize our employees based on their experience levels. How would you suggest doing that?”