Statements

• SELECT

  Use SELECT to retrieve data from tables.

  SELECT [DISTINCT] * | field_list

  FROM <table to select from>

  [WHERE <predicates_list>]

  [ORDER BY <order_fields_list>]

  [GROUP BY <group_fields_list>]

  [HAVING <some_criteria>]

  A SELECT statement can be used to:

  • retrieve a single row, or part of a row, from a table
  • retrieve multiple rows from a table.
  • retrieve related rows by joining two or more tables

  The fields in the field list for the SELECT clause may come from more than one table, but can only come from those tables listed in the FROM clause.

  The following example retrieves data for four fields in all rows of a table:

  SELECT PROJECT, SUBPROJ, BHID, SAMPLE_ID

  FROM G_SAMPLE

  Use the DISTINCT clause to restrict the retrieved data to only the first instance of the combined value of the fields specified in the SELECT clause. In other words you can use DISTINCT to retrieve unique values across one or more fields.

  WHERE clause: Examples 

  WHERE PROJECT = 'BLOCK5' [Field PROJECT has the value BLOCK5]

  WHERE HDR_TYPE <> 'BH' [HDR_TYPE is not equal to BH]

  WHERE END_DEPTH >= 50 [END_DEPTH is greater than or equal to 50 ]

  WHERE YCOORD BETWEEN 5000 AND 10000 [YCOORD in range 5000 to 10000]

  WHERE YCOORD NOT BETWEEN 5000 AND 10000 [YCOORD not in range 5000 to 10000]

  WHERE BHID BETWEEN 'BH001' AND 'BH050' [BHID in range BH001 to BH050]

  WHERE DB_DATE BETWEEN '06/15/96' AND '06/30/96' [DB_DATE in range 15 June to 30 June]

  WHERE TARGET IN ('RF1', 'RF2', 'RF3') [value of TARGET matches one of those in the list]

  WHERE TARGET NOT IN ('RF1', 'RF2', 'RF3') [value of TARGET matches none of those in the list]

  WHERE BHID LIKE 'ZF%' [value of BHID starts with the characters ZF]

  WHERE BHID NOT LIKE 'ZF%' [value of BHID does not start with the characters ZF]

  WHERE PARENT IS NULL [the PARENT field is blank]

  WHERE PARENT IS NOT NULL [the PARENT field is not blank]

   

  • When using "wildcards", the percentage sign masks any set of characters, while the underscore is used for individual characters.
  • Note the mm/dd/yy syntax for dates.
  • Blank fields are said to have a NULL value. This is especially important in the case of numeric fields, where indeterminate values may occur. In such cases, a value of NULL is quite different from a zero value.

• INSERT

  Use INSERT to add one or more rows to a table.

  INSERT INTO <table to insert into>

  [(fields_list)]

  VALUES (field_values_ list)

  The fields list is an optional comma-separated list (enclosed in brackets) of the fields in the table. The VALUES clause introduces a comma-separated list (enclosed in brackets) of the values that will be used to populate the fields for each new record in the table.

  If no fields list is specified, then the specified update values are stored in fields as they are encountered sequentially in the table structure.

  There must always be as many update values as there are fields in the table.

  INSERT INTO collars

  VALUES ("ERD110", 7930607.30, 807882.31, 166.00, 281.00)

  When a fields list is specified, values in the field values list are applied to the fields in the order they are listed. NULL values are stored for any fields that are not specified in the fields list.

  To add rows from one table to another, you can use a subquery instead of using the VALUES keyword:

  INSERT INTO collars_A (hole_no, northing, easting, RL, Depth)

  SELECT hole_no, northing, easting, RL, Depth

  FROM collars_B

• UPDATE

  Use an UPDATE statement to modify the values of one or more fields in one or more records.

  UPDATE <table to be updated>

  SET <field name or alias> = field_value [, <field name

   or alias> = field_value ...]

  [WHERE predicate_list]

  The SET clause introduces a comma-separated list of update expressions.

  Each update expression consists of a field name or alias, the assignment operator (=), and an update value.

  Update values may be literal values or the results of single subqueries or functions.

  UPDATE collars

  SET class = 2

  WHERE (depth > 300)

  As shown above, you can restrict the update by using an optional WHERE clause. If no WHERE clause is specified, all rows in the table are updated.

• DELETE

  Use DELETE to delete one or more rows from a table.

  DELETE FROM <table to delete from>

  [WHERE <predicate_list>]

  You can restrict the delete operation by using an optional WHERE clause. If no WHERE clause is specified, all rows in the table are deleted.

  DELETE FROM collars

  WHERE (hole_no IN (SELECT hole_no FROM old_collars))

Using parameters in a query:

Micromine Geobank allows you to specify parameters for a query using Local Substitution Parameters.

Clauses