How to write different DDL queries in sql :

How to write different DDL queries in sql :

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

bigintInteger value (-9,223,372,036,854,775,808 – 9,223,372,036,854,775,807)
intsmaller Integer value (-2,147,483,648) – (2,147,483,647)
smallintsmaller Integer value (-32,768) – (32,767)
tinyintsmaller Integer values 0 – 255
bitInteger data value (either 1 or 0 value)
decimalDecimal values from -10^38 – 10^38
numericDecimal values from -10^38 – 10^38
moneyMoney values (-922,337,203,685,477.5808) – (922,337,203,685,477.5807)
smallmoneySmaller Money Values (-214,748.3648) – (214,748.3647)
datetimeDate value (January 1, 1753) – (December 31, 9999)
smalldatetimeSmaller Date Value (January 1, 1900) – (June 6, 2079)
timestampUnique Number Value (updates when row is updated)
charCharacter String Value (max 8,000 characters)
varcharCharacter String Value maximum of 8,000 characters, unless otherwise noted)
ncharCharacter String Value (max 4,000 characters)
nvarcharCharacter String Value (max 4,000 characters)
textCharacter String Value (max 2,147,483,647 characters)
ntextCharacter String Value (max 1,073,741,823 characters)
binaryBinary Value (max 8,000 bytes)
varbinaryBinary Value (max 8,000 bytes)
imageBinary Value (max 2,147,483,647 bytes)
uniqueidentifierGlobal 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

Comments

Your comments are welcome