Advertisement
jacket410

Untitled

May 24th, 2022
776
144 days
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE Bus_Station;
  2. GO
  3.  
  4. /*Получить данные (время, цену на билет) о рейсах в город Томск из города
  5. Новосибирск.*/
  6. SELECT Price, Bus_route.Time_Destination, Bus_route.Time_Arrival_Point
  7. FROM Bus_service
  8.     JOIN Bus_route ON Bus_route.ID = Bus_service.FK_Bus_route_ID
  9. WHERE Destination_City_ID = (SELECT ID
  10.     FROM City
  11.     WHERE Name = 'Новосибирск') AND Arrival_City_ID = (SELECT ID
  12.     FROM City
  13.     WHERE Name = 'Томск')
  14.  
  15. --Получить суммарный доход какой-либо кассы.
  16. IF OBJECT_ID('SumProfit', 'F') is NOT NULL
  17.     DROP FUNCTION SumProfit;
  18. GO
  19.  
  20. CREATE FUNCTION SumProfit (@busServiceID int)
  21. RETURNS INT AS
  22. BEGIN
  23.     DECLARE @Price int = 0;
  24.     SELECT @Price = @Price + Bus_service.Price
  25.     FROM Buy_ticket JOIN
  26.         Bus_service ON Bus_service.ID = Buy_ticket.FK_Bus_service_ID
  27.     WHERE Buy_ticket.FK_Bus_office_id = @busServiceID
  28.     RETURN @Price
  29. END
  30. GO
  31.  
  32. SELECT dbo.SumProfit(1);
  33. GO
  34.  
  35. --Получить суммарные доходы касс в городе Томске.
  36. DECLARE @Price int = 0;
  37. SELECT @Price = @Price + Bus_service.Price
  38. FROM Buy_ticket JOIN
  39.     Bus_service ON Bus_service.ID = Buy_ticket.FK_Bus_service_ID
  40. WHERE Buy_ticket.FK_Bus_office_id = (SELECT ID
  41. FROM Bus_office
  42. WHERE FK_City_ID = (SELECT ID
  43. FROM City
  44. WHERE Name = 'Томск'))
  45. SELECT @Price AS Price
  46.  
  47. --Получить число рейсов, совершѐнных данным водителем за год.
  48. IF OBJECT_ID('CountTripDriver', 'P') is NOT NULL
  49.     DROP PROCEDURE CountTripDriver;
  50. GO
  51.  
  52. CREATE PROCEDURE CountTripDriver
  53.     @driver nvarchar(25)
  54. AS
  55. BEGIN
  56.     SELECT COUNT(*) AS Count_Services
  57.     FROM Bus_service
  58.         JOIN Driver ON Driver.ID = Bus_service.FK_Driver_ID
  59.     WHERE Driver.First_Name = @driver AND Bus_service.Departure_date > DATEADD(yy, -1, GETDATE())
  60. END
  61. GO
  62.  
  63. exec CountTripDriver 'Федоров';
  64.  
  65. --Назначить водителя на рейс.
  66. UPDATE Bus_service
  67.     SET FK_Driver_ID = 1 WHERE ID = 2;
  68. GO
  69.  
  70. /*Оформить билет для клиента в город Новосибирск из Томска, с учѐтом занятых
  71. мест в автобусе.*/
  72.  
  73. IF OBJECT_ID('insPassenger', 'P') is NOT NULL
  74.     DROP PROCEDURE insPassenger;
  75. GO
  76.  
  77. CREATE PROCEDURE insPassenger
  78.     @Dest int,
  79.     @Arriv int
  80. AS
  81. BEGIN
  82.     DECLARE @TripBus int = (SELECT Bus_service.ID
  83.     FROM Bus_service
  84.         JOIN Bus_route ON Bus_route.ID = Bus_service.FK_Bus_route_ID
  85.     WHERE Destination_City_ID = @Dest AND Arrival_City_ID = @Arriv AND Departure_date = '20210315'),
  86.             @CountPlace int = 1,
  87.             @Condition bit = 'true';
  88.  
  89.     WHILE (@Condition = 'true' AND @CountPlace <= (SELECT Count_place
  90.         FROM Bus
  91.         WHERE Num = (SELECT FK_Bus_Num
  92.         FROM Bus_service
  93.         WHERE ID = @TripBus)))
  94.     BEGIN
  95.         IF ((SELECT COUNT(ID)
  96.         FROM Buy_ticket
  97.         WHERE FK_Bus_service_ID = @TripBus
  98.             AND Place = @CountPlace
  99.             AND Passenger = 1
  100.             AND Departure_date = '20210315') = 0)
  101.         BEGIN
  102.             INSERT Buy_ticket
  103.             VALUES
  104.                 ((SELECT FK_Bus_Num
  105.                     FROM Bus_service
  106.                     WHERE ID = @TripBus), 1, @TripBus, '20210315', @CountPlace);
  107.             SET @Condition = 'false'
  108.         END
  109.         SET @CountPlace = @CountPlace + 1;
  110.     END
  111. END
  112. GO
  113.  
  114. exec insPassenger 1, 2;
  115. GO
  116.  
  117. --Получить число рейсов, совершѐнных автобусами «Икарус» и «ПАЗ».
  118. SELECT COUNT(*) AS Count_Services
  119. FROM Bus_service
  120.     JOIN Bus ON Bus.Num = Bus_service.FK_Bus_Num
  121. WHERE Bus.Model IN ('ИКАРУС', 'ПАЗ')
  122.  
  123. --Проверить все свободные места на рейс.
  124. IF OBJECT_ID('FreePlace', 'P') is NOT NULL
  125.     DROP PROCEDURE FreePlace;
  126. GO
  127.  
  128. CREATE PROCEDURE FreePlace
  129.     @numberTrip int
  130. AS
  131. BEGIN
  132.     DECLARE @CountPlace int = 1;
  133.     WHILE (@CountPlace <= (SELECT Count_place
  134.     FROM Bus
  135.     WHERE Num = (SELECT FK_Bus_Num
  136.     FROM Bus_service
  137.     WHERE ID = @numberTrip)))
  138.     BEGIN
  139.         IF (@CountPlace NOT IN (SELECT Place
  140.         FROM Buy_ticket
  141.         WHERE FK_Bus_service_ID = @numberTrip))
  142.             PRINT(@CountPlace)
  143.  
  144.         SET @CountPlace = @CountPlace + 1;
  145.     END
  146. END
  147. GO
  148.  
  149. exec FreePlace 1;
  150.  
  151. --Показать число билетов, проданных кассами.
  152. SELECT COUNT(*) AS CountTicket
  153. FROM Buy_ticket
  154.  
  155. --Показать среднюю цену билетов.
  156. SELECT SUM(Price)/COUNT(PRICE) AS AveragePrice
  157. FROM Bus_service
  158.  
  159. --Показать рейсы, цена на которые выше средней цены на билеты.
  160. SELECT FK_Bus_Num, Price, Departure_date, FK_Bus_route_ID
  161. FROM Bus_service
  162. WHERE Price > (SELECT SUM(Price)/COUNT(PRICE)
  163. FROM Bus_service)
  164. --GROUP BY Bus, Price, Departure_date, Bus_route
  165.  
  166. --Отменить клиенту билет, после того как он его вернул.
  167. DELETE FROM Buy_ticket
  168.     WHERE Passenger = (SELECT ID
  169. FROM Passenger
  170. WHERE First_Name = 'Шостакович' AND Second_Name = 'Дмитрий')
  171.  
  172. /*Показать информацию о рейсах из города Томск (включая цену на билет, время
  173. рейса).*/
  174. SELECT FK_Bus_Num, Price, Departure_date, Bus_route.Destination_City_ID, Bus_route.Arrival_City_ID, Bus_route.Time_Destination, Bus_route.Time_Arrival_Point
  175. FROM Bus_service
  176.     JOIN Bus_route ON Bus_route.ID = Bus_service.FK_Bus_route_ID
  177. WHERE Bus_route.Destination_City_ID = (SELECT ID
  178. FROM City
  179. WHERE Name = 'Томск')
  180.  
  181. /*Посчитать количество билетов, проданных по рейсам Томск – Новосибирск за
  182. 2020-2021.*/
  183. SELECT COUNT(Buy_ticket.ID) AS COUNT_TICKET
  184. FROM Bus_service
  185.     JOIN Buy_ticket ON Buy_ticket.FK_Bus_service_ID= Bus_service.ID
  186. WHERE FK_Bus_route_ID IN
  187.             (SELECT ID
  188.     FROM Bus_route
  189.     WHERE Destination_City_ID = 1 AND Arrival_City_ID = 2)
  190.     AND '20220101' > Buy_ticket.Purchase_date
  191.     AND Buy_ticket.Purchase_date >= '20200101'
  192.  
  193. --Назначить автобус на рейс.
  194. IF OBJECT_ID('insBusTrip', 'P') is NOT NULL
  195.     DROP PROCEDURE insBusTrip;
  196. GO
  197.  
  198. CREATE PROCEDURE insBusTrip
  199.     @Bus int,
  200.     @Trip int
  201. AS
  202. BEGIN
  203.     IF (SELECT Count_place
  204.     FROM Bus
  205.     WHERE Num = @Bus) >
  206.        (SELECT MIN_QuantityPlace
  207.     FROM Bus_route
  208.     WHERE ID = (SELECT FK_Bus_route_ID
  209.     FROM Bus_service
  210.     WHERE ID = @Trip))
  211.     BEGIN
  212.         UPDATE Bus_service
  213.             SET FK_Bus_Num = @Bus WHERE ID = @Trip;
  214.     END
  215. END
  216. GO
  217.  
  218. exec insBusTrip 2, 3;
  219.  
  220. /*Получить список рейсов, на которые может быть назначен данный водитель и
  221. данный автобус с числом мест более 30.*/
  222. IF OBJECT_ID('TripBusDriver', 'V') is NOT NULL
  223.     DROP VIEW TripBusDriver;
  224. GO
  225.  
  226. CREATE VIEW TripBusDriver
  227. AS
  228.     SELECT Bus_service.ID, Bus_route.Destination_City_ID, Bus_route.Arrival_City_ID, Bus_route.MIN_QuantityPlace
  229.     FROM Bus_service
  230.         JOIN Bus_route ON Bus_route.ID = Bus_service.FK_Bus_route_ID
  231. GO
  232.  
  233. IF OBJECT_ID('SeachTrip', 'P') is NOT NULL
  234.     DROP PROCEDURE SeachTrip;
  235. GO
  236.  
  237. CREATE PROCEDURE SeachTrip
  238.     @Driver nvarchar(25),
  239.     @bus int
  240. AS
  241. BEGIN
  242.     SELECT ID
  243.     FROM TripBusDriver
  244.     WHERE Destination_City_ID = (SELECT FK_City_ID
  245.         FROM Driver
  246.         WHERE First_Name = @Driver)
  247.         AND MIN_QuantityPlace <= (SELECT Count_place
  248.         FROM Bus
  249.         WHERE Num = @bus)
  250. END
  251. GO
  252.  
  253. exec SeachTrip 'Федоров', 2;
  254. GO
  255.  
  256. --После ремонта в автобусе добавили 3 места, добавить эти места и в БД
  257. UPDATE Bus
  258.     SET Count_place = Count_place + 3
  259.     WHERE Num = 2
  260. GO
  261.  
Advertisement
RAW Paste Data Copied
Advertisement