Learn how to use the ROW_NUMBER() windows function to generate a unique
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.
Problem We want to compare two moving averages in SQL. We’ll use window functions to do this, as it is easier write
Problem You want to remove duplicates in SQL. You know it is easy to find duplicates using GROUP BY, but how do
Problem You need to find duplicates and then identify the records by their primary keys. Background Here’s an example of how to
Problem How do you create a moving average using windows functions in SQL? Background There is no built-in function to create a
Problem How do you create a running total on a column using SQL? Background There is no built-in function to create a
Introduction Window functions are an essential tool for data analysis, particularly in the SQL language. They allow you to perform complex calculations,
In this article let’s look at window functions vs GROUP BY queries. We’ll do so by looking at how each statement works
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
Sooner or later, you want to know when a column contains in SQL another value. In this article we’ll go over several
Use SQL HAVING to filter summary results from a GROUP BY. It specifies the search condition for the group or aggregate. SQL
Use the SQL GROUP BY Clause is to consolidate like values into a single row. The group is a set of columns. The group
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
In this article learn when to use WHERE and HAVING. Both perform similar functions, but for different purposes! All the examples for
In this article you’ll learn to use the LIKE operator in your SQL. In most situations you’ll find yourself using LIKE within
In this video we’ll walk you though how to use the LIKE clause. We’ll show you how to you can perform
In today’s lesson, you’re going to learn how to filter query results using the SQL WHERE clause. This clause is important as
The SQL ORDER BY clause is used to sort your query result in ascending or descending order. Once you have written a
Both TOP and OFFSET & FETCH can be used to limit the number of rows returned. OFFSET and FETCH can return similar
In this article we explore the OFFSET and FETCH clauses. OFFSET and FETCH are used in conjunction with the SELECT statement ORDER
Some times you just need to use SQL TOP to reduce the number of rows shown in your result. This is handy
You will learn how to query Microsoft SQL Server using the SQL SELECT statement in this series of lessons. Once you have
Its is easy to get confused between inner join vs outer join clauses. Both inner and outer joins are used to combine
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
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
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
