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
- ALTER TABLE ExistingTableName
- ADD ColumnName datatype
To remove a column from the existing table
- ALTER TABLE ExistingTableName
- DROP ColumnName
To modify the datatype of a column in our existing table:
- ALTER TABLE ExistingTableName
- 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.