AI InterviewSearch…Search problems, topics, or pages…Ctrl KGet started
Skip to content

05 Functions / Checkpoint

Checkpoint — Functions

25 min · +25 XP · 3 exercises · 11 questions

Exercises0/3
Quiz · 70% to clear11 q

This checkpoint covers the whole module: string functions for joining, cleaning, cutting and padding text; numeric functions for rounding, integer division and remainders; date types, date parts and DATE_FORMAT; date arithmetic with INTERVAL, DATEDIFF, TIMESTAMPDIFF and LAST_DAY; and the conditional functions IF, IFNULL, NULLIF, GREATEST, LEAST and FIELD.

How it works. Eleven questions and three queries. You need 70% on the questions and every query accepted to clear the module. You can retake it as often as you like; your best score counts.

Before you start, make sure you can answer these from memory:

  • What does CONCAT return when one argument is NULL, and what does CONCAT_WS do instead?
  • What do SUBSTRING_INDEX(s, '@', 1) and SUBSTRING_INDEX(s, '@', -1) return for an email address?
  • What is -7 DIV 2, and how do FLOOR, CEIL, ROUND and TRUNCATE differ for negative numbers?
  • Why is 5 / 2 equal to 2 in PostgreSQL and 2.5 in MySQL?
  • Which weekday is 1 in DAYOFWEEK, and which is 0 in WEEKDAY?
  • What is '2024-01-31' + INTERVAL 1 MONTH?
  • What is the argument order of DATEDIFF, and of TIMESTAMPDIFF?
  • Why is a half-open range better than BETWEEN for a month of DATETIME values?
  • What do NULLIF(x, 0) and FIELD(status, …) return, and what are they used for?

The three queries are a contact-card formatter that cleans names and splits emails, an invoice report that computes due dates, days overdue and a rounded late fee, and a shipment board with labels, a safe on-time rate and a custom sort order.

Common questions

What does CONCAT return when one argument is NULL?

In MySQL CONCAT returns NULL if any argument is NULL, so a missing middle name erases the whole full name. CONCAT_WS(' ', first, middle, last) skips NULL arguments, and wrapping a column in COALESCE(col, '') is the other fix.

What is the argument order of DATEDIFF and TIMESTAMPDIFF?

DATEDIFF(later, earlier) puts the later date first and returns days. TIMESTAMPDIFF(unit, earlier, later) puts the unit first and then the start date, so the order of the dates is reversed. Mixing them up flips the sign of the answer.

Why use NULLIF in a division?

x / NULLIF(y, 0) divides by NULL instead of zero when y is 0, so the result is NULL rather than an error in PostgreSQL and SQL Server. MySQL returns NULL for division by zero anyway, but the guard keeps a query portable and makes the intent visible.

AI InterviewGet started

MySQL Functions Quiz: String, Date and Numeric Practice Test

Test yourself: 11 questions and three queries on MySQL string functions, ROUND and MOD, date formatting, INTERVAL, DATEDIFF, TIMESTAMPDIFF, IF and NULLIF.

  • Course: SQL study plan
  • Module: Functions
  • Kind: Checkpoint — cleared at 70%
  • Reading time: 25 min
  • Runtime: MySQL 8 syntax on SQLite 3.49

Checkpoint — Functions is the checkpoint that closes the Functions module: a graded quiz and whole-program exercises, passed at 70%.

Instructions

This checkpoint covers the whole module: string functions for joining, cleaning, cutting and padding text; numeric functions for rounding, integer division and remainders; date types, date parts and DATE_FORMAT; date arithmetic with INTERVAL, DATEDIFF, TIMESTAMPDIFF and LAST_DAY; and the conditional functions IF, IFNULL, NULLIF, GREATEST, LEAST and FIELD.

How it works. Eleven questions and three queries. You need 70% on the questions and every query accepted to clear the module. You can retake it as often as you like; your best score counts.

