.NET FAQs Unleashed!

What are DDL statements in SQL? Explain the Create, Alter and Drop commands.

In SQL, DDL stands for Data Definition Language. It is the part of SQL programming language that deals with the construction and alteration of database structures like tables, views, and further the entities inside these tables like columns. It may be used to set the properties of columns as well.

The three popular commands used in DDL are:
Create - Used to create tables, views, and also used to create functions, stored procedures, triggers, indexes etc.

-- An example of Create command below
CREATE TABLE t_students (
first_name VARCHAR2(20) NULL,
last_name VARCHAR2(20) NOT NULL,
dateofbirth DATE NULL);

Drop - Used to totally eliminate a table, view, index from a database - which means that the records as well as the total structure is eliminated from the database.

-- An example of Drop command below
DROP TABLE t_students;

Alter - Used to alter or in other words, change the structure of a table, view, index. This is particularly used when there is a scenario wherein the properties of fields inside a table, view, index are supposed to be updated.

-- An example of Alter command below
ALTER TABLE t_students ADD address VARCHAR2(200); -- adds a column ALTER TABLE t_students DROP COLUMN dateofbirth; -- drops a column ALTER TABLE t_students MODIFY COLUMN address VARCHAR2(100); -- drops a column

-- You can also modify multiple columns using a single modify clause ALTER TABLE t_students MODIFY
COLUMN address VARCHAR2(100)
COLUMN first_name VARCHAR2(50)
COLUMN last_name VARCHAR2(50)

-- You can also add constraints like NOT NULL using the Modify statement
ALTER TABLE t_students Modify
{ first_name VARCHAR2(50) NOT NULL };

SQL Queries  SQL  Create Table  DDL, Create, Alter, Drop commands  DML, Select, Insert, Update, Delete  Create Database  ACID Rules  Dual Table  NULL  Join, Inner, Outer, Left, Right, Full, Cartesian, Natural, Equi  Table, View and a Synonym?  Execute  Function  Stored Procedure  Syntax Stored Proc  Stored Procedure Vs. Function