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.
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/
Submit an article or tip
