Overview

Use Data Manipulation Language (DML) to add, retrieve, update and manipulate data. The Cinchy DML statements covered on this page are:

• ​SELECT​

• ​INSERT​

• ​UPDATE​

• ​DELETE​

• ​IF​

• DECLARE​

• ​SET​

SELECT

Use the SELECT statement 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

SELECT [Column1],[Column2], ...
FROM [Domain].[Table_Name]

Example

SELECT [Full Name], [Email Address], [Start Date]
FROM [HR].[Employee]

Produce a nested JSON

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

SELECT (
SELECT
(SELECT p1.[Parent A],p1.[Parent B]
FROM [QA].[Parents 5080] p1 WHERE p1.[Parents]=p.[Parents]
FOR JSON PATH) As Parents,
(SELECT c.[Name] AS [Child Name]
FROM [QA].[Children 5080] c
WHERE c.[Deleted] IS NULL AND c.[Parents]= p.[Parents]
FOR JSON PATH) AS Children
FROM [QA].[Parents 5080] p
WHERE p.[Deleted] IS NULL
FOR JSON PATH, INCLUDE_NULL_VALUES)

Output

[
  {
    "Parents": [{ "Parent A": "Tom", "Parent B": "Maria" }],
    "Children": [
      { "Child Name": "John" },
      { "Child Name": "Theodor" },
      { "Child Name": "Lynette" }
    ]
  },
  {
    "Parents": [{ "Parent A": "Ray", "Parent B": "Sofia" }],
    "Children": [{ "Child Name": "Stephen" }]
  },
  {
    "Parents": [{ "Parent A": "Greg", "Parent B": "Mona" }],
    "Children": [{ "Child Name": "Elizabeth" }, { "Child Name": "Martin" }]
  },
  {
    "Parents": [{ "Parent A": "Henry", "Parent B": "Susanne" }],
    "Children": null
  }
]

INSERT

Use an INSERT statement 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.

INSERT INTO [Domain].[Table_Name] ([Column1],[Column2],[Column3], ...)
VALUES ([Value1],[Value2],[Value3], ...)

Example

INSERT INTO [Contacts].[People] ([First Name],[Last Name],[Address],[Job Title])
VALUES (@firstname, @lastname, @address, @jobtitle)

UPDATE

Use the UPDATE statement to change the data in a table. The UPDATE statement modifies zero or more rows of a table, depending on how many rows meet the search condition specified in the WHERE clause. You can also use an UPDATE statement to specify the values to be updated in a single row. To do this, specify the constants, host variables, expressions, DEFAULT, or NULL. Specify NULL to remove a value from a row's column (without removing the row).

Syntax

UPDATE [Domain].[Table_Name]
SET [Column1] = [Value1], [Column2] = [value2], ...
WHERE [condition]

Example

UPDATE [Revenue].[Customers]
SET [ContactName] = 'Alfred Schmidt', [City]= 'Frankfurt'
WHERE [Customer_ID] = 1;

DELETE

Use the DELETE statement to remove entire rows from a table. The number of rows deleted depends on how many rows match the search condition specified in the WHERE statement.

Syntax

DELETE FROM [Domain].[Table_Name] WHERE [condition]

Example

DELETE FROM [Revenue].[Customers] WHERE [CustomerName] = 'Alfreds Futterkiste';

IF

Use the IF statement to execute a condition. If the condition is satisfied, then the Boolean expressions returns TRUE value. The optional ELSE keyword introduces another statement that executes when the IF condition isn't satisfied and returns FALSE value.

Syntax

IF [condition] THEN [value_if_true] ELSE [value_if_false]

Example

IF 500<1000 THEN 'YES' ELSE 'NO'

DECLARE

Use the DECLARE statement to declare a variable.

Syntax

DECLARE @variable_name variable_type;

Example

DECLARE @var varchar(50);

SET

Use the SET variable to assign a value to a variable.

Syntax

SET @variable_name = variable_value

Example

SET @var = 'Alfreds Futterkiste'

Overview

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

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

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

Data Manipulation Language (DML)

List of available DML statements:

• ​SELECT​

• ​INSERT​

• ​UPDATE​

• ​DELETE​

• ​IF​

• ​DECLARE​

• SET

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​

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 data type the column can hold (such as varchar, char, int, date).

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 has rows and columns, taken from fields of an original table and presents them with a different structure and organization. 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, including indexed views. ALTER VIEW doesn't affect dependent stored procedures or triggers and doesn't 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

Use the DROP VIEW command to delete a view.

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]