SQL DDL Commands

• The Data Definition Language (DDL) commands in SQL is used to create database schema and to define the type and structure of the data that will be stored in a database.

• It is purely related to handling the structure of a database and the tables.

• In short, the DDL commands are used to change the structure of the table like creating a table, deleting a table, altering a table, etc.

• These are auto-committed commands that mean it permanently saves all the changes in the database.

• The main commands of DDL are as follows:

  • CREATE Command
  • ALTER Command
  • DROP Command
  • TRUNCATE Command

SQL DML Commands

• The Data Manipulation Language (DML) commands are used to modify the database and tables .

• It is purely related to handling of data in the database and the tables.

• These are not the auto-committed commands that mean it cannot permanently save all the changes in the database. That means Rollback is possible.

• The main commands of DML are as follows:

  • INSERT Command
  • UPDATE Command
  • DELETE Command
  • SELECT Command

Database Creation and Management using DDL and DML Commands

1] The DDL CREATE Command -

• This DDL CREATE command is used to create a database or objects such as tables, views, stored procedures, etc.

• To create Database: The below CREATE command creates a database named Bookstore.

CREATE DATABASE BookStore;

• To create Table: The below CREATE command creates a table Books inside the above-created database Bookstore.

USE Bookstore
CREATE TABLE Books
(
B_id INT PRIMARY KEY IDENTITY(1,1),
Book_Name VARCHAR (60) NOT NULL,
Book_Price INT
);

• This Books table contains three columns: Book_id, Book_Name, and Book_Price.
• In Book tabel Book_id is the primary key and its value starts from 1 and whenever a new entry is inserted into the table then it is auto-incremented by 1 due to the applied IDENTITY property.
• The Book_Name column cannot be empty but the Book_Price column can have NULL values.

To see all the columns in the Books table following DML command SELECT is used as follows:

SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='Books';

This will show output like this below:

2] The DDL ALTER Command -

• The DDL ALTER command is used to modify the structure of an already existing table.

• Let's add a new column Book_ISBN in the already existing table Books in the Bookstore database using ALTER command as follows:

USE Bookstore
ALTER TABLE Books
ADD Book_ISBN INT NOT NULL;

• This will add a new column named Book_ISBN with NOT NULL constraints.

• Again run the above-mentioned DML SELECT command to see all the columns in the Books table.

This time it will show the output as follows:

• The DDL ALTER command is also used sometimes to modify the existing column.

• Let's change the data type for the Book_ISBN column from integer to varchar type by using ALTER command as follows:

USE Bookstore
ALTER TABLE Books
ALTER COLUMN Book_ISBN VARCHAR(50);

• Again run the above-mentioned DML SELECT command to see all the columns in the Books table.

This time it will show the output as follows:

3] The DML INSERT Command -

• The DML INSERT command is used to insert new records or data in the form of rows into the table.

• Let's insert 5 values in the above-created table Books as follows:

INSERT INTO Books
VALUES ('Book A', '50', 'ISBN1'),
('Book B', '150', 'ISBN2'),
('Book C', '520', 'ISBN3'),
('Book D', '550', 'ISBN4'),
('Book E', '200', 'ISBN5');

OR

INSERT INTO Books (Book_NAME, Book_Price, Book_ISBN)
VALUES ('Book A', '50', 'ISBN1'),
('Book B', '150', 'ISBN2'),
('Book C', '520', 'ISBN3'),
('Book D', '550', 'ISBN4'),
('Book E', '200', 'ISBN5');

• Here, the value for B_id is not inserted because it is already initiated from number 1 and auto-incremented value.

• Run the below DML SELECT command to see the resultant table Books after inserting the values as follows:

SELECT *
FROM Books;

This will show output like this below:

4] The DML UPDATE Command -

• The DML UPDATE command is used to modify the already existing rows or data in the table.

• Let's update the book price to 1000 in the above table where the Book ISBN is ISBN 3 using the UPDATE command as follows:

UPDATE Books
SET Book_Price = '1000'
WHERE Book_ISBN = "ISBN3";

This will show output like this below after running the below command:

SELECT * FROM Books;

5] The DML DELETE Command -

• The DML DELETE command is used to delete existing one or more rows from the table.

• Let's delete the record for the book whose ISBN Number is ISBN 5 in the table Books as follows:

DELETE FROM Books
WHERE BOOK_ISBN = "ISBN5";

This will show output like this below after running the below command:

SELECT * FROM Books;

6] The DDL DROP Command -

• The DDL DROP command is used to delete an existing database or an object within a database.

• To DROP column of the table: The DROP command used with ALTER command to DROP a particular column from the table. Here, we DROP column Book_ISBN from the table Books as follows:

ALTER TABLE Books
DROP COLUMN Book_ISBN;

• To DROP a Table: The below DROP command DROP a table named Books.

DROP TABLE Books;

• To DROP a Database: The below DROP command DROP a database named Bookstore.

DROP DATABASE Bookstore;

7] The DDL TRUNCATE Command -

• This DDL TRUNCATE command is used to remove all the records from a table.

TRUNCATE TABLE Books;

After applying the TRUNCATE command, If select all the records from the Books table, then it will give the empty table.