# Teacher - Dept Tables

## teacher

<table><thead><tr><th width="88">id</th><th width="72">dept</th><th width="125">name</th><th width="95" data-type="number">phone</th><th>mobile</th></tr></thead><tbody><tr><td>101</td><td>1</td><td>Shrivell</td><td>2753</td><td>07986 555 1234</td></tr><tr><td>102</td><td>1</td><td>Throd</td><td>2754</td><td>07122 555 1920</td></tr><tr><td>103</td><td>1</td><td>Splint</td><td>2293</td><td></td></tr><tr><td>104</td><td></td><td>Spiregrain</td><td>3287</td><td></td></tr><tr><td>105</td><td>2</td><td>Cutflower</td><td>3212</td><td>07996 555 6574</td></tr><tr><td>106</td><td></td><td>Deadyawn</td><td>3345</td><td></td></tr><tr><td>...</td><td></td><td></td><td>null</td><td></td></tr></tbody></table>

## dept

<table><thead><tr><th width="163">d</th><th>name</th></tr></thead><tbody><tr><td>1</td><td>Computing</td></tr><tr><td>2</td><td>Design</td></tr><tr><td>3</td><td>Engineering</td></tr><tr><td>...</td><td></td></tr></tbody></table>

### Teachers and Departments

The school includes many departments. Most teachers work exclusively for a single department. Some teachers have no department.

## NULL, INNER JOIN, LEFT JOIN, RIGHT JOIN

1. List the teachers who have NULL for their department.

```sql
SELECT name FROM teacher WHERE dept IS NULL
```

2. Note the INNER JOIN misses the teachers with no department and the departments with no teacher.

```sql
SELECT teacher.name, dept.name 
FROM teacher 
INNER JOIN dept ON (teacher.dept=dept.id)
```

3. Use a different JOIN so that all teachers are listed.&#x20;

```sql
SELECT teacher.name, dept.name 
FROM teacher 
LEFT JOIN dept ON (teacher.dept=dept.id)
```

4. Use a different JOIN so that all departments are listed.

```sql
SELECT teacher.name, dept.name 
FROM teacher 
RIGHT JOIN dept ON (teacher.dept=dept.id)
```

5. Use **COALESCE** to print the mobile number. Use the number '07986 444 2266' if there is no number given. Show teacher name and mobile number or '07986 444 2266'

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

6. Use the **COALESCE** function and a LEFT JOIN to print the teacher name and department name. Use the string 'None' where there is no department.

```sql
SELECT teacher.name, COALESCE(dept.name, 'None') AS dept
FROM teacher 
LEFT JOIN dept ON (teacher.dept=dept.id)
```

7. Use COUNT to show the number of teachers and the number of mobile phones.

```sql
SELECT COUNT(name) AS num_teacher, COUNT(mobile) AS num_mobile
FROM teacher 
```

8. Use COUNT and GROUP BY dept.name to show each department and the number of staff. Use a RIGHT JOIN to ensure that the Engineering department is listed.

```sql
SELECT  d.name, COUNT(t.name) AS num_teacher
FROM teacher t
RIGHT JOIN dept d 
ON t.dept = d.id
GROUP BY d.name
```

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

```sql
SELECT
    name, 
    CASE
        WHEN (dept = 1 OR dept = 2) THEN  'Sci'
        ELSE 'Art'
    END AS new_name
FROM teacher 
```

10. Use **CASE** to show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2, show 'Art' if the teacher's dept is 3 and 'None' otherwise.

```sql
SELECT
    name, 
    CASE
        WHEN (dept = 1 OR dept = 2) THEN  'Sci'
        WHEN dept = 3 THEN 'Art'
        ELSE 'None'
    END
FROM teacher
```