Do You Want to Learn SQL?

Take our free eight day course, where I'll teach you in simple to understand English all you need to get started learning SQL.

-
DAYS
-
HOURS
-
MINUTES
-
SECONDS

Free Beginners SQL Course!

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:

 

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?
    ,

    ·

    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
    ,

    ·

    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

    ·

    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

    ·

    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
    ,

    ·

    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

    ·

    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?

    ·

    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

    ·

    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
    ,

    ·

    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
    ,

    ·

    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
    ,

    ·

    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
    ,

    ·

    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
    , ,

    ·

    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
    , ,

    ·

    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
    ,

    ·

    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
    , ,

    ·

    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
    , ,

    ·

    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.
    ,

    ·

    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?
    , ,

    ·

    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
    ,

    ·

    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

    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

    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?
    ,

    ·

    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
    ,

    ·

    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

    ·

    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

    ·

    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?
    ,

    ·

    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…