<jupyter_start><jupyter_text>Using Null- teacherid	| dept	| name	| phone	| mobile----|-------|-------|-------|-----101	| 1 | Shrivell	| 2753 | 07986 555 1234102	| 1	| Throd	    | 2754 | 07122 555 1920103	| 1	| Splint	| 2293	|104 |	| Spiregrain | 3287	|105 | 2	| Cutflower	 | 3212 | 07996 555 6574106 |	| Deadyawn | 3345 |	... |      |        |        |- deptid	| name----|----1	| Computing2	| Design3	| Engineering... | Teachers and DepartmentsThe school includes many departments. Most teachers work exclusively for a single department. Some teachers have no department.[Selecting NULL values](https://sqlzoo.net/wiki/Selecting_NULL_values).<jupyter_code># Prerequesites
import getpass
%load_ext sql
pwd = getpass.getpass()
# %sql mysql+pymysql://root:$pwd@localhost:3306/sqlzoo
%sql postgresql://postgres:$pwd@localhost/sqlzoo
%config SqlMagic.displaylimit = 20<jupyter_output>·········<jupyter_text>1. NULL, INNER JOIN, LEFT JOIN, RIGHT JOINList the teachers who have NULL for their department.> _Why we cannot use =_   > You might think that the phrase dept=NULL would work here but it doesn't - you can use the phrase dept IS NULL> > _That's not a proper explanation._  > No it's not, but you can read a better explanation at Wikipedia:NULL.<jupyter_code>%%sql
SELECT name
  FROM teacher
    WHERE dept IS NULL;<jupyter_output>* postgresql://postgres:***@localhost/sqlzoo
2 rows affected.<jupyter_text>2.Note the INNER JOIN misses the teachers with no department and the departments with no teacher.<jupyter_code>%%sql
SELECT teacher.name teacher, dept.name dept
 FROM teacher INNER JOIN dept
    ON (teacher.dept=dept.id);<jupyter_output>* postgresql://postgres:***@localhost/sqlzoo
4 rows affected.<jupyter_text>3.Use a different JOIN so that all teachers are listed.<jupyter_code>%%sql
SELECT teacher.name teacher, dept.name dept
  FROM teacher LEFT JOIN dept
    ON teacher.dept=dept.id;<jupyter_output>* postgresql://postgres:***@localhost/sqlzoo
6 rows affected.<jupyter_text>4.Use a different JOIN so that all departments are listed.<jupyter_code>%%sql
SELECT teacher.name teacher, dept.name dept
  FROM teacher RIGHT JOIN dept
    ON teacher.dept=dept.id;<jupyter_output>* postgresql://postgres:***@localhost/sqlzoo
5 rows affected.<jupyter_text>5. Using the [COALESCE](https://sqlzoo.net/wiki/COALESCE) functionUse 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'**<jupyter_code>%%sql
SELECT name, COALESCE(mobile, '07986 444 2266') mobile
FROM teacher;<jupyter_output>* postgresql://postgres:***@localhost/sqlzoo
6 rows affected.<jupyter_text>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.<jupyter_code>%%sql
SELECT teacher.name teacher, COALESCE(dept.name, 'None') dept
FROM teacher LEFT JOIN dept ON teacher.dept = dept.id;<jupyter_output>* postgresql://postgres:***@localhost/sqlzoo
6 rows affected.<jupyter_text>7.Use COUNT to show the number of teachers and the number of mobile phones.<jupyter_code>%%sql
SELECT COUNT(name) n_teacher, COUNT(mobile) n_mobile
FROM teacher;<jupyter_output>* postgresql://postgres:***@localhost/sqlzoo
1 rows affected.<jupyter_text>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.<jupyter_code>%%sql
SELECT dept.name dept, COUNT(teacher.id) n_teacher
FROM teacher RIGHT JOIN dept ON teacher.dept=dept.id
GROUP BY dept.name;<jupyter_output>* postgresql://postgres:***@localhost/sqlzoo
3 rows affected.<jupyter_text>9. Using [CASE](https://sqlzoo.net/wiki/CASE)Use CASE to show the **name** of each teacher followed by 'Sci' if the teacher is in **dept** 1 or 2 and 'Art' otherwise.<jupyter_code>%%sql
SELECT name, CASE WHEN dept IN (1, 2) THEN 'Sci'
                  ELSE 'Art'
             END flg
FROM teacher;<jupyter_output>* postgresql://postgres:***@localhost/sqlzoo
6 rows affected.<jupyter_text>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.<jupyter_code>%%sql
SELECT name, CASE WHEN dept IN (1, 2) THEN 'Sci'
                  WHEN dept IN (3) THEN 'Art'
                  ELSE 'None'
             END flg
FROM teacher;<jupyter_output>* postgresql://postgres:***@localhost/sqlzoo
6 rows affected.
{'fraction_non_alphanumeric': 0.11478338543992854, 'fraction_numerical': 0.03528360875390799, 'mean_word_length': 4.468864468864469, 'pattern_counts': {'":': 0, '<': 34, '<?xml version=': 0, '>': 39, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '92842', 'license': 'MIT', 'n_tokens_mistral': 1639, 'n_tokens_neox': 1468, 'n_words': 484, 'path': 'PostgreSQL/08 Using Null.ipynb', 'repo_name': 'madlogos/sqlzoo', 'size': '4478'}