You should never underestimate the power of using SQL when working with data, be that alphanumerical data or spatial data. SQL can help you in many ways to analyse your data, find specific records and even to create derived data.
Below you can find five basic SQL Select statements. I show them individually, but you can combine them in many ways.
1. Finding specific value
Often you are looking for records with a specific value. You can very easily use SQL to find these records.
The SQL Select statement allows you to specify conditions when selecting records. In this case, you want to say that the value in a certain column should be equal to a specific value.
For text columns, you need to specify the value in quotes. For numerical columns, you must not.
In the example below, I am looking for all addresses on a specific road. The condition in the Where Condition field is: Streetname = "Rugårdsvej"
.
See Attachment
The query looks like this:
Select *
From Addresses
Where StreetName = "Rugårdsvej"
Into Selection
If I were looking for specific road number, using numerical values, the condition would look like this instead: StreetCode = 6814
.
See Attachment
The query looks like this:
Select *
From Addresses
Where StreetCode = 6814
Into Selection
This is a very basic query that you also can do using the Simple Select dialog.
Remember if you are viewing your result in a browser window, you can always filter the content of the browser by right-clicking on the column headers or using the Filter options on the TABLE tab.
See Attachment
2. Finding specific values
Let us take the first query to the next level. Often you are not looking for just a single value but multiple values – in my case multiple streets. You want to compare the values in a column to a list of values. To do so you need to use the In operator instead of the = operator.
I am looking for addresses on three specific streets and can use this condition to find these: StreetName In ("Rugårdsvej", "Stadionvej", "Store Glasvej")
.
See Attachment
Notice how I list the street names inside the parentheses.
The query looks like this:
Select *
From Addresses
Where StreetName In ("Rugårdsvej", "Stadionvej", "Store Glasvej")
Into Selection
If you are specifying numerical values, you do not need to use quotes around the values.
This is a very basic query that you also can do using the Simple Select dialog.
3. Sorting by values in a column
If you have many records, you need to scroll thru you often want these presented in a certain order. To do so you can request the result to be sorted. In my example below, I have chosen to sort my addresses by three columns: the street name and the house number. You separate the column using a comma (,) and you can specify up to five columns.
See Attachment
The query looks like this:
Select *
From Addresses
Order by StreetName, HouseNo
Into Selection
This is a very basic query that you also can do using the Simple Select dialog. The Simple Select dialog does however only allow you to sort using a single column.
Remember if you are viewing your result in a browser window, you can always sort the content of the browser by right-clicking on the column headers or using the Sort options on the TABLE tab.
See Attachment
4. Grouping by values in a column
If you have many records if can be difficult to get an overview of the data. To understand your data better, it can be a good idea to group the records by for example a type or in my example here using the street name.
When you group your data, the resulting query will only hold one record for each unique value. In this way, you can go from thousands of records to a few hundred records.
Often you will combine a group by query with a sorting query and add some sort of aggregation to the result. In this example we will sort by the street names and use the Count(*) aggregation to count the number of addresses on each street.
See Attachment
Notice that I only include the column that I am grouping by in my result. If I were including other columns, the values in these would be random, typically from the first occurrence of the column, I am grouping by.
The query looks like this:
Select StreetName, Count(*)
From Addresses
Group By StreetName
Order By StreetName
Into Selection
5. Using column aliases
A column alias is used to rename an existing column name or an expression used in a SQL Select statement. As the query result is only temporary, the renaming is so too. If you save the query as a new base table, the alias name will however stick.
To specify a column alias, you enter the new name in quotes right after the column or expression that you want to assign it to. Here we are using it on the Count(*)
aggregation from the earlier example
See Attachment
The resulting query will look like this in a floating browser window:
See Attachment
The query looks like this:
Select StreetName, Count(*) "NumAddresses"
From Addresses
Group By StreetName
Order By StreetName
Into Selection
Which basic SQL Select statement are you using the most?