SQL DDL statements

---

SQL DDL (Data Definition Language) statements are used to define, manage, and manipulate the structure of a database, including tables, indexes, constraints, and other schema-related objects.

DDL statements are responsible for creating, altering, and deleting database objects.

SQL DDL statements:

1. CREATE TABLE: This statement is used to create a new table with its columns and data types. You can also specify constraints such as primary keys, foreign keys, and unique constraints.

   CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(100) UNIQUE, department_id INT );

2. ALTER TABLE: This statement is used to modify an existing table. You can use it to add, modify, or delete columns, as well as add or remove constraints.

   • Adding a new column:

     ALTER TABLE employees ADD hire_date DATE;

   • Modifying a column:

     ALTER TABLE employees ALTER COLUMN email VARCHAR(150);

3. DROP TABLE: This statement is used to delete an existing table and all its data permanently.

   DROP TABLE employees;

4. CREATE INDEX: This statement is used to create an index on one or more columns of a table to improve query performance.

   CREATE INDEX idx_last_name ON employees (last_name);

5. DROP INDEX: This statement is used to remove an existing index from a table.

   DROP INDEX idx_last_name;

6. CREATE VIEW: This statement is used to create a virtual table (a view) based on the result of a SELECT query. Views are useful for simplifying complex queries and restricting access to certain columns or rows.

   CREATE VIEW employee_names AS SELECT first_name, last_name FROM employees;

7. DROP VIEW: This statement is used to delete an existing view.

   DROP VIEW employee_names;

8. CREATE DATABASE: This statement is used to create a new database.

   CREATE DATABASE mydb;

9. DROP DATABASE: This statement is used to delete an existing database and all its tables, views, and other objects.

   DROP DATABASE mydb;

10. TRUNCATE TABLE: This statement is used to remove all rows from a table, but it retains the table structure.

    TRUNCATE TABLE employees;

11. COMMENT: This statement is used to add comments or descriptions to database objects such as tables, columns, or indexes.

    COMMENT ON COLUMN employees.first_name IS 'First name of the employee';

These are some of the essential SQL DDL statements for managing the structure of a database.

The specific syntax and capabilities of DDL statements may vary slightly depending on the database management system (DBMS) you are using.

Always consult the documentation of your DBMS for precise details and options.

Enroll Now

• SQL
• DBMS