Assessment: Using Databases --- Combining Data

Oct 2, 2014 • Thomas Guignard

This assessment uses the Pole of Inaccessibility survey database that is being used to illustrate the Software Carpentry SQL version 5 lesson.

Given the tables Person (5 rows total)

person

and Survey (21 rows total, 2 of them have NULLs in the person column)

survey

How many rows would the following SQL statement return?

SELECT * FROM Survey
         JOIN Person ON Survey.person = Person.ident
         GROUP BY Person;
  • a) 5
  • b) 21
  • c) 4
  • d) 19

Now, if from the same two tables we wanted to return the radiation (rad in the quant column) measurements taken by surveyors whose family name ends with the letter e, where should the following code snippets fit in our SQL statement?

SELECT _______ FROM _____ JOIN _______ ON ______ WHERE _________
  • Survey.reading
  • Survey.person = Person.ident
  • Person.family LIKE “%e”
  • Person
  • quant=”rad”;
  • Survey
  • AND