You can use the SQL UPDATE statement to update multiple columns. The trick is to specify the columns you wish to update in the SET clause. All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks database. You can get started using these free tools using my Guide Getting […]
Continue reading
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
The purpose of this article is to introduce you to SQL window functions and some of the high-level concepts. In SQL, a window function refers to a function, such as sum or average, which acts upon a result set’s rows relative to the current row. There are a lot of details to cover, but you’ll […]
Continue reading
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, […]
Continue reading
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 […]
Continue reading
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 […]
Continue reading
A very common question I get asked is whether I can have a CASE Statement in the WHERE Clause. There are a number of 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 […]
Continue reading
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 […]
Continue reading
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 […]
Continue reading
In the 70-761 exam covers the SQL Server 2016. Since this exam covers the data query, most of the topics that are included in the 70-461 exam are also covered. So, if you are familiar with that exam, then you are in luck! If not, then I would recommend that you check out our study […]
Continue reading
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 […]
Continue reading
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 […]
Continue reading
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 […]
Continue reading
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
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 […]
Continue reading
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 […]
Continue reading
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 […]
Continue reading
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) […]
Continue reading
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 […]
Continue reading
Whether you are a database administrator, programmer, or an SQL specialist, who is looking for employment, your resume will often be the deciding factor whether or not you get hired. According to The Motley Fool, 40% of employment managers spend less than a minute reading through a resume. They look for elements such as an […]
Continue reading
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 […]
Continue reading
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 […]
Continue reading
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
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 […]
Continue reading
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
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
After you have read this article, you will get to learn the basics of a dynamic SQL, how to build statements based on variable values, and how to execute constructed statements using sp_executesql and EXECUTE() from within a stored procedure. All examples found in this lesson are based on the Microsoft SQL Server Management Studio […]
Continue reading
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 […]
Continue reading
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 […]
Continue reading
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
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 […]
Continue reading
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
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 […]
Continue reading
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
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 […]
Continue reading
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 […]
Continue reading
“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 […]
Continue reading
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 […]
Continue reading
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
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
Studying and preparing for the 70-761 can be intimidating. There are many topics you need to cover, but with so little time. It can be overwhelming! So why not create a 70-761 study plan and reduce that stress? The plan will help you in understanding what topics to study, the study order, and how many […]
Continue reading
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
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 […]
Continue reading
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 […]
Continue reading
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. […]
Continue reading
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
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
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
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
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 […]
Continue reading
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 […]
Continue reading
In this puzzle, we are going to learn how to join dirty data using the SQL Server. There are many instances that you are forced to use someone else’s data. Since it is their system and data (not yours), then you can’t always fix the inconsistently formatted data before it is in the database. So […]
Continue readingIn 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 readingIn 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 […]
Continue reading
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 […]
Continue reading
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 […]
Continue reading
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 […]
Continue reading
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 […]
Continue reading
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
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
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 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
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 […]
Continue reading
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 […]
Continue reading
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. […]
Continue reading
In this article, we’ll discover data modification statements used to modify data in the SQL server using the INSERT, UPDATE, DELETE, and MERGE statements. The SELECT, INSERT, UPDATE, DELETE and MERGE statements are collectively referred to as DML (Data Manipulation Language) statements. These statements allow you to view and modify data. We extensively cover SELECT in […]
Continue reading
A cross join is used when you wish to create a 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 […]
Continue reading
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
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 […]
Continue reading
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, […]
Continue reading
Recursive CTE’s This article will be discussing recursive Common Table Expressions (CTEs). Recursive CTEs are unique, such that they are allowed to reference their own. With this special ability, you can use recursive CTEs in solving problems where other queries cannot. Recursive CTEs are best in working with hierarchical data such as org charts for […]
Continue reading
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
The 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 article, we’ll introduce you to common table expressions, the two types of the CTEs, and their uses. In addition, we’ll […]
Continue reading
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. […]
Continue reading
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. […]
Continue reading
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
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 are sure to practice using the examples! All the examples for this lesson are based on […]
Continue reading
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
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
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 […]
Continue reading
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
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 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 changing 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 SQL […]
Continue reading
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
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 […]
Continue reading
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 […]
Continue reading
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 […]
Continue reading
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 […]
Continue reading
This article will teach you the difference between a primary key and a foreign key. This article will also teach you why both of these keys are important when it comes to the maintenance of a relational database structure. All the examples for this lesson are based on Microsoft SQL Server Management Studio and the […]
Continue reading
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
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
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
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 […]
Continue reading
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
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 […]
Continue reading
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
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 […]
Continue reading
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
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. […]
Continue reading
This article is the fourth in a series of articles about subqueries, and we will be discussing how to use a subquery in the FROM clause. In other articles, it covered the uses in other clauses. All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can […]
Continue reading
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 […]
Continue reading
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
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, […]
Continue reading
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
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
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
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.
Continue reading
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 […]
Continue reading
I recently had the opportunity to interview Jack Vamvas about SQL, Optimization, and what it takes to become a Junior DBA. The interview is full of gems involving practical advices.
Continue reading
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
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 […]
Continue reading
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
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
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 data […]
Continue reading
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 […]
Continue reading
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 […]
Continue reading
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
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 […]
Continue reading
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
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
Database Joins are important to master. As you progress from a beginner to advanced beginner, you’ll soon need to combine data from more than one table. To do this, you’ll use the database joins. In this series of articles I’ll show you how to write a query that combines, or joins, data from more than […]
Continue reading
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 […]
Continue reading
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
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 […]
Continue reading
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 our examples, we’ll use the Sales Staff Information shown below as our starting point. As we described in the last […]
Continue reading
Introduction to Database Normalization Database normalization is a process used to organize a database into tables and columns. The main idea with this is that a table should be about a specific topic and only supporting topics included. Take a spreadsheet containing the information as an example, where the data contains salespeople and customers serving […]
Continue reading
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
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 […]
Continue reading
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 GROUP BY Clause The SQL GROUP […]
Continue reading
In today’s lesson, you’re going to learn about grouping results returned from your queries using the SQL 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 […]
Continue reading
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 […]
Continue reading
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
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
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 […]
Continue reading
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
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
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 […]
Continue reading
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 […]
Continue reading
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
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
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 […]
Continue reading
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
Continue reading
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
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
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
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
You will learn how to query Microsoft SQL Server using the SQL SELECT statement in this series of lessons. Once you have read this lesson you’ll be able to: Identify all tables in a SQL Server SSMS (SQL Server Management Studio) Database. Understand how to list a table’s columns. To view columns, create text and […]
Continue reading
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 […]
Continue readingIn 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 […]
Continue readingThis 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 […]
Continue reading