· 4 years ago · Oct 11, 2021, 06:50 PM
1-- 1. DDL
2
3CREATE TABLE Planes
4(
5 Id INT PRIMARY KEY IDENTITY,
6 [Name] VARCHAR(30) NOT NULL,
7 Seats INT NOT NULL,
8 [Range] INT NOT NULL
9);
10
11CREATE TABLE Flights
12(
13 Id INT PRIMARY KEY IDENTITY,
14 DepartureTime DATETIME,
15 ArrivalTime DATETIME,
16 Origin VARCHAR(50) NOT NULL,
17 Destination VARCHAR(50) NOT NULL,
18 PlaneId INT FOREIGN KEY REFERENCES Planes(Id) NOT NULL
19);
20
21CREATE TABLE Passengers
22(
23 Id INT PRIMARY KEY IDENTITY,
24 FirstName VARCHAR(30) NOT NULL,
25 LastName VARCHAR(30) NOT NULL,
26 Age INT NOT NULL,
27 [Address] VARCHAR(30) NOT NULL,
28 PassportId CHAR(11) NOT NULL
29);
30
31CREATE TABLE LuggageTypes
32(
33 Id INT PRIMARY KEY IDENTITY,
34 [Type] VARCHAR(30) NOT NULL
35);
36
37CREATE TABLE Luggages
38(
39 Id INT PRIMARY KEY IDENTITY,
40 LuggageTypeId INT FOREIGN KEY REFERENCES LuggageTypes(Id) NOT NULL,
41 PassengerId INT FOREIGN KEY REFERENCES Passengers(Id) NOT NULL
42);
43
44CREATE TABLE Tickets
45(
46 Id INT PRIMARY KEY IDENTITY,
47 PassengerId INT FOREIGN KEY REFERENCES Passengers(Id) NOT NULL,
48 FlightId INT FOREIGN KEY REFERENCES Flights(Id) NOT NULL,
49 LuggageId INT FOREIGN KEY REFERENCES Luggages(Id) NOT NULL,
50 Price DECIMAL(18,2) NOT NULL
51)
52
53-- 2. INSERT
54INSERT INTO Planes ([Name], Seats, [Range]) VALUES
55('Airbus 336', 112, 5132),
56('Airbus 330', 432, 5325),
57('Boeing 369', 231, 2355),
58('Stelt 297', 254, 2143),
59('Boeing 338', 165, 5111),
60('Airbus 558', 387, 1342),
61('Boeing 128', 345, 5541)
62
63
64INSERT INTO LuggageTypes ([Type]) VALUES
65('Crossbody Bag'),
66('School Backpack'),
67('Shoulder Bag')
68
69-- 3. UPDATE
70
71UPDATE Tickets SET Price = Price * 1.13
72WHERE FlightId IN (SELECT Id FROM Flights WHERE Destination = 'Carlsbad')
73
74-- 4. DELETE
75
76DELETE FROM Tickets WHERE FlightId IN (SELECT Id FROM Flights WHERE Destination = 'Ayn Halagim');
77DELETE FROM Flights WHERE Destination = 'Ayn Halagim';
78
79-- 5. The "Tr" Planes
80
81SELECT * FROM Planes
82WHERE [Name] LIKE '%tr%'
83ORDER BY Id, [Name], Seats, [Range]
84
85-- 6. Flight Profits
86
87SELECT FlightId, SUM(Price) AS TotalPrice FROM Tickets
88GROUP BY FlightId
89ORDER BY TotalPrice DESC, FlightId
90
91-- 7. Passenger Trips
92
93SELECT
94 CONCAT(ps.FirstName, ' ', ps.LastName) AS [Full Name],
95 f.Origin,
96 f.Destination
97FROM Tickets AS ts
98JOIN Passengers AS ps ON ts.PassengerId = ps.Id
99JOIN Flights AS f ON ts.FlightId = f.Id
100ORDER BY [Full Name], Origin, Destination
101
102-- 8. Non Adventures People
103
104SELECT
105 ps.FirstName,
106 ps.LastName,
107 ps.Age
108FROM Passengers AS ps
109LEFT JOIN Tickets AS ts ON ts.PassengerId = ps.Id
110WHERE ts.Id IS NULL
111ORDER BY ps.Age DESC, ps.FirstName, ps.LastName
112
113-- 9. Full Info
114
115SELECT
116 CONCAT(p.FirstName, ' ', p.LastName) AS [Full Name],
117 pl.[Name] AS [Plane Name],
118 CONCAT(f.Origin, ' - ', f.Destination) AS Trip,
119 lt.[Type] AS [Luggage Type]
120FROM Tickets AS t
121JOIN Passengers AS p ON t.PassengerId = p.Id
122JOIN Flights AS f ON t.FlightId = f.Id
123JOIN Luggages AS l ON t.LuggageId = l.Id
124JOIN LuggageTypes AS lt ON l.LuggageTypeId = lt.Id
125JOIN Planes AS pl ON f.PlaneId = pl.Id
126ORDER BY [Full Name], [Plane Name], Trip, [Luggage Type]
127
128-- 10. PSP
129
130SELECT
131 pl.[Name],
132 pl.Seats,
133 COUNT(t.Id) AS [Passengers Count]
134FROM Planes AS pl
135LEFT JOIN Flights AS f ON f.PlaneId = pl.Id
136LEFT JOIN Tickets AS t ON t.FlightId = f.Id
137GROUP BY pl.Id, pl.[Name], pl.Seats
138ORDER BY [Passengers Count] DESC, [Name], Seats
139
140-- 11. Vacation
141
142CREATE FUNCTION udf_CalculateTickets(@origin VARCHAR(50), @destination VARCHAR(50), @peopleCount INT)
143RETURNS VARCHAR(50) AS
144BEGIN
145 IF (@peopleCount <= 0) RETURN 'Invalid people count!'
146 IF (NOT EXISTS (SELECT 1 FROM Flights WHERE Origin = @origin AND Destination = @destination))
147 RETURN 'Invalid flight!'
148 RETURN CONCAT('Total price ',
149 (SELECT TOP(1) ts.Price FROM Tickets AS ts
150 JOIN Flights AS f ON ts.FlightId = f.Id
151 WHERE f.Origin = @origin AND f.Destination = @destination) * @peopleCount)
152END
153
154-- 12. Wrong Data
155
156CREATE PROCEDURE usp_CancelFlights
157AS
158UPDATE Flights SET
159DepartureTime = NULL, ArrivalTime = NULL
160WHERE ArrivalTime > DepartureTime