Oracle Live SQL - Script: FAU Technology Team Inventory Management System

Statement 1

DROP TABLE Vendors CASCADE CONSTRAINTS

Table dropped.

Statement 2

DROP TABLE Equipment CASCADE CONSTRAINTS

Table dropped.

Statement 3

DROP TABLE Employees CASCADE CONSTRAINTS

Table dropped.

Statement 4

CREATE TABLE Employees ( 
    Employee_ID NUMBER PRIMARY KEY, 
    First_Name VARCHAR2(50), 
    Last_Name VARCHAR2(50), 
    Department VARCHAR2(100), 
    Role_Title VARCHAR2(100) 
)

Table created.

Statement 5

CREATE TABLE Equipment ( 
    Equipment_ID NUMBER PRIMARY KEY, 
    Equipment_Name VARCHAR2(100), 
    Equipment_Type VARCHAR2(50), 
    Purchase_Date DATE, 
    Condition_Status VARCHAR2(50) 
)

Table created.

Statement 6

CREATE TABLE Assignments ( 
    Assignment_ID NUMBER PRIMARY KEY, 
    Employee_ID NUMBER REFERENCES Employees(Employee_ID), 
    Equipment_ID NUMBER REFERENCES Equipment(Equipment_ID), 
    Issue_Date DATE, 
    Return_Date DATE 
)

Table created.

Statement 7

CREATE TABLE Maintenance ( 
    Maintenance_ID NUMBER PRIMARY KEY, 
    Equipment_ID NUMBER REFERENCES Equipment(Equipment_ID), 
    Maintenance_Date DATE, 
    Description VARCHAR2(100), 
    Cost NUMBER 
)

Table created.

Statement 8

CREATE TABLE Vendors ( 
    Vendor_ID NUMBER PRIMARY KEY, 
    Vendor_Name VARCHAR2(100), 
    Contact_Info VARCHAR2(100) 
)

Table created.

Statement 9

INSERT INTO Employees VALUES (1, 'Jason', 'Ball', 'OIT Leadership', 'Associate Provost & CIO')

1 row(s) inserted.

Statement 10

INSERT INTO Employees VALUES (2, 'Christian', 'Block', 'Infrastructure', 'Deputy CIO')

1 row(s) inserted.

Statement 11

INSERT INTO Employees VALUES (3, 'Ying', 'Liu', 'Academic Tech', 'Assistant Provost')

1 row(s) inserted.

Statement 12

INSERT INTO Employees VALUES (4, 'Lucy', 'Martucci', 'Fiscal Management', 'Director')

1 row(s) inserted.

Statement 13

INSERT INTO Employees VALUES (5, 'Zachary', 'Macdonald', 'Client Support', 'Manager')

1 row(s) inserted.

Statement 14

INSERT INTO Employees VALUES (6, 'Jennifer', 'Turner', 'Enterprise Support', 'Associate Director')

1 row(s) inserted.

Statement 15

INSERT INTO Equipment VALUES (1, 'HP Monitor', 'Laptop', DATE '2024-05-10', 'Good')

1 row(s) inserted.

Statement 16

INSERT INTO Equipment VALUES (2, 'Dell Laptop', 'Peripheral', DATE '2024-01-05', 'Good')

1 row(s) inserted.

Statement 17

INSERT INTO Equipment VALUES (3, 'Xfinity Router', 'Network', DATE '2024-03-15', 'Needs Repair')

1 row(s) inserted.

Statement 18

INSERT INTO Equipment VALUES (4, 'MacBook Pro', 'Computer', DATE '2024-06-25', 'Excellent')

1 row(s) inserted.

Statement 19

INSERT INTO Equipment VALUES (5, 'Logitech Mouse', 'Peripheral', DATE '2024-07-12', 'Good')

1 row(s) inserted.

Statement 20

INSERT INTO Assignments VALUES (1, 2, 1, DATE '2024-08-17', NULL)

1 row(s) inserted.

Statement 21

INSERT INTO Assignments VALUES (2, 4, 2, DATE '2024-09-01', NULL)

1 row(s) inserted.

Statement 22

INSERT INTO Assignments VALUES (3, 5, 4, DATE '2024-10-15', NULL)

1 row(s) inserted.

Statement 23

INSERT INTO Assignments VALUES (4, 6, 5, DATE '2024-10-20', NULL)

1 row(s) inserted.

Statement 24

INSERT INTO Maintenance VALUES (1, 2, DATE '2024-09-15', 'Battery Replacement', 120)

1 row(s) inserted.

Statement 25

INSERT INTO Maintenance VALUES (2, 4, DATE '2024-10-10', 'Firmware Update', 50)

1 row(s) inserted.

Statement 26

INSERT INTO Maintenance VALUES (3, 5, DATE '2024-10-22', 'Lens Cleansing', 25)

1 row(s) inserted.

Statement 27

INSERT INTO Vendors VALUES (1, 'Tech Supply', 'techsupply@email.com')

1 row(s) inserted.

Statement 28

