MCQ: SELECT Statement (SQL)

Feb 13, 2014 • Abigail Cabunoc

Dataset for all questions:

Students:
student_id  first_name  last_name
---------------------------------
1           John        Smith
2           John        Doe
3           Jane        Smith
4           Sally       Johnson

Question to distinguish novices from competent practitioners before you start teaching:

Which query will give us the following result?

last_name    student_id
-----------------------
Smith        1
Doe          2

A)

SELECT last_name, student_id 
  FROM Students 
 WHERE last_name IN ('Smith', 'Doe');

B)

SELECT last_name, student_id 
  FROM Students 
 WHERE first_name = 'John';

C)

SELECT * 
  FROM Students 
 WHERE student_id < 3;

D)

SELECT * 
  FROM Students 
 WHERE last_name IN ('Smith', 'Doe') 
   AND student_id < 3;
 

 

One multiple-choice question to tell whether your audience has actually learned what you set out to teach

What would the results of this query look like?

SELECT * 
  FROM Students 
 WHERE last_name != 'Smith' 
   AND first_name = 'John'

A)

first_name  last_name
---------------------
John        Doe
Sally       Johnson

B)

first_name  last_name
---------------------
John        Doe

C)

student_id  first_name  last_name
---------------------------------
1           John        Smith
2           John        Doe

D)

student_id  first_name  last_name
---------------------------------
2           John        Doe

 

One short practical exercise they could do to exercise their new knowledge

**Write a query that returns the student id for all students with the last name Smith.