Using Null Quiz

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

Shrivell	Computing
Throd	Computing
Splint	Computing
Spiregrain	Other
Cutflower	Other
Deadyawn	Other

Table-B

Shrivell	Computing
Throd	Computing
Splint	Computing
Spiregrain	Computing
Cutflower	Computing
Deadyawn	Computing

Table-C

Shrivell	Computing
Throd	Computing
Splint	Computing

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