Mastering Database Management

0 of 78 lessons complete (0%)

Lab Assignment/Tutorial

Lab Tutorial 3

You don’t have access to this lesson

Please register or sign in to access the course content.

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?”