SQL Queries
Writing a SQL Query is part art and part craft. I say this as not only do you need to know a query’s syntax and technical considerations, the craft, but you also need to understand how to formulate a query to match real-world use cases, the art.
For if you can take a customer’s request, or yours for that matter, and formulate it into a meaningful SQL statement, not matter how well you understand how a SQL query works, you won’t deliver value.
SQL Queries – Craft
Before we get into the art, let’s explore the craft. I’ve already written a slew of articles to help you. Here are some to get you started writing SQL Queries:
- SQL SELECT
- SQL WHERE – Guide and Examples
- Use SQL ORDER BY to Sort Results
- SQL GROUP BY Guide
- SQL Joins – The Ultimate Guide
- Ultimate Guide to Subqueries
You may already know some of these topics. That’s great! If not, just start with SQL SELECT. It’s a great starting point.
The ART of Writing SQL QUERIES
To be honest, I think this is the hardest part to master. Here you need to learn to “encode” your customers requirements into SQL to get the desired result. It’s frustrating, as SQL is one of those language where you don’t tell the computer “how” to do it, but “what” you want.
For some developers this is a big change, for they are used to writing algorithms to instruct the computer how to do operations.
With SQL you specify what result you want returned, and then it is up to the DBMS (Database Management System) to make it happen.
I’ve tried to convey the process I used to write a query in the Three Steps to Writing SQL. You’ll see I go through all the steps in detail. Once you get the hang, these steps become intuitive. It becomes second nature before long.
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…
-
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…
-
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…
-
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. Group by SQL 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…
-
Mastering SQL FULL JOIN: A Comprehensive Guide
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 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…
-
Common Table Expressions – The Ultimate Guide
The Common Table Expressions or CTE’s for short are used within SQL Server to simplify complex joins and subqueries, and to provide a means to query hierarchical data such as an organizational chart. In this article, we’ll introduce you to common table expressions, the two types of the SQL CTEs, and their uses. In addition,…
-
Is it possible to use Joins, Subqueries, and Unions on Multiple Tables?
A reader recently asked me to help them solve a problem combining data from multiple tables into a single result. What was interesting was that the solution used the three ways to combine data that we have recently covered, namely: joins, set operators, and subqueries. Given the comprehensive nature of this problem I thought it…
-
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…
-
What Is the Difference Between a Join and a UNION?
Use joins and UNIONS to combine data from two or more tables. Read this article to find out each command’s strengths and when to use them. To get the most of this and our other lessons are sure to practice using the examples! All the examples for this lesson are based on Microsoft SQL Server…
-
SQL Union, Intersect, and Except – The Ultimate Guide
The SQL UNION, SQL INTERSECT, and SQL EXCEPT clauses are used to combine or exclude like rows from two or more tables. They are useful when you need to combine the results from separate queries into one single result. They differ from a join in that entire rows are matched and, as a result, included…
-
SQL ANY and ALL Operators
SQL ANY and ALL keywords are used in subquery comparisons to compare a set of value against all values in the result or any one value in the set. Comparison operators such as greater than, less than, equal, and not equal can be modified in interesting ways to enhance comparisons done in conjunction with WHERE…
-
SQL IN Operator
The SQL In operator compare a column to a list. In this article we’ll see how to use In and NOT IN with list generated using subqueries. SQL IN Operator and NOT IN Operator Review The SQL IN operator is considered a membership type. The membership type allows you to conduct multiple match tests compactly…
-
Derived Tables
A derived table is a subquery in the from clause. It returns a table as its result. Unlike other subqueries you name them. In this articles I’ll show you to use derived tables, a how to use their special abilities to work around SQL limitations. Things you can do with Derived Table Subqueries. There are…
-
Correlated Subqueries in SQL
Correlated subqueries are used to tie an inner query’s criteria to values within the outer query. They are powerful technique to avoid “hard coding” values. In this article look at a couple example and compare a correlated sub query to a join. All the examples for this lesson are based on Microsoft SQL Server Management…
-
SQL Bill of Materials Example
In this puzzle we’ll explore a SQL bill of materials example. Companies use a BOM (Bill of Materials) to itemize the components and sub assemblies used to construct their products. 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…
-
Subqueries versus Joins
Use both Joins and subqueries to query data from different tables. Though they may even share the same query plan, are many differences between them. Knowing the differences and when to use either a join or subquery to search data from one or more tables is key to mastering SQL. All the examples for this…
-
SQL Subqueries: Exploring Dynamic and Data-Driven Queries
SQL subqueries make it possible for you to write queries that are more dynamic, and data driven. Think of them as query within another query. In this article I’ll introduce you to subqueries and some of their high-level concepts. I will show you how to write a SQL subquery as part of the SELECT statement. Subqueries return two types of results: …
-
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…
-
SQL COUNT Function
The SQL COUNT function is an aggregate function used to count rows. Use it alone within a SELECT statement to return a count of all rows within a table, or with a GROUP BY to provide a count of rows within each group. Use COUNT(*) to count every record in the grouping or COUNT(expression) to count every record where expression’s result isn’t NULL. Use DISTINCT with…
-
Order of Execution in SQL Explained
Knowing the order of execution in SQL helps you better understand SQL’s “hair pulling” errors! As you start to work with SQL you find that some of the errors don’t make sense or you wonder why you’re able to use a column alias in the ORDER BY clause but not in a join condition. As…
-
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…
-
An Ultimate Guide to Write an SQL Query
The knowledge of SQL has always been in demand. Even if you don’t have a job as a data analyst or any other relevant position, knowing how to write basic SQL queries can be a useful addition to your resume. There are plenty of resources to learn about SQL, including our informative Essential SQL blog.…
-
SQL APPROX_COUNT_DISTINCT Function
Use the Approximate Count Distinct function, APPROX_COUNT_DISTINCT, to return an estimate distinct count of values withing rows. It is best suited for very large tables where performance matters over precision. The latest 2019 version of SQL Server introduced many functions to the system and enriched the database engine in order to make it work faster…