INSERT INTO Vendors VALUES (2, 'Digital Works', 'support@digitalworks.com')

1 row(s) inserted.

Statement 29

INSERT INTO Vendors VALUES (3, 'Office Gear Solutions', 'officegear@fau.edu')

1 row(s) inserted.

Statement 30

CREATE TABLE Employees ( 
    Employee_ID NUMBER PRIMARY KEY, 
    First_Name VARCHAR2(50), 
    Last_Name VARCHAR2(50), 
    Department VARCHAR2(100), 
    Role_Title VARCHAR2(100) 
)

Table created.

Statement 31

CREATE TABLE Equipment ( 
    Equipment_ID NUMBER PRIMARY KEY, 
    Equipment_Name VARCHAR2(100), 
    Equipment_Type VARCHAR2(50), 
    Purchase_Date DATE, 
    Condition_Status VARCHAR2(50) 
)

Table created.

Statement 32

CREATE TABLE Assignments ( 
    Assignment_ID NUMBER PRIMARY KEY, 
    Employee_ID NUMBER REFERENCES Employees(Employee_ID), 
    Equipment_ID NUMBER REFERENCES Equipment(Equipment_ID), 
    Issue_Date DATE, 
    Return_Date DATE 
)

Table created.

Statement 33

CREATE TABLE Maintenance ( 
    Maintenance_ID NUMBER PRIMARY KEY, 
    Equipment_ID NUMBER REFERENCES Equipment(Equipment_ID), 
    Maintenance_Date DATE, 
    Description VARCHAR2(100), 
    Cost NUMBER 
)

Table created.

Statement 34

CREATE TABLE Vendors ( 
    Vendor_ID NUMBER PRIMARY KEY, 
    Vendor_Name VARCHAR2(100), 
    Contact_Info VARCHAR2(100) 
)

Table created.

Statement 35

BEGIN 
   EXECUTE IMMEDIATE 'DROP TABLE Assignments CASCADE CONSTRAINTS'; 
   EXECUTE IMMEDIATE 'DROP TABLE Maintenance CASCADE CONSTRAINTS'; 
   EXECUTE IMMEDIATE 'DROP TABLE Vendors CASCADE CONSTRAINTS'; 
   EXECUTE IMMEDIATE 'DROP TABLE Equipment CASCADE CONSTRAINTS'; 
   EXECUTE IMMEDIATE 'DROP TABLE Employees CASCADE CONSTRAINTS'; 
EXCEPTION 
   WHEN OTHERS THEN 
      NULL; 
END;

Statement processed.

Statement 36

BEGIN 
   EXECUTE IMMEDIATE 'DROP TABLE Assignments CASCADE CONSTRAINTS'; 
   EXECUTE IMMEDIATE 'DROP TABLE Maintenance CASCADE CONSTRAINTS'; 
   EXECUTE IMMEDIATE 'DROP TABLE Vendors CASCADE CONSTRAINTS'; 
   EXECUTE IMMEDIATE 'DROP TABLE Equipment CASCADE CONSTRAINTS'; 
   EXECUTE IMMEDIATE 'DROP TABLE Employees CASCADE CONSTRAINTS'; 
EXCEPTION 
   WHEN OTHERS THEN 
      NULL; 
END;

Statement processed.

Statement 37

CREATE TABLE Employees ( 
    Employee_ID NUMBER PRIMARY KEY, 
    First_Name VARCHAR2(50), 
    Last_Name VARCHAR2(50), 
    Department VARCHAR2(100), 
    Role_Title VARCHAR2(100) 
)

Table created.

Statement 38

CREATE TABLE Equipment ( 
    Equipment_ID NUMBER PRIMARY KEY, 
    Equipment_Name VARCHAR2(100), 
    Equipment_Type VARCHAR2(50), 
    Purchase_Date DATE, 
    Condition_Status VARCHAR2(50) 
)

Table created.

Statement 39

CREATE TABLE Assignments ( 
    Assignment_ID NUMBER PRIMARY KEY, 
    Employee_ID NUMBER REFERENCES Employees(Employee_ID), 
    Equipment_ID NUMBER REFERENCES Equipment(Equipment_ID), 
    Issue_Date DATE, 
    Return_Date DATE 
)

Table created.

Statement 40

CREATE TABLE Maintenance ( 
    Maintenance_ID NUMBER PRIMARY KEY, 
    Equipment_ID NUMBER REFERENCES Equipment(Equipment_ID), 
    Maintenance_Date DATE, 
    Description VARCHAR2(100), 
    Cost NUMBER 
)

Table created.

Statement 41

CREATE TABLE Vendors ( 
    Vendor_ID NUMBER PRIMARY KEY, 
    Vendor_Name VARCHAR2(100), 
    Contact_Info VARCHAR2(100) 
)

Table created.

Statement 42

INSERT INTO Employees VALUES (1, 'Jason', 'Ball', 'OIT Leadership', 'Associate Provost & CIO')

1 row(s) inserted.

Statement 43

