How to create derived attribute?

Question

I need to create a derived attribute Age that calculates the age of the tuple based on its Datebought attribute. I found that it can be made with views, but I have no idea on where to put 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)
);

Your question is tagged Postgres, but you are using SQL Server functions. — Gordon Linoff, Apr 19 '18 at 10:42
This could be considerably clearer, i.e. explain what the pile of question marks is there for, presumaby to indicates that the following line is pseudocode for which you want a working implementation. — underscore_d, Apr 19 '18 at 10:54

Gordon Linoff · Accepted Answer · 2018-04-19T10:49:04.230

0

You put 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 happen to be using SQL Server, you can define the computed column in the create table as:

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

edited Apr 19 '18 at 10:49

answered Apr 19 '18 at 10:43

Gordon Linoff

1,198,228
53
572
709

How to create derived attribute?

1 Answers1

Linked