Select Statements

Use MagazineSubscription

/*this is a multiline comment
I can write as much as I need
to wrote */

--simple selects and alias
--The AS keyword is optional
--the square brackets indicate a field with a space or a reserved word
--double quotes can also be used

Select CustLastName AS [Last Name],
CustFirstName As [First Name],
CustPhone As [Phone]
From customer

--the * is a wild card that returns all columns
Select * From Customer

Select CustFirstName,CustLastName,CustCity
From Customer
Order by CustLastName Desc --z to a

Select CustFirstName,CustLastName,CustCity
From Customer
Order by CustCity,CustLastName Desc
--primary sort by city and then secondary sort by lastname in reverse order

Select SubscriptionPrice, SubscriptionPrice * .095 AS [TAX]
From MagazineDetail

Select (4 + 3) * 2-1

--WHERE Clauses differentiate between rows

Select CustFirstName,CustLastName,CustCity
From Customer
Where NOT CustCity = 'Seattle'

--can us = > < >= <= with number or dates
Select MagDetID, SubscriptionPrice
From MagazineDetail
Where SubscriptionPrice >100

Select MagDetID, SubscriptionPrice
From MagazineDetail
Where SubscriptionPrice >=50 AND SubscriptionPrice <= 100

Select MagDetID, SubscriptionPrice
From MagazineDetail
Where SubscriptionPrice between 50 And 100

Select SubscriptionID, SubscriptionStart, SubscriptionEnd
From Subscription
Where SubscriptionStart between '4-1-2006' and '4-30-2006'


Select * from Customer

--inserting a new customer just to have a null value
Insert Into Customer(CustLastName,
CustFirstName,
CustAddress,
CustCity,
CustState,
CustZipcode)
Values ('Smith','Fred','1001 Somewhere','Seattle', 'WA', '98001')

--Use the IS keyword to find Nulls
Select * From Customer
Where CustPhone IS Null

--LIKE is used to find patterns
--the wildcards are % any number of characters and _ a single character

Select CustFirstName, CustLastName
From Customer
Where CustFirstName LIKE 'L%'

--this uses an escape character to indicate that
--you are searching for a literal % sign and
--not to use it as a wildcard

Select CustFirstName, CustLastName
From Customer
Where CustLastName LIKE '%an!%'
Escape '!'


--ITC 22_ returns ITC220, ITC222, ITC224, ITC226


--distinct returns only unique rows
Select Distinct Magid from MagazineDetail