TSql Practical SQL Examples (Where, Order By)

Another post focusing on WHERE + ORDER BY this time to continue the previous one on SELECT

Where

The where clause does partitioning of data (horizontal, when you limit the number of the column retrieved in vertical)

— where on one column

SELECT LastName, FirstName FROM dbo.DimCustomer

WHERE MiddleName IS
NOT
NULL

N> use AND/ OR / IN operators to add multiple conditions

— using IN operator

SELECT FirstName, LastName

FROM dbo.DimCustomer

WHERE FirstName IN
(
‘Claudia’,
‘Henry’,
‘Curtis’
)
AND MiddleName IS
NOT
NULL

N> you can use even NOT

— comparing columns values

SELECT CustomerKey FROM dbo.DimCustomer

WHERE TotalChildren < NumberChildrenAtHome

The comparison is done for each t-ple, using a fixed value the comaprison is done for each row value against the value itself

— range by where

SELECT CustomerKey, TotalChildren FROM dbo.DimCustomer

WHERE TotalChildren >0 AND TotalChildren<=2

Comparing on the same column

— like with wildcard % nad alternate way

SELECT FirstName, LastName

FROM DimCustomer

WHERE LastName LIKE
‘M%’

SELECT FirstName, LastName

FROM DimCustomer

WHERE
LEFT(LastName, 1)
=
‘M’

Using wildcards to find pattern

N>Using the N prefix means you are searching with a Unicode rather than an ASCII character.

— two letters, three in length

SELECT FirstName, LastName

FROM DimCustomer

WHERE LastName LIKE
‘Am_’

Using the _ a signpost, the underscore (_) means exactly one character.

— better to set multiple conditions

SELECT FirstName, LastName

FROM DimCustomer

WHERE LastName LIKE
‘[A-C]%’

FirstName LastName

————————————————– ————————————————–

Janet Alvarez

Shannon Carlson

Sydney Bennett

Alejandro Beck

Amanda Carter

N> The use of square brackets denotes a range of values

— and not like a range

SELECT FirstName, LastName

FROM DimCustomer

WHERE LastName LIKE
‘[^A-C]%’

FirstName LastName

————————————————– ————————————————–

Jon Yang

Eugene Huang

Ruben Torres

Christy Zhu

Elizabeth Johnson

— looking for contents in columns

SELECT FirstName, LastName

FROM DimCustomer

WHERE FirstName LIKE
‘%li%’

FirstName LastName

————————————————– ————————————————–

Elizabeth Johnson

Julio Ruiz

Willie Raji

Linda Serrano

Felicia Jimenez

Julia Nelson

Leslie Moreno

N> You can use CONTAINS if full text is enabled, change % wih * in this case

Order By

If you need to sort the output of your SQL queries, you need to use the Order By clause.

— simple odrder desc

USE AdventureWorksDW2008

SELECT LastName FROM dbo.DimCustomer

ORDER
BY LastName DESC

SELECT LastName FROM dbo.DimCustomer

ORDER
BY 1 DESC

LastName

————————————————–

Zukowski

Zimmerman

Zimmerman

— using TOP clause

SELECT
TOP (1) LastName, YearlyIncome FROM dbo.DimCustomer

ORDER
BY YearlyIncome ASC

SELECT
TOP (0.01)
percent LastName, YearlyIncome FROM dbo.DimCustomer

ORDER
BY YearlyIncome ASC

LastName YearlyIncome

————————————————– ———————

Beck 10000.00

(1 row(s) affected)

LastName YearlyIncome

————————————————– ———————

Beck 10000.00

Yuan 10000.00

(2 row(s) affected)

N> WITH TIES will add all ties for the retrieved rank (aka all people with the Income of 10000 aven they are more than the maximun number in the TOP)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s