-
Notifications
You must be signed in to change notification settings - Fork 6
Expand file tree
/
Copy path15-Using_NULL.sql
More file actions
92 lines (68 loc) · 2.25 KB
/
Copy path15-Using_NULL.sql
File metadata and controls
92 lines (68 loc) · 2.25 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
/*
1. List the teachers who have NULL for their department.
Why we cannot use =
You might think that the phrase dept=NULL would work here but it doesn't - you can use the phrase dept IS NULL
That's not a proper explanation.
*/
SELECT name FROM teacher WHERE dept IS NULL;
/*
2. Note the INNER JOIN misses the teachers with no department and the departments with no teacher.
*/
SELECT teacher.name, dept.name
FROM teacher INNER JOIN dept
ON (teacher.dept=dept.id);
/*
3. Use a different JOIN so that all teachers are listed.
*/
SELECT teacher.name, dept.name
FROM teacher LEFT JOIN dept
ON (teacher.dept=dept.id);
/*
4. Use a different JOIN so that all departments are listed.
*/
SELECT teacher.name, dept.name
FROM teacher RIGHT JOIN dept
ON (teacher.dept=dept.id);
/*
5. Use COALESCE to print the mobile number. Use the number '07986 444 2266' if there is no number given. Show teacher name and mobile number or '07986 444 2266'
*/
SELECT name, COALESCE(mobile, '07986 444 2266')
FROM teacher;
/*
6. Use the COALESCE function and a LEFT JOIN to print the teacher name and department name. Use the string 'None' where there is no department.
*/
SELECT teacher.name, COALESCE(dept.name, 'None')
FROM
teacher LEFT JOIN dept ON teacher.dept=dept.id;;
/*
7. Use COUNT to show the number of teachers and the number of mobile phones.
*/
SELECT COUNT(teacher.name), COUNT(teacher.mobile)
FROM teacher;
/*
8. Use COUNT and GROUP BY dept.name to show each department and the number of staff. Use a RIGHT JOIN to ensure that the Engineering department is listed.
*/
SELECT dept.name, COUNT(teacher.name)
FROM teacher RIGHT JOIN dept ON teacher.dept=dept.id
GROUP BY dept.name;
/*
9. Use CASE to show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2 and 'Art' otherwise.
*/
SELECT
teacher.name,
CASE
WHEN teacher.dept IN (1,2) THEN 'Sci'
ELSE 'Art'
END
FROM teacher;
/*
10. Use CASE to show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2, show 'Art' if the teacher's dept is 3 and 'None' otherwise.
*/
SELECT
teacher.name,
CASE
WHEN teacher.dept IN (1,2) THEN 'Sci'
WHEN teacher.dept = 3 THEN 'Art'
ELSE 'None'
END
FROM teacher;