SQLASSIGNMENT3.sql

USE JANUARY2026

---TABLE CREATION : Employee_Operators
CREATE TABLE Employee_Operators
(
    Eid INT IDENTITY(1,1),
    Ename VARCHAR(30),
    Age INT,
    Department VARCHAR(25),
    Salary MONEY
);

---INSERTING DATA
   
INSERT INTO Employee_Operators (Ename, Age, Department, Salary)
VALUES
('Anjali', 20, 'IT', 30000),
('Riya', 25, 'Sales', 28000),
('Anu', 28, 'Marketing', 32000),
('Nilu', 35, 'Finance', 40000),
('Aarv', 30, 'HR', 27000),
('Kiran', 22, 'Operations', 22000),
('Suman', 26, 'Admin', 21000),
('Pratik', 34, 'IT', 36000),
('Pooja', 29, NULL, 25000);

Select * from Employee_Operators

---1. Displaying the Ename and a 10% bonus amount for every employee
SELECT Ename, Salary * 0.10 AS Bonus_Amount
FROM Employee_Operators;

---2. Calculating the new salary for all employees if everyone gets a flat raise of ?5,000. 
SELECT Ename, Salary + 5000 AS New_Salary
FROM Employee_Operators;

---3. If an employee's age is increased by 2, what would it be? Display Ename and
SELECT Ename, Age + 2 AS ProjectedAge
FROM Employee_Operators;

---4. Show the Ename and the monthly salary deduction if a 2% professional tax isapplied to the current salary.
SELECT Ename, Salary * 0.02 AS Professional_Tax
FROM Employee_Operators;

---1. Listing all employees who are exactly 20 years old.
SELECT * FROM Employee_Operators
WHERE Age = 20;

---2. Finding all employees whose salary is greater than 20,000.
SELECT * FROM Employee_Operators
WHERE Salary > 20000;

---3. Display details of employees who do not belong to the 'Cloud' department.
SELECT * FROM Employee_Operators
WHERE Department <> 'Cloud';

---4.Listing employees who were hired at an age of 25 or younger.
SELECT * FROM Employee_Operators
WHERE Age <= 25;

---5.Finding employees whose salary is less than or equal to 25,000.
SELECT * FROM Employee_Operators
WHERE Salary <= 25000;

---1.List employees who work in the 'IT' department AND have a salary greater than30,000.
SELECT * FROM Employee_Operators
WHERE Department = 'IT' AND Salary > 30000;

---2.Find employees who are either in the 'Sales' department OR the 'Marketing'department.
SELECT * FROM Employee_Operators
WHERE Department = 'Sales' OR Department = 'Marketing';

---3.Display employees who are older than 30 but do NOT work in the 'Finance'department.
SELECT * FROM Employee_Operators
WHERE Age > 30 AND Department <> 'Finance';

---4.Find employees whose age is 28 AND salary is exactly 30,000.
SELECT * FROM Employee_Operators
WHERE Age = 28 AND Salary = 30000;

---5.List all employees who are NOT in the 'Admin' department and have a salary above 20,000.
SELECT * FROM Employee_Operators
WHERE Department <> 'Admin' AND Salary > 20000;

---1. Find employees whose age is between 25 and 35 (inclusive).
SELECT * FROM Employee_Operators
WHERE Age BETWEEN 25 AND 35;

---2. List employees who work in any of these departments: 'IT', 'HR', or 'Operations'.
SELECT * FROM Employee_Operators
WHERE Department IN ('IT','HR','Operations');

---3.Find all employees whose name starts with the letter 'A'.
SELECT * FROM Employee_Operators
WHERE Ename LIKE 'A%';

---4.List employees whose salary is in the range of 25,000 to 35,000 using the BETWEEN operator.
SELECT * FROM Employee_Operators
WHERE Salary BETWEEN 25000 AND 35000;

---5. Find employees whose Department value is missingList employees who are NOT in the 'IT' or 'Sales' departments using the NOT IN operator.
SELECT * FROM Employee_Operators
WHERE Department IS NULL;

SELECT * FROM Employee_Operators
WHERE Department NOT IN ('IT','Sales');

---7. Find employees whose name ends with the letter 'n'.
SELECT * FROM Employee_Operators
WHERE Ename LIKE '%n';

---8. Show employees whose age is NOT between 20 and 30.
SELECT * FROM Employee_Operators
WHERE Age NOT BETWEEN 20 AND 30;