As discussed earlier in this series of posts about querying data in MapInfo, SQL is used as the language which is used to carry this out.
SQL stands for Structured Query Language and is used to interact with an existing database. There are many different types of databases however SQL is used as the front end for many of them to allow people to interact with tem. It is important to remember that SQL is case sensitive so it requires care and attention to detail to work with SQL.
In SQL when querying a database all queries use the following parameters:
SELECT columns
FROM table
WHERE condition
GROUP BY columns
HAVING condition
ORDER BY column [ASC / DESC]
LIMIT offset / count
Remember that there is no requirement that you use all of these parameters and usually you won't use them all however it is useful to have them there as a list when you are working on a SQL query.
It is also worth knowing that * can be used to represent "everything" so if you want to see all of the data in a table called Maps then - SELECT * FROM Towns - would show all of the data held in the Towns table. Imagine that the Towns table holds data on all of the towns in the UK including their population and their pollution levels (ranked on a scale of 1-10) amongst other things.
Equally a query which stated - SELECT * FROM Towns WHERE population BETWEEN 20,000 AND 100,000 AND pollution BETWEEN 1 AND 5 would bring up a list of all towns with populations between 20,000 and 100,000 who had pollution between 1 and 5 on the scale. If you wanted to order the results by the size of the towns then you would all ORDER BY population [DESC].
When working with SQL it is worth remembering that many operators that you would have learned in maths at school are available. All of the usual mathematical signs are available so +, - , * , / amd =. Similarly >, <, =< , >= are also available to use as is the ! sign for a not expression so != means "not equal' and !> means " not greater than".
In addition to the above there are a number of frequently used terms that it is useful to introduce you to:
ALL: This is used to compare a value to all values in a column.
AND: This allows you to work with more than one condition primarily when work with the WHERE clause.
ANY: This allows you to compare a value to any value in a column.
BETWEEN: This allows you to search for values that all with in the range given by the minimum and maximum value.
EXISTS: Allows the existence of a row in the specified table that meets the criteria specified.
LIKE: This is used to compare values when working with the wildcard operator.
NOT: This reverses the meaning of the operator that it precedes, so for example NOT BETWEEN means a value that is outside the minimum and maximum specified.
OR: This allows you to specify multiple conditions and see if any of them are met.
You will notice that I used some of these in the query that I showed you earlier. As you can tell although these terms can appear to be challenging when you seeing them being used they are often relatively straight forward to work with as they fit naturally into the query.
Previously we've said that as a MapInfo blog we won't be going too far into SQL but it is difficult to avoid it really as it is such an integral part of MapInfo as you become a more advanced user. I don't think I'm planning to write much more about SQL for the time being but may come back to it in due course to keep developing this theme.
SQL stands for Structured Query Language and is used to interact with an existing database. There are many different types of databases however SQL is used as the front end for many of them to allow people to interact with tem. It is important to remember that SQL is case sensitive so it requires care and attention to detail to work with SQL.
In SQL when querying a database all queries use the following parameters:
SELECT columns
FROM table
WHERE condition
GROUP BY columns
HAVING condition
ORDER BY column [ASC / DESC]
LIMIT offset / count
Remember that there is no requirement that you use all of these parameters and usually you won't use them all however it is useful to have them there as a list when you are working on a SQL query.
It is also worth knowing that * can be used to represent "everything" so if you want to see all of the data in a table called Maps then - SELECT * FROM Towns - would show all of the data held in the Towns table. Imagine that the Towns table holds data on all of the towns in the UK including their population and their pollution levels (ranked on a scale of 1-10) amongst other things.
Equally a query which stated - SELECT * FROM Towns WHERE population BETWEEN 20,000 AND 100,000 AND pollution BETWEEN 1 AND 5 would bring up a list of all towns with populations between 20,000 and 100,000 who had pollution between 1 and 5 on the scale. If you wanted to order the results by the size of the towns then you would all ORDER BY population [DESC].
When working with SQL it is worth remembering that many operators that you would have learned in maths at school are available. All of the usual mathematical signs are available so +, - , * , / amd =. Similarly >, <, =< , >= are also available to use as is the ! sign for a not expression so != means "not equal' and !> means " not greater than".
In addition to the above there are a number of frequently used terms that it is useful to introduce you to:
ALL: This is used to compare a value to all values in a column.
AND: This allows you to work with more than one condition primarily when work with the WHERE clause.
ANY: This allows you to compare a value to any value in a column.
BETWEEN: This allows you to search for values that all with in the range given by the minimum and maximum value.
EXISTS: Allows the existence of a row in the specified table that meets the criteria specified.
LIKE: This is used to compare values when working with the wildcard operator.
NOT: This reverses the meaning of the operator that it precedes, so for example NOT BETWEEN means a value that is outside the minimum and maximum specified.
OR: This allows you to specify multiple conditions and see if any of them are met.
You will notice that I used some of these in the query that I showed you earlier. As you can tell although these terms can appear to be challenging when you seeing them being used they are often relatively straight forward to work with as they fit naturally into the query.
Previously we've said that as a MapInfo blog we won't be going too far into SQL but it is difficult to avoid it really as it is such an integral part of MapInfo as you become a more advanced user. I don't think I'm planning to write much more about SQL for the time being but may come back to it in due course to keep developing this theme.