2

5 Typical SQL Syntax Mistakes

For a beginner, learning any kind of programming language can be challenging and can cause you to make mistakes quite often. However, this is how we learn. Learning SQL syntax programming involves the same concept. After a while, you will start to see common patterns in those mistakes, and you will learn more as we […]

Continue reading

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

Continue reading
1

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

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

70-761 Certification and Study Guide

70-461 Exam Preparation

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

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

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
2

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
23

Foreign and Primary Key Differences (Visually Explained)

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
5

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
13

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
20

How do I combine results from several SQL tables (hint: there are three ways)

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
6

SQL Inner Join to Two or More Tables

Use an SQL INNER JOIN 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 sql inner […]

Continue reading
1

BI For Beginners 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
2

Do Partial Matches Using LIKE

Pattern Matching with 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 […]

Continue reading
1

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

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

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
7

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
2

SQL ACID Explained

What are the  ACID Database Properties? The ACID database properties define SQL database key properties to ensure consistent, safe and robust database modification when saved. ACID is an acronym that helps to remember the fundamental principles of a transnational system. ACID stands for Atomic, Consistent, Isolation, and Durability.  Here are some informal definitions: Atomic – In a transaction […]

Continue reading
3

How to sort results using SQLite

Order

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

Continue reading
>