Cinchy DDL statements

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]