Differences

This shows you the differences between two versions of the page.

--- sql:intro [2009/07/17 05:07]
admin
+++ — (current)
@@ Line 1: / Line 1: @@
-====== Omnidex SQL Syntax ======
-Omnidex Queries are typically specified using an Omnidex SQL SELECT statement.
-===== Select List =====
-==== DISTINCT ====
-Distinct can be applied to an entire select-item list or used within an aggregate function.
-  SELECT DISTINCT city, state FROM prospects
-  SELECT count(DISTINCT zip_code) FROM prospects
-==== Result Set Limiters ====
-Result set limiters limit the rows returned by the select statement. The result set limiter processing is done AFTER the query has completed.
-TOP n, EVERY n, RANDOM n
-  SELECT TOP 10 company FROM prospects
-  SELECT EVERY 20 company FROM prospects
-  SELECT RANDOM 10 company FROM prospects
-These result set limiters do not order returned data in any way. Ordering is set by the ORDER BY clause.
-==== Column name(s) ====
-Column names from one or more tables.
-  SELECT company, contact, phone FROM customers
-  SELECT company, contact, order_date FROM customers, orders ...
-==== Column name(s) with an alias ====
-An alias containing a space or special character must be quoted. Single or double quotes.
-The AS keyword is optional.
-  SELECT company CO, contact 'Contact Name', phone "Phone Number" FROM ...
-  SELECT contact AS 'Contact Name', phone AS 'Phone #' FROM ...
-==== Table-qualified column name(s) ====
-  SELECT customers.company, customers.contact, orders.order_date
-    FROM customers, orders ...
-==== Table-alias qualified column name(s) ====
-  SELECT A.company, A.contact, B.order_date FROM company A, orders B ...
-==== Aggregate Functions ====
-min()
-max()
-avg()
-sum()
-count()
-count(*)
-  SELECT customer_no, min(amount), max(amount), avg(amount)
-    FROM orders GROUP BY customer_no
-  SELECT company, sum(amount) FROM ...
-  SELECT count(*), count(state) FROM ...
-==== SQL92-Compliant Functions ====
-These functions can be used on literals or columns.
-<code>
-CASE
-CAST
-CHARACTER_LENGTH | CHAR_LENGTH
-Concatenation using ||
-CURRENT_DATE
-CURRENT_TIME
-CURRENT_TIMESTAMP
-CURRENT_USER
-EXTRACT
-LOWER
-POSITION
-SESSION_USER
-SUBSTRING
-SYSTEM_USER
-TRIM
-UPPER
-USER
-</code>
-  SELECT upper(company), upper(contact), current_date FROM ...
-  SELECT position('SYS' IN 'DYNAMIC INFORMATION SYSTEMS'), system_user FROM ...
-==== Omnidex Extended Functions ====
-Extended Functions
-These functions can be used on literals or columns
-^ Function ^ Description ^
-| $COL_LEN | Return the length of a column as defined in the environment catalog. |
-| $COLUMN_LENGTH | Return the length of a column as defined in the environment catalog. |
-| [[sql:functions:contains|$CONTAINS]] | |
-| $CONTEXT | Return snippets of text from data qualified in a $CONTAINS function. |
-| $CONVERT | Convert a scalar expression from one data type to another. |
-| $CURRENT_ROW | Return the current row number. |
-| $EXTERNAL | Execute an external user-defined function. |
-| $IFNULL | Specify a return value for columns containing null values. |
-| $LJ | Left justify a string by eliminating leading white space. |
-| $LOOKUP | Retrieve textual metadata. |
-| $LPAD | Add leading "PAD" characters to a string. |
-| $MOD | Return "n modulus y" (remainder). |
-| $PROPER | Shift the first letter of each word in a string to upper case and all other letters to lower case. |
-| $RANDOM | Return a pseudo-random number. |
-| $RJ | Right justify a string by eliminating trailing white space and inserting leading spaces as needed. |
-| $ROUND | Round a numerical value to the specified number of decimal places. |
-| $RPAD | Add trailing "PAD" characters to a string. |
-| $SCORE | Returns the rank/relevancy score of qualified text from a $CONTAINS function. |
-| $SOUNDEX | Return the Soundex equivalent to a character string. |
-| $TRUNC | Return a numeric expression truncated to a specified number of digits to the right of the decimal point. |
-  SELECT $proper(company), $random(3) FROM ...
-  SELECT $soundex('database') FROM ...
-==== Literals ====
-  SELECT 'Company Name: ', company, 'Contact Name: ', contact FROM ...
-==== Expressions ====
-  SELECT order_number, 'Discount: ', (quantity * amount)*.02 FROM ...
-==== $ROWID, $ODXID ====
-$ROWID returns the internal rowid. This is either the native rowid or the redefined rowid.
-$ODXID is the Omnidex ID from the Omnidex indexes.
-  SELECT $ROWID, $ODXID, Company FROM ...
-==== * (Asterisk) ====
-  SELECT * FROM CUSTOMERS ...
-  SELECT c.*, o.* FROM CUSTOMERS c, ORDERS o ...
-==== Nested Select Statement ====
-  SELECT STATE, DESCRIPTION,
-    (SELECT SUM(TOTAL) FROM ORDERS WHERE TAX_STATE='CO')
-    FROM STATES WHERE STATE='CO'
-===== FROM Clause =====
-The FROM clause allows the following retrieval types and join operations:
-  * Single Table
-  * Multiple Tables (Joins)
-  * Inner Joins - Join occurs in the where clause
-  * Outer Joins - Left Outer Join, Right Outer Join
-  * Nested Selects
-==== Single Table ====
-  SELECT * FROM CUSTOMERS
-==== Multiple Tables (Joins) ====
-Join columns must have matching data types and lengths.
-  SELECT company, order_number FROM CUSTOMERS, ORDERS ...
-==== Inner Joins ====
-Tables are joined in the WHERE clause. The join predicate is 'AND'ed to other WHERE criteria predicates.
-  SELECT company, order_number, status FROM customers, orders WHERE
-     customers.customer_no = orders.customer_no AND orders.status='CANCELED'
-==== Outer Joins ====
-Outer joins prevent the exclusion of records in one table that do not have records in the joined table.
-  SELECT company, order_number, status FROM customers c LEFT JOIN orders o ON
-         c.customer_no = o.customer_no
-  SELECT company, order_number, status FROM customers c LEFT OUTER JOIN
-         orders o ON c.customer_no = o.customer_no
-  SELECT company, order_number, status FROM customers c RIGHT JOIN orders o ON
-         c.customer_no = o.customer_no
-  SELECT company, order_number, status FROM customers c RIGHT OUTER JOIN
-         orders o ON c.customer_no = o.customer_no
-==== Nested Selects ====
-  select company, contact, state from customers c,
-         (select distinct customer_no from orders where status='back') AS o
-         where c.customer_no = o.customer_no and state!='co'
-===== WHERE Clause =====
-$CONTAINS
-left_operand = right_operand (equal to)
-left_operand < right_operand (less than)
-left_operand > right_operand (greater than)
-left_operand <= right_operand (less than or equal to)
-left_operand >= right_operand (greater than or equal to)
-left_operand <> right_operand (not equal to)
-left_operand != right_operand (not equal to)
-left_operand in (value-list | Nested Select)
-left_operand like right_operand_with_wildcard
-exists (Nested Select) - MUST RETURN ONLY ONE COLUMN.
-=, <, >, <=, >=, <>, like - Nested Select that GUARANTEES to return ONLY ONE ROW. MUST RETURN ONLY ONE COLUMN.
-in (Nested Select) - Can return 0 or more rows. MUST RETURN ONLY ONE COLUMN.
-NOT, AND, OR with any of the above criteria predicates
-left_operand and right_operand can contain any of the following:
-    * Columns
-    * Table Qualified Columns - table.column
-    * Column alias
-    * Literals
-    * Constants
-    * Expressions
-    * Wildcards
-    * $VALUES, $ROWID, $ODXID
-    * Omnidex Sentinel Character
-    * IS NULL - This will not be an optimized query!
-    * Use parenthesis to force order of precedence.
-left_operand = right_operand (equal to)
-SELECT company, contact FROM customers where company='systems'
-SELECT customers.company, orders.order_no FROM customers, orders WHERE
-customers.customer_no=orders.customer_no AND (orders.status='shipped' OR
-orders.status='pending')
-left_operand < right_operand (less than)
-  SELECT company FROM customers WHERE customer_no < 100
-left_operand > right_operand (greater than)
-  SELECT customer_no, product_no FROM orders WHERE status='back' AND
-         (quantity*amount) > 50 ORDER BY 1, 2
-left_operand <= right_operand (less than or equal to)
-  SELECT * FROM inventory WHERE onhand <= 10
-left_operand >= right_operand (greater than or equal to)
-  SELECT company FROM customers WHERE customer_no < 100
-left_operand <> right_operand (not equal to)
-  SELECT company, state FROM customers WHERE state <> 'CO'
-left_operand != right_operand (not equal to)
-  SELECT company, state FROM customers WHERE state != 'CA'
-left_operand in (value-list | Nested Select)
-  SELECT company, state FROM customers WHERE state IN ('A?', 'C?')
-  SELECT company, state FROM customers WHERE state NOT IN ('AZ', 'CA')
-  SELECT * FROM orders WHERE total > (SELECT avg(total) FROM orders)
-left_operand like right_operand_with_wildcard
-  SELECT company, state FROM customers WHERE state like 'C?'
-  SELECT company, state FROM customers WHERE company = 'sys%'
-exists (Nested Select)
-Evaluates false if nested select returns 0 rows, true otherwise. In this example, all customer records will be returned.
-  SELECT c.company FROM customers c WHERE exists (SELECT * FROM orders o
-         WHERE o.status='back')
-=, <, >, <=, >=, <>, !=, like (Nested Select)
-These selects MUST return only one column and only one row.
-  SELECT * FROM orders WHERE customer_no = (SELECT customer_no FROM customers
-         WHERE company='Dynamic Information Systems')
-  SELECT customer_no, order_date, STATUS FROM orders WHERE product_no like
-        (SELECT product_no FROM products WHERE product_no='SUP16210')
-in (Nested Select)
-  SELECT distinct customer_no, status, product_no FROM orders WHERE product_no in
-         (SELECT product_no FROM products WHERE product_name='monitor')
-NOT, AND, OR
-The following two statements return identical results, but note the placement of the NOT operator.
-  SELECT company, state FROM customers WHERE company='%systems or software'
-         AND state NOT in ('AZ', 'CA')
-  SELECT company, state FROM customers WHERE company='%systems or software'
-         AND NOT state in ('AZ', 'CA')
-The next two statements return identical results, but note the differences in the WHERE clause with the Omnidex sentinel character and OR operator.
-  SELECT company, state FROM customers WHERE company='%systems or software'
-  SELECT company, state FROM customers WHERE company='systems' OR
-         company='software'
-The next two statements return companies located in Boulder CO, Boulder Creek CA, and Denton TX, but NOT Denver CO.
-  SELECT company, city, state FROM customers WHERE (city like 'boul%'
-         AND state in ('CO', 'CA')) OR (city like 'den%' AND state != 'CO')
-  SELECT company, city, state FROM customers WHERE (city like 'boul%'
-         AND state in ('CO', 'CA')) OR (city like 'den%' AND NOT state = 'CO')
-===== GROUP BY Clause =====
-Column Names from the Select List
-Column Names Not in the Select List
-HAVING Clause
-Column Names From the Select List
-select customer_no, sum(amount) from orders group by customer_no
-select city, state, count(distinct company) from customers group by city, state
-Column Names Not in the Select List
-select status, sum(amount) from orders group by customer_no, status
-select count(company) from customers group by state
-Having
-select customer_no, sum(amount) from orders
-group by customer_no having customer_no = 1001
-select customer_no, sum(amount) from orders
-group by customer_no having status != 'shipped'
-select status, sum(amount) from orders group by status having customer_no
-in (select customer_no from customers where company='systems')
-====== Quick Links ======
-^    [[:start|Quick Links]]  ^^^^^
-^   General     ^   Programs              ^       Interfaces       ^   General Topics       ^   Miscellaneous Items^
-| [[overview:intro|Omnidex Introduction]] | [[programs:odxsql:intro|OdxSQL]] |[[development:interfaces:odbc:intro|ODBC]] | [[design:grids:intro|Grids]] | [[releasenotes:intro|Release Notes]] |
-| [[odxadmin:intro|Administrating Omnidex (OdxAdmin)]]     | [[programs:odxnet:intro|OdxNet]] |[[development:interfaces:jdbc:intro|JDBC]] | [[design:unionview:intro|Union Views]]| [[osinstall:intro|Server Installs]] |
-| [[design:intro|Data and Index Design]]         | [[programs:odxaim:intro|OdxAim]] |[[development:interfaces:storedproc:intro|Stored Procedures]] | [[design:rolluptable:intro|Rollup Tables]] | [[osinstall:licensing:intro|Licensing]] |
-| [[sql:intro|Omnidex Queries & SQL]]       | [[programs:dbinstal:intro|DBInstal]]   |[[development:interfaces:omniaccess:intro|OmniAccess]] | [[design:partitioning:intro|Partitioning]] | [[osinstall:settings:intro|Settings]] |
-| [[activecounts:intro|Active Counts]] | [[programs:oaenv:intro|OaComp/decomp/helper]]   |[[development:interfaces:clientoa:intro|Client OA]] | [[development:explainplan:intro|Explain Plans]]| [[glossary:intro|Glossary]] |
-| [[powersearch:intro|PowerSearch]] | [[programs:client:intro|DSEdit/OdxQuery]]  | [[rdbms:intro|RDBMS Indexing]] | [[development:debugging:intro|Debugging]]  | [[appendix:intro|Appendix]] |
-(c) Copyright Dynamic Information Systems - This document was last updated July 15, 2009.
-----

Back to top

sql/intro.1247807255.txt.gz · Last modified: 2012/10/26 14:21 (external edit)

Sitemap Recent Changes