DEV Community

faxridd1n
faxridd1n

Posted on • Edited on

Munisa 

-- Таблица: Виза
CREATE TABLE Visa (
    VisaNumber INT PRIMARY KEY,
    Series VARCHAR(50),
    Number VARCHAR(50),
    IssueDate DATE,
    ValidUntil DATE,
    VisaTypeId INT,
    CountryId INT,
    Entries INT,
    Duration INT,
    ClientId INT
);

-- Таблица: Выдача виз
CREATE TABLE VisaIssue (
    IssueDate DATE,
    TariffNumber INT,
    VisaNumber INT REFERENCES Visa(VisaNumber),
    PackageCode INT,
    PaymentAmount DECIMAL(10, 2)
);

-- Таблица: Загранпаспорт
CREATE TABLE Passport (
    PassportNumber INT PRIMARY KEY,
    Series VARCHAR(50),
    Number VARCHAR(50),
    IssueDate DATE,
    ValidUntil DATE,
    LastName VARCHAR(100),
    FirstName VARCHAR(100),
    ClientId INT
);

-- Таблица: Выдача загранпаспортов
CREATE TABLE PassportIssue (
    IssueDate DATE,
    TariffNumber INT,
    PassportNumber INT REFERENCES Passport(PassportNumber),
    PackageCode INT,
    PaymentAmount DECIMAL(10, 2)
);

-- Таблица: Клиенты
CREATE TABLE Clients (
    ClientId INT PRIMARY KEY,
    LastName VARCHAR(100),
    FirstName VARCHAR(100),
    MiddleName VARCHAR(100),
    PhoneNumber VARCHAR(15),
    PassportDetails VARCHAR(255),
    RegistrationDate DATE,
    Notes TEXT
);

-- Таблица: Путёвки
CREATE TABLE Packages (
    PackageCode INT PRIMARY KEY,
    Name VARCHAR(255),
    Countries TEXT,
    Hotels TEXT,
    TransportIncluded BOOLEAN,
    MealPlanId INT,
    DepartureDate DATE,
    ReturnDate DATE,
    Price DECIMAL(10, 2),
    VisaDiscount DECIMAL(10, 2),
    PassportDiscount DECIMAL(10, 2)
);

-- Таблица: Продажа путёвок
CREATE TABLE PackageSales (
    SaleId INT PRIMARY KEY,
    ClientId INT REFERENCES Clients(ClientId),
    PackageCode INT REFERENCES Packages(PackageCode),
    ClientLevel INT,
    SaleDate DATE,
    PaymentAmount DECIMAL(10, 2)
);

-- Таблица: Маршруты
CREATE TABLE Routes (
    RouteId INT PRIMARY KEY,
    PackageCode INT REFERENCES Packages(PackageCode),
    Day INT,
    Time TIME,
    TransportId INT,
    CountryId INT,
    Location VARCHAR(255)
);

-- Таблица: Мероприятия
CREATE TABLE Activities (
    ActivityId INT PRIMARY KEY,
    PackageCode INT REFERENCES Packages(PackageCode),
    Day INT,
    Time TIME,
    ActivityName VARCHAR(255)
);

-- Таблица: Отели
CREATE TABLE Hotels (
    HotelId INT PRIMARY KEY,
    Name VARCHAR(255),
    CountryId INT,
    Region VARCHAR(255)
);

-- Таблица: Питание
CREATE TABLE MealPlans (
    MealPlanId INT PRIMARY KEY,
    Name VARCHAR(100)
);

-- Таблица: Скидки клиентам
CREATE TABLE ClientDiscounts (
    ClientLevel INT PRIMARY KEY,
    PackageDiscount DECIMAL(10, 2)
);

-- Таблица: Средства перемещения
CREATE TABLE Transport (
    TransportId INT PRIMARY KEY,
    Name VARCHAR(100)
);

-- Таблица: Страны
CREATE TABLE Countries (
    CountryId INT PRIMARY KEY,
    CountryName VARCHAR(100)
);

-- Таблица: Тарифы на визы
CREATE TABLE VisaTariffs (
    TariffNumber INT PRIMARY KEY,
    ValidityPeriod INT,
    VisaTypeId INT,
    CountryId INT,
    Price DECIMAL(10, 2)
);

-- Таблица: Тарифы на загранпаспорта
CREATE TABLE PassportTariffs (
    TariffNumber INT PRIMARY KEY,
    Price DECIMAL(10, 2)
);

