CLASS 11 Data Definition Language (DDL) Commands in SQL NOTES

 Data Definition Language (DDL) Commands in SQL

DDL commands are used to define, modify, and delete the structure of database objects like databases, tables, indexes, and views. Here's a look at some key DDL commands:

1. CREATE DATABASE

The CREATE DATABASE command is used to create a new database in the database management system.

Syntax:

CREATE DATABASE database_name;

Example:

CREATE DATABASE school;

This command creates a database named school.

2. CREATE TABLE

The CREATE TABLE command is used to create a new table in the database. When creating a table, you must specify the table name and define the columns along with their data types.

Syntax:

CREATE TABLE table_name (
    column1_name datatype constraints,
    column2_name datatype constraints,
    ...
);

Example:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birthdate DATE,
    grade_level INT
);

This command creates a table named students with columns for student_id, first_name, last_name, birthdate, and grade_level.

3. DROP

The DROP command is used to delete an existing database or table, along with all the data contained within it. This operation is irreversible.

Syntax:

• To drop a database:

  DROP DATABASE database_name;
  

• To drop a table:

  DROP TABLE table_name;
  

Example:

• Dropping a database:

  DROP DATABASE school;
  

• Dropping a table:

  
  DROP TABLE students;
  

These commands will permanently delete the school database or the students table, respectively.

4. ALTER

The ALTER command is used to modify an existing database object, such as adding, deleting, or modifying columns in a table.

Syntax:

• To add a column:

  ALTER TABLE table_name ADD column_name datatype;
  

• To modify a column:

  ALTER TABLE table_name MODIFY column_name new_datatype;
  

• To drop a column:

  ALTER TABLE table_name DROP COLUMN column_name;
  

Example:

• Adding a column:

  ALTER TABLE students ADD email VARCHAR(100);
  

  This adds an email column to the students table.

• Modifying a column:

  ALTER TABLE students MODIFY grade_level TINYINT;
  

  This modifies the grade_level column to use the TINYINT data type.

• Dropping a column:

  ALTER TABLE students DROP COLUMN birthdate;
  

  This removes the birthdate column from the students table.