In our previous post we have already discussed about the DML (data manipulation language) and different quarries which will help you to understand about data base queries. In Easy 5 we always try to express you as simple as possible. People are so puzzled about the dml and ddl. So, here we are going to discuss about the DDL(Data Definition Language). At first we need to clear which one is DDL quarry and which one is DML.
In simple word to describe something in the database, all belong to the DDL (Data Definition Language). Usually there are six different types of commands. It also deals with different types of schemes.
How to write different DDL queries in sql
- CREATE :
The most important and well known command is CREATE. Basically create a data table, we use it. But before that we need to know about the different types data type. Data type means suppose you are going to insert a column like ‘age’, so, before it needs to select that the data type of age which will be. So, we have listed here some important data types.
|bigint||Integer value (-9,223,372,036,854,775,808 – 9,223,372,036,854,775,807)|
|int||smaller Integer value (-2,147,483,648) – (2,147,483,647)|
|smallint||smaller Integer value (-32,768) – (32,767)|
|tinyint||smaller Integer values 0 – 255|
|bit||Integer data value (either 1 or 0 value)|
|decimal||Decimal values from -10^38 – 10^38|
|numeric||Decimal values from -10^38 – 10^38|
|money||Money values (-922,337,203,685,477.5808) – (922,337,203,685,477.5807)|
|smallmoney||Smaller Money Values (-214,748.3648) – (214,748.3647)|
|datetime||Date value (January 1, 1753) – (December 31, 9999)|
|smalldatetime||Smaller Date Value (January 1, 1900) – (June 6, 2079)|
|timestamp||Unique Number Value (updates when row is updated)|
|char||Character String Value (max 8,000 characters)|
|varchar||Character String Value maximum of 8,000 characters, unless otherwise noted)|
|nchar||Character String Value (max 4,000 characters)|
|nvarchar||Character String Value (max 4,000 characters)|
|text||Character String Value (max 2,147,483,647 characters)|
|ntext||Character String Value (max 1,073,741,823 characters)|
|binary||Binary Value (max 8,000 bytes)|
|varbinary||Binary Value (max 8,000 bytes)|
|image||Binary Value (max 2,147,483,647 bytes)|
|uniqueidentifier||Global Unique ID (GUID)|
Suppose we are going to create a database namely isltb. Before that the the following Query will be-
Create table <table_name>
Eg: here we want to create a table namely tbisl. So table_name will be isltb, and if you notice the data table, then we have to declare the following attributes like name, played, goal, assist. And data_type we have used like varchar2(20),number, number, number. And the structure will be-
Create table tbisl
It is usually used to modify something or some attribute in the data table. Suppose before the data table we want to change the data type of Assist. We want to change the data type into varchar. So, the following query will be :
Alter table <table_name> add constraint <constraint_name> primary key (<attribute list>);
Eg : suppose there is a table where we have declared a primary key , so we need to change that primary key. Suppose in tbisl there is another column, namely ID which is the primary key of tbisl, so-
Eg: Alter TABLE tbisl add constarint not null primary key (ID);
In dml we have seen the delete statement, but here the drop statement is usually used to delete the whole data table. So, in data Data Definition Language (DDL), to drop or delete whole data table we have used it.
Suppose we want to drop the tbisl tables
Drop table <table_name>
Eg : drop table tbisl
- TRUNCATE :
Although TRUNCATE TABLE is similar to DELETE, it is classified as a DDL statement rather than a DML statement. It differs from DELETE statement. Basically, it remove all records from a table, including all spaces allocated for the records are removed.
And others two different types of ddl commands are in there into the database.
- COMMENT – add comments to the data dictionary
- RENAME – rename an object
Here you may also read about different dml quarries in database