SQL For Beginners - DDL Statements

Introduction

In this article, we will learn about the DDL Language Statements of SQL. We have seen a brief overview of SQL DDL Statements in the introductory article SQL For Beginners - Introduction. Please go through it if you are unaware of the various types of SQL Commands.

The various DDL commands are CREATE, ALTER and DROP.

Let us learn about each DDL Command one by one with examples.

SQL CREATE Statement

The CREATE statement in SQL is used to create the database and the database objects like Tables, Views, Sequences, etc. Let us see how a database and table are created in SQL.

Creating a database in SQL

Syntax

CREATE DATABASE DatabaseName;

Example

CREATE DATABASE Demos;  

On executing this query, a database named "Demos" is created.

Creating Table in SQL

A database is a collection of related tables and other objects. This implies that a table is a part of the database. Now, we have created the "Demos" database. And we need to create a "Students" table in this database.

By default, the master database is selected. Select the Demos database from the dropdown list or use the following command.

Use Demos

This command will instruct the SQL Server that you want to use the database, Demos. Then we can create a table within the Demos database. 

Syntax

CREATE TABLE TableName(

   columnName1 datatype, 

   columnName2 datatype,

   columnNameN datatype);

Here, the datatype is the type of data that you need to store in that column. For numeric values, we have "numeric" datatype; for strings, we have "varchar," and so on. We will study the data types in the upcoming articles.

Example

Create Table Students  
(  
   StudentID numeric(3),  
   StudentName varchar(50)  
);  

This will create a table named Students with two columns, one StudentID, which can contain a numeric value up to three digits, and a second StudentName which is varchar(50), which means it can have 50 characters.

We have created a table with two columns. We need to add two more columns, "City" and "marks," to the student table. Then, how can we do this?

This can be done using the ALTER Command of SQL. 

ALTER Statement

The Alter Table command helps us to modify the structure of the table. If we need to add, delete or modify the columns in our existing table, we use the Alter Table Statement.

Syntax

To add a column to the existing table

  1. ALTER TABLE ExistingTableName  
  2. ADD ColumnName datatype  

To remove a column from the existing table

  1. ALTER TABLE ExistingTableName  
  2. DROP ColumnName  

To modify the datatype of a column in our existing table: 

  1. ALTER TABLE ExistingTableName  
  2. ALTER COLUMN ColumnName NewDatatype  

Example

Let's add the City and marks columns to our table, Students.

Alter table Students  
Add City varchar(25)  
Alter table Students  
Add marks numeric(3) 

 DROP Statement

As the name suggests, the DROP Statement is used to delete a table or a database.

Syntax

To delete a table

Drop Table TableName;

To delete a database

Drop Database DatabaseName;

Example

Drop table Students;  

This will delete the Students table which we have created.

There may be situations when we want to empty the table and not delete the table itself. In such situations, we use the Truncate Table statement of SQL.

Suppose we have data of 50 old students in our table. But we don't need that data anymore. But we need the table to keep a record of our new students. In such situations, we will use the truncate table statement so that only the data gets deleted and the table is as it is.

Example

Truncate table Students;  

Conclusion

This was a brief overview of the various DDL Statements in SQL. I hope that this article helps you. We have worked with the table and its structure over here. In the upcoming articles, we will learn to insert, delete, and modify the data in our table in SQL.


Similar Articles