T-SQL - Introduction (Part 2)

  07 Mar 2019
  tsql

Note : The following scripts are the part of the course on edx titled as: Querying Data with Transact-SQL -
Querying Data with Transact-SQL
These queries works on AdventureWorks database and information regarding the same
can be accessed by visiting following link -
AdventureWorks Installation and configuration

In the last post, we started with Introduction to T-SQL, where some of the forms of SELECT statement were discussed, along with examples regarding Data Type Conversion , Working with NULLs and CASE-END operations were shown.
You can follow the same - Introduction to Transact-SQL - Part 1

We’ll be discussing these in this post -

Removing Duplicates

Distinct is used for getting results distinct values from the selected columns

  • The following queries results in a column with distinct Color names with null replaced as None, creating a new category for such cases.
select
  distinct isnull(Color, 'None') as Color
from
  SalesLT.Product

Sorting Results

Order By is used to order the final output return by sql queries by the order of the column in ascending / descending manner. The default order is ascending.

  • The following queries will give the distinct color names sorted alphabetically. 
select
  distinct isnull(Color, 'None') as Color
from
  SalesLT.Product
order by
  Color
  • This one will do the same, sorting the output in alphabetical manner by column color of the output returned. Here we are bringing distinct values of color with replacing null by None in Color column and for each color bringing the size associated with them where null values are replaced by - character.
select
  distinct isnull(Color, 'None') as Color,
  isnull(Size, '-')
from
  SalesLT.Product
order by
  Color

Paging Sorted Results

  • Displaying top n rows - Top N command, where N is the no. of rows you want -
select
  top 10 Name,
  ListPrice
from
  SalesLT.Product
order by
  ProductNumber
  • Displaying last n rows - Top N command with Order By Desc , to get bottom N rows -
select
  top 10 Name,
  ListPrice
from
  SalesLT.Product
order by
  ProductNumber Desc
  • We can also get the percentage of data, by using Top N percent -
select
  top 10 percent Name,
  ListPrice
from
  SalesLT.Product
order by
  ProductNumber
  • Top N with ties - Top N will always return you the top N rows, doesn’t matter if they are duplicate or not. Whereas, Top N with ties will return all rows that have same values associated, will do it for all 10 distinct values -

As per Microsoft documentation - With Ties
Return two or more rows that tie for last place in the limited results set. You must use this argument with the ORDER BY clause. WITH TIES might cause more rows to be returned than the value specified in expression. For example, if expression is set to 5 but two additional rows match the values of the ORDER BY columns in row 5, the result set will contain seven rows.

-- return only top 10 rows
SELECT
  TOP 10 SalesOrderID, OrderQty
FROM
  SalesLT.SalesOrderDetail
ORDER BY
  OrderQty

-- return around 128 rows, all rows associated with where OrderQty is same is returned
SELECT
  TOP 10 with ties SalesOrderID, OrderQty
FROM
  SalesLT.SalesOrderDetail
ORDER BY
  OrderQty
  • Offset and Fetch are used to implement a query paging solutions
    • Offset - no. of rows to ignore
    • Fetch Next - next set of rows to bring

    The following query will bring rows no. 11-20 and not first 10 rows -

-- fetch rows 11-20
select
  Name,
  ListPrice
from
  SalesLT.Product
order by
  ProductNumber offset 10 Rows Fetch Next 10 Rows Only

-- offset is 0, hence fetch first 10 rows
select
  Name,
  ListPrice
from
  SalesLT.Product
order by
  ProductNumber offset 0 Rows Fetch Next 10 Rows Only

Filtering Data

Filtering data can be done using multiple ways - like, equal to, not equal to, between, and, in, like, is null, not null etc. Some of these can be implemented with multiple combinations as well. Following queries explore them in more details.

  • Not equal to can be implemented by <> -
-- Prodcut Id not equal to 6
Select
  Name,
  Color,
  Size
from
  SalesLT.Product
where
  ProductModelID <> 6
  • Is Not Null - Return not null values only -
Select
  Name
from
  SalesLT.Product
where
  SellEndDate Is Not Null;
  • Between and And - both the ranges are inclusive -
Select
  Name,
  SellEndDate
from
  SalesLT.Product
where
  SellEndDate Between '2006/1/1'
  and '2006/12/31'
  • In - values matches in the particular list -
-- Only those results where ProductCategoryID is 5,6 or 7
Select
  Name,
  ProductCategoryID
From
  SalesLT.Product
where
  ProductCategoryID in (5, 6, 7)
order by
  ProductCategoryID Desc

-- ProductCategoryID is 5, 6 or 7 and SellEndDate is not null
Select
  ProductCategoryID,
  Name,
  SellEndDate
From
  SalesLT.Product
where
  ProductCategoryID in (5, 6, 7)
  and SellEndDate Is Null
  • Like is used to match string pattern -
    • % - anything string of 0 or more characters
    • _ - any single character For more details - Wildcard Characters
-- ProductNumber starts with FR
select
  productnumber,
  Name,
  ListPrice
from
  SalesLT.Product
where
  ProductNumber like 'FR%'

-- ProductNumber starts with FR-,
-- then contains any single character followed by two single character in range 5-6 and 5-9 (both inclusive)
-- then conatins any single character followed by -
-- and finally any with two number ranging from 0-9
select
  Name,
  ListPrice,
  ProductNumber
from
  SalesLT.Product
where
  ProductNumber Like 'FR-_[5-6][5-9]_-[0-9][0-9]'

-- ProductNumber starts with FR or ProductCategoryID is 5,6,or 7
select
  Name,
  ProductCategoryID,
  ProductNumber
From
  SalesLT.Product
where
  ProductNumber like 'FR%'
  or ProductCategoryID IN (5, 6, 7)

Related Posts