sql query practice

 

1️⃣ Create Employee Table

CREATE TABLE Employee(
    EmpID INT PRIMARY KEY,
    EName VARCHAR(50),
    Job VARCHAR(50),
    Salary DECIMAL(10,2),
    City VARCHAR(50)
);

---

2️⃣ Add Columns HRA, PF, DA with Domain (numeric)

ALTER TABLE Employee
ADD HRA DECIMAL(10,2);

ALTER TABLE Employee
ADD PF DECIMAL(10,2);

ALTER TABLE Employee
ADD DA DECIMAL(10,2);

---

3️⃣ Insert 5 Records (Using Keyboard / Manual Insert)

INSERT INTO Employee(EmpID, EName, Job, Salary, City) VALUES
(101,'Adarsh','Manager',50000,'Delhi'),
(102,'Riya','Clerk',30000,'Mumbai'),
(103,'Amit','Developer',40000,'Lucknow'),
(104,'Neha','Analyst',35000,'KNP'),
(105,'Sara','Tester',28000,'ALLD');

---

4️⃣ Update HRA, PF, DA (Percent of Salary)

HRA = 12%, PF = 10%, DA = 65%

UPDATE Employee
SET HRA = Salary*0.12,
    PF  = Salary*0.10,
    DA  = Salary*0.65;

---

5️⃣ Deduct PF from Salary Column

UPDATE Employee
SET Salary = Salary - PF;

---

6️⃣ Delete Employees with EmpID 105, 107, 109 AND City in ('ALLD','KNP','Lucknow')

DELETE FROM Employee
WHERE EmpID IN (105,107,109)
   OR City IN ('ALLD','KNP','Lucknow');

Note: Ye deletion condition me OR use kiya kyunki aapne dono criteria diye hain (empid OR city)

---

7️⃣ Delete the Column City

ALTER TABLE Employee
DROP COLUMN City;

---

8️⃣ List Name, Salary, Total Salary (Salary + HRA + PF + DA)

SELECT EName,
       Salary,
       (Salary + HRA + PF + DA) AS TotalSalary
FROM Employee;

---

✅ Explanation of Steps:

1. Table created with basic employee info.

2. HRA, PF, DA columns added separately with numeric domain.

3. 5 sample records inserted.

4. HRA, PF, DA calculated as % of Salary.

5. PF deducted from Salary column.

6. Unwanted employees deleted using EmpID or City criteria.

7. City column removed from table.

8. Final output displays employee name, net salary after PF deduction, and total salary including HRA, PF, DA.