BIOGRAPHY

Each row represents a chimpanzee and is a transformation of the corresponding BIOGRAPHY_DATA row, making the data more like the traditional format and therefore, in one sense, easier to work with. This view (like the underlying BIOGRAPHY_DATA table itself) contains one row for each chimpanzee on which data has ever been recorded (in SokweDB). Additional rows exist for various codes used when a chimpanzee is partially or entirely unidentified, such as UNK or MGF. An additional NONE row is used when it does not make sense to record an individual. BIOGRAPHY contains the basic demographic data of individual chimpanzees.

BIOGRAPHY is a simple view, consisting of a single table along with a few expressions which construct data to be presented in computed (virtual, in a sense) columns. In this spirit, here is a relatively simple query. (It does contain a computed column, and does use a computation to determine sort order.[1]) It reports on males, sorting them first by whether or not they are alive, missing, known to be dead, etc., and within that by age, and within that by name.

Males, by whether they are alive (or missing, or etc.), by age, by name

SELECT biography.animid, biography.departtype, biography.animname
     , biography.birthdate
     , (biography.departdate - biography.birthdate) / 365.25
       AS years_old
  FROM biography
  WHERE sex = 'M'
  ORDER BY biography.departtype
         , (biography.departdate - biography.birthdate)
         , biography.animname;

Definition

CREATE OR REPLACE VIEW biography (
   animid
  ,animidnum
  ,animname
  ,birthcomm
  ,bccertainty
  ,sex
  ,momid
  ,dadid
  ,dadidpub
  ,firstborn
  ,birthdate
  ,bdmin
  ,bdmax
  ,bddist
  ,entrydate
  ,entrytype
  ,departdate
  ,departtype)
  AS
    SELECT
        biography_data.animid
       ,biography_data.animidnum
       ,biography_data.animname
       ,biography_data.birthcomm
       ,biography_data.bccertainty
       ,biography_data.sex
       ,biography_data.momid
       ,CASE
          WHEN biography_data.dadstatus = 'Prelim'
            THEN biography_data.dadid || '_prelim'
          ELSE biography_data.dadid
       END CASE
       ,biography_data.dadidpub
       ,biography_data.firstborn
       ,biography_data.birthdate
       ,biography_data.bdmin
       ,biography_data.bdmax
       ,biography_data.bddist
       ,biography_data.entrydate
       ,biography_data.entrytype
       ,biography_data.departdate
       ,biography_data.departtype
     FROM biography_data;

Columns of the BIOGRAPHY View

The columns of the BIOGRAPHY view

Column	From	Description
AnimID	BIOGRAPHY_DATA.AnimID	Animal IDentifier
AnimIDNum	BIOGRAPHY_DATA.AnimIDNum	Animal IDentifier Number
AnimName	BIOGRAPHY_DATA.AnimName	Animal Name
BirthComm	BIOGRAPHY_DATA.BirthComm	Birth Community
BCCertainty	BIOGRAPHY_DATA.BCCertainty	Certainty of BirthComm
Sex	BIOGRAPHY_DATA.Sex	Individual’s Sex
MomID	BIOGRAPHY_DATA.MomID	AnimID of the individual’s mother
DadID	BIOGRAPHY_DATA.DadID BIOGRAPHY_DATA.DadStatus	AnimID of the individual’s father, suffixed with _prelim if DadStatus is Prelim
DadIDPub	BIOGRAPHY_DATA.DadIDPub	Publication of Paternity citation
FirstBorn	BIOGRAPHY_DATA.FirstBorn	First born status code
BirthDate	BIOGRAPHY_DATA.BirthDate	Birth Date
BDMin	BIOGRAPHY_DATA.BDMin	Minimum Birth Date
BDMax	BIOGRAPHY_DATA.BDMax	Maximum Birth Date
BDDist	BIOGRAPHY_DATA.BDDist	Birth Date Distribution
EntryDate	BIOGRAPHY_DATA.EntryDate	Date of study Entry
EntryType	BIOGRAPHY_DATA.EntryType	Entry status code
DepartDate	BIOGRAPHY_DATA.DepartDate	Date last seen
DepartType	BIOGRAPHY_DATA.DepartType	Depart date status code

Operations Allowed

None.

Footnotes

[1]

The computation leverages the fact that, when performing addition and subtraction operations on the DATE data type, the result is an integer – a number of days. This makes it easy to compute an age in days, and to convert that to other units.

Other data types, like date-and-time-together TIMESTAMP data type, do not have this feature. In those cases the julian() function, which returns a “date” that is counted in units of days, is a convenient way to replicate this kind of computation.

Page last generated: 2026-08-13 23:02:22 UTC