Stan0033

Databases MSSQL Server Retake Exam - 10 Dec 2021

Jul 3rd, 2022 (edited)
871
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 6.43 KB | None
  1. ------------------------------------------------------------
  2. -- 01. DDL
  3. ------------------------------------------------------------
  4. CREATE 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.  (
  54. SELECT
  55.  [FirstName] + ' ' + [LastName] AS [FullName],
  56.   [FirstName] + [LastName]  + '@gmail.com' AS [Email]
  57.    FROM [Pilots] as [p]
  58. WHERE [p].[Id] >= 5 and [p].[Id] <= 15
  59. )
  60. ------------------------------------------------------------
  61. -- 03. Insert
  62. ------------------------------------------------------------
  63. UPDATE [Aircraft]
  64. SET [Condition] = 'A'
  65. WHERE ([Condition] = 'C' OR [Condition] = 'B')
  66. AND
  67. ([FlightHours] IS NULL OR [FlightHours] <= 100)
  68. AND ( [Year]>= 2013)
  69. ------------------------------------------------------------
  70. -- 04. Insert
  71. ------------------------------------------------------------
  72. DELETE FROM [Passengers]
  73. WHERE
  74. LEN([FullName]) <= 10
  75. ------------------------------------------------------------
  76. -- 05. Aircraft  
  77. ------------------------------------------------------------
  78.  SELECT [Manufacturer], [Model], [FlightHours], [Condition]
  79. FROM [Aircraft]
  80. ORDER BY [FlightHours] DESC
  81. ------------------------------------------------------------
  82. -- 06. Aircraft  
  83. ------------------------------------------------------------
  84. SELECT
  85. [p].[FirstName],
  86. [p].[LastName],
  87. [a].[Manufacturer],
  88. [a].[Model],
  89. [a].[FlightHours]
  90. FROM [Pilots] AS [p]
  91. JOIN [PilotsAircraft] AS [pa] ON [p].[Id] = [pa].[PilotId]
  92. JOIN [Aircraft] AS [a] ON [pa].[AircraftId] = [a].[Id]
  93. WHERE [a].[FlightHours] IS NOT NULL AND [a].[FlightHours] <= 304
  94. ORDER BY [a].[FlightHours] DESC , [p].[FirstName]
  95. ------------------------------------------------------------
  96. -- 07. Top 20 Flight Destinations
  97. ------------------------------------------------------------
  98. SELECT
  99.   TOP(20)
  100.   [pa].[Id] AS [DestinationId],
  101.   [pa].[Start],
  102.   [p].[FullName],
  103.   [a].[AirportName],
  104.   [TicketPrice]
  105. FROM
  106.   [Passengers] as [p]
  107.   JOIN [FlightDestinations] AS [pa] ON [p].[Id] = [pa].[PassengerId]
  108.    JOIN [Airports] as [a] ON [a].Id = [pa].[AirportId]
  109. WHERE
  110.   DAY([Start]) % 2 = 0
  111. ORDER BY
  112.   [TicketPrice] DESC,
  113.   [AirportName]
  114. ------------------------------------------------------------
  115. -- 08. Number of Flights for Each Aircraft
  116. ------------------------------------------------------------
  117. SELECT
  118. [f].[AircraftId]
  119. , [a].[Manufacturer]
  120. , [FlightHours]
  121. , COUNT (*) AS [FlightDestinationsCount]
  122. ,ROUND(AVG([f].[TicketPrice]),2) AS [AvgPrice]
  123. FROM [Aircraft] AS [a]
  124. JOIN [FlightDestinations] [f] ON [F].[AircraftId]= [A].[Id]
  125. GROUP BY [AircraftId], [a].[Manufacturer], [a].[FlightHours]
  126. HAVING COUNT(*) >= 2
  127. ORDER BY COUNT(*) DESC, [AircraftId]
  128. ------------------------------------------------------------
  129. -- 09. Regular Passengers
  130. ------------------------------------------------------------
  131.  
  132.  
  133. ------------------------------------------------------------
  134. --10. Full Info for Flight Destinations
  135. ------------------------------------------------------------
  136.  
  137. SELECT
  138. [ar].[AirportName],
  139. [f].[Start],
  140. [f].[TicketPrice],
  141. [p].[FullName],
  142. [a].[Manufacturer],
  143. [a].[Model]
  144. FROM
  145. [FlightDestinations] AS [f]
  146. JOIN [Airports] AS [ar] ON [f].[AirportId] = [ar].[Id]
  147. JOIN [Passengers] AS [p]  ON [p].[Id] = [f].[PassengerId]
  148. JOIN [Aircraft] AS [a]  ON [a].[Id] = [f].[AircraftId]
  149.  
  150. WHERE
  151. (CONVERT(time,[f].[Start]) >= CONVERT(time, '6:00') AND CONVERT(time, [f].[Start]) <=CONVERT(time, '20:00'))
  152. AND
  153. [f].[TicketPrice] > 2500
  154.  ORDER BY [a].[Model]
  155.  
  156. ------------------------------------------------------------
  157. -- 11. Find all Destinations by Email Address
  158. ------------------------------------------------------------
  159. CREATE FUNCTION udf_FlightDestinationsByEmail(@email VARCHAR(30))
  160. RETURNS INT
  161. AS
  162. BEGIN
  163. RETURN(
  164.   SELECT COUNT(*) FROM [FlightDestinations] AS [f]
  165.  JOIN [Passengers] AS [p] ON [p].[Id] = [f].[PassengerId]
  166.   WHERE [p].[Email] = @email
  167. )
  168. END
  169. ------------------------------------------------------------
  170. -- 12. Full Info for Airports
  171. ------------------------------------------------------------
  172. CREATE PROCEDURE  usp_SearchByAirportName( @airportName VARCHAR(70))
  173. AS
  174. BEGIN
  175. SELECT
  176. [ar].[AirportName],
  177. [p].[FullName],
  178. CASE
  179. WHEN [f].[TicketPrice] < 400 THEN 'Low'
  180. WHEN [f].[TicketPrice] > 400 AND [f].[TicketPrice] <=1500 THEN 'Medium'
  181.  WHEN [f].[TicketPrice] > 1501 THEN 'High'
  182.  END
  183. AS
  184. [LevelOfTickerPrice],
  185. [a].[Manufacturer],
  186. [a].[Condition],
  187. [at].[TypeName]
  188. FROM
  189. [Airports]
  190. AS [ar]
  191. JOIN [FlightDestinations] aS [f] ON [f].[AirportId] = [ar].[Id]
  192. JOIN [Passengers] as [p] ON [f].[PassengerId] = [p].[Id]
  193. JOIN [Aircraft] as [a] ON [a].[Id] = [f].[AircraftId]
  194. JOIN [AircraftTypes] as [at] ON [at].[Id] = [a].[TypeId]
  195. WHERE  [ar].[AirportName] = @airportName
  196. ORDER BY [a].[Manufacturer], [p].[Fullname]
  197. END
RAW Paste Data Copied