Diff for "BabaseQuiz"

This quiz was created by Karl O. Pinc, Susan C. Alberts and Niki H. Learn. Questions originally compiled by Lacey Roerish and later revised by Niki H. Learn and Jake Gordon.

You may not pass go, you may not collect 200 dollars at the gates of Babase until you have successfully completed this quiz.

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. 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;

4. Write a query that will return a table showing 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 2009, 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 age
  from actor_actees,
       ranks,
       rankdates,
       biograph
  where actor_actees.actor = biograph.sname and
       actor_actees.date >= '2000-01-01' and
       actor_actees.date <= '2008-12-31' and
       actor_actees.actee = rankdates.sname and
       actor_actees.date >= rankdates.ranked and
       rnkdate(actor_actees.date) = ranks.rnkdate and
       actor_actees.actor_grp = ranks.grp and
       actor_actees.act = 'G'
  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,
pregs,
members
where mtd_cycles.sname = members.sname and
mtd_cycles.dcpid = pregs.conceive and
mtd_cycles.ddate > '1999-10-31'
order by ddate
limit 100;

7. 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.

Instead of:

from mtd_cycles, pregs, members

You can say:

from mtd_cycles as mc, pregs as p, members as m

Or simply:

from mtd_cycles mc, pregs p, members m

Then you can rewrite the query from question 6 as follows:

      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, pregs p, members m
        where   mc.sname = m.sname and
                mc.dcpid = p.conceive and
                mc.ddate = m.date and
                mc.ddate > '1999-10-31' 
      order by 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 at maturity of males and females (in years with separate means for males and females but using only one query – you’ll need to use “group by”) baboons born in wild-feeding study groups (hint: matgrp < 3) whose birthdate and maturedate are both known to within a few weeks (hint: pay attention to the 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.)

9. Employ a subquery to create a list of all adult males who have ever lived in a wild-feeding study group that includes 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 each 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.)

10. You’re interested in creating grooming networks for groups six months before, during, and six months after each fission but you need to know which fissions have grooming data consistently throughout the periods you are looking at. (Hint: See the technical specifications for the groups_history view and the behave_gaps table. http://papio.biology.duke.edu/babase_system_html/)

• a. Write a query that shows the boundaries for each fission and includes last_reg_census (for determination of whether or not a group was actually ever a study group).
• b. What table or view should you use to check whether there are any substantial chunks of time within your pre-fission, fission, and post-fission periods when grooming data might be systematically sparse or lacking? Do you see any such periods of time?
• c. Write a query that checks how dense grooming data are during at least one of the periods of time for a group, identified in your answer to part b.

QuizAnswers

There are additional quiz and quiz-like questions available that are more illustrative of real-world queries.

• ExtendedQuizQuestions

• WritingAComplexQuery