SQL Reference

Applicable to Sisense on Linux and Microsoft Windows

You can use SQL to create new tables and fields in Sisense.

Below is an example of an SQL query with some SQL main clauses:

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)

HAVING FieldName(s) Condition

ORDER BY FieldName(s)

SELECT CustomerLastName, CustomerName, ContactEmail, Count(OrderID) AS Counter

FROM Customers c

INNER JOIN Order o

ON c.CustomerID = o.CustomerID

WHERE

o.OrderId BETWEEN 10 AND 100 AND

(c.CustomerName IN ('John','Mary','David') OR c.CustomerLastName LIKE 'Harrison%')

GROUP BY c.CustomerLastName, c.CustomerName, c.ContactEmail

HAVING Count(o.OrderID) > 3

ORDER BY c.CustomerLastName, c.CustomerName

 

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 Sisense.

 

FUNCTIONS AFTER SELECT CLAUSE

FUNCTIONS           

AVG() – Returns the average value

COUNT() – Returns the number of rows

MAX() – Returns the largest value

MIN() – Returns the smallest value

SUM() – Returns the sum  

 

TABLE JOIN FUNCTIONS

 INNER JOIN        SELECT column_name(s)
FROM table_name1 T1
INNER JOIN table_name2  T2
ON T1.column_name=T2.column_name
 LEFT JOIN   SELECT column_name(s)
FROM table_name1 T1
LEFT JOIN table_name2  T2
ON T1.column_name=T2.column_name 
 RIGHT JOIN SELECT column_name(s)
FROM table_name1 T1
RIGHT JOIN table_name2  T2
ON T1.column_name=T2.column_name 
FULL JOIN   SELECT column_name(s)
FROM table_name1 T1
FULL JOIN table_name2  T2
ON T1.column_name=T2.column_name 
AS (alias)   SELECT column_name AS column_alias
FROM table_nameor
SELECT column_name
FROM table_name  AS table_alias
WHERE   SELECT column_name(s)
FROM table_name
WHERE column_name operator value   

 

FUNCTIONS AFTER THE WHERE CLAUSE

 AND / OR            SELECT column_name(s)
FROM table_name
WHERE condition AND|OR condition
 BETWEEN   SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
 IN SELECT column_name(s)
FROM table_name
WHERE column_name
IN (value1,value2,..)
LIKE   SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern          

 

ORDERING AND GROUPING AFTER WHERE CONDITIONS

 GROUP BY   SELECT column_name,
aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
ORDER BY   SELECT column_name(s)
FROM table_name
ORDER BY column_name [ASC|DESC]
 HAVING   SELECT column_name,
aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value 

 

CLAUSES TO COMBINE TABLES

 UNION                SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
UNION ALL   SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2 

See Also

Basic SQL Guide

MSDN SQL Reference