· 3 years ago · Jun 18, 2022, 08:40 PM
1CREATE DATABASE Airport
2
3GO
4
5USE Airport
6
7--Problem01
8CREATE TABLE Passengers(
9 Id INT PRIMARY KEY IDENTITY,
10 FullName VARCHAR(100) UNIQUE NOT NULL,
11 Email VARCHAR(50) UNIQUE NOT NULL
12)
13
14CREATE TABLE Pilots(
15 Id INT PRIMARY KEY IDENTITY,
16 FirstName VARCHAR(30) UNIQUE NOT NULL,
17 LastName VARCHAR(30) UNIQUE NOT NULL,
18 Age TINYINT CHECK(Age BETWEEN 21 AND 62) NOT NULL,
19 Rating FLOAT CHECK(Rating BETWEEN 0.0 AND 10.0),
20)
21
22CREATE TABLE AircraftTypes(
23 Id INT PRIMARY KEY IDENTITY,
24 TypeName VARCHAR(30) UNIQUE NOT NULL
25)
26
27CREATE TABLE Aircraft(
28 Id INT PRIMARY KEY IDENTITY,
29 Manufacturer VARCHAR(25) NOT NULL,
30 Model VARCHAR(30) NOT NULL,
31 [Year] INT NOT NULL,
32 FlightHours INT,
33 Condition CHAR(1) NOT NULL,
34 TypeId INT FOREIGN KEY REFERENCES AircraftTypes(Id) NOT NULL
35)
36
37CREATE TABLE PilotsAircraft(
38 AircraftId INT FOREIGN KEY REFERENCES Aircraft(Id) NOT NULL,
39 PilotId INT FOREIGN KEY REFERENCES Pilots(Id) NOT NULL,
40 PRIMARY KEY(AircraftId, PilotId)
41)
42
43CREATE TABLE Airports(
44 Id INT PRIMARY KEY IDENTITY,
45 AirportName VARCHAR(70) UNIQUE NOT NULL,
46 Country VARCHAR(100) UNIQUE NOT NULL
47)
48
49CREATE TABLE FlightDestinations (
50 Id INT PRIMARY KEY IDENTITY,
51 AirportId INT FOREIGN KEY REFERENCES Airports(Id) NOT NULL,
52 [Start] DATETIME NOT NULL,
53 AircraftId INT FOREIGN KEY REFERENCES Aircraft(Id) NOT NULL,
54 PassengerId INT FOREIGN KEY REFERENCES Passengers(Id) NOT NULL,
55 TicketPrice DECIMAL(18,2) DEFAULT 15 NOT NULL
56)
57
58--Problem02
59INSERT INTO Passengers(FullName, Email)
60(
61 SELECT ([FirstName] + ' ' + [LastName]) AS [FullName],
62 ([FirstName] + [LastName] + '@gmail.com') AS [Email]
63 FROM [Pilots]
64 WHERE [Id] BETWEEN 5 AND 15
65)
66
67--Problem03
68UPDATE Aircraft
69 SET Condition = 'A'
70 WHERE [Condition] IN ('C', 'B') AND
71 ([FlightHours] IS NULL OR [FlightHours] BETWEEN 0 AND 100) AND
72 [Year] >= 2013
73
74--Problem04
75DELETE FROM Passengers
76 WHERE LEN([FullName]) <= 10
77
78--Problem05
79SELECT Manufacturer,
80 Model,
81 FlightHours,
82 Condition
83 FROM Aircraft
84ORDER BY FlightHours DESC
85
86--Problem06
87SELECT p.FirstName,
88 p.LastName,
89 a.Manufacturer,
90 a.Model,
91 a.FlightHours
92 FROM PilotsAircraft AS pa
93LEFT JOIN Pilots AS p ON pa.PilotId = p.Id
94LEFT JOIN Aircraft AS a ON pa.AircraftId = a.Id
95 WHERE a.FlightHours <= 304
96ORDER BY a.FlightHours DESC, p.FirstName
97
98--Problem07
99SELECT TOP(20)
100 fd.Id AS DestinationId,
101 fd.[Start],
102 p.FullName,
103 a.AirportName,
104 fd.TicketPrice
105 FROM FlightDestinations AS fd
106 JOIN Passengers AS p ON fd.PassengerId = p.Id
107 JOIN Airports AS a ON fd.AirportId = a.Id
108 WHERE DATEPART(DAY, fd.[Start]) % 2 = 0
109 ORDER BY fd.TicketPrice DESC, a.AirportName
110
111 --Problem08
112 SELECT fd.AircraftId,
113 a.Manufacturer,
114 a.FlightHours,
115 COUNT(*) AS FlightDestinationsCount,
116 ROUND(AVG(fd.TicketPrice), 2) AS AvgPrice
117 FROM FlightDestinations AS fd
118 JOIN Aircraft AS a ON fd.AircraftId = a.Id
119GROUP BY AircraftId, a.Manufacturer, a.FlightHours
120 HAVING COUNT(*) >= 2
121ORDER BY FlightDestinationsCount DESC, fd.AircraftId
122
123--Problem09
124SELECT p.FullName,
125 COUNT(p.FullName) AS CountOfAircaft,
126 SUM(fd.TicketPrice) AS TotalPayed
127 FROM [Passengers] AS p
128 JOIN FlightDestinations as fd ON fd.PassengerId = p.Id
129 GROUP BY p.FullName
130 HAVING COUNT(p.FullName) > 1 AND p.FullName LIKE '_a%'
131 ORDER BY p.FullName
132
133--Problem10
134 SELECT a.AirportName,
135 fd.[Start] AS DayTime,
136 fd.TicketPrice,
137 p.FullName,
138 ac.Manufacturer,
139 ac.Model
140 FROM FlightDestinations AS fd
141 JOIN Airports AS a ON fd.AirportId = a.Id
142 JOIN Passengers AS p ON fd.PassengerId = p.Id
143 JOIN Aircraft AS ac ON fd.AircraftId = ac.Id
144WHERE (DATEPART(HOUR, fd.[Start]) BETWEEN 6 AND 20) AND
145 [TicketPrice] > 2500
146ORDER BY ac.Model
147
148GO
149--Problem11
150CREATE FUNCTION udf_FlightDestinationsByEmail(@email VARCHAR(50))
151RETURNS INT
152AS
153BEGIN
154 DECLARE @Count INT = (SELECT COUNT(*)
155 FROM FlightDestinations AS fd
156 JOIN Passengers AS p ON fd.PassengerId = p.Id
157 WHERE p.Email = @email)
158 RETURN @Count
159END
160
161GO
162
163--TestFunction Problem 11
164SELECT dbo.udf_FlightDestinationsByEmail ('PierretteDunmuir@gmail.com')
165
166SELECT dbo.udf_FlightDestinationsByEmail('Montacute@gmail.com')
167
168SELECT dbo.udf_FlightDestinationsByEmail('MerisShale@gmail.com')
169
170GO
171--Problem12
172CREATE PROCEDURE usp_SearchByAirportName @airportName VARCHAR(70)
173AS
174BEGIN
175 SELECT a.AirportName,
176 p.FullName,
177 CASE
178 WHEN fd.TicketPrice <= 400 THEN 'Low'
179 WHEN fd.TicketPrice BETWEEN 401 AND 1500 THEN 'Medium'
180 WHEN fd.TicketPrice >= 1501 THEN 'High'
181 END AS LevelOfTickerPrice,
182 ac.Manufacturer,
183 ac.Condition,
184 act.TypeName
185 FROM Airports AS a
186 JOIN FlightDestinations AS fd ON a.Id = fd.AirportId
187 JOIN Passengers AS p ON fd.PassengerId = p.Id
188 JOIN Aircraft AS ac ON fd.AircraftId = ac.Id
189 JOIN AircraftTypes AS act ON ac.TypeId = act.Id
190 WHERE a.AirportName = @airportName
191 ORDER BY ac.Manufacturer, p.FullName
192END
193
194GO
195
196--TestProcedure Problem12
197
198EXEC usp_SearchByAirportName 'Sir Seretse Khama International Airport'
199
200--TheEnd