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 the AdventureWorks2012 database. You can get started using these free tools using my Guide Getting Started Using SQL Server.
Introduction to Three Simple Steps to Writing SQL
Writing queries is difficult! It’s hard enough sometimes to ask another person a question and get the right answer; asking a very literal minded computer sometimes seems nearly impossible. What we say and what we mean are sometimes entirely different things.
If you understand SQL syntax, but are having a hard time formulating your queries, this article is for you. It can be frustrating knowing all the pieces of the puzzle, but not having the knowledge to complete the full picture. I think that once you go through the steps in this article series, you will gain the confidence to write SQL queries.
The goal of these articles is to help guide you through the process of writing SQL queries. The focus of the articles isn’t to teach you SQL, rather it is to help you understand how to properly pose questions into the form the database can understand.
No doubt you have worked with computers in the past and know how unyielding they are. Computers make little assumptions and have a very difficult time “filling in the gaps.” Computers don’t like ambiguity.
Before we begin to write queries, we’ll briefly review the parts of a database. This is important as tables, views, and relationships make up the substance of our queries. Understanding the functions of these database objects will help you recognize which set of tables and joins are relevant to your query.
In this approach to learning SQL, we’ll divide the problem of writing a query into three steps:
- The first step is to pose the question. This will be in the form of a phrase.
- In the second step, we’ll work toward taking our question and transforming it into a SQL statement. To do this, we’ll start to map key aspects of our question into database language, such as tables and column names. In addition, we’ll map out the various relationships. To make this easy, I’ll introduce you to a simple worksheet that you can use to organize your findings.
- In the third step, we’ll translate our mapped information into SQL. Here we’ll focus on syntax, gradually building up your statement, and verifying its results along the way.
Once you’re ready to complete the SQL, we’ll show you how to do so gradually, making troubleshooting easier. Nothing is more frustrating than running a large SQL statement and seeing no results. By starting small, working slowly, and building on small success, we’ll work ourselves up to the complete and fully functioning answer.
Database Review
In this section, we are going to review the various parts of a database. This is important as when we are writing queries we interact with various pieces of the database. We directly interact with tables and views to pull information from the databases, but what isn’t usually apparent is that we also interact with the databases’ relationships and indexes to get a feel for the meaning of tables and the dependencies between them.
A relational database is made up of several components, of which the table is most significant. The 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 Table
A database consists of one or more tables. Each table is made up of rows and columns. If you think of a table as a grid, the columns go from left to right across the grid and each entry of data is listed down as a row.
The diagram above shows my method to model a relational database table. The major elements that are depicted include:
- The Table Name, which is located at the top of the table.
- Table Columns – There can be one or more table columns. Columns hold specific types of data such as dates, numbers, or text.
- The Primary Keys. Every relational table has one primary key. Its purpose is to uniquely identify each row in the database. No two rows can have the same primary key value. The practical result of this is that you can select every single row by just knowing its primary key.
- Foreign Key – This is a column or set of columns which match a primary key in another table.
Relationships
When databases are normalized, similar information is typically split up and placed in separate tables. This could happen, for instance, if an employee has worked in several departments over the years.
Instead of listing all the departments in the employee record, a separate table is created showing the employee’s history working in various departments.
When this is the case, the tables are said to be related.
In this example, the tables are related by the EmployeeID.
We connect lines between tables to show relationships. In some cases, an entry in one table can be related to one or more entries in another. This is called a one-to-many relationship. In our example, there is one employee that has worked in many departments; therefore, we show a one-to-many relationship.
The reason this is important is it influences the number of rows returned in a query.
Database Views
A view is a searchable object in a database that is defined by a query.
Though a view doesn’t store data, you can query a view like you can a table.
A view can combine data from two or more tables, using joins, and it can also just contain a subset of information. This makes them convenient to abstract, or hide, complicated queries.
What’s Next?
This is one article in a series of four that explain how to organize and write SQL queries. All four articles, including this one, are listed below:
The post How to Write SQL – Three Simple Steps to Writing SQL Queries appeared first on Essential SQL.
