Home Tech Easy5 How to write different DDL queries in sql :

How to write different DDL queries in sql :

23176
SHARE
ddl_in_sql
ddl_in_sql

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 

  1. 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>

{

    <attribute_name> <data_type>,

      …….

} ;

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

{

 Name varchar2(20),

Played number,

Goal number,

Assist number,

};

  1. ALTER:

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);

  1. DROP:

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

  1. 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.

  1. COMMENT – add comments to the data dictionary
  2. RENAME – rename an object

Here you may also read about different dml quarries in database

SHARE