task6.sql

--Create Database & Table
CREATE DATABASE task6;
USE task6;

CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    joining_date DATE,
    status VARCHAR(20)
);

-- Insert Bulk Data
INSERT INTO employees (emp_name, department, salary, joining_date, status) VALUES
('Rahul Sharma', 'IT', 55000, '2022-01-15', 'Active'),
('Anita Verma', 'HR', 45000, '2021-03-10', 'Active'),
('Suresh Kumar', 'Finance', 60000, '2020-07-25', 'Inactive'),
('Priya Singh', 'IT', 70000, '2019-11-01', 'Active'),
('Amit Patel', 'Sales', 40000, '2023-02-05', 'Active');

-- Read (SELECT) – Filtered Data
-- All employees
SELECT * FROM employees;

-- Employees from IT department
SELECT emp_name, salary
FROM employees
WHERE department = 'IT';

-- Salary greater than 50,000
SELECT * FROM employees
WHERE salary > 50000;

-- Update Records Using Conditions
-- Increase salary for IT department
UPDATE employees
SET salary = salary + 5000
WHERE department = 'IT';

-- Mark employee as inactive
UPDATE employees
SET status = 'Inactive'
WHERE emp_name = 'Amit Patel';


--Validate after update

SELECT * FROM employees;

--Delete Selective Rows
-- Delete inactive employees
DELETE FROM employees
WHERE status = 'Inactive';

-- Practice Safe Deletes
-- Enable safe updates (MySQL specific)
SET SQL_SAFE_UPDATES = 1;

-- Safe delete using primary key
DELETE FROM employees
WHERE emp_id = 5;


-- Never do this

-- DANGEROUS (deletes all rows)
DELETE FROM employees;

-- Use Transactions
START TRANSACTION;

UPDATE employees
SET salary = salary - 2000
WHERE department = 'HR';

-- Check before committing
SELECT * FROM employees;

-- Rollback if mistake
ROLLBACK;

-- Or save permanently

-- COMMIT;

-- Validate Before & After States
SELECT COUNT(*) FROM employees;
SELECT * FROM employees;