assesment 1.sql

#Q-1
CREATE DATABASE sampal ;

#Q-2
USE sampal ;

#Q-3
CREATE TABLE employee (
    emp_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(50),
    position VARCHAR(50),
    hire_date DATE,
    salary DECIMAL(10, 2),
    bonus DECIMAL(10, 2),
    total_salary DECIMAL(10, 2),
    emp_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

#Q-4
INSERT INTO employee (emp_name, position, salary, hire_date, bonus)
VALUES 
('varun', 'Manager', 50000.00, '2022-01-15', 5000.00),
('tirth', 'Developer', 40000.00, '2022-03-10', 3000.00),
('jay', 'Designer', 45000.00, '2022-05-20', 4000.00);

#Q-5
CREATE TABLE employee_audit (
    audit_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    emp_id INT,
    emp_name VARCHAR(50),
    position VARCHAR(50),
    inserted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

#Q-6
DELIMITER $$
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employee
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (emp_id, emp_name, position)
    VALUES (NEW.emp_id, NEW.emp_name, NEW.position);
END$$
DELIMITER ;

SHOW TRIGGERS ;
select * from employee;
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS;