<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>import os
import pandas as pd
import findspark
os.environ['SPARK_HOME'] =  '/opt/spark'
findspark.init()

from pyspark.sql import SparkSession
sc = (SparkSession.builder.appName('app08')
      .config('spark.sql.warehouse.dir', 'hdfs://quickstart.cloudera:8020/user/hive/warehouse')
      .config('hive.metastore.uris', 'thrift://quickstart.cloudera:9083')
      .enableHiveSupport().getOrCreate())<jupyter_output><empty_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>teacher = sc.read.table('sqlzoo.teacher')
dept = sc.read.table('sqlzoo.dept')
from pyspark.sql.functions import *
teacher.filter(isnull(teacher['dept'])).select('name').toPandas()<jupyter_output><empty_output><jupyter_text>2.Note the INNER JOIN misses the teachers with no department and the departments with no teacher.<jupyter_code>(teacher.withColumnRenamed('name', 'teacher')
     .join(dept, teacher['dept']==dept['id'])
    .select('teacher', 'name')
     .toPandas())<jupyter_output><empty_output><jupyter_text>3.Use a different JOIN so that all teachers are listed.<jupyter_code>(teacher.withColumnRenamed('name', 'teacher')
    .join(dept, teacher['dept']==dept['id'], how='left')
    .select('teacher', 'name')
    .toPandas())<jupyter_output><empty_output><jupyter_text>4.Use a different JOIN so that all departments are listed.<jupyter_code>(teacher.withColumnRenamed('name', 'teacher')
    .join(dept, teacher['dept']==dept['id'], how='right')
    .select('teacher', 'name')
    .toPandas())<jupyter_output><empty_output><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>teacher.select('name', 'mobile').fillna({'mobile': '07986 444 2266'}).toPandas()<jupyter_output><empty_output><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>(teacher.withColumnRenamed('name', 'teacher')
    .join(dept, teacher['dept']==dept['id'], how='left')
    .select('teacher', 'name')
    .fillna({'name': 'None'})
    .toPandas())<jupyter_output><empty_output><jupyter_text>7.Use COUNT to show the number of teachers and the number of mobile phones.<jupyter_code>teacher.agg({'name': 'count', 'mobile': 'count'}).toPandas()<jupyter_output><empty_output><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>(teacher.withColumnRenamed('name', 'teacher')
 .join(dept, teacher['dept']==dept['id'], how='right')
 .groupBy('name')
 .agg({'teacher': 'count'})
 .toPandas())<jupyter_output><empty_output><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>(teacher.select('name', 'dept', when(teacher['dept'].isin([1, 2]), 'Sci')
                .otherwise('Art').alias('label'))
 .toPandas())<jupyter_output><empty_output><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>(teacher.select('name', 'dept', 
                when(teacher['dept'].isin([1, 2]), 'Sci')
                .when(teacher['dept'].isin([3, ]), 'Art')
                .otherwise('None').alias('label'))
 .toPandas())
sc.stop()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15909584872853727, 'fraction_numerical': 0.03303629645729189, 'mean_word_length': 4.930412371134021, 'pattern_counts': {'":': 0, '<': 45, '<?xml version=': 0, '>': 50, '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': '499386', 'license': 'MIT', 'n_tokens_mistral': 1716, 'n_tokens_neox': 1564, 'n_words': 390, 'path': 'Spark/08 Using Null.ipynb', 'repo_name': 'madlogos/sqlzoo', 'size': '4601'}