Table of Contents
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
