SQLASSIGNMENT2.sql

USE SQL_Assignmentpart2;

-- FIRSTLY CREATING A TABLE NAMED EmployeeNew 
CREATE TABLE EmployeeNew
(
    EID INT,
    Ename VARCHAR(50),
    Eage INT,
    Esalary MONEY,
    Edepartment VARCHAR(50),
    City VARCHAR(50)
);

-- Part 1: INSERT Operations

-- 1. Inserting record for 'Suresh Patel’, age 28, salary 55000, in 'IT' with EID 101. 
INSERT INTO EmployeeNew (EID, Ename, Eage, Esalary, Edepartment)
VALUES (101, 'Suresh Patel', 28, 55000, 'IT');


-- 2. Inserting record for 'Mahesh Patel' (EID 102) where the Edepartment is explicitly set to NULL.
INSERT INTO EmployeeNew (EID, Ename, Eage, Esalary, Edepartment)
VALUES (102, 'Mahesh Patel', 30, 60000, NULL);

-- 3. Adding three employees (EIDs 103, 104, 105) using a single VALUES list.
INSERT INTO EmployeeNew (EID, Ename, Eage, Esalary, Edepartment)
VALUES 
(103, 'Rahul Sharma', 26, 50000, 'HR'),
(104, 'Neha Singh', 22, 45000, 'Finance'),
(105, 'Rohan Kumar', 25, 47000, 'IT');

-- 4. Adding record for 'Jayesh Patel' (EID 106) specifying only the EID and Ename columns.
INSERT INTO EmployeeNew (EID, Ename)
VALUES (106, 'Jayesh Patel');

-- 5. Adding record for a new hire in 'HR' with EID 107.
INSERT INTO EmployeeNew (EID, Edepartment)
VALUES (107, 'HR');

SELECT * FROM EmployeeNew;

--Part 2: UPDATE Operations (Keyword Only)

-- 1. Changing the Edepartment to 'Marketing' for any employee whose current department IS NULL.
UPDATE EmployeeNew
SET Edepartment = 'Marketing'
WHERE Edepartment IS NULL;

-- 2. Changing the Edepartment to 'Tech' for any employee whose City is Vadodara
UPDATE EmployeeNew
SET Edepartment = 'Tech'
WHERE City = 'Vadodara';

-- 3. Updating the salary to 75000 for employees whose EID is IN the list (101, 103).
UPDATE EmployeeNew
SET Esalary = 75000
WHERE EID IN (101, 103);

-- 4. Updating the Edepartment to 'Management' for employees whose Eage is 22.
UPDATE EmployeeNew
SET Edepartment = 'Management'
WHERE Eage = 22;

-- 5. Updating the Esalary to 80000 for everyone whose name is Suresh
UPDATE EmployeeNew
SET Esalary = 80000
WHERE Ename LIKE '%Suresh%';

-- Part 3: DELETE Operations

-- 1. Removing the record for the employee whose EID is (105).
DELETE FROM EmployeeNew
WHERE EID = 105;

-- 2. Removing all employees who work in departments 'Marketing'.
DELETE FROM EmployeeNew
WHERE Edepartment = 'Marketing';

-- 3. Removing employees whose Eage is 25.
DELETE FROM EmployeeNew
WHERE Eage = 25;

-- 4. Removing records where the Esalary IS NULL.
DELETE FROM EmployeeNew
WHERE Esalary IS NULL;

-- 5. 15.Using TRUNCATE command to empty the entire table at once
TRUNCATE TABLE EmployeeNew;