Scribd
Upload
412 views

DDL, DML, DCL, DQL, TCL 1

This document discusses the different types of SQL commands: 1. DDL commands are used to define and modify database structures like tables and columns. Common DDL commands include CREATE, ALTER, DROP, and TRUNCATE. 2. DML commands are used to manipulate data within database tables. Common DML commands include INSERT, UPDATE, and DELETE. 3. TCL commands control transactions and include COMMIT, ROLLBACK, and SAVEPOINT. 4. DCL commands control user permissions and privileges with GRANT and REVOKE. 5. DQL commands retrieve data from databases using the SELECT statement.

Uploaded by

Ajith Ram
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
412 views

DDL, DML, DCL, DQL, TCL 1

This document discusses the different types of SQL commands: 1. DDL commands are used to define and modify database structures like tables and columns. Common DDL commands include CREATE, ALTER, DROP, and TRUNCATE. 2. DML commands are used to manipulate data within database tables. Common DML commands include INSERT, UPDATE, and DELETE. 3. TCL commands control transactions and include COMMIT, ROLLBACK, and SAVEPOINT. 4. DCL commands control user permissions and privileges with GRANT and REVOKE. 5. DQL commands retrieve data from databases using the SELECT statement.

Uploaded by

Ajith Ram
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 13

STRUCTURED QUERY

LANGUAGE

Name : AJITHRAM
EMP ID :OM2116262
ORACLE :
Oracle sql developer is an integrated development
environment for working with sql in oracle databases

SQL :
o SQL commands are instructions. It is used to communicate with the database.
It is also used to perform specific tasks, functions, and queries of data.
o SQL can perform various tasks like create a table, add data to tables, drop the
table, modify the table, set permission for users.

Types of SQL Commands


There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.
1. Data Definition Language (DDL)

o DDL changes the structure of the table like creating a table, deleting a table,
altering a table, etc.
o All the command of DDL are auto-committed that means it permanently save
all the changes in the database.

Here are some commands that come under DDL:

o CREATE
o ALTER
o DROP
o TRUNCATE

a. CREATE It is used to create a new table in the database.

Syntax:

1. create table ncl(emp_id number,emp_name varchar2(20),salary number(10));

ALTER: It is used to alter the structure of the database. This change could be either
to modify the characteristics of an existing attribute or probably to add a new
attribute.

ADD :

Syntax:

To add a new column in the table


1. alter table ncl add(place varchar2(20));

Modify :
* The alter table statement is also used to add and drop various
constraints on an existing table
1.alter table dcl modify(place varchar2(15));
Drop :
The drop column command is used to delete a column in an
existing table.
Syntax :
1. alter table ncl drop (place);

Rename:
Columns can be also be given new name with the use of ALTER TABLE. QUERY:
Change the name of column NAME to FIRST_NAME in table Student.

1.alter table dcl rename column emp_name to emps_name;


Drop :
 Drop is used for deleting the column data.

Syntax :
1. drop table ncl;

Truncate:
TRUNCATE is a DDL(Data Definition Language) command and is used to
delete all the rows or tuples from a table

Syntax :
1.Truncate table test_table;

Rename :
  Use the ALTER TABLE RENAME command to rename column names.
Syntax :
1. Rename ncl to pcl;
Output:

2. Data Manipulation Language

o DML commands are used to modify the database. It is responsible for all form
of changes in the database.
o The command of DML is not auto-committed that means it can't permanently
save all the changes in the database. They can be rollback.

 Insert
 Update
 Delete

a. INSERT: 
 The INSERT statement is a SQL query. It is used to insert data into
the row of a table.

Syntax:

insert into pcl values(101,'suresh',500);


insert into pcl values(102,'kala',2000);
insert into pcl values(103,'mugu',5000);
insert into pcl values(104,'senai',5020);
output :

UPDATE :

b. UPDATE: This command is used to update or modify the value of a column


in the table.

Syntax:

1. update pcl set emps_name='ajith' where emp_id='104';

output:
DELETE :

DELETE: It is used to remove one or more row from a table.

Syntax:

1.Delete table pcl;

Output :

3. Transaction Control Language


 TCL commands can only use with DML commands like INSERT,
DELETE and UPDATE only.

 These operations are automatically committed in the database that's


why they cannot be used while creating tables or dropping them.

Here are some commands that come under TCL:

o COMMIT
o ROLLBACK
o SAVEPOINT

a. Commit: Commit command is used to save all the transactions to the database.


Syntax:

1.commit;

Output :

b. Rollback:
 Rollback command is used to undo transactions that have not
already been saved to the database.

Syntax:

1. ROLLBACK;  

Output :
c. SAVEPOINT: 
 It is used to roll the transaction back to a certain point without
rolling back the entire transaction.

Syntax:

1. SAVEPOINT SAVEPOINT_NAME;  

4. Data Control Language


DCL commands are used to grant and take back authority from any database user.

o Grant
o Revoke

a. Grant: It is used to give user access privileges to a database.

Example

1.
GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;  

b. Revoke: It is used to take back permissions from the user.

Example
1. REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;  

5. Data Query Language


DQL is used to fetch the data from the database.

It uses only one command:

o SELECT

a. SELECT: 

 This is the same as the projection operation of relational algebra. It is


used to select the attribute based on the condition described by
WHERE clause.

Syntax:

1. SELECT expressions    
2. FROM TABLES    
3. WHERE conditions.

You might also like