1

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)
);
MammaDiGrenie'
  • 97
  • 1
  • 7
  • 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
  • There is no `datediff()` function in Postgres –  Apr 19 '18 at 10:58

1 Answers1

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 ),
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786