≡ 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

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

1 comment
Subqueries in Select Statement

This is the second in a series of articles about subqueries.  In this article we discuss subqueries in the SELECT statement’s column list.  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 Select Statement

When a subquery is placed within the column list it is used to return single values.  In this case you can think of the subquery as single value expression.  The result returned is no different than the expression “2 + 2.”  Of course subqueries can return text as well, but you get the point!

When working with sub queries, the main statement is sometimes called the outer query.  Subqueries are enclosed in parenthesis, this make them easier to spot.

Be careful when using subqueries.  They can be fun to use, but as you add more to your query they can start to slow down your query. [click to continue…]

0 comments
Introduction to Sub Queries

The purpose of this article is to introduce you to subqueries and some of their high-level concepts.  There are a lot of details to cover in order to learn sub queries, but you’ll see we cover those in depth in later articles.

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.

Introduction to Subqueries

Subqueries provide a powerful mean to combine data from two tables in to a single result.  Subqueries are sometimes called nested queries.  As the name implies, subqueries contain one or more queries, one inside the other.

Subqueries are very versatile and that can make them somewhat hard to understand.  For most cases a subquery can be used anywhere you can use an expression or table specification.

For example, you can use subqueries in the SELECT, FROM, WHERE, or HAVING clauses.  Depending on its use, a subquery may return one or more rows, or in other cases, such as when used in a SELECT clause, be restricted to returning a single value.

In later articles we’ll get into the specifics on using subqueries throughout your SELECT statements.  They can be quite slippery and hard to wrap your brain around.  Perhaps it would help to see a couple of examples. [click to continue…]

0 comments

What is a Stored Procedure?

What is a stored procedure

What is a Stored Procedure?

A stored procedure is a group of one or more database statements stored in the database’s data dictionary and called from either a remote program, another stored procedure, or the command line.  Stored procedure are commonly called SPROCS, or SP’s.  Stored procedure features and command syntax are specific to the database engine.  Traditionally Oracle uses PL/SQL as its language; whereas, SQL Server uses T/SQL. [click to continue…]

3 comments

Interview with Eugenio Tacchini

tile

In this post I interview Eugenio Tacchini, author of DaDaBIK and college lecturer, and ask him what he feels beginning SQL students should learn and what he sees as some of their major frustrations. [click to continue…]

0 comments