“I want to see….” Is the key information that tells us what needs to be in the SELECT part of the statement. The SELECT statement is what you want to see in the results and what order you want the columns (if more than one) displayed in. You can also introduce aliases if you want to have the results renamed in a certain way.
Occasionally I will suggest that the group draw a picture of the results they are expecting from the database. I use this to help people understand if they are going to get a multi-line response (a list of values) or a single line response. This tells us if we are going to use aggregation functions or not. If we are using aggregate functions we need to know if we want a single summary value or if we want the value returned in some other way, for example, a summary value per year or group. This lets us know if we are going to use the GROUP BY clause or not. We can also consider if we want to use scalar functions to enhance or modify the results in any way.
The FROM clause is very much driven by the SELECT statement, the SELECT statement lets us know the data sets we will use to show the key information. The FROM clause can include a join, a subquery or a single table. It is a very flexible part of the SQL query that gives the source of the information that will be returned or displayed.
“but only if it is…..” tells us that we will be using either a WHERE clause or a HAVING clause. Where is used when we are filtering the returns based on data in the database – i.e. working on existing stored data. HAVING is used when we are filtering based on derived or aggregate values – i.e. working on transient data that is the result of a previous clause.
If we want the results sorted in a particular way, we can add the ORDER BY clause which allows what is shown in the SELECT statement to be sorted in a more human-readable, or logical way.
SQL is structured and systematic, but only if you know what you are looking for. If you are unclear about the results you want, then your queries could be unclear, convoluted or even just plain wrong. Think about what you need to know, put it in a sentence and look for the key words, make a mental image of the result set you want to see, then write the query based on that. Don’t make it harder than it needs to be!
Post by Sharon Robson