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
-
SQL Less Than or Equal To
The SQL Less Than Or Equal To comparison operator (<=) is used to compare two values. It returns TRUE if the first value is less than the second. If the second is less, it returns FALSE. You can also test for Less than by using <. Here are a couple of examples: 10 <=5 FALSE 5 <= 20 TRUE 10 <= 10 TRUE Here is…
-
SQL Greater Than or Equal To Comparison Operator
The SQL Greater Than or Equal To comparison operator (>=) is used to compare two values. It returns TRUE if the first value is greater than or equal to the second. If the second is greater, it returns FALSE. You can also test for greater than or equal to by using >=. Here are a couple of examples: 10 >= 5 TRUE …
-
SQL MIN Function
The SQL MIN function returns the smallest value within a table or group. Throughout this section we’ll use the HumanResource.Employee table for our examples: Using the data above, MIN calculates the smallest SickLeaveHours amount for the entire table: When used with GROUP BY, MIN returns the largest value within a group. Here is a similar query showing the minimum SickLeaveHours by JobTitle: Additional SQL MIN Resources …
-
SQL MAX Function
The SQL MAX function returns the largest value within a table or group. In this section, we’ll use the HumanResource.Employee table for our examples: Using the data above, MAX calculates the largest SickLeaveHours amount for the entire table: When used with GROUP BY, MAX returns the largest value within a group. You can see this in this query showing the maximum SickLeaveHours by JobTitle: Additional SQL MAX Resources To learn…
-
SQL AVG Function
The SQL AVG function returns the average value within a table or group. In this section, we’ll use the HumanResource.Employee table for our examples: Using sample data above, AVG calculates the average SickLeaveHours amount for the entire table: When used with GROUP BY, AVG returns the average value within a group. You can see this in this query showing the maximum SickLeaveHours by JobTitle: Additional SQL AVG Resources To learn more,…
-
SQL Less Than Comparison Operator
The SQL Less Than comparison operator (<) is used to compare two values. It returns TRUE if the first value is less than the second. If the second is less, it returns FALSE. You can also test for Less than or equal to by using <=. Here are a couple of examples: 10 < 5 FALSE 5 < 20 TRUE 10 < 10 FALSE Here…
-
SQL Greater Than Comparison Operator
The SQL Greater Than comparison operator (>) is used to compare two values. It returns TRUE if the first value is greater than the second. If the second is greater, it returns FALSE. You can also test for greater than or equal to by using >=. Here are a couple of examples: 10 > 5 TRUE 5 > 20 FALSE 10 >…
-
SQL SELECT AS
Rename Columns with SQL SELECT AS You can use a form of SQL SELECT AS to rename columns in your query results. So far you’ve seen where queries results return results named after the table columns. This is fine for most cases, but once you start working with expressions, you’ll see this doesn’t work well.…
-
Window Functions in SQL – What are they?
The purpose of this article is to introduce you to SQL window functions and some of the high-level concepts. In SQL, a window function refers to a function, such as sum or average, which acts upon a result set’s rows relative to the current row. There are a lot of details to cover, but you’ll…
-
SQL Server Hierarchical Query using the hierarchyid type
In this SQL minute we’ll look at how to create a SQL server hierarchical query using the hierarchyid data type. This is a two-part article. In part one you were introduced to the hierarchyid type. In this article we dig deeper into the hierarchyid functions. If you’re studying for the 70-761 exam, you’ll find there aren’t…
-
Use hierarchyid to query Hierarchical data
In this SQL minute we’ll look at how you can use hierarchyid to query hierarchy data. This is a two-part article. In this article we look at how to do one query to get portion of the hierarchy. Next week, we will look to see how to query the entire tree. For this problem we…
-
Basic SQL: A Business User’s Guide to Writing Queries
Most people are familiar enough with basic SQL to be able to provide a simple explanation of it. Unfortunately, few know how to use the language or apply it to their business goals. Many see SQL as an advanced language intended for use by data professionals and programmers. In reality, it’s a fairly simple language…
-
SQL Pivot Table – Learn to Create in Six Steps
In this episode of SQL Minute I want to share with you how to create a pivot or cross tab chart. I’ll show you the steps using an example from the adventure works database. I get so many questions on how to create these! Once you get the steps down, you’ll see they are not…
-
Left Join versus Right Join Comparison
I recently put together a lesson on the difference between left join versus right join operators. Outer joins are a great way to match tables, while allowing still including rows from one table that don’t necessarily match to another table. The good news is that when it comes to right outer joins or, its twin,…
-
What is a Non-Equi Join in SQL and What is its Purpose?
A non-equi join can be used to solve some interesting query problems. You can use a non-equi join to check for duplicate value or when you need to compare one value in a table falls within a range of values within another. You can learn more about this this video, it’s transcript follows: What is…
-
How to Make a Join Easier to Read using Table Aliases
I recently put together a lesson on table aliases and multi-table joins. You’ll find that as you write more complicated joins, that the SQL can become harder to read, as you’ll need to qualify column with table names to avoid ambiguity. To make you SQL more compact and more readable you can create table aliases.…
-
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…
-
Full Outer Join as Union
In this puzzle, we’re going to learn how to write a SQL UNION without using Set Operations. Set operations allow us to compare rows from two or more tables to arrive at a result. For several classes of problems, it is much easier to use a set operation, than join data. But can we do…
-
SQL Set Operators
In this puzzle, we’re going learn to about SQL set operators. Set operators allow us to compare rows from two or more tables to arrive at a result. For several classes of problems, is is much easier to use a set operation, than join data. Solving puzzles is a great way to learn SQL. Nothing…
-
Joins vs Subqueries SQL Puzzle
In this puzzle, we’re going to learn about joins vs subqueries. In many queries you can substitute joins and subqueries. Yet, since each has their strengths, it isn’t wise to do so. Once you have worked through this puzzle you see some of the benefits and disadvantages of joins vs subqueries. Solving puzzles is a…
-
Rewrite Subquery as a Join
In this puzzle, we’re going to learn how to rewrite a subquery using inner joins. Knowing about a subquery versus inner join can help you with interview questions and performance issues. Though subqueries have unique abilities, there are times when it is better to use other SQL constructs such as joins. By reading this article…
-
Use SQL to Calculate Median Values.
In this puzzle we’re going to learn how to find the person whose birthday, among others, is in the middle. Knowing how to calculate the median value is a good skill to have. As you start to explore business intelligence you’ll come across similar problems to solve. Also, be sure to check out the bonus…
-
What is a Dynamic Pivot Table?
In this puzzle, we’re going to learn how to create a dynamic pivot table using SQL Server. A dynamic pivot table is a great way to summarize data. And given that Business Intelligence is a hot topic, knowing how to create one is key. By reading this article you’ll learn to create a dynamic pivot…
-
Use SQL Server to Create a Cross Tab Query
In this puzzle, we’re going to learn how to create a cross tab query using SQL Server. Cross tabs are a great way to summarize data. And given that Business Intelligence is a hot topic, knowing how to create one is key. We’ll learn to create a cross tab query. Before you read the entire…
-
How to Find a Gap in a Sequence using SQL SERVER
In this puzzle, we’re going to learn how to find a gap in a sequence using SQL Server. I was recently reviewing my bank statement and noticed they flagged the start of a missing check number with ***’s. I figured with would be a great example for a SQL puzzle as there are many ways…
-
SQL Percentile Rank Puzzle
In this puzzle, we’re going with with SQL Percentile Rank to work through a problem to identify the top and bottom student scores. Solving puzzles is a great way to learn SQL. Nothing beats practicing what you’ve learned. SQL Puzzle Question In today’s puzzle assume you’ve been asked by the head of the Mathematics department…
-
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…
-
SQL DISTINCT and TOP in Same Query
This article is inspired by a series of questions that one of my readers, Nan, recently sent me regarding SQL DISTINCT, TOP, and ORDER BY. All the examples for this lesson 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…
-
Query Plans in SQL Server
All the query plan examples for this lesson 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. Let’s get started and learn about a query plans in SQL Server. What is a Query Plan? A query plan is…
-
Using WHERE EXISTS in SQL
The SQL EXISTS operator is mostly used to test whether a subquery returns rows. It returns TRUE if rows exists in the subquery and FALSE if they do not. SQL Exists The EXISTS condition is used in combination with a subquery. It returns TRUE whenever the subquery returns one or more values. In its simplest…
-
What is the Difference between ORDER and GROUP BY?
Both the GROUP and ORDER BY clauses are used in organizing data. Find out which of the two is used to sort the data and which is used for counting and summing up. All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started…
