Blog
Latest Posts
-
Master the Art of SQL Query Validation: Best Practices and Essential Tips
Are you looking to master the art of SQL query validation? Look no further! In this article, we will share the best practices and essential tips to help you become a pro at ensuring the accuracy and reliability of your SQL queries. SQL query validation is a critical step in the development process to prevent…
-
Master the Art of Creating Tables in SQL: A Step-by-Step Guide to Database Design
Are you ready to unlock the power of SQL and become a master of database design? Look no further! In this comprehensive step-by-step guide, we will take you on a journey through the art of creating tables in SQL. Whether you are a beginner or an experienced programmer, this guide will provide you with the…
-
Use ROW_NUMBER to Create a Unique ID
Learn how to use the ROW_NUMBER() windows function to generate a unique ID. Problem: You have a table named Teams with information about various sports teams, including their names, points, and conferences. You want to add a unique identifier to each row in the table to track them easily, but you don’t have a unique…
-
Compare Two Moving Averages in SQL
Problem We want to compare two moving averages in SQL. We’ll use window functions to do this, as it is easier write and manage the query using them. Background Moving averages are a commonly used tool in financial analysis that smooths out the price data by averaging the closing prices of a stock over a…
-
Find and Remove Duplicates in SQL
Problem You want to remove duplicates in SQL. You know it is easy to find duplicates using GROUP BY, but how do your remove all but one? Background Here’s an example of how to find duplicate records using a window function in SQL: Suppose we have a table of students that includes columns for student…
-
How to Find Duplicates using a Windows Function?
Problem You need to find duplicates and then identify the records by their primary keys. Background Here’s an example of how to find duplicate records using a window function in SQL: Suppose we have a table of students that includes columns for student ID, name, and age. We want to find all the students who…
-
Calculate a Moving Average in SQL 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 moving average, but you can use the AVG() window function to do so. For our example we calculate the 4 week moving average of weekly sales. Here is the table we’ll use: Note: …
-
Calculate a Running Total 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 running total, but you can use a windows function to do so. In this case well construction a running total of Weekly Product Sales. For this solution we’ll work with the WinCode WeeklySales…
-
Working with ROW_NUMBER, RANK, and DENSE_RANK
Introduction Window functions are an essential tool for data analysis, particularly in the SQL language. They allow you to perform complex calculations, like running totals, moving averages, and ranking values, on a set of data, without the need for subqueries or self-joins. In this article, we’ll explore three popular ranking functions in SQL: ROW_NUMBER(), RANK(),…
-
Window Functions vs Group By Queries?
In this article let’s look at window functions vs GROUP BY queries. We’ll do so by looking at how each statement works and giving examples. Though you can use both GROUP BY and window functions to create summary values, the way SQL includes the results are different. Let’s first look at how GROUP BY does…
-
CREATE VIEW Side Effects
So what happens when you create a view, and then later someone else changes the underlying tables? In this article let’s explore a simple scenario, check out how the SQL view handles the change, and what we can learn from viewing the data dictionary. Create View Scenario Imagine we are accepting data from a table…
-
Concatenate Text with SQL and how to protect from NULL.
In this article I’ll show you three ways you can use SQL to concatenate columns into a single expression. You’ll see as we go through this exercise that CONCAT_WS comes out on top for handling NULL the best! The examples are based on the PizzaDB, git it here! The goal is to combine the FirstName…
-
SQL Server Inserting Multiple Rows
Problem Statement Getting data loaded into your SQL Server tables can come in a variety of methods. There are many mechanisms that SQL Server offers including BULK INSERT, OPENQUERY, loop logic and subquery inserts to consider. Let’s take a look at a few options that can come into play when you need to load multiple…
-
How to Round Up to Nearest Integer
Problem You want to round up to the nearest integer. Background Suppose you have a list of final sales amount that you want to round to make your sales analysis easier. For this example, we’ll use data from the PizzaDB sample database. Here is the query you can use with MS SQL, PostgeSQL, and MySQL…
-
Calculate Moving Median in SQL
Recently a student asked me how he could calculate a Moving Median. It’s not as easy as you think, as SQL doesn’t provide a built-in MEDIAN() function, nor an easy way calculate the “moving” aspect. In this article we’ll calculate a Moving median using SQL Server. Let’s use the PizzaDB sample database for example data.…
-
Use SQL to Find the MEDIAN
Problem You need use SQL to calculate the Median of a result from SQL Server. Background Suppose you need to calculate the Median using SQL. You can loosely define the median as the “middle” value of your data set. If you were calculating the median by hand, you would use the following rules to do…
-
How to Avoid SQL Divide by Zero
Problem You need to avoid SQL divide by zero errors. Suppose you need to calculate velocity using the formula v = s / t Where v = velocity, s = distance, and t = time. Here is the sample data we’ll use. select 100 s, 10 t union all select 110 s, 11 t union all select 2 s, 0 t union all select 10 s, 1 t union all select 120 s, 20 t Line Three is going to cause us issues,…
-
How to Subtract 30 Days from a Date using SQL
Problem Given a date, you want to subtract 30 days in the past using SQL. Said another way, get the date thirty days in the past. We’ll show you to do this using MySQL, PostgreSQL, and SQL Server. Each uses a different approach, so be sure to read each solution to gain an appreciation of…
-
How to Find the Week Number in PostgreSQL, MySQL, and SQL Server
Problem You need to figure out the week number from date, assuming Monday is the first day of the week using PostgreSQL, MySQL, or SQL Server. Background To work through this problem, we’ll use orders for smelt pizza. For each order date we’ll calculate the week number in PostgreSQL. Our example is based on the…
-
Replace using PostgreSQL
Problem You want to replace all occurrences of a substring with a new substring using PostgreSQL, MySQL, or SQL Server. Background The owners of the PizzaDB shops are changing their drinks from Coke to Pepsi. Let’s help them see what the changes would be, before they make them permanent. We’ll write a query show them…
-
Database Design In Four Steps – Build the Sample PizzaDB
In this article we are going to design and create a sample relational database you can use with MySql, PostgreSQL or Microsoft SQL Server. Though SQL is mostly the same within these products, I’ll be sure to point out differences so you can easily follow along using your favorite DBMS (Database Management System). In our…
-
CREATE TABLE SQL
You can use the SQL CREATE TABLE command to create a new table in the database. When you create a table you define the table name, the columns, data types, and any constraints against them. You initially create tables empty, so you’ll need to use the insert statement to add data into it. If you’re…
-
How to Get Today’s Date
Problem You want to use today’s date in your SQL query, but don’t want to type it in each day. Solution You can use GETDATE() to return the current date and time. But to remove the time part case the result as a DATE. Assuming the current date is 2022-02-27, then SELECT CAST(GETDATE() as DATE)…
-
How to Get a Date 30 Days into the Future using SQL
How to Get a Date 30 Days into the Future. Problem You want to calculate a date 30 days into the Future using SQL. We’ll use the AdventureWorks Production.Product table for our example: select ProductID, Name, SellStartDate from Production.Product where productID in(1, 770, 927) Let’s add thirty days to select Product SellStartDates. Solution Use the SQLSERVER DATEADD() function to add 30 days to…
-
How to do an Impossible Join with String Split
In this article I’ll show you how to use STRING_SPLIT() to join two tables. I call this the impossible join. I’ve come across this situation several times and always when dealing with data imported from two different sources. In most case it has to do with users inputting data inconsistently. Let me show you. Watch…
-
Contains in SQL
Sooner or later, you want to know when a column contains in SQL another value. In this article we’ll go over several ways you can test to see whether one value is contained within an another. What makes this problem different, is that we aren’t looking for an exact match, rather, we’re looking for a…
-
What is a SQL Trigger?
What is a Database Trigger? A SQL trigger is special stored procedure that is run when specific actions occur within a database. Most database triggers are defined to run when changes are made to a table’s data. Triggers can be defined to run instead of or after DML (Data Manipulation Language) actions such as INSERT,…
-
SQL HAVING Tutorial
Use SQL HAVING 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…
-
SQL GROUP BY Guide
Use the SQL GROUP BY Clause is to consolidate like values into a single row. The group is a set of columns. The group by returns a single row from one or more within the query having the same column values. Its main purpose is this work alongside functions, such as SUM or COUNT, and provide a…
-
SQL SUM Function
The SQL SUM function returns the total value within a table or group. In its simplest use, SUM() totals a column for all results and returns a single value. In this example the query returns the total orders for all SalesOrderDetail records SELECT SUM(LineTotal) AS OrderTotal FROM Sales.SalesOrderDetail You can also filter your results prior to totaling. Here the SUM() is restricted to two…
-
What is the difference between WHERE and HAVING clauses?
In this article learn when to use WHERE and HAVING. Both perform similar functions, but for different purposes! All the examples for this article are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started using these free tools using my Guide Getting Started Using SQL Server. How do Where and Having Differ?…
-
SQL WHERE LIKE
In this article you’ll learn to use the LIKE operator in your SQL. In most situations you’ll find yourself using LIKE within the WHERE clause to filter data using patterns such as all values beginning with “S.” Using this phrase allows us perform partial matches of data values and obtain answers to questions which can’t…
-
How do I use the Like Clause in SQL Server?
In this video we’ll walk you though how to use the LIKE clause. We’ll show you how to you can perform partial matches, such as being able to match all names beginning with “B” or ending in “Y.” We’ll also show you how to use use ranges. For instance you may want select all…
-
SQL WHERE – Guide and Examples
In today’s lesson, you’re going to learn how to filter query results using the SQL WHERE clause. This clause is important as only those records matching the where clause’s conditions are returned in the query results. The objectives of today’s lesson are to: Important! Please follow along and do the examples in your database. If you…
-
Use SQL ORDER BY to Sort Results
The SQL ORDER BY clause is used to sort your query result in ascending or descending order. Once you have written a query, you’ll naturally want to reorder the results. You can do so using the ORDER BY clause. SQL ORDER BY is versatile. Use the ORDER BY keyword to sort results with a SELECT statement. You can…
-
What is the Difference between TOP and OFFSET & Fetch?
Both TOP and OFFSET & FETCH can be used to limit the number of rows returned. OFFSET and FETCH can return similar results to top, but there are differences which may influence which method is best for you to use in your given situation. All the examples for this lesson are based on Microsoft SQL…
-
Using OFFSET and FETCH with the ORDER BY clause
In this article we explore the OFFSET and FETCH clauses. OFFSET and FETCH are used in conjunction with the SELECT statement ORDER BY clause to provide a means to retrieve a range of records. The starting row to return is determined by the OFFSET value and the maximum number of rows to return from that…
-
SQL TOP
Some times you just need to use SQL TOP to reduce the number of rows shown in your result. This is handy for troubleshooting for showing a summary, such as the first in a list. SQL TOP Video I put together a video to help you understand TOP. If covers the main topics presented in…
-
SQL SELECT
You will learn how to query Microsoft SQL Server using the SQL SELECT statement in this series of lessons. Once you have read this lesson you’ll be able to: Important! Please follow the examples in your database and do them. If you haven’t already done so, sign up for my Guide to Getting Started with…
-
Inner Join vs Outer Join Clauses
Its is easy to get confused between inner join vs outer join clauses. Both inner and outer joins are used to combine data from two or more tables; however, there are some key differences! Read this article to find out each command’s strengths and when to use them. To get the most of this and…
-
SQL FULL Join
The SQL FULL JOIN combines results from a left and right outer join into one result. Or in other words, it is an inner join including unmatched rows from both the left and right tables. This is the key difference between a SQL FULL JOIN and inner join. Where an inner join returns rows matching the join condition, a FULL outer join guarantees all table rows…
-
SQL Right Join
The SQL RIGHT JOIN Includes all rows from the Right table and those that match from the left. For that doesn’t match from the right, NULL is returned in the corresponding columns. This is the key difference between a SQL RIGHT JOIN and INNER JOIN. Where an inner join only returns rows matching the join condition, in a Right outer join, it’s guaranteed all the Right tables rows are included in the…
-
SQL Left Join
The SQL LEFT JOIN Includes all rows from the left table and those that match from the right table. When the right table doesn’t match the join condition, the query returns null values for those columns. This is the key difference between a LEFT JOIN and inner join. Where an inner join only returns rows matching the join condition, a SQL…
-
SQL Joins – The Ultimate Guide
SQL Joins are important to master. As you progress from a beginner to advanced beginner, you’ll soon need to combine data from more than one table. To do this, you’ll one of several SQL join types. In this series of articles I’ll show you how to write a query that combines, or joins, data from…
-
SQL Cross Join
Use SQL cross joins when you wish to create a combination of every row from two tables. All row combinations are included in the result; this is commonly called cross product join. A common use for a cross join is to create obtain all combinations of items, such as colors and sizes. What is a…
-
SQL Inner Join
Use an SQL INNER JOIN when you need to match rows from two tables. Rows that match remain in the result, those that don’t are rejected. The match condition is commonly called the join condition. When the match conditions involve equality, that is matching exactly the contents of one column to another, the sql inner…
-
SQL Self Join
In a SQL self join the table is joined to itself. You can think of it being like an inner join. The idea is to match rows using a join condition. Here is the general syntax for a SQL self join: SELECT a.column1, b.column2, …FROM table1 aINNER JOIN table1 b ON a.matchColumn = b.matchColumn This…
-
SQL Data Conversions
Implicit data conversion Implicit conversions are those conversions that occur automatically whenever the CAST or CONVERT functions aren’t used. Not all values can be implicitly converted to another data type. The following chart shows what can be implicitly converted for the common data types we’ve previously covered: Please keep in mind this chart shows what…
-
SQL Puzzle: How to Calculate Moving Averages
In this puzzle, we’re going to learn how to calculate moving averages by working through a hypothetical stock market example. Solving puzzles is a great way to learn SQL. Nothing beats practicing what you’ve learned. Once you have figured out the puzzle, post you answer in the comments so we all can learn from one…
-
What is a Recursive CTE (Common Table Expression)?
Recursive CTE’s Recursive CTEs are unique, such that they are allowed to reference their own. With this special ability, you can use recursive CTEs in solving problems where other queries cannot. Recursive CTEs are best in working with hierarchical data such as org charts for the bill of materials. If you’re unfamiliar with CTE’s, then…