INSERT INTO Employees VALUES (2, 'Christian', 'Block', 'Infrastructure', 'Deputy CIO')

1 row(s) inserted.

Statement 44

INSERT INTO Employees VALUES (3, 'Ying', 'Liu', 'Academic Tech', 'Assistant Provost')

1 row(s) inserted.

Statement 45

INSERT INTO Employees VALUES (4, 'Lucy', 'Martucci', 'Fiscal Management', 'Director')

1 row(s) inserted.

Statement 46

INSERT INTO Employees VALUES (5, 'Zachary', 'Macdonald', 'Client Support', 'Manager')

1 row(s) inserted.

Statement 47

INSERT INTO Employees VALUES (6, 'Jennifer', 'Turner', 'Enterprise Support', 'Associate Director')

1 row(s) inserted.

Statement 48

INSERT INTO Equipment VALUES (1, 'HP Monitor', 'Laptop', DATE '2024-05-10', 'Good')

1 row(s) inserted.

Statement 49

INSERT INTO Equipment VALUES (2, 'Dell Laptop', 'Peripheral', DATE '2024-01-05', 'Good')

1 row(s) inserted.

Statement 50

INSERT INTO Equipment VALUES (3, 'Xfinity Router', 'Network', DATE '2024-03-15', 'Needs Repair')

1 row(s) inserted.

Statement 51

INSERT INTO Equipment VALUES (4, 'MacBook Pro', 'Computer', DATE '2024-06-25', 'Excellent')

1 row(s) inserted.

Statement 52

INSERT INTO Equipment VALUES (5, 'Logitech Mouse', 'Peripheral', DATE '2024-07-12', 'Good')

1 row(s) inserted.

Statement 53

INSERT INTO Assignments VALUES (1, 2, 1, DATE '2024-08-17', NULL)

1 row(s) inserted.

Statement 54

INSERT INTO Assignments VALUES (2, 4, 2, DATE '2024-09-01', NULL)

1 row(s) inserted.

Statement 55

INSERT INTO Assignments VALUES (3, 5, 4, DATE '2024-10-15', NULL)

1 row(s) inserted.

Statement 56

INSERT INTO Assignments VALUES (4, 6, 5, DATE '2024-10-20', NULL)

1 row(s) inserted.

Statement 57

INSERT INTO Maintenance VALUES (1, 2, DATE '2024-09-15', 'Battery Replacement', 120)

1 row(s) inserted.

Statement 58

INSERT INTO Maintenance VALUES (2, 4, DATE '2024-10-10', 'Firmware Update', 50)

1 row(s) inserted.

Statement 59

INSERT INTO Maintenance VALUES (3, 5, DATE '2024-10-22', 'Lens Cleansing', 25)

1 row(s) inserted.

Statement 60

INSERT INTO Vendors VALUES (1, 'Tech Supply', 'techsupply@email.com')

1 row(s) inserted.

Statement 61

INSERT INTO Vendors VALUES (2, 'Digital Works', 'support@digitalworks.com')

1 row(s) inserted.

Statement 62

INSERT INTO Vendors VALUES (3, 'Office Gear Solutions', 'officegear@fau.edu')

1 row(s) inserted.

Statement 63

BEGIN 
   EXECUTE IMMEDIATE 'DROP TABLE Assignments CASCADE CONSTRAINTS'; 
   EXECUTE IMMEDIATE 'DROP TABLE Maintenance CASCADE CONSTRAINTS'; 
   EXECUTE IMMEDIATE 'DROP TABLE Vendors CASCADE CONSTRAINTS'; 
   EXECUTE IMMEDIATE 'DROP TABLE Equipment CASCADE CONSTRAINTS'; 
   EXECUTE IMMEDIATE 'DROP TABLE Employees CASCADE CONSTRAINTS'; 
EXCEPTION 
   WHEN OTHERS THEN 
      NULL; 
END;

Statement processed.

Statement 64

BEGIN 
   EXECUTE IMMEDIATE 'DROP TABLE Assignments CASCADE CONSTRAINTS'; 
   EXECUTE IMMEDIATE 'DROP TABLE Maintenance CASCADE CONSTRAINTS'; 
   EXECUTE IMMEDIATE 'DROP TABLE Vendors CASCADE CONSTRAINTS'; 
   EXECUTE IMMEDIATE 'DROP TABLE Equipment CASCADE CONSTRAINTS'; 
   EXECUTE IMMEDIATE 'DROP TABLE Employees CASCADE CONSTRAINTS'; 
EXCEPTION 
   WHEN OTHERS THEN 
      NULL; 
END;

Statement processed.

Statement 65

BEGIN 
   EXECUTE IMMEDIATE 'DROP TABLE Assignments CASCADE CONSTRAINTS'; 
   EXECUTE IMMEDIATE 'DROP TABLE Maintenance CASCADE CONSTRAINTS'; 
   EXECUTE IMMEDIATE 'DROP TABLE Vendors CASCADE CONSTRAINTS'; 
   EXECUTE IMMEDIATE 'DROP TABLE Equipment CASCADE CONSTRAINTS'; 
   EXECUTE IMMEDIATE 'DROP TABLE Employees CASCADE CONSTRAINTS'; 
