Add a new column to a view in SQL

Question

This is my view

CREATE VIEW seat_availability AS
SELECT flightid,flightdate, maxcapacity,
FROM flight

And I want to add 2 new columns named 'bookedseats' and 'availableseats' which don't exist in any tables but are columns I need to add.

I've done my research online and some say you can alter views by using:

ALTER VIEW 

And some have said that you can't do that and have to edit from the view you've just created.

I've tried this:

CREATE VIEW seat_availability AS
SELECT flightid,flightdate, maxcapacity, bookedseats varchar(10), availableseats varchar(10)
FROM flight

which gave this error:

ERROR: syntax error at or near "varchar" LINE 2: ...ECT flightid,flightdate, maxcapacity, bookedseats varchar(10...

I've also tried ALTER VIEW:

ALTER VIEW seat_availability AS
SELECT flightid,flightdate, maxcapacity, bookedseats varchar(10), availableseats varchar(10)
FROM flight

And I got this error:

ERROR: syntax error at or near "AS" LINE 1: ALTER VIEW seat_availability AS

It would be easy to add columns if they existed in other tables but because I need to add 2 columns that don't exist in any table, it's proving difficult to do. If someone could help it would be very appreciated. Thank you.

Perhaps I may need to drop the view? and start again with two new columns added but how do I add them since they don't exist in any table in my database??

Solution

You don't define the datatype of a column like that. You let the view use the underlying datatype like this.

ALTER VIEW seat_availability AS
SELECT flightid
    , flightdate
    , maxcapacity
    , bookedseats
    , availableseats
FROM flight

Or if you need to explicitly change the datatype you need to use CONVERT like this.

ALTER VIEW seat_availability AS
SELECT flightid
    , flightdate
    , maxcapacity
    , bookedseats = convert(varchar(10), bookedseats)
    , availableseats = convert(varchar(10), availableseats)
FROM flight