Click here to Skip to main content
15,867,835 members
Articles / Programming Languages / SQL

Write SQL – Formulate your Question – Step 2

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
19 Jan 2016MIT4 min read 5.4K   4  
Write SQL – Formulate your Question – Step 2

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:

  1. Are we asking for employees that have worked in a department more than five years, or is it five years total in the company?
  2. Are we going to include the time an employee could have been a contract employee?
  3. If a person leaves and then rejoins the company, how is that tracked?
  4. 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.

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/

Comments and Discussions

 
-- There are no messages in this forum --