Introduction

The role of SQL

SQL is the language of data. Data is the language of business because every business runs off data. We can say that SQL is the language of business, or at least it is the intermediary language for those of us who straddle the line between technology and business.

It’s hard to imagine a business application without a database. Whether you are dealing with Online Transaction Processing (OLTP), Online Analytical Processing (OLAP), or ad hoc queries, SQL is the key to getting data into and out of the database. To the uninitiated or even the casual user, SQL can seem like magic. SQL is a fourth-generation language (4GL). This means that, unlike most programming languages, we don’t tell the database how to do what needs to be done. We describe what needs to be done and leave the database to decide how best to do what we requested.

SQL allows us to describe the data we need. The database determines which indexes to use, how to join the data in the various tables involved, how to read from the disks, how to isolate dirty data, and so on.

What will be covered

Properly initiated, we can work this magic ourselves.

We will cover how to get data into the database, update records that are already there, and delete data that is no longer needed. We will slice and dice the data any way you can imagine. We will see how to follow relationships across tables and how to apply filters to find the relevant data. We will use built-in aggregate functions to summarize data and look at solving common problems with date and time values, as well as various ways to navigate hierarchical data, such as org charts, sales hierarchies, etc.

Often the hardest part of crafting a query is understanding the data model being used, so we will finish by exploring the data dictionary. This holds all the data that the database tracks about the data being stored. We will use these views to get details about every column in every table in any database. This can substantially shorten the learning curve for learning a new data model.

The focus for this book will be ANSI SQL, but many vendors have varying levels of support for the ANSI standards. All of the samples given in this book have been tested against SQL Server 2012. Most of the examples will work with SQL Server 2008R2. The few cases that will require a new version are noted as the sample is explained.

You can download a free version of SQL Server 2016 Express here.

What won’t be covered

SQL in general is a big topic. Here we will focus on using the language itself. Several topics on the edge will, out of necessity, not be covered, even though each of these topics is very important on its own.

We will ignore most issues of database administration. We will skip over hardware and infrastructure concerns, and ignore the critical issues of redundancy and fault tolerance. We will mention performance only briefly, to showcase how a query could be rearranged to improve performance and discuss some common problems that can cause the database to work harder than it should in order to get the data we are interested in.

Security is everyone’s concern, but in this book, we will leave this concern on the wayside. Just know that it’s everyone’s responsibility to protect the data. Defense in depth requires validations and appropriate constraints in the prompts on the user interface for business applications, validations in the business logic that cannot be bypassed in the user interface, and appropriate constraints in the database. These are important, but will not be directly addressed here.

Database constraints and checks are the domain of data modeling, which we will also be skipping. In each of our discussions, we will be working with a data model, but we won’t delve deeply into how the data model was created or which alternative structures could have been used. In many cases, we will work with a less-than-optimal data model, acknowledging that we often don’t get to work with an ideal data model. The reality is that we are often stuck with the database that we have, which has often grown and mutated beyond its original design.

Note: We will stress the importance of constraints and checks as appropriate, but still accept the reality that we mostly have to live with the database we have, and that we rarely get to design a new database from scratch.

Summary

In this introductory chapter we have looked at the nature of SQL and its importance in modern business applications. We have seen an overview of what we will cover in this book, as well as a partial list of some of the topics we will be skipping. Even though we are skipping these topics, they are still important. I urge you to consider this your first step in learning SQL and follow up on some of these side topics that we’re skipping.

Now let’s start with some of the simplest forms the standard SQL operations can take.