EXCEPTION 
   WHEN OTHERS THEN 
      NULL; 
END;

Statement processed.

Statement 66

CREATE TABLE Employees ( 
    Employee_ID NUMBER PRIMARY KEY, 
    First_Name VARCHAR2(50), 
    Last_Name VARCHAR2(50), 
    Department VARCHAR2(100), 
    Role_Title VARCHAR2(100) 
)

Table created.

Statement 67

CREATE TABLE Equipment ( 
    Equipment_ID NUMBER PRIMARY KEY, 
    Equipment_Name VARCHAR2(100), 
    Equipment_Type VARCHAR2(50), 
    Purchase_Date DATE, 
    Condition_Status VARCHAR2(50) 
)

Table created.

Statement 68

CREATE TABLE Assignments ( 
    Assignment_ID NUMBER PRIMARY KEY, 
    Employee_ID NUMBER REFERENCES Employees(Employee_ID), 
    Equipment_ID NUMBER REFERENCES Equipment(Equipment_ID), 
    Issue_Date DATE, 
    Return_Date DATE 
)

Table created.

Statement 69

CREATE TABLE Maintenance ( 
    Maintenance_ID NUMBER PRIMARY KEY, 
    Equipment_ID NUMBER REFERENCES Equipment(Equipment_ID), 
    Maintenance_Date DATE, 
    Description VARCHAR2(100), 
    Cost NUMBER 
)

Table created.

Statement 70

CREATE TABLE Vendors ( 
    Vendor_ID NUMBER PRIMARY KEY, 
    Vendor_Name VARCHAR2(100), 
    Contact_Info VARCHAR2(100) 
)

Table created.

Statement 71

INSERT INTO Employees VALUES (1, 'Jason', 'Ball', 'OIT Leadership', 'Associate Provost & CIO')

1 row(s) inserted.

Statement 72

INSERT INTO Employees VALUES (2, 'Christian', 'Block', 'Infrastructure', 'Deputy CIO')

1 row(s) inserted.

Statement 73

INSERT INTO Employees VALUES (3, 'Ying', 'Liu', 'Academic Tech', 'Assistant Provost')

1 row(s) inserted.

Statement 74

INSERT INTO Employees VALUES (4, 'Lucy', 'Martucci', 'Fiscal Management', 'Director')

1 row(s) inserted.

Statement 75

INSERT INTO Employees VALUES (5, 'Zachary', 'Macdonald', 'Client Support', 'Manager')

1 row(s) inserted.

Statement 76

INSERT INTO Employees VALUES (6, 'Jennifer', 'Turner', 'Enterprise Support', 'Associate Director')

1 row(s) inserted.

Statement 77

INSERT INTO Equipment VALUES (1, 'HP Monitor', 'Laptop', DATE '2024-05-10', 'Good')

1 row(s) inserted.

Statement 78

INSERT INTO Equipment VALUES (2, 'Dell Laptop', 'Peripheral', DATE '2024-01-05', 'Good')

1 row(s) inserted.

Statement 79

INSERT INTO Equipment VALUES (3, 'Xfinity Router', 'Network', DATE '2024-03-15', 'Needs Repair')

1 row(s) inserted.

Statement 80

INSERT INTO Equipment VALUES (4, 'MacBook Pro', 'Computer', DATE '2024-06-25', 'Excellent')

1 row(s) inserted.

Statement 81

INSERT INTO Equipment VALUES (5, 'Logitech Mouse', 'Peripheral', DATE '2024-07-12', 'Good')

1 row(s) inserted.

Statement 82

INSERT INTO Assignments VALUES (1, 2, 1, DATE '2024-08-17', NULL)

1 row(s) inserted.

Statement 83

INSERT INTO Assignments VALUES (2, 4, 2, DATE '2024-09-01', NULL)

1 row(s) inserted.

Statement 84

INSERT INTO Assignments VALUES (3, 5, 4, DATE '2024-10-15', NULL)

1 row(s) inserted.

Statement 85

INSERT INTO Assignments VALUES (4, 6, 5, DATE '2024-10-20', NULL)

1 row(s) inserted.

Statement 86

INSERT INTO Maintenance VALUES (1, 2, DATE '2024-09-15', 'Battery Replacement', 120)

1 row(s) inserted.

Statement 87

INSERT INTO Maintenance VALUES (2, 4, DATE '2024-10-10', 'Firmware Update', 50)

1 row(s) inserted.

Statement 88

INSERT INTO Maintenance VALUES (3, 5, DATE '2024-10-22', 'Lens Cleansing', 25)

1 row(s) inserted.

Statement 89

INSERT INTO Vendors VALUES (1, 'Tech Supply', 'techsupply@email.com')

1 row(s) inserted.

Statement 90

INSERT INTO Vendors VALUES (2, 'Digital Works', 'support@digitalworks.com')

1 row(s) inserted.

