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 lose sight of the original question or goal.
This is the third of four articles in a series explaining the three simple steps I take to writing complex SQL statements. 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 using my Guide Getting Started Using SQL Server.
Write SQL – Step 2 – Pose the Question
Before you start writing SQL, write down the question you are trying to answer. I find when I’m having a tough time trying to formulate SQL that writing down a description really helps. Unless the problem is clearly defined, it is hard for me to know how to approach it and find a solution.
Many times, we take the problem definition to be a given, but often there is more than meets the eye. For example, let’s assume the question being asked is:
Which departments have employees who worked in the company more than five years?
A couple of questions come to mind:
- Are we asking for employees that have worked in a department more than five years, or is it five years total in the company?
- Are we going to include the time an employee could have been a contract employee?
- If a person leaves and then rejoins the company, how is that tracked?
- Are we looking for a list of departments of those employees with more than five years tenure?
As you can see, even a simple question can generate all sorts of questions and assumptions. Because of this, be very explicit when writing the question. Here is a better example:
What are all the departments an employee has worked in while being employed by the company for five or more years?
Assumptions:
- Don’t count time the employee was a contractor
- Only include full time employees
Sometimes, I find it easier to write the SQL request as a statement rather than a question.
To me “List all fulltime employees first and last names” is more intuitive than “What are the first and last names of all full time employees?”
In either case, it is important you understand the goal of the question or statement. What are the results you seek to acquire? If you don’t know the goal, you won’t know how to pose the question.
Write out your statement in simple English.
Be succinct, if you get too wordy, your statement will be hard to understand. The idea is that as we formulate the statement’s key elements required for the SQL statement will reveal themselves.
When writing the question or statement, speak in the language of your database.
If people are employees, then pose your questions as “which employees are given raises?” instead of “which people are given raises”.
Here are some examples of questions and statements you could ask and how they could be improved.
Example 1
OK: Who is married and takes accounting?
Better: Which employees are married and have taken accounting as a training class?
Why is it better? By being more clear on what “takes accounting” means, we are being clear we are looking for employees who took accounting in one of the company’s training classes rather than, say, at a community college.
Example 2
OK: List past due accounts
Better: List customer accounts that have a balance due and haven’t made a payment in the last 30 days?
What is better? We are defining the criteria for what it means for an account to be past due.
Example 3
OK: What customer are we late in shipping parts?
Better: What customers have an order whose order date was five days ago and have no shipment date?
What’s Next?
This is one article in a series of four that explains how to organize and write SQL queries. All four articles, including this one, are listed below:
The post Write SQL – Formulate your Question – Step 2 appeared first on Essential SQL.
