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

SQL Commands | MYSQL Commands

SQL Commands

In SQL, there is five types of following commands.

• Data Definition Languages (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)
• Transactional Control Language (TCL)
• Data Query Language (DQL)

Data Definition Language

• DDL commands are used to change the structure of the table
• Using with DDL commands we can creating a table, deleting a table, altering a table etc.
• DDL are auto-committed.In other language we can say that DDL are permanently save all the changes in the database. So, we can not rollback (can not undo or re-do).

DDL Command List :

1. CREATE
2. ALTER
3. DROP
4. TRUNCATE

Data Manipulation Language

• DML commands are used to modify the database.
• Using with DML commands we can insert, update or delete database.
• The command of DML is not auto-committed. In other language we can say that DDL can't permanently save all the changes in the database. They can be rollback (can undo or re-do).

DML Command List :

1. INSERT
2. UPDATE
3. DELETE

Data Control Language

• DCL commands are used to provide privileges to any database user. Like, grant and revoke (take back) authority from any database user.
• SQL provides extensive features of security in order to safeguard information stored in its table unauthorized viewing data.

DCL Commands List :

1. Grant
2. Revoke

Transaction Control Language

• TCL commands we can use only  with DML commands like INSERT, DELETE and UPDATE.
• Operations of TCL commands are automatically committed in the database, that's why they cannot be used while creating tables or dropping them.

TCL Commands List :

1. COMMIT
2. ROLLBACK
3. SAVEPOINT

Data Query Language

• DQL is used to fetch the data from the database or table.

DQL Commands List :

1. SELECT 

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

In Easy Language (Simplify)

1) DDL commands are used to modify the structure of database.

2) DML commands are used to modify the database.

3) DCL commands are used to provide privileges (grant, revoke) to authorized database user.

4) TCL commands are used to save database permanently (commit), un do the database
    (rollback),  and temporarily save transaction (savepoint).

5) DQL command used to select or fetch data from database.

Recommended :

DDL Commands | Data Definition Language | SQL Commands
DML Commands | Data Manipulation Language | SQL Commands
DCL | Data Control Language
TCL | TCL Commands | SQL Command
DQL Command | Data Query Language | SQL Command

SQL Server | What Is SQL | SQL Meaning

What is SQL(Structured Query Language)?

• SQL stands for Structured Query Language.
• SQL is a domain-specific language,it is used in designed for managing data and programming  held in a RDBMS(Relational Database Management System) , or for stream processing in a RDSMS(Relational Data Stream Management System).
• It is mainly useful to handling structured data - there are relations between different entities/variables of the data.
• SQL became a standard of the ANSI(American National Standards Institute) in 1986, and of the ISO(International Organization for Standardization) in 1987.

What Can SQL do Or Work Of SQL ?

• Execute queries against a database or table
• Retrieve data from a database or table
• Insert records in a database or table
• Update records in a database or table
• Delete records from a database or table
• Create new databases or table
• Create new tables in a database or table
• Create stored procedures in a database or table
• Create views in a database or table
• Set permissions on tables, procedures, and views

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

In Easy Language (Simplify)

In simple words we can say that SQL is used to communicate with a database. 
SQL is the standard language for RDMS (Relational Database Management Systems).
SQL statements / commands are used to perform tasks such as update data on a database, or retrieve(get) data from a database or table.



Useful Full Forms

ANSI - American National Standards Institute
RDBMS - Relational Database Management System
RDSMS - Relational Data Stream Management System
ISO - International Organization for Standardization