-- Create EMPLOYEE table
CREATE TABLE EMPLOYEE (
    Emp_no INT PRIMARY KEY,
    E_name VARCHAR(100),
    E_address VARCHAR(200),
    E_ph_no VARCHAR(15),
    Dept_no VARCHAR(10),
    Dept_name VARCHAR(100),
    Job_id VARCHAR(20),
    Salary DECIMAL(10, 2)
);

-- 1. Insert at least 5 rows in the table
INSERT INTO EMPLOYEE (Emp_no, E_name, E_address, E_ph_no, Dept_no, Dept_name, Job_id, Salary)
VALUES
    (1, 'John Doe', '123 Main St, New York', '123-456-7890', 'D10', 'HR', 'HR001', 50000.00),
    (2, 'Jane Smith', '456 Elm St, Los Angeles', '987-654-3210', 'D20', 'Finance', 'FIN002', 60000.00),
    (3, 'Alice Johnson', '789 Oak St, Chicago', '555-123-4567', 'D10', 'HR', 'HR002', 55000.00),
    (4, 'Bob Williams', '101 Pine St, San Francisco', '222-333-4444', 'D30', 'Engineering', 'ENG001', 70000.00),
    (5, 'Michael Brown', '202 Maple St, Houston', '777-888-9999', 'D40', 'Sales', 'SAL001', 65000.00);


-- 2. Display all the information of EMP table
SELECT * FROM EMPLOYEE;

-- 3. Display the record of each employee who works in department D10
SELECT * FROM EMPLOYEE WHERE Dept_no = 'D10';

-- 4. Update the city of Emp_no-12 with current city as Nagpur
UPDATE EMPLOYEE SET E_address = 'Nagpur' WHERE Emp_no = 12;

-- 5. Display the details of Employee who works in department MECH
SELECT * FROM EMPLOYEE WHERE Dept_name = 'MECH';

-- 6. Delete the email_id of employee James
DELETE FROM EMPLOYEE WHERE E_name = 'James';

-- 7. Display the complete record of employees working in SALES Department
SELECT * FROM EMPLOYEE WHERE Dept_name = 'SALES';