Learn how to use the ROW_NUMBER() windows function to generate a unique ID. Problem: You have a table named Teams with information

Read More
Use ROW_NUMBER to Create a Unique ID

Problem We want to compare two moving averages in SQL. We’ll use window functions to do this, as it is easier write

Read More
Compare Two Moving Averages in SQL

Problem You want to remove duplicates in SQL. You know it is easy to find duplicates using GROUP BY, but how do

Read More
Find and Remove Duplicates in SQL

Problem You need to find duplicates and then identify the records by their primary keys. Background Here’s an example of how to

Read More
How to Find Duplicates using a Windows Function?

Problem How do you create a moving average using windows functions in SQL? Background There is no built-in function to create a

Read More
Calculate a Moving Average in SQL using A Windows Function

Problem How do you create a running total on a column using SQL? Background There is no built-in function to create a

Read More
Calculate a Running Total in SQL using a Windows Function

Introduction Window functions are an essential tool for data analysis, particularly in the SQL language. They allow you to perform complex calculations,

Read More
Working with ROW_NUMBER, RANK, and DENSE_RANK

In this article let’s look at window functions vs GROUP BY queries. We’ll do so by looking at how each statement works

Read More
Window Functions vs Group By Queries?

So what happens when you create a view, and then later someone else changes the underlying tables? In this article let’s explore

Read More
CREATE VIEW Side Effects

In this article I’ll show you three ways you can use SQL to concatenate columns into a single expression.  You’ll see as

Read More
Concatenate Text with SQL and how to protect from NULL.

Problem Statement Getting data loaded into your SQL Server tables can come in a variety of methods. There are many mechanisms that

Read More
SQL Server Inserting Multiple Rows

Problem You want to round up to the nearest integer. Background Suppose you have a list of final sales amount that you

Read More
How to Round Up to Nearest Integer

Recently a student asked me how he could calculate a Moving Median.  It’s not as easy as you think, as SQL doesn’t

Read More
Calculate Moving Median in SQL

Problem You need use SQL to calculate the Median of a result from SQL Server. Background Suppose you need to calculate the

Read More
Use SQL to Find the MEDIAN

Problem You need to avoid SQL divide by zero errors. Suppose you need to calculate velocity using the formula v = s

Read More
How to Avoid SQL Divide by Zero

Problem Given a date, you want to subtract 30 days in the past using SQL. Said another way, get the date thirty

Read More
How to Subtract 30 Days from a Date using SQL

Problem You need to figure out the week number from date, assuming Monday is the first day of the week using PostgreSQL,

Read More
How to Find the Week Number in PostgreSQL, MySQL, and SQL Server

Problem You want to replace all occurrences of a substring with a new substring using PostgreSQL, MySQL, or SQL Server. Background The

Read More
Replace using PostgreSQL

In this article we are going to design and create a sample relational database you can use with MySql, PostgreSQL or Microsoft

Read More
Database Design In Four Steps – Build the Sample PizzaDB

SQL Joins are important to master. As you progress from a beginner to advanced beginner, you’ll soon need to combine data from

Read More
SQL Joins – The Ultimate Guide

In this puzzle, we’re going to learn how to do some basic data modeling.  Many of you have expressed an interest in

Read More
Data Modeling Principles in Action

Database normalization is a process used to organize a database into tables and columns. There are three main forms: first normal form

Read More
Database Normalization – in Easy to Understand English

Select Top Rows In this video we’ll walk you though how to select top rows within a query.   We’ll show you

Read More
Select the Top Row from your result.

The main purpose of a SQL CASE expression returns a value based on one or more conditional tests.  Use CASE expressions anywhere

Read More
SQL CASE Statement – Simple and Searched Forms