Statement 91

INSERT INTO Vendors VALUES (3, 'Office Gear Solutions', 'officegear@fau.edu')

1 row(s) inserted.

Statement 92

SELECT * FROM Employees


EMPLOYEE_ID FIRST_NAME LAST_NAME DEPARTMENT ROLE_TITLE
1 Jason Ball OIT Leadership Associate Provost & CIO
2 Christian Block Infrastructure Deputy CIO
3 Ying Liu Academic Tech Assistant Provost
4 Lucy Martucci Fiscal Management Director
5 Zachary Macdonald Client Support Manager
6 Jennifer Turner Enterprise Support Associate Director

6 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	DEPARTMENT	ROLE_TITLE
1	Jason	Ball	OIT Leadership	Associate Provost & CIO
2	Christian	Block	Infrastructure	Deputy CIO
3	Ying	Liu	Academic Tech	Assistant Provost
4	Lucy	Martucci	Fiscal Management	Director
5	Zachary	Macdonald	Client Support	Manager
6	Jennifer	Turner	Enterprise Support	Associate Director

Statement 93

SELECT * FROM Equipment


EQUIPMENT_ID EQUIPMENT_NAME EQUIPMENT_TYPE PURCHASE_DATE CONDITION_STATUS
1 HP Monitor Laptop 10-MAY-24 Good
2 Dell Laptop Peripheral 05-JAN-24 Good
3 Xfinity Router Network 15-MAR-24 Needs Repair
4 MacBook Pro Computer 25-JUN-24 Excellent
5 Logitech Mouse Peripheral 12-JUL-24 Good

5 rows selected.

EQUIPMENT_ID	EQUIPMENT_NAME	EQUIPMENT_TYPE	PURCHASE_DATE	CONDITION_STATUS
1	HP Monitor	Laptop	10-MAY-24	Good
2	Dell Laptop	Peripheral	05-JAN-24	Good
3	Xfinity Router	Network	15-MAR-24	Needs Repair
4	MacBook Pro	Computer	25-JUN-24	Excellent
5	Logitech Mouse	Peripheral	12-JUL-24	Good

Statement 94

SELECT * FROM Assignments


ASSIGNMENT_ID EMPLOYEE_ID EQUIPMENT_ID ISSUE_DATE RETURN_DATE
1 2 1 17-AUG-24  - 
2 4 2 01-SEP-24  - 
3 5 4 15-OCT-24  - 
4 6 5 20-OCT-24  - 

4 rows selected.

ASSIGNMENT_ID	EMPLOYEE_ID	EQUIPMENT_ID	ISSUE_DATE	RETURN_DATE
1	2	1	17-AUG-24	-
2	4	2	01-SEP-24	-
3	5	4	15-OCT-24	-
4	6	5	20-OCT-24	-

Statement 95

SELECT * FROM Maintenance


MAINTENANCE_ID EQUIPMENT_ID MAINTENANCE_DATE DESCRIPTION COST
1 2 15-SEP-24 Battery Replacement 120
2 4 10-OCT-24 Firmware Update 50
3 5 22-OCT-24 Lens Cleansing 25

3 rows selected.

MAINTENANCE_ID	EQUIPMENT_ID	MAINTENANCE_DATE	DESCRIPTION	COST
1	2	15-SEP-24	Battery Replacement	120
2	4	10-OCT-24	Firmware Update	50
3	5	22-OCT-24	Lens Cleansing	25

Statement 96

SELECT * FROM Vendors


VENDOR_ID VENDOR_NAME CONTACT_INFO
1 Tech Supply techsupply@email.com
2 Digital Works support@digitalworks.com
3 Office Gear Solutions officegear@fau.edu

3 rows selected.

VENDOR_ID	VENDOR_NAME	CONTACT_INFO
1	Tech Supply	techsupply@email.com
2	Digital Works	support@digitalworks.com
3	Office Gear Solutions	officegear@fau.edu

Statement 97

SELECT  
    e.First_Name || ' ' || e.Last_Name AS Employee_Name, 
    e.Department, 
    eq.Equipment_Name, 
    eq.Equipment_Type, 
    eq.Condition_Status, 
    a.Issue_Date 
FROM Employees e 
JOIN Assignments a ON e.Employee_ID = a.Employee_ID 
JOIN Equipment eq ON a.Equipment_ID = eq.Equipment_ID


EMPLOYEE_NAME DEPARTMENT EQUIPMENT_NAME EQUIPMENT_TYPE CONDITION_STATUS ISSUE_DATE
Christian Block Infrastructure HP Monitor Laptop Good 17-AUG-24
Lucy Martucci Fiscal Management Dell Laptop Peripheral Good 01-SEP-24
Zachary Macdonald Client Support MacBook Pro Computer Excellent 15-OCT-24
Jennifer Turner Enterprise Support Logitech Mouse Peripheral Good 20-OCT-24

4 rows selected.