Before you start, make sure you can answer these from memory:

  • What does CONCAT return when one argument is NULL, and what does CONCAT_WS do instead?
  • What do SUBSTRING_INDEX(s, '@', 1) and SUBSTRING_INDEX(s, '@', -1) return for an email address?
  • What is -7 DIV 2, and how do FLOOR, CEIL, ROUND and TRUNCATE differ for negative numbers?
  • Why is 5 / 2 equal to 2 in PostgreSQL and 2.5 in MySQL?
  • Which weekday is 1 in DAYOFWEEK, and which is 0 in WEEKDAY?
  • What is '2024-01-31' + INTERVAL 1 MONTH?
  • What is the argument order of DATEDIFF, and of TIMESTAMPDIFF?
  • Why is a half-open range better than BETWEEN for a month of DATETIME values?
  • What do NULLIF(x, 0) and FIELD(status, …) return, and what are they used for?

The three queries are a contact-card formatter that cleans names and splits emails, an invoice report that computes due dates, days overdue and a rounded late fee, and a shipment board with labels, a safe on-time rate and a custom sort order.

Common questions

What does CONCAT return when one argument is NULL?

In MySQL CONCAT returns NULL if any argument is NULL, so a missing middle name erases the whole full name. CONCAT_WS(' ', first, middle, last) skips NULL arguments, and wrapping a column in COALESCE(col, '') is the other fix.

What is the argument order of DATEDIFF and TIMESTAMPDIFF?

DATEDIFF(later, earlier) puts the later date first and returns days. TIMESTAMPDIFF(unit, earlier, later) puts the unit first and then the start date, so the order of the dates is reversed. Mixing them up flips the sign of the answer.

Why use NULLIF in a division?

x / NULLIF(y, 0) divides by NULL instead of zero when y is 0, so the result is NULL rather than an error in PostgreSQL and SQL Server. MySQL returns NULL for division by zero anyway, but the guard keeps a query portable and makes the intent visible.

Exercises

Contact cards

contacts.full_name was typed in any case with stray spaces; the first word is the first name and the last word the surname.

idfull_nameemail
1' ada LOVELACE 'Ada@Example.COM
2'grace hopper'grace@NAVY.mil
3'Linus'linus@kernel.org

Return id, first_name and last_name — each with a capital first letter and the rest lower case — and domain, the lower-cased part of the email after @. A one-word name is both the first and the last name. Order by last_name, then id.

id | first_name | last_name | domain
2 | Grace | Hopper | navy.mil
3 | Linus | Linus | kernel.org
1 | Ada | Lovelace | example.com

Invoice ageing with late fees

Report on invoices as of 2024-06-30. An invoice is due terms_days after it was issued; an unpaid one (paid_on NULL) is measured up to 2024-06-30.

idissued_onterms_dayspaid_onamount
12024-05-0130NULL1200.00
22024-06-01152024-06-10500.00
32024-04-15452024-06-05250.00
42024-06-207NULL80.00

Return id, due_on, days_overdue — days from the due date to the payment date (or to 2024-06-30), never below 0 — and late_fee, 1% of the amount for every week or part-week overdue, rounded to 2 decimals. Order by days_overdue descending, then id.

id | due_on | days_overdue | late_fee
1 | 2024-05-31 | 30 | 60
3 | 2024-05-30 | 6 | 2.5
4 | 2024-06-27 | 3 | 0.8
2 | 2024-06-16 | 0 | 0

Carrier scorecard

Grade each delivery carrier on its on-time rate.

nametierdeliveredon_time
BlueDartexpress200192
Delhiverystandard500410
IndiaPosteconomy300210
Ekartstandard00
DTDCexpress150140

Return name, tier, rate — 100 * on_time / delivered rounded to 1 decimal, NULL when nothing was delivered — and grade: no data when the rate is NULL, excellent at 95 or above, ok at 80 or above, otherwise poor (grade on the rounded rate). Order by tier — express, standard, economy — then name.

name | tier | rate | grade
BlueDart | express | 96 | excellent
DTDC | express | 93.3 | ok
Delhivery | standard | 82 | ok
Ekart | standard | NULL | no data
IndiaPost | economy | 70 | poor

In this module: Functions

← Conditional functions — IF, IFNULL, NULLIF, GREATEST and FIELD · Aggregate functions — COUNT, SUM, AVG, MIN and MAX →