Basic Select Statements

use Community_Assist
--select statements
--the * is a wild card meaning return all columns
--in the order they are in the underlying table
Select * from Person
Select * from PersonAddress

--you can alias column names
--the "as" is optional
--square brackets are necessary if there are illegal
--characters such as spaces
Select PersonLastName as [Last Name], 
PersonFirstName [First Name], 
PersonEmail Email
from Person

--the where clause limits the number of rows by specifying 
--what values to return
--character types are always quoted with single quotes
Select PersonAddressApt,PersonAddressStreet,
PersonAddressCity,PersonAddressZip
From PersonAddress
Where PersonAddressCity='Kent'

--with an or clause
Select PersonAddressApt,PersonAddressStreet,
PersonAddressCity,PersonAddressZip
From PersonAddress
Where PersonAddressCity='Kent'
Or PersonAddressCity='Bellevue'

--nulls cannot be = to or compared to any value
--so the "is" keyword is used to find nulls
Select PersonAddressApt,PersonAddressStreet,
PersonAddressCity,PersonAddressZip
From PersonAddress
Where PersonAddressApt is null

--find values that are NOT null
Select PersonAddressApt,PersonAddressStreet,
PersonAddressCity,PersonAddressZip
From PersonAddress
Where PersonAddressApt is not null

--not
Select PersonAddressApt,PersonAddressStreet,
PersonAddressCity,PersonAddressZip
From PersonAddress
Where not PersonAddressCity ='Seattle' --!=, <>

--numeric values are not quoted and can be
--used with comparitives = > < >= <=
Select DonationDate, DonationAmount from Donation
Where DonationAmount > 500

--dates are quoted
Select DonationDate, DonationAmount from Donation
Where DonationDate > '8/30/2015'

Select DonationDate, DonationAmount from Donation
Where DonationDate = '9/3/2015'

--be wary of Date time types the time is counted in the results
Select DonationDate, DonationAmount from Donation
Where DonationDate between '9/3/2015' and '9/11/2015'

--order by sorts ASC is the default
--DESC sorts descending
Select * from Person
order by PersonLastName asc, PersonFirstname Desc

Select * from Donation
order by DonationAmount desc

--Like searches for patterns. the "%" is a wild card
--it represents any number of characters
Select * from Person
Where PersonLastName like 'Z%n'

Select * from Person
Where PersonLastName like '%mm%'
-- the underscore _ is a wild card for single character

Select * from Person
Where PersonLastName like 'Tan_er'

--Distict returns only Distinct, unique rows. 
--(it doesn't apply to the columns only the rows)
Select Distinct PersonKey from Donation
order by PersonKey

--returns only the specified number of rows
--literally just takes the first rows for as many
--as are specified-- order by makes the command
--return a more meaningful result
Select Distinct top 10 DonationAmount from Donation
order by DonationAmount desc

--the offset starts at the specified number 
--of rows from the top and then returns
--the stated number of rows
--order by is required
Select Distinct DonationAmount from Donation
Order by DonationAmount desc
Offset 5 rows fetch next 10 rows only