Blog
Latest Posts
-
How to include a single quote in a SQL query
In this video we’ll walk you through 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 continue to 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…
-
How to use the SQL IN Operator with an Expression List
In this video we’ll walk you though how to use the SQL 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 SQL IN operator matches a column to a list. Once you’ve gone through this article, I would recommend watching…
-
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…
-
Use SQL to Calculate a Running Total
The running total in SQL can be calculated in several ways. This article will cover two methods: the Joins and the Window functions. We will first look at how to calculate the running total using the INNER JOIN. By doing so, you’ll not only learn more about join conditions, but see how to take the result…
-
Full Outer Join as Union
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 do…
-
SQL Set Operators
In this puzzle, we’re going learn to about SQL set operators. Set operators 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…
-
Joins vs Subqueries SQL Puzzle
In this puzzle, we’re going to learn about joins vs 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 vs subqueries. Solving puzzles is a…
-
Rewrite Subquery as a 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…
-
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…
-
How to Create A Bar Chart Using SQL Server
We are going to learn how to create a bar chart using SQL server in this puzzle. It can sometimes be fun to see what you can do with the SELECT statement. Besides, I figured it would be fun to see if I could create an “old-fashioned” bar chart — this is much like what we used to…
-
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…
-
Select the Top Row from your result.
Select Top Rows 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).…
-
SQL Percentile Rank Puzzle
In this puzzle, we’re going with with SQL Percentile Rank to work through a problem to identify the top and bottom 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…
-
SQL INSERT Statement (Transact SQL)
Use the INSERT statement is to add rows to a SQL Server data table. In this article we explore how to use the INSERT statement. We discuss best practices, limitations, and wrap-up with several examples. This is the second article in a series of articles. The first is Introduction to SQL Server Data Modification Statements.…
-
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…
-
What is the Difference between Cast versus Convert?
In this article, we will be exploring the CAST and CONVERT functions to understand whether there are any significant differences worth knowing when we want to data from one type to another. Since the CAST and CONVERT can be used interchangeably in most situations, you may wonder if one function is better than the other.…
-
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 really…
-
Built-In Functions (SQL Server)
You can use Built-In functions in SQL SELECT expressions to calculate values and manipulate data. Use these SQL functions anywhere expressions are allowed. Common uses of functions include changing a name to all upper case. In this article, we’ll introduce you to basic concepts to use SQL Function in SQL Server, MySQL, PostgreSQL, and Oracle.…
-
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…
-
SQL CASE Statement – Simple and Searched Forms
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 expression is allowed. Though truly an expression, some people refer to them as “CASE statements.” This most likely stems from their use in programming languages. The SQL CASE expression…
-
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 SQL 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…
-
Query Plans in SQL Server
All the query plan 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 plans in SQL Server. What is a Query Plan? A query plan is…
-
Using WHERE EXISTS in SQL
The SQL EXISTS operator is mostly used to test whether a subquery returns rows. It returns TRUE if rows exists in the subquery and FALSE if they do not. SQL Exists The EXISTS condition is used in combination with a subquery. It returns TRUE whenever the subquery returns one or more values. In its simplest…
-
What is the Difference between ORDER and GROUP BY?
Both the GROUP and ORDER BY clauses are used in organizing data. Find out which of the two is used to sort the data and which is used for counting and summing up. All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started…
-
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…
-
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…
-
Keep it 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…
-
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…
-
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 tables. …
-
Database Management System Main Parts
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:
-
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…
