DDL - Data Definition
Language
Data Types
• char(n). Fixed length character string, with user-specified length n.
• varchar(n). Variable length character strings, with user-specified maximum
length n.
• int. Integer (a finite subset of the integers that is machine-dependent).
• smallint. Small integer (a machine-dependent subset of the integer domain
type).
• numeric(p,d). Fixed point number, with user-specified precision of p digits, with
d digits to the right of decimal point. (ex., numeric(3,1), allows 44.5 to be stores
exactly, but not 444.5 or 0.32)
• real, double precision. Floating point and double-precision floating point
numbers, with machine-dependent precision.
• float(n). Floating point number, with user-specified precision of at least n digits.
DDL
• 1. DDL - Data Definition Language
• DDL (Data Definition Language) consists of SQL commands that can
be used for defining, altering and deleting database structures such as
tables, indexes and schemas. It simply deals with descriptions of the
database schema and is used to create and modify the structure of
database objects in the database.
DDL Statements
Command Description Syntax
Create database or its objects (table, index,
CREATE TABLE table_name (column1
CREATE function, views, store procedure and
data_type, column2 data_type, ...);
triggers)
DROP Delete objects from the database DROP TABLE table_name;
ALTER TABLE table_name ADD COLUMN
ALTER Alter the structure of the database
column_name data_type;
Remove all records from a table, including
TRUNCATE all spaces allocated for the records are TRUNCATE TABLE table_name;
removed
COMMENT ON TABLE table_name IS
COMMENT Add comments to the data dictionary
'comment_text';
RENAME TABLE old_table_name TO
RENAME Rename an object existing in the database
new_table_name;
Create
Drop
1. DROP Table
To delete an entire table including its data and structure:
Syntax:
DROP TABLE table_name;
2. DROP database
To delete an entire database and all of its associated tables:
Syntax:
DROP DATABASE database_name;
Alter
• Common Use Cases for SQL ALTER TABLE
• 1. ADD
• The ADD clause is used to add a new column to an existing table. You
must specify the name of the new column and its data type.
Alter
• Common Use Cases for SQL ALTER TABLE
• 2. MODIFY
• The MODIFY (or ALTER COLUMN in some databases like SQL Server)
clause is used to modify the definition of an existing column, such as
changing its data type or size.
Alter
• Common Use Cases for SQL ALTER TABLE
• 3. DROP
• The DROP clause allows you to remove a column from a table. Be
cautious when using this command as it will permanently remove the
column and its data.
Alter
• Common Use Cases for SQL ALTER TABLE
• 4. RENAME COLUMN
• We can rename an existing column using RENAME COLUMN clause.
This allows you to change the name of a column while preserving its
data type and content.
Alter
• Common Use Cases for SQL ALTER TABLE
• 5. RENAME TO
• We can rename an entire table using the RENAME TO clause. This
changes the name of the table while preserving its structure and
data.
Truncate
• The TRUNCATE command is a Data Definition Language (DDL) action
that removes all rows from a table but preserves the structure of the
table for future use.
• Although TRUNCATE is similar to the DELETE command (without the
WHERE clause), it is much faster because it bypasses certain integrity
constraints and locks. It was officially introduced in the SQL:2008
standard.
• Syntax:
• TRUNCATE TABLE table_name;
Rename
• Syntax:
• RENAME TABLE old_table_name TO new_table_name;
SQL Constraints
Not Null
Unique
Check
Default
Create Index
Foreign key