SQL Exercise – 12

SQL Exercise:

You are provided with a table of employees containing their names, departments, and salaries. Write a query to determine the applicable tax rate for each employee based the tax slabs are as follows:

1. Income up to ₹2,50,000: No tax.
2. ₹2,50,001 to ₹5,00,000: 5% tax.
3. ₹5,00,001 to ₹10,00,000: 20% tax.
4. Above ₹10,00,000: 30% tax.

Create table scripts:

CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
EmployeeName NVARCHAR(50),
Department NVARCHAR(50),
Salary DECIMAL(10, 2)
);

Insert table scripts:

INSERT INTO Employee (EmployeeID, EmployeeName, Department, Salary)
VALUES
(1, 'Amit', 'HR', 240000),
(2, 'Priya', 'Finance', 400000),
(3, 'Rohit', 'IT', 700000),
(4, 'Meera', 'Finance', 550000),
(5, 'Karan', 'IT', 200000),
(6, 'Ananya', 'HR', 1200000),
(7, 'Suresh', 'IT', 300000);

Solution:

SELECT 
EmployeeID,
EmployeeName,
Department,
Salary,
CASE 
WHEN Salary <= 250000 THEN 'No Tax'
WHEN Salary BETWEEN 250001 AND 500000 THEN '5%'
WHEN Salary BETWEEN 500001 AND 1000000 THEN '20%'
ELSE '30%'
END AS TaxRate
FROM 
Employee;

Output:

Explanation:

• Tax Slabs Applied:
  • Salaries up to ₹2,50,000 are exempted from tax.
  • Salaries between ₹2,50,001 and ₹5,00,000 have a 5% tax rate.
  • Salaries between ₹5,00,001 and ₹10,00,000 have a 20% tax rate.
  • Salaries above ₹10,00,000 have a 30% tax rate.
• CASE Statement Logic:
  • The CASE statement evaluates the salary and categorizes it into the appropriate tax rate.