2

How to use the Choose Function with Select

In this video we’ll walk you though how to use the CHOOSE function with SELECT. You’ll see how the function works and then a practical example using some sample data. Once you’ve gone through this article, I would recommend watching our next Essential SQL Minute to continue learn more about SQL Server! Once you have […]

Continue reading

Common Data Types used in SQL Server

In this video we’ll walk you though the common data types used in SQL Server; you’ll see examples of each type’s values, and how to define them. Once you’ve gone through this article, I would recommend watching our next Essential SQL Minute to continue learn more about SQL Server! Once you have watched the video […]

Continue reading

Build Dynamic SQL in a Stored Procedure

Dynamic SQL

After reading this article you will understand the basics of dynamic SQL; how to build statements based on variable values, and how to execute those constructed statements using sp_executesql and EXECUTE() from within a stored procedure. All the examples for this lesson are based on Microsoft SQL Server Management Studio and the sample databases AdventureWorks […]

Continue reading

Using the WHILE Statement in Stored Procedures

After reading this article you will understand the basics of using the WHILE statement to write a loop within a stored procedure. All the examples for this lesson are based on Microsoft SQL Server Management Studio and the sample databases AdventureWorks and WideWorldImporters.  You can get started using these free tools with my Guide Getting Started […]

Continue reading

Use IF…ELSE Statements to Program a Stored Procedure

After reading this article you will understand the basics of programming a stored procedure using IF…ELSE statements; most importantly how keywords, such as BEGIN, END, IF and ELSE affect the order of execution within your stored procedures. All the examples for this lesson are based on Microsoft SQL Server Management Studio and the sample databases […]

Continue reading

How to use the BETWEEN operator to compare a range of values in SQL SERVER Queries

In this video we’ll walk you though how to use the BETWEEN operator; we’ll show you why you would want to use the BETWEEN operator and how to avoid pitfalls when using it with the DATETIME type. The BETWEEN operator is equivalent to using AND combination of >= and <= comparison operators, but makes for […]

Continue reading

Introduction to Stored Procedures for SQLServer

Stored Procedures

After reading this article you will understand the benefits of stored procedures and how to create and execute them. All the examples for this lesson are based on Microsoft SQL Server Management Studio and the sample databases AdventureWorks and WideWorldImporters.  You can get started using these free tools using my guide Getting Started Using SQL Server. […]

Continue reading

Calculate a Running Total in SQL with Joins or Window Functions

There are several ways to calculate a running total in SQL.  In this article, we will cover two methods:  Joins, and Window Functions. We’ll first look at how to calculate the running total using an INNER JOIN.  By doing so, you’ll not only learn more about join conditions, but see how to take the result […]

Continue reading

What is the difference between a primary and unique key?

Primary and Unique Key

In this article we learn the difference between a primary and unique key, 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 […]

Continue reading

EssentialSQL 70-761 Resource Page

70-461 Exam Preparation

To help you get the most out your studying time, I put together this 70-761 resource page to help you succeed in getting ready for your certification.  These resources help you prepare to take the 70-761 Querying Microsoft SQL Server Certification exam. Microsoft’s Official 70-761 Resource Page When preparing for the 70-761, your first stop […]

Continue reading

Joins versus Subqueries SQL Puzzle

Joins versus Subqueries

In this puzzle, we’re going to learn about joins versus subqueries.  In many queries you can substitute joins and subqueries.  Yet, since each has their strengths, it isn’t wise to do so.  Once you have worked through this puzzle you see some of the benefits and disadvantages of joins versus subqueries. Solving puzzles is a […]

Continue reading

What is the difference between a subquery and inner join?

In this puzzle, we’re going to learn how to rewrite a subquery using inner joins.  Knowing about a subquery versus inner join can help you with interview questions and performance issues.  Though subqueries have unique abilities, there are times when it is better to use other SQL constructs such as joins. By reading this article […]

Continue reading

Learn how to Calculate the Median Value using PERCENTILE_DISC

In this puzzle we’re going to learn how to find the person whose birthday, among others, is in the middle.  Knowing how to calculate the median value is a good skill to have.  As you start to explore business intelligence you’ll come across similar problems to solve. Also, be sure to check out the bonus […]

Continue reading

Learn to use the Data Dictionary in SQL Server

In this puzzle, we’re going to learn how to query the data dictionary using SQL Server.  Knowing how to query the data dictionary is good to know.  There are many questions you can answer about your database through the data dictionary. For instance, do you know how many of your table don’t have a primary […]

Continue reading

Learn how to Work Joins and Messy Data

In this puzzle, we’re going to learn how to join dirty data using SQL Server.  Many times you’re forced to use someone else’s data.  Since it is their system and data, not yours, you can’t always fix the inconsistently formatted data before it is in the database. So how can you use it?  Do this […]

Continue reading

Work with the AdventureWorks Bill of Materials using Subqueries

In this puzzle, we’re going to learn how to work with the AdventureWorks Bill of Materials table.  Companies use a BOM (Bill of Materials) to itemize the products that are used or contained in another product. Solving puzzles is a great way to learn SQL.  Nothing beats practicing what you’ve learned.  Once you have figured […]

Continue reading

SQL Puzzle:  How to Calculate Moving Averages

In this puzzle, we’re going to learn how to calculate moving averages by working through a hypothetical stock market example. Solving puzzles is a great way to learn SQL.  Nothing beats practicing what you’ve learned.  Once you have figured out the puzzle, post you answer in the comments so we all can learn from one […]

Continue reading
1

Rank Data with SQL

In this puzzle, we’re going to work through a problem to identify the top and bottom 25 percent to rank student scores. Solving puzzles is a great way to learn SQL.  Nothing beats practicing what you’ve learned. SQL Puzzle Question In today’s puzzle assume you’ve been asked by the head of the Mathematics department to […]

Continue reading

What is the Difference Between Merge and Update?

When modifying one table based on the contents of another it may be tempting to simply use the merge statement, but before you throw away all other forms of modifying data, I think it’s important for you to know the difference between merge and update. What is the Difference Between MERGE and UPDATE? Both the […]

Continue reading

Introduction to the Merge Statement

Introduction to the MERGE Statement and SQL Server Data Modification The MERGE statement is used to make changes in one table based on values matched from anther.   It can be used to combine insert, update, and delete operations into one statement.  In this article, we’ll explore how to use the MERGE statement.  We discuss some […]

Continue reading

Introduction to the INSERT Statement using SQL Server

INSERT Statement

The INSERT statement is used to add rows to a SQL Server data table.  In this article we’ll explore how to use the INSERT statement.  We discuss some best practices, limitations, and wrap-up with several examples. This is the second article in a series of articles.  The first is entitled Introduction to SQL Server Data […]

Continue reading

Learn how to Add, Remove, or Modify values in a SQL Table

Metamorphisis

In this article we’ll discover data modification statements used to modify data in SQL server using the INSERT, UPDATE, DELETE, and MERGE statement. The SELECT, INSERT, UPDATE, DELETE, and MERGE statement are collectively referred to DML (Data Manipulation Language) statements.  These statements allow you to view and modify data.  We extensively cover SELECT in other […]

Continue reading

Learn How to Combine Data with a CROSS JOIN

Cross Join Meme

A cross join is used when you wish to create combination of every row from two tables.  All row combinations are included in the result; this is commonly called cross product join.  A common use for a cross join is to create obtain all combinations of items, such as colors and sizes. Note:  The series […]

Continue reading

Uncommon SQL Server Data Types

In this article we’re going to go over a uncommon SQL server data types.  These are those you won’t use every day, but you’ll want to know if you take the 70-461 exam. Even if you don’t plan on taking the 70-461, learning these data types is fun, especially the spatial datatype (I had no idea […]

Continue reading

Recursive CTEs Explained

Recursive CTE’s In this article we explore recursive CTE’s (Common Table Expressions).  Recursive CTEs are special in the sense they are allowed to reference themselves!  Because of this special ability, you can use recursive CTEs to solve problems other queries cannot.  Recursive CTEs are really good at working with hierarchical data such as org charts […]

Continue reading

Non Recursive CTEs Explained and Why to Use Them

Introduction to Non Recursive CTEs In this article we explore non recursive CTEs (Common Table Expressions).  This is a broad class, and basically covers every form of CTEs except those that call themselves.  This other class is called the recursive CTEs; they are covered in the next article. If you’re unfamiliar with CTEs I would […]

Continue reading

Introduction to Common Table Expressions (CTE’s)

Common Table Expressions or CTE’s for short are used within SQL Server to simplify complex joins and subqueries, and to provide a means to query hierarchical data such as an organizational chart.  In this set of articles, we’ll introduce you to common table expressions, the two types, and their uses.  In this article we’ll introduce […]

Continue reading
4

Know when to use Cast versus Convert

In this article we’re going to explore the CAST and CONVERT functions to understand whether there are any significant differences worth knowing when we want to convert data from one type to another. Since CAST and CONVERT can be used interchangeably in most situations, you may be wondering if one is better than the other. […]

Continue reading
1

What is the Difference between Inner and Outer Joins?

Both inner and outer joins can be used to combine data from two or more tables; however, there are some key differences!  Read this article to find out each command’s strengths and when to use them. To get the most of this and our other lessons be sure to practice using the examples! All the […]

Continue reading
1

Learn to use SQL Server Date Functions

SQL server’s date functions provide you a set of function that you can use to manipulate dates.  The function are used for a wide variety of operation such as adding weeks to a date, calculating the difference between two dates, or to decompose a date into its fundamental parts. If you not familiar with SQL […]

Continue reading

Introduction to SQL Server’s Common String Functions

The built in SQL String functions make it possible for you to find and alter text values, such as VARCHAR and CHAR datatypes, in SQLServer.  Using these functions you can alter a text value such as changing “Smith, Joe” to “Joe Smith.” If you not familiar with SQL functions, then I would recommend staring with […]

Continue reading
1

Introduction to SQL Server’s Mathematical Functions

Mathematical Functions to Old Way - Slide Rule!

Math Rocks! SQLServer includes many mathematical functions you can use to perform business and engineering calculations.  Many of these aren’t used in typical day-to-day operations; however, there are several commonly used functions we’ll cover. If you not familiar with SQL functions, then I would recommend staring with the Introduction to SQL Server Built-In Functions. All […]

Continue reading
2

Introduction to SQL Server’s Built-In Logical Functions

Logical Circuit

Logical functions provide a way to use logical conditions to display one of several values.  You can use logical functions to test a field’s value such as gender (M or F) and display another value(‘Male’ or ‘Female’) as a result. In this article we describe how to use the CHOOSE and IIF functions.  CHOOSE is […]

Continue reading

Built-In Functions (SQL Server)

Built in SQL Functions

Built-In functions are used in SQL SELECT expressions to calculate values and manipulate data.  These functions can be used anywhere expressions are allowed.  Common uses of functions include to change a name to all upper case.  In this article we’ll introduce you to basic concepts. All the examples for this lesson are based on Microsoft […]

Continue reading
3

Converting Between Data Types with SQL Server Functions

SQL server uses data types to store a specific kind of value such as numbers, dates, or text in table columns and to use in functions, such as mathematical expressions. One issue with data types is that they don’t usually mix well.  Conversion functions make them mix better! Though there are time when types are […]

Continue reading
3

Using Conditional Logic in SQL with CASE Expression

Kings Chapel College

The main purpose of a CASE expression is to return a value based on one or more conditional tests.  CASE expressions can be used anywhere in a SQL statement an expression is allowed. Though a truly an expression, some people refer to them as “CASE statements.”  This most likely stems from that use of the […]

Continue reading
10

What is the Difference between a Primary Key and a Foreign Key?

Primary and Foreign Keys

In this article we learn the difference between a primary key and a foreign key, 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 […]

Continue reading
3

How do the SQL DISTINCT and TOP SELECT Clauses Work Together to Generate Results?

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

Continue reading
2

Is it possible to use Joins, Subqueries, and Unions in one Query?

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

Continue reading
4

Write Better SQL – Step 3 of 3

To write sql, we’ll take all the information we’ve compiled in the previous articles and map it to the appropriate SELECT clause.  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 […]

Continue reading

How to Write SQL – Understand Database Table Meanings and Relationships – Step 1

The first step to write SQL is to understand the database tables meanings and relationships.  This is important, as complex queries often span more than one table.  Knowing the purpose of the table and how they relate to one another is key.   The series starts with this introductory article. All the examples for this […]

Continue reading
1

What is a SQL Server Data Dictionary?

Data Dictionary - Cards

What is a Data Dictionary? In SQL Server the data dictionary is a set of database tables used to store information about a database’s definition.  The dictionary contains information about database objects such as tables, indexes, columns, datatypes, and views. The data dictionary is used by SQL server to execute queries and is automatically updated […]

Continue reading

Get Ready to Learn SQL Server 23: Using Subqueries in the HAVING Clause

HAVING Clause Subquery

This is the fifth in a series of articles about subqueries.  In this article we discuss subqueries in the HAVING 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 […]

Continue reading
2

Using A Subquery in the FROM clause

Derived Tables in From Statement

This is the fourth in a series of articles about subqueries.  In this article we discuss using a subquery 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 […]

Continue reading
1

Interview with Alessandro Alpi on Database Life Cycle Management

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

Continue reading

Using Subqueries in the WHERE Clause

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

Continue reading
8

Using Subqueries in the Select Statement

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

Continue reading
4

Learn SQL – Introduction to Subqueries

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

Continue reading
4

Learn about Stored Procedures

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

Continue reading
3

What is the difference between WHERE and HAVING clauses?

In this article learn when to use WHERE and HAVING.  Both perform similar functions, but for different purposes! All the examples for this article 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 do Where and Having Differ? […]

Continue reading
2

What is the Difference between ORDER and GROUP BY?

Differences in Order By versus Group By

The GROUP and ORDER BY clauses are both used to organize data.  Learn which is used to sort the data, and which is used for counting and summing. 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 […]

Continue reading
4

How do I combine results from more than one table?

Combine data in SQL

Many times in a relational database the information you want to show in your query is in more than one table.  This begs the question “How do you combine results from more than one table?” All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database.  You can […]

Continue reading
9

Use SQL Server to Sort Alphanumeric Values

Sort Alphanumeric Values with SQL Server Recently a reader asked me how to sort an alpha numeric field in SQL in natural order.  Alphanumeric values are commonly found and don’t sort naturally using numeric methods. When we have a numeric fields we expect values to be sorted in ordinal order, for example: 1,2,5,7,10,11,15,20,21 However when […]

Continue reading
11

Learn to use Union, Intersect, and Except Clauses

Intersection, Exception, Union

Learn to use Union, Intersect, and Except Clauses The UNION, INTERSECT, and EXCEPT clauses are used to combine or exclude like rows from two or more tables.  They are useful when you need to combine the results from separate queries into one single result.  They differ from a join in that entire rows are matched […]

Continue reading
2

What is a Database NULL Value?

Null and unknown values

What is a Null Value? In databases a common issue is what value or placeholder do you use to represent a missing values.   In SQL, this is solved with null.  It is used to signify missing or unknown values.  The keyword NULL is used to indicate these values.  NULL really isn’t a specific value as much as it […]

Continue reading
1

BI For Beginners – a Business Intelligence Interview with Brian Larson

Business Intelligence

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

Continue reading
40

Database Normalization Explained in Simple English

Database Normalization Explained

Introduction to Database Normalization Database normalization is process used to organize a database into tables and columns.  The idea is that a table should be about a specific topic and that only those columns which support that topic are included. For example, a spreadsheet containing information about sales people and customers serves several purposes: Identify […]

Continue reading
4

Keep is Simple: Easy To Understand Data Modeling Concepts

Relational Database Data Models

When working with SQL databases it is often useful to create diagrams of the database tables and their relationships.  These may be done during the design process, as  your data modeling, or once the database is created, in order to document the tables’ dependencies.  As I explain various concepts in my lessons, I’ll sometimes use […]

Continue reading
2

How to Use GROUP BY to Summarize Query Results

Group and Summarize Data

In today’s lesson you’re going to learn about grouping results returned from your queries using the GROUP BY clause. The objectives of today’s lesson are to: Learn how to group results using GROUP BY Use aggregate functions to perform calculations Understand how to filter groups using the HAVING clause Important! Please follow along and do the […]

Continue reading
2

Use Pattern Matching in SQL to Filter Results

In this lesson you are going to explore how to use pattern matching in SQL with the LIKE operator.  Using this phrase allows us perform partial matches of data values and obtain answers to questions which can’t be done with conventional comparisons. The lesson’s objectives are to: learn about the LIKE match condition understand wild cards Important! Please follow […]

Continue reading
4

Query Results Using Boolean Logic

Old Fashioned Computer

In today’s lesson you’re going to learn how to query results using the WHERE clause. The objectives of today’s lesson are to: Learn to use more than one compare condition at a time using Boolean logic Comprehensive example with Select, Where, and Order By   Using Multiple Conditions to Query Results In previous lesson’s we learned how […]

Continue reading
3

Get Ready to Learn SQL Server:  4. Query Results Using Boolean Logic

In today’s lesson you’re going to learn more about filtering results returned from your queries using the WHERE clause. The objectives of today’s lesson are to: Learn to use more than one compare condition at a time using Boolean logic Comprehensive example with Select, Where, and Order By Important! Please follow along and do the examples […]

Continue reading

How to Filter Query Results

Where Clause

In today’s lesson you’re going to learn how filter the results returned from your queries using the WHERE clause.  This clause is important as only those records matching the where clause’s conditions are returned in the query results. The objectives of today’s lesson are to: Learn about various condition types, such as Equality, Range, and […]

Continue reading
3

How to Filter Query Results

In today’s lesson you’re going to learn how to filter query results using the WHERE clause.  This clause is important as only those records matching the where clause’s conditions are returned in the query results. The objectives of today’s lesson are to: Learn about various condition types, such as Equality, Range, and Membership Comprehensive example […]

Continue reading
4

What are the Major Part of a SQL DB?

Parts of a Database

A SQL database is used to store and retrieve data.  The database is housed in a database server and largely controlled by a database management system.  All SQL databases, whether they MS SQL Server, MySQL, Oracle, or Progress have several components in common.  They are: Tables Indexes Views Stored Procedures Triggers It is these various […]

Continue reading
2

What is ACID (atomicity, consistency, isolation, and durability)?

What are the  ACID Database Properties? The ACID database properties define the key characteristics SQL databases use to ensure database modification are saved in a consistent, safe, and robust manner. ACID is an acronym to remember the key principles of a transnational system.  ACID stands for Atomic, Consistent, Isolation, and Durability.  Here are some informal definitions: […]

Continue reading
9

Getting Started With SQL Server: 2. Sort Your Query Results

In this lesson you are going to explore how to sort your query results by using SQL’s ORDER BY statement.  Using this phrase allows us to sort our result in ascending or descending order.  In addition you can limit your query to a specified number of results. The lesson’s objectives are to: learn how to sort on […]

Continue reading

Learning SQL

Recently I had a chance to ask Allen Taylor questions about learning SQL and his perspective on the learning and using the language.  Allen has worked with computer for over thirty years, and brings a wealth of experience to the table.  Allen has written many books on databases and frequently lectures.  Please be sure to […]

Continue reading
8

Getting Started with SQL Server: 1. Simple SELECT Queries

In this series of lessons you’re going to learn how to query Microsoft SQL Server using the SQL SELECT statement.  Once you have read this lesson you’ll be able to: Identify all the tables in a SQL Server Database Using SSMS (SQL Server Management Studio). Understand how to list a table’s columns. Use the SQL SELECT statement to view […]

Continue reading