SQL Reference
  • 18 May 2022
  • 1 Minute to read
  • Contributors
  • Dark
    Light
  • PDF

SQL Reference

  • Dark
    Light
  • PDF

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 StructureSQL 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 

SELECTSELECT 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 JOINSELECT 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
 INSELECT 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


Was this article helpful?