SQLize Online / PHPize Online  /  SQLtest Online

-- Create the database CREATE DATABASE IF NOT EXISTS attendance_db; USE attendance_db; -- Create the employees table CREATE TABLE IF NOT EXISTS employees ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, contract_overtime BOOLEAN NOT NULL DEFAULT FALSE ); -- Insert sample data into employees table INSERT INTO employees (name, contract_overtime) VALUES ('Alice Smith', TRUE), ('Bob Johnson', FALSE), ('Charlie Brown', TRUE); -- Create the attendance table CREATE TABLE IF NOT EXISTS attendance ( id INT AUTO_INCREMENT PRIMARY KEY, employee_id INT NOT NULL, date DATE NOT NULL, clock_in DATETIME NOT NULL, clock_out DATETIME NOT NULL, FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE ); -- Insert sample data into attendance table INSERT INTO attendance (employee_id, date, clock_in, clock_out) VALUES (1, '2024-07-25', '2024-07-25 09:00:00', '2024-07-25 18:30:00'), (2, '2024-07-25', '2024-07-25 09:00:00', '2024-07-25 17:00:00'), (3, '2024-07-25', '2024-07-25 09:00:00', '2024-07-25 17:30:00');