1
148views
Create and populate database using Data Definition Language (DDL) and DML Commands.
1 Answer
2
4views

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:

COLUMN_NAME DATA_TYPE
Book_id int
Book_Name varchar
Book_Price int


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:

COLUMN_NAME DATA_TYPE
Book_id int
Book_Name varchar
Book_Price int
Book_ISBN int
  • 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:

COLUMN_NAME DATA_TYPE
Book_id int
Book_Name varchar
Book_Price int
Book_ISBN varchar


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:

Book_id Book_Name Book_Price Book_ISBN
1 Book A 50 ISBN1
2 Book B 150 ISBN2
3 Book C 520 ISBN3
4 Book D 550 ISBN4
5 Book E 200 ISBN5


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;
Book_id Book_Name Book_Price Book_ISBN
1 Book A 50 ISBN1
2 Book B 150 ISBN2
3 Book C 1000 ISBN3
4 Book D 550 ISBN4
5 Book E 200 ISBN5


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;
Book_id Book_Name Book_Price Book_ISBN
1 Book A 50 ISBN1
2 Book B 150 ISBN2
3 Book C 1000 ISBN3
4 Book D 550 ISBN4


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.

Please log in to add an answer.