Using Null Quiz

From SQLZOO
Jump to: navigation, search

Test your understanding of the NULL value

teacher
id dept name phone
101 1 Shrivell 2753
102 1 Throd 2754
103 1 Splint
104 Spiregrain
105 2 Cutflower 3212
106 Deadyawn
dept
id name
1 Computing
2 Design
3 Engineering
Select the code which uses a JOIN correctly.
 SELECT teacher.name, dept.name FROM teacher JOIN dept ON (dept = id)
 SELECT teacher.name, dept.name FROM teacher, dept INNER JOIN ON (teacher.dept = dept.id)
 SELECT teacher.name, dept.name FROM teacher, dept JOIN WHERE(teacher.dept = dept.id)
 SELECT teacher.name, dept.name FROM teacher OUTER JOIN dept ON dept.id
 SELECT teacher.name, dept.name FROM teacher LEFT OUTER JOIN dept ON (teacher.dept > dept.id)
Select the correct statement that shows the name of department which employs Cutflower -
 SELECT dept.name FROM teacher JOIN dept ON (dept.id = (SELECT dept FROM teacher WHERE name = 'Cutflower'))
 SELECT dept.name FROM teacher JOIN dept ON (dept.id = teacher.dept) WHERE dept.id = (SELECT dept FROM teacher HAVING name = 'Cutflower')
 SELECT dept.name FROM teacher JOIN dept ON (dept.id = teacher.dept) WHERE teacher.name = 'Cutflower'
 SELECT dept.name FROM teacher JOIN dept WHERE dept.id = (SELECT dept FROM teacher WHERE name = 'Cutflower')
 SELECT name FROM teacher JOIN dept ON (id = dept) WHERE id = (SELECT dept FROM teacher WHERE name = 'Cutflower')
Select out of following the code which uses a JOIN to show a list of all the departments and number of employed teachers
 SELECT dept.name, COUNT(*) FROM teacher LEFT JOIN dept ON dept.id = teacher.dept
 SELECT dept.name, COUNT(teacher.name) FROM teacher, dept JOIN ON dept.id = teacher.dept GROUP BY dept.name
 SELECT dept.name, COUNT(teacher.name) FROM teacher JOIN dept ON dept.id = teacher.dept GROUP BY dept.name
 SELECT dept.name, COUNT(teacher.name) FROM teacher LEFT OUTER JOIN dept ON dept.id = teacher.dept GROUP BY dept.name
 SELECT dept.name, COUNT(teacher.name) FROM teacher RIGHT JOIN dept ON dept.id = teacher.dept GROUP BY dept.name
Using SELECT name, dept, COALESCE(dept, 0) AS result FROM teacher on teacher table will:
display 0 in result column for all teachers
display 0 in result column for all teachers without department
do nothing - the statement is incorrect
set dept value of all teachers to 0
set dept value of all teachers without department to 0
Query:
SELECT name,
       CASE WHEN phone = 2752 THEN 'two'
            WHEN phone = 2753 THEN 'three'
            WHEN phone = 2754 THEN 'four'
            END AS digit
  FROM teacher

shows following 'digit':

'four' for Throd
NULL for all teachers
NULL for Shrivell
'two' for Cutflower
'two' for Deadyawn
Select the result that would be obtained from the following code:
 SELECT name, 
      CASE 
       WHEN dept 
        IN (1) 
        THEN 'Computing' 
       ELSE 'Other' 
      END 
  FROM teacher
Table-A
ShrivellComputing
ThrodComputing
SplintComputing
SpiregrainOther
CutflowerOther
DeadyawnOther
Table-B
ShrivellComputing
ThrodComputing
SplintComputing
SpiregrainComputing
CutflowerComputing
DeadyawnComputing
Table-C
ShrivellComputing
ThrodComputing
SplintComputing
Table-D
Spiregrain Other
Cutflower Other
Deadyawn Other
Table-E
Shrivell 1
Throd 1
Splint 1
Spiregrain 0
Cutflower 0
Deadyawn 0