SQL How To
This archive contains slew of posts on how to use SQL to solve problem. If you have a question about a particular issue, or technique, you may find a solution here.
For instance, here is one of our more “fun” article on how to use SQL to create a bar chart: How to Create A Bar Chart Using SQL Server
If you’re studying for the 70-761 Querying Microsoft SQL Server certification, these two articles are a must read:
How to Create a 70-761 Study Plan Today!
9 Best Study Tips for 70-761 Certification
All the examples for this lesson are based on Microsoft SQL Server Management Studio and the sample databases AdventureWorks and WideWorldImporters. You can get started using these free tools using my guide Getting Started Using SQL Server
Latest Posts
-
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…
-
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…
-
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…
-
How do I combine results from several SQL tables (hint: there are three ways)
Many times in a relational database the information you want to show in your query is in more than one table. This begs the question “How do you combine results from more than one table?” All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can…
-
Replace Missing Values in your Queries using ISNULL
When working with NULL, you’ll often find yourself wanting to replace these “missing” values, with something else. Just use ISNULL to do so. In this video I’ll walk through some of the example and you’ll see it is pretty easy to use. If you like what you are seeing, then why not get some super…
-
How To Find Duplicates in SQL using Window Functions
If you want to find duplicate rows in SQL, you can go two routes. The first is to use the GROUP BY and HAVING to identify “duplicate” groups of data, or you can use a easy to understand window function to not only identify the duplicates but also its primary key. In this video I’ll…
-
Three Tips To Learn SQL Easily
If you have ever have wanted to start learning SQL but didn’t know where to start because you were frustrated, then let me show you three tips to learn SQL. You can use these tips to get started learning SQL today! I want to Learn SQL but Don’t Know Where to Start It is daunting…
-
Automatically Format Your SQL
Hey, welcome back to another SQL Minute. Today we’re going to learn how to code like a boss by delving into formatting your code. Now, some of you may have some rules you use to format code. Others may just type it out and wonder how they should format it. What I’m going to do…
-
70-761 Exam: Newly Added Topics
In the 70-761 exam covers the SQL Server 2016. Since this exam covers the data query, most of the topics that are included in the 70-461 exam are also covered. So, if you are familiar with that exam, then you are in luck! If not, then I would recommend that you check out our study…
-
Get Started with Power BI
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…
-
How do I handle a “Error converting data type” error?
A reader recently asked about a error converting data type error they received. Since this is a common issue, especially when numeric data is stored withing VARCHAR datatypes, I thought you would appreciate the answer I shared with them. I’m using Windows 10 and SQL SMS 2014. When I run a query, I get the…
-
How to use the Choose Function with Select
In this video we’ll walk you though how to use the CHOOSE function with SELECT. You’ll see how the function works and then a practical example using some sample data. Once you’ve gone through this article, I would recommend watching our next Essential SQL Minute to continue learn more about SQL Server! Once you have…
-
Avoid the 5 Most Common SQL Mistakes Beginners Make
Over the years there I’ve seen students makes these common SQL mistakes. They are really easy to avoid, and once you know the solution, you’ll find you write SQL much faster and with less tears and frustration than before. All the examples for this lesson are based on Microsoft SQL Server Management Studio and the…
-
How to include a single quote in a SQL query
In this video we’ll walk you through how to include a single quote in a SQL query; we’ll show how to escape the single quote, so it is treated as text rather than text delimiters. Once you’ve gone through this article, I would recommend watching our next Essential SQL Minute continue to learn more about…
-
How to use the IN Operator with a SubQuery
In this video we’ll walk you though how to use the IN operator with a Subquery; we’ll show you why you would want to use IN with a subquery. In it’s simplest form the IN statement matches a column values to a list. TRUE is returned if there is a match. A main advantage of…
-
How to use the SQL IN Operator with an Expression List
In this video we’ll walk you though how to use the SQL IN operator; we’ll show you why you would want to use the IN operator and how to avoid some pitfalls associated with NULL. The SQL IN operator matches a column to a list. Once you’ve gone through this article, I would recommend watching…
-
How to use the SQL BETWEEN operator
In this video we’ll walk you though how to use the SQL BETWEEN operator; we’ll show you why you would want to use the BETWEEN operator and how to avoid pitfalls when using it with the DATETIME type. The BETWEEN operator is equivalent to using AND combination of >= and <= comparison operators, but makes…
-
Use SQL to Calculate a Running Total
The running total in SQL can be calculated in several ways. This article will cover two methods: the Joins and the Window functions. We will first look at how to calculate the running total using the INNER JOIN. By doing so, you’ll not only learn more about join conditions, but see how to take the result…
-
How to Create a 70-761 Study Plan Today!
Studying and preparing for the 70-761 can be intimidating. There are many topics you need to cover, but with so little time. It can be overwhelming! So why not create a 70-761 study plan and reduce that stress? The plan will help you in understanding what topics to study, the study order, and how many…
-
Learn how to Work Joins and Messy Data
In this puzzle, we are going to learn how to join dirty data using the SQL Server. There are many instances that you are forced to use someone else’s data. Since it is their system and data (not yours), then you can’t always fix the inconsistently formatted data before it is in the database. So…
-
How to Create A Bar Chart Using SQL Server
We are going to learn how to create a bar chart using SQL server in this puzzle. It can sometimes be fun to see what you can do with the SELECT statement. Besides, I figured it would be fun to see if I could create an “old-fashioned” bar chart — this is much like what we used to…
-
Select the Top Row from your result.
Select Top Rows In this video we’ll walk you though how to select top rows within a query. We’ll show you how to retrieve the top or bottom rows in the result. We’ll also show you how to use one sort to select the rows, and once completed, another sort to present them (tricky).…
-
Calculate the Last Day of the Month using SQL
When working with SQL dates, sometimes you need to calculate the end of the month. Months are tricky! Some are 28 days, others 30 or 31, and now and then there’s a leap year! So, given a date, how do you calculate the number of days remaining in the month? The calculation is really a…
-
9 Best Tips for 70-461 Certification
You can do this! Pass exam 70-461. It may seem intimidating, but you can pass Exam 70-461 Querying SQL Server. All it takes is a bit of planning and basic study tips. AsapScience explains in less than four minutes study habits you can use today to get the most out of your exam prep time.…
-
How can I find Duplicate Values in SQL Server?
In this article find out how to find duplicate values in a table or view using SQL. We’ll go step by step through the process. We’ll start with a simple problem, slowly build up the SQL, until we achieve the end result. By the end you’ll understand the pattern used to identify duplicate values and…
-
Learn the Three Crucial Steps to Write Better SQL
You can learn to write SQL. It isn’t hard. Yes, there are many details to mind, but none of it is impossible. In this series of articles I’ll show you the three steps I go through to write complex queries. All the examples for this lesson are based on Microsoft SQL Server Management Studio and…
