· 3 years ago · Jul 03, 2022, 04:10 PM
1------------------------------------------------------------
2-- 01. DDL
3------------------------------------------------------------
4CREATE TABLE [Passengers] (
5 [Id] INT PRIMARY KEY IDENTITY,
6 [FullName] VARCHAR(100) UNIQUE NOT NULL,
7 [Email] VARCHAR(50) UNIQUE NOT NULL
8) CREATE TABLE [Pilots] (
9 [Id] INT PRIMARY KEY IDENTITY,
10 [FirstName] NVARCHAR(30) NOT NULL UNIQUE,
11 [LastName] NVARCHAR(30) NOT NULL UNIQUE,
12 [Age] TINYINT NOT NULL,
13 [Rating] FLOAT ,
14 CHECK (
15 [Age] >= 21
16 AND [Age] <= 62
17 ),
18 CHECK (
19 [Rating] >= 0.0
20 AND [Rating] <= 10.0
21 )
22) CREATE TABLE [AircraftTypes] (
23 [Id] INT PRIMARY KEY IDENTITY,
24 [TypeName] VARCHAR(30) NOT NULL UNIQUE
25) CREATE TABLE [Aircraft] (
26 [Id] INT PRIMARY KEY IDENTITY,
27 [Manufacturer] VARCHAR(25) NOT NULL,
28 [Model] VARCHAR(30) NOT NULL,
29 [Year] INT NOT NULL,
30 [FlightHours] INT,
31 [Condition] NCHAR(1) NOT NULL,
32 [TypeId] INT FOREIGN KEY REFERENCES [AircraftTypes]([Id]) NOT NULL
33) CREATE TABLE [PilotsAircraft] (
34 [AircraftId] INT FOREIGN KEY REFERENCES [Aircraft]([Id]) NOT NULL,
35 [PilotId] INT FOREIGN KEY REFERENCES [Pilots]([Id]) NOT NULL,
36 PRIMARY KEY ([AircraftId], [PilotId])
37 ) CREATE TABLE [Airports] (
38 [Id] INT PRIMARY KEY IDENTITY,
39 [AirportName] VARCHAR(70) NOT NULL UNIQUE,
40 [Country] VARCHAR(100) NOT NULL UNIQUE
41) CREATE TABLE [FlightDestinations] (
42 [Id] INT PRIMARY KEY IDENTITY,
43 [AirportId] INT FOREIGN KEY REFERENCES [Airports]([Id]) NOT NULL,
44 [Start] DATETIME2 NOT NULL,
45 [AircraftId] INT FOREIGN KEY REFERENCES [Aircraft]([Id]) NOT NULL,
46 [PassengerId] INT FOREIGN KEY REFERENCES [Passengers]([Id]) NOT NULL,
47 [TicketPrice] DECIMAL(18, 2) NOT NULL DEFAULT(15)
48)
49------------------------------------------------------------
50-- 02. Insert
51------------------------------------------------------------
52 Insert into [Passengers] ([FullName], [Email])
53 (
54SELECT
55 [FirstName] + ' ' + [LastName] AS [FullName],
56 [FirstName] + [LastName] + '@gmail.com' AS [Email]
57 FROM [Pilots] as [p]
58WHERE [p].[Id] >= 5 and [p].[Id] <= 15
59)
60------------------------------------------------------------
61-- 03. Insert
62------------------------------------------------------------
63UPDATE [Aircraft]
64SET [Condition] = 'A'
65WHERE ([Condition] = 'C' OR [Condition] = 'B')
66AND
67([FlightHours] IS NULL OR [FlightHours] <= 100)
68AND ( [Year]>= 2013)
69------------------------------------------------------------
70-- 04. Insert
71------------------------------------------------------------
72DELETE FROM [Passengers]
73WHERE
74LEN([FullName]) <= 10
75------------------------------------------------------------
76-- 05. Aircraft
77------------------------------------------------------------
78 SELECT [Manufacturer], [Model], [FlightHours], [Condition]
79FROM [Aircraft]
80ORDER BY [FlightHours] DESC
81------------------------------------------------------------
82-- 06. Aircraft
83------------------------------------------------------------
84SELECT
85[p].[FirstName],
86[p].[LastName],
87[a].[Manufacturer],
88[a].[Model],
89[a].[FlightHours]
90FROM [Pilots] AS [p]
91JOIN [PilotsAircraft] AS [pa] ON [p].[Id] = [pa].[PilotId]
92JOIN [Aircraft] AS [a] ON [pa].[AircraftId] = [a].[Id]
93WHERE [a].[FlightHours] IS NOT NULL AND [a].[FlightHours] <= 304
94ORDER BY [a].[FlightHours] DESC , [p].[FirstName]
95------------------------------------------------------------
96-- 07. Top 20 Flight Destinations
97------------------------------------------------------------
98SELECT
99 TOP(20)
100 [pa].[Id] AS [DestinationId],
101 [pa].[Start],
102 [p].[FullName],
103 [a].[AirportName],
104 [TicketPrice]
105FROM
106 [Passengers] as [p]
107 JOIN [FlightDestinations] AS [pa] ON [p].[Id] = [pa].[PassengerId]
108 JOIN [Airports] as [a] ON [a].Id = [pa].[AirportId]
109WHERE
110 DAY([Start]) % 2 = 0
111ORDER BY
112 [TicketPrice] DESC,
113 [AirportName]
114------------------------------------------------------------
115-- 08. Number of Flights for Each Aircraft
116------------------------------------------------------------
117SELECT
118[f].[AircraftId]
119, [a].[Manufacturer]
120, [FlightHours]
121, COUNT (*) AS [FlightDestinationsCount]
122,ROUND(AVG([f].[TicketPrice]),2) AS [AvgPrice]
123FROM [Aircraft] AS [a]
124JOIN [FlightDestinations] [f] ON [F].[AircraftId]= [A].[Id]
125GROUP BY [AircraftId], [a].[Manufacturer], [a].[FlightHours]
126HAVING COUNT(*) >= 2
127ORDER BY COUNT(*) DESC, [AircraftId]
128------------------------------------------------------------
129-- 09. Regular Passengers
130------------------------------------------------------------
131
132
133------------------------------------------------------------
134--10. Full Info for Flight Destinations
135------------------------------------------------------------
136
137SELECT
138[ar].[AirportName],
139[f].[Start],
140[f].[TicketPrice],
141[p].[FullName],
142[a].[Manufacturer],
143[a].[Model]
144FROM
145[FlightDestinations] AS [f]
146JOIN [Airports] AS [ar] ON [f].[AirportId] = [ar].[Id]
147JOIN [Passengers] AS [p] ON [p].[Id] = [f].[PassengerId]
148JOIN [Aircraft] AS [a] ON [a].[Id] = [f].[AircraftId]
149
150WHERE
151(CONVERT(time,[f].[Start]) >= CONVERT(time, '6:00') AND CONVERT(time, [f].[Start]) <=CONVERT(time, '20:00'))
152AND
153[f].[TicketPrice] > 2500
154 ORDER BY [a].[Model]
155
156------------------------------------------------------------
157-- 11. Find all Destinations by Email Address
158------------------------------------------------------------
159------------------------------------------------------------
160-- 12. Full Info for Airports
161------------------------------------------------------------