transaction_id: Unique identifier for each transaction.
customer_id: Unique identifier for each customer.
product_id: Unique identifier for each product sold.
transaction_date: The date when the transaction occurred.
quantity: The quantity of the product sold in the transaction.
unit_price: The price of one unit of the product.
You’re tasked with finding the top 5 customers who made the highest total purchase amount in the last quarter (last three months) and displaying their names and total purchase amounts. Write an Oracle SQL query to retrieve this information.
WITH LastQuarterSales AS (
SELECT customer_id, SUM(quantity * unit_price) AS total_purchase_amount
FROM sales
WHERE transaction_date >= TRUNC(SYSDATE) - INTERVAL '3' MONTH
GROUP BY customer_id
)
SELECT c.customer_id, c.customer_name, lqs.total_purchase_amount
FROM LastQuarterSales lqs
JOIN customers c ON lqs.customer_id = c.customer_id
ORDER BY lqs.total_purchase_amount DESC
FETCH FIRST 5 ROWS ONLY;
26-30. Case Study: Employee Performance Evaluation System.
The dataset contains information about employees' performance evaluations in a company. The "employees" table includes the following columns:
employee_id: Unique identifier for each employee.
employee_name: The name of the employee.
department: The department to which the employee belongs (e.g., HR, Finance, Sales).
rating: The employee's performance rating on a scale of 1 to 5 (5 being the highest).
years_of_experience: The number of years of experience of the employee.
salary: The salary of the employee.
manager_id: The ID of the employee's manager.