EMPLOYEE_NAME	DEPARTMENT	EQUIPMENT_NAME	EQUIPMENT_TYPE	CONDITION_STATUS	ISSUE_DATE
Christian Block	Infrastructure	HP Monitor	Laptop	Good	17-AUG-24
Lucy Martucci	Fiscal Management	Dell Laptop	Peripheral	Good	01-SEP-24
Zachary Macdonald	Client Support	MacBook Pro	Computer	Excellent	15-OCT-24
Jennifer Turner	Enterprise Support	Logitech Mouse	Peripheral	Good	20-OCT-24

Statement 98

BEGIN 
   EXECUTE IMMEDIATE 'DROP TABLE Assignments CASCADE CONSTRAINTS'; 
   EXECUTE IMMEDIATE 'DROP TABLE Maintenance CASCADE CONSTRAINTS'; 
   EXECUTE IMMEDIATE 'DROP TABLE Vendors CASCADE CONSTRAINTS'; 
   EXECUTE IMMEDIATE 'DROP TABLE Equipment CASCADE CONSTRAINTS'; 
   EXECUTE IMMEDIATE 'DROP TABLE Employees CASCADE CONSTRAINTS'; 
EXCEPTION 
   WHEN OTHERS THEN 
      NULL; 
END;

Statement processed.

Statement 99

CREATE TABLE Employees ( 
    Employee_ID NUMBER PRIMARY KEY, 
    First_Name VARCHAR2(50), 
    Last_Name VARCHAR2(50), 
    Department VARCHAR2(100), 
    Role_Title VARCHAR2(100) 
)

Table created.

Statement 100

CREATE TABLE Equipment ( 
    Equipment_ID NUMBER PRIMARY KEY, 
    Equipment_Name VARCHAR2(100), 
    Equipment_Type VARCHAR2(50), 
    Purchase_Date DATE, 
    Condition_Status VARCHAR2(50) 
)

Table created.

Statement 101

CREATE TABLE Assignments ( 
    Assignment_ID NUMBER PRIMARY KEY, 
    Employee_ID NUMBER REFERENCES Employees(Employee_ID), 
    Equipment_ID NUMBER REFERENCES Equipment(Equipment_ID), 
    Issue_Date DATE, 
    Return_Date DATE 
)

Table created.

Statement 102

CREATE TABLE Maintenance ( 
    Maintenance_ID NUMBER PRIMARY KEY, 
    Equipment_ID NUMBER REFERENCES Equipment(Equipment_ID), 
    Maintenance_Date DATE, 
    Description VARCHAR2(100), 
    Cost NUMBER 
)

Table created.

Statement 103

CREATE TABLE Vendors ( 
    Vendor_ID NUMBER PRIMARY KEY, 
    Vendor_Name VARCHAR2(100), 
    Contact_Info VARCHAR2(100) 
)

Table created.

Statement 104

INSERT INTO Employees VALUES (1, 'Jason', 'Ball', 'OIT Leadership', 'Associate Provost & CIO')

1 row(s) inserted.

Statement 105

INSERT INTO Employees VALUES (2, 'Christian', 'Block', 'Infrastructure', 'Deputy CIO')

1 row(s) inserted.

Statement 106

INSERT INTO Employees VALUES (3, 'Ying', 'Liu', 'Academic Tech', 'Assistant Provost')

1 row(s) inserted.

Statement 107

INSERT INTO Employees VALUES (4, 'Lucy', 'Martucci', 'Fiscal Management', 'Director')

1 row(s) inserted.

Statement 108

INSERT INTO Employees VALUES (5, 'Zachary', 'Macdonald', 'Client Support', 'Manager')

1 row(s) inserted.

Statement 109

INSERT INTO Employees VALUES (6, 'Jennifer', 'Turner', 'Enterprise Support', 'Associate Director')

1 row(s) inserted.

Statement 110

INSERT INTO Equipment VALUES (1, 'HP Monitor', 'Laptop', DATE '2024-05-10', 'Good')

1 row(s) inserted.

Statement 111

INSERT INTO Equipment VALUES (2, 'Dell Laptop', 'Peripheral', DATE '2024-01-05', 'Good')

1 row(s) inserted.

Statement 112

INSERT INTO Equipment VALUES (3, 'Xfinity Router', 'Network', DATE '2024-03-15', 'Needs Repair')

1 row(s) inserted.

Statement 113

INSERT INTO Equipment VALUES (4, 'MacBook Pro', 'Computer', DATE '2024-06-25', 'Excellent')

1 row(s) inserted.

Statement 114

INSERT INTO Equipment VALUES (5, 'Logitech Mouse', 'Peripheral', DATE '2024-07-12', 'Good')

1 row(s) inserted.

Statement 115

INSERT INTO Assignments VALUES (1, 2, 1, DATE '2024-08-17', NULL)

1 row(s) inserted.

Statement 116

INSERT INTO Assignments VALUES (2, 4, 2, DATE '2024-09-01', NULL)

1 row(s) inserted.

Statement 117

INSERT INTO Assignments VALUES (3, 5, 4, DATE '2024-10-15', NULL)

