SQLize | PHPize | SQLtest

-- Create the 'Departments' table CREATE TABLE IF NOT EXISTS Departments ( DepartmentID INT PRIMARY KEY AUTO_INCREMENT, DepartmentName VARCHAR(50) UNIQUE NOT NULL ); -- Create the 'Employees' table CREATE TABLE IF NOT EXISTS Employees ( EmployeeID INT PRIMARY KEY AUTO_INCREMENT, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, Email VARCHAR(100) UNIQUE, PhoneNumber VARCHAR(20), HireDate DATE, JobID VARCHAR(30), Salary DECIMAL(10, 2), DepartmentID INT, FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ); -- Data Manipulation Language (DML) Commands for Employees -- Insert data into the 'Departments' table INSERT INTO Departments (DepartmentName) VALUES ('Sales'), ('Marketing'), ('Engineering'), ('Human Resources'); -- Insert data into the 'Employees' table INSERT INTO Employees (FirstName, LastName, Email, PhoneNumber, HireDate, JobID, Salary, DepartmentID) VALUES ('Alice', 'Smith', 'alice.smith@example.com', '123-456-7890', '2023-08-15', 'Sales Manager', 60000.00, 1), ('Bob', 'Johnson', 'bob.johnson@example.com', '987-654-3210', '2022-05-20', 'Marketing Analyst', 55000.00, 2), ('Charlie', 'Brown', 'charlie.brown@example.com', '555-123-4567', '2024-01-10', 'Software Engineer', 70000.00, 3), ('Diana', 'Miller', 'diana.miller@example.com', '111-222-3333', '2023-11-01', 'HR Specialist', 58000.00, 4), ('Eve', 'Williams', 'eve.williams@example.com', '444-555-6666', '2024-03-01', 'Sales Representative', 50000.00, 1); -- Retrieve all employees SELECT * FROM Employees; -- Retrieve specific employee details SELECT FirstName, LastName, Email FROM Employees; -- Retrieve employees in a specific department SELECT e.FirstName, e.LastName FROM Employees e JOIN Departments d ON e.DepartmentID = d.DepartmentID WHERE d.DepartmentName = 'Sales'; -- Update an employee's salary UPDATE Employees SET Salary = 75000.00 WHERE EmployeeID = 3; -- Add a new column to the Employees table (DDL) ALTER TABLE Employees ADD COLUMN DateOfBirth DATE; -- Update the DateOfBirth for some employees (DML) UPDATE Employees SET DateOfBirth = '1990-05-10' WHERE EmployeeID = 1; UPDATE Employees SET DateOfBirth = '1988-12-25' WHERE EmployeeID = 2; -- Retrieve employees ordered by their last name SELECT * FROM Employees ORDER BY LastName; -- Delete an employee DELETE FROM Employees WHERE EmployeeID = 5; -- Remove the DateOfBirth column (DDL) ALTER TABLE Employees DROP COLUMN DateOfBirth;