forked from DaveOnData/SQLForExcelUsers
-
Notifications
You must be signed in to change notification settings - Fork 0
/
SQLForExcelUsersPart15.sql
96 lines (77 loc) · 2.19 KB
/
SQLForExcelUsersPart15.sql
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
91
92
93
94
95
96
--
-- Use a subset of 6 employees from the DimEmployee table.
--
SELECT E.EmployeeKey
,E.FirstName
,E.LastName
FROM DimEmployee E
WHERE E.EmployeeKey = 271 OR
E.EmployeeKey = 274 OR
E.EmployeeKey = 275 OR
E.EmployeeKey = 277 OR
E.EmployeeKey = 282 OR
E.EmployeeKey = 283
--
-- The SQL IN keyword is a shortcut for using a lot of
-- ORs in your WHERE clauses.
--
SELECT E.EmployeeKey
,E.FirstName
,E.LastName
FROM DimEmployee E
WHERE E.EmployeeKey IN (271, 274, 275, 277, 282, 283)
--
-- Take a peek at the FactSalesQuota table.
--
SELECT SQ.EmployeeKey
,SQ.SalesAmountQuota
FROM FactSalesQuota SQ
ORDER BY SQ.EmployeeKey, SQ.SalesQuotaKey
--
-- The following isn't legit SQL, here to talk about concepts.
--
SELECT E.EmployeeKey
,E.FirstName
,E.LastName
,SQ.SalesAmountQuota
FROM DimEmployee E
LEFT OUTER JOIN FactSalesQuota SQ
WHERE E.EmployeeKey IN (271, 274, 275, 277, 282, 283)
--
-- The following is a legit LEFT OUTER JOIN as it has
-- an ON clause (i.e., a JOIN condition) defined.
--
SELECT E.EmployeeKey
,E.FirstName
,E.LastName
,SQ.SalesAmountQuota
FROM DimEmployee E
LEFT OUTER JOIN FactSalesQuota SQ ON (E.EmployeeKey = SQ.EmployeeKey)
WHERE E.EmployeeKey IN (271, 274, 275, 277, 282, 283)
ORDER BY E.EmployeeKey
--
-- Beware the NULL of SQL! NULL denote the absence of value.
-- Use the IS NULL syntax to get employees that do not have
-- sales quotas.
--
SELECT E.EmployeeKey
,E.FirstName
,E.LastName
,SQ.SalesAmountQuota
FROM DimEmployee E
LEFT OUTER JOIN FactSalesQuota SQ ON (E.EmployeeKey = SQ.EmployeeKey)
WHERE E.EmployeeKey IN (271, 274, 275, 277, 282, 283) AND
SQ.SalesAmountQuota IS NULL
--
-- Use the IS NOT NULL syntax to get employees that do have
-- sales quotas.
--
SELECT E.EmployeeKey
,E.FirstName
,E.LastName
,SQ.SalesAmountQuota
FROM DimEmployee E
LEFT OUTER JOIN FactSalesQuota SQ ON (E.EmployeeKey = SQ.EmployeeKey)
WHERE E.EmployeeKey IN (271, 274, 275, 277, 282, 283) AND
SQ.SalesAmountQuota IS NOT NULL
ORDER BY E.EmployeeKey