Diff for "BabaseQuiz"

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

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

1. 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 matured date.

2. Write a query that returns all the information in biograph about offspring of the female named PLU.

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. What does ending your query with “limit 100” do and when should you use this statement?

5. You are running a query to find all grooming events on adult males between 2000 and 2008, inclusive, but your query is hanging up in babase (because you forgot to use limit 100). After you ask Jake or Niki 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 – all 100 rows 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
-- It is fiendishly tricky so pay close attention!
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. Write a query that returns the number of days that each male offspring spends in his maternal group -- the one codified in the GROUPS table.

9. Write a query that shows, for each male, how many days the male stays in its maternal group before dispersing. Hint: Use the DISPERSEDATES table.

10. Ignoring problems due to intermittent observational frequency of the groups, what is the per-group sample size, mean, and standard deviation of the number of days a male stays in each group before dispersing?

QuizAnswers

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

• ExtendedQuizQuestions

• WritingAComplexQuery