Wednesday, April 3, 2013

ITC222 first Selects

Use CommunityAssist

--simple select
Select firstname, lastname from person;
Select * From Person

--aliasing the field names
Select Firstname as [First Name], Lastname as [Last Name]
From Person

--aliasing without the as keyword
Select Firstname  [First Name], Lastname  [Last Name]
From Person

--select distinct values
Select Distinct PersonKey from Donation

--sort results
Select * From Person
order by LastName desc, Firstname Desc

--where criteria
Select * From PersonAddress
Where City ='Seattle'

Select * From PersonAddress
Where City ='kent'

Select * From PersonAddress
Where Not City ='Seattle'

Select * From PersonAddress
Where City !='Seattle'

Select * From PersonAddress
Where City <>'Seattle'

--finding nulls
Select * From PersonAddress
Where Apartment is null

--finding not nulls with and criteria
Select * From PersonAddress
Where Apartment is Not null
And Not City = 'Seattle'

--or
Select * From PersonAddress
Where Apartment is Not null
OR Not City = 'Seattle'

Select * From Donation
Where DonationAmount > 2000
--you can use these comparison operators
-- >, <, >=, <=, =

--using between
Select * From Donation
Where DonationDate between '3/1/2010' and '3/31/2010'

-- using the in keyword
Select * From Donation where DonationAmount in (500, 1000, 1200, 50)

--like

Select * From PersonAddress
where Street Like '%ave%'

Select * From PersonAddress nolock
where City Like '%ll%' 

No comments:

Post a Comment