-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathUber.sql
251 lines (214 loc) · 7.22 KB
/
Uber.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
CREATE TABLE Passenger (
p_id NUMBER(30) PRIMARY KEY,
p_phone_no VARCHAR2(30),
p_e_wallet_no VARCHAR2(30) UNIQUE,
p_email VARCHAR2(30),
p_password VARCHAR2(30),
p_name VARCHAR2(30),
p_last_canceled_trip VARCHAR2(30),
p_fine NUMBER(30) DEFAULT 0,
p_trip_count NUMBER(30) DEFAULT 0,
p_rating NUMBER(30),
p_isDeleted NUMBER (1),
p_language VARCHAR2(30)
);
CREATE TABLE Driver (
d_id NUMBER(30) PRIMARY KEY,
d_phone_no VARCHAR2(30),
d_e_wallet_no VARCHAR2(30) ,
d_email VARCHAR2(30) ,
d_password VARCHAR2(30) ,
d_name VARCHAR2(30) ,
d_last_canceled_trip VARCHAR2(30),
d_fine NUMBER(30) DEFAULT 0,
d_trip_count NUMBER(30) DEFAULT 0,
d_rating NUMBER(30),
d_isDeleted NUMBER (1),
d_car_type VARCHAR2(30) ,
d_car_model VARCHAR2(30),
d_plate_no VARCHAR2(30) ,
d_avail_status NUMBER(1)
);
CREATE TABLE Trip_Requests (
t_id NUMBER(30) PRIMARY KEY,
ps_id NUMBER(30),
estimated_cost NUMBER(30),
start_location VARCHAR2(30),
destination VARCHAR2(30),
ride_type VARCHAR2(30),
trip_start_time VARCHAR2(30) ,
estimated_end_time VARCHAR2(30) ,
payment_method VARCHAR2(30),
trip_status VARCHAR2(30),
promocode VARCHAR2(30),
CONSTRAINT passenger_id_FK FOREIGN KEY (ps_id) REFERENCES Passenger(p_id)
);
CREATE TABLE Accepted_Trip_Requests (
t_id NUMBER(30) PRIMARY KEY,
ps_id NUMBER(30),
dr_id NUMBER(30),
estimated_cost NUMBER(30),
start_location VARCHAR2(30),
destination VARCHAR2(30),
ride_type VARCHAR2(30),
trip_start_time VARCHAR2(30) ,
estimated_end_time VARCHAR2(30) ,
payment_method VARCHAR2(30),
trip_status VARCHAR2(30),
promocode VARCHAR2(30),
CONSTRAINT passenger_id_FKK FOREIGN KEY (ps_id) REFERENCES Passenger(p_id),
CONSTRAINT driver_id_FKK FOREIGN KEY (dr_id) REFERENCES Driver(d_id)
);
CREATE TABLE Compeleted_Trips (
t_id NUMBER(30) PRIMARY KEY NOT NULL,
ps_id NUMBER(30),
dr_id NUMBER(30),
actual_cost NUMBER(30) NOT NULL,
start_location VARCHAR2(30),
final_destination VARCHAR2(30),
ride_type VARCHAR2(30),
trip_start_time Date ,
trip_end_time Date ,
payment_method VARCHAR2(30),
p_review VARCHAR2(30),
promocode VARCHAR2(30),
CONSTRAINT tid3_FK FOREIGN KEY (ps_id) REFERENCES Passenger(p_id),
CONSTRAINT tid4_FK FOREIGN KEY (dr_id) REFERENCES Driver(d_id)
);
CREATE TABLE Canceled_Requests (
t_id NUMBER(30) PRIMARY KEY NOT NULL,
ps_id NUMBER(30),
dr_id NUMBER(30),
estimated_cost NUMBER(30) NOT NULL,
start_location VARCHAR2(30),
destination VARCHAR2(30),
ride_type VARCHAR2(30),
trip_start_time Date ,
estimated_end_time Date ,
payment_method VARCHAR2(30),
cancelation_time VARCHAR2(30),
promocode VARCHAR2(30),
CONSTRAINT tid5_FK FOREIGN KEY (ps_id) REFERENCES Passenger(p_id),
CONSTRAINT tid6_FK FOREIGN KEY (dr_id) REFERENCES Driver(d_id)
);
CREATE SEQUENCE passenger_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE driver_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE trip_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE accepted_trips_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE canceled_trips_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE completed_trips_seq START WITH 1 INCREMENT BY 1;
CREATE TRIGGER passenger_trigger
BEFORE INSERT ON Passenger
FOR EACH ROW
BEGIN
SELECT passenger_seq.NEXTVAL INTO :new.p_id FROM dual;
END;
CREATE TRIGGER completed_trips_trigger
BEFORE INSERT ON Compeleted_Trips
FOR EACH ROW
BEGIN
SELECT completed_trips_seq.NEXTVAL INTO :new.t_id FROM dual;
END;
CREATE TRIGGER cancel_req_trigger
BEFORE INSERT ON Canceled_Requests
FOR EACH ROW
BEGIN
SELECT canceled_trips_seq.NEXTVAL INTO :new.t_id FROM dual;
END;
CREATE TRIGGER driver_trigger
BEFORE INSERT ON Driver
FOR EACH ROW
BEGIN
SELECT driver_seq.NEXTVAL INTO :new.d_id FROM dual;
END;
CREATE TRIGGER trip_requests_trigger
BEFORE INSERT ON Trip_Requests
FOR EACH ROW
BEGIN
SELECT trip_seq.NEXTVAL INTO :new.t_id FROM dual;
END;
CREATE TRIGGER accepted_trips_trigger
BEFORE INSERT ON Accepted_Trip_Requests
FOR EACH ROW
BEGIN
SELECT accepted_trips_seq.NEXTVAL INTO :new.t_id FROM dual;
END;
CREATE OR REPLACE PROCEDURE update_driver (
driver_id IN VARCHAR2,
phone_number IN VARCHAR2,
e_wallet_number IN VARCHAR2,
email IN VARCHAR2,
password IN VARCHAR2,
name IN VARCHAR2,
car_type IN VARCHAR2,
car_model IN VARCHAR2,
plate_number IN VARCHAR2
)
AS
BEGIN
UPDATE Driver SET
d_phone_no = phone_number,
d_e_wallet_no = e_wallet_number,
d_email = email,
d_password = password,
d_name = name,
d_car_type = car_type,
d_car_model = car_model,
d_plate_no = plate_number
WHERE d_id = driver_id;
END;
CREATE OR REPLACE PROCEDURE update_passenger (
passenger_id IN VARCHAR2,
phone_number IN VARCHAR2,
e_wallet_number IN VARCHAR2,
email IN VARCHAR2,
password IN VARCHAR2,
name IN VARCHAR2,
succeeded OUT NUMBER
)
AS
BEGIN
UPDATE Passenger SET
p_phone_no = phone_number,
p_e_wallet_no = e_wallet_number,
p_email = email,
p_password = password,
p_name = name
WHERE p_id = passenger_id;
succeeded = 1;
END;
CREATE OR REPLACE PROCEDURE get_canceled_requests (
p_id IN NUMBER,
p_results OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN p_results FOR
SELECT * FROM Canceled_Requests
WHERE ps_id = p_id;
END;
INSERT INTO PASSENGER (p_phone_no, p_email, p_password) VALUES ('010123456', '[email protected]', '123');
INSERT INTO Trip_Requests (t_id, ps_id, estimated_cost, start_location, destination, ride_type, payment_method, trip_status, promocode)
VALUES (1, 1, 50.00, '123 Main St', '456 Elm St', 'UberX', 'Credit Card', 'Pending', 'SUMMER20');
INSERT INTO Accepted_Trip_Requests (ps_id, dr_id, estimated_cost, start_location, destination, ride_type, trip_start_time, estimated_end_time, payment_method, trip_status, promocode)
VALUES ( 1, 1, 50.00, '123 Main St', '456 Elm St', 'UberX', '2023-04-26 12:00:00', '2023-04-26 12:30:00', 'Credit Card', 'Pending', 'SUMMER20');
INSERT INTO Compeleted_Trips (ps_id, dr_id, actual_cost, start_location, final_destination, ride_type, trip_start_time, trip_end_time, payment_method, p_review, promocode)
VALUES (1, 4, 50.00, '123 Main St', '456 Elm St', 'UberX', TO_DATE('2023-04-28 08:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2023-04-28 09:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Credit Card', 'Great ride!', 'FIRST10');
INSERT INTO Driver (d_phone_no, d_e_wallet_no, d_email, d_password, d_name, d_car_type, d_car_model, d_plate_no)
VALUES ('111', '2000', '[email protected]', '123', 'test_name', 'test2', 'test2', 'test_plate_number');
INSERT INTO Canceled_Requests ( ps_id, estimated_cost, start_location, destination, ride_type, trip_start_time, estimated_end_time, payment_method, cancelation_time, promocode)
VALUES ( 1, 25.50, 'Alex', 'Washington', 'Regular', '01-JAN-97', '01-JAN-98', 'Credit Card', '2023-04-27 14:35:00', 'XYZ123');
DELETE FROM trip_requests WHERE ps_id = 1;
SELECT * FROM accepted_trip_requests;
SELECT * FROM Trip_requests;
SELECT * FROM Compeleted_Trips;
SELECT * FROM DRIVER;
SELECT * FROM passenger;
SELECT * FROM Canceled_Requests;
SELECT * FROM Trip_Requests WHERE ps_id = 1;
SELECT * FROM Accepted_Trip_Requests WHERE ps_id = 1;
DELETE FROM Canceled_Requests WHERE ps_id = 1;
DELETE FROM accepted_trip_requests WHERE ps_id = 1;
commit;
SELECT estimated_end_time - trip_start_time AS duration
FROM Canceled_Requests;