New users must successfully complete this quiz before working on their own projects!
1. What does ending your query with “limit 100” do and when should you use this statement?
2. Write a query that returns all the information in biograph about offspring of the female named PLUM in birth order. (Hint: Don’t order by pid – pids may not match parity and 10 through 19 will organize themselves between 1 and 2.)
3. The result of the below query could be described as "a list of all dead individuals".
SELECT * FROM biograph WHERE status = 1;
The result of the below query will return a list of...what?
SELECT * FROM biograph, members WHERE matgrp = grp AND biograph.sname = members.sname AND sex = 'M' AND status = 0 AND dcause = 0 AND statdate = date ORDER BY matgrp, pid;
4. Write a query whose result shows each individual’s short name, birth date, status of their birthdate estimate, their year of birth, maturedate, and whether their maturedate is an “on” or a “by” date, and that will write that table to your own schema in a table called “temptable”, and will order it by the individual’s name. The table should return only females, and only ones that actually have a mature date. (Hint: You'll need biograph and maturedates.)
5. You are running a query to find all grooming events on adult males between 2000 and 2008, inclusive, organizable by year and the male’s age, but your query is hanging up in Babase (because you forgot to use limit 100). After you ask a Babase administrator to kill your query, you need to figure out what is wrong. Add a line to the query to fix it.
select *, (actor_actees.date - biograph.birth)/365.25 as male_age from actor_actees join ranks on ranks.grp = actor_actees.actee_grp join rankdates on rankdates.sname = actor_actees.actee join biograph on biograph.sname = actor_actees.actee where actor_actees.date >= '2000-01-01' and actor_actees.date <= '2008-12-31' and actor_actees.date >= rankdates.ranked and rnkdate(actor_actees.date) = ranks.rnkdate and actor_actees.act = 'G' and ranks.rnktype = 'ALM' order by actor_actees.date;
6. You are looking for all conceptions that have occurred since the beginning of hydrological year 2000 (i.e., since 1 Nov 1999). This time you remembered to use “limit 100” and don’t have to have your query killed but the output is clearly wrong – when Babase finally spits out an answer, all 100 rows returned by the query are about one conception. Add a line to fix the query.
select mtd_cycles.sname , mtd_cycles.ddate , extract(year from mtd_cycles.ddate) as year , hydroyear(mtd_cycles.ddate) as hydroyr , extract(month from mtd_cycles.ddate) as month , pregs.pid , pregs.resume , members.grp from mtd_cycles join pregs on pregs.conceive = mtd_cycles.dcpid join members on members.sname = mtd_cycles.sname where mtd_cycles.ddate > '1999-10-31' order by ddate limit 100;
- a. What data will this query return?
select * from biograph, members where matgrp = grp and biograph.sname = members.sname and sex = 'M' and status = 0 and dcause = 0 and statdate = date order by matgrp, pid;
- b. What data will this query return?
select * from biograph, members where biograph.matgrp = members.grp and biograph.sname = members.sname and biograph.sex = 'M' and biograph.status = 0 and biograph.dcause = 0 and biograph.statdate = members.date order by biograph.matgrp, biograph.pid;
- c. What data will this query return?
select * from biograph join members on (biograph.matgrp = members.grp and biograph.sname = members.sname) where biograph.sex = 'M' and biograph.status = 0 and biograph.dcause = 0 and biograph.statdate = members.date order by biograph.matgrp, biograph.pid;
- d. What data will this query return?
-- This is an illustrative query used in the Babase Quiz -- This is how you can write notes about the query to yourself within the query. SELECT * FROM biograph JOIN members ON (biograph.matgrp = members.grp AND biograph.sname = members.sname) WHERE biograph.sex = 'M' AND -- M means male (tricky 'eh?) biograph.status = 0 AND biograph.dcause = 0 AND biograph.statdate = members.date ORDER BY biograph.matgrp, biograph.pid;
- e. What are the pros and cons of the various queries above. Which do you prefer?
8. Here’s another option, which may be especially helpful for longer queries – you can "alias" the tables. As an example, let’s rewrite the query from question 6 using aliases.
from mtd_cycles, pregs, members
You can say:
from mtd_cycles as mc, pregs as p, members as m
from mtd_cycles mc, pregs p, members m
Then you can rewrite the query from question 6 as follows: (this query has not been fixed by the answer to #6, so don't run this query unless you've fixed it first!)
select mc.sname , mc.ddate , extract(year from mc.ddate) as year , hydroyear(mc.ddate) as hydroyr , extract(month from mc.ddate) as month , p.pid , p.resume , m.grp from mtd_cycles mc join pregs p on p.conceive = mc.dcpid join members m on m.sname = mc.sname where mc.ddate > '1999-10-31' order by mc.ddate limit 100;
When you give the tables aliases, you can name them whatever you want; you don't HAVE to pick the first letter of the table like in the example above.
Now write a query using aliases that will provide the minimum, maximum, mean, and standard error for age (in years) at maturity of males and females, with separate rows for males and females but using only one query (you’ll need to use “group by”). Include only baboons born in wild-feeding study groups whose birth date is both known to within a few weeks and whose maturedate is known (hint: pay attention to the matgrp, bstatus, and mstatus columns). Be sure to name the output columns by placing “as column_name” at the end of each row that’s doing math for you. How do the results compare if you don’t constrain the results by matgrp, bstatus, and mstatus? (Hint: you’ll need biograph and maturedates.) (Another hint: "wild-feeding" = "grp < 3")
9. Write a query that will create a list of all adult males who have ever lived in a wild-feeding study group. Include the male’s sname, birthdate, the date he attained adult rank, his statdate, his biograph "status" (alive, dead, or censored), and a count of all offspring the male is known to have sired (be sure to include adult males who have no known offspring!). (Hint: You’ll need biograph, rankdates, and parents.) (Another hint: to get all the males who have ever lived in a wild-feeding study group, you'll need to use a subquery that involves one or more other tables, i.e. not biograph, rankdates, nor parents.)
10. Throughout the 50+ years of baboon project history, there have been periods of time when data collection was interrupted or was generally sparser than usual. Also, there are periods of time where study groups are in the process of a fission into two or more new groups, or fusing multiple groups into a new one. Data from these fission/fusion periods can be weird in numerous ways. For most analyses, it's important that you be aware of these periods (both the data interruptions and the fissions/fusions) and handle your data appropriately. Exactly what is "appropriate" is a topic that you will need to discuss with your advisor/principal investigator/etc.
In this question, we'll work with some of the tools available to you to help you identify these periods:
The BEHAVE_GAPS table is discussed in the Babase tech specs. It identifies periods where data collection was interrupted or sparse for a known systematic reason, e.g. "Focal data collection was limited because observers were transitioning to collecting data on a new device". This often includes fission/fusion periods, but there are “gaps” listed here that are unrelated to fission/fusion, and there are some fissions that do not have a related “gap” here.
The GROUPS_HISTORY view is also discussed in the Babase tech specs. It is easier in this view than it is in the GROUPS table to recognize fission/fusion periods. Or rather, it is easier to see when a group is NOT in one of those periods. See the Babase FAQs for more information.
The DATA_SUMMARY_MONTHLY view is in the babase_pending schema, and is therefore not (yet) documented. It shows the amount of different kinds of data were collected for each group, in each month. Specifically: the number of hours that observers were present observing the group (according to our “SWERB”/GPS data), the number of individuals censused present in the group at all in that month, the number of agonisms and groomings recorded, and the number of focal samples collected.
The DATA_SUMMARY_YEARLY view is...like DATA_SUMMARY_MONTHLY. But it's by year.
Before you proceed with this question, go look at each of these tables/views and make sure you understand what information they contain.
Now let's use these tools to answer some questions. You’re interested in creating grooming networks for groups six months before, during, and six months after each fission. Before you do that, you need to know which fissions have grooming data consistently throughout the periods you are looking at.
a. Identify three groups that fissioned, the groups that formed from that fission, and the beginning and end dates of their fission. E.g. "The fission of group into and group began on _ and ended on _".
b. For each of the three fission periods you identified above and their 6-month pre- and post-fission periods, are there any times when grooming data might be systematically sparse or absent?
- c. Choose one of the fissions from part a, then write a query that checks how dense grooming data are during that fission and the 6 months before and after it.
11. You have probably noticed by now that nearly every table in Babase has a column called "Sys_Period".
- a. What does this column represent?
b. The Sys_Period column is always a range of time, like [2020-02-20 02:02:02, 2021-11-11, 11:11:11). In the babase schema, the "end" time of the Sys_Period is always unspecified, e.g. [2022-01-02 03:04:05,). Why?
- c. If you need to see an earlier version of the data in a series of tables, where can you go to find instructions on how to recreate these earlier versions?
There are additional quiz and quiz-like questions available that are more illustrative of real-world queries.
Questions 1-7 were created by Karl O. Pinc, Susan C. Alberts and Niki H. Learn. Questions 8-10 were written by Niki H. Learn, with some revisions by Jake Gordon. Question 11 is all Jake's fault.