Please Create stored Procedure Mandatory Create the tables

$$

Please Create stored Procedure Mandatory

Create the tables and insert values in table and create the stored procedure and then execution needed.

Create a table ‘tbl_FlightDetails’ used to store the details of various flights

tbl_FlightDetails consists of

FlightNumber with datatype Numeric(6)

DateofTravel with datatype Date

Source with datatype varchar(50)

Destination with datatype Varchar(50)

TicketCost with datatype Decimal(8,2)

TicketsAvailable with datatype Integer

And insert the values in the tbl_FlightDetails table.

And then create the stored procedure which performs the following operations on the table tbl_FlightDetails

Based on the FlightNumber, DateofTravel, Source, Destination and TicketsRequired the procedure must provide

TotalTicketCost based on the TicketsRequired and TicketCost. If the flight is available for the given source and destination for the specified date.

Update the TicketsAvailable column of the table based on the TicketsRequired if the required tickets are available.

If the required number of tickets are available it must return 0 else it must return -1.

If flight is not available for the specified date, source or destination it must return -2

Any other error, it must return -3.

Solution

--------- Table Creation --------

CREATE TABLE tbl_FlightDetails
(
   [FlightNumber] NUMERIC(6),
   [DateofTravel] DATE,
   [Source] VARCHAR(50),
   [Destination] VARCHAR(50),
   [TicketCost] DECIMAL(8,2),
   [TicketsAvailable] INTEGER
)

------------------ Table Values ------------------------------------

INSERT INTO tbl_FlightDetails VALUES(18001,\'27-DEC-2014\',\'Bengaluru\',\'London\',50000.00,15)
INSERT INTO tbl_FlightDetails VALUES(18002,\'28-DEC-2014\',\'Bengaluru\',\'NewYork\',75000.00,18)
INSERT INTO tbl_FlightDetails VALUES(18003,\'29-DEC-2014\',\'Chennai\',\'London\',60000.00,16)
INSERT INTO tbl_FlightDetails VALUES(18004,\'30-DEC-2014\',\'Mumbai\',\'NewYork\',70000.00,5)
INSERT INTO tbl_FlightDetails VALUES(18005,\'31-DEC-2014\',\'Delhi\',\'London\',60000.00,4)

---------- Stored Procedure Creation --------------

Go
CREATE PROC usp_GetTicketCost
(
   @FlightNumber NUMERIC(6),
   @DateofTravel DATE,
   @Source VARCHAR(50),
   @Destination VARCHAR(50),
   @TicketsRequired INT,
   @TotalTicketCost Decimal(8,2) OUT
)
AS
BEGIN
  
   DECLARE @TicketsAvailable INT
   DECLARE @TicketCost DECIMAL(8,2)
   DECLARE @Status INT
  
   BEGIN TRY
  
       IF EXISTS(SELECT * FROM tbl_FlightDetails WHERE
       FlightNumber = @FlightNumber and
       DateofTravel = @DateofTravel and
       Source = @Source and
       Destination = @Destination)
       BEGIN
      
           SELECT @TicketsAvailable = TicketsAvailable,
               @TicketCost = TicketCost
               FROM tbl_FlightDetails
                 
           WHERE FlightNumber = @FlightNumber and
               DateofTravel = @DateofTravel and
               Source = @Source and
               Destination = @Destination
              
           IF(@TicketsAvailable >= @TicketsRequired)
               BEGIN
                   UPDATE tbl_FlightDetails SET
                   TicketsAvailable = TicketsAvailable-@TicketsRequired
                   WHERE FlightNumber = @FlightNumber
                   SET @TotalTicketCost = @TicketCost*@TicketsRequired
                  
                   PRINT \'Success\'
                   SET @STATUS = 0
               END
           ELSE
                   PRINT \'Tickets Available lessthan the Tickets Required\'
                   SET @STATUS = -1
       END
       ELSE
           BEGIN
               PRINT \'The Flight Does not exist\'
               SET @STATUS = -2
           END
   END TRY
   BEGIN CATCH
       SET @STATUS = -3
       PRINT \'ERROR OCCURRED\'
   END CATCH
END

-------- *** Execution for Stored Procedure *** --------
Go

DECLARE @TotalTicketCost AS Decimal(8,2)
EXEC usp_GetTicketCost 18002,\'28-Dec-2014\',\'Bengaluru\',\'NewYork\',0,
@TotalTicketCost OUT