Difference between revisions of "Using Null Quiz"

From SQLZOO
Jump to: navigation, search
Line 1: Line 1:
 
Using Null Quiz
 
Using Null Quiz
  
−
<div class='db_ref' style='background:none'>
+
<div class='ref_section' style='background:none'>
−
<table class='zoo'>
+
<table class='zoo db_ref'>
 
<caption>teacher</caption>
 
<caption>teacher</caption>
 
<tr>
 
<tr>

Revision as of 02:06, 19 July 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. Which of the following uses of JOIN is correct?

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. Which of following uses of JOIN shows 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

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

Variants
Actions
Reference
Toolbox
Google AdSense