Quiz: SQL Basics

A review of basic syntax for SELECT statements, from FROM to some aggregations with GROUP BY

Table of contents
Deadline
Tuesday, October 25 at 1:50 PM
Points
10 Quiz points
Deliverables

  • In your padjo-2016 folder, create a new spreadsheet named quiz-sql-basics

Requirements

  • The quiz-sql-basics spreadsheets should be a simple 2-column spreadsheet that looks like this (no header necessary):

    1 A
    2 B
    3 C
    4 Short answer blah blah blah
This quiz has 13 questions.

Instructions

All of the SQL questions assume you have a database that includes a table named people.

The people table looks exactly like this:

name sex age
Austin M 33
Blair M 90
Carolina F 28
Dani F 41
Donald M 70
Eliza F 37
Farida F 23
Georgina F 19
Hillary F 68
Leland M 16
Liam M 22
Michael M 48
Phoebe F 52
Sherry F 39
Zed M 42

Questions

Question 1

What is the result of this query:

SELECT 'Hello, world';
A.
‘Hello’
world
B.
‘Hello, world’
Hello, world
C.
Hello world
Hello world
D.
‘Hello, world’
HELLO, WORLD
E.
‘Hello, world’
Hello world

Question 2

Which of these queries contains a syntax error? (Remember that SQL keywords and names of tables and columns are not case-sensitive)

A.
SELECT * FROM people;
B.
Select * from PEOPLE;
C.
SELECT 'HELLO' FROM people;
D.
FROM people SELECT *;
E.
select 'hello', 'world' from people;

Question 3

To get only the first five rows from people, with the columns in the default order, which one of the only following queries will work?

A.
SELECT 5 FROM people;
B.
FROM people SELECT *, LIMIT 5;
C.
SELECT FROM people LIMIT 5;
D.
SELECT * FROM people LIMIT 5;
E.
SELECT * LIMIT 5 FROM people;

Question 4

The following query will result in an error:

SELECT name, gender, name
FROM PEOPLE
LIMIT 2;

What error message will result?

A.

No such table: PEOPLE

B.

Called name column too many times

C.

No such column: gender

D.

Failed to include column: sex

E.

Cannot call LIMIT without calling ORDER first

Question 5

We want to query the people table and get all of its rows and columns, except with the rows sorted in order of oldest person first. Which of the following queries will do the job?

A.
SELECT * FROM people ORDER BY name DESC;
B.
FROM SELECT ORDER BY age DESC;
C.
SELECT * FROM people ORDER BY age DESC;
D.
SELECT age FROM people ORDER BY age DESC;
E.
SELECT * FROM people ORDER BY age DESC LIMIT 9;

Question 6

Which query would result in exactly this:

name
Austin
A.
SELECT * FROM people;
B.
SELECT * FROM people LIMIT 1;
C.
SELECT name, sex FROM people LIMIT 1;
D.
SELECT name FROM people;
E.
SELECT name FROM people LIMIT 1;

Question 7

Which query would result in exactly this result:

name first_letter
Austin A
A.
SELECT * FROM people LIMIT 1;
B.
SELECT name, first_letter FROM people LIMIT 1;
C.
SELECT name, SUBSTR(name, 1, 1) AS first_letter FROM people LIMIT 1;
D.
SELECT name, SUBSTR(name, 1, 1) FROM people LIMIT 1;
E.
SELECT SUBSTR(name, 1, 1), name  FROM people LIMIT 1;

Question 8

Given the following query with incomplete WHERE clause:

SELECT name, age
FROM people
WHERE __________
LIMIT 2

And the desired result of:

name age
Blair 90
Dani 41

Which of the following conditional statements should be used?

A.

age > 40

B.

age < 40

C.

name > 40

D.

age > 50

E.

age < 100

Question 9

Given the following query with incomplete WHERE clause:

SELECT
  name, age, sex
FROM
  people

WHERE __________

LIMIT 3;

And the desired result of:

name age sex
Hillary 68 F
Phoebe 52 F

Which of the following conditional statements should be used?

A.

age > 50

B.

sex = 'F'

C.

age > 50 AND sex='F'

D.

age > 50 OR sex='F'

E.

age > 52 AND age = 68

Question 10

Given this incomplete query:

SELECT *
FROM people
WHERE sex = 'M'
______

And the desired result of:

name sex age
Zed M 42
Michael M 48

Which of the following SQL lines will complete the query and get the desired result?

A.

LIMIT 2;

B.

ORDER BY age DESC LIMIT 2;

C.

ORDER BY name;

D.

ORDER BY name DESC LIMIT 2;

E.

LIMIT 2 ORDER BY name DESC;

Question 11

We want to do a simple count of the people table. This is what the desired result will look like:

COUNT(*)
15

Which of the following queries will get the desired result?

A.

SELECT COUNT FROM people;

B.

SELECT COUNT(*) FROM people;

C.

SELECT ROWCOUNT FROM people;

D.

COUNT people;

E.

SELECT *, COUNT FROM people;

Question 12

Let’s get a count of the people table by sex, listed in order of highest row count per sex.

We want the exact results, headers, and column arrangement here:

sex sexcount
F 8
M 7

Which of the following queries will return the exact desired result?

A.
SELECT
  sex, COUNT(*) AS sexcount
FROM people;
B.
SELECT
  sex, sexcount
FROM people
GROUP BY sex
ORDER BY sexcount DESC;
C.
SELECT sex,
  COUNT(*) AS sexcount
FROM people
GROUP BY sex
ORDER BY sexcount DESC;
D.
SELECT sex,
  COUNT(*) AS sexcount
FROM people
ORDER BY sexcount DESC;
E.
SELECT sex,
  COUNT(*) AS sexcount
FROM people
GROUP BY sex
ORDER BY sex DESC;

Question 13

We want to create a simple list showing just the sex of the 5 oldest people in the people table.

Write the query out as a short answer.

Hints:

  1. No aggregation, or even WHERE clause required.
  2. Do not include any unnecessary columns or rows.
  3. These are the keywords you’ll need: FROM, LIMIT, ORDER, SELECT, BY, DESC
  4. …Not in that order.

When your query is executed, this what the exact result should be:

sex
M
M
F
F
M

Answers

  1. B
  2. D
  3. D
  4. C
  5. C
  6. E
  7. C
  8. A
  9. C
  10. D
  11. B
  12. C

  13. The query:

    SELECT sex
FROM people
ORDER BY age DESC
LIMIT 5;