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

Click Here To Get Started!

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…]

5 comments
Primary and Foreign Keys

In this article we learn the difference between primary and foreign keys, and why both are important to maintaining a relational database structure.

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 is the Difference between a Primary Key and a Foreign Key?

Before we can dig into the difference, let’s first explore primary and foreign key characteristics.  Let’s start out by learning about primary keys. [click to continue…]

2 comments
Distinct pattern of terminals

This article is inspired by a series of questions that one of my readers, Nan, recently sent me regarding 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 Using SQL Server.

How does the SQL Top and Distinct SELECT modifiers Work Together to Produce Results? [click to continue…]

0 comments
pattern

A reader recently asked me to help them solve a problem combining data from multiple tables into a single result.  What was interesting was that the solution used the three ways to combine data that we have recently covered, namely:  JOINS, set operators, and subqueries.

Given the comprehensive nature of this problem I thought it would be a great case study for you to read. [click to continue…]

2 comments

How to Write Queries – Write the Query – Step 3

circlepattern

This is the fourth of four articles in a series explaining the three simple steps I take to writing complex SQL statements.  The series starts with this introductory article.

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.

Step 3 – Write the Query

Now that you have your goal defined in terms of a good question it is time to start to hone in on the specifics.  To do this we are going to work through the general structure of SQL:

  • Select List – What columns do we want to include in our query?
  • Join Conditions – What tables are needed to display those columns? Are other tables, such as bridge tables required to “navigate” to those tables?
  • Where Clause – Are there any filter conditions we want to consider? Are the columns we want to filter included in the list of tables?  If not, we’ll have to include them in the join conditions.
  • Order by – How should the results be sorted?

As we work through the structure, we aren’t going to focus on the actual SQL syntax.  That will come later.  The goal is to start to identify and collect all the pieces we’ll need to put the puzzle together.  We’ll use the SQL Coding Worksheet found in Appendix A. [click to continue…]

0 comments
circlepattern

This is the third of four articles in a series explaining the three simple steps I take to writing complex SQL statements.  The series starts with this introductory article.

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.

Step 2 – Pose the Question

Before you start writing SQL, write down the question you are trying to answer.  I find when I’m having a tough time trying to formulate SQL that writing down a description really helps.  Unless the problem is clearly defined, it is hard for me to know how to approach it and find a solution. [click to continue…]

0 comments