MapInfo SQL Commands The MapInfo SQL Language is based on the SQL-3 and SQL/MM standards.

String literals must be enclosed in single quotation marks (example) while identifiers (column names, table names, aliases, etc.) should be enclosed in double quotation marks (example identifier) if necessary. Identifiers only need to be quoted if the parsing logic is unable to correctly parse the identifier. This would include identifiers that have spaces in their names or other special characters. The Commands that comprise the MapInfo SQL Language are: Select SELECT < select_list > FROM { < table_source > } [ ,...n ] [ WHERE < search_condition > ] [ GROUP BY expression [ ,...n ] ] [ ORDER BY {expression | column_position [ ASC | DESC ] } [ ,...n ]] < select_list > ::= { * | { table_name | table_alias }.* | { expression } [ [ AS ] column_alias ] } [ ,...n ] < table_source > ::= table_name [ [ AS ] table_alias ]

Insert
INSERT [INTO] { table_name } [ ( column_list ) ] { VALUES ({expression | NULL}[, ...n]) | query_specification

Update
UPDATE { table_name } SET {{ column_name } = { expression | NULL }} [, ...n] [WHERE < search_condition > ]

Delete
DELETE [FROM] { table_name } [ WHERE < search_condition > ] < search_condition > ::=

{ [ NOT ] < predicate > | ( < search_condition > ) } [ { AND | OR } [ NOT ] { < predicate > | ( < search_condition > ) } [ ,...n ] ] < predicate > ::= { expression [ { = | < > | != | > | >= | < | <= } expression ] | string_expression [ NOT ] LIKE string_expression [ ESCAPE 'escape_character' ] | expression [ NOT ] BETWEEN expression AND expression | expression IS [ NOT ] NULL }

expression is a column name, pseudo column, column alias, constant, function, or any combination of column names, column aliases, constants, and functions connected by an operator(s). Column names and pseudo columns may be prefixed with a table name or a table alias followed by the dot (".") character. search_condition is a reference to an expression that results in either true or false. Most of the functions in this document can be used in search conditions, but aggregation functions are not supported for this use. group_by_expression is a reference to a column in the select list - either an exact copy of the select list expression, the alias, a 1-based number indicating the position of the column, or coln where n is a number representing a column. order_by_expression is a reference to a column in the select list - either an exact copy of the select list expression, the alias, a 1-based number indicating the position of the column, or coln where n is a number representing a column. Operators in MapInfo
Symbol + Usage Addition operator; also concatenation operator. NOTE: MapBasic uses "&" Subtraction operator; also unary negation Multiplication operator Division operator Exponentiation operator Equality operator (case sensitive for strings) Inequality operator Inequality operator

* / ^ = <> !=

Symbol < > <= >= ( ) % , @ or : Less-than operator Greater-than operator Less-than or equal-to operator

Usage

Greater-than or equal-to operator Expression delimiter Expression delimiter Wildcard symbol List item and function argument separator Parameter names - parameter names will be required to start with a @ or : symbol. String constant delimiter Quoted identifier delimiter Equals at least one of the values in the literal list or subquery. Satisfies one of the values of the literal list or subquery. Modifies any of the comparison operators =, <, >, <=, >=. =ANY produces the same results as IN. May be used to modify MapInfo SQL spatial operators EnvelopesIntersect, Intersect, Within, CentroidWithin, Contains, and ContainsCentroid. Satisfies all values in the literal list or subquery. Modifies any of the comparison operators =, <, >, <=, >=. May be used to modify MapInfo SQL spatial operators EnvelopesIntersect, Intersect, Within, CentroidWithin, Contains, and ContainsCentroid. Equivalent to MapBasic's Entirely Within Equivalent to MapBasic's Contains Entire Equivalent to MapBasic's Intersects, Partly Within, and Partly Contains Equivalent to MapBasic's Within Equivalent to MapBasic's Contains Tests if the envelopes (MBRs) of the operands intersect.

" "" IN ANY

ALL

Within Contains Intersects CentroidWithin ContainsCentroid EnvelopesIntersect

SELECT Statement Basics

The SQL SELECT statement queries data from tables in the database. The statement begins with the SELECT keyword. The basic SELECT statement has 3 clauses: SELECT FROM WHERE

The SELECT clause specifies the table columns that are retrieved. The FROM clause specifies the table accessed. The WHERE clause specifies which table rows are used. The WHERE clause is optional; if missing, all table rows are used. For example: SELECT name FROM s WHERE city = Rome This query accesses rows from the table s. it then filters those rows where the city column contains Rome. Finally, the query retrieves the name column from each filtered row. Using the example s table, this query produces:

name Mario
A detailed description of the query actions: The FROM clause accesses the s table. Contents:

sno name

city

S1 Pierre Paris S2 John London S3 Mario Rome

The WHERE clause filters the rows of the FROM table to use those whose city column contains Rome. This chooses a single row from s:

sno name city S3 Mario Rome

The SELECT clause retrieves the name column from the rows filtered by the WHERE clause:

name Mario
The remainder of this subsection examines the 3 major clauses of the SELECT statement, detailing their syntax and semantics. SELECT Clause Specifies the table columns retrieved FROM Clause Specifies the tables to be accessed WHERE Clause Specifies which rows in the FROM Tables to use.

Extended query capabilities are covered in the next sub-section.

SELECT Clause The SELECT clause is mandatory. It specifies a list of columns to be retrieved from the tables in the FROM clause. It has the following general format: SELECT [ALL|DISTINCT] select- list Select-list is a list of column names separated by commas. The ALL and DISTINCT specifiers are optional. DISTINCT specifies that duplicates rows are discarded. A duplicate row is when each corresponding select-list column has the same value. The default is ALL, which retains duplicate rows. For Example: SELECT descry, color FROM p The column names in the select list can be qualified by the appropriate table name: SELECT p.descr, p.color FROM p A column in the select list can be renamed by following the column name with the new name. For Example: SELECT name supplier, city location FROM s This produces:

supplier location Pierre Paris John Mario London Rome

The select list may also contain expressions. A special select list consisting of a single (*) requests all columns in all tables in the FROM clause. For example: SELECT * FROM sp

sno pno qty S1 P1 NULL S2 P1 S3 P1 S3 P2 200 1000 200

The * delimiter will retrieve just the columns of a single table when qualified by the table name. For example: SELECT sp. * FROM sp This produces the same result as the previous example. An unqualified * cannot be combined with other elements in the select list; it must be stand alone. However, a qualified * can be combined with other elements. For Example:

SELECT sp.*, city FROM sp, s WHERE sp.sno= s.sno

sno pno S2 P1 S3 P1 S3 P2

qty

city

S1 P1 NULL Paris 200 London 1000 Rome 200 Rome

Note: This is an example of a query of joining 2 tables. FROM CLAUSE The FROM Clause always follows the SELECT clause. It has the tables accessed by the query. For example: SELECT * FROM s When the FORM LIST contains multiple tables, commas separate the table names. For example: SELECT sp.*, city FROM sp, s WHERE sp.sno=s.sno When the FROM List has multiple tables, they must be joined together. CORRELATION NAMES Like columns in the select list, tables in the from list can be renamed by following the table name with the new name. for example: SELECT supplier.name FROM s supplier The new name is known as the correlation (or range) name for the table. Self joins require correlation names. WHERE CLAUSE The WHERE Clause is optional. When specified, it always follows the FROM clause. The WHERE clause filters rows from the FROM Clause tables. Omitting the WHERE clause specifies that all rows are used. Following the WHERE keyword is a logical expression, also known as a predicate. The predicate evaluates to a SQL Logical value- True, False or Unknown. The most basic predicate is a comparison: Color= Red This predicate returns:

True- if the color column contains the string value Red False- if the color column contains another string value (not Red) or Unknown- if the color column contains null.

Generally, a comparison expression compares the contents of a table column to a literal, as above. A comparison expression may also compare two columns to each other. Table joins use this type of comparison. The = (equal) comparison operator compares two values for equality. Additional comparison operators are: > greater than < less than >= greater than or equal to <= less than or equal to <> not equal to

For example: SELECT * FROM sp WHERE qty >=200

sno pno qty S2 P1 S3 P2 200 20 S3 P1 1000

Note: in the sp table, the qty column for one of the rows contains null. This comparison qty >=200 evaluates to unknown for this row. In the final result of a query, rows with a WHERE clause evaluating to unknown (or false) are eliminated (filtered out). Both operands of a comparison should be the same data type, however automatic conversions are performed between numeric, datetime and interval types. The CAST expression provides explicit type conversions EXTENDED COMPARISON In addition to the basic comparisons described above, SQL supports extended comparison operators BETWEEN, IN, LIKE and IS NULL BETWEEN OPERATOR The BETWEEN operator implements a range comparison, that is, it tests whether a value is between two other values. BETWEEN comparisons have the following format: Value-1 [NOT] BETWEEN value-2 AND value-3 This comparison tests if value-1 is greater than or equal to value 2 and less than or equal to value-3. It is equivalent to the following predicate: value-1 >= value-2 AND value-1 <=value-3 Or if NOT is included: NOT (value-1 >=value-2 AND value-1<=value-3) For example: SELECT * FROM sp

WHERE qty BETWEEN 50 and 500

sno pno qty S2 P1 200 S3 P2 200

IN OPERATOR The IN operator implements comparison to a list of values, that is, it tests whether a value matches any value in a list of values. IN comparisons have the following general format: Value-1 [NOT] IN( value-2 [, value-3]) This comparison tests if value-1 matches value-2 or matches value-3 and so on. It is equivalent to the following logical predicate: Value-1 = value-2 [OR value-1 =value-3] Or if NOT is included: NOT (value-1 = value-2 [OR value-1 = value-3]) For Example: SELECT name FROM s WHERE city IN (Rome, Paris)

name Pierre Mario

LIKE OPERATOR The LIKE operator implements a pattern match comparison, that is, it matches a string value against a pattern string containing wild-card characters. The wild-card characters for LIKE are percent % and underscore- _. Underscore matches any single character. Percent matches zero or more characters. Examples:

Match Value Pattern Result 'abc' '_b_' True 'ab' 'abc' 'ab' 'abc' 'ab' '_b_' '%b%' '%b%' 'a_' 'a_' False True True False True

'abc' 'ab'

'a%_' 'a%_'

True True

LIKE comparison has the following general format: Value-1 [NOT] LIKE value-2 [ESCAPE value-3] All values must be string (character). This comparison uses value-2 as a pattern to match value-1. The optional ESCAPE subclause specifies an escape character for the pattern, allowing the pattern to use % and _ (and the escape character) for matching. The ESCAPE value must be a single character string. In the pattern, the ESCAPE character precedes any character to be escaped. For example, to match a string ending with %, use: x LIKE %/% ESCAPE / A more contrived example that escapes the escape character: y LIKE /%//% ESCAPE / matches any string beginning with %/ The optional NOT reverses the result so that: z NOT LIKE abc% is equivalent to: NOT z LIKE abc% IS NULL Operator A database null in a table column has a special meaningthe value of the column is no currently known (missing), however its value may be known at a later time. A database null may represent any value in the future, but the value is not available at this time. Since two null columns may eventually be assigned different values, one null cant be compared to another in the conventional way. The following syntax is illegal in SQL: WHERE qty = NULL A special comparison operator IS NULL, tests a column for null. It has the following general format: value-1 IS [NOT] NULL This comparison returns true if value-1 contains a null and false otherwise. The optional NOT reverses the result: value-1 IS NOT NULL is equivalent to: NOT value-1 IS NULL For example: SELECT * FROM sp WHERE qty IS NULL

sno pno

qty

S1 P1 NULL
Logical Operators The logical operators are AND, OR, NOT. They take logical expressions as operands and produce a logical result. (TRUE, FALSE, UNKNOWN). In logical expressions, parenthesis are used for grouping AND Operator

The AND Operator combines two logical operands. The operands are comparisons or logical expressions. It has the following general format: Predicate-1 AND predicate-2 AND returns: TRUE if both operands evaluate to true FALSE if either operand evaluates to false Unknown- otherwise (one operand is true and other is unknown or both are unknown)

Truth Tables for AND:

Input 1 True True False False

Input 2 AND Result True False False True True False False False Unknown False

Unknown Unknown Unknown Unknown True Unknown False True False

For example SELECT * FROM sp WHERE sno= S3 AND qty< 500

Unknown Unknown Unknown False

sno pno qty S3 P2 200

OR Operator The OR Operator combines two logical operands. The operands are comparisons or logical expressions. It has the following general format: Predicate-1 OR predicate-2 OR Returns: True if either operand evaluates to true

False if both the operands evaluate to false Unknown- otherwise (one operand is false and the other returns unknown or both are unknown)

Input 1 Input 2 OR Result True True True True False False False False True True False True True Unknown

Unknown Unknown Unknown Unknown True Unknown False True False

For example: SELECT * FROM s WHERE sno=S3 OR City=London

Unknown True Unknown Unknown

sno name city S2 John London S3 Mario Rome

AND has a higher precedence than OR, so the following expression: a OR b AND c is equivalent to a OR (b AND c) NOT Operator The NOT operator inverts the result of a comparison expression or a logical expression. It has the following general format: NOT predicate-1

Input True False

NOT Result False True

Unknown Unknown
Example Query: SELECT *

FROM sp WHERE NOT sno=S3

sno pno qty S1 P1 NULL S2 P1 200

Extended Query Capabilities The Select statement also supports extended capabilities. They are: Sorting Query Results using the ORDER BY clause Expressions in the SELECT clause and WHERE Clause Joining Tables in the FROM clause Subqueries embedding a query in another Grouping Queries using the GROUP BY clause and HAVING clause Aggregate Queries using SET Functions and the HAVING clause Union Queries using the query operator UNION

The other SQL-Data Statements (DML) are: INSERT Statement adds rows to tables UPDATE Statement modify columns in table rows DELETE Statement remove rows from tables

MapInfo SQL Queries The SQL select box can be used for more powerful queries than the standard select box. It allows bespoke queries to be written using SQL. The standard query box can only select rows from one table that contain information that is explicitly in the base tables, the SQL query box can be used to return columns from multiple tables containing information that is only implicit in the base tables. The SQL query dialog box appears complicated, but dont be put off, it becomes easier once you have used it.

SELECT COLUMN:

This is used to select the columns that you would like to be returned in your query results. The query results may be comprised of columns from both tables. This is similar to queries in MS Access, where you can create a query that returns columns from multiple tables. The columns will not be saved to an existing table. They will be displayed as Query xx (where xx is the query number). The query will re-run every time the workspace is opened. This is because databases are generally designed to be more permanent data repositories than spreadsheets. Therefore their structure should not be changed for short term projects. Queries can be run and saved without affecting the underlying data. To create a new table, Select File, Save As then select the relevant query to save as a new table. As you will see from examples, it is possible to specify new column names and the formulae used to calculate columns here too. From Tables Enter the table to update first and the donor table second. Condition Enter the rules (i.e. table 1 row no = table 2 row no) The Group By and Order By: These boxes allow the results to be grouped and ordered by a specific column. Info Table Named: This box allows a new table with a new name to be created from the query results. SQL Query Examples Count (*) lists the different values in a column and count them. E.g. select pipes above 12 and show which ones are imperial and which ones are metric: Select Columns- Diameter, Diameter Units, Count(*) Group By- Diameter Order By- Diameter Area Overlap Select ALLOCATIONS.ID ,Sum(CatersianArea(Overlap(Sites.OBJ, FloodZone.OBJ), sq m)) SUM_OVERLAP_FloodZone ,CartesianArea(Sites.OBJ, sq m) LOCATION_AREA From Sites, FloodZone Where Sites.OBJ Intersects FloodZone.OBJ Group By ID SUM_OVERLAP_FZ and Location_Area are the alias for the new columns containing the calculation. Alias must follow the formula with just a space separating. Do not add a comma to the end of the formula or a column containing the string will be created for every row!

Proportion Overlap It is important to order the table names so the expression reads What proportion of the first table is being overlapped by t he second Select SITES.REFERENCE_NUMBER, Proportionoverlap (SITES.obj, Flood_Zone.obj)*100 overlap Where the amount of overlap is required so it can be added to the source table, the above returns the information that is needed. The optional *100 returns the result as a percentage, which is easier to understand. Overlap is the alias for the new colum n containing the calculation. Alias must follow the formulae with just a space separating. Do not add a comma to the end of the formula or a column containing the string will be created for every row! Leaving the default * will return every column. This may be useful if all the data for records that contain a certain amount of overlap is required. From: SITES, Flood_Zone Order the table names so the expression reads Want proportion of the first table is being overlapped by the second. Where Porportionoverlap (SITES.obj, Flood_Zone.obj) *100 and Sites.Obj Intersects Flood_Zone.Obj The Table2.Obj Intersects Table1.Obj is redundant in this case, however it may provide a performance gain. Group By: SITES.REFERENCE_NUMBER This is not necessary but prevents multiple results for the same object being returned when one polygon is overlapped by several others.

Add T to the end of the where part of the overlap queries. This adds a column T with a constant T to all rows that have a calculated overlap e.g.: Select ALLOCATIONS.ID

, Sum (CartesianArea(Overlap(Sites.OBJ, Floodzone.OBJ), sq m)) SUM_OVERLAP_Floodzone , CartesianArea (Sites.OBJ, sq m) LOCATION_AREA, T From Sites, Floodzone Where Sites.OBJ Intersects Floodzone.OBJ Group By ID This is useful where one only needs to know if there is an overlap and the actual amount of overlap is superfluous detail. It is also possible to word the query to select objects that touch, intersect and do no intersect. Time and Date Based Queries It is possible to select data based on events that happened within a specific time frame, e.g. pollution incidents that were reported in 2010 or crimes that occurred between 22:00-06:00 The date/time column must be formatted in a date/time format. For new tables, it is advisable to use this format when creating the table. Older tables can be converted providing the existing data is in suitable format. Use a SQL query to specify the desired time and data range, e.g.: Crime_Date between 04/10/2003 and 04/10/2004 AND Crime_Time between 12:00PM and 08:00PM

To select just certain types of objects such as text in MapInfo Select * From {table} Where Str$(Obj) = TEXT For other obejcts use the following key words in place of TEXT: POINTS LINE POLYLINE ARC REGION ELLIPSE RECTANGLE ROUNDED RECTANGLE

To find the longest entry in a MapInfo character column To select the biggest entry in a particular column use the following select statement: Select * From{table} where Len({column}) = (Select Max(Len({column}) From {table}) To find the smallest entry use Min instead of Max

To put text objects strings into a column To assign the text object string to column use the following SQL statement: Update {Text Table} Set {Column} = ObjectInfo (Obj,3) If the {text table} contains other objects that are not text, an error will occur. To overcome this problem, select out all the text from the table first and using the selection. To extract out all the text object use the following SQL Statement: Select * From {Text Table} Where Str$(Obj) = TEXT To select Complex Objects Complex objects contain more than one object within themselves (e.g. a doughnut). Select complex objects use the following statement: Select * From {table} Where Val (Str$(ObjectInfo(Obj,21))>1 To Select all the points outside a region Select * From {Points Table} Where NOT Obj Within Any (Select Obj From {Region Table}) To Select region objects that are adjoining a selection Select * From {region table} where Obj Intersects Any (Select Obj From Selection)