Basic SQL Queries using AdventureWorks

In a previous post, we walked through installing SQL Server 2008 and the Adventure Works databases. Upon successful install, we were able to connect to our database server and expand the installed databases.

Let’s take a moment to explore our surroundings a little bit. Click the plus sign next to AdventureWorks2008R2. You’ll see a number of tables within the database. I have to assume some basic level of acquaintance with databases, but succinctly put, tables are to databases what tracks are to CDs (I wonder how long that analogy will last). If you expand a table, say HumanResources.Department, you find features that are appropriate to tables contained within. The two features you’re likely to be most interested to start with are columns and constraints.

In the above picture, we can observe that we have four columns titled, “DepartmentID”, “Name”, “GroupName”, and, “ModifiedDate”. In addition, we have a default constraint on the ModifiedDate column. Constraints are quite important in databases, you can learn about them more here as well as in Books Online (BOL), the help built into SQL Server Management Studio (SSMS). You may find the BOL entry by clicking, “Help” inside of SSMS and clicking, “search”. At that point, just type in “Constraints”, and you’ll find an entry constraints in the section on understanding tables. It’s a good read.

We can assume that this table models departments and divisions inside of the AdventureWorks organization. So let’s begin to query the database. Right click on AdventureWorks2008RS and select, “New Query”.

Let’s start by just returning all rows and all columns from with HumanResources.Department. We can do this by typing out in the new query window, “SELECT * FROM HumanResources.Department”.

It’s best practice to return only the columns you need. For example, let’s just return the Name and GroupName. To do so let’s type, “SELECT Name, GroupName FROM HumanResources.Department”.

Of course, it’s also best practice to only return the rows you need as well. There’s a lot of reasons for this, sometimes from a managing data perspective, other times from a server load perspective. In general, it just follows from a good principle of living, take only what you need and no more. To return precisely the number of rows you need, we will need use the WHERE clause in our query. Let’s suppose we need the Departments under Executive General and Administration. To do so, let’s use the following query, “SELECT Name, GroupName FROM HumanResources.Department WHERE GroupName = ‘Executive General and Administration”.

These queries work fine when we only need data out of a single table. Querying data out of a single table is a rare event. SQL Server is a relational database. Intuitively, a relational database is a technology that describes relationships between objects via one or more tables. A common example of this would be employees have departments and vice versa. What if we wanted to know what employees worked in what departments? Well, we need to figure out where the relationships lie within the database. There’s a couple of ways to do this. My favorite is to draw a picture. Many of my mathematics professors in college would say, when in doubt, draw a picture. So let’s draw a picture. Let’s right click on Database Diagrams right above the tables. It’ll ask to configure the database for diagrams. Click okay and once that’s done, you’ll get a window asking what table you want to diagram. Scroll through and find the Departments table.

Click, “Add” and then click, “Close. You’ll have a window that should look like the picture below.

Let’s right click on the Departments table and select, “Add Related Tables”.

The related table will drop right on top of the first table. Click and drag it off, right click it and select, “Add Related Tables”.

Before we move on, let’s think about what we want. We want to pull information held in two separate tables out. To do this and not have it be a Cartesian product (commonly referred to as a CROSS JOIN), we will need to find the intersecting attribute that unites the data between the tables. This means we must use a JOIN. In particular, we want the stuff that is only in both tables or sets. To this end, we’re going to employ the INNER JOIN operator.

Let’s look at this screen grab of a handy Mathematica demonstration I downloaded:

I want the rows from table A that have red circles in table B. The syntax would be as follows: SELECT A.* FROM A INNER JOIN B ON A.RedCircles = B.RedCircles. That would only give us the elements in table A that have corresponding elements in tables B.

Let’s move back into the world of AdventureWorks. I’m going to want to find out who in the Person table has a company credit card assigned to them and get the ID of that credit card. This is a great opportunity for you to dive back into the Database Diagram tool and see if you can find the column that is common between the two tables.

Let’s check the row counts on the two tables by themselves. We want uneven tables so we can see the operator for as it. SELECT * FROM Person.Person returns 19972 rows. SELECT * FROM Sales.PersonCreditCard returns 19918 rows. We have our match. To illustrate my point, I will perform an INNER JOIN on those tables.

