Teacher Department Data

5. Show teacher name and mobile number or '07986 444 2266'

Use COALESCE to print the mobile number. Use the number '07986 444 2266' if there is no number given.

SELECT name, COALESCE(mobile, '07986 444 2266') AS mobile
FROM teacher;

COALESCE takes any number of arguments and returns the first value that is not null.

  COALESCE(x,y,z) = x if x is not NULL
  COALESCE(x,y,z) = y if x is NULL and y is not NULL
  COALESCE(x,y,z) = z if x and y are NULL but z is not NULL
  COALESCE(x,y,z) = NULL if x and y and z are all NULL

We can also use CASE WHEN:

SELECT name, (CASE WHEN mobile IS NOT NULL THEN mobile ELSE '07986 444 2266' END)AS mobile
FROM teacher;

9. show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2 and 'Art' otherwise

Using CASE operator:

SELECT name,
    (CASE
    WHEN dept IN (1, 2) THEN 'Sci'
    ELSE 'Art'
    END) AS type
FROM teacher;

Using IF() function:

SELECT name,
    IF(dept IN (1, 2), 'Sci', 'Art') AS type
FROM teacher;