blog - Essential SQL

SQL Coalesce Function and NULL

Knowing when to use the SQL COALESCE function is a lifesaver when you’re dealing with NULL. As you know, NULL is a tricky concept, and it seem what ever NULL “touches” in an expression, it renders the result NULL. So, when you’re dealing with NULL, how can you break out of the cycle? That is, […]

SQL Server Hierarchical Query using the hierarchyid type

In this SQL minute we’ll look at how to create a SQL server hierarchical query using the hierarchyid data type. This is a two-part article. In part one you were introduced to the hierarchyid type. In this article we dig deeper into the hierarchyid functions. If you’re studying for the 70-761 exam, you’ll find there aren’t […]

Use hierarchyid to query Hierarchical data

In this SQL minute we’ll look at how you can use hierarchyid to query hierarchy data. This is a two-part article. In this article we look at how to do one query to get portion of the hierarchy. Next week, we will look to see how to query the entire tree. If you’re studying for […]

Can I have a CASE Statement in the WHERE Clause?

A common question I get ask is whether I can have a CASE Statement in the WHERE Clause. There are so many examples of CASE being used in SELECT columns, or in ORDER BY that we tend to forget CASE can be used wherever an expression is expected. Where Can I use a CASE Statement? […]

Basic SQL: A Business User’s Guide to Writing Queries

Most people are familiar enough with basic SQL to be able to provide a simple explanation of it. Unfortunately, few know how to use the language or apply it to their business goals. Many see SQL as an advanced language intended for use by data professionals and programmers. In reality, it’s a fairly simple language […]

Create a SQL Pivot Table in Six Steps

In this episode of SQL Minute I want to share with you how to create a pivot or cross tab chart. I’ll show you the steps using an example from the adventure works database. I get so many questions on how to create these! Once you get the steps down, you’ll see they are not […]

70-761 Exam: Newly Added Topics

The 70-761 Exam covers SQL Server 2016. Since this exam covers querying data, most of the topics covered in the 70-461 Exam are also covered, so if you’re familiar with that exam you’re in luck. If not, I would recommend checking out our study guide, as it details every topic covered by the 70-761 exam. […]

Get Started with Power BI Desktop

This is the first in a series of articles on how to Get Started with Power BI. For those of you unaware, Microsoft Power BI is an analytics tool you can use to create interactive visual reports. The best part is it’s free! In this guide I’ll show you how to get started using Power […]

WHILE Loops in T-SQL Stored Procedures

In this lesson, we’re going to learn about the While Loop. So, what is a While Loop? Well, While Loop is set up using a While statement. While statements are used to repeatedly execute a block of SQL statements. That’s where the Loop phrase comes from because we’re kind of going around and around in […]

Right vs Left Outer Join Differences

I recently put together a lesson on the difference between right vs left outer join operators. It is part of my Join Together Now course. Click here get the course at a discount. Outer joins are a great way to match tables, while allowing still including rows from one table that don’t necessarily match to […]

TSQL Variables in Stored Procedures

I recently put together a lesson on how to work with TSQL variables. It is part of my Stored Procedures Unpacked course. When you create stored procedures, you can encapsulate logic securely, and part of this process is using TSQL variables to temporarily store and manipulate values. After watching this video you’ll be able to […]

Debugging Stored Procedures in SQL Server

I recently put together a lesson on debugging stored procedures. It is part of my Stored Procedures Unpacked course. When you debug stored procedures, you’re able to run their code line by line, to easily troubleshoot logic issues. If you’re not familiar with debugging stored procedures you’ll want to watch this introductory video; as it […]

Create Stored Procedures – Your First Walk Through

I recently put together a lesson on how to create stored procedures. It is part of my Stored Procedures Unpacked course. When you create stored procedures, you can encapsulate logic securely, while allowing client program an easy way to execute it. I put this walk through together so that you’ll see how the whole process […]

SQL Stored Procedures – why do we need them?

I recently put together a lesson on why we need SQL stored procedures. It is part of my Stored Procedures Unpacked course. SQL Stored procedures are a great way to encapsulate logic securely, while allowing client program an easy way to execute it. The good news is that writing SQL stored procedures isn’t as hard […]

Error 3154 – Restore SQL database is terminating abnormally

As the word “Restore” suggests, this Microsoft SQL server error 3154 is associated with SQL Backup. Even though SQL Administrators have vast experience in setting and regulating of SQL database and its backup, they get stuck when they experience SQL database error 3154 because this error is displayed when users try to restore SQL database (DB) […]

How do I handle a “Error converting data type” error?

A reader recently asked about a error converting data type error they received. Since this is a common issue, especially when numeric data is stored withing VARCHAR datatypes, I thought you would appreciate the answer I shared with them. I’m using Windows 10 and SQL SMS 2014. When I run a query, I get the […]

What is a Non-Equi Join in SQL and What is its Purpose?

I recently put together a lesson on non-equi joins. It is part of my Join Together Now course. Click here get the course at a discount. A non-equi join can be used to solve some interesting query problems. You can use a non-equi join to check for duplicate value or when you need to compare […]

How to Make a Join Easier to Read using Table Aliases

I recently put together a lesson on table aliases and multi-table joins. It is part of my Join Together Now course. Click here get the course at a discount. You’ll find that as you write more complicated joins, that the SQL can become harder to read, as you’ll need to qualify column with table names […]

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

Avoid the 5 Most Common SQL Mistakes Beginners Make

Over the years there I’ve seen students makes these common SQL mistakes. They are really easy to avoid, and once you know the solution, you’ll find you write SQL much faster and with less tears and frustration than before. All the examples for this lesson are based on Microsoft SQL Server Management Studio and the […]

What is a Database Cursor?

After reading this article you will understand what a database cursor is, see an example, and understand when to use them in 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 using […]

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

Build Dynamic SQL in a Stored Procedure

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

How to include a single quote in a SQL query

In this video we’ll walk you though how to include a single quote in a SQL query; we’ll show how to escape the single quote, so it is treated as text rather than text delimiters. Once you’ve gone through this article, I would recommend watching our next Essential SQL Minute to continue learn more about […]

How to use the IN Operator with a SubQuery

In this video we’ll walk you though how to use the IN operator with a Subquery; we’ll show you why you would want to use IN with a subquery. In it’s simplest form the IN statement matches a column values to a list. TRUE is returned if there is a match. A main advantage of […]

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

How to use the IN operator with an Expression List

In this video we’ll walk you though how to use the IN operator; we’ll show you why you would want to use the IN operator and how to avoid some pitfalls associated with NULL. The IN operator matches a column to a list. Once you’ve gone through this article, I would recommend watching our next […]

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

How to use the SQL BETWEEN operator

In this video we’ll walk you though how to use the SQL 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 […]

Introduction to Stored Procedures for SQLServer

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

TOP Clause in SQL

In this Video we’ll show you how to use the TOP clause to return a portion of the result. This comes in handy in situations you’re required to return the top or bottom ten entries of a list. This video is an excerpt for our SQL 101 video class. You can learn more about SQL […]

Use SQL to Calculate a Running Total

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

Here is What You’ll Need to Learn SQL Server

“How do I start learn SQL Server?” That is the most popular question I’m asked. Many people just don’t know where to start. I understand! SQL is a simple enough language to write basic queries, but putting together all the pieces to get started isn’t straight forward. So, if you’re looking to get started learning […]

9 Best Study Tips for 70-761 Certification

Check out these 9 best study tips I came across in to help you with 70-761 certification. If you’re planning on studying for exam 70-761, the I would recommend that you put these good habits into practice today. You’ll see that cramming isn’t effective, so its best to plan ahead. 9 Best Study Tips Whether […]

What is the difference between a 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 […]

EssentialSQL 70-761 Resource Page

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

How to Create a 70-761 Study Plan Today!

Getting ready to study for the 70-761 can be intimidating! There are some many topics to study and so little time to do so. It can be overwhelming! So why not create a 70-761 study plan and reduce that stress? The plan will help you understand what topics to study, the order to study them, […]

70-761 Certification and Study Guide

Recently Microsoft released “Exam 70-761: Querying Data with Transact-SQL – Microsoft.” To help you study for this certification, I put together my 70-761 study guide. This guide is an update from the prior, which covered the 70-461 certification. The study guide includes every topic you should study to pass the 70-761. The guide is also […]

How to use SQL Union in Set Operations

In this puzzle, we’re going to learn how to write a SQL UNION without using Set Operations. Set operations allow us to compare rows from two or more tables to arrive at a result. For several classes of problems, it is much easier to use a set operation, than join data. But can we […]

SQL Server and Set Operations

In this puzzle, we’re going to about SQL set operations. Set operations allow us to compare rows from two or more tables to arrive at a result. For several classes of problems, is is much easier to use a set operation, than join data. Solving puzzles is a great way to learn SQL. Nothing beats […]

