[DB] T-SQL Query

Transact-SQL (T-SQL) is Microsoft’s and Sybase’s proprietary extension to SQL. The “AdventureWorks” database is used here.

1. SELECT Syntax

SELECT [ALL | DISTINCT] <select_list>
[FROM <table_name> [, <table_name2> [..., <table_name16>]]
[WHERE <search_conditions>]
[GROUP BY <clause>]
[HAVING <search_conditions>]
[ORDER BY <clause>]
[COMPUTE <clause>]
  • Joined Tables:
{ <table_name> CROSS JOIN <table_name> |
  <table_name> {INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER]} JOIN <table_name> ON <search_conditions> }
  • GROUP BY clause:
GROUP BY [ALL] <aggregate_free_expression> [[, <aggregate_free_expression>]...]
  • ORDER BY clause:
ORDER BY {{<table_name>. | <view_name>.}<column_name> | <select_list_number> | <expression>} [ASC | DESC] [...{{<table_name16>. | <view_name16>.}<column_name> | <select_list_number> | <expression>} [ASC | DESC]]
  • COMPUTE clause:
COMPUTE <row_aggregate>(<column_name>) [, <row_aggregate>(<column_name>)...]
[BY <column_name> [, <column_name>]...]

 

2. Selecting Columns: Projection

  • An asterisk (*) is used to designate all columns in a table.
  • The columns can be explicitly specified¬† instead of using an asterisk (*).
  • String data can be combined together. This is called concatenation.
  • Columns can be renamed (alias): Use a space and then specify the alias name or Specify the keyword “AS” and then the alias name.
SELECT *
FROM HumanResources.Employee;

SELECT EmployeeID, LoginID, Title
FROM HumanResources.Employee;

 

SELECT AddressLine1 + ',' + PostalCode
FROM Person.Address;

SELECT AddressLine1 + ',' + PostalCode FullAddress
FROM Person.Address;

SELECT AddressLine1 + ',' + PostalCode AS 'Full Address'
FROM Person.Address;

Aliases can be applied in two places:

  • SELECT clause : rename the column headers for the output
  • FROM clause : save some typing and also perform some higher level queries which will require this

 

3. Limiting the Result Set Using the WHERE Clause

  • The WHERE clause tells the database which rows to retrieve.
  • Multiple conditions can be combined using “AND” and “OR
SELECT *
FROM Person.Address
WHERE PostalCode = '98011';

SELECT AddressLine1 + ',' + City AS Addr
FROM Person.Address
WHERE PostalCode = '98011' OR City = 'York';
  • ¬†Instead of using multiple ORs, we can use an “IN” operator.
  • Use a “BETWEEN” operator for a range check.
SELECT *
FROM Person.Address
WHERE City IN ('Ottawa', 'Detroit', 'Phoenix');

SELECT *
FROM Sales.SalesOrderDetail
WHERE UnitPrice BETWEEN 1000 AND 2000;
  • Wildcards can be used for string pattern matching.
  • Percent (%): designates any string of zero or more characters
  • Underscore (_): designates a single character
  • Brackets are used to specify possible characters for pattern matching
SELECT *
FROM Production.Product
WHERE (Name LIKE '%Ball') OR (ProductNumber LIKE 'C_-%');
SELECT *
FROM Person.Contact
WHERE FirstName Like '[CJR]%'; -- where the fist name start C, J, or R

 

4. Specifying the Sorting Order Using the ORDER BY Clause

  • The sorting can be performed by columns.
  • The default sort order is ascending (ASC), but you can specify a descending order by using the keyword “DESC
SELECT *
FROM HumanResources.Employee
ORDER BY Title ASC, BirthDate DESC;

 

5. Removing Duplicate Rows Using the DISTINCT Keyword

SELECT PostalCode
FROM Person.Address
WHERE City = 'Bothell';

SELECT DISTINCT PostalCode
FROM Person.Address
WHERE City = 'Bothell';

 

6. Aggregates and the GROUP BY Keyword

There are times when we want to perform calculations on all of the values in a column or table. Transact-SQL provides the following aggregate functions:

  • AVG( ): returns the average of the values in a group. Null values are ignored.
  • COUNT( ) , COUNT_BIG( ): returns the number of items in a group. COUNT() returns an int data type value. COUNT_BIG() returns a bigInt data type value.
  • MAX( ), MIN( ): returns the maximum/minimum value in the expression.
  • SUM( ): returns the sum of all the values.
  • STDEV( ): returns the statistical standard deviation of all values.
  • STDEVP( ): returns the statistical standard deviation for the population for all values.
  • VAR( ): returns the statistical variance of all values.
  • VARP( ): returns the statistical variance for the population for all values.
SELECT COUNT(*)
FROM Person.Address
WHERE City = 'Kenmore';

SELECT MAX(ListPrice) AS MaxPrice, MIN(ListPrice) AS MinPrice
FROM Production.Product
WHERE ListPrice > 0;

The “GROUP BY” will order the data into groups that you specified and then return the set of rows that determine the groups

  • Duplicate rows are removed from this result set. In this way, “GROUP BY” performs a similar operation to distinct.
  • The “GROUP BY” will not sort the result set. You need to do this using the “ORDER BY” clause.
  • The true power of a “GROUP BY” comes from using it in conjunction with an aggregate.
SELECT ProductID, SUM(UnitPrice*OrderQty), COUNT(UnitPrice*OrderQty)
FROM Sales.SalesOrderDetail
GROUP BY ProductID;

 

7. HAVING Clause

The “HAVING” defines which groups are going to be returned to the user. The “HAVING” clause generally contain aggregates as part of the selection criteria.

SELECT ProductID, SUM(UnitPrice*OrderQty), COUNT(UnitPrice*OrderQty)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING SUM(UnitPrice*OrderQty) > 10000;

 

8. COMPUTE Clause

The “COMPUTE” is used to calculate grand summaries.

  • With a “COMPUTE/COMPUTE BY“, you can only use columns in the SELECT list.
  • The “COMPUTEBY …” clause is used with an “ORDER BY” clause.
  • You can use any aggregate except Count(*).
SELECT ProductID, UnitPrice, OrderQty, UnitPrice*OrderQty AS OrderPrice
FROM Sales.SalesOrderDetail
WHERE ProductID = 925
COMPUTE AVG(UnitPrice*OrderQty), SUM(UnitPrice*OrderQty);

SELECT ProductID, UnitPrice, OrderQty, UnitPrice*OrderQty AS OrderPrice
FROM Sales.SalesOrderDetail
ORDER BY ProductID
COMPUTE AVG(UnitPrice*OrderQty), SUM(UnitPrice*OrderQty) BY ProductID;

 

9. UNION Clause

When you want to return two or more sets of data as a single result set, the “UNION” can be used. The only restrictions on unions are that the same number of columns must be in each separate result set and the data types must match.

SELECT ProductID, UnitPrice, OrderQty, UnitPrice*OrderQty AS OrderPrice
FROM Sales.SalesOrderDetail
WHERE ProductID = 776
UNION
SELECT ProductID, UnitPrice, OrderQty, UnitPrice*OrderQty AS OrderPrice
FROM Sales.SalesOrderDetail
WHERE ProductID = 777;

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s