Basic T-SQL

In this article I’m going to outline the basic T-SQL commands for those new to the language, and as a reference for others. Here is some basic SQL, using the most common commands, as might be used in a reporting stored procedure.

SELECT Store.StoreName       AS 'StoreName'
       ,Prod.ProductName     AS 'ProductName'
       ,YEAR(Dat.[Date])     AS 'SaleYear'
       ,MONTH(Dat.[Date])    AS 'SaleMonth'
       ,SUM(TotalSaleAmount) AS 'TotalSaleAmount'
FROM       Fact_Sales  Sale
INNER JOIN Dim_Store   Store ON Sale.StoreSK = Store.StoreSK
INNER JOIN Dim_Product Prod  ON Sale.ProductSK = Prod.ProductSK
INNER JOIN Dim_Date    Dat   ON Sale.SaleDateSK = Dat.DateSK
WHERE IsCorrect = 1
  AND Sale.ValidTo IS NULL
GROUP BY Store.StoreName
         ,Prod.ProductName
         ,YEAR(Dat.[Date])
         ,MONTH(Dat.[Date])
HAVING SUM(TotalSaleAmount) >= 100
ORDER BY TotalSaleAmount DESC

SELECT x AS y          x specifies the field to return (usually as table.field), y is what the field should be named. Alternatively you can specify a function of a field e.g YEAR(x), a calculation e.g. TotalSales + 10 or a literal e.g. SELECT 4 or SELECT 'John'. Multiple fields are separated by commas. If only one table is involved, you can just specify the field names.

SELECT *          gives you all the fields from the tables specified. Can be useful for a quick query, but best not used in a final stored procedure as it is unstable when fields are added/removed.

FROM x y          x specifies the main table, y is the table alias (a shortened version of the table name, which you can use to refer to the table in the rest of the statement).

INNER JOIN x ON y = z          x specifies an extra table joined to the main table, y = z determines which records should be joined. It usually takes the form of the dimension table’s primary key = the corresponding foreign key in the fact table. See below for different types of join.

WHERE x = y          x = y specifies a condition which must be true for a record to be included. Multiple conditions can be linked using AND / OR.

GROUP BY x          x is a list of fields to group by, separated by commas. In essence, grouping creates a unique list based on the GROUP BY fields and performs whichever aggregations have been chosen. In a grouped select, all fields selected must either be grouped by, or be an aggregate, or be a literal.

HAVING x = y          x = y is a condition which must be true for a group to be included. Similar to the Where clause, but at the group level. Multiple conditions can be linked using AND / OR.

ORDER BY x y          x specifies a field to order by, y is ascending or descending. Multiple fields are separated by commas and are sorted in the order they are specified. Ordering is usually best left to the report to decide, rather than doing in the stored procedure (this is usually quicker too).

Results
Results from the SELECT statement above

Main JOIN types:

INNER JOIN = The join test is applied to every record in A with every record in B. Only matches that pass the test will be shown.

LEFT JOIN = All records in table A are shown. Records in B that pass the test when paired with records in A will be shown. If a record in A has no matches, it is paired with NULL values.

RIGHT JOIN = same as left join, but A and B are reversed.

FULL OUTER JOIN = All records in A and B are shown. Any that pass the test together are shown together. Any non-linked records are paired with NULL values.

CROSS JOIN = Every record in table A is paired with every record in table B. No conditions are needed.

 

Advertisements

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