SQLize | PHPize | SQLtest

CREATE TABLE CAR_T ( CarID int NOT NULL, Name varchar(15) NOT NULL, Model varchar(20) NOT NULL, Color varchar(15) NOT NULL, LicensePlate varchar(10) NOT NULL, CONSTRAINT CAR_T_pk PRIMARY KEY (CarID) ); -- Table: CITY_T CREATE TABLE CITY_T ( CityID int NOT NULL, Name varchar(20) NOT NULL, Province varchar(30) NOT NULL, Country varchar(30) NOT NULL, CONSTRAINT CITY_T_pk PRIMARY KEY (CityID) ); -- Table: COMMUTER_CAR_T CREATE TABLE COMMUTER_CAR_T ( CommuterCarID int NOT NULL, CarID int NOT NULL, CommuterID int NOT NULL, CarRegistrationNumber varchar(20) NOT NULL, CONSTRAINT COMMUTER_CAR_T_pk PRIMARY KEY (CommuterCarID) ); -- Table: COMMUTER_PREFERENCE_T CREATE TABLE COMMUTER_PREFERENCE_T ( CommuterID int NOT NULL, SmokingAllowed varchar(3) NOT NULL, CourseCode1 varchar(10) NOT NULL, CourseCode2 varchar(10) NULL, CourseCode3 varchar(10) NULL, Hobby1 varchar(50) NOT NULL, Hobby2 varchar(50) NULL, AllowDifferentGenderMatching varchar(3) NOT NULL, CONSTRAINT COMMUTER_PREFERENCE_T_pk PRIMARY KEY (CommuterID) ); -- Table: COMMUTER_T CREATE TABLE COMMUTER_T ( CommuterID int NOT NULL, FirstName varchar(15) NOT NULL, LastName varchar(15) NOT NULL, DateOfBirth date NOT NULL, Nationality varchar(20) NOT NULL, Gender varchar(10) NOT NULL, PhoneNumber varchar(20) NOT NULL, Email varchar(50) NOT NULL, UserName varchar(25) NOT NULL, Password varchar(25) NOT NULL, DrivingLicenseNumber varchar(20) NULL, DrivingLicenseValidFrom date NULL, CONSTRAINT COMMUTER_T_pk PRIMARY KEY (CommuterID) ); -- Table: FEEDBACK_T CREATE TABLE FEEDBACK_T ( FeedbackID int NOT NULL, FeedbackText varchar(200) NOT NULL, StarsRanking int NOT NULL, GivenFeedback_CommuterID int NOT NULL, ReceivedFeedback_CommuterID int NOT NULL, CONSTRAINT FEEDBACK_T_pk PRIMARY KEY (FeedbackID) ); -- Table: IMAGE_T CREATE TABLE IMAGE_T ( ImageID int NOT NULL, CommuterID int NOT NULL, URL_Link varchar(100) NULL, CONSTRAINT IMAGE_T_pk PRIMARY KEY (ImageID) ); -- Table: LUGGAGE_SIZE_T CREATE TABLE LUGGAGE_SIZE_T ( LuggageSizeID int NOT NULL, LuggageDescription varchar(30) NOT NULL, CONSTRAINT LUGGAGE_SIZE_T_pk PRIMARY KEY (LuggageSizeID) ); -- Table: REQUEST_STATUS_T CREATE TABLE REQUEST_STATUS_T ( RequestStatusID int NOT NULL, Description varchar(50) NOT NULL, CONSTRAINT REQUEST_STATUS_T_pk PRIMARY KEY (RequestStatusID) ); -- Table: REQUEST_T CREATE TABLE REQUEST_T ( RequestID int NOT NULL, Requester_CommuterID int NOT NULL, RideID int NOT NULL, CreatedOn date NOT NULL, RequestStatusID int NOT NULL, CONSTRAINT REQUEST_T_pk PRIMARY KEY (RequestID) ); -- Table: RIDE_INQUIRY_T CREATE TABLE RIDE_INQUIRY_T ( RideID int NOT NULL, Requested_LuggageSizeID int NOT NULL, CONSTRAINT RIDE_INQUIRY_T_pk PRIMARY KEY (RideID) ); -- Table: RIDE_OFFER_T CREATE TABLE RIDE_OFFER_T ( RideID int NOT NULL, NumberOfSeats int NOT NULL, CostsPerCommuter int NOT NULL, Offered_LuggageSizeID int NOT NULL, CommuterCarID int NOT NULL, CONSTRAINT RIDE_OFFER_T_pk PRIMARY KEY (RideID) ); -- Table: RIDE_T CREATE TABLE RIDE_T ( RideID int NOT NULL, RideStartDate date NOT NULL, RideStartTime time NULL, EstimatedArrivalTime time NULL, Departure_CityID int NOT NULL, Destination_CityID int NOT NULL, CONSTRAINT RIDE_T_pk PRIMARY KEY (RideID) ); -- foreign keys -- Reference: COMMUTER_CAR_T_CAR_T (table: COMMUTER_CAR_T) ALTER TABLE COMMUTER_CAR_T ADD CONSTRAINT COMMUTER_CAR_T_CAR_T FOREIGN KEY COMMUTER_CAR_T_CAR_T (CarID) REFERENCES CAR_T (CarID); -- Reference: COMMUTER_CAR_T_COMMUTER_T (table: COMMUTER_CAR_T) ALTER TABLE COMMUTER_CAR_T ADD CONSTRAINT COMMUTER_CAR_T_COMMUTER_T FOREIGN KEY COMMUTER_CAR_T_COMMUTER_T (CommuterID) REFERENCES COMMUTER_T (CommuterID); -- Reference: COMMUTER_PREFERENCE_T_COMMUTER_T (table: COMMUTER_PREFERENCE_T) ALTER TABLE COMMUTER_PREFERENCE_T ADD CONSTRAINT COMMUTER_PREFERENCE_T_COMMUTER_T FOREIGN KEY COMMUTER_PREFERENCE_T_COMMUTER_T (CommuterID) REFERENCES COMMUTER_T (CommuterID); -- Reference: GIVEN_FEEDBACK_T_COMMUTER_T (table: FEEDBACK_T) ALTER TABLE FEEDBACK_T ADD CONSTRAINT GIVEN_FEEDBACK_T_COMMUTER_T FOREIGN KEY GIVEN_FEEDBACK_T_COMMUTER_T (GivenFeedback_CommuterID) REFERENCES COMMUTER_T (CommuterID); -- Reference: IMAGE_T_COMMUTER_T (table: IMAGE_T) ALTER TABLE IMAGE_T ADD CONSTRAINT IMAGE_T_COMMUTER_T FOREIGN KEY IMAGE_T_COMMUTER_T (CommuterID) REFERENCES COMMUTER_T (CommuterID); -- Reference: RECEIVED_FEEDBACK_T_COMMUTER_T (table: FEEDBACK_T) ALTER TABLE FEEDBACK_T ADD CONSTRAINT RECEIVED_FEEDBACK_T_COMMUTER_T FOREIGN KEY RECEIVED_FEEDBACK_T_COMMUTER_T (ReceivedFeedback_CommuterID) REFERENCES COMMUTER_T (CommuterID); -- Reference: REQUEST_T_COMMUTER_T (table: REQUEST_T) ALTER TABLE REQUEST_T ADD CONSTRAINT REQUEST_T_COMMUTER_T FOREIGN KEY REQUEST_T_COMMUTER_T (Requester_CommuterID) REFERENCES COMMUTER_T (CommuterID); -- Reference: REQUEST_T_REQUEST_STATUS_ID (table: REQUEST_T) ALTER TABLE REQUEST_T ADD CONSTRAINT REQUEST_T_REQUEST_STATUS_ID FOREIGN KEY REQUEST_T_REQUEST_STATUS_ID (RequestStatusID) REFERENCES REQUEST_STATUS_T (RequestStatusID); -- Reference: REQUEST_T_RIDE_T (table: REQUEST_T) ALTER TABLE REQUEST_T ADD CONSTRAINT REQUEST_T_RIDE_T FOREIGN KEY REQUEST_T_RIDE_T (RideID) REFERENCES RIDE_T (RideID); -- Reference: RIDE_INQUIRY_T_LUGGAGE_SIZE_T (table: RIDE_INQUIRY_T) ALTER TABLE RIDE_INQUIRY_T ADD CONSTRAINT RIDE_INQUIRY_T_LUGGAGE_SIZE_T FOREIGN KEY RIDE_INQUIRY_T_LUGGAGE_SIZE_T (Requested_LuggageSizeID) REFERENCES LUGGAGE_SIZE_T (LuggageSizeID); -- Reference: RIDE_INQUIRY_T_RIDE_T (table: RIDE_INQUIRY_T) ALTER TABLE RIDE_INQUIRY_T ADD CONSTRAINT RIDE_INQUIRY_T_RIDE_T FOREIGN KEY RIDE_INQUIRY_T_RIDE_T (RideID) REFERENCES RIDE_T (RideID); -- Reference: RIDE_OFFER_T_COMMUTER_CAR_T (table: RIDE_OFFER_T) ALTER TABLE RIDE_OFFER_T ADD CONSTRAINT RIDE_OFFER_T_COMMUTER_CAR_T FOREIGN KEY RIDE_OFFER_T_COMMUTER_CAR_T (CommuterCarID) REFERENCES COMMUTER_CAR_T (CommuterCarID); -- Reference: RIDE_OFFER_T_LUGGAGE_SIZE_T (table: RIDE_OFFER_T) ALTER TABLE RIDE_OFFER_T ADD CONSTRAINT RIDE_OFFER_T_LUGGAGE_SIZE_T FOREIGN KEY RIDE_OFFER_T_LUGGAGE_SIZE_T (Offered_LuggageSizeID) REFERENCES LUGGAGE_SIZE_T (LuggageSizeID); -- Reference: RIDE_OFFER_T_RIDE_T (table: RIDE_OFFER_T) ALTER TABLE RIDE_OFFER_T ADD CONSTRAINT RIDE_OFFER_T_RIDE_T FOREIGN KEY RIDE_OFFER_T_RIDE_T (RideID) REFERENCES RIDE_T (RideID); -- Reference: RIDE_T_DEPARTUR_CITY_T (table: RIDE_T) ALTER TABLE RIDE_T ADD CONSTRAINT RIDE_T_DEPARTUR_CITY_T FOREIGN KEY RIDE_T_DEPARTUR_CITY_T (Departure_CityID) REFERENCES CITY_T (CityID); -- Reference: RIDE_T_DESTINATION_CITY_T (table: RIDE_T) ALTER TABLE RIDE_T ADD CONSTRAINT RIDE_T_DESTINATION_CITY_T FOREIGN KEY RIDE_T_DESTINATION_CITY_T (Destination_CityID) REFERENCES CITY_T (CityID); INSERT INTO COMMUTER_T VALUES ( 1, 'Caroline', 'Ballier', '1998-10-09', 'German', 'Female', '+491234567', 'c.ballier@student.maastrichtuniversity.nl', 'c.ballier', '0000', '12345', '2016-06-01' ), ( 2, 'Miguel', 'Moederscheim', '1999-12-19', 'Dutch', 'Male', '+315678436', 'm.moederscheim@student.maastrichtuniversity.nl', 'm.moederscheim', '9999', '57362', '2017-01-01' ), ( 3, 'Maik', 'Dijkhoff', '1996-05-14', 'Dutch', 'Male', '+31 16737652', 'm.dijkhoff@student.maastrichtuniversity.nl', 'm.dijkhoff', '362852md', NULL, NULL ), ( 4, 'Lisa', 'Müller', '1997-02-27', 'German', 'Female', '+49 173528463', 'l.müller@student.maastrichtuniversity.nl', 'lisamüller', 'Lisa1234', NULL, NULL ), ( 5, 'Luuce', 'Bernard', '1995-01-30', 'French', 'Male', '0033 2635241', 'l.bernard@student.maastrichtuniversity.nl', 'luuce.m', 'commute2021', NULL, NULL ), ( 6, 'Susanne', 'Bilcaut', '1998-07-30', 'Luxembourg', 'Female', '+352 7362536', 's.bilcaut@student.maastrichtuniversity.nl', 'su.bi', 'studyatUM', NULL, NULL ), ( 7, 'Nicolas', 'Lamarq', '1998-04-25', 'Luxembourg', 'Male', '+352 9473118', 'n.lamarq@student.maastrichtuniversity.nl', 'nico.l', 'UM2021', '648362', '2016-07-15' ); INSERT INTO COMMUTER_PREFERENCE_T VALUES ( 1, 'No', 'EBC4091', 'EBC4264', NULL, 'Sports', 'Food', 'No' ), ( 2, 'No', 'EBC4091', 'EBC4264', NULL, 'Cars', 'Music', 'Yes' ), ( 3, 'No', 'EBC4091', 'EBC5678', 'EBC9876', 'Reading', 'Food', 'Yes' ), ( 4, 'No', 'EBC4091', 'EBC5678', NULL, 'Food', 'Animals', 'No' ), ( 5, 'Yes', 'EBC1234', 'EBC5678', 'EBC2468', 'Books', 'Sports', 'Yes' ), ( 6, 'No', 'EBC1234', 'EBC4091', NULL, 'Sports', NULL, 'Yes' ), ( 7, 'No', 'EBC1234', NULL, NULL, 'Sports', NULL, 'Yes' ); INSERT INTO FEEDBACK_T VALUES ( 80, 'I enjoyed the drive! Great company and we arrived on time!', 5, 6, 7 ); INSERT INTO CAR_T VALUES ( 20, 'VW', 'Polo', 'Black', 'TR-SB-11' ), ( 21, 'Toyota', 'Aygo', 'White', 'Z-123-RT' ), ( 22, 'Audi', 'A1', 'Blue', 'CD-70-05' ); INSERT INTO CITY_T VALUES ( 10, 'Maastricht', 'Limburg', 'Netherlands' ), ( 11, 'Roermond', 'Limburg', 'Netherlands' ), ( 12, 'Trier', 'Rhineland-Palatinate', 'Germany' ), ( 13, 'Luxembourg', 'Luxembourg', 'Luxembourg' ), ( 14, 'Metz', 'Departement Moselle', 'France' ); INSERT INTO REQUEST_STATUS_T VALUES (50, 'Available'), (51, 'Fully booked'), (52, 'Expired'); INSERT INTO LUGGAGE_SIZE_T VALUES (40, 'Small'), (41, 'Medium'), (42, 'Large'); INSERT INTO COMMUTER_CAR_T VALUES (30, 20, 1, '2458362'), (31, 21, 2, '3863512'), (32, 22, 7, '4862549'); INSERT INTO RIDE_T VALUES ( 60, '2021-12-17', '12:00:00', '13:30:00', 10, 12 ), ( 61, '2021-11-10', '09:00:00', '09:45:00', 11, 10 ), ( 62, '2022-01-02', '15:00:00', '18:50:00', 14, 10 ), ( 63, '2021-10-08', '11:30:00', '14:00:00', 13, 10 ), ( 64, '2021-11-10', '08:00:00', '08:45:00', 11, 10 ); INSERT INTO RIDE_OFFER_T VALUES (60, 2, 3, 40, 30), (61, 3, 5, 41, 31), (63, 2, 10, 41, 32), (64, 2, 10, 41, 30); INSERT INTO RIDE_INQUIRY_T VALUES (61, 41), (60, 40), (62, 42), (63, 41); INSERT INTO REQUEST_T VALUES (70, 1, 60, '2021-10-01', 50), (71, 2, 61, '2021-09-29', 50), (72, 3, 61, '2021-09-28', 50), (73, 4, 60, '2021-10-01', 50), (74, 5, 62, '2021-10-03', 50), (75, 6, 63, '2021-09-03', 52), (76, 7, 63, '2021-09-05', 52), (77, 1, 64, '2021-09-28', 51); INSERT INTO IMAGE_T VALUES (90, 1, NULL), ( 91, 2, 'www.dropbox.com/photo001' ), (92, 3, NULL), ( 93, 4, 'www.dropbox.com/photo002' ), ( 94, 5, 'www.dropbox.com/photo003' ), ( 95, 6, 'www.dropbox.com/photo004' ), ( 96, 7, 'www.dropbox.com/photo005' ); SELECT COMMUTER_T.Username, AVG(FEEDBACK_T.StarsRanking), CITY_T.Name, RIDE_T.RideStartTime, RIDE_T.EstimatedArrivalTime, RIDE_OFFER_T.NumberOfSeats, RIDE_OFFER_T.CostsPerCommuter, RIDE_OFFER_T.Offered_LuggageSizeID, CAR_T.Name, CAR_T.Model, CAR_T.Color, COMMUTER_PREFERENCE_T.SmokingAllowed, COMMUTER_PREFERENCE_T.AllowDifferentGenderMatching, REQUEST_STATUS_T.Description FROM COMMUTER_T, FEEDBACK_T, IMAGE_T, CITY_T, RIDE_T, RIDE_OFFER_T, CAR_T, COMMUTER_CAR_T, COMMUTER_PREFERENCE_T, REQUEST_T, REQUEST_STATUS_T WHERE COMMUTER_T.CommuterID = 1 AND COMMUTER_T.CommuterID = FEEDBACK_T.ReceivedFeedback_CommuterID AND COMMUTER_T.CommuterID = IMAGE_T.CommuterID AND COMMUTER_T.CommuterID = COMMUTER_PREFERENCE_T.CommuterID AND COMMUTER_T.CommuterID = REQUEST_T.Requester_CommuterID AND REQUEST_T.RequestStatusID = REQUEST_STATUS_T.RequestStatusID AND REQUEST_T.RideID = RIDE_T.RideID AND RIDE_T.RideID = RIDE_OFFER_T.RideID AND COMMUTER_T.CommuterID = COMMUTER_CAR_T.CommuterID AND COMMUTER_CAR_T.CarID = CAR_T.CarID AND RIDE_T.Departure_CityID = CITY_T.CityID AND RIDE_T.Destination_CityID = CITY_T.CityID;