DROP TABLE CUSTOMER CASCADE CONSTRAINTS;
CREATE TABLE CUSTOMER (
Customer_ID INTEGER,
Customer_Phone# INTEGER CHECK (Customer_Phone# <= 9999999999),
Customer_Fname VARCHAR2(30),
Customer_Lname VARCHAR2(30),
Customer_License# INTEGER CHECK (Customer_License# <= 999999999),
Customer_Addr VARCHAR2(150),
Customer_Complaint VARCHAR2(240),
PRIMARY KEY(Customer_ID)
);
DROP TABLE EMPLOYEE CASCADE CONSTRAINTS;
CREATE TABLE EMPLOYEE (
Employee_ID INTEGER,
Service_ID INTEGER,
Employee_Phone# INTEGER CHECK (Employee_Phone# <= 9999999999),
Employee_Dep VARCHAR2(50),
Employee_Fname VARCHAR2(30),
Employee_Lname VARCHAR2(30),
Employee_Pos VARCHAR2(50),
PRIMARY KEY(Employee_ID)
);
DROP TABLE SERVICE CASCADE CONSTRAINTS;
CREATE TABLE SERVICE (
Service_ID INTEGER,
VIN INTEGER,
Service_Info VARCHAR2(512),
Service_Tech VARCHAR2(80),
Service_Started DATE,
Service_Status VARCHAR2(20),
PRIMARY KEY(Service_ID)
);
DROP TABLE VEHICLE CASCADE CONSTRAINTS;
CREATE TABLE VEHICLE (
VIN INTEGER,
Vehicle_ID INTEGER,
Vehicle_Year DATE,
Vehicle_Make VARCHAR2(40),
Vehicle_Color VARCHAR2(40),
Vehicle_Motor VARCHAR2(50),
Vehicle_Mileage INTEGER,
Vehicle_Status VARCHAR2(20),
PRIMARY KEY(VIN)
);
insert into CUSTOMER values (00100, 6313052122, 'Bill', 'Smith', 1132459, '1 Deer Lane Manorville NY 11949', 'Low air in tire');
insert into CUSTOMER values (00110, 6312941025, 'Paul', 'Brown', 2733598, '5 Christine Court Riverhead NY 11738', 'Car jolts when shifting in drive');
insert into CUSTOMER values (00120, 5165230134, 'Ashley', 'Cali', 4852189, '25 Roe Ct. Mastic NY 11544', 'Engine light on');
insert into CUSTOMER values (00130, 5162318876, 'John', 'Zimmerman', 1145952, '70 Bay Meadow St. Hampton Bays NY 11489', 'Front bumper damage');
insert into CUSTOMER values (00140, 6312214399, 'Neff', 'Wallace', 3225821, '92 West St. Browmsburg NY 11354', 'Oil leak');
insert into CUSTOMER values (00150, 6319984590, 'Ashley', 'Lorence', 5539012, '1711 53rd Lane Woodhaven NY 11421', 'AC wont blow cool air');
insert into CUSTOMER values (00160, 6313052122, 'Victoria', 'Vasquez', 8335612, '9204 Harrison St. Plainview NY 11803', 'Car is overheating');
insert into CUSTOMER values (00170, 5162341067, 'Lee', 'Wanton', 2164578, '99 Tarkilin Hill St. NY 11701', 'Driver seat lose');
insert into CUSTOMER values (00180, 6312942355, 'Zelda', 'Ween', 4582145, ' 3 Terrance Meadow St. Hampton Bays NY 11489', 'Clunking from rear end');
insert into CUSTOMER values (00190, 6313052122, 'Bill', 'Cosby', 9478812, '30 Christine Court Riverhead NY 11738', 'Brake pedal is mushy');
insert into EMPLOYEE values (99901, 30010, 6312993245, 'Sales', 'Pat', 'Dorny', 'Salesman');
insert into EMPLOYEE values (99902, 30020, 6317562134, 'Sales', 'Justin', 'Merc', 'Salesman');
insert into EMPLOYEE values (99903, 30030, 6315668790, 'Sales', 'Bob', 'Bonito', 'Salesman');
insert into EMPLOYEE values (99904, 30040, 6313695532, 'Sales', 'Ashley', 'Lewis', 'Salesman');
insert into EMPLOYEE values (99905, 30050, 5163305478, 'Sales', 'Marissa', 'Sanon', 'Salesman');
insert into EMPLOYEE values (99906, 30060, 5162228902, 'Sales', 'Paul', 'LaBart', 'Salesman');
insert into EMPLOYEE values (99907, 30070, 6312348876, 'Sales', 'Enrico', 'Suarez', 'Salesman');
insert into EMPLOYEE values (99908, 30080, 5169907812, 'Sales', 'Jr', 'Poposhka', 'Salesman');
insert into EMPLOYEE values (99909, 30090, 6313902476, 'Sales', 'Tim', 'Toolman', 'Salesman');
insert into EMPLOYEE values (99910, 30100, 6319804666, 'Sales', 'Selina', 'Horonda', 'Salesman');
insert into SERVICE values (30010, 12739115790237455 , 'Tire needed patch nail in tire', '90010', '03-APR-19', 'DONE');
insert into SERVICE values (30020, 12739115790225633 , '', '', '', 'NOT WORKED');
insert into SERVICE values (30030, 12739115790200287 , 'Needs 02 Sensor', '90020', '27-MAR-19', 'WORKING');
insert into SERVICE values (30040, 12739115790294336 , 'Front bumper mounts are broken, need new bumper', '90020', '01-APR-19', 'WORKING');
insert into SERVICE values (30050, 12739115790288345 , 'Needed new oil Pan', '90010', '02-APR-19', 'DONE');
insert into SERVICE values (30060, 12739115790247328 , '', '', '', 'NOT WORKED');
insert into SERVICE values (30070, 12739115790296365 , 'Needs head gaskets and thermostat', '90030', '20-MAR-19', 'WORKING');
insert into SERVICE values (30080, 12739115790245991, 'Needed new seat track', '90030', '05-FEB-19', 'DONE');
insert into SERVICE values (30090, 12739115790221663 , '', '', '', 'NOT WORKED');
insert into SERVICE values (30100, 12739115790233487 , 'Needs front and rear pads/rotors', '90040', '02-MAR-19', 'WORKING');
insert into VEHICLE values (12739115790237455, 50000, '14-FEB-19', 'Honda', 'BLU', '1.5L', 15210, 'DONE');
insert into VEHICLE values (12739115790225633, 50100, '05-MAR-01', 'Ford', 'GRN', '6.7L', 20000, 'NOT WORKED');
insert into VEHICLE values (12739115790200287, 50200, '07-DEC-16', 'Lincoln', 'BLK', '4.0L', 67020, 'WORKING');
insert into VEHICLE values (12739115790294336, 50300, '09-AUG-12', 'Ford', 'GRY', '6.0L', 43000, 'WORKING');
insert into VEHICLE values (12739115790288345, 50400, '23-SEP-04', 'Ford', 'RED CHERRY', '10.0L', 5000, 'DONE');
insert into VEHICLE values (12739115790247328, 50500, '27-JUL-15', 'Chevy', 'YEL', '4.0L', 100000, 'NOT WORKED');
insert into VEHICLE values (12739115790296365, 50600, '02-MAY-12', 'Honda', 'BLU MIST', '1.5L', 30000, 'WORKING');
insert into VEHICLE values (12739115790245991, 50700, '09-AUG-12', 'GMC', 'WHI', '7.3L', 250000, 'DONE');
insert into VEHICLE values (12739115790221663, 50800, '14-FEB-19', 'Honda', 'BLU', '1.5L', 20430, 'NOT WORKED');
insert into VEHICLE values (12739115790233487, 50900, '06-DEC-98', 'Toyota', 'WHI', '5.7L', 86321, 'WORKING');