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;
