Select Statements

Today, after the initial chaos of setting up Sql Server and assigning permissions, we looked at a few select statements:

First make sure you are in the correct database.

use CommunityAssist

The following statement selects all the records from the table Person.

Select * from Person;

/*sort by lastname z to a and firstname a-z*/
Select Firstname, lastname from Person
order by Lastname Desc, firstname;

Order by allows you to sort the results as you like. DESC lets you sort in reverse order. If you have more than one column listed for sorting, the leftmost column is the primary sort, and then it sorts left to right.

Below are some examples of the WHERE clause with various criteria. Note the use of quotes for string (char, varchar, nchar, nvarchar)and dates. Number values are not quoted. Semicolons are optional in SQL Server. The last two statements are equivalent.

Select * from PersonAddress
Where City = 'Kent';

Select * from donation
Where DonationAmount <= 100;

Select * from Donation
Where DonationDate > '3/1/2010'
And DonationDate < '4/1/2010'

Select * from Donation
Where DonationDate not between '3/1/2010' and '4/1/2010'

Nulls are special cases. They are not a value--not a zero or empty string. They represent an unknown value. As such they cannot be equal to or greater than or less than a value. Therefore SQL uses the IS keyword with Nulls.

Select * from PersonAddress
where Apartment is not null

You can do calculations with Columns. Columns can be aliased to give them more meaningful names

Select 45 * 4 + 3 /2

Select GrantAmount OldAmount, GrantAmount * 1.1 as NewAmount 
from ServiceGrant 

One that we didn't look at in class is the LIKE operator. It lets you look for patterns in character type data. the % is a wildcard that say look for any number of characters. the underscore is the wild card for a single character. The first one returns all the last names beginning with 'S'. The second one returns any course that start 'MIC 12'. For instance it would return 'MIC 121,' 'MIC 123,' etc.

Select * from Person where Lastname like '$%'

Select * from course wher coursenumber like 'MIC 12_'

--