1 row(s) inserted.

Statement 118

INSERT INTO Assignments VALUES (4, 6, 5, DATE '2024-10-20', NULL)

1 row(s) inserted.

Statement 119

INSERT INTO Maintenance VALUES (1, 2, DATE '2024-09-15', 'Battery Replacement', 120)

1 row(s) inserted.

Statement 120

INSERT INTO Maintenance VALUES (2, 4, DATE '2024-10-10', 'Firmware Update', 50)

1 row(s) inserted.

Statement 121

INSERT INTO Maintenance VALUES (3, 5, DATE '2024-10-22', 'Lens Cleansing', 25)

1 row(s) inserted.

Statement 122

INSERT INTO Vendors VALUES (1, 'Tech Supply', 'techsupply@email.com')

1 row(s) inserted.

Statement 123

INSERT INTO Vendors VALUES (2, 'Digital Works', 'support@digitalworks.com')

1 row(s) inserted.

Statement 124

INSERT INTO Vendors VALUES (3, 'Office Gear Solutions', 'officegear@fau.edu')

1 row(s) inserted.

Statement 125

SELECT * FROM Employees


EMPLOYEE_ID FIRST_NAME LAST_NAME DEPARTMENT ROLE_TITLE
1 Jason Ball OIT Leadership Associate Provost & CIO
2 Christian Block Infrastructure Deputy CIO
3 Ying Liu Academic Tech Assistant Provost
4 Lucy Martucci Fiscal Management Director
5 Zachary Macdonald Client Support Manager
6 Jennifer Turner Enterprise Support Associate Director

6 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	DEPARTMENT	ROLE_TITLE
1	Jason	Ball	OIT Leadership	Associate Provost & CIO
2	Christian	Block	Infrastructure	Deputy CIO
3	Ying	Liu	Academic Tech	Assistant Provost
4	Lucy	Martucci	Fiscal Management	Director
5	Zachary	Macdonald	Client Support	Manager
6	Jennifer	Turner	Enterprise Support	Associate Director

Statement 126

SELECT * FROM Equipment


EQUIPMENT_ID EQUIPMENT_NAME EQUIPMENT_TYPE PURCHASE_DATE CONDITION_STATUS
1 HP Monitor Laptop 10-MAY-24 Good
2 Dell Laptop Peripheral 05-JAN-24 Good
3 Xfinity Router Network 15-MAR-24 Needs Repair
4 MacBook Pro Computer 25-JUN-24 Excellent
5 Logitech Mouse Peripheral 12-JUL-24 Good

5 rows selected.

EQUIPMENT_ID	EQUIPMENT_NAME	EQUIPMENT_TYPE	PURCHASE_DATE	CONDITION_STATUS
1	HP Monitor	Laptop	10-MAY-24	Good
2	Dell Laptop	Peripheral	05-JAN-24	Good
3	Xfinity Router	Network	15-MAR-24	Needs Repair
4	MacBook Pro	Computer	25-JUN-24	Excellent
5	Logitech Mouse	Peripheral	12-JUL-24	Good

Statement 127

SELECT * FROM Assignments


ASSIGNMENT_ID EMPLOYEE_ID EQUIPMENT_ID ISSUE_DATE RETURN_DATE
1 2 1 17-AUG-24  - 
2 4 2 01-SEP-24  - 
3 5 4 15-OCT-24  - 
4 6 5 20-OCT-24  - 

4 rows selected.

ASSIGNMENT_ID	EMPLOYEE_ID	EQUIPMENT_ID	ISSUE_DATE	RETURN_DATE
1	2	1	17-AUG-24	-
2	4	2	01-SEP-24	-
3	5	4	15-OCT-24	-
4	6	5	20-OCT-24	-

Statement 128

SELECT * FROM Maintenance


MAINTENANCE_ID EQUIPMENT_ID MAINTENANCE_DATE DESCRIPTION COST
1 2 15-SEP-24 Battery Replacement 120
2 4 10-OCT-24 Firmware Update 50
3 5 22-OCT-24 Lens Cleansing 25

3 rows selected.

MAINTENANCE_ID	EQUIPMENT_ID	MAINTENANCE_DATE	DESCRIPTION	COST
1	2	15-SEP-24	Battery Replacement	120
2	4	10-OCT-24	Firmware Update	50
3	5	22-OCT-24	Lens Cleansing	25

Statement 129

SELECT * FROM Vendors


VENDOR_ID VENDOR_NAME CONTACT_INFO
1 Tech Supply techsupply@email.com
2 Digital Works support@digitalworks.com
3 Office Gear Solutions officegear@fau.edu

3 rows selected.

VENDOR_ID	VENDOR_NAME	CONTACT_INFO
1	Tech Supply	techsupply@email.com
2	Digital Works	support@digitalworks.com
3	Office Gear Solutions	officegear@fau.edu

Statement 130

SELECT  
    e.First_Name || ' ' || e.Last_Name AS Employee_Name, 
    e.Department, 
    eq.Equipment_Name, 
    eq.Equipment_Type, 
    eq.Condition_Status, 
    a.Issue_Date 
