CREATE TABLE Destinations (
destination_id INT PRIMARY KEY,
name VARCHAR(100),
country VARCHAR(100),
region VARCHAR(100),
description TEXT
);
CREATE TABLE TourPackages (
package_id INT PRIMARY KEY,
name VARCHAR(100),
duration INT,
price FLOAT,
description TEXT,
destination_id INT,
FOREIGN KEY (destination_id) REFERENCES Destinations(destination_id)
);
CREATE TABLE Tours (
tour_id INT PRIMARY KEY,
package_id INT,
start_date DATE,
end_date DATE,
guide_id INT,
max_tourists INT,
FOREIGN KEY (package_id) REFERENCES TourPackages(package_id),
FOREIGN KEY (guide_id) REFERENCES Guides(guide_id)
);
CREATE TABLE Tourists (
tourist_id INT PRIMARY KEY,
name VARCHAR(100),
contact_info VARCHAR(255),
passport_number VARCHAR(50),
nationality VARCHAR(100)
);
CREATE TABLE Bookings (
booking_id INT PRIMARY KEY,
tour_id INT,
tourist_id INT,
booking_date DATE,
status VARCHAR(50),
FOREIGN KEY (tour_id) REFERENCES Tours(tour_id),
FOREIGN KEY (tourist_id) REFERENCES Tourists(tourist_id)
);
CREATE TABLE Guides (
guide_id INT PRIMARY KEY,
name VARCHAR(100),
contact_info VARCHAR(255),
languages_spoken VARCHAR(100),
experience INT
);
CREATE TABLE Accommodations (
accommodation_id INT PRIMARY KEY,
name VARCHAR(100),
type VARCHAR(50),
location VARCHAR(255),
contact_info VARCHAR(255),
rating FLOAT
);
CREATE TABLE TourAccommodations (
tour_id INT,
accommodation_id INT,
start_date DATE,
end_date DATE,
PRIMARY KEY (tour_id, accommodation_id),
FOREIGN KEY (tour_id) REFERENCES Tours(tour_id),
FOREIGN KEY (accommodation_id) REFERENCES Accommodations(accommodation_id)
);
CREATE TABLE Transport (
transport_id INT PRIMARY KEY,
type VARCHAR(50),
company VARCHAR(100),
contact_info VARCHAR(255),
capacity INT
);
CREATE TABLE TourTransports (
tour_id INT,
transport_id INT,
departure_time DATETIME,
arrival_time DATETIME,
PRIMARY KEY (tour_id, transport_id),
FOREIGN KEY (tour_id) REFERENCES Tours(tour_id),
FOREIGN KEY (transport_id) REFERENCES Transport(transport_id)
);
CREATE TABLE Reviews (
review_id INT PRIMARY KEY,
tour_id INT,
tourist_id INT,
rating INT,
comment TEXT,
review_date DATE,
FOREIGN KEY (tour_id) REFERENCES Tours(tour_id),
FOREIGN KEY (tourist_id) REFERENCES Tourists(tourist_id)
);
CREATE TABLE TourItinerary (
itinerary_id INT PRIMARY KEY,
tour_id INT,
day_number INT,
activity_description TEXT,
location VARCHAR(255),
FOREIGN KEY (tour_id) REFERENCES Tours(tour_id)
);
CREATE TABLE TourActivities (
activity_id INT PRIMARY KEY,
name VARCHAR(100),
description TEXT,
type VARCHAR(50),
duration INT
);
CREATE TABLE ItineraryActivities (
itinerary_id INT,
activity_id INT,
start_time TIME,
end_time TIME,
PRIMARY KEY (itinerary_id, activity_id),
FOREIGN KEY (itinerary_id) REFERENCES TourItinerary(itinerary_id),
FOREIGN KEY (activity_id) REFERENCES TourActivities(activity_id)
);
CREATE TABLE SpecialOffers (
offer_id INT PRIMARY KEY,
description TEXT,
discount_percentage FLOAT,
valid_from DATE,
valid_to DATE,
package_id INT,
FOREIGN KEY (package_id) REFERENCES TourPackages(package_id)
);
INSERT INTO Destinations (destination_id, name, country, region, description) VALUES
(1, 'Paris', 'France', 'Île-de-France', 'The capital city of France, known for its art, fashion, and culture.'),
(2, 'Tokyo', 'Japan', 'Kanto', 'The bustling capital city of Japan, known for its skyscrapers, shopping, and rich history.'),
(3, 'Cairo', 'Egypt', 'Cairo Governorate', 'The sprawling capital of Egypt, set on the Nile River with a rich historical heritage.'),
(4, 'New York', 'USA', 'New York', 'The largest city in the USA, known for its iconic skyline, Central Park, and Broadway theaters.'),
(5, 'Sydney', 'Australia', 'New South Wales', 'The largest city in Australia, known for its Sydney Opera House and Harbour Bridge.'),
(6, 'Rio de Janeiro', 'Brazil', 'Rio de Janeiro', 'A large seaside city in Brazil, famous for its Copacabana and Ipanema beaches.'),
(7, 'Rome', 'Italy', 'Lazio', 'The capital city of Italy, known for its nearly 3,000 years of globally influential art and architecture.'),
(8, 'Cape Town', 'South Africa', 'Western Cape', 'A port city on South Africa’s southwest coast, on a peninsula beneath the imposing Table Mountain.'),
(9, 'Bangkok', 'Thailand', 'Central Thailand', 'The capital of Thailand, known for its vibrant street life and cultural landmarks.'),
(10, 'London', 'UK', 'England', 'The capital and largest city of England and the United Kingdom, known for its history and landmarks.');
INSERT INTO Guides (guide_id, name, contact_info, languages_spoken, experience) VALUES
(1, 'John Smith', 'john@example.com', 'English, Spanish', 5),
(2, 'Maria Garcia', 'maria@example.com', 'English, French, Spanish', 7),
(3, 'Ahmed Ali', 'ahmed@example.com', 'English, Arabic', 10),
(4, 'Hiroshi Tanaka', 'hiroshi@example.com', 'Japanese, English', 3),
(5, 'Fatima Hassan', 'fatima@example.com', 'English, Swahili', 6),
(6, 'Emma Johnson', 'emma@example.com', 'English, German', 8),
(7, 'Luigi Rossi', 'luigi@example.com', 'Italian, English', 12),
(8, 'Chen Wei', 'chen@example.com', 'Mandarin, English', 4),
(9, 'Carlos Silva', 'carlos@example.com', 'Portuguese, English', 9),
(10, 'Amara Okafor', 'amara@example.com', 'English, French', 5);
INSERT INTO TourPackages (package_id, name, duration, price, description, destination_id) VALUES
(1, 'Paris City Tour', 5, 1500.00, 'Explore the main attractions of Paris with a local guide.', 1),
(2, 'Tokyo Highlights', 7, 2000.00, 'Experience the best of Tokyo, from temples to modern marvels.', 2),
(3, 'Cairo Adventure', 4, 1200.00, 'Discover the ancient wonders of Cairo.', 3),
(4, 'New York City Explorer', 6, 1800.00, 'See the top sights of New York City with a knowledgeable guide.', 4),
(5, 'Sydney Experience', 5, 1600.00, 'Enjoy the highlights of Sydney, including the Opera House and beaches.', 5),
(6, 'Rio Carnival Special', 7, 2200.00, 'Experience the vibrant Rio Carnival and explore the city.', 6),
(7, 'Rome Historic Tour', 4, 1300.00, 'Walk through the ancient streets of Rome and visit historic sites.', 7),
(8, 'Cape Town Adventure', 6, 1700.00, 'Discover the beauty of Cape Town and its surroundings.', 8),
(9, 'Bangkok Cultural Tour', 5, 1400.00, 'Experience the cultural landmarks of Bangkok.', 9),
(10, 'London Classic', 7, 1900.00, 'Explore the iconic landmarks of London with an expert guide.', 10);
INSERT INTO Tourists (tourist_id, name, contact_info, passport_number, nationality) VALUES
(1, 'Alice Brown', 'alice@example.com', 'A1234567', 'USA'),
(2, 'Bob Smith', 'bob@example.com', 'B2345678', 'Canada'),
(3, 'Charlie Johnson', 'charlie@example.com', 'C3456789', 'UK'),
(4, 'David Wilson', 'david@example.com', 'D4567890', 'Australia'),
(5, 'Emma Davis', 'emma@example.com', 'E5678901', 'New Zealand'),
(6, 'Fiona White', 'fiona@example.com', 'F6789012', 'South Africa'),
(7, 'George Brown', 'george@example.com', 'G7890123', 'Ireland'),
(8, 'Hannah Martin', 'hannah@example.com', 'H8901234', 'Germany'),
(9, 'Ian Lee', 'ian@example.com', 'I9012345', 'Singapore'),
(10, 'Jack Taylor', 'jack@example.com', 'J0123456', 'USA'),
(11, 'Lily Evans', 'lily@example.com', 'K2345678', 'USA'),
(12, 'James Potter', 'james@example.com', 'L3456789', 'Canada'),
(13, 'Severus Snape', 'severus@example.com', 'M4567890', 'UK'),
(14, 'Remus Lupin', 'remus@example.com', 'N5678901', 'Australia'),
(15, 'Sirius Black', 'sirius@example.com', 'O6789012', 'New Zealand'),
(16, 'Peter Pettigrew', 'peter@example.com', 'P7890123', 'South Africa'),
(17, 'Alice Longbottom', 'alice.l@example.com', 'Q8901234', 'Ireland'),
(18, 'Frank Longbottom', 'frank.l@example.com', 'R9012345', 'Germany'),
(19, 'Minerva McGonagall', 'minerva@example.com', 'S0123456', 'Singapore'),
(20, 'Albus Dumbledore', 'albus@example.com', 'T1234567', 'USA'),
(21, 'Hermione Granger', 'hermione@example.com', 'U2345678', 'USA'),
(22, 'Ron Weasley', 'ron@example.com', 'V3456789', 'Canada'),
(23, 'Harry Potter', 'harry@example.com', 'W4567890', 'UK'),
(24, 'Neville Longbottom', 'neville@example.com', 'X5678901', 'Australia'),
(25, 'Ginny Weasley', 'ginny@example.com', 'Y6789012', 'New Zealand');
INSERT INTO Tours (tour_id, package_id, start_date, end_date, guide_id, max_tourists) VALUES
(1, 1, '2024-07-01', '2024-07-06', 1, 20),
(2, 2, '2024-07-10', '2024-07-17', 2, 25),
(3, 3, '2024-07-05', '2024-07-09', 3, 15),
(4, 4, '2024-07-20', '2024-07-25', 4, 30),
(5, 5, '2024-07-15', '2024-07-20', 5, 18),
(6, 6, '2024-08-01', '2024-08-08', 6, 22),
(7, 7, '2024-08-10', '2024-08-14', 7, 16),
(8, 8, '2024-08-20', '2024-08-25', 8, 24),
(9, 9, '2024-09-01', '2024-09-06', 9, 20),
(10, 10, '2024-09-10', '2024-09-17', 10, 25),
(11, 1, '2024-07-05', '2024-07-10', 1, 25),
(12, 2, '2024-08-01', '2024-08-08', 2, 30),
(13, 3, '2024-08-15', '2024-08-20', 3, 20),
(14, 4, '2024-09-05', '2024-09-10', 4, 35),
(15, 5, '2024-09-15', '2024-09-20', 5, 20),
(16, 6, '2024-10-01', '2024-10-08', 6, 25),
(17, 7, '2024-10-10', '2024-10-15', 7, 20),
(18, 8, '2024-10-20', '2024-10-25', 8, 30),
(19, 9, '2024-11-01', '2024-11-06', 9, 25),
(20, 10, '2024-11-10', '2024-11-17', 10, 30),
(21, 1, '2024-11-20', '2024-11-25', 1, 20),
(22, 2, '2024-12-01', '2024-12-08', 2, 25),
(23, 3, '2024-12-10', '2024-12-15', 3, 20),
(24, 4, '2024-12-20', '2024-12-25', 4, 30),
(25, 5, '2025-01-01', '2025-01-06', 5, 25);
INSERT INTO Bookings (booking_id, tour_id, tourist_id, booking_date, status) VALUES
(1, 1, 1, '2024-06-01', 'Confirmed'),
(2, 2, 2, '2024-06-10', 'Pending'),
(3, 3, 3, '2024-06-05', 'Confirmed'),
(4, 4, 4, '2024-06-20', 'Cancelled'),
(5, 5, 5, '2024-06-15', 'Confirmed'),
(6, 6, 6, '2024-07-01', 'Pending'),
(7, 7, 7, '2024-07-10', 'Confirmed'),
(8, 8, 8, '2024-07-20', 'Confirmed'),
(9, 9, 9, '2024-08-01', 'Cancelled'),
(10, 10, 10, '2024-08-10', 'Confirmed'),
(11, 11, 11, '2024-06-15', 'Confirmed'),
(12, 12, 12, '2024-07-01', 'Pending'),
(13, 13, 13, '2024-07-15', 'Confirmed'),
(14, 14, 14, '2024-08-01', 'Cancelled'),
(15, 15, 15, '2024-08-15', 'Confirmed'),
(16, 16, 16, '2024-09-01', 'Pending'),
(17, 17, 17, '2024-09-15', 'Confirmed'),
(18, 18, 18, '2024-10-01', 'Confirmed'),
(19, 19, 19, '2024-10-15', 'Cancelled'),
(20, 20, 20, '2024-11-01', 'Confirmed'),
(21, 21, 21, '2024-11-15', 'Confirmed'),
(22, 22, 22, '2024-12-01', 'Pending'),
(23, 23, 23, '2024-12-15', 'Confirmed'),
(24, 24, 24, '2024-12-20', 'Cancelled'),
(25, 25, 25, '2025-01-01', 'Confirmed'),
(26, 26, 4, '2025-01-01', 'Confirmed');
INSERT INTO Accommodations (accommodation_id, name, type, location, contact_info, rating) VALUES
(1, 'Hotel Paris', 'Hotel', 'Paris, France', 'contact@hotelparis.com', 4.5),
(2, 'Tokyo Inn', 'Hotel', 'Tokyo, Japan', 'contact@tokyoinn.com', 4.7),
(3, 'Cairo Suites', 'Hotel', 'Cairo, Egypt', 'contact@cairosuites.com', 4.3),
(4, 'NYC Apartments', 'Apartment', 'New York, USA', 'contact@nycapartments.com', 4.6),
(5, 'Sydney Lodge', 'Hotel', 'Sydney, Australia', 'contact@sydneylodge.com', 4.8),
(6, 'Rio Hostel', 'Hostel', 'Rio de Janeiro, Brazil', 'contact@riohostel.com', 4.2),
(7, 'Rome Villa', 'Villa', 'Rome, Italy', 'contact@romevilla.com', 4.9),
(8, 'Cape Town Guesthouse', 'Guesthouse', 'Cape Town, South Africa', 'contact@capetownguesthouse.com', 4.4),
(9, 'Bangkok Resort', 'Resort', 'Bangkok, Thailand', 'contact@bangkokresort.com', 4.6),
(10, 'London Inn', 'Hotel', 'London, UK', 'contact@londoninn.com', 4.7),
(11, 'Paris Inn', 'Hotel', 'Paris, France', 'contact@parisinn.com', 4.5),
(12, 'Tokyo House', 'Hotel', 'Tokyo, Japan', 'contact@tokyohouse.com', 4.7),
(13, 'Cairo Hotel', 'Hotel', 'Cairo, Egypt', 'contact@cairohotel.com', 4.3),
(14, 'NYC Suites', 'Apartment', 'New York, USA', 'contact@nycsuites.com', 4.6),
(15, 'Sydney House', 'Hotel', 'Sydney, Australia', 'contact@sydneyhouse.com', 4.8),
(16, 'Rio Apartments', 'Hostel', 'Rio de Janeiro, Brazil', 'contact@rioapartments.com', 4.2),
(17, 'Rome House', 'Villa', 'Rome, Italy', 'contact@romehouse.com', 4.9),
(18, 'Cape Town Apartments', 'Guesthouse', 'Cape Town, South Africa', 'contact@capetownapartments.com', 4.4),
(19, 'Bangkok House', 'Resort', 'Bangkok, Thailand', 'contact@bangkokhouse.com', 4.6),
(20, 'London House', 'Hotel', 'London, UK', 'contact@londonhouse.com', 4.7),
(21, 'Paris Villas', 'Villa', 'Paris, France', 'contact@parisvillas.com', 4.5),
(22, 'Tokyo Suites', 'Hotel', 'Tokyo, Japan', 'contact@tokyosuites.com', 4.7),
(23, 'Cairo Apartments', 'Apartment', 'Cairo, Egypt', 'contact@cairoapartments.com', 4.3),
(24, 'NYC Hostel', 'Hostel', 'New York, USA', 'contact@nychostel.com', 4.6),
(25, 'Sydney Villas', 'Villa', 'Sydney, Australia', 'contact@sydneyvillas.com', 4.8);
INSERT INTO TourAccommodations (tour_id, accommodation_id, start_date, end_date) VALUES
(1, 1, '2024-07-01', '2024-07-06'),
(2, 2, '2024-07-10', '2024-07-17'),
(3, 3, '2024-07-05', '2024-07-09'),
(4, 4, '2024-07-20', '2024-07-25'),
(5, 5, '2024-07-15', '2024-07-20'),
(6, 6, '2024-08-01', '2024-08-08'),
(7, 7, '2024-08-10', '2024-08-14'),
(8, 8, '2024-08-20', '2024-08-25'),
(9, 9, '2024-09-01', '2024-09-06'),
(10, 10, '2024-09-10', '2024-09-17'),
(11, 11, '2024-07-05', '2024-07-10'),
(12, 12, '2024-08-01', '2024-08-08'),
(13, 13, '2024-08-15', '2024-08-20'),
(14, 14, '2024-09-05', '2024-09-10'),
(15, 15, '2024-09-15', '2024-09-20'),
(16, 16, '2024-10-01', '2024-10-08'),
(17, 17, '2024-10-10', '2024-10-15'),
(18, 18, '2024-10-20', '2024-10-25'),
(19, 19, '2024-11-01', '2024-11-06'),
(20, 20, '2024-11-10', '2024-11-17'),
(21, 21, '2024-11-20', '2024-11-25'),
(22, 22, '2024-12-01', '2024-12-08'),
(23, 23, '2024-12-10', '2024-12-15'),
(24, 24, '2024-12-20', '2024-12-25'),
(25, 25, '2025-01-01', '2025-01-06'),
(26, 5, '2025-01-01', '2025-09-01');
INSERT INTO Transport (transport_id, type, company, contact_info, capacity) VALUES
(1, 'Bus', 'Travel Co.', 'contact@travelco.com', 50),
(2, 'Plane', 'Airways Inc.', 'contact@airwaysinc.com', 180),
(3, 'Train', 'Railway Ltd.', 'contact@railwayltd.com', 200),
(4, 'Boat', 'Marine Services', 'contact@marineservices.com', 100),
(5, 'Helicopter', 'HeliTours', 'contact@helitours.com', 5),
(6, 'Bus', 'Travel Co.', 'contact@travelco.com', 50),
(7, 'Plane', 'Airways Inc.', 'contact@airwaysinc.com', 180),
(8, 'Train', 'Railway Ltd.', 'contact@railwayltd.com', 200),
(9, 'Boat', 'Marine Services', 'contact@marineservices.com', 100),
(10, 'Helicopter', 'HeliTours', 'contact@helitours.com', 5);
INSERT INTO TourTransports (tour_id, transport_id, departure_time, arrival_time) VALUES
(1, 1, '2024-07-01 08:00:00', '2024-07-01 10:00:00'),
(2, 2, '2024-07-10 09:00:00', '2024-07-10 11:00:00'),
(3, 3, '2024-07-05 07:00:00', '2024-07-05 09:00:00'),
(4, 4, '2024-07-20 06:00:00', '2024-07-20 08:00:00'),
(5, 5, '2024-07-15 08:00:00', '2024-07-15 10:00:00'),
(6, 6, '2024-08-01 09:00:00', '2024-08-01 11:00:00'),
(7, 7, '2024-08-10 07:00:00', '2024-08-10 09:00:00'),
(8, 8, '2024-08-20 06:00:00', '2024-08-20 08:00:00'),
(9, 9, '2024-09-01 08:00:00', '2024-09-01 10:00:00'),
(10, 10, '2024-09-10 09:00:00', '2024-09-10 11:00:00'),
(11, 1, '2024-07-05 08:00:00', '2024-07-05 10:00:00'),
(12, 2, '2024-08-01 09:00:00', '2024-08-01 11:00:00'),
(13, 3, '2024-08-15 07:00:00', '2024-08-15 09:00:00'),
(14, 4, '2024-09-05 06:00:00', '2024-09-05 08:00:00'),
(15, 5, '2024-09-15 08:00:00', '2024-09-15 10:00:00'),
(16, 6, '2024-10-01 09:00:00', '2024-10-01 11:00:00'),
(17, 7, '2024-10-10 07:00:00', '2024-10-10 09:00:00'),
(18, 8, '2024-10-20 06:00:00', '2024-10-20 08:00:00'),
(19, 9, '2024-11-01 08:00:00', '2024-11-01 10:00:00'),
(20, 10, '2024-11-10 09:00:00', '2024-11-10 11:00:00'),
(21, 1, '2024-11-20 08:00:00', '2024-11-20 10:00:00'),
(22, 2, '2024-12-01 09:00:00', '2024-12-01 11:00:00'),
(23, 3, '2024-12-10 07:00:00', '2024-12-10 09:00:00'),
(24, 4, '2024-12-20 06:00:00', '2024-12-20 08:00:00'),
(25, 5, '2025-01-01 08:00:00', '2025-01-01 10:00:00');
INSERT INTO Reviews (review_id, tour_id, tourist_id, rating, comment, review_date) VALUES
(1, 1, 1, 5, 'Amazing experience!', '2024-07-07'),
(2, 2, 2, 4, 'Great tour, but a bit too long.', '2024-07-18'),
(3, 3, 3, 5, 'Loved the history and culture.', '2024-07-10'),
(4, 4, 4, 3, 'Good tour, but the guide was not very friendly.', '2024-07-26'),
(5, 5, 5, 4, 'Enjoyed the sights and the guide was knowledgeable.', '2024-07-21'),
(6, 6, 6, 5, 'Fantastic tour with lots of fun activities.', '2024-08-09'),
(7, 7, 7, 4, 'Good tour but a bit rushed.', '2024-08-15'),
(8, 8, 8, 5, 'Wonderful experience, highly recommend!', '2024-08-26'),
(9, 9, 9, 3, 'It was okay, but not great.', '2024-09-07'),
(10, 10, 10, 5, 'Loved every moment of it!', '2024-09-18'),
(11, 11, 11, 5, 'Great tour, very well organized.', '2024-07-11'),
(12, 12, 12, 4, 'Nice tour, but too crowded.', '2024-08-09'),
(13, 13, 13, 3, 'Good experience, but could be better.', '2024-08-21'),
(14, 14, 14, 5, 'Amazing tour, loved it!', '2024-09-11'),
(15, 15, 15, 4, 'Enjoyed the tour, but the accommodation was not great.', '2024-09-21'),
(16, 16, 16, 5, 'Fantastic tour, highly recommend.', '2024-10-09'),
(17, 17, 17, 4, 'Good tour, but the guide was not very informative.', '2024-10-16'),
(18, 18, 18, 3, 'Okay experience, but not great.', '2024-10-26'),
(19, 19, 19, 5, 'Loved the tour, especially the activities.', '2024-11-07'),
(20, 20, 20, 4, 'Nice tour, but too expensive.', '2024-11-18'),
(21, 21, 21, 5, 'Great experience, highly recommend.', '2024-11-26'),
(22, 22, 22, 4, 'Good tour, but the transport was not comfortable.', '2024-12-09'),
(23, 23, 23, 3, 'Average tour, could be improved.', '2024-12-16'),
(24, 24, 24, 5, 'Loved every part of the tour.', '2024-12-26'),
(25, 25, 25, 4, 'Nice tour, but the guide was not friendly.', '2025-01-07');
INSERT INTO TourItinerary (itinerary_id, tour_id, day_number, activity_description, location) VALUES
(1, 1, 1, 'Visit the Eiffel Tower', 'Paris, France'),
(2, 2, 1, 'Explore Sensoji Temple', 'Tokyo, Japan'),
(3, 3, 1, 'Tour the Pyramids of Giza', 'Cairo, Egypt'),
(4, 4, 1, 'Visit Central Park', 'New York, USA'),
(5, 5, 1, 'Tour the Sydney Opera House', 'Sydney, Australia'),
(6, 6, 1, 'Explore Copacabana Beach', 'Rio de Janeiro, Brazil'),
(7, 7, 1, 'Visit the Colosseum', 'Rome, Italy'),
(8, 8, 1, 'Hike up Table Mountain', 'Cape Town, South Africa'),
(9, 9, 1, 'Tour the Grand Palace', 'Bangkok, Thailand'),
(10, 10, 1, 'Visit the British Museum', 'London, UK'),
(11, 11, 1, 'Visit the Louvre Museum', 'Paris, France'),
(12, 12, 1, 'Explore Meiji Shrine', 'Tokyo, Japan'),
(13, 13, 1, 'Tour the Sphinx', 'Cairo, Egypt'),
(14, 14, 1, 'Visit Times Square', 'New York, USA'),
(15, 15, 1, 'Tour the Harbour Bridge', 'Sydney, Australia'),
(16, 16, 1, 'Explore Ipanema Beach', 'Rio de Janeiro, Brazil'),
(17, 17, 1, 'Visit the Pantheon', 'Rome, Italy'),
(18, 18, 1, 'Hike up Signal Hill', 'Cape Town, South Africa'),
(19, 19, 1, 'Tour Wat Arun', 'Bangkok, Thailand'),
(20, 20, 1, 'Visit the Tower of London', 'London, UK'),
(21, 21, 1, 'Walk along the Seine River', 'Paris, France'),
(22, 22, 1, 'Explore Asakusa District', 'Tokyo, Japan'),
(23, 23, 1, 'Tour the Egyptian Museum', 'Cairo, Egypt'),
(24, 24, 1, 'Visit the Statue of Liberty', 'New York, USA'),
(25, 25, 1, 'Tour Bondi Beach', 'Sydney, Australia');
INSERT INTO TourActivities (activity_id, name, description, type, duration) VALUES
(1, 'Sightseeing', 'Visit famous landmarks and attractions.', 'Tour', 2),
(2, 'Cultural Experience', 'Engage in local cultural activities.', 'Cultural', 3),
(3, 'Historical Tour', 'Learn about the history of the area.', 'Educational', 4),
(4, 'Adventure Activity', 'Participate in adventurous activities.', 'Adventure', 5),
(5, 'Relaxation', 'Enjoy some leisure time and relaxation.', 'Leisure', 2),
(6, 'Food Tasting', 'Taste local cuisine and delicacies.', 'Food', 3),
(7, 'Shopping', 'Explore local markets and shops.', 'Leisure', 2),
(8, 'Nature Walk', 'Walk through natural landscapes.', 'Nature', 4),
(9, 'Water Sports', 'Participate in water-based activities.', 'Adventure', 5),
(10, 'Nightlife', 'Experience the local nightlife.', 'Entertainment', 3),
(11, 'Wine Tasting', 'Taste local wines.', 'Food', 2),
(12, 'Museum Tour', 'Visit famous museums.', 'Educational', 3),
(13, 'Mountain Climbing', 'Climb mountains and enjoy the view.', 'Adventure', 5),
(14, 'City Walk', 'Walk through the city and explore.', 'Tour', 4),
(15, 'Beach Party', 'Enjoy a party on the beach.', 'Entertainment', 3),
(16, 'Cultural Show', 'Watch local cultural performances.', 'Cultural', 2),
(17, 'Photography Tour', 'Take photos of scenic spots.', 'Leisure', 3),
(18, 'Wildlife Safari', 'See wildlife in their natural habitat.', 'Adventure', 5),
(19, 'Kayaking', 'Kayak through local waters.', 'Adventure', 3),
(20, 'Shopping Spree', 'Shop at local markets.', 'Leisure', 4),
(21, 'Cooking Class', 'Learn to cook local dishes.', 'Food', 3),
(22, 'Yoga Session', 'Participate in a yoga session.', 'Leisure', 2),
(23, 'Concert', 'Attend a live concert.', 'Entertainment', 3),
(24, 'Scuba Diving', 'Dive and explore underwater.', 'Adventure', 5),
(25, 'Art Workshop', 'Create art with local artists.', 'Educational', 4),
(26, 'Art Workshop', 'Create art with local artists.', 'Educational', 4);
INSERT INTO ItineraryActivities (itinerary_id, activity_id, start_time, end_time) VALUES
(1, 1, '10:00:00', '12:00:00'),
(2, 2, '14:00:00', '17:00:00'),
(3, 3, '09:00:00', '13:00:00'),
(4, 4, '15:00:00', '20:00:00'),
(5, 5, '11:00:00', '13:00:00'),
(6, 6, '18:00:00', '21:00:00'),
(7, 7, '10:00:00', '12:00:00'),
(8, 8, '08:00:00', '12:00:00'),
(9, 9, '13:00:00', '18:00:00'),
(10, 10, '19:00:00', '22:00:00'),
(11, 11, '10:00:00', '12:00:00'),
(12, 12, '14:00:00', '17:00:00'),
(13, 13, '09:00:00', '13:00:00'),
(14, 14, '15:00:00', '20:00:00'),
(15, 15, '11:00:00', '13:00:00'),
(16, 16, '18:00:00', '21:00:00'),
(17, 17, '10:00:00', '12:00:00'),
(18, 18, '08:00:00', '12:00:00'),
(19, 19, '13:00:00', '18:00:00'),
(20, 20, '19:00:00', '22:00:00'),
(21, 21, '10:00:00', '13:00:00'),
(22, 22, '14:00:00', '16:00:00'),
(23, 23, '09:00:00', '12:00:00'),
(24, 24, '15:00:00', '20:00:00'),
(25, 25, '11:00:00', '15:00:00');
INSERT INTO SpecialOffers (offer_id, description, discount_percentage, valid_from, valid_to, package_id) VALUES
(1, 'Summer Special: 10% off', 10.0, '2024-06-01', '2024-08-31', 1),
(2, 'Early Bird Discount: 15% off', 15.0, '2024-01-01', '2024-03-31', 2),
(3, 'Holiday Deal: 20% off', 20.0, '2024-12-01', '2025-01-31', 3),
(4, 'Spring Sale: 5% off', 5.0, '2024-04-01', '2024-05-31', 4),
(5, 'Fall Discount: 12% off', 12.0, '2024-09-01', '2024-11-30', 5),
(6, 'Exclusive Offer: 18% off', 18.0, '2024-07-01', '2024-07-31', 6),
(7, 'Flash Sale: 25% off', 25.0, '2024-10-01', '2024-10-31', 7),
(8, 'Winter Special: 8% off', 8.0, '2024-11-01', '2024-12-31', 8),
(9, 'Limited Time Offer: 20% off', 20.0, '2024-06-15', '2024-07-15', 9),
(10, 'Exclusive Deal: 22% off', 22.0, '2024-08-01', '2024-08-31', 10),
(11, 'Mega Sale: 30% off', 30.0, '2024-06-01', '2024-06-30', 1),
(12, 'Summer Special: 20% off', 20.0, '2024-07-01', '2024-07-31', 2),
(13, 'Autumn Offer: 25% off', 25.0, '2024-08-01', '2024-08-31', 3),
(14, 'Early Bird: 15% off', 15.0, '2024-09-01', '2024-09-30', 4),
(15, 'Holiday Special: 10% off', 10.0, '2024-10-01', '2024-10-31', 5),
(16, 'Festive Discount: 20% off', 20.0, '2024-11-01', '2024-11-30', 6),
(17, 'Winter Sale: 30% off', 30.0, '2024-12-01', '2024-12-31', 7),
(18, 'New Year Special: 25% off', 25.0, '2025-01-01', '2025-01-31', 8),
(19, 'Spring Offer: 15% off', 15.0, '2025-02-01', '2025-02-28', 9),
(20, 'Weekend Deal: 20% off', 20.0, '2025-03-01', '2025-03-31', 10),
(21, 'Flash Sale: 25% off', 25.0, '2024-06-15', '2024-07-15', 1),
(22, 'Seasonal Discount: 15% off', 15.0, '2024-08-01', '2024-09-01', 2),
(23, 'Limited Time Offer: 20% off', 20.0, '2024-10-15', '2024-11-15', 3),
(24, 'Exclusive Offer: 22% off', 22.0, '2024-11-15', '2024-12-15', 4),
(25, 'Special Deal: 18% off', 18.0, '2025-01-15', '2025-02-15', 5);
-- WITH DiscountedBookings AS (
-- SELECT
-- b.booking_id,
-- b.tour_id,
-- b.booking_date,
-- t.package_id,
-- tp.price,
-- COALESCE(so.discount_percentage, 0) AS discount
-- FROM
-- Bookings b
-- JOIN
-- Tours t ON b.tour_id = t.tour_id
-- JOIN
-- TourPackages tp ON t.package_id = tp.package_id
-- LEFT JOIN
-- SpecialOffers so ON tp.package_id = so.package_id
-- AND b.booking_date BETWEEN so.valid_from AND so.valid_to
-- WHERE
-- b.status = 'Confirmed'
-- ),
-- FinalProfit AS (
-- SELECT
-- package_id,
-- SUM(price * (1 - discount / 100)) AS TotalProfit
-- FROM
-- DiscountedBookings
-- GROUP BY
-- package_id
-- )
-- SELECT
-- tp.name AS PackageName,
-- fp.TotalProfit
-- FROM
-- FinalProfit fp
-- JOIN
-- TourPackages tp ON fp.package_id = tp.package_id
-- ORDER BY
-- fp.TotalProfit DESC;
-- WITH TouristBookings AS (
-- SELECT
-- tr.tourist_id,
-- tr.name AS TouristName,
-- COUNT(b.booking_id) AS NumberOfBookings,
-- GROUP_CONCAT(DISTINCT d.name) AS VisitedDestinations
-- FROM
-- Bookings b
-- JOIN
-- Tourists tr ON b.tourist_id = tr.tourist_id
-- JOIN
-- Tours ts ON b.tour_id = ts.tour_id
-- JOIN
-- TourPackages tp ON ts.package_id = tp.package_id
-- JOIN
-- Destinations d ON tp.destination_id = d.destination_id
-- WHERE
-- b.status = 'Confirmed'
-- GROUP BY
-- tr.tourist_id, tr.name
-- )
-- SELECT
-- TouristName,
-- NumberOfBookings,
-- VisitedDestinations
-- FROM
-- TouristBookings
-- ORDER BY
-- NumberOfBookings DESC;
-- WITH AccommodationUsage AS (
-- SELECT
-- a.accommodation_id,
-- a.name AS AccommodationName,
-- a.type AS AccommodationType,
-- COUNT(ta.tour_id) AS TotalUses
-- FROM
-- TourAccommodations ta
-- JOIN
-- Accommodations a ON ta.accommodation_id = a.accommodation_id
-- GROUP BY
-- a.accommodation_id, a.name, a.type
-- )
-- SELECT
-- AccommodationName,
-- AccommodationType,
-- TotalUses
-- FROM
-- AccommodationUsage
-- ORDER BY
-- TotalUses DESC
-- LIMIT 1;
-- WITH TransportUsage AS (
-- SELECT
-- t.type AS TransportType,
-- t.company AS Company,
-- COUNT(tt.tour_id) AS TotalUses
-- FROM
-- TourTransports tt
-- JOIN
-- Transport t ON tt.transport_id = t.transport_id
-- GROUP BY
-- t.type, t.company
-- )
-- SELECT
-- TransportType,
-- Company,
-- TotalUses
-- FROM
-- TransportUsage
-- ORDER BY
-- TotalUses DESC
-- LIMIT 1;
-- SELECT
-- d.name AS destination_name,
-- d.country,
-- COUNT(b.booking_id) AS total_bookings,
-- SUM(tp.price) AS total_revenue
-- FROM
-- Bookings b
-- JOIN
-- Tours t ON b.tour_id = t.tour_id
-- JOIN
-- TourPackages tp ON t.package_id = tp.package_id
-- JOIN
-- Destinations d ON tp.destination_id = d.destination_id
-- WHERE
-- b.status = 'Confirmed'
-- GROUP BY
-- d.name, d.country
-- ORDER BY
-- total_bookings DESC, total_revenue DESC
-- LIMIT 1;
-- SELECT
-- g.name AS guide_name,
-- COUNT(t.tour_id) AS total_tours,
-- SUM(t.max_tourists) AS total_tourists
-- FROM
-- Guides g
-- JOIN
-- Tours t ON g.guide_id = t.guide_id
-- GROUP BY
-- g.guide_id, g.name
-- ORDER BY
-- total_tours DESC, total_tourists DESC
-- LIMIT 1;
-- SELECT
-- tp.name AS package_name,
-- d.name AS destination_name,
-- AVG(r.rating) AS average_rating
-- FROM
-- Reviews r
-- JOIN
-- Tours t ON r.tour_id = t.tour_id
-- JOIN
-- TourPackages tp ON t.package_id = tp.package_id
-- JOIN
-- Destinations d ON tp.destination_id = d.destination_id
-- GROUP BY
-- tp.package_id, tp.name, d.name
-- ORDER BY
-- average_rating DESC
-- LIMIT 1;
-- SELECT
-- t.name AS tourist_name,
-- t.nationality,
-- SUM(tp.price) AS total_spent
-- FROM
-- Tourists t
-- JOIN
-- Bookings b ON t.tourist_id = b.tourist_id
-- JOIN
-- Tours tour ON b.tour_id = tour.tour_id
-- JOIN
-- TourPackages tp ON tour.package_id = tp.package_id
-- WHERE
-- b.status = 'Confirmed'
-- GROUP BY
-- t.tourist_id, t.name, t.nationality
-- ORDER BY
-- total_spent DESC
-- LIMIT 10;
-- WITH AverageRatings AS (
-- SELECT
-- tp.package_id,
-- tp.name AS package_name,
-- AVG(r.rating) AS average_rating
-- FROM
-- TourPackages tp
-- JOIN
-- Tours t ON tp.package_id = t.package_id
-- JOIN
-- Bookings b ON t.tour_id = b.tour_id
-- JOIN
-- Reviews r ON b.tour_id = r.tour_id AND b.tourist_id = r.tourist_id
-- GROUP BY
-- tp.package_id, tp.name
-- ),
-- MostCommonComments AS (
-- SELECT
-- tp.package_id,
-- tp.name AS package_name,
-- r.comment,
-- COUNT(r.comment) AS comment_count,
-- ROW_NUMBER() OVER (PARTITION BY tp.package_id ORDER BY COUNT(r.comment) DESC) AS rn
-- FROM
-- TourPackages tp
-- JOIN
-- Tours t ON tp.package_id = t.package_id
-- JOIN
-- Bookings b ON t.tour_id = b.tour_id
-- JOIN
-- Reviews r ON b.tour_id = r.tour_id AND b.tourist_id = r.tourist_id
-- GROUP BY
-- tp.package_id, tp.name, r.comment
-- )
-- SELECT
-- ar.package_name,
-- ar.average_rating,
-- mcc.comment AS most_common_comment
-- FROM
-- AverageRatings ar
-- JOIN
-- MostCommonComments mcc ON ar.package_id = mcc.package_id
-- WHERE
-- mcc.rn = 1;
-- WITH SeasonalBookings AS (
-- SELECT
-- booking_id,
-- CASE
-- WHEN strftime('%m', booking_date) IN ('12', '01', '02') THEN 'Winter'
-- WHEN strftime('%m', booking_date) IN ('03', '04', '05') THEN 'Spring'
-- WHEN strftime('%m', booking_date) IN ('06', '07', '08') THEN 'Summer'
-- WHEN strftime('%m', booking_date) IN ('09', '10', '11') THEN 'Autumn'
-- END AS season
-- FROM
-- Bookings
-- )
-- SELECT
-- season,
-- COUNT(*) AS booking_count
-- FROM
-- SeasonalBookings
-- GROUP BY
-- season;
WITH TouristAccommodationStays AS (
SELECT
t.tourist_id,
t.name AS tourist_name,
ta.accommodation_id,
a.name AS accommodation_name,
(julianday(ta.end_date) - julianday(ta.start_date)) AS stay_duration
FROM
Tourists t
JOIN
Bookings b ON t.tourist_id = b.tourist_id
JOIN
Tours tour ON b.tour_id = tour.tour_id
JOIN
TourAccommodations ta ON tour.tour_id = ta.tour_id
JOIN
Accommodations a ON ta.accommodation_id = a.accommodation_id
WHERE
b.status = 'Confirmed'
),
TotalStayDurations AS (
SELECT
tourist_id,
tourist_name,
SUM(stay_duration) AS total_stay_duration,
GROUP_CONCAT(accommodation_name) AS accommodation_names
FROM
TouristAccommodationStays
GROUP BY
tourist_id, tourist_name
)
SELECT
tourist_name,
total_stay_duration,
accommodation_names
FROM
TotalStayDurations
ORDER BY
total_stay_duration DESC
LIMIT 1;
To embed this program on your website, copy the following code and paste it into your website's HTML: