≡ Menu

Essential SQL

Want to Get Started Using SQL Server?

Databases Have Beautiful Structure

Now it is time to learn SQL in simple English.

  • Receive our free guide Getting Started with SQL Server!
  • Understand fundamental database concepts
  • Take your job to the next level.

Enter your email below to get our free guide to Getting Started with SQL Server.

Tiling_by_Patterns_stock

Introduction

One of the biggest issues beginning SQL writers have is being able to write queries that use more than one table.  In this series of articles we are going to show you how to write a query that combines, or joins, data from more than one table. Once you have gone through the examples you will understand how to write the basic commands to make this happen and why data is separated in the first place.

This first article introduces the concept of joining tables.  The focus is going to be more on the type of joins, not necessarily their syntax.  The later articles focus on the various types of joins.  Through the narrative and examples you’ll become very comfortable with each one.

In my prior articles you learned about the need to normalize to make it easier to maintain the data.  Though this makes it easier to maintain and update the data, it makes it very inconvenient to view and report information.  Typically the information you need to see has to be cross referenced across several tables for you to see the full picture. [click to continue…]

3 comments

Interview with Jack Vamvas

Interview with Jack Vamvas

I recently had the opportunity to interview Jack Vamvas about SQL, Optimization, and being a DBA.

This interview is sprinkled with many gems of practical advice.

[click to continue…]

0 comments

What is the difference between WHERE and HAVING clauses?

WhereVersusHaving

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?

When working with more advanced SQL it can be unclear when it makes sense to use a WHERE versus a HAVING clause.

Though it appears that both clauses do the same thing, they do it in different ways.  In fact, their functions complement each other.

  • A WHERE clause is used is filter records from a result.  The filter occurs before any groupings are made.
  • A HAVING clause is used to filter values from a group.

Before we go any further let’s review the format of an SQL Statement.  It is

SELECT
FROM
WHERE
GROUP BY
HAVING

To help keep things straight I like to think of the order of execution of SQL statements as from top to bottom.  That means the WHERE clause is first applied to the result and then, the remaining rows summarized according to the GROUP BY. [click to continue…]

0 comments

What is the Difference between GROUP BY and ORDER BY

Differences in Order By versus Group 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 Using SQL Server.

How do GROUP BY and ORDER BY Differ?

This is a question I’m frequently asked.  On the surface both clauses appear to do the same thing; that is sort sort data. But this is where their similarities end.  In fact, both serve entirely different purposes.

  • The ORDER BY clause’s purpose is to sort the query result by specific columns.
  • The GROUP BY clause’s purpose is summarize unique combinations of columns values.

Before we get into their differences consider the general setup of the SELECT statement:

SELECT columnlist
From table
GROUP BY columnA, columnB
ORDER BY columnlist

Notice that the ORDER BY clause appears at the end.  You can use this as a clue to understand that the ORDER BY statement is used to sort the final result of the query.  In fact, it can be used to sort results from a GROUP BY clause.  Confused?  I was at first!

Ok, let’s break it down. [click to continue…]

0 comments

How Do I combine results from more than one table?

Combine data in SQL

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 Using SQL Server.

What are the ways I can combine results from more than one query?

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

SQL wouldn’t be a very useful language if it didn’t provide an easy means to do so.  Fortunately there are three main ways you can combine data from multiple tables.  We’ll go over these briefly here and provide links to more in-depth articles.

Three Main Ways to Combine Data

Data in relational database tables are organized into rows and columns.  As we investigate ways to combine data, keep in mind that the end result will be to either add more columns to a result, perhaps from another relate table, or rows, by taking a set of rows from two or more tables.

When most people learn to combine data they lean about:

  1. JOIN – You can use joins to combine columns from one or more queries into one result.
  2. UNION – Use Unions and other set operators to combine rows from one or more queries into one result.
  3. Sub Queries – Sometimes called nested queries, these can be used to perform a separate search in the database showed results can be used in another query.

[click to continue…]

0 comments

How Do I Use SQL Server to Sort Alphanumeric Values?

Dutch Alphabet

Sort Alphanumeric Values with SQL Server

Recently a reader asked me how to sort an alpha numeric field in SQL in natural order.

When we have a numeric fields we expect values to be sorted in ordinal order, for example: 1,2,5,7,10,11,15,20,21

However when these numbers are in character field, such as char or varchar, the sort becomes  alphabetic and the ordering not what we may wish:  1,10,11,15,2,20,21,5,7.

For numerals in a text field we could easily convert the values to numeric values and sort; however, this option isn’t available if the column values contain alphanumeric values.

NOTE:  This is somewhat of an intermediate to advanced article.  I’m assuming that you already know of the LEFT, SUBSTRING, and CONVERT functions.  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 Using SQL Server. [click to continue…]

4 comments