Data Definition Language Accenture Training Pre-Learning Modules

What Does Data Definition Language (DDL) Mean?

A data definition language (DDL) is a computer language used to create and modify the structure of database objects in a database. These database objects include views, schemas, tables, indexes, etc.

This term is also known as data description language in some contexts, as it describes the fields and records in a database table.

Click here to read more Questions and Answers

Techopedia Explains Data Definition Language (DDL)

The present database industry incorporates DDL into any formal language describing data. However, it is considered to be a subset of SQL (Structured Query Language). SQL often uses imperative verbs with normal English such as sentences to implement database modifications. Hence, DDL does not show up as a different language in an SQL database, but does define changes in the database schema.

It is used to establish and modify the structure of objects in a database by dealing with descriptions of the database schema. Unlike data manipulation language (DML) commands that are used for data modification purposes, DDL commands are used for altering the database structure such as creating new tables or objects along with all their attributes (data type, table name, etc.).

Commonly used DDL in SQL querying are CREATE, ALTER, DROP, and TRUNCATE.

The present database industry incorporates DDL into any formal language describing data. However, it is considered to be a subset of SQL (Structured Query Language). SQL often uses imperative verbs with normal English such as sentences to implement database modifications. Hence, DDL does not show up as a different language in an SQL database, but does define changes in the database schema.

It is used to establish and modify the structure of objects in a database by dealing with descriptions of the database schema. Unlike data manipulation language (DML) commands that are used for data modification purposes, DDL commands are used for altering the database structure such as creating new tables or objects along with all their attributes (data type, table name, etc.).

Commonly used DDL in SQL querying are CREATE, ALTER, DROP, and TRUNCATE.

Create

This command builds a new table and has a predefined syntax. The CREATE statement syntax is:

CREATE TABLE [table name] ([column definitions]) [table parameters];

For example:

CREATE TABLE Employee (Employee Id INTEGER PRIMARY KEY, First name CHAR (50) NULL, Last name CHAR (75) NOT NULL);

The mandatory semi-colon at the end of the statement is used to process every command before it. In this example, the string CHAR is used to specify the data type. Other data types can be DATE, NUMBER, or INTEGER.

Alter

An alter command modifies an existing database table. This command can add up additional column, drop existing columns and even change the data type of columns involved in a database table.

An alter command syntax is:

ALTER object type object name parameters;

For example:

ALTER TABLE Employee ADD PRIMARY KEY (employee_pk);

In this example, we added a unique primary key to the table to add a constraint and enforce a unique value. The constraint “employee_pk” is a primary key and is on the Employee table.

Drop

A drop command is used to delete objects such as a table, index or view. A DROP statement cannot be rolled back, so once an object is destroyed, there’s no way to recover it.

Drop statement syntax is:

DROP object type object name;

For example:

DROP TABLE Employee;

In this example, we’re deleting the Employee table.

Truncate

Similar to DROP, the TRUNCATE statement is used to quickly remove all records from a table. However, unlike DROP that completely destroys a table, TRUNCATE preserves its full structure to be reused later.

Truncate statement syntax is:

TRUNCATE TABLE table_name;

For example:

TRUNCATE TABLE Employee;

In this example, we’re marking all the extents of the Employee table for deallocation, so they’re considered empty for reuse.

Other statements

Other commonly used commands include RENAME and COMMENT. The first one is used with the ALTER TABLE statement to change the name of an object (table, column, etc.). COMMENT is used to add single line, multi-line and in-line comments.

. Add a column in Registration Table

Path: RDBMS/Data Definition Language/Add a column in Registration Table/Add a column in Registration Table/sample.sql

alter table registration add DOC Date;

ALTER TABLE REGISTRATION ADD CONSTRAINT CHKGRTR CHECK(DOC>DOJ); 

2. Add a column in Student Table

Path: RDBMS/Data Definition Language/Add a column in Student Table/Add a column in Student Table/sample.sql

alter table student add Age Number(2);

3. Add a constraint to Course Table

Path: RDBMS/Data Definition Language/Add a constraint to Course Table/Add a constraint to Course Table/sample.sql

alter table course ADD CONSTRAINT CHK_FEES Check(fees>0);

4. Alter – Add CHECK constraint to Mobile_Master

Path: RDBMS/Data Definition Language/Alter – Add CHECK constraint to Mobile_Master/model.sql

ALTER TABLE MOBILE_MASTER ADD CONSTRAINT CHK_WARRANTY CHECK(WARRANTY_IN_YEARS>0);

5. Alter – Add Check constraint to Course Table

Path: RDBMS/Data Definition Language/Alter – Add Check constraint to Course Table/sample.sql

alter table course ADD CONSTRAINT CHK_FEES Check(fees>0);

6. Alter – Add Referential Integrity Constraint/

Path: RDBMS/Data Definition Language/Alter – Add Referential Integrity Constraint/sample.sql

alter table PointofInterest add constraint FK foreign key (townID) references Town(townID);

7. Alter – Add a column age

Path: RDBMS/Data Definition Language/Alter – Add a column age/model.sql

Alter table Customer_Info add Age number(10);

8. Alter – Establish Referential Integrity Constraint

Path: RDBMS/Data Definition Language/Alter – Establish Referential Integrity Constraint/model.sql

alter table Sales_info add constraint FK_KEY foreign key (Customer_ID) REFERENCES Customer_info(Customer_ID);

9. Alter – removing a column in Mobile Specification

Path: RDBMS/Data Definition Language/Alter – removing a column in Mobile Specification/model.sql

alter table Mobile_Specification drop column Weight;

10. Alter -Modify the field type

Path: RDBMS/Data Definition Language/Alter -Modify the field type/sample.sql

ALTER TABLE MOBILE_SPECIFICATION MODIFY  WEIGHT NUMBER(10);

11. Alter table buses  remove column

Path: RDBMS/Data Definition Language/Alter table buses  remove column/ddl5.sql

alter table buses drop column Ac_Available;

12. Alter table payments Rename column

Path: RDBMS/Data Definition Language/Alter table payments Rename column/alter1.sql

alter table payments rename column bd_id to booking_id;

13. Alter-Add a new column

Path: RDBMS/Data Definition Language/Alter- Add a new column/ddl3.sql

alter table Buses add Ac_Available varchar2(10);

14. Alter- Modify the datatype

Path: RDBMS/Data Definition Language/Alter- Modify the datatype/ddl4.sql

alter table buses modify Ac_Available varchar(5);

15. Alter-Rename the field

Path: RDBMS/Data Definition Language/Alter-Rename the field/sample.sql

ALTER TABLE DISTRIBUTOR RENAME COLUMN DISTRIBUTOR_NAME TO DISTRIBUTOR_FULL_NAME;

Click here to read more our blogs

SAP S/4HANA Master Data Concepts Questions & Answers
SAP S/4HANA Functional Capabilities 1909 Questions & Answer
SAP S/4HANA Technical Capabilities 1909 Questions & Answers
SAP Implementation S/4HANA Project Management Questions & Answers
SAP S/4 HANA Enterprise Management – Sales Questions Answers
SAP S/4HANA Key Concepts Overview Questions & Answers
SAP S/4HANA Enterprise Structure Questions & Answer
SAP S/4HANA Reporting and Analytics Questions & Answers
SAP S/4HANA Navigation Interview Questions and Answers
SAP S/4HANA Awareness 1909 Questions and Answers

About Author


After years of Technical Work, I feel like an expert when it comes to Develop wordpress website. Check out How to Create a Wordpress Website in 5 Mins, and Earn Money Online Follow me on Facebook for all the latest updates.

Leave a Comment