> For the complete documentation index, see [llms.txt](https://lei-d.gitbook.io/sql/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://lei-d.gitbook.io/sql/sqlzoo/teacher-department-data.md).

# 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.

```php
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.

```php
  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:

```php
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:

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

Using IF() function:

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