Difference between revisions of "Using Null Quiz"
Line 114: | Line 114: | ||
- set dept value of all teachers without department to 0 | - set dept value of all teachers without department to 0 | ||
− | {Query: < | + | {Query: |
+ | <syntaxhighlight lang='sql'>SELECT name, CASE WHEN phone = 2752 THEN 'two' WHEN phone = 2753 THEN 'three' WHEN phone = 2754 THEN 'four' END AS digit FROM teacher</syntaxhighlight> | ||
+ | |||
+ | shows following 'digit': | ||
|type="()"} | |type="()"} | ||
+ 'four' for Throd | + 'four' for Throd |
Revision as of 10:58, 12 July 2013
Using Null Quiz
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 | ||
... |
id | name |
---|---|
1 | Computing |
2 | Design |
3 | Engineering |
... |
<quiz shuffle=none display=simple> {Select the code which uses a JOIN correctly. |type="()"}
-
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 |type="()"}
-
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 |type="()"}
-
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:
|type="()"}
- 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': |type="()"} + '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
Shrivell | Computing |
Throd | Computing |
Splint | Computing |
Spiregrain | Other |
Cutflower | Other |
Deadyawn | Other |
Shrivell | Computing |
Throd | Computing |
Splint | Computing |
Spiregrain | Computing |
Cutflower | Computing |
Deadyawn | Computing |
Shrivell | Computing |
Throd | Computing |
Splint | Computing |
Spiregrain | Other |
Cutflower | Other |
Deadyawn | Other |
Shrivell | 1 |
Throd | 1 |
Splint | 1 |
Spiregrain | 0 |
Cutflower | 0 |
Deadyawn | 0 |
|type="()"} + Table-A - Table-B - Table-C - Table-D - Table-E
</quiz>