-- Таблица: Типы виз
CREATE TABLE VisaTypes (
    VisaTypeId INT PRIMARY KEY,
    Name VARCHAR(100)
);
Enter fullscreen mode Exit fullscreen mode

Image description

Image description

Image description

-- Fill Visa table
INSERT INTO Visa VALUES
(1, 'A1', '123456', '2025-01-01', '2026-01-01', 1, 1, 1, 30, 1),
(2, 'A2', '123457', '2025-02-01', '2026-02-01', 2, 2, 2, 60, 2),
(3, 'A3', '123458', '2025-03-01', '2026-03-01', 3, 3, 1, 90, 3),
(4, 'A4', '123459', '2025-04-01', '2026-04-01', 1, 4, 2, 120, 4),
(5, 'A5', '123460', '2025-05-01', '2026-05-01', 2, 5, 1, 150, 5);

-- Fill VisaIssue table
INSERT INTO VisaIssue VALUES
('2025-01-10', 1, 1, 1, 100.0),
('2025-02-10', 2, 2, 2, 200.0),
('2025-03-10', 3, 3, 3, 300.0),
('2025-04-10', 4, 4, 4, 400.0),
('2025-05-10', 5, 5, 5, 500.0);

-- Fill Passport table
INSERT INTO Passport VALUES
(1, 'P1', '654321', '2025-01-01', '2035-01-01', 'Smith', 'John', 1),
(2, 'P2', '654322', '2025-02-01', '2035-02-01', 'Doe', 'Jane', 2),
(3, 'P3', '654323', '2025-03-01', '2035-03-01', 'Brown', 'Bob', 3),
(4, 'P4', '654324', '2025-04-01', '2035-04-01', 'Taylor', 'Alice', 4),
(5, 'P5', '654325', '2025-05-01', '2035-05-01', 'Lee', 'Chris', 5);

-- Fill PassportIssue table
INSERT INTO PassportIssue VALUES
('2025-01-10', 1, 1, 1, 150.0),
('2025-02-10', 2, 2, 2, 250.0),
('2025-03-10', 3, 3, 3, 350.0),
('2025-04-10', 4, 4, 4, 450.0),
('2025-05-10', 5, 5, 5, 550.0);

-- Fill Clients table
INSERT INTO Clients VALUES
(1, 'Smith', 'John', 'A.', '+123456789', 'P1:654321', '2025-01-01', 'VIP'),
(2, 'Doe', 'Jane', 'B.', '+987654321', 'P2:654322', '2025-02-01', 'Frequent traveler'),
(3, 'Brown', 'Bob', 'C.', '+112233445', 'P3:654323', '2025-03-01', 'First trip'),
(4, 'Taylor', 'Alice', 'D.', '+556677889', 'P4:654324', '2025-04-01', ''),
(5, 'Lee', 'Chris', 'E.', '+998877665', 'P5:654325', '2025-05-01', 'Family package');

-- Fill Packages table
INSERT INTO Packages VALUES
(1, 'Holiday in Europe', 'France, Italy', 'Hotel A, Hotel B', 1, 1, '2025-06-01', '2025-06-15', 2000.0, 100.0, 50.0),
(2, 'Asian Adventure', 'Japan, China', 'Hotel C, Hotel D', 1, 2, '2025-07-01', '2025-07-15', 2500.0, 200.0, 100.0),
(3, 'African Safari', 'Kenya, Tanzania', 'Hotel E, Hotel F', 1, 3, '2025-08-01', '2025-08-15', 3000.0, 300.0, 150.0),
(4, 'Caribbean Cruise', 'Bahamas, Cuba', 'Hotel G, Hotel H', 1, 4, '2025-09-01', '2025-09-15', 3500.0, 400.0, 200.0),
(5, 'South American Tour', 'Brazil, Argentina', 'Hotel I, Hotel J', 1, 5, '2025-10-01', '2025-10-15', 4000.0, 500.0, 250.0);

-- Fill PackageSales table
INSERT INTO PackageSales VALUES
(1, 1, 1, 1, '2025-01-15', 1900.0),
(2, 2, 2, 2, '2025-02-15', 2400.0),
(3, 3, 3, 3, '2025-03-15', 2900.0),
(4, 4, 4, 4, '2025-04-15', 3400.0),
(5, 5, 5, 5, '2025-05-15', 3900.0);

-- Fill Routes table
INSERT INTO Routes VALUES
(1, 1, 1, '09:00:00', 1, 1, 'Paris'),
(2, 1, 2, '10:00:00', 1, 1, 'Rome'),
(3, 2, 1, '11:00:00', 2, 2, 'Tokyo'),
(4, 2, 2, '12:00:00', 2, 2, 'Beijing'),
(5, 3, 1, '13:00:00', 3, 3, 'Nairobi');

