SQL for Super Users Part 1: Basic Terminology

As mentioned in my introduction to this series, we’ll be using this series to give super users an overview of some SQL basics. The objective isn’t to turn you into a SQL programmer, but to give you enough knowledge to fix queries and understand what your IT team is driving at.

Throughout this series, we’ll be using the Northwind database. For more info about the series and how to play along at home, do read the introductory post first.

Now, let’s start by covering some basic terminology.

As a super user, you probably already understand some of this basic terminology. But let’s do a quick review to make sure we’re all on the same page.

A table is a collection of data, where each column contains a specific item of data and each row contains a set of items related to one particular key.

For our purposes, a table is just like an Excel sheet.

As an example, let’s look at the first 10 rows of the Products table in Northwind:

We have 10 columns:

1. ProductID
2. ProductName
3. SupplierID
4. CategoryID
5. QuantityPerUnit
6. UnitPrice
7. UnitsInStock
8. UnitsOnOrder
9. ReorderLevel, and
10. Discontinued.

And we have 10 rows that correspond to ProductID, numbered one through 10.

Many people refer to columns as “fields” and rows as “records.” And that’s fine if you know what you mean. But keep in mind that DBA’s and SQL gurus may secretly make fun of you. (Michael J. Swart explains why.)

So, if you want to be cool, you should use the terms columns and rows. As I’m definitely not cool, I still use the terms fields and records sometimes.

In a relationally correct database, every table has a unique column (or set of columns). The values in that column (or set of columns) uniquely identify each record. The column (or columns) is the primary key.

Going back to our example, ProductID is the primary key. It only refers to one description. Always, always, always. It’s the unique value that identifies the record.

So, for example, ProductID 1 refers to only one description, in this case “Chai.”

(It’s possible to have other constraints on a table, but let’s put that aside for now. It’s rarely important when querying data (although it is important when inserting data).

SSMS does you a favor by displaying a nice yellow key to the left of the primary key.

In our example, it’s in the Products table: ProductID:

Let’s look at a few other examples to drive the point home. In the Customers table, it’s CustomerID:

And in the Orders table it’s OrderID:

A side note: In these examples, the primary key happens to occur at the beginning of the table. This ISN’T ALWAYS the case.

Another side note: All of these examples have a single column primary key. However, that ISN’T ALWAYS the case either.

For example, the Order Details table has two columns in the key—OrderID and ProductID. Which makes sense because you can order multiple products on a given order:

In most systems, including GP and NAV, the primary key of the Order Detail table is order and order line, because customers might want to order the same thing twice on a given order.

Primary keys are great. But they aren’t that helpful for reporting. They are helpful when used to bring tables together, a.k.a. “join.”

Let’s go back to the Orders table. You may have noticed (if you were paying attention) that in addition to the gold key, there were also two silver keys, one for CustomerID and one for EmployeeID:

The silver keys indicate that these columns are foreign keys, which means values in these columns MUST exist in another table. (Usually, they’re the primary key of that table.)

In this case, customer values in the Orders table must also exist in the Customers table.

1. When writing a report on orders, you connect (or join) to customers to get the customer name using these keys.
2. In GP and NAV (as well as many other ERP databases), you won’t see those helpful little silver keys. But the concept still holds true. For an order, the customer must exist in the customer table.

(An explanation of why GP and NAV don’t give you these helpful little hints is beyond the scope of these posts.)

So now that we have our basic terms down, let’s move on to create some real code—in our next blog post.