TSql Practical SQL Examples (Select)

To dive into sql, is essential to do some practical writing of SQL queries:

Examples from this post are taken and adapted from Practical SQL Queries for Microsoft SQL Server 2008 R2 buy it

 

Reminder on SQL

The SQL language is often divided into three sections:

  • data manipulation language (DML)
  • data definition language (DDL)
  • data control language (DCL)

DML consists of Select, Insert, Update, and Delete.

DDL consists of Create, Alter, and Drop.

DCL consists of Grant, Revoke, and Deny.

Prerequisites to run the examples

You will need two databases on your SQL Server instance: AdventureWorksDW2008 and AdventureWorks2008. Refer to the http://msftdbprodsamples.codeplex.com to download the free copy.

AdventureWorks2008 = OLTP version of AdventureWorks

AdventureWorksDW2008 = AdventureWorks star/snowflake/OLAP schema of AdventureWorks

 

Select: Single Table

Let’s focus on retrieving columns and rows of data from a single table doing column calculations, aliasing columns, handling dates and nulls, etc..

— server name, schema, database name

SELECT
*

FROM [RIO-PC].AdventureWorksDW2008.dbo.DimCustomer

You can specify the server name, database name and the schema, together with the table from which read data

N> to use * is not a best practice, specify the columns in each query as the just the order of the column could be different from the one you expect

 

— column name

USE AdventureWorksDW2008

SELECT LastName ,

FirstName

FROM DimCustomer

Example of using specific column name

N> you have to separate the columns using ,

 

— aliases

SELECT lastname AS Surname

FROM dimcustomer

SELECT lastname Surname

FROM dimcustomer

SELECT Surname = lastname

FROM dimcustomer

You can specify the alias for the columns in 3 different ways

N> use the [] is you have spaces in the alias

 

— concatenation, strings

SELECT LastName +
‘ ,’
+ FirstName AS FullName FROM dbo.DimCustomer

Use the + operator to contatenate

N> the result is one column even it is built on 2 separate columns

— cast, convert

SELECT lastname +
‘ ‘
+
CAST(customerkey AS
NVARCHAR)
AS [Name ID]

FROM dimcustomer

SELECT lastname +
‘ ‘
+
CONVERT(NVARCHAR, customerkey)
AS [Name ID]

FROM dimcustomer

Casting is necessary when mixing together different data types

N> cast is ANSI standard

http://msdn.microsoft.com/en-us/library/ms187928(v=SQL.105).aspx

 

— date , converion format

SELECT
CONVERT(VARCHAR, BirthDate, 101)
AS [4 digits BDate] FROM dbo.DimCustomer

–4 digits BDate

——————————–

–04/08/1966

–05/14/1965

–08/12/1965

SELECT
CONVERT(VARCHAR, BirthDate, 1)
AS [US format BirthdDate] FROM dbo.DimCustomer

–US format BirthdDate

——————————–

–04/08/66

–05/14/65

Use Convert to change the date format in otput

— datediff

SELECT BirthDate,
DATEDIFF(yy,BirthDate,
GETDATE())
AS [Current Age] FROM dbo.DimCustomer

–BirthDate Current Age

———— ———–

–1966-04-08 45

–1965-05-14 46

–1965-08-12 46

 

— distinct

SELECT EnglishOccupation FROM dbo.DimCustomer

SELECT
DISTINCT EnglishOccupation FROM dbo.DimCustomer

–EnglishOccupation

——————————————————————————————————

–Professional

–Manual

–Clerical

–Management

–Skilled Manual

 

–(5 row(s) affected)

The DISTINCT operator removes duplicated

select englishoccupation, englisheducation from DimCustomer

select
distinct englishoccupation, englisheducation from DimCustomer

The Distinct operator is working on both columns together

 

— top

SELECT
TOP 3 *
FROM DimCustomer

TOP keyword is very useful when handling large amount od data or when ordering data

 

— xml

SELECT
TOP 1 LastName, FirstName,BirthDate FROM DimCustomer FOR
XML
AUTO

–<DimCustomer LastName=”Yang” FirstName=”Jon” BirthDate=”1966-04-08″/>

 

SELECT
TOP 1 LastName, FirstName,BirthDate FROM DimCustomer FOR
XML
AUTO,
ELEMENTS

–<DimCustomer><LastName>Yang</LastName><FirstName>Jon</FirstName><BirthDate>1966-04-08</BirthDate></DimCustomer>

 

You can format the output as XML

N> the usage of Elements change the format defining elements for the result structure (attribute centric and element centric)

 

— NULL

SELECT
TOP 5 LastName +
‘ ‘
+ MiddleName FROM dbo.DimCustomer

–Yang V

–Huang L

–NULL

–NULL

–NULL

SELECT
TOP 5 LastName+
ISNULL(MiddleName,‘…’)
FROM dbo.DimCustomer

–YangV

–HuangL

–Torres…

–Zhu…

–Johnson…

N> Null values represent missing values—they are not the same as empty strings or zeroes, when concatenated with NON NULL the result is NULL

6 thoughts on “TSql Practical SQL Examples (Select)

  1. Pingback: TSql Practical SQL Examples (Joining Tables) | Obar1 aka Mario Amatucci

  2. Pingback: TSql Practical SQL Examples (Aggregates) | Obar1 aka Mario Amatucci

  3. Pingback: TSql Practical SQL Examples (New Tables) | Obar1 aka Mario Amatucci

  4. Pingback: TSql Practical SQL Examples (Except/Intersect/Union) | Obar1 aka Mario Amatucci

  5. Pingback: TSql Practical SQL Examples (Where, Order By) | Obar1 aka Mario Amatucci

  6. Pingback: TSql Practical SQL Examples (Group By/Compute) | Obar1 aka Mario Amatucci

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