Data Retrieval

SQL language has evolved, it has expanded to add more capabilities. This topic will guide you through the fundamentals of the Data Manipulation Language (DML) aspects of T-SQL as they relate to retrieving of data.

The SELECT Statement

The SELECT statement consists of four clauses or components. SELECT - followed by a list of columns or an asterisk, indicating that you want to return all columns. FROM - followed by a table or view name, or multiple tables with join expressions. WHERE- followed by filtering criteria. ORDER BY - followed by a list of columns for sorting.

Column Aliasing

In order to simplify a large querries and to avoid conflicts on naming.

The following example clarifies the source of an ambiguos column by using an alias:

Example: Multi Table Query

Table 1: customerinfo      Table 2: transactionx      Table 3: employee
customerid                      trx_id                           employeeid
lastname                         trx_date                        lastname
firstname                        employeeid                   firstname
mname                           customerid                    mname



In order to organize your query. Assigned customerinfo table as A, trasactionx table as B while employee table as C.

Select C.trx_date, (C.lastname +  ' ' + C.firstname + ' ' + C.mname) as Customer, (B.lastname +  ' ' + B.firstname + ' ' + B.mname) as Employee from transactionx A join customerinfo C on A.customerid =C.customerid join employee B on A.employeeid =B.employeeid

Filtering Rows

   The WHERE Clause
   
   Filtering is largely the job of the where clause, which is followed by some sort of filtering expression.

   Select * From transaction where customerid = 5

  Comparision Operators

  Operator   Description
  =               Equal to. Matches a value that is exactly equal to another value.
<> or !=     Not equal to
<                 Less than. Typically used with numeric and date/time data types.
>                 Greater than
!<               Not less than
!>               Not greater than
<=              Less than or equal to
>=              Greater than or equal to
Like            Used to perform wildcard matches with character values.

Here are couple of examples of their use:

Select Name, ListPrice From Production.Product where ListPrice >= 75.00

Select Name, ListPrice from Production.Product where ListPrice !> 5.00

Select Name From Sales.Store Where Name Like 'Top%'

To find a value that contains a certain string you can use both leading and trailing wildcards.
   Select * from customerinfo where lastname LIKE '%sto%'

To find a value when only a single character is unknown, the underscore can be used.
   Select * from customerinfo where lastname LIKE '_urley'

To find a value when a single character is within a specified range or set, square brackets are used. This exaple looks for first names of either Dan or Don.
   Select * from customerinfo where firstname Like 'D[a-o]n'

To search for a value that does not contain specific characters, the caret symbol is used.
   Select * from customerinfo where firstname LIke 'D[^o]n'

or

 Select * from customerinfo where firstname not like 'Dan'

Selecting Null 

Select * from customerinfo where lastname is not NULL

or

Select * from customerinfo where lastname is NULL

Extended Filtering Techniques

    The Between Operator

      Select * from transactionx where trx_date between '1992-1-1' and '1995-1-1'

    The IN() Function

     Select * from transactionx where customerid in (1,2,3)   --Shortcut

    Select * from transactionx where customerid = 1 or customerid = 2 or customerid = 3

Top Values
   
   Select Top 10 Name from transactionx order by customerid

   It's necessary to filter the results based on some sort of criteria. In some cases you will want to simplify return a specific number of records regardless of the number of qualifying rows. The two options for returning top values: including a fixed number of rows or a percentage of total rows.

   With Ties

   What if in Adventure Works Database price of the 25th product were the same as one or more products down the list. Solution is quite simple use the with ties statement.

   Select Top 10 WITH TIES Name, ListPrice from Production.Product Order by ListPrice DESC

   Percent
  
   Rather than specifying a number of records to be returned with the top statement, you can specify a percentage of the entire result set.

   Select Top 10 Percent Name,ListPrice from Production.Product order by ListPrice DESC