These two MCQs come from the Databases and SQL part of the SWC Lessons. I have never used SQL outside of the SWC workshop, so I may be wrong about some things.
Question 1.
From this lesson.
-
You have a dataset
Visited
that is stored in a relational database that has a column calledSite
. Using the SQLite commandselect * from Visited where Site = DIR-1;
, what would the expected result be?- All records where the value of site is
DIR-1
- Nothing
- An error message
- All records where the value of site contains the string
DIR-1
- All records where the value of site is
Question 2.
From this lesson.
-
In the
Survey
dataset, you were told that there are missing values in thePerson
column (which has 3 names in the data). Using the SQLite commandselect * from Survey where Reading < 25 and Person != 'lake';
, what would you expect the result to be?- Nothing, because there is missingness in that column
- All values that have
Reading
as less than 25 andPerson
that is not ‘lake’, including missing values - An error because of the missingness
- Same as
b.
except excluding missing values
Distractors
- From my experience (at least in Linux), an output of nothing in
this case would mean that the code didn’t work. Given that there
are at least 3 names in the
Person
column, the code should output something. If a student answers with this option, they likely don’t understand the SQL syntax or don’t understand what is within the dataset. - SQL does not know how to treat NULL values in this way, so will not
output missingness. If the student assumes this by answering with
this option, their end product will not accurately represent the
data as they want it. They need to explicitly call for null values
(
IS NULL
). - If answering with this option, the student probably uses R, as R often throws up an error whenever it encounters a NULL value. I’m sure there are other languages that do this, but I have only used R and SAS (which doesn’t have this behaviour). However, SQL it not R and should not be treated as such.
- Correct answer.