-- Fill Activities table
INSERT INTO Activities VALUES
(1, 1, 1, '09:30:00', 'Eiffel Tower Visit'),
(2, 1, 2, '10:30:00', 'Colosseum Tour'),
(3, 2, 1, '11:30:00', 'Mount Fuji Hike'),
(4, 2, 2, '12:30:00', 'Great Wall Tour'),
(5, 3, 1, '13:30:00', 'Safari Adventure');

-- Fill Hotels table
INSERT INTO Hotels VALUES
(1, 'Hotel A', 1, 'Region A'),
(2, 'Hotel B', 1, 'Region B'),
(3, 'Hotel C', 2, 'Region C'),
(4, 'Hotel D', 2, 'Region D'),
(5, 'Hotel E', 3, 'Region E');

-- Fill MealPlans table
INSERT INTO MealPlans VALUES
(1, 'Breakfast Only'),
(2, 'Half Board'),
(3, 'Full Board'),
(4, 'All Inclusive'),
(5, 'No Meals');

-- Fill ClientDiscounts table
INSERT INTO ClientDiscounts VALUES
(1, 5.0),
(2, 10.0),
(3, 15.0),
(4, 20.0),
(5, 25.0);

-- Fill Transport table
INSERT INTO Transport VALUES
(1, 'Bus'),
(2, 'Train'),
(3, 'Flight'),
(4, 'Cruise'),
(5, 'Car');

-- Fill Countries table
INSERT INTO Countries VALUES
(1, 'France'),
(2, 'Japan'),
(3, 'Kenya'),
(4, 'Bahamas'),
(5, 'Brazil');

-- Fill VisaTariffs table
INSERT INTO VisaTariffs VALUES
(1, 30, 1, 1, 100.0),
(2, 60, 2, 2, 200.0),
(3, 90, 3, 3, 300.0),
(4, 120, 1, 4, 400.0),
(5, 150, 2, 5, 500.0);

-- Fill PassportTariffs table
INSERT INTO PassportTariffs VALUES
(1, 50.0),
(2, 100.0),
(3, 150.0),
(4, 200.0),
(5, 250.0);

-- Fill VisaTypes table
INSERT INTO VisaTypes VALUES
(1, 'Tourist'),
(2, 'Business'),
(3, 'Student'),
(4, 'Transit'),
(5, 'Work');
Enter fullscreen mode Exit fullscreen mode

Image description

Image description

Image description

Image description

Image description
1

SELECT Series, Number, VisaTypeId FROM Visa
Enter fullscreen mode Exit fullscreen mode

Image description
2

SELECT DISTINCT CountryId FROM Routes
Enter fullscreen mode Exit fullscreen mode

Image description
3

SELECT * FROM Hotels
WHERE CountryId = 2;
Enter fullscreen mode Exit fullscreen mode

Image description
4

SELECT * FROM VisaIssue
ORDER BY PaymentAmount DESC;
Enter fullscreen mode Exit fullscreen mode

Image description
5

SELECT * FROM VisaTariffs
WHERE ValidityPeriod > 60 
AND Price = 400
Enter fullscreen mode Exit fullscreen mode

Image description
6

SELECT * FROM Visa
WHERE Series = 'A5' AND (VisaTypeId = 1 OR VisaTypeId = 2);
Enter fullscreen mode Exit fullscreen mode

Image description
7

SELECT * FROM PassportTariffs
WHERE Price NOT IN (50,100,150);
Enter fullscreen mode Exit fullscreen mode

Image description
8

UPDATE PackageSales
SET SaleId = 666
WHERE ClientId = 1;
Enter fullscreen mode Exit fullscreen mode

Image description
9

DELETE FROM PackageSales WHERE SaleId = 666;
Enter fullscreen mode Exit fullscreen mode

Before
Image description
After
Image description
10

SELECT * 
FROM Packages
WHERE PackageCode > 1
LIMIT 2;
Enter fullscreen mode Exit fullscreen mode

Image description
11

SELECT MIN(Price)
FROM PassportTariffs;
Enter fullscreen mode Exit fullscreen mode

Image description
12

SELECT MAX(Price)
FROM PassportTariffs;
Enter fullscreen mode Exit fullscreen mode

Image description
13

SELECT COUNT(TariffNumber)
FROM PassportTariffs
WHERE Price IS 200;
Enter fullscreen mode Exit fullscreen mode

