Note: This is the second in a series of articles covering joins.
The series starts with the article Introduction to Database Joins. 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. In this article we are going to cover inner joins.
An inner join is used when you need to match rows from two tables. Rows that match remain in the result, those that don’t are rejected. The match condition is commonly called the join condition. When the match conditions involve equality, that is matching exactly the contents of one column to another, the join is called an equijoin. You’ll find that most of the joins you’ll use are equijoins. A common situation is where you need to join the primary key of one table to the foreign key of another. This is needed when you are denormalizing data.
[click to continue…]
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…]
This is the fourth in a series of posts teaching normalization.
The third post focused on the second normal form, its definition, and examples to hammer it home.
Once a table is in second normal form, we are guaranteed that every column is dependent on the primary key, or as I like to say, the table serves a single purpose. But what about relationships among the columns? Could there be dependencies between columns that could cause an inconsistency?
A table containing both columns for an employee’s age and birth date is spelling trouble, there lurks an opportunity for a data inconsistency!
How are these addressed? By the third normal form. [click to continue…]
I recently had the opportunity to ask Brian Larson some questions regarding Business Intelligence. For those new to the field, Business Intelligence, or BI for short, is an rapidly expanding field within the database realm.
BI brings with it a different way of thinking! We all struggled to understand normalization and create efficient transactional data models. BI turns this thinking on its head. With BI the emphasis is placed on fast reporting. This mean pre-calculating values and denormalizing data.
BI tools can be as simple as using add-ins in Excel to as complex as stand-along database products.
I got a lot out of hearing what Brian had to say, and you share the feeling. So with out further delay, enjoy the interview.
[click to continue…]
This is the third in a series of posts teaching normalization.
The second post focused on the first normal form, its definition, and examples to hammer it home.
Now it is time to take a look at the second normal form. I like to think the reason we place tables in 2nd normal form is to narrow them to a single purpose. Doing so bring’s clarity to the database design, makes it easier for us to describe and use a table, and tends to eliminate modification anomalies.
This stems from the primary key identifying the main topic at hand, such as identifying buildings, employees, or classes, and the columns, serving to add meaning through descriptive attributes.
An EmployeeID isn’t much on its own, but add a name, height, hair color and age, and now you’re starting to describe a real person.
So what is the definition of 2nd normal form?
[click to continue…]