As an aside, you may be wondering why I use all caps for certain words in my queries. It’s common practice to capitalize T-SQL syntax when writing a query and leaving non-SQL operators and clauses as lowercase. It’s a readability issue but I would suggest strongly in adopting it as a practice.

Returning to the query, let’s get all the data out of the Person.Person table and match it with the Credit card ID of the Sales.PersonCreditCard table.

Note the row count matches the row count of the Sales.PersonCreditCard table. That means there’s rows in the Person.Person table that do not have a matching entry in the Sales.PersonCreditCard table. What if we wanted those rows as well? Enter the LEFT JOIN operator! Let’s make a small adjustment to the query and see what happens.

19972 rows! We’ve seen that number before! Note the duplicate rows and note that rows without a matching BusinessEntityID in the Sales.PersonCreditCard table have NULL in the CreditCardID column. You can also use the RIGHT JOIN operator if you need all rows from the right table in your query. Here’s plenty of documentation on joins here.

So let’s get back to the original example. We need to get data that spans three tables. Not a problem? Not a problem!

As you can see, we can span INNER JOINs across tables as we need. Nifty.

So what next? Inserts, updates, and deletes? Why not? We’ve come this far, let’s just power through this last little bit and then we’ll be on to Mathematica as a front end for querying SQL Server! Understanding how to interact with databases directly will give us a background to all things we do. Fundamentals! It’s all about fundamentals.

We refer to adding new data to a database as an INSERT. The syntax is usually quite basic. You’ll often see the syntax in two forms. INSERT INTO <Table> VALUES (<Value 1>, <Value 2>, … , <Value k>). You’ll use this syntax when you’re inserting a value for every single column in the table. The database will insert each value (assuming that it doesn’t break any rules) in a manner of left to right. Sometimes, when you’re inserting data, you’ll have NULL values for a particular column. If that’s the case, you’ll want to use the following syntax, INSERT INTO <Table> (<Column 1>, <Column 2>, … , <Column k>) VALUES (<Value 1>, <Value 2>, … , <Value k>). The assumption here is that there’s a break in the sequence. Let’s do a quick example.

The DepartmentID column has an identity so it auto-incremented when we inserted the Name and GroupName and the default constraint took over and inserted a timestamp since I didn’t have a value in its place. The SELECT query shows that the values took. That’s an insert.

UPDATES follow the same in the same fashion. UPDATE < Table> SET <Column> = <Value> WHERE <Other or same column> = <Value>. The WHERE clause is really important here. Without it being present, every row would be updated. Eek.

And to verify:

You might note that I have multiple queries and I’ve highlighted one in each of the screenshots I’ve taken. In SSMS (and in many other database management tools), if you highlight a query or parts of a query and attempt to execute that query, SQL Server will only try to execute the portion of the query that’s highlighted. This is very handy for running queries and testing to ensure that you wrote the query accurately.

Finally, there’s DELETE. If you want to delete all rows, just type DELETE FROM <Table>. Bingo! Empty table. Of course, the nuclear option isn’t usually the appropriate option. So you’ll want to use a WHERE clause to ensure that you’re removing the only row(s) you wish to remove. In that case, you do something like this, DELETE FROM <Table> WHERE <Column> = <Value>.

A simple SELECT query returns no results so we know we’ve been successful.

So there it is, the ultra-basics of querying in a nut-shell. Where to next? Well, as promised here’s some fantastic resources that I’ve used and still use as well today.

There’s a lot of great SQL Server blogs and websites out there as well.

I highly suggest getting a book on SQL Server development as well. There’s really no replacing a book. Forums and searches are helpful but that’s information and information is a subset of knowledge. Books contain knowledge. Go to the source.

4 Responses to “Basic SQL Queries using AdventureWorks”

  1. Installing SQL Server 2012 « Cold Logics

    […] time to play! If you’ve never played with SQL Server before, you may wish to get started here. Happy querying! Share this:FacebookRedditTwitterEmailPrintStumbleUponDiggLinkedInLike this:LikeBe […]

  2. kallol samanta

    Informative and Knowledgefull

  3. kumar

    Inner Join’s are easy to understand by mathematical unions and intersections.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s