SQLize Online / PHPize Online  /  SQLtest Online

-- Hint: use Ctrl+Enter for SQL autocomplete -- schema CREATE TABLE Departments ( Id INT NOT NULL, Name VARCHAR(25) NOT NULL, PRIMARY KEY(Id) ); CREATE TABLE Employees ( Id INT NOT NULL, FName VARCHAR(35) NOT NULL, LName VARCHAR(35) NOT NULL, PhoneNumber VARCHAR(11), ManagerId INT, DepartmentId INT NOT NULL, Salary INT NOT NULL, HireDate DATE NOT NULL, PRIMARY KEY(Id), FOREIGN KEY (ManagerId) REFERENCES Employees(Id), FOREIGN KEY (DepartmentId) REFERENCES Departments(Id) ); CREATE TABLE Customers ( Id INT NOT NULL, FName VARCHAR(35) NOT NULL, LName VARCHAR(35) NOT NULL, Email varchar(100) NOT NULL, PhoneNumber VARCHAR(11), PreferredContact VARCHAR(5) NOT NULL, PRIMARY KEY(Id) ); CREATE TABLE Sales ( Id INT NOT NULL, CustomerId INT, EmployeeId INT NOT NULL, Model varchar(50) NOT NULL, Status varchar(25) NOT NULL, SaleDate DATE NOT NULL, TotalCost INT, PRIMARY KEY(Id), FOREIGN KEY (CustomerId) REFERENCES Customers(Id), FOREIGN KEY (EmployeeId) REFERENCES Employees(Id) ); -- data INSERT INTO Departments (Id, Name) VALUES (1, 'HR'), (2, 'Sales'), (3, 'Tech'), (4, 'Data'), (5, 'Research') ; INSERT INTO Employees (Id, FName, LName, PhoneNumber, ManagerId, DepartmentId, Salary, HireDate) VALUES (1, 'James', 'Smith', 1234567890, NULL, 1, 2000, TO_DATE('01-01-2002', '%d-%m-%Y')), (2, 'John', 'Johnson', 2468101214, '1', 1, 400, TO_DATE('23-03-2005', '%d-%m-%Y')), (3, 'Michael', 'Williams', 1357911131, '1', 2, 600, TO_DATE('12-05-2009', '%d-%m-%Y')), (4, 'Johnathon', 'Smith', 1212121212, '2', 1, 500, TO_DATE('24-07-2016', '%d-%m-%Y')), (5, 'Benjamin', 'Barros', 6345333432, NULL, 4, 2200, TO_DATE('10-12-2018', '%d-%m-%Y')), (6, 'Carlos', 'Regis', 12125654212, '2', 5, 1200, TO_DATE('19-01-2019', '%d-%m-%Y')) ; INSERT INTO Customers (Id, FName, LName, Email, PhoneNumber, PreferredContact) VALUES (1, 'William', 'Jones', 'william.jones@example.com', '3347927472', 'PHONE'), (2, 'David', 'Miller', 'dmiller@example.net', '2137921892', 'EMAIL'), (3, 'Richard', 'Davis', 'richard0123@example.com', NULL, 'EMAIL'), (4, 'Matias', 'Carballo', 'mcarballo@example.com', NULL, 'EMAIL'), (5, 'Marina', 'Saavedra', 'marinasaav12@example.com', '23123125', 'PHONE') ; INSERT INTO Sales (Id, CustomerId, EmployeeId, Model, Status, TotalCost, SaleDate) VALUES ('1', '1', '2', 'Ford F-150', 'READY', 230, TO_DATE('14-02-2002', '%d-%m-%Y')), ('2', NULL, '2', 'Ford F-150', 'READY', 200, TO_DATE('01-05-2005', '%d-%m-%Y')), ('3', '2', '1', 'Ford Mustang', 'WAITING', 100, TO_DATE('21-03-2002', '%d-%m-%Y')), ('4', '3', '3', 'Toyota Prius', 'WORKING', 1254, TO_DATE('07-07-2009', '%d-%m-%Y')), ('5', NULL, '5', 'Ford Mustang', 'READY', 3100, TO_DATE('15-04-2017', '%d-%m-%Y')), ('6', '1', '2', 'Honda Civic', 'WAITING', 560, TO_DATE('10-01-2017', '%d-%m-%Y')), ('7','4' , '1', 'Honda Civic', 'READY', 600, TO_DATE('26-12-2019', '%d-%m-%Y')), ('8', '2', '5', 'Ford Mustang', 'READY', 150, TO_DATE('22-05-2002', '%d-%m-%Y')), ('9', '2', '2', 'Ford Mustang', 'READY', 150, TO_DATE('11-05-2005', '%d-%m-%Y')) ;