Querying with Transact-SQL

Written on Mar 06, 2016

Following article is outline of session by mva, conducted by Graeme Malcolm and Geoff Allix. All the examples showned in this article uses database AdventureWorksLT.

Removing Duplicates

Whenever we select any statement it dosen't filter duplicate values; to remove duplicate values use DISTINCT keyword. For example, if we want to get all distinct list of cities we use below query.

Limiting Sorted Results

Keyword TOP allows to limit the number or percentage of rows returned by a query. It works with ORDER BY clause to limit rows by sort order. Below are the sample syntaxt with SELECT clause.

Below are the some examples of it.

Paging Through Results

Sql server 2012 added new keywords to simplify paging, which are OFFSET and FETCH. OFFSET-FETCH is an extension to the ORDER BY clause. OFFSET keyword specifies number of rows to skip, and FETCH specifies number of rows to take. Below is syntax of it.

Below are the examples of OFFSET-FETCH.

Filtering and Using Predicates

Predicates and filtering are performed using WHERE keyword. It is the most essence of SQL querying. Below table lists predicates and operators.

Predicates and Operators Description
=<> Compares values for equality / non-equality.
IN Determines whether a specified value matches any value in a subquery or a list.
BETWEEN Specifies an inclusive range to test.
LIKE Determines whether a specific character string matches a specified pattern, which can include wildcards.
OR Combines two Boolean expressions and returns TRUE if either is TRUE.
NOT Reverses the result of a search condition.

I dosen't go in details of these predicates, but below list of examples explaines itself.