Difference between revisions of "Using Null Quiz"

From SQLZOO
Jump to: navigation, search
 
(3 intermediate revisions by one user not shown)
Line 84: Line 84:
 
{Select the code which uses a JOIN correctly.
 
{Select the code which uses a JOIN correctly.
 
|type="()"}
 
|type="()"}
−
-A. SELECT teacher.name, dept.name FROM teacher JOIN dept ON (dept = id)
+
- SELECT teacher.name, dept.name FROM teacher JOIN dept ON (dept = id)
−
-B. SELECT teacher.name, dept.name FROM teacher, dept INNER JOIN ON (teacher.dept = dept.id)
+
- SELECT teacher.name, dept.name FROM teacher, dept INNER JOIN ON (teacher.dept = dept.id)
−
-C. SELECT teacher.name, dept.name FROM teacher, dept JOIN WHERE(teacher.dept = dept.id)
+
- SELECT teacher.name, dept.name FROM teacher, dept JOIN WHERE(teacher.dept = dept.id)
−
-D. SELECT teacher.name, dept.name FROM teacher OUTER JOIN dept ON dept.id
+
- SELECT teacher.name, dept.name FROM teacher OUTER JOIN dept ON dept.id
−
+E. SELECT teacher.name, dept.name FROM teacher LEFT OUTER JOIN dept ON (teacher.dept > 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 the correct statement that shows the name of department which employs Cutflower
 
|type="()"}
 
|type="()"}
−
-A. 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 = (SELECT dept FROM teacher WHERE name = 'Cutflower'))
−
-B. 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 dept.id = (SELECT dept FROM teacher HAVING name = 'Cutflower')
−
+C. SELECT dept.name FROM teacher JOIN dept ON (dept.id = teacher.dept) WHERE teacher.name = 'Cutflower'
+
+ SELECT dept.name FROM teacher JOIN dept ON (dept.id = teacher.dept) WHERE teacher.name = 'Cutflower'
−
-D. SELECT dept.name FROM teacher JOIN dept WHERE dept.id = (SELECT dept FROM teacher WHERE name = 'Cutflower')
+
- SELECT dept.name FROM teacher JOIN dept WHERE dept.id = (SELECT dept FROM teacher WHERE name = 'Cutflower')
−
-E. SELECT name FROM teacher JOIN dept ON (id = dept) WHERE 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 out of following the code which uses a JOIN to show a list of all the departments and number of employed teachers
 
|type="()"}
 
|type="()"}
−
-A. SELECT dept.name, COUNT(*) FROM teacher LEFT JOIN dept ON dept.id = teacher.dept
+
- SELECT dept.name, COUNT(*) FROM teacher LEFT JOIN dept ON dept.id = teacher.dept
−
-B. 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, dept JOIN ON dept.id = teacher.dept GROUP BY dept.name
−
-C. 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 JOIN dept ON dept.id = teacher.dept GROUP BY dept.name
−
-D. 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 LEFT OUTER JOIN dept ON dept.id = teacher.dept GROUP BY dept.name
−
+E. SELECT dept.name, COUNT(teacher.name) FROM teacher RIGHT 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 <code>SELECT name, dept, COALESCE(dept, 0) AS result FROM teacher</code> on <code>teacher</code> table will:
 
{Using <code>SELECT name, dept, COALESCE(dept, 0) AS result FROM teacher</code> on <code>teacher</code> table will:
 
|type="()"}
 
|type="()"}
−
-A. display 0 in result column for all teachers
+
- display 0 in result column for all teachers
−
+B. display 0 in result column for all teachers without department
+
+ display 0 in result column for all teachers without department
−
-C. do nothing - the statement is incorrect
+
- do nothing - the statement is incorrect
−
-D. set dept value of all teachers to 0
+
- set dept value of all teachers to 0
−
-E. set dept value of all teachers without department to 0
+
- set dept value of all teachers without department to 0
  
 
{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':
 
{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="()"}
 
|type="()"}
−
+A. 'four' for Throd
+
+ 'four' for Throd
−
-B. NULL for all teachers
+
- NULL for all teachers
−
-C. NULL for Shrivell
+
- NULL for Shrivell
−
-D. 'two' for Cutflower
+
- 'two' for Cutflower
−
-E. 'two' for Deadyawn
+
- '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 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>
 +
<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>
 +
<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>
 +
<table style='float:left'><caption>Table-D</caption>
 +
  <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 style='float:left'><caption>Table-E</caption><tr><td>Shrivell</td>
 +
<td>1</td></tr><tr><td>Throd</td>
 +
<td>1</td></tr><tr><td>Splint</td>
 +
<td>1</td></tr><tr><td>Spiregrain</td>
 +
<td>0</td></tr><tr><td>Cutflower</td>
 +
<td>0</td></tr><tr><td>Deadyawn</td>
 +
<td>0</td></tr></table>
 +
|type="()"}
 +
+ Table-A
 +
- Table-B
 +
- Table-C
 +
- Table-D
 +
- Table-E
  
 
</quiz>
 
</quiz>
  
 
[[Category:Quizzes]]
 
[[Category:Quizzes]]

Latest revision as of 13:43, 15 August 2012

Using Null Quiz

teacher
id dept name phone mobile
101 1 Shrivell 2753 07986 555 1234
102 1 Throd 2754 07122 555 1920
103 1 Splint 2293
104 Spiregrain 3287
105 2 Cutflower 3212 07996 555 6574
106 Deadyawn 3345
...
dept
id name
1 Computing
2 Design
3 Engineering
...

1. 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)

2. 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')

3. 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

4. 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

5. 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

6. 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
Table-A
Table-B
Table-C
Table-D
Table-E

Your score is 0 / 0
Retrieved from "http://sqlzoo.net/w/index.php?title=Using_Null_Quiz&oldid=3679"
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense