FrançaisGet started

Functions and CASE

The usual scalar functions (strings, numbers, dates, CAST) and the CASE expression, simple or searched, including inside an aggregate: predicting what each expression returns, NULL included.

  • 22 cards
  • 5 topics
  • Free
Study this deck

The outline, topic by topic

Each topic is weighted the way the exam weighs it.

  1. Concepts
  2. Questions
  3. Sort and link
  4. Scenarios
  5. Understand

A few cards from the deck

  • Scalar function: what is it?

    Show the answer

    A function applied to the values of one row, returning one value per row: unlike an aggregate, it does not reduce the number of rows.

    UPPER(nom), ROUND(prix, 2) or EXTRACT(YEAR FROM date) give as many values as rows read. SUM or COUNT sum up a whole table or a group.

  • What does this CASE return for note = 12?

    ```sql
    CASE
    WHEN note >= 10 THEN 'Pass'
    WHEN note >= 12 THEN 'Good'
    ELSE 'Fail'
    END
    `

    • 'Pass'
    • 'Good'
    • 'Fail'
    • NULL
    Show the answer

    'Pass'

    CASE stops at the first true WHEN, and 12 >= 10. The second WHEN is unreachable: test the highest threshold first (note >= 12 before note >= 10).

  • In a CASE with no …, a row that satisfies no WHEN gets ….

    Show the answer

    In a CASE with no ELSE, a row that satisfies no WHEN gets NULL.

    The database does not choose for you: the result is NULL, not an empty string or 0. Add an ELSE when you want a default value.

  • Searched CASE: what is it?

    Show the answer

    An expression that tests conditions in order and returns the result of the first true WHEN, otherwise that of the ELSE, or NULL if there is none.

    CASE WHEN note >= 10 THEN 'pass' ELSE 'fail' END. The order of the WHEN clauses matters: once a condition is true, the following ones are no longer looked at.

  • The ville column holds Lyon, LYON and lyon. Which condition finds all three?

    • WHERE ville = LOWER('Lyon')
    • WHERE LOWER(ville) = 'lyon'
    • WHERE UPPER(ville) = 'Lyon'
    • WHERE ville = 'Lyon'
    Show the answer

    WHERE LOWER(ville) = 'lyon'

    Both sides must be in the same case. LOWER(ville) is always lower case, so compare it with 'lyon'. ville = LOWER('Lyon') finds only lyon, ville = 'Lyon' only Lyon; UPPER(ville) = 'Lyon' finds nothing, since an upper-case text never equals 'Lyon'.

Study this deck