Something a bit more advanced - Getting Started with the SQL Select dialog box
|I would classify last month's article on conditional labeling as being a pretty advanced topic. This month we will go with a little bit lighter topic and introduce the SQL Select dialog box. This is a big topic and we will cover more uses in future issues.|
What is the SQL Select dialog box?
The SQL Select dialog box is a tool that you can use to work with your data in a number of different ways. For background, SQL stands for "Structured Query Language". Anyone familiar with using an SQL from a database system will easily get the concepts in MapInfo Professional's SQL. However, the SQL syntax in MapInfo Pro does not quite follow the ANSI standard SQL.
What can you do with the SQL Select dialog box?
LOTS! For example
- Filter your data (so you look only at certain records)
- Sort your data (ascending/descending and across multiple columns)
- Join tables (work with data from more than one table at a time)
- Summarise information (calculate sums, averages, counts)
- Derive new information (create your own new columns from existing columns or from functions)
- Format your data (currency signs, percent signs, commas, decimal points, etc)
- Perform geographic calculations
Not for everyone or everything
Simple sorting and filtering can be done either in the Browser window or in the somewhat less intimidating Query > Select command. For this article, we will be sure to go a bit beyond these capabilities.
For those who are new to database concepts, here are three important terms. Many of you will know these things but for some newer users of MapInfo Pro, this info might come in handy.
Table - A set of data in MapInfo Pro. A table may also appear as a layer in a map.
Record - A single row of a table (as appears in a Browser window).
Field - A column in the table (as appears in a Browser window or in the Table Structure dialog box).
Before we get started...
Here is the table that is used in the examples that follow. This is part of the World sample data. Our overall goal is to create a query that includes the percentage of urban population for each country.
SQL Select dialog box overview
Here is how to bring up the SQL Select dialog box.
In the 64 bit versions of MapInfo Pro you will find the command on the TABLE, MAP or SPATIAL tabs.
In the 32 bit versions click on the Query menu and choose SQL Select.
Here is a quick overview:
The first thing to tell you about this dialog box is that you do not have to fill in all of the empty boxes!
Query example: Deriving new information from your existing columns. We will start of by learning how you can create new columns out of your existing data. We will make an attempt to follow this example through and in so doing, introduce a few different aspects of working with SQL queries.
Notice that the World table includes a column containing the urban population of each country. It does not include a column indicating the percentage of people living in the urban area. For this first example we will create a simple Browser window with the name of the country, the population, the urban popluation and the percentage of urban population. We will name this query World_pct_urban.
The "trick" to understanding this is simply that in the Select columns box, a column can be an expression. In the dialog box below, note the last column (underlined) calculates the percentage of urban population.
Note that the expression can be given an easy to understand column name. This is done by adding the desired name in quotes to the end of the expression. (An extra tip is that this can be done to rename any column, not just those formed of expressions.)
Also note that the query has been given a name. This is not mandatory. Leaving the default "Selection" results in each successive query being named Query1, Query2, etc.
The following is the result from the first example. Note that we have some incomplete data. Many of the countries have a figure of zero for the urban population. We will exclude these in the next example.
Next: Sort and filter the results
We will make two changes to this query. We will sort the results in descending order such that the countries with the highest percentage of urban population will be listed first. For the purpose of this example we will assume that an urban population of zero represents missing data and we will exclude these records from the result.
The following is the result. According to the data in this table, Israel has the highest percentage of urban population at nearly 90%
Next: Apply formatting
We will make one more adjustment to this example. Note that the percentages are displayed to four decimal places. Also note that there is no percent sign displayed. We can use the Format$() function to control the display format of numbers.
To make this easier to see and understand, here is the column expression with the formatting added:
Format$((Pop_Urban / Pop_1994) * 100, "0.#\%") "Pct_urban"
Here is the screen shot of the dialog box.
Finally, here is a screen shot of the result.
Rounding instead of formatting
If you would like the numbers rounded off and are not concerned with having a percent sign in the display you can use the Round() function instead of the Format$() function.
However, with the Round() function, the decimal points may not all line up. This is not an issue if you round off to the nearest whole number. (A difference with the Format$() function is that you can use it to pad leading or trailing zeros.)
Here is the column expression using Round() instead of Format$()
Round((Pop_Urban / Pop_1994) * 100, 1) "Pct_urban"
And here is the result...
The SQL Select dialog box is a big topic. Heck, the Format$() function is a big topic in its own right and there are many other functions you might want to use!
If you wish to learn the details of any of the functions then the MapInfo Pro Help is a good place for you to look. If you want the details on the Format$() you can look it up in the index. Also there is a help topic called "Using Functions in Expressions" with info on lots of functions.
There are many articles available on using the SQL Select capabilities. Check out the "Query Ninja" topic in the Topic List on the main LI360 page!
Article by Tom Probert, Editor of "The MapInfo Pro" journal
When not writing articles for "The MapInfo Pro", Tom enjoys talking to MapInfo Professional users at conferences and events. When not working he likes to see movies with car chases, explosions and kung-fu fighting.