🧱 Step 1: Create Table (PostgreSQL)

-- Create Employee table in PostgreSQL
CREATE TABLE Employee (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary NUMERIC(10,2),
    hire_date DATE
);

🧩 Step 2: Insert Sample Data

INSERT INTO Employee (first_name, last_name, department, salary, hire_date) VALUES
('Abhishek', 'Raut', 'Engineering', 90000, '2021-06-10'),
('Riya', 'Patel', 'HR', 60000, '2022-01-15'),
('Arjun', 'Mehta', 'Engineering', 75000, '2020-09-05'),
('Sneha', 'Sharma', 'Marketing', 50000, '2023-03-20'),
('Raj', 'Singh', 'Engineering', 95000, '2019-12-01'),
('Neha', 'Khan', 'Finance', 80000, '2020-04-12');

🧠 Step 3: Basic SQL Operations in PostgreSQL

🧩 CREATE, ALTER, DROP, TRUNCATE

-- CREATE already done above

-- ALTER: Add new column 'experience'
ALTER TABLE Employee ADD COLUMN experience INT;

-- UPDATE experience (approx years)
UPDATE Employee
SET experience = EXTRACT(YEAR FROM AGE(CURRENT_DATE, hire_date));

-- DROP the 'experience' column
ALTER TABLE Employee DROP COLUMN experience;

-- TRUNCATE: Delete all rows but keep structure
TRUNCATE TABLE Employee;

-- DROP TABLE: Remove table structure completely
DROP TABLE Employee;

🧩 INSERT, UPDATE, DELETE

-- INSERT new record
INSERT INTO Employee (first_name, last_name, department, salary, hire_date)
VALUES ('Karan', 'Joshi', 'HR', 55000, '2024-02-05');

-- UPDATE salary of a specific employee
UPDATE Employee
SET salary = 100000
WHERE first_name = 'Abhishek';

-- DELETE an employee record
DELETE FROM Employee
WHERE first_name = 'Karan';

🧩 SELECT — Retrieving Data

-- Select all records
SELECT * FROM Employee;

-- Select specific columns
SELECT first_name, salary FROM Employee;

-- Rename columns using alias
SELECT first_name AS name, salary AS monthly_salary FROM Employee;

-- Filter: Employees with salary greater than 70k
SELECT * FROM Employee WHERE salary > 70000;

🧩 WHERE + Logical Operators

-- Employees in Engineering department
SELECT * FROM Employee WHERE department = 'Engineering';

-- Employees not in Marketing
SELECT * FROM Employee WHERE department <> 'Marketing';

-- Employees with salary between 60k and 90k
SELECT * FROM Employee WHERE salary BETWEEN 60000 AND 90000;

-- Employees in Engineering or HR
SELECT * FROM Employee WHERE department IN ('Engineering', 'HR');

-- Employees hired after 2021
SELECT * FROM Employee WHERE hire_date > '2021-01-01';