SQLize | PHPize | SQLtest

# create tables CREATE TABLE Department ( DepartmentID INT(7) PRIMARY KEY, Dept_Name VARCHAR(30) NOT NULL, ManagerID INT(7) NULL ); CREATE TABLE Employee ( EmployeeID INT(7) PRIMARY KEY, E_FName VARCHAR(20) NOT NULL, E_LName VARCHAR(30) NOT NULL, E_Salary FLOAT(8, 2 ) NOT NULL, E_BirthDate DATE NOT NULL, E_HireDate DATE NOT NULL, DepartmentID INT(7) NULL, FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID) ON DELETE SET NULL ); CREATE TABLE Team ( TeamID INT(7) PRIMARY KEY, Team_Name VARCHAR(30) NOT NULL, Coach_FName VARCHAR(20) NOT NULL, Coach_LName VARCHAR(30) NOT NULL, City VARCHAR(20) NOT NULL, State VARCHAR(20) NOT NULL ); CREATE TABLE Event ( EventID INT(7) PRIMARY KEY, Event_DateTime DATETIME NOT NULL, Sport_Type VARCHAR(20) NOT NULL, SeatsAvailable INT(4) NOT NULL, HomeTeamID INT(7) NULL, VisitorTeamID INT(7) NULL, FOREIGN KEY (HomeTeamID) REFERENCES Team (TeamID) ON DELETE SET NULL, FOREIGN KEY (VisitorTeamID) REFERENCES Team (TeamID) ON DELETE SET NULL ); CREATE TABLE SeasonPass ( SeasonPass_ID INT(7) PRIMARY KEY, ExpirationDate DATE NOT NULL, CustomerID INT(10) NULL ); CREATE TABLE Customer ( CustomerID INT(10) PRIMARY KEY, C_FName VARCHAR(20) NOT NULL, C_LName VARCHAR(30) NOT NULL, SeasonPass_ID INT(7) NULL, FOREIGN KEY (SeasonPass_ID) REFERENCES SeasonPass (SeasonPass_ID) ON DELETE SET NULL ); CREATE TABLE Task ( TaskID INT(7) PRIMARY KEY, Task_Name VARCHAR(30) NOT NULL ); CREATE TABLE TaskAssignment ( TaskAssan_ID INT(7) PRIMARY KEY, TaskID INT(7) NOT NULL, EmployeeID INT(7) NOT NULL, TA_DateTime DATETIME NOT NULL, FOREIGN KEY (TaskID) REFERENCES Task (TaskID) ); CREATE TABLE Food ( FoodItemID INT(7) PRIMARY KEY, Food_Type VARCHAR(15) NOT NULL, Food_name VARCHAR(15) NOT NULL, Food_price FLOAT(5, 2) NOT NULL ); CREATE TABLE FoodOrders ( OrderID INT(7) PRIMARY KEY, EmployeeID INT(7) NOT NULL, CustomerID INT(10) NULL, FoodItemID INT(7) NULL, F_DateTime DATETIME DEFAULT CURRENT_TIMESTAMP, Quantity INT(2) NOT NULL ); CREATE TABLE Attendance ( TicketNumber INT(7) PRIMARY KEY, CustomerID INT(10) NULL, EventID INT(7) NULL, Ticket_Price FLOAT(5, 2) NOT NULL, FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID) ON DELETE SET NULL ); #alters ALTER TABLE Department ADD CONSTRAINT fl_Department_Manager FOREIGN KEY (ManagerID) REFERENCES Employee (EmployeeID) ON DELETE SET NULL; ALTER TABLE SeasonPass ADD CONSTRAINT fk_SeasonPass_Customer FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID) ON DELETE SET NULL; ALTER TABLE FoodOrders ADD CONSTRAINT fk_foodorders_fooditem FOREIGN KEY (FoodItemID) REFERENCES Food(FoodItemID); ALTER TABLE FoodOrders ADD CONSTRAINT fk_foodorders_employee FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID); ALTER TABLE FoodOrders ADD CONSTRAINT fk_foodorders_customer FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID); #Modify Foodorder Table ALTER TABLE FoodOrders MODIFY COLUMN F_DateTime DATETIME DEFAULT CURRENT_TIMESTAMP; INSERT INTO Department (DepartmentID, Dept_Name) VALUES (501, 'Ticketing'), (502, 'Food Service'), (503, 'Gift Shop'), (504, 'Maintenance'), (505, 'Security'); INSERT INTO Employee (EmployeeID, E_FName, E_LName, E_Salary, E_BirthDate, E_HireDate, DepartmentID) VALUES (301, 'Rozan', 'Maghrbi', 5000.00, '1990-05-15', '2013-11-1', 501), (302, 'Sara', 'Ghamdi', 6000.00, '1985-12-10', '2016-08-15', 502), (303, 'Khailed', 'Zahrani', 5500.00, '1992-07-20', '2017-04-10', 503), (304, 'Majeed', 'Fahad', 7000.00, '1988-02-03', '2018-01-22', 504), (305, 'Mohammed', 'Qahtani', 4800.00, '1995-09-25', '2019-07-01', 505), (306, 'Tuleen', 'Amoudi', 5300.00, '1993-08-12', '2020-03-15', 503), (307, 'Reyouf', 'Alotibe', 4900.00, '1987-11-05', '2021-05-22', 504), (308, 'Norah', 'Fahad', 5700.00, '1991-02-28', '2022-01-30', 505); UPDATE Department SET ManagerID = 301 WHERE DepartmentID = 501; UPDATE Department SET ManagerID = 302 WHERE DepartmentID = 502; UPDATE Department SET ManagerID = 303 WHERE DepartmentID = 503; UPDATE Department SET ManagerID = 304 WHERE DepartmentID = 504; UPDATE Department SET ManagerID = 305 WHERE DepartmentID = 505; INSERT INTO SeasonPass (SeasonPass_ID,ExpirationDate) VALUES (1, '2023-12-31'), (2, '2023-11-30'), (3, '2023-10-31'), (4, '2023-09-30'), (5, '2023-08-31'), (6, '2023-07-31'), (7, '2023-06-30'), (8, '2023-05-31'); INSERT INTO Team (TeamID, Team_Name, Coach_FName, Coach_LName, City, State) VALUES (101, 'Team A', 'Coach John', 'Smith', 'City A', 'State A'), (102, 'Team B', 'Coach Jane', 'Johnson', 'City B', 'State B'), (103, 'Team C', 'Coach Alice', 'Williams', 'City C', 'State C'), (104, 'Team D', 'Coach Bob', 'Brown', 'City D', 'State D'), (105, 'Team E', 'Coach Eva', 'Miller', 'City E', 'State E'), (106, 'Team F', 'Coach Charlie', 'Davis', 'City F', 'State F'), (107, 'Team G', 'Coach Sophia', 'Martinez', 'City G', 'State G'); INSERT INTO Event (EventID, Event_DateTime, Sport_Type, SeatsAvailable, HomeTeamID, VisitorTeamID) VALUES (201, '2023-04-15 18:00:00', 'Basketball', 150, 101, 102), (202, '2023-05-20 15:30:00', 'Volleyball', 120, 103, 104), (203, '2023-06-10 20:00:00', 'Tennis', 100, 105, 106), (204, '2023-07-05 19:15:00', 'Soccer', 200, 107, 101), (205, '2023-08-18 16:45:00', 'Basketball', 180, 102, 103), (206, '2023-09-12 17:30:00', 'Volleyball', 130, 104, 105), (207, '2023-10-25 21:00:00', 'Tennis', 90, 106, 107); INSERT INTO Food (FoodItemID, Food_Type, Food_name, Food_price) VALUES (401, 'Drink', 'Soda', 3.50), (402, 'Snack', 'Popcorn', 2.00), (403, 'Main Dish', 'Burger', 5.50), (404, 'Drink', 'Water', 2.00); INSERT INTO Task (TaskID, Task_Name) VALUES (601, 'Sell Tickets'), (602, 'Prepare Food'), (603, 'Manage Inventory'), (604, 'Maintenance Work'), (605, 'Security Check'); INSERT INTO Attendance (TicketNumber, CustomerID, EventID, Ticket_Price) VALUES (1, 1111111111, 201, 50.00), (2, 2222222222 202, 40.00), (3, 3333333333, 203, 60.00), (4, 4444444444, 204, 45.00), (5, 5555555555, 205, 55.00), (6, 6666666666, 206, 30.00), (7, 7777777777, 207, 70.00); INSERT INTO FoodOrders (OrderID, EmployeeID, CustomerID, FoodItemID, F_DateTime, Quantity) VALUES (1, 301, 1111111111, 401, '2023-3-16 10:23:08', 9), (2, 302, 2222222222, 402, '2023-3-17 10:23:08', 10), (3, 303, 3333333333, 403, '2023-3-17 10:23:08', 16), (4, 304, 4444444444, 404, '2019-3-18 10:23:08', 12), (5, 305, 5555555555, 401, '2023-3-19 10:23:08', 9), (6, 301, 6666666666, 402, '2023-3-17 10:23:08', 11), (7, 302, 7777777777, 403, '2019-3-16 10:23:08', 10), (8, 303, 8888888888, 404, '2019-3-19 10:23:08', 8); INSERT INTO TaskAssignment (TaskAssan_ID, TaskID, EmployeeID, TA_DateTime) VALUES (701, 601, 301, '2023-04-01 09:00:00'), (702, 602, 302, '2023-04-02 10:30:00'), (703, 603, 303, '2023-04-03 11:45:00'), (704, 604, 304, '2023-04-04 14:00:00'), (705, 605, 305, '2023-04-05 15:30:00'), (706, 601, 306, '2023-04-06 09:15:00'), (707, 602, 307, '2023-04-07 10:45:00'), (708, 603, 308, '2023-04-08 12:00:00'), (709, 604, 301, '2023-04-09 14:15:00'), (710, 605, 302, '2023-04-10 15:45:00'), (711, 601, 303, '2023-04-11 09:30:00'), (712, 602, 304, '2023-04-12 11:00:00'), (713, 603, 305, '2023-04-13 12:15:00'), (714, 604, 306, '2023-04-14 14:30:00'), (715, 605, 307, '2023-04-15 16:00:00'), (716, 601, 308, '2023-04-16 09:45:00');