SQLize | PHPize | SQLtest

-- STEP 1: Create employees table CREATE TABLE employees ( emp_id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, department VARCHAR(50), hire_date DATE DEFAULT CURRENT_DATE ); -- STEP 2: Create attendance table CREATE TABLE attendance ( att_id SERIAL PRIMARY KEY, emp_id INT REFERENCES employees(emp_id), check_in TIMESTAMP NOT NULL, check_out TIMESTAMP, att_date DATE DEFAULT CURRENT_DATE ); -- STEP 3: Insert sample employees INSERT INTO employees (name, email, department) VALUES ('Alice', 'alice@example.com', 'HR'), ('Bob', 'bob@example.com', 'IT'); -- STEP 4: Insert attendance data (check-in) INSERT INTO attendance (emp_id, check_in) VALUES (1, '2025-08-08 09:05:00'), (2, '2025-08-08 09:15:00'); -- STEP 5: Update with check-out UPDATE attendance SET check_out = '2025-08-08 17:00:00' WHERE emp_id = 1; UPDATE attendance SET check_out = '2025-08-08 17:30:00' WHERE emp_id = 2; -- STEP 6: Create a VIEW to show daily attendance report CREATE VIEW daily_attendance_report AS SELECT e.name, a.att_date, a.check_in, a.check_out, ROUND(EXTRACT(EPOCH FROM (a.check_out - a.check_in))/3600, 2) AS hours_worked FROM employees e JOIN attendance a ON e.emp_id = a.emp_id; -- STEP 7: Create a TRIGGER to prevent multiple check-ins CREATE OR REPLACE FUNCTION prevent_multiple_checkin() RETURNS TRIGGER AS $$ BEGIN IF EXISTS ( SELECT 1 FROM attendance WHERE emp_id = NEW.emp_id AND att_date = NEW.att_date ) THEN RAISE EXCEPTION 'You already checked in today.'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg_checkin_once BEFORE INSERT ON attendance FOR EACH ROW EXECUTE FUNCTION prevent_multiple_checkin(); -- STEP 8: Test trigger (should fail on second check-in) -- Uncomment below to test trigger error -- INSERT INTO attendance (emp_id, check_in) VALUES (1, '2025-08-08 10:00:00'); -- STEP 9: Query the view SELECT * FROM daily_attendance_report;