FROM Employees e 
JOIN Assignments a ON e.Employee_ID = a.Employee_ID 
JOIN Equipment eq ON a.Equipment_ID = eq.Equipment_ID


EMPLOYEE_NAME DEPARTMENT EQUIPMENT_NAME EQUIPMENT_TYPE CONDITION_STATUS ISSUE_DATE
Christian Block Infrastructure HP Monitor Laptop Good 17-AUG-24
Lucy Martucci Fiscal Management Dell Laptop Peripheral Good 01-SEP-24
Zachary Macdonald Client Support MacBook Pro Computer Excellent 15-OCT-24
Jennifer Turner Enterprise Support Logitech Mouse Peripheral Good 20-OCT-24

4 rows selected.

EMPLOYEE_NAME	DEPARTMENT	EQUIPMENT_NAME	EQUIPMENT_TYPE	CONDITION_STATUS	ISSUE_DATE
Christian Block	Infrastructure	HP Monitor	Laptop	Good	17-AUG-24
Lucy Martucci	Fiscal Management	Dell Laptop	Peripheral	Good	01-SEP-24
Zachary Macdonald	Client Support	MacBook Pro	Computer	Excellent	15-OCT-24
Jennifer Turner	Enterprise Support	Logitech Mouse	Peripheral	Good	20-OCT-24

Statement 131

SELECT * FROM Employees


EMPLOYEE_ID FIRST_NAME LAST_NAME DEPARTMENT ROLE_TITLE
1 Jason Ball OIT Leadership Associate Provost & CIO
2 Christian Block Infrastructure Deputy CIO
3 Ying Liu Academic Tech Assistant Provost
4 Lucy Martucci Fiscal Management Director
5 Zachary Macdonald Client Support Manager
6 Jennifer Turner Enterprise Support Associate Director

6 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	DEPARTMENT	ROLE_TITLE
1	Jason	Ball	OIT Leadership	Associate Provost & CIO
2	Christian	Block	Infrastructure	Deputy CIO
3	Ying	Liu	Academic Tech	Assistant Provost
4	Lucy	Martucci	Fiscal Management	Director
5	Zachary	Macdonald	Client Support	Manager
6	Jennifer	Turner	Enterprise Support	Associate Director

Statement 132

SELECT * FROM Equipment


EQUIPMENT_ID EQUIPMENT_NAME EQUIPMENT_TYPE PURCHASE_DATE CONDITION_STATUS
1 HP Monitor Laptop 10-MAY-24 Good
2 Dell Laptop Peripheral 05-JAN-24 Good
3 Xfinity Router Network 15-MAR-24 Needs Repair
4 MacBook Pro Computer 25-JUN-24 Excellent
5 Logitech Mouse Peripheral 12-JUL-24 Good

5 rows selected.

EQUIPMENT_ID	EQUIPMENT_NAME	EQUIPMENT_TYPE	PURCHASE_DATE	CONDITION_STATUS
1	HP Monitor	Laptop	10-MAY-24	Good
2	Dell Laptop	Peripheral	05-JAN-24	Good
3	Xfinity Router	Network	15-MAR-24	Needs Repair
4	MacBook Pro	Computer	25-JUN-24	Excellent
5	Logitech Mouse	Peripheral	12-JUL-24	Good

Statement 133

SELECT * FROM Assignments


ASSIGNMENT_ID EMPLOYEE_ID EQUIPMENT_ID ISSUE_DATE RETURN_DATE
1 2 1 17-AUG-24  - 
2 4 2 01-SEP-24  - 
3 5 4 15-OCT-24  - 
4 6 5 20-OCT-24  - 

4 rows selected.

ASSIGNMENT_ID	EMPLOYEE_ID	EQUIPMENT_ID	ISSUE_DATE	RETURN_DATE
1	2	1	17-AUG-24	-
2	4	2	01-SEP-24	-
3	5	4	15-OCT-24	-
4	6	5	20-OCT-24	-

Statement 134

SELECT * FROM Maintenance


MAINTENANCE_ID EQUIPMENT_ID MAINTENANCE_DATE DESCRIPTION COST
1 2 15-SEP-24 Battery Replacement 120
2 4 10-OCT-24 Firmware Update 50
3 5 22-OCT-24 Lens Cleansing 25

3 rows selected.

MAINTENANCE_ID	EQUIPMENT_ID	MAINTENANCE_DATE	DESCRIPTION	COST
1	2	15-SEP-24	Battery Replacement	120
2	4	10-OCT-24	Firmware Update	50
3	5	22-OCT-24	Lens Cleansing	25

Statement 135

SELECT * FROM Vendors


VENDOR_ID VENDOR_NAME CONTACT_INFO
1 Tech Supply techsupply@email.com
2 Digital Works support@digitalworks.com
3 Office Gear Solutions officegear@fau.edu

3 rows selected.

FAU Technology Team Inventory Management System - Group 2_ISM4133

Additional Information