Image description
14

SELECT SUM(Price) AS [NewTable]
FROM VisaTariffs
WHERE ValidityPeriod > 90;
Enter fullscreen mode Exit fullscreen mode

Image description
15

SELECT AVG(Price) AS [NewTable]
FROM VisaTariffs
WHERE VisaTypeId = 1;
Enter fullscreen mode Exit fullscreen mode

Image description
16

SELECT * FROM Routes
WHERE Location LIKE 'P%';
Enter fullscreen mode Exit fullscreen mode

Image description
17

SELECT * FROM Transport
WHERE Name IN ('Car', 'Cruise');
Enter fullscreen mode Exit fullscreen mode

Image description
18

SELECT * FROM Transport
WHERE Name NOT IN ('Bus', 'Car');
Enter fullscreen mode Exit fullscreen mode

Image description
19

SELECT * FROM VisaIssue
WHERE PaymentAmount BETWEEN 200 AND 400;
Enter fullscreen mode Exit fullscreen mode

Image description
20

SELECT Hotels.HotelId, Hotels.Name, MealPlans.MealPlanId, MealPlans.Name
FROM Countries
INNER JOIN Hotels ON Hotels.HotelId = Hotels.HotelId
INNER JOIN MealPlans ON Hotels.CountryId = MealPlans.MealPlanId
Enter fullscreen mode Exit fullscreen mode

Image description
21

SELECT Hotels.HotelId, Hotels.Name, MealPlans.MealPlanId, MealPlans.Name
FROM MealPlans
LEFT JOIN Hotels ON Hotels.HotelId = MealPlans.MealPlanId;
Enter fullscreen mode Exit fullscreen mode

Image description
22

SELECT Hotels.HotelId, Hotels.Name, MealPlans.MealPlanId, MealPlans.Name
FROM MealPlans
RIGHT JOIN Hotels ON Hotels.HotelId = MealPlans.MealPlanId;
Enter fullscreen mode Exit fullscreen mode

23

SELECT C1.FirstName AS CustomerFirstName, 
       C1.LastName AS CustomerLastName, 
       C2.FirstName AS RelatedPersonFirstName, 
       C2.LastName AS RelatedPersonLastName, 
       C1.Notes AS CustomerNotes
FROM Clients C1
LEFT JOIN Clients C2 ON C1.ClientId = C2.ClientId

UNION

SELECT C1.FirstName AS CustomerFirstName, 
       C1.LastName AS CustomerLastName, 
       C2.FirstName AS RelatedPersonFirstName, 
       C2.LastName AS RelatedPersonLastName, 
       C1.Notes AS CustomerNotes
FROM Clients C2
LEFT JOIN Clients C1 ON C1.ClientId = C2.ClientId;
Enter fullscreen mode Exit fullscreen mode

Image description
24

SELECT COUNT(ClientId), FirstName
FROM Clients
GROUP BY FirstName;
Enter fullscreen mode Exit fullscreen mode

Image description
25

SELECT Clients.FirstName, Clients.LastName, COUNT(Countries.CountryId) AS NumberOfListings
FROM Clients
LEFT JOIN Countries ON Countries.CountryId = Clients.ClientID
GROUP BY Clients.ClientID;
Enter fullscreen mode Exit fullscreen mode

Image description
26

SELECT ClientDiscounts.PackageDiscount, COUNT(Clients.ClientId) AS NumberOfAgents
FROM Clients
LEFT JOIN ClientDiscounts ON ClientDiscounts.ClientLevel = Clients.ClientId
GROUP BY ClientDiscounts.PackageDiscount
HAVING COUNT(Clients.ClientId) > 0

UNION

SELECT ClientDiscounts.PackageDiscount, COUNT(Clients.ClientId) AS NumberOfAgents
FROM ClientDiscounts
LEFT JOIN Clients ON ClientDiscounts.ClientLevel = Clients.ClientId
GROUP BY ClientDiscounts.PackageDiscount
HAVING COUNT(Clients.ClientId) > 0;
Enter fullscreen mode Exit fullscreen mode

Image description

27

SELECT CountryName
FROM Countries
WHERE EXISTS (SELECT Name FROM Hotels WHERE Countries.CountryId = Hotels.HotelId);
Enter fullscreen mode Exit fullscreen mode

Image description

28

INSERT INTO NewWorkers (NewCustomerID, NewFirstName,NewLastNamee)
SELECT ClientId, FirstName,LastName FROM Clients;
Enter fullscreen mode Exit fullscreen mode

Image description

Top comments (0)