SQL Reference

You can use SQL to create new tables and fields in the ElastiCube Manager.

SQL Structure SQL Example

SELECT FieldName(s),Function(), *

FROM TableName 1

INNER JOIN\ LEFT JOIN\RIGHT JOIN\FULL JOIN TableName 2

ON TableName 1. JoinField = TableName 2. JoinField

WHERE FieldName Condition

AND\OR FieldName Condition

GROUP BY FieldName(s)

ORDER BY FieldName(s)

HAVING FieldName(s) Condition

SELECT CustomerName, ContactEmail,Count(Orders) FROM Customer

INNER JOIN Order ON Customer.CustomerID = Order.CustomerID WHERE Order.OrderId BETWEEN 10 AND 100

AND Customer.CustomerName IN (‘John’,’Mary’,’David’) OR Customer.CustomerLastName LIKE ‘Harrison’

GROUP BY Customer.CustomerName

ORDER BY Customer.CustomerLastName HAVING Count(Orders) > 3

 

Basic SQL Syntax Guide

SELECT STATEMENT 

SELECT SELECT column_name(s)FROM table_name 
 SELECT *  SELECT *FROM table_name 
SELECT DISTINCT  SELECT DISTINCT column_name(s)FROM table_name                                    
Note: Nested SELECT statements will not work with an AND clause in the ElastiCube Manager.

 

FUNCTIONS AFTER SELECT CLAUSE

FUNCTIONS            AVG() – Returns the average valueCOUNT() – Returns the number of rowsMAX() – Returns the largest valueMIN() – Returns the smallest valueSUM() – Returns the sum  

 

TABLE JOIN FUNCTIONS

 INNER JOIN        SELECT column_name(s)FROM table_name1 T1INNER JOIN table_name2  T2ON T1.column_name=T2.column_name
 LEFT JOIN   SELECT column_name(s)FROM table_name1 T1LEFT JOIN table_name2  T2ON T1.column_name=T2.column_name 
 RIGHT JOIN SELECT column_name(s)FROM table_name1 T1RIGHT JOIN table_name2  T2ON T1.column_name=T2.column_name 
FULL JOIN   SELECT column_name(s)FROM table_name1 T1FULL JOIN table_name2  T2ON T1.column_name=T2.column_name 
AS (alias)   SELECT column_name AS column_aliasFROM table_nameorSELECT column_nameFROM table_name  AS table_alias
WHERE   SELECT column_name(s)FROM table_nameWHERE column_name operator value   

 

FUNCTIONS AFTER THE WHERE CLAUSE

 AND / OR            SELECT column_name(s)FROM table_nameWHERE conditionAND|OR condition
 BETWEEN   SELECT column_name(s)FROM table_nameWHERE column_nameBETWEEN value1 AND value2
 IN SELECT column_name(s)FROM table_nameWHERE column_nameIN (value1,value2,..)
LIKE   SELECT column_name(s)FROM table_nameWHERE column_name LIKE pattern          

 

ORDERING AND GROUPING AFTER WHERE CONDITIONS

 GROUP BY   SELECT column_name,aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_name
ORDER BY   SELECT column_name(s)FROM table_nameORDER BY column_name [ASC|DESC]
 HAVING   SELECT column_name,aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVINGaggregate_function(column_name) operator value 

 

CLAUSES TO COMBINE TABLES

 UNION                SELECT column_name(s) FROM table_name1UNIONSELECT column_name(s) FROM table_name2
UNION ALL   SELECT column_name(s) FROM table_name1UNION ALLSELECT column_name(s) FROM table_name2 

See Also

Basic SQL Guide

MSDN SQL Reference