Analytics in Action

A beginner’s guide to SQL: An introduction to the 5 most commonly used pieces of SQL code

This 10 minute "Introduction to SQL" tutorial  teaches students the 5 most common SQL  tasks that  data analysts perform on a daily basis such as Select  all rows and columns  from a database table Returning a subset of columns from a table Sorting results from a query Filtering...

Introduction to Merge Statements in T-SQL

Merge statements provide a flexible approach to manipulate data in a "target" table based on a join to a "source" table. This enables users to perform a large number  of updates and/or inserts in a target table using a single...

Window Functions in T-SQL (Ranking, Cumulative totals, Row counts, First and Last values)

Window functions allow you to apply calculations against "sets" or groups of data. These sets are known as windows. Many books classify window functions as an advanced topic in T-SQL. But in fact, once you see some practical examples window...

Creating Temporary Tables with Transact SQL

Temporary Tables are a handy technique for creating cascading logic. Results from one temporary table can be passed to other temp tables. This can simplify the process for creating complex result sets. Temporary tables are saved to disk, and are...

Creating Subqueries in Microsoft SQL Server (T_SQL)

Transact SQL allows you to nest queries within queries. The inner query is known as a subquery, and this is used as an input for the outer query. Unlike queries that use constants, the results of a subquery can change...

Logical Query Processing in SQL Server

SQL Server processes code in a sequence which is known as the Logical Query Processing order. In a typical query, the logical processing order is : FROM -> WHERE -> GROUP BY -> HAVING -> SELECT ->ORDER BY. Each step...

Joining Tables in SQL Server

Like a lot of aspects of databases & analytics, table joins are pretty straight forward. You just need to be aware of some core concepts and a few tricks and tips, and then practice. So why join tables?  Well databases are...

Indexes in SQL Server – making queries run fast with Clustered, Non-clustered & Column indexes

Indexes in SQL Server make  queries run faster and more efficiently.  With the size of databases increasing almost exponentially, being able to return results quickly with minimal stress on the underlying  server is crucial. This tutorial includes a practical demonstration of  performance...

Introduction to Stored Procedures in SQL Server

A Stored Procedure is a SQL statement stored in a SQL Server database. They can be triggered with a short "EXECUTE" statement. Common functions of stored procedures include retrieving, modifying and deleting data. They are typically designed to accept one...

Introduction to Transact-SQL (T-SQL) using Microsoft SQL Server

Transact - Structure Query Language (T-SQL) is Microsoft's (& Sybase's) proprietary extension to  SQL. Its used for querying, altering and defining databases.  Transact-SQL is central to using Microsoft SQL Server. All applications that communicate with an instance of SQL Server do so by...