How to create derived attribute?

Question

I would like to create one derived attribute "Age" which estimates the age of the tuple based on its "Datebought" attribute. I discovered that it can be made with views, although I have no idea where to place the view.

CREATE TABLE Kids_Bike(
    BikeId ID,
    BrandName VARCHAR(max),
    ModelName VARCHAR(max),
    DateBought DATE,
    /*??????????????????????*/
    Age (SELECT datediff(day, DateBought , GETdate()) / 365.2425 ) DECIMAL,
    Color VARCHAR(max),
    StationId ID,
    TrainingWheels BIT,
    PRIMARY KEY (BikeId),
    FOREIGN KEY (StationId) REFERENCES RentingStation(StationID)
);

Answer 1

You have to place the view outside the table:

create view v_kids_bike as
    select kb.*, extract(day from (current_date - date_bought)) / 365.2425 as age
    from kids_bike;

If you're using the SQL Server, then you can define the computed column in the create table like this:

Age as (SELECT datediff(day, DateBought, GETdate()) / 365.2425 ),

Sign up for the SQL Course to learn SQL in detail and to get certified.