×

Courses

Chapters

>
<

DBMS

DBMS SQL DDL | CrackEase

SQL DDL Commands

DDL Commands

DDL (Data Definition Language) commands define or modify the database structure. Main commands:

  • CREATE
  • ALTER
  • DROP
  • TRUNCATE
CREATE

The CREATE statement makes new database objects (tables, views, indexes, etc.). For tables you define column names, data types and constraints.


CREATE TABLE table_name (
  column1 datatype(size),
  column2 datatype(size),
  ...
  columnN datatype(size),
  PRIMARY KEY (column1)
);

Example :


CREATE TABLE emp (
  empno NUMBER(4,0),
  ename VARCHAR2(50),
  job VARCHAR2(20),
  mgr NUMBER(4,0),
  hiredate DATE,
  sal NUMBER(7,2),
  deptno NUMBER(2,0),
  PRIMARY KEY (empno)
);

You can also create a new table from an existing table's structure (no data copied):


CREATE TABLE sample AS SELECT * FROM emp WHERE 1=0;
ALTER

The ALTER statement changes an existing object's structure. For tables common actions are:

  • ADD column
  • DROP column
  • MODIFY column (type/size)
  • RENAME column/table

-- Add a column
ALTER TABLE student ADD (address VARCHAR2(100));

-- Modify column type/size (syntax varies by RDBMS)
ALTER TABLE student MODIFY (address VARCHAR2(200));

-- Drop a column
ALTER TABLE student DROP COLUMN address;
DROP

DROP removes an object permanently (table, view, index). Use with caution — data and structure are deleted.


DROP TABLE table_name;     -- deletes the table and its data
DROP INDEX index_name;     -- deletes an index
DROP VIEW view_name;       -- deletes a view

Some RDBMS support DROP TABLE IF EXISTS table_name; to avoid errors when the object is absent.

TRUNCATE

TRUNCATE TABLE removes all rows from a table quickly while keeping the table structure (columns, constraints) intact. It's typically faster than DELETE FROM table_name; because it deallocates storage pages.


TRUNCATE TABLE table_name;

Notes:

  • TRUNCATE is usually not transactional (can't be rolled back in many RDBMS without special settings).
  • TRUNCATE does not fire DELETE triggers.
Footer Content | CrackEase
→
←