DDL - Data Definition Language.md

Data Definition Language (DDL)

• DDL statements define the blueprint of the database.
• Define, modify, manage or delete the structure of the database objects (tables, views, indexes).
• All the commands of DDL are auto-committed (Permanently save all the changes in the database)

CREATE | ALTER | DROP | TRUNCATE

CREATE

Create Schema:

-- Syntax:
CREATE SCHEMA IF NOT EXISTS schema_name;

-- Example:
CREATE SCHEMA IF NOT EXISTS employee_schema;

Create database:

-- Syntax:
CREATE DATABASE database_name;

-- Example:
CREATE DATABASE employee_details;

Enter into the database:

-- Syntax:
USE database_name

-- Example:
USE employee_details;

Create Table:

-- Syntax:
CREATE TABLE table_name(
    column1 datatype constraints,
    column2 datatype constraints,
    column3 datatype constraints
);

-- Example:
CREATE TABLE employee(
  employee_id INT IDENTITY PRIMARY KEY,
  first_name VARCHAR(25),
  last_name VARCHAR(25),
  age INT CHECK (Age >= 18) NOT NULL,
  birth_date DATE,
  gender VARCHAR(1),
  designation VARCHAR(25),
  city VARCHAR(25) DEFAULT 'Bengaluru'
)

Create View:

CREATE VIEW staff_view AS
SELECT s.ID, s.last_name, cd.company
FROM staff s
LEFT JOIN company_division cd
ON s.department = cd.department;

Create Index:

CREATE INDEX idx_staff_last_name
ON staff
USING (last_name);

ALTER

• Alter/Modify/Change table structure (data type, column names, add or drop columns).
• Either to modify the characteristics of an existing attribute or probably to add a new attribute.

Drop an existing column from the table:

-- Syntax:
ALTER TABLE table_name
DROP COLUMN column_name;

-- Example:
ALTER TABLE employee
DROP COLUMN email;

Add a new column in the table:

-- Syntax:
ALTER TABLE table_name
ADD column1 datatype constraints, column2 datatype constraints;

-- Example:
ALTER TABLE employee
ADD age INT, department VARCHAR(25);

Modify the data type of an existing column in a table:

ALTER TABLE employee
ALTER COLUMN country VARCHAR(25);

Rename the table name:

ALTER TABLE table_name
RENAME TO new_table_name;    

Rename an existing column name in the table:

ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;   

DROP

Delete the entire table data including the structure, metadata, values and constraints.

DROP TABLE employee;

TRUNCATE

Delete only the data values from the table, while retaining the structure and metadata of the table.

TRUNCATE TABLE employee;