SQL Commands | DDL, DML, TCL, DCL, DQL

SQL Commands:

SQL Command

 

SQL commands are guidelines, coded into SQL proclamations, which are utilized to speak with the database to perform explicit undertakings, work, capacities and inquiries with information.
SQL commands can be utilized for looking through the database as well as to perform different capacities like, for instance, you can make tables, add information to tables, or alter information, drop the table, set consents for clients. SQL directions are assembled into four noteworthy classifications relying upon their usefulness:

Information Definition Language (DDL) -

These SQL commands are used for making, altering, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.

See More >>>

Information Manipulation Language (DML) -

These SQL commands are used for putting away, recovering, changing, and erasing information.
These Data Manipulation Language commands are: INSERT, UPDATE, and DELETE.

See More >>>

Exchange Control Language (TCL) -

These SQL commands are used for overseeing changes influencing the information. These commands are COMMIT, ROLLBACK, and SAVEPOINT.

See More >>>

Information Control Language (DCL) -

These SQL commands are used for giving security to database objects. These commands are GRANT and REVOKE.

See More >>>

Data Query Language (DQL) -

This SQL command is used for retrieve value from database. This command is SELECT.

See More >>>
Recommended :

Data Definition Language :
	Create
	Drop
	Alter
	Truncate
Data Manipulation Language :
	Insert
	Update
	Delete
Data Control Language :
	Grant
	Revoke
Transaction Control Language :
	Commit
	Rollback
	Savepoint
Data Query Language:
	Select

TRUNCATE | DDL Command | TRUNCATE SQL

TRUNCATE Command

Truncate command used to delete data from table, but not the table structure.

This command may delete whole records from table.

We didn't rollback data. Because DDL commands are auto-committed that means it permanently save all the changes in the database.


Syntax :

TRUNCATE TABLE table_name;

Parameters

table_name = Name of table, which you want to truncate.

 Example :

// truncate Customer01 table
TRUNCATE TABLE Customre01;

* * * * * * * * * * * * * * * * *

In Easy Language

Truncate command used to delete data from table.

ALTER | DDL Command | Alter Command In SQL

ALTER Command

Alter command used to add, modify and delete columns and constraints in an existing table.

ALTER TABLE | ALTER Command with table

Syntax :

ALTER TABLE table_name ADD column_name datatype /
                                              MODIFY COLUMN column_name datatype /
                                              DROP COLUMN column_name ;

In SQL ALTER command, there is two type of parameter :

1) table_name - This parameter specifies the name of table
2) column_name - This parameter specifies the name of column.

We can do three things like, add, modify and delete table column and constraints using with alter command of SQL Server. Which are as under :

1) ALTER TABLE -  ADD column SQL Server

Syntax :

ALTER TABLE table_name ADD column_name datatye;

Example :

// Add Purchase_Item column in Customer01 table

ALTER TABLE Customer01 ADD Purchase_Item Varchar(255);

2) ALTER TABLE - DROP column SQL Server | SQL DROP Column

Syntax :

ALTER TABLE table_name DROP column_name;

Example :

// DROP City column from Customer01 table

ALTER TABLE Customer01 DROP City;

Above example for SQL Server DROP column

Above example for alter table drop column.

3) ALTER TABLE - MODIFY  column SQL Server

Syntax :

ALTER TABLE table_name MODIFY column_name datatype;

Example :

// Change the data type of Phone_No column

ALTER TABLE Customer01 MODIFY Phone_No varchar(255);

DROP CONSTRAINT  | ALTER Command with Constraint

Syntax :

ALTER TABLE table_name ADD constraint_name (column_name) /
                                              MODIFY COLUMN constraint_name (column_name) /
                                              DROP COLUMN constraint_name (column_name) ;

ADD Constraint

Syntax :

ALTER TABLE table_name ADD constraint_name (column_name);

Example :

// ADD PRIMARY KEY constraint to Cust_Id column

ALTER TABLE Customer01 ADD PRIMARY KEY (Cust_Id);

DROP Constraint

Syntax :

ALTER TABLE table_name DROP constraint_name (column_name);

Example :

// DROP UNIQUE constraint from First_Name column

ALTER TABLE Customer01 DROP UNIQUE (First_Name);

MODIFY Constraint

Syntax :

ALTER TABLE table_name MODIFY constraint_name(column_name);

Example :

// Change CHECK constraint value

ALTER TABLE Customer01 MODIFY CHECK (C_Item >= 5);

* * * * * * * * * * * * * * * * *

In Easy Language (Simplify)

Alter command used to add, modify and drop column and constraint of an existing table.

DROP | DDL Commands | Drop Command In SQL

DROP Command

SQL Drop command used to remove a table definition and all the data, indexes, triggers, constraints and permission specifications for that table.

Drop command is used to delete table structure as well as all of table data.

If you may use SQL DROP command, You should be very careful. Because, once a table is deleted then all the information available in that table will also be lost forever. In other words we can say that we didn't rollback this data.

In short, DDL commands are auto-committed that means it permanently save all the changes in the database.

We didn't drop entire row. Because, this command is used only for drop whole table (table data and table structure )  or database.


Syntax :

DROP TABLE table_name ;

In DROP command there is one parameter :

1) table_name -This parameter specifies the name of table.

Drop Table SQL

Example :

// Drop Customer01 table

DROP TABLE Customer01;

Above example, may Drop Table SQL Server

SQL DROP All Tables

If we want to delete or drop all tables from database, we can delete whole database from SQL Server.

Syntax : 

DROP DATABASE database_name;

Example :

// Drop Student database

DROP DATABASE Student;

* * * * * * * * * * * * * * * * *

In Easy Language (Simplify)

DROP command used to remove table definition and table data.

Create | DDL Commands

CREATE Command

Create command used to create new table in a database.

Syntax :

CREATE TABLE table_name (column1 datatype, column2 datatype, column3 datatype, column4 datatype,  . . .)

In CREATE command, there is two types of parameters :

1) Column - parameter specify the name of the column.
2) Datatype - parameters specify the type of data of column.

Example :

// CREATE Customer01 table 

CREATE TABLE Customer01 (Cust_Id int,Last_Name varchar(255),
First_Name varchar(255),Address varchar(255),City varchar(255));

• Above, example creates a table called "Customer01" that contains five columns: Cust_Id, Last_Name, First_Name, Address, and City.
• The Cust_Id column is of type int and will hold an integer.
• The Last_Name, First_Name, Address, and City columns are of type varchar and will hold characters, and the maximum length for these fields is 0-255 characters.

Create New Table Using Another Existing (Old) Table

• We can create a copy of an existing table using CREATE command.
• The new table gets the same column definitions.
• We can use all columns or specific selected columns.
• If we create a new table using an old existing table, the new table will be filled with the existing values from old table.

Syntax :

CREATE TABLE new_table_name AS SELECT [* from] column1, column2, column3,... from existing_table_name [where];

In above syntax WHERE clause is optional.

Examples :

1) CREATE TABLE Customer_Details AS SELECT  Customer_Id,City FROM Customer01;
2) CREATE TABLE Customer_Details AS SELECT * FROM Customer01;
3) CREATE TABLE Customer_Details AS SELECT * FROM Customer01, Salary;
4) CREATE TABLE Customer_Details AS SELECT * FROM Customer01, Salary WHERE Customer_name="Raj" AND Salary_Month="June";

In above examples, 

• We can select particular column.
• We can use also * FROM to copy all table columns.
• We can also select multiple tables.
• In third and fourth example i am using two different tables Customer01 and Salary.

* * * * * * * * * * * * * * *

In Easy Language (Simplify)

CREATE command used to create new table from existing database.
Using with CREATE command also we can create database.

DDL Commands | Data Definition Language | SQL Commands

Data Definition Language

In SQL, there are following types of DDL commands. As below

• CREATE
• DROP
• ALTER
• TRUNCATE

These DDL commands are used to creating, modifying and dropping the structure of database object.
These DDL commands are auto-committed that means it permanently save all the changes in the database.

CREATE Command

Create command used to create new table in a database.

Syntax : 

CREATE TABLE table_name (column_name1 datatype, column_name2 datatype, column_name3 datatype, column_name4 datatype,  . . .) ;

In create command, there is one type of parameters:

1) column_name - This parameter specify the name of the column.


Example :

// Create new Customer01 table

CREATE TABLE Customer01 (Cust_Id int,Last_Name varchar(255),
First_Name varchar(255),Address varchar(255),City varchar(255));

• Above, example creates a table called "Customer01" that contains five columns: Cust_Id, Last_Name, First_Name, Address, and City.
• The Cust_Id column is of type int and will hold an integer.
• The Last_Name, First_Name, Address, and City columns are of type varchar and will hold characters, and the maximum length for these fields is 255 characters.

DROP Command

Drop command used to remove existing table definition and all data from database.

Syntax :

DROP TABLE table_name;

In Drop command, there is one type of parameter:

1) table_name - This parameter specifies the name of table, which we want to drop.

Example :

// Drop Customer01 table 

DROP TABLE Customer01:

ALTER Command

Alter command used to add, modify and drop or delete columns or constraints on an existing table.

Syntax :

ALTER TABLE table_name ADD column_name datatype /
                                              MODIFY COLUMN column_name datatype /
                                              DROP COLUMN column_name ;

In ALTER command, there is three types of  Keywords :

1) ADD - This keyword used to add new column in existing table.
2) MODIFY - This keyword used to update or change name of existing column.
3) DROP - This keyword used to drop column from an existing table.

In ALTER command, there is one type of parameter :

1) column_name - This parameter used to  specifies name of column.

Example :

//Add new column

1) ALTER TABLE Customer01 ADD Registration_No int;

//Modify existing column name
 
2) ALTER TABLE Customer01 MODIFY COLUMN Last_Name varchar(30);

//Drop City column

3) ALTER TABLE Customer01 DROP City ;

TRUNCATE Command

Truncate command used to delete data from database or table.

Syntax :

TRUNCATE TABLE table_name;

Example :

// Truncate Customer01 table

TRUNCATE TABLE Customer01;

* * * * * * * * * * * * * * * *

In Easy Language(Simplify)

CREATE - This command used to create new table in existing database.

DROP - This command used to drop or delete existing table definition from database.

ALTER - This command used to add,modify or drop columns.

TRUNCATE - This command used to delete database from table.

Recommended :

Create | DDL Commands
DROP | DDL Commands
ALTER | DDL Commands
TRUNCATE | DDL Command