Essential SQL

SQL Window Functions [Visual Explanation] In SQL, a window function refers to a function, such as sum or average, which acts upon a result sets rows' relative to the current row.

The purpose of this article is to introduce you to SQL window functions and some of the high-level concepts.  In SQL, a window function refers to a function, such as sum or average, which acts upon a result sets rows’ relative to the current row.

There are a lot of details to cover, but you’ll see we cover those in later articles.  For now, we’ll dig into the how window functions work and…

SQL SELECT Statement tutorial

SQL SELECT TOP Results

Once you get results you want to sort them using the SQL ORDER BY clause. Use it to specify columns or expressions to sort your results.

Additional SELECT Statement Resources

If you’re looking to learn more about the SQL ORDER BY clause, then I would recommend you read our article Sort Your Query Results.

To learn more about functions, such as UPPER, check out…

SQL SELECT Statement tutorial

Sort Results with SQL ORDER BY

Once you get results you want to sort them using the SQL ORDER BY clause. Use it to specify columns or expressions to sort your results.

The general form of the command is:

SELECT column1, column2, column3 FROM table1 ORDER BY column1, column4

You can sort by more than one column, and the column don’t have to be displayed; however, they have to be known…

SQL SELECT Statement tutorial

Rename Columns with SQL SELECT AS

You can use a form of SQL SELECT AS to rename columns in your query results.

So far you’ve seen where queries results return results named after the table columns. This is fine for most cases, but once you start working with expressions, you’ll see this doesn’t work well.

To rename a column use AS.

In this example we’re renaming FirstName to First…

SQL SELECT Statement tutorial

The SQL SELECT Statement

The SQL Select statement is used to query a database and return rows of data as a result set.

Here is the general syntax for a select statement:

SELECT column1, column 2, ..., column n FROM table

Below is an excerpt of the persons table from the AdventureWorksdatabase. Each person is listed as a row. The fields or attributes, are columns. You’ll see the column…

SQL HAVING Clause and GROUP BY

The SQL Having clause is used to filter summary results from a GROUP BY.  It specifies the search condition for the group or aggregate.

SQL HAVING is only used with SELECT.  It is mostly used when a GROUP BY is present, if one isn’t there is an implicit single aggregated group.

When constructing a SELECT statement using HAVING the order is:

SELECT columnlist FROM table WHERE condition GROUP…

SQL DISTINCT Clause For SQL Server

Use the SQL DISTINCT clause to return a unique list of values from a SQL SELECT statement. With DISTINCT, you elect to return unique values based on a combination of one or more columns.

SQL DISTINCT Example

The DISTINCT clause is used with the SELECT statement. It is placed immediately after SELECT and before the columns you wish to select. Here is a general form for the command:

SEL…

SQL Coalesce Function and NULL

Knowing when to use the SQL COALESCE function is a lifesaver when you’re dealing with NULL.

As you know, NULL is a tricky concept, and it seem what ever NULL “touches” in an expression, it renders the result NULL. So, when you’re dealing with NULL, how can you break out of the cycle? That is, how can you display another value instead?

This is where SQL COALESCE comes into play. With this…

SQL Server Hierarchical Query using the hierarchyid type

In this SQL minute we’ll look at how to create a SQL server hierarchical query using the hierarchyid data type.  This is a two-part article.  In part one you were introduced to the hierarchyid type.  In this article we dig deeper into the hierarchyid functions.

If you’re studying for the 70-761 exam, you’ll find there aren’t many intermediate SQL problems with explanations for you to solve. …

Use hierarchyid to query Hierarchical data

In this SQL minute we’ll look at how you can use hierarchyid to query hierarchy data.  This is a two-part article.  In this article we look at how to do one query to get portion of the hierarchy.  Next week, we will look to see how to query the entire tree.

If you’re studying for the 70-761 exam, you’ll find there aren’t many intermediate SQL problems with explanation for you to solve.  To…

Can I have a CASE Statement in the WHERE Clause?

A common question I get ask is whether I can have a CASE Statement in the WHERE Clause. There are so many examples of CASE being used in SELECT columns, or in ORDER BY that we tend to forget CASE can be used wherever an expression is expected.

Where Can I use a CASE Statement?

According to Ms SQL Docs a CASE statement can be used throughout the SELECT statement.

CASE can be used in any…

Create a Pivot table in six steps to cross tabulate your data

In this episode of SQL Minute I want to share with you how to create a pivot or cross tab chart.

I’ll show you the steps using an example from the adventure works database. I get so many questions on how to create these!

Once you get the steps down, you’ll see they are not hard at all! I’ve seen where people get hung up in many ways, such as not knowing how to visualize the data, or…

You can expect the 70-761 exam to cover new features of SQL Server 2016, namely JSON and temporal tables. Focus on these if you're familiar with 70-461.

The 70-761 Exam covers SQL Server 2016.  Since this exam covers querying data, most of the topics covered in the 70-461 Exam are also covered, so if you’re familiar with that exam you’re in luck.

If not, I would recommend checking out our study guide, as it details every topic covered by the 70-761 exam.

The purpose of this post is to highlight added topics, which fortunately are few.

JSON –…

Whats the Difference between 70-461 and 70-761 Certification Exam?

Get Started with Power BI Desktop

This is the first in a series of articles on how to Get Started with Power BI.  For those of you unaware, Microsoft Power BI is an analytics tool you can use to create interactive visual reports.

The best part is it’s free!

In this guide I’ll show you how to get started using Power BI.  We’ll then get some sample data and use it to build a simple project.

The idea is to get you up and…

WHILE Loops in T-SQL Stored Procedures

In this lesson, we’re going to learn about the While Loop. So, what is a While Loop? Well, While Loop is set up using a While statement. While statements are used to repeatedly execute a block of SQL statements. That’s where the Loop phrase comes from because we’re kind of going around and around in a loop as we execute the statements.

After watching this video you’ll be able to declare…

Working with TSQL Variables in Stored Procedures

I recently put together a lesson on how to work with TSQL variables.  It is part of my Stored Procedures Unpacked course.  When you create stored procedures, you can encapsulate logic securely, and part of this process is using TSQL variables to temporarily store and manipulate values.

After watching this video you’ll be able to declare TSQL variables and understand how they are used in…