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).
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.