w3d1

Question 1

What is the syntax to check if an element is within a collection (in a WHERE clause)?

Answer 1

name IN (‘Denmark’, ‘Finland’, ‘Norway’, ‘Sweden’)

Question 2

How can we retrieve a country that starts with the letter ‘G’

Answer 2

name LIKE ‘G%’;

Question 3

Explain regex syntax for finding words that contain letters.

Answer 3

Use ‘LIKE’, then put a ‘%’ at the end of the string you are searching for.

Question 4

In which years was the Physics prize awarded, but no Chemistry prize?

Answer 4

SELECT DISTINCT
      yr
    FROM
      nobels
    WHERE
      (subject = 'Physics' AND yr NOT IN (
        SELECT
          yr
        FROM
          nobels
        WHERE
          subject = 'Chemistry'
      ))

Question 5

T/F: values from an inner SELECT are inaccessible from an outer SELECT.

Answer 5

F; they are.

Question 6

What is the order of evaluation in the SELECT-FROM-WHERE triad?

Answer 6

FROM is evaluated first, then WHERE, then SELECT.

Question 7

Find each country that belongs to a continent where all populations are
less than 25,000,000. Show name, continent and population.

Answer 7

SELECT
      c1.name, c1.continent, c1.population
    FROM
      countries c1
    WHERE
      c1.continent NOT IN (
        SELECT
          c2.continent
        FROM
          countries c2
        WHERE
          c2.population >= 25000000
      );

Question 8

How do we get rid of repetition in SELECT clauses?

Answer 8

Use SELECT DISTINCT

Question 9

What is up with the GROUP BY clause?

Answer 9

Tells how to combine elements:

SELECT
      continent, COUNT(*)
    FROM
      countries
    WHERE
      population >= 10000000
    GROUP BY
      continent;

Question 10

Explain JOIN

Answer 10

You JOIN tables ON a value = another (logically correlated) value. The resulting table now has access to EVERYTHING from the previous tables.

Question 11

What does the COALESCE keyword do?

Answer 11

Retrieves the first value from a list of values that is not NULL.

Question 12

Explain the CASE-WHEN-ELSE clauses. Where are they used?

Answer 12

They are used within a SELECT clause, and follow the following syntactical guidelines:

SELECT
      teachers.name,
      CASE
        WHEN teachers.dept_id IN (1, 2) THEN 'Sci'
        ELSE 'Art'
      END AS dept_name
    FROM
      teachers;

Question 13

What does an unqualified JOIN represent?

Answer 13

INNER JOIN