Difference between revisions of "Using Null Quiz"

From SQLZOO
Jump to: navigation, search
 
(10 intermediate revisions by 2 users not shown)
Line 1: Line 1:
Using Null Quiz
+
Test your understanding of the NULL value
 
+
 
<div class='ref_section' style='background:none'>
 
<div class='ref_section' style='background:none'>
 
<table class='zoo db_ref'>
 
<table class='zoo db_ref'>
Line 9: Line 8:
 
<th>name</th>  
 
<th>name</th>  
 
<th>phone</th>
 
<th>phone</th>
<th>mobile</th>
 
 
</tr>
 
</tr>
 
<tr>
 
<tr>
Line 16: Line 14:
 
<td>Shrivell</td>
 
<td>Shrivell</td>
 
<td align='right'>2753</td>
 
<td align='right'>2753</td>
<td>07986 555 1234</td>
 
 
</tr>
 
</tr>
 
<tr>
 
<tr>
Line 23: Line 20:
 
<td>Throd</td>
 
<td>Throd</td>
 
<td align='right'>2754</td>
 
<td align='right'>2754</td>
<td>07122 555 1920</td>
 
 
</tr>
 
</tr>
 
<tr><td align='right'>103</td>
 
<tr><td align='right'>103</td>
 
<td align='right'>1</td>
 
<td align='right'>1</td>
 
<td>Splint</td>
 
<td>Splint</td>
<td align='right'>2293</td>
+
<td align='right'></td>
<td></td>
+
 
</tr>
 
</tr>
 
<tr><td align='right'>104</td>
 
<tr><td align='right'>104</td>
 
<td align='right'></td>
 
<td align='right'></td>
 
<td>Spiregrain</td>
 
<td>Spiregrain</td>
<td align='right'>3287</td>
+
<td align='right'></td>
<td></td>
+
 
</tr>
 
</tr>
 
<tr>
 
<tr>
 
<td align='right'>105</td>
 
<td align='right'>105</td>
 
<td align='right'>2</td>
 
<td align='right'>2</td>
<td>Cutflower</td>
+
<td>Cutflower </td>
 
<td align='right'>3212</td>
 
<td align='right'>3212</td>
<td>07996 555 6574</td>
 
 
</tr>
 
</tr>
 
<tr>
 
<tr>
Line 48: Line 41:
 
<td align='right'></td>
 
<td align='right'></td>
 
<td>Deadyawn</td>
 
<td>Deadyawn</td>
<td align='right'>3345</td>
+
<td align='right'></td>
<td></td>
+
</tr>
+
<tr>
+
<td colspan='5'>...</td>
+
 
</tr>
 
</tr>
 +
<tr><td colspan='4'></td></tr>
 
</table>
 
</table>
 
<table class='zoo db_ref'>
 
<table class='zoo db_ref'>
Line 73: Line 63:
 
<td>Engineering</td>
 
<td>Engineering</td>
 
</tr>
 
</tr>
<tr>
+
<tr><td colspan='2'></td></tr>
<td colspan='2'>...</td>
+
</tr>
+
 
</table>
 
</table>
 
</div>
 
</div>
  
<div class='extra_space' style='width:1em; height:16em;'></div>
+
<div class=quiz>
 +
<div class=q>Select the code which uses a JOIN correctly.
 +
<syntaxhighlight class=d lang='sql'> SELECT teacher.name, dept.name FROM teacher JOIN dept ON (dept = id) </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'> SELECT teacher.name, dept.name FROM teacher, dept INNER JOIN ON (teacher.dept = dept.id) </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'> SELECT teacher.name, dept.name FROM teacher, dept JOIN WHERE(teacher.dept = dept.id) </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'> SELECT teacher.name, dept.name FROM teacher OUTER JOIN dept ON dept.id </syntaxhighlight>
 +
<syntaxhighlight class='d y' lang='sql'> SELECT teacher.name, dept.name FROM teacher LEFT OUTER JOIN dept ON (teacher.dept > dept.id) </syntaxhighlight>
 +
</div>
  
<quiz shuffle=none display=simple>
+
<div class=q>Select the correct statement that shows the name of department which employs Cutflower
{Select the code which uses a JOIN correctly.
+
- <syntaxhighlight class=d lang='sql'> SELECT dept.name FROM teacher JOIN dept ON (dept.id = (SELECT dept FROM teacher WHERE name = 'Cutflower')) </syntaxhighlight>
|type="()"}
+
<syntaxhighlight class=d lang='sql'> SELECT dept.name FROM teacher JOIN dept ON (dept.id = teacher.dept) WHERE dept.id = (SELECT dept FROM teacher HAVING name = 'Cutflower') </syntaxhighlight>
- SELECT teacher.name, dept.name FROM teacher JOIN dept ON (dept = id)
+
<syntaxhighlight class='d y' lang='sql'> SELECT dept.name FROM teacher JOIN dept ON (dept.id = teacher.dept) WHERE teacher.name = 'Cutflower' </syntaxhighlight>
- SELECT teacher.name, dept.name FROM teacher, dept INNER JOIN ON (teacher.dept = dept.id)
+
<syntaxhighlight class=d lang='sql'> SELECT dept.name FROM teacher JOIN dept WHERE dept.id = (SELECT dept FROM teacher WHERE name = 'Cutflower') </syntaxhighlight>
- SELECT teacher.name, dept.name FROM teacher, dept JOIN WHERE(teacher.dept = dept.id)
+
<syntaxhighlight class=d lang='sql'> SELECT name FROM teacher JOIN dept ON (id = dept) WHERE id = (SELECT dept FROM teacher WHERE name = 'Cutflower') </syntaxhighlight>
- SELECT teacher.name, dept.name FROM teacher OUTER JOIN dept ON dept.id
+
</div>
+ 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
+
<div class=q>Select out of following the code which uses a JOIN to show a list of all the departments and number of employed teachers
|type="()"}
+
<syntaxhighlight class=d lang='sql'> SELECT dept.name, COUNT(*) FROM teacher LEFT JOIN dept ON dept.id = teacher.dept </syntaxhighlight>
- SELECT dept.name FROM teacher JOIN dept ON (dept.id = (SELECT dept FROM teacher WHERE name = 'Cutflower'))
+
<syntaxhighlight class=d lang='sql'> SELECT dept.name, COUNT(teacher.name) FROM teacher, dept JOIN ON dept.id = teacher.dept GROUP BY dept.name </syntaxhighlight>
- SELECT dept.name FROM teacher JOIN dept ON (dept.id = teacher.dept) WHERE dept.id = (SELECT dept FROM teacher HAVING name = 'Cutflower')
+
<syntaxhighlight class=d lang='sql'> SELECT dept.name, COUNT(teacher.name) FROM teacher JOIN dept ON dept.id = teacher.dept GROUP BY dept.name </syntaxhighlight>
+ SELECT dept.name FROM teacher JOIN dept ON (dept.id = teacher.dept) WHERE teacher.name = 'Cutflower'
+
<syntaxhighlight class=d lang='sql'> SELECT dept.name, COUNT(teacher.name) FROM teacher LEFT OUTER JOIN dept ON dept.id = teacher.dept GROUP BY dept.name </syntaxhighlight>
- SELECT dept.name FROM teacher JOIN dept WHERE dept.id = (SELECT dept FROM teacher WHERE name = 'Cutflower')
+
<syntaxhighlight class='d y' lang='sql'> SELECT dept.name, COUNT(teacher.name) FROM teacher RIGHT JOIN dept ON dept.id = teacher.dept GROUP BY dept.name </syntaxhighlight>
- SELECT name FROM teacher JOIN dept ON (id = dept) WHERE id = (SELECT dept FROM teacher WHERE name = 'Cutflower')
+
</div>
  
{Select out of following the code which uses a JOIN to show a list of all the departments and number of employed teachers
+
<div class=q>Using <code>SELECT name, dept, COALESCE(dept, 0) AS result FROM teacher</code> on <code>teacher</code> table will:
|type="()"}
+
<div class=d>display 0 in result column for all teachers</div>
- SELECT dept.name, COUNT(*) FROM teacher LEFT JOIN dept ON dept.id = teacher.dept
+
<div class='d y'>display 0 in result column for all teachers without department</div>
- SELECT dept.name, COUNT(teacher.name) FROM teacher, dept JOIN ON dept.id = teacher.dept GROUP BY dept.name
+
<div class=d>do nothing - the statement is incorrect</div>
- SELECT dept.name, COUNT(teacher.name) FROM teacher JOIN dept ON dept.id = teacher.dept GROUP BY dept.name
+
<div class=d>set dept value of all teachers to 0</div>
- SELECT dept.name, COUNT(teacher.name) FROM teacher LEFT OUTER JOIN dept ON dept.id = teacher.dept GROUP BY dept.name
+
<div class=d>set dept value of all teachers without department to 0</div>
+ SELECT dept.name, COUNT(teacher.name) FROM teacher RIGHT JOIN dept ON dept.id = teacher.dept GROUP BY dept.name
+
</div>
  
{Using <code>SELECT name, dept, COALESCE(dept, 0) AS result FROM teacher</code> on <code>teacher</code> table will:
+
<div class=q>Query:
|type="()"}
+
<syntaxhighlight lang='sql'>
- display 0 in result column for all teachers
+
SELECT name,
+ display 0 in result column for all teachers without department
+
      CASE WHEN phone = 2752 THEN 'two'
- do nothing - the statement is incorrect
+
            WHEN phone = 2753 THEN 'three'
- set dept value of all teachers to 0
+
            WHEN phone = 2754 THEN 'four'
- set dept value of all teachers without department to 0
+
            END AS digit
 
+
  FROM teacher</syntaxhighlight>
{Query: <code>SELECT name, CASE WHEN phone = 2752 THEN 'two' WHEN phone = 2753 THEN 'three' WHEN phone = 2754 THEN 'four' END AS digit FROM teacher</code> shows following 'digit':
+
|type="()"}
+
shows following 'digit':
+ 'four' for Throd
+
<div class='d y'>'four' for Throd</div>
- NULL for all teachers
+
<div class=d>NULL for all teachers</div>
- NULL for Shrivell
+
<div class=d>NULL for Shrivell</div>
- 'two' for Cutflower
+
<div class=d>'two' for Cutflower</div>
- 'two' for Deadyawn
+
<div class=d>'two' for Deadyawn</div>
 +
</div>
  
{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
+
<div class=q>Select the result that would be obtained from the following code:  
<table style='float:left'><caption>Table-A</caption><tr><td>Shrivell</td><td>Computing</td></tr><tr><td>Throd</td><td>Computing</td></tr><tr><td>Splint</td><td>Computing</td></tr><tr><td>Spiregrain</td><td>Other</td></tr><tr><td>Cutflower</td><td>Other</td></tr><tr><td>Deadyawn</td><td>Other</td></tr></table>
+
<syntaxhighlight lang='sql'>
<table style='float:left'><caption>Table-B</caption><tr><td>Shrivell</td><td>Computing</td></tr><tr><td>Throd</td><td>Computing</td></tr><tr><td>Splint</td><td>Computing</td></tr><tr><td>Spiregrain</td><td>Computing</td></tr><tr><td>Cutflower</td><td>Computing</td></tr><tr><td>Deadyawn</td><td>Computing</td></tr></table>
+
SELECT name,  
<table style='float:left'><caption>Table-C</caption><tr><td>Shrivell</td><td>Computing</td></tr><tr><td>Throd</td><td>Computing</td></tr><tr><td>Splint</td><td>Computing</td></tr></table>
+
      CASE  
<table style='float:left'><caption>Table-D</caption>
+
      WHEN dept  
 +
        IN (1)  
 +
        THEN 'Computing'  
 +
      ELSE 'Other'  
 +
      END  
 +
  FROM teacher
 +
</syntaxhighlight>
 +
<table class='d y'><caption>Table-A</caption><tr><td>Shrivell</td><td>Computing</td></tr><tr><td>Throd</td><td>Computing</td></tr><tr><td>Splint</td><td>Computing</td></tr><tr><td>Spiregrain</td><td>Other</td></tr><tr><td>Cutflower</td><td>Other</td></tr><tr><td>Deadyawn</td><td>Other</td></tr></table>
 +
<table class=d><caption>Table-B</caption><tr><td>Shrivell</td><td>Computing</td></tr><tr><td>Throd</td><td>Computing</td></tr><tr><td>Splint</td><td>Computing</td></tr><tr><td>Spiregrain</td><td>Computing</td></tr><tr><td>Cutflower</td><td>Computing</td></tr><tr><td>Deadyawn</td><td>Computing</td></tr></table>
 +
<table class=d><caption>Table-C</caption><tr><td>Shrivell</td><td>Computing</td></tr><tr><td>Throd</td><td>Computing</td></tr><tr><td>Splint</td><td>Computing</td></tr></table>
 +
<table class=d><caption>Table-D</caption>
 
   <tr>
 
   <tr>
 
     <td>Spiregrain</td>
 
     <td>Spiregrain</td>
Line 140: Line 145:
 
   </tr>
 
   </tr>
 
</table>
 
</table>
<table style='float:left'><caption>Table-E</caption><tr><td>Shrivell</td>
+
<table class=d><caption>Table-E</caption><tr><td>Shrivell</td>
 
<td>1</td></tr><tr><td>Throd</td>
 
<td>1</td></tr><tr><td>Throd</td>
 
<td>1</td></tr><tr><td>Splint</td>
 
<td>1</td></tr><tr><td>Splint</td>
Line 147: Line 152:
 
<td>0</td></tr><tr><td>Deadyawn</td>
 
<td>0</td></tr><tr><td>Deadyawn</td>
 
<td>0</td></tr></table>
 
<td>0</td></tr></table>
|type="()"}
+
</div>
+ Table-A
+
</div>
- Table-B
+
- Table-C
+
- Table-D
+
- Table-E
+
 
+
</quiz>
+
  
 
[[Category:Quizzes]]
 
[[Category:Quizzes]]

Latest revision as of 12:35, 23 April 2015

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