SQLize | PHPize | SQLtest

-- Create the database and use it CREATE DATABASE IF NOT EXISTS myschema; USE myschema; -- Create HEAD table CREATE TABLE HEAD( Dept_ID CHAR(3) PRIMARY KEY, Dept_Head VARCHAR(20) ); -- Create EMPLOYEE table CREATE TABLE EMPLOYEE( Empl_ID CHAR(5), Dept_ID CHAR(3), Specialization VARCHAR(15), Designation VARCHAR(25) NOT NULL, Age INT DEFAULT 25 CHECK (Age > 20 AND Age < 60), Joined_date DATE DEFAULT '2019-12-17', Joined_time TIME DEFAULT '00:00:00', Married BOOLEAN DEFAULT TRUE, Security_code CHAR(6) DEFAULT '110011', PRIMARY KEY (Empl_ID, Designation), CONSTRAINT uc1 UNIQUE(Empl_ID), CONSTRAINT fk1 FOREIGN KEY (Dept_ID) REFERENCES HEAD(Dept_ID) ON DELETE SET NULL ON UPDATE CASCADE ); -- Insert into HEAD INSERT INTO HEAD VALUES ('EIE', 'Meranda'), ('MME', 'Randitha'), ('CEE', 'Layanthi'); -- Insert into EMPLOYEE INSERT INTO EMPLOYEE (Empl_ID, Dept_ID, Specialization, Designation) VALUES ('EE001', 'EIE', 'Software', 'Lecturer'), ('EE002', 'EIE', 'Communication', 'Senior lecturer'), ('MM007', 'MME', 'Automobile', 'Lecturer'), ('MM001', 'MME', 'Manufacturing', 'Senior lecturer'), ('CE001', 'CEE', NULL, 'Lab attendant'), ('CE003', 'CEE', 'Structural', 'Lecturer'), ('CE007', 'CEE', 'Environmental', 'Lecturer'), ('EE005', 'EIE', NULL, 'Labourer'); -- Drop column Experience if needed (already excluded here) -- Insert after schema change INSERT INTO EMPLOYEE (Empl_ID, Dept_ID, Designation) VALUES ('CE008', 'CEE', 'Lecturer'); -- Update some rows UPDATE EMPLOYEE SET Age=27, Joined_date='2017-12-28', Joined_time='00:08:30', Married=TRUE, Security_code='011100' WHERE Empl_ID='EE001'; UPDATE EMPLOYEE SET Age=39, Joined_date='2012-01-07', Joined_time='00:11:30', Married=TRUE, Security_code='110101' WHERE Empl_ID='EE002'; UPDATE EMPLOYEE SET Age=55, Joined_date='2000-05-06', Joined_time='00:10:00', Married=FALSE, Security_code='010111' WHERE Empl_ID='EE005'; -- Select all rows SELECT * FROM EMPLOYEE;