SQL Examples

Use CommunityAssist
/*
this is a multiline comment
This is a basic SQL 
tutorial
*/
--these are simple selects
--the * is a wild card meaning list all columns
Select * From Person

--choose the columns to display
Select PersonLastName, PersonUserName From Person

--sort by lastname ascending
Select PersonLastName, PersonUserName From Person
order by PersonLastName;

--sort by last name descending and user name ascending
Select PersonLastName, PersonUserName From Person
order by PersonLastName desc, PersonUserName;

--this shows using math in the select clause
Select DonationAmount, DonationAmount+ 100 AS Added
From Donation
order by DonationAmount desc

--you can use these operators with numeric
--or date values
-- <, >, <=, >=, =, !=, <>, Not =
Select DonationDate, DonationAmount From Donation
Where DonationAmount < 100

--with AND
Select DonationDate, DonationAmount From Donation
Where DonationAmount > 100 and DonationAmount < 1000

--with Between
Select DonationDate, DonationAmount From Donation
Where DonationAmount between 100 and 1000

--Between with dates
Select DonationDate, DonationAmount From Donation
Where DonationDate between '9/1/2013' and '9/30/2013'

--OR
Select * from PersonAddress
Where City = 'Bellevue' or City = 'Redmond'

--Like with wildcard. % is for any number of characters
-- _ is for a single character
--the following returns all names starting with t
--and ending with r
Select PersonLastName from Person
Where PersonLastName like 'T%r'

--joining two tables also looking for not null values
--nulls must be addressed using is or is not
--you cannot use comparitive values (=, <, > etc) with nulls
Select PersonFirstName, PersonLastName, Street,Apartment, [State], City, Zip
From Person
inner join PersonAddress
on Person.PersonKey=PersonAddress.PersonKey
Where Apartment is not null

--insert into a table 
Insert into Person (PersonLastName, PersonFirstName)
Values ('Bender','Robot')

--inner joins always return matched values
--outer joins return unmatched values
Select PersonlastName, Street, Apartment, [State], City, Zip
From Person
left outer join PersonAddress
on Person.PersonKey = PersonAddress.Personkey
Where PersonAddress.Personkey is null

-- an inner join with multiple tables
Select PersonFirstName, PersonLastName, Street,Apartment, [State], City, Zip,
ContactInfo, contactTypeName
From Person
inner join PersonAddress
on Person.PersonKey=PersonAddress.PersonKey
inner Join PersonContact
on Person.Personkey=PersonContact.PersonKey
inner join ContactType
on ContactType.ContactTypeKey=PersonContact.ContactTypeKey
Where not City = 'Seattle'
Order by City

--insert. You can only insert into one table at a time
Insert into Person(PersonLastName, PersonFirstName, PersonUsername, 
PersonPlainPassword,PersonEntryDate)
Values ('Conger','Steve','steve@spconger.com','password',getDate())

--insert  the Ident_current function returns the last 
--autonumber generated in the table listed
--it only works with autonumbers
Insert into PersonAddress(Street, Apartment, State, City, Zip, PersonKey)
values('1701 Broadway',null,'Wa','Seattle','98122', IDENT_CURRENT('Person'))

--you can insert multiple rows at a time
--as long as they are in the same table
Insert into Person(PersonLastName, PersonFirstName)
Values('Flanders', 'Ned'),
('Clown','Krusty'),
('Simpson','Homer')

--updates change existing data
--they should always (almost always)
--have a where clause
--Update is the most dangerous
--command in SQL
Update PersonAddress
Set Apartment='3176b',
State='WA'
Where PersonKey=128



Select * From PersonAddress where PersonKey=128

--to be safe with updates and deletes
--you can manually set the beginning and
--ending of a transaction
--this locks the table for the duration
Begin tran

--will set every last name to smith
Update Person
Set PersonLastName='Smith'

Select * From Person

--rollback undoes the command
--commit writes it 
Rollback tran
commit tran

--Delete removes a row or rows from
--a table
--you cannot delete a row that has 
--children in another table
Delete from person
Where PersonKey =128