1. Overview

The Cinchy Query Language (CQL) statements are grouped into two (2) categories:

• Data Manipulation Language (DML) - DML is used to add, retrieve, update and manipulate data. For example, insert, update, delete.

• Data Definition Language (DDL) - DDL is used to create a database schema and define constraints. For example, create a table and alter a table.

2. Data Manipulation Language (DML)

List of available DML statements:

• ​SELECT​

• ​INSERT​

• ​UPDATE​

• ​DELETE​

• ​IF​

• ​Declare Variables​

• ​Set Variables​

3. Data Definition Languages (DDL)

List of available DDL statements:

• ​Create Table​

• ​Alter Table​

• ​Drop Table​

• ​Create View​

• ​Alter View​

• ​Drop View​

• ​Create Index​

• ​Drop Index​

1. Overview

DML is used to add, retrieve, update and manipulate data.The Cinchy DML statements covered on this page are:

• ​

• ​

• ​​

• ​​

SELECT

The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set.

Note: Table name requires domain prefix.

Syntax

Example

Nested JSONs

You can create a query that will produce a nested JSON by wrapping it in an outer SELECT statement, such as in the example below.

Example Statement

Example Output

INSERT

• An INSERT statement can be used to add new rows to a table or view

• You can also include a SELECT statement to identify that another table or view contains the data for the new row or rows.

Note: The table name requires the domain prefix.

Syntax

Example

UPDATE

The data in a table can be changed by using the UPDATE statement.

The UPDATE statement modifies zero or more rows of a table, depending on how many rows satisfy the search condition that was specified in the WHERE clause.

An UPDATE statement can also be used to specify the values that are to be updated in a single row.

• This is done by specifying constants, host variables, expressions, DEFAULT, or NULL. Specify NULL to remove a value from a row's column (without removing the row).

Note: Table name requires domain prefix.

Syntax

Example

DELETE

A DELETE statement can be used to remove entire rows from a table. The number of rows deleted depends on how many rows satisfy the search condition specified in the WHERE statement.

Note: Table name requires domain prefix.

Syntax

Example

IF

The IF statement is used to execute a condition. If the condition is satisfied then the boolean expressions returns TRUE value. The optional ELSE keyword introduces another statement that is executed when the IF condition is not satisfied and returns FALSE value.

Syntax

Example

Declare Variable

The DECLARE statement is used to declare a variable.

Syntax

Example

Set Variable

The SET variable is used to set a value to a variable.

Syntax

Example

1. Overview

DDL is used to create a database schema and define constraints.The Cinchy DDL functions covered on this page are:

• ​Create Table​

• ​Alter Table​

• ​Drop Table​

• ​Create View​

• ​Alter View​

• ​Drop View​

• ​Create Index​

• ​Drop Index​

Data Type Translations

The following tables provide the data type(s) that a Cinchy Data Type translates to in the database:

Create Table

The CREATE TABLE statement is used to create a new table in a database.

The column parameters specify the names of the columns of the table.The datatype parameter specifies the type of data the column can hold (e.g. varchar, char, int, date, etc.).

Syntax

CREATE TABLE [Domain].[Table_Name] (
 [Column1] [datatype],
 [Column2] [datatype],
 [Column3] [datatype]
)

Example

CREATE TABLE [HR].[Employees] (
 [Employee_ID] INT,
 [LastName] VARCHAR(255),
 [FirstName] VARCHAR(255),
 [Start Date] SMALLDATETIME
)

Alter Table

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

Syntax

ALTER TABLE [Domain].[Table_Name]
ADD [column_name] datatype

Example

ALTER TABLE [HR].[Employees]
ADD [Email] VARCHAR(255)

Drop Table

The DROP TABLE statement is used to drop an existing table in a database.

Syntax

DROP TABLE [Domain].[Table_Name]

Example

DROP TABLE [HR].[Employees]

Create View

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, taken from fields of an original table and presents them with a different structure and organization. For example, this is useful when only some columns of a table may be relevant to a user, so they don't have to see information from other columns that may be irrelevant to them.

SQL functions and the WHERE statement can be added to a view to present the data as if the data was coming from one single table.

Syntax

CREATE VIEW [Domain].[View_Name] AS
SELECT [Column1], [Column2], ...
FROM [Domain].[Table_Name]
WHERE [condition]

Example

CREATE VIEW [Detractors] AS
SELECT [Client Name].[Full Name], [Client Name].[Eamil Address], [Advisor].[Full Name], [Q1], [Q2]
FROM [Wealth].[Net Promoter Score]
WHERE [Q1] BETWEEN 0 AND 6

Alter View

Modifies a previously created view. This includes an indexed view. ALTER VIEW does not affect dependent stored procedures or triggers and does not change permissions.

Syntax

ALTER VIEW [Domain].[View_Name]   
AS <select_statement>

Example

ALTER VIEW [Wealth].[Detractors]
AS
SELECT [Client Name].[Full Name], [Client Name].[Eamil Address], [Advisor].[Full Name], [Q1], [Q2]
FROM [Wealth].[Net Promoter Score]
WHERE [Q1] BETWEEN 0 AND 6

Drop View

A view is deleted with the DROP VIEW command.

Syntax

DROP VIEW [Domain].[View_Name]

Example

DROP VIEW [Wealth].[Detractors]

Create Index

The CREATE INDEX statement is used to create indexes in tables.

Syntax

CREATE INDEX [index_name]
ON [Domain].[Table_Name] ([Column1],[Column2], ...)

Example

CREATE INDEX [idx_lastname]
ON [HR].[Employees] ([LastName])

Drop Index

The DROP INDEX statement is used to delete an index on a table.

Syntax

DROP INDEX [index_name] ON [Domain].[Table_Name]

Example

DROP INDEX [idx_lastname] ON [HR].[Employees]