-
Notifications
You must be signed in to change notification settings - Fork 0
/
NOTES_FOR_SELECTS_TWO
116 lines (92 loc) · 4.48 KB
/
NOTES_FOR_SELECTS_TWO
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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
Use Community_Assist
Select * From Donation
Select Year(DonationDate) from Donation
Select Distinct Year(DonationDate) from Donation
Select Distinct Year(DonationDate) [Year] from Donation --in square brackets because year is a reserved word
Select Distinct Month(DonationDate) [Month] from Donation
Select Distinct Day (DonationDate) [Day] from Donation
Select DatePart(YY, DonationDate) [Year] from Donation --does the same thing as above
Select DatePart(hour, DonationDate) [Hour] from Donation
Select Distinct DatePart(hour, DonationDate) [Hour] from Donation
Select cast(month(DonationDate) as nchar(2)) + '/' + cast(day(DonationDate) as nchar(2)) + '/' + cast(year(DonationDate) as nchar(4)) as [Date] From Donation
--cast creates different data types (cant concatenate integers, date functions return integers)
--DATE DIFF
Select dateDiff(yy,min(EmployeeHireDate),
max(EmployeeHireDate)) From Employee
--days can be 'dd' or 'day'
--years can 'yy' or 'year'
Select dateDiff(mm,min(EmployeeHireDate),
(max(EmployeeHireDate))) / cast(12 as Decimal(10,2)) From Employee
Select dateDiff(mm,min(EmployeeHireDate),
max(EmployeeHireDate)) / 12 [Years],
dateDiff(mm,min(EmployeeHireDate),
max(EmployeeHireDate)) % 12 [Months]
from Employee
--returns Years and Months
Select EmployeeKey, format (EmployeeAnnualSalary, '$#,###.00') from Employee --pound signs are optional digits, 0s are required digits
--phone numbers are entered as a character type 'format' will not work on phone numbers. must cast them to an integer
Select EmployeeKey, '$' + cast(EmployeeAnnualSalary as nvarchar(10)) from Employee
--format function takes a number and turns it into a character pattern
Select 4 * 3 - 2 / 5 --=12
Select 4 * 3 - 2 / 5.0 --=11.6
--follow regular order of operations, add decimals to increase digits listed
Select EmployeeAnnualSalary,
EmployeeAnnualSalary * 1.05 as Raise from Employee
Select Sum(DonationAmount) as total From Donation --'as' is optional
Select Avg(DonationAmount) as Average From Donation
Select Count(DonationAmount) number From Donation
Select Max(DonationAmount) maximum From Donation
Select Min(DonationAmount) minimum From Donation
Select Year(DonationDate) [year], Sum(donationAmount) total From Donation --wont work. Year operates row by row, sum works on a whole table. conflict between scalar and aggregate function
Select Year(DonationDate) [year], Sum(donationAmount) total
From Donation
Group by Year(donationDate) --you must group to do this
Select Year(DonationDate) [year], Month(DonationDate) [Month],
Sum(donationAmount) total
From Donation
Group by Year(donationDate), Month(donationDate) --you must group to do this
Select Year(GrantRequestDate) [Year],
Month(GrantRequestDate) [Month],
Count(GrantRequestKey) Number ,
Sum(GrantRequestamount) Total
from GrantRequest
Group by Year(GrantRequestDate), Month(GrantRequestDate) --Must group by all aggregate values
Select Year(GrantRequestDate) [Year],
Month(GrantRequestDate) [Month],
Count(GrantRequestKey) Number ,
format(Sum(GrantRequestamount), '$#,###0.00') Total
from GrantRequest
Group by Year(GrantRequestDate), Month(GrantRequestDate) --Must group by all aggregate values
--Only See the ones above $1000 Below
--Cant use aggregate functions in a WHERE CLAUSE
--'HAVING' is the way to do this
Select Year(GrantRequestDate) [Year],
Month(GrantRequestDate) [Month],
Count(GrantRequestKey) Number ,
format(Sum(GrantRequestamount), '$#,###0.00') Total
from GrantRequest
Group by Year(GrantRequestDate), Month(GrantRequestDate)
having Sum(GrantRequestAmount) > 1000 --Must group by all aggregate values
Select Year(GrantRequestDate) [Year],
Month(GrantRequestDate) [Month],
Count(GrantRequestKey) Number ,
format(Sum(GrantRequestamount), '$#,###0.00') Total
from GrantRequest
Where Month(GrantRequestDate)=8 --This is allowed
Group by Year(GrantRequestDate), Month(GrantRequestDate)
having Sum(GrantRequestAmount) > 1000
--Where clause always comes before the 'GROUP BY'
Select * from Sys.Databases
--built in VIEWS that give you information about the server and the database
--Useful for coming in on the command line
--within the context of an actual table, there are several hundred of these views
Select * from Sys.Tables
--Context based so this returns all of the tables in CommunityAssist
use MetroAlt
Select * from Sys.Tables
--In this context, it returns the tables in MetroAlt
use Community_Assist
Select * from sys.columns
--lists all columns in this DB
Select * from sys.columns where Object_ID=277576027
Select * from sys.columns where Object_ID=373576369