Data Modeling Principles in Action

In this puzzle, we’re going to learn how to do some basic data modeling. Many of you have expressed an interest in learning more about data modeling and database design. I figure we could start with a simple case study and let it evolve from there. Solving puzzles is a great way to learn SQL. […]

Joins versus Subqueries SQL Puzzle

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

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

Use SQL to Calculate Median Values.

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

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

What is a Dynamic Pivot Table?

In this puzzle, we’re going to learn how to create a dynamic pivot table using SQL Server. A dynamic pivot table is a great way to summarize data. And given that Business Intelligence is a hot topic, knowing how to create one is key. By reading this article you’ll learn to create a dynamic pivot […]

Use SQL Server to Create a Cross Tab Query

In this puzzle, we’re going to learn how to create a cross tab query using SQL Server. Cross tabs are a great way to summarize data. And given that Business Intelligence is a hot topic, knowing how to create one is key. We’ll learn to create a cross tab query. Before you read the entire […]

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

Use Subquery with Bill of Materials

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

How do I use OFFSET and FETCH NEXT?

In this video we’ll walk you though how to use OFFSET and FETCH NEXT. We’ll show you how you can use these clauses to return a portion of your query results, such as 10 rows starting 1000 rows down. OFFSET and FETCH NEXT are similar to TOP, but I believe they are more powerful. Once […]

How to Create A Bar Chart Using SQL Server

In this puzzle, we’re going to learn how to create a bar chart using SQL Server. Sometimes it’s fun to see what you can do with the humble SELECT statement. Today I figured it would be fun to see if I could create an “old fashioned” bar chart, much like we used to do with […]

How do I use the Like Clause in SQL Server?

In this video we’ll walk you though how to use the LIKE clause. We’ll show you how to you can perform partial matches, such as being able to match all names beginning with “B” or ending in “Y.” We’ll also show you how to use use ranges. For instance you may want select all […]

How to Find a Gap in a Sequence using SQL SERVER

In this puzzle, we’re going to learn how to find a gap in a sequence using SQL Server. I was recently reviewing my bank statement and noticed they flagged the start of a missing check number with ***’s. I figured with would be a great example for a SQL puzzle as there are many ways […]

Return the first rows from your result.

In this video we’ll walk you though how to select top rows within a query. We’ll show you how to retrieve the top or bottom rows in the result. We’ll also show you how to use one sort to select the rows, and once completed, another sort to present them (tricky). For instance you […]

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

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

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

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

Introduction to the Delete Statement

The DELETE statement is used to remove rows from a SQL Server data table. In this article we’ll explore how to use the DELETE statement. We discuss some best practices, limitations, and wrap-up with several examples. This is the fourth article in a series of articles. You can start at the beginning by reading Introduction to […]

Use SQL UPDATE to Query and Modify Data

The SQL UPDATE statement is used to change data within columns of a SQL Server data table. In this article we’ll explore how to use the UPDATE statement. We discuss some best practices, limitations, and wrap-up with several examples. This is the third article in a series of articles. You can start at the beginning […]

Introduction to the INSERT Statement using SQL Server

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

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

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

Learn How to Combine Data with a CROSS JOIN

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

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

Calculate the Last Day of the Month using SQL

When working with SQL dates, sometimes you need to calculate the end of the month. Months are tricky! Some are 28 days, others 30 or 31, and now and then there’s a leap year! So, given a date, how do you calculate the number of days remaining in the month? The calculation is really a […]

How to Study for the 70-461 Exam

Preparing for the 70-461 Exam In order to be successful studying for the 70-461 exam, you’ll need a good strategy and plan. Before we get into how to study for the 70-461, I want to tell you a quick story. Back in 2002 I heard a piece on National Public Radio talking about normal people, […]

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

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

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

9 Best Tips for 70-461 Certification

You can do this! Pass exam 70-461. It may seem intimidating, but you can pass Exam 70-461 Querying SQL Server. All it takes is a bit of planning and basic study tips. AsapScience explains in less than four minutes study habits you can use today to get the most out of your exam prep time. […]

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

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

What Is the Difference Between a Join and a UNION?

Both Joins and UNIONS can be used to combine data from two or more tables. 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 examples for this lesson are based on […]

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

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

Subqueries versus Joins

Joins and subqueries are both be used to query data from different tables and may even share the same query plan, but there are many differences between them. Knowing the differences and when to use either a join or subquery to search data from one or more tables is key to mastering SQL. All the examples […]

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

Introduction to SQL Server’s Built-In Logical Functions

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

Built-In Functions (SQL Server)

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

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

How can I find Duplicate Values in SQL Server?

In this article find out how to find duplicate values in a table or view using SQL. We’ll go step by step through the process. We’ll start with a simple problem, slowly build up the SQL, until we achieve the end result. By the end you’ll understand the pattern used to identify duplicate values and […]

Seven SQL Server Data Types you Must Know

In SQL the columns of a table are defined to store a specific kind of value such as numbers, dates, or text; these are called data types. With over thirty types of SQL server data types to choose from, data types can seem intimidating, but in reality, there are just a few commonly used in […]

Using Conditional Logic with SQL CASE Expression

The main purpose of a SQL CASE expression returns a value based on one or more conditional tests. Use CASE expressions 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 their use in programming languages. The SQL […]

What is the Difference between TOP and OFFSET & Fetch?

Difference between top and offset and fetch

Both TOP and OFFSET & FETCH can be used to limit the number of rows returned. OFFSET and FETCH can return similar results to top, but there are differences which may influence which method is best for you to use in your given situation. All the examples for this lesson are based on Microsoft SQL […]

Using OFFSET and FETCH with the ORDER BY clause

In this article we explore the OFFSET and FETCH clauses. OFFSET and FETCH are used in conjunction with the SELECT statement ORDER BY clause to provide a means to retrieve a range of records. The starting row to return is determined by the OFFSET value and the maximum number of rows to return from that […]

Foreign and Primary Key Differences

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

SQL DISTINCT and TOP in Same Query

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

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

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

Write SQL – Formulate your Question – Step 2

To write SQL it is important to understand the question you wish to ask the database. I know this seems pretty obvious, but you would be surprised how many people get tripped up on this step. Some queries get so complex that people loose sight of the original question or goal. This is the third of […]

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

Learn the Three Crucial Steps to Writing Better SQL

You can learn to write SQL. It isn’t hard. Yes, there are many details to mind, but none of it is impossible. In this series of articles I’ll show you the three steps I go through to write complex queries. All the examples for this lesson are based on Microsoft SQL Server Management Studio and […]

What is a SQL Server Data Dictionary?

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

SQL Skills are Valuable, Learn why!

Are you serious about learning SQL? If so, then get started with free tools using my Guide Getting Started Using SQL Server. Is SQL a Valuable Skill… is it Sought Out? If I was to say that SQL is not a sought out skill would you believe me? Is it possible to safely say that since databases […]

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

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

Interview with Brad Botz on Business Analysts

I recently met Brad Botz in a professional Facebook group we both belong to and found we have much in common. I really like databases and development, and Brad likes to read users’ minds. Well not really, but see Brad is a Business Analyst and his job is to eek user requirements out of users. […]

Using A Subquery in the FROM clause

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

Query Plans in SQL

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. Let’s get started and learn about a query plan. What is a Query Plan? A query plan is a set of steps that […]

Using Subqueries in the 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 […]

What is a Database Trigger?

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

Using Subqueries in the 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 […]

Subqueries – Introduction to a Subquery in SQL

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

Learn about Stored Procedures

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

What Beginning SQL Students Should Learn

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.

SQL Truncate and Delete

In SQL Server there are a couple a ways you can delete rows from a table. You can use the TRUNCATE and DELETE command. Though the end result of both commands is the same, there are very important differences you should know about. Whats the Difference between Truncate and Delete? The TRUNCATE command is like a DELETE […]

What does it take to be a Junior DBA?

I recently had the opportunity to interview Jack Vamvas about SQL, Optimization, and what it takes to become a Junior DBA. This interview is sprinkled with many gems of practical advice.

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

What is the Difference between ORDER and 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 […]

How do I combine results from more than one table?

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

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

How to use the EXCEPT operator

How to use the Except Operator The EXCEPT operator is used to exclude like rows that are found in one query but not another. It returns rows that are unique to one result. To use the EXCEPT operator, both queries must return the same number of columns and those columns must be of compatible […]

What is the SQL INTERSECT Operator?

How to use the Intersect Operator The INTERSECT operator is used to combine like rows from two queries. It returns rows that are in common between both results. To use the INTERSECT operator, both queries must return the same number of columns and those columns must be of compatible data types. Note: All the […]

Combine Table Rows Using UNION

Combine Table Rows Using UNION In this lesson we are going to talk about the UNION clause. You can use the UNION clause to combine table rows from two different queries into one result. Unlike a join, which combines columns from different tables, a union combines rows from different tables. Here is an illustration of […]

Learn to use Union, Intersect, and Except Clauses

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

Introduction to Outer Joins

Outer Joins Outer joins are used to match rows from two tables. Even if there is no match rows are included. Rows from one of the tables are always included, for the other, when there are no matches, NULL values are included. The series starts with the article Introduction to Database Joins. All the examples for […]

What is a Database NULL Value?

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

Inner Join Introduction

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

Introduction to Database Joins

One of the biggest issues beginning SQL writers have is being able to write queries that use more than one table, that is to use database joins. 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 […]

Database Third Normal Form Explained in Simple English

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

BI For Beginners with Brian Larson

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

Database Second Normal Form Explained in Simple English

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

Learn about the First Normal Form and Database Design

This is the second in a series of posts teaching normalization. The first post introduced database normalization, its importance, and the types of issues it solves. In this article we’ll explore the first normal form. For the examples, we’ll use the Sales Staff Information shown below as a starting point. As we pointed out in the […]

Database Normalization Explained in Simple English

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

Keep is Simple: Easy To Understand Data Modeling Concepts

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

What is a Relational Database View?

A database view is a searchable object in a database that is defined by a query. Though a view doesn’t store data, some refer to a views as “virtual tables,” you can query a view like you can a table. A view can combine data from two or more table, using joins, and also just […]

How to Group and Summarize your Results

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 to how to group results using GROUP BY Use aggregate functions to perform calculations Understand how to filter groups using the HAVING clause GROUP BY Clause The […]

How to Use GROUP BY to Summarize Query Results

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

Ten Things a Junior DBA Should Learn

There is more to being a Junior DBA than knowing SQL. The DBA works at the intersection of the database, server, operations group, and developers. A DBA must understand concepts from all these facets of IT as well as be able to draw upon knowledge of their production environment to troubleshoot performance, hardware, and software […]

Do Partial Matches Using LIKE

In this lesson you are going to explore how to do pattern matching with the SQL Where clause 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 clause understand wild cards […]

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

What is SQLite3?

SQLite3 Review SQLite3 is a compact free database you can use easily create and use a database. Though SQLite3 is not a full-featured database, it supports a surprisingly large set of the SQL standard, and is ideal for those just starting to learn SQL as well for developers that need a simple database engine to […]

What are the Top Ten Databases?

Everyone has their favorite DBMS, is your favorite one of the top databases? Though some non-relational products have entered the top ten, the relational paradigm remains firmly entrenched in the database world. However specialized database types such document store databases and wide column stores are gaining in popularity to become top databases. Top of the […]

Query Results Using Boolean Logic

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

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 using Boolean logic. 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 […]

Database Indexes Explained

A database index allows a query to efficiently retrieve data from a database. Indexes are related to specific tables and consist of one or more keys. A table can have more than one index built from it. The keys are a fancy term for the values we want to look up in the index. The […]

How to Filter Query Results

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

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

What is a Database Table?

A relational database is made up of several components, of which the table is most significant. The database table is where all the data in a database is stored, and without tables, there would not be much use for relational databases. Overall Structure of a Database Table A database consists of one or more […]

What are the Major Parts of a DBMS (Database Management System)?

The main objective of a database management system is to store, retrieve, and process data. In order to do this in a safe and efficient manner, several major components work together to achieve this aim. They are: Databases Security Data Dictionary Storage Engine Query Processor

What are the Major Part of a SQL DB?

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

SQL ACID Explained

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

How to sort results using SQLite

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

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

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

Learn to Write Simple Queries with SQLite

In this series of lessons you’re going to learn how to query the sample database using the SQL Select statement. Once you have read this blog and viewed the series of videos you’ll be able to: Identify all the tables in a SQLite database. Understand how to list a table’s columns. Use the SQL Select statement […]

Use SQLBackupAndFTP to Backup your SQLExpress Databases

In this tutorial learn about a dead simple way to back up your SQL databases to the cloud. I’ll show you how to use SQLBackupandFTP to back up a database to OneDrive (SkyDrive). This tool is really great as provides a means to backup SQLExpress databases, which as you know, don’t have a built-in backup […]

How to Install SQLite and the Sample Database

This is the first in a series of lessons devoted to teaching you SQL. We are going to install SQLite and then move on to the fundamentals , which means learning the command line. Do worry it is not as hard as it seem, and really, the payoff is huge. For once you learn SQL […]