≡ 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
Derived Tables in From Statement

This is the fourth in a series of articles about subqueries.  In this article we discuss subqueries in the FROM clause.  Other articles discuss their uses in other clauses.

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.

Using Subqueries in the FROM clause

When subqueries are used in the FROM clause they act as a table that you can use to select columns and join to other tables.  Because of this some people argue they really aren’t subqueries, but derived tables.  I like to think of derived tables as a special case of subqueries… subqueries used in the FROM clause!

Regardless of what you call them, there are some unique features derived tables bring to the SQL world that are worth mentioning.

Before we jump into those though, let’s start with the basics the get familiar with them. [click to continue…]

0 comments
Database Life Cycle

I recently had the opportunity to interview Alessandro Alpi, a SQL Server MVP, regarding SQL and ALM (Application Life cycle Management).  In this interview we talk about the tool and importance of managing your code and static data with version control software such as Git.  In addition we talk about testing.

All these topics struck a chord with me as I’ve been a developer for more than twenty years and I find each of these topics key to managing program source code.  Though I have a really good handle on this in the programming world, I didn’t when it came to databases.

I really learned alot through this interview and I hope you do to! [click to continue…]

1 comment

What is a Query Plan?

What is a Query Plan

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 a Query Plan?

A query plan is a set of steps that the database management system executes in order to complete the query.  The reason we have query plans is that the SQL you write may declare your intentions, but it does not tell SQL the exact logic flow to use.  The query optimizer determines that.  The result of that is the query plan

In SQL Server a query plan is called an execution plan. [click to continue…]

0 comments
Sub query in Where Clause

This is the third in a series of articles about subqueries.  In this article we discuss subqueries in the WHERE clause.  Other articles discuss their uses in other clauses.

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.

Using Subqueries in the WHERE Clause

It is very common to use subqueries in the WHERE clause.  A common use is to test for existence using EXISTS or IN.  In some cases it may make sense to rethink the query and use a JOIN, but you should really study both forms via the query optimizer before making a final decision.

The comparison modifiers ANY and ALL can be used with greater than, less than, or equals operators.  Doing so provides a means to compare a single value, such as a column, to one or more results returned from a subquery.

Let’s now explore these in detail

[click to continue…]

0 comments

What is a Database Trigger?

What is a database Tigger?

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 a Database Trigger?

A database trigger is special stored procedure that is run when specific actions occur within a database.  Most triggers are defined to run when changes are made to a table’s data.  Triggers can be defined to run instead of or after DML (Data Manipulation Language) actions such as INSERT, UPDATE, and DELETE.

Triggers help the database designer ensure certain actions, such as maintaining an audit file, are completed regardless of which program or user makes changes to the data.

The programs are called triggers since an event, such as adding a record to a table, fires their execution.

Triggers and their implementations are specific to database vendors.  In this article we’ll focus on Microsoft SQL server; however, the concepts are the same or similar in Oracle and MySQL. [click to continue…]

2 comments