WHERE Clause:
Question: Retrieve all orders with a total price greater than $100.
Table: Orders (with columns like OrderID, TotalPrice, CustomerID)
HAVING Clause:
Question: Find the average salary of employees in each department and display
departments with an average salary above $50,000.
Tables: Employees (with columns like EmployeeID, Salary, DepartmentID), Departments
(with columns like DepartmentID, DepartmentName)
IF-ELSE Condition:
Question: Create a query that selects the product name and sets the category to
'Other' if the category is null.
Table: Products (with columns like ProductID, ProductName, Category)
GROUP BY Clause:
Question: Calculate the total revenue for each product category.
Table: Products (with columns like ProductID, ProductName, Category), OrderDetails
(with columns like OrderID, ProductID, Quantity, UnitPrice)
JOINs:
Question: Retrieve a list of customers along with their order details (order date
and total price) for orders placed after January 1, 2023.
Tables: Customers (with columns like CustomerID, CustomerName), Orders (with
columns like OrderID, CustomerID, OrderDate), OrderDetails (with columns like
OrderID, ProductID, Quantity, UnitPrice)
Aggregate Functions:
Question: Calculate the total number of products in each category.
Table: Products (with columns like ProductID, ProductName, Category)
WHERE Clause:
Question: Retrieve all products with a price greater than $50.
Table: Products (with columns like ProductID, ProductName, Price)
Question: Find all customers who are located in the 'United States.'
Table: Customers (with columns like CustomerID, CustomerName, Country)
Question: Get a list of employees who were hired after January 1, 2020.
Table: Employees (with columns like EmployeeID, EmployeeName, HireDate)
Question: Find all orders placed by a specific customer (use customer's ID).
Table: Orders (with columns like OrderID, CustomerID, OrderDate)
Question: Retrieve products with a quantity in stock greater than 100.
Table: Inventory (with columns like ProductID, QuantityInStock)
HAVING Clause:
6. Question: Calculate the average salary of employees in each department and
display departments with an average salary greater than $60,000.
Tables: Employees (with columns like EmployeeID, Salary, DepartmentID), Departments
(with columns like DepartmentID, DepartmentName)
Question: Find the total revenue for each product category and display only
categories with total revenue exceeding $10,000.
Tables: Products (with columns like ProductID, ProductName, Category), OrderDetails
(with columns like OrderID, ProductID, Quantity, UnitPrice)
Question: List customers who have placed at least five orders and have a total
order value of more than $1,000.
Tables: Customers (with columns like CustomerID, CustomerName), Orders (with
columns like OrderID, CustomerID), OrderDetails (with columns like OrderID,
Quantity, UnitPrice)
Question: Calculate the average number of products purchased per order and display
orders with an average quantity greater than 5.
Tables: Orders (with columns like OrderID, OrderDate), OrderDetails (with columns
like OrderID, ProductID, Quantity)
Question: Find departments with more than 50 employees.
Tables: Departments (with columns like DepartmentID, DepartmentName), Employees
(with columns like EmployeeID, DepartmentID)
IF-ELSE (CASE) Statement:
11. Question: Create a query that categorizes products as 'High Demand' if the
quantity in stock is less than 10, 'Medium Demand' if it's between 10 and 50, and
'Low Demand' otherwise.
- Table: Inventory (with columns like ProductID, QuantityInStock)
Question: Calculate the bonus for employees based on their years of service.
Employees with 5 or more years get a 10% bonus, others get a 5% bonus.
Table: Employees (with columns like EmployeeID, EmployeeName, HireDate)
Question: Classify customers as 'VIP' if they have made total purchases exceeding
$10,000; otherwise, label them as 'Regular.'
Tables: Customers (with columns like CustomerID, CustomerName), Orders (with
columns like OrderID, CustomerID), OrderDetails (with columns like OrderID,
Quantity, UnitPrice)
Question: Categorize products as 'New' if they were added to the inventory in the
last 30 days, 'Recent' if added in the last 90 days, and 'Old' otherwise.
Tables: Products (with columns like ProductID, ProductName, DateAdded)
Question: Assign a shipping status to orders. If the order date is within the last
7 days, mark it as 'Pending'; otherwise, mark it as 'Shipped.'
Question: List all employees along with their respective managers' names.
Table: Employees (with columns like EmployeeID, EmployeeName, ManagerID)
Question: Get a list of customers who have placed orders along with the order
details (products and quantities) for each order.
Tables: Customers (with columns like CustomerID, CustomerName), Orders (with
columns like OrderID, CustomerID), OrderDetails (with columns like OrderID,
ProductID, Quantity)
Question: Retrieve all products and their respective categories.
Tables: Products (with columns like ProductID, ProductName, CategoryID), Categories
(with columns like CategoryID, CategoryName)
Question: List employees and their respective departments.