SQL Command

SQL Commands: 
SQL commands are broadly classified into 5 categories: They are
1. DDL (Data Definition Language)
2. DML (Data Manipulation Language)
3. DCL (Data Control Language)
4. TCL (Transaction Control Language)
5. DQL (Data Query Language)

1. Data Definition Language (DDL) - DDL commands are used to Creating, Modifying and Deleting the structure of database objects.
DDL Commands: CREATE, ALTER, DROP, TRUNCATE and RENAME

2. Data Manipulation Language (DML) - DML commands are used to storing, modifying and deleting the data in the database.
DML Commands: INSERT, UPDATE and DELETE

3. Data Control Language (DCL) - DCL commands are used for providing the security to database objects.
DCL Commands: GRANT and REVOKE.

4. Transaction Control Language (TCL) - TCL commands are used to allow the user to control the transactions in a database.
TCL Commands: COMMIT, ROLLBACK and SAVEPOINT

5. Data Query Language (DQL) - DQL command are used to get/retrieve the data from the database.
DQL Commands: SELECT

NOTE: SQL Commands are terminated by a semicolon(;)
Create Command - The CREATE TABLE command is used to create table(s) or relation(s) to store data.
Syntax:

CREATE TABLE table_name(
   column_name1 datatype,
   column_name2 datatype,
   column_name3 datatype,
   .....
   column_nameN datatype,
   PRIMARY KEY( one or more columns )
);

Example:

CREATE TABLE employee( 
  id number(5), 
  name varchar(20),   
  age number(2), 
  salary number(10),
  PRIMARY KEY(id)
);

Describe Command - The DESCRIBE or DESC command is used to view the description of a table.
Syntax:

Desc table_name;

Example:

Desc employee;

Insert command - Insert command is used to insert the values in a table.
Syntax:

INSERT INTO TABLE_NAME [ (column1,column2,column3,... columnN)] VALUES (value1, value2, value3,...valueN);

Example:

INSERT INTO employee(id, name, age, salary) VALUES(1, 'Ranga', 27, 3000);
INSERT INTO employee VALUES(2, 'Raja', 47, 8000);

Select Command - The SELECT statement is used to query or retrieve data from a table in the database.
A query may retrieve information from specified columns or from all of the columns in the table.
Syntax:
There are three ways we can retrieve data from a table:

• Retrieve one column
• Retrieve multiple columns
• Retrieve all columns

SELECT column_name FROM table_name;
SELECT column_name1, column_name2, column_name3 FROM table_name;
SELECT * FROM table_name;

Example:

SELECT name FROM employee;

SELECT id, name, age FROM employee;

SELECT * FROM employee;

Update Command - The UPDATE command is used to update the values of a table.
Syntax:

UPDATE table_name SET column_name1=value1, column_name2=value2, ......column_namen=valuen WHERE condition;

Example:

UPDATE employee SET name='Ranga Reddy' WHERE id=1;

Alter Command : The ALTER command is used to alter the structure of a table. Alter command has three attributes namely add, modify and drop.
Add:      Adding a column in a table.
Modify: Modify the size of a column.
Drop:     Dropping a column of a table.
Add Column:
Syntax:

ALTER TABLE table_name ADD(column_name datatype);

Example:

ALTER TABLE employee ADD(company varchar(30));

Modify Column:
Syntax:

ALTER TABLE table_name MODIFY(column_name datatype);

Example:

ALTER TABLE employee MODIFY(name varchar(25));

Drop Column:
Syntax:

ALTER TABLE table_name DROP column column_name;

Example:

ALTER TABLE employee DROP column company;

Rename Command - The RENAME command is used to change the name of the table.
Syntax:

RENAME old_table_name TO new_table_name;

Example:

RENAME employee TO employees;

Delete Command - DELETE command is used to delete a row(s) from a table.
Syntax:

DELETE FROM table_name [WHERE condition];

Example:

DELETE FROM employee WHERE id=1;

Truncate command - The TRUNCATE command is used to delete all rows from a table and free the space containing the table.
Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE employee;

Drop Command - The DROP command is used to drop the structure of a table permanently. If you drop a table, all the rows in the is deleted.
Syntax: 

DROP TABLE table_name;

Example:

DROP TABLE employee;

Commit Command - The COMMIT is used to save the transaction. It will saves all transactions to the database since the last COMMIT or ROLLBACK command.
Syntax & Example:

COMMIT

Rollback Command - ROLLBACK command is used to restoring the database to its original position since last COMMIT.
Syntax & Example:

ROLLBACK 

SavePoint Command- The SAVEPOINT command used to identifies the transaction in a database.
Syntax:

SAVEPOINT SAVEPOINT_NAME;

Example:

SAVEPOINT S1;

The ROLLBACK command is used to undo a group of transactions.
Syntax:

ROLLBACK TO SAVEPOINT_NAME;

Example:

ROLLBACK TO S1;

Grant Command: The GRANT command is used to grants(gives) user permissions to access the database objects.

Syntax:

GRANT privilege_name ON object_name TO {user_name |PUBLIC |role_name}[WITH GRANT OPTION]; 

privilege_name is the access right or privilege granted to the user. Some of the access rights are ALL, EXECUTE, and SELECT.
object_name is the name of an database object like TABLE, VIEW, STORED PROC and SEQUENCE.
Example:

CREATE USER rangareddy IDENTIFIED by ranga;
GRANT ALL privileges TO rangareddy;

Revoke Command: REVOKE command is used to rovokes(removes) the permission given to user.
Syntax:

REVOKE privilege_name ON object_name FROM {user_name |PUBLIC |role_name} 

Example:

REVOKE ALL ON employee FROM rangareddy;

Read More