Execute DDL, DML, DCL and TCL Commands on below given relational schema. EMP(Empno, Ename, Job, Salary, Mgr, Comm, Hiredate, Deptno).

Exercise : 1 

AIM : Execute DDL,DML,DCL and TCL commands on below given relational schema. EMP(Empno,Ename,Job,Salary,Mgr,Comm,Hiredate,Deptno).

Description : 

SQL(Structured Query Language) is a standard language for storing, manipulating and retrieving data in  databases.

The SQL uses four different languages for the commands 

They are:

1. DDL – Data Definition Language.

2. DML –Data Manipulation Language.

3. DCL- Data Control Language.

4. TCL - Transaction Control Language.

Data Definition Language (DDL)

Data definition language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in database

List of DDL commands :

1. CREATE

2. DROP

3. ALTER

4. TRUNCATE

5. RENAME

1. CREATE : 

This command is used to create the database or its objects like table,index,function,views,store procedure and triggers.

Syntax :

CREATE table table_name( column 1 domain type 1 , column 2 domain type 2 , …);

Example : 

CREATE table EMP(Empno number(15), Ename varchar2(10), Job varchar2(10), Salary real, Mgr int, Comm real, Hiredate date, Deptno int);

desc emp;

2. DROP :

This command is used to delete objects from database.

Syntax :

DROP table table_name;

Example :

DROP table EMP;

Select * from emp;

3. ALTER :

This is used to alter the structure of the database.

Syntax : 

ALTER TABLE -  ADD COLUMN :

ALTER table table_name add column_name domain type;

ALTER TABLE – DROP COLUMN :

ALTER table table_name DROP column column_name;

ALTER TABLE – MODIFY COLUMN :

ALTER table table_name MODIFY column_name datatype; 

Example : 

ALTER table EMP add(fathername varchar2(20)) ;

 

Desc emp;

 

Alter table emp drop column fathername;

Desc emp;

 

Alter table emp modify name varchar2(20);

Alter table emp modify empno int;

 

4.TRUNCATE : 

This is used to remove all records from a table, including all spaces allocated for the records are removed.

Syntax : 

TRUNCATE table table_name

Example:

TRUNCATE table EMP;

Select * from emp;

 

5. RENAME

Rename will be in two situations.

1. To change the name of the table.

2. To change the name of the column.

Syntax

i) alter table tablename rename to players.

Example

alter table player rename to players;

Table altered.

desc players;

Output

 

ii) alter table tablename column<old-column> to <new-coloumn>

Example

alter table players rename column Event to Events;

table altered.

desc players;

Output

 

Data Manipulating Language (DML) :

The SQL commands that deals with the manipulation of data present in the data .

DML is the component of SQL statement that controls access to data and to the database.

List of DML commands : 

1. SELECT – it is used to retrieve data from the database.

2. INSERT – it is used to insert data into a table.

3. UPDATE – it is used to update existing data within a table.

4. DELETE – it is used to delete records from a database table.

1. SELECT :  It is used to select data from a database. The data returned is stored in a result table, called the result set.

Syntax :

SELECT * FROM table_name ;

Example :

Select * from EMP;

 

2. INSERT :It is an SQL command used to insert new rows in a table.

Syntax : 

INSERT INTO table_name  values(value1 , value 2 , …);

Example :

insert into emp values(7369,'smith','clerk',8000,7902,800,'17-dec-80',20);

 insert into emp values(7499,'allen','sales',15000,7698,800,'20-feb-81',30);

 insert into emp values(7521,'ward','sales',15000,7698,600,'22-feb-81',30)

insert into emp values(7566,'jones','manager',20000,7839,1000,'2-apr-81',30);

 insert into emp values(7782,'clark','manager',20000,7839,1500,'9-jan-81',40);

 insert into emp values(7788,'scott','analyst',18000,7566,1200,'19-apr-82',40);

 

3. UPDATE : It is an SQL command used to update existing rows in a table.

Syntax 

UPDATE table_name

SET attribute = value

WHERE condition;

Example :

Update emp set salary = 200000 where empno = 7566;

select * from emp;

 

4. DELETE :The delete command is an SQL command used to delete existing records in a table.

Syntax : 

DELETE FROM table_name WHERE condition ;

Example :

DELETE FROM EMP WHERE empno = 7566;

Select  *  from emp;

Data Control Language (DCL) :

DCL commands mainly deals with the rights , permissions , and other controls of the database system.

List of DCL commands : 

1. GRANT – this command gives users access privileges to the database.

2. REVOKE – this command withdraws the users access privileges given by the GRANT command.

1. GRANT : SQL grant command is specifically used to provide privileges to database objects for  a user. This command also allows users to grant permissions to other users too.

Syntax 

Grant privilege_name on object_name to {usesr_name};

Example :

Create user ram identified by sri;

User created.

Grant all privileges to ram;

Grant succeded.

 

2. REVOKE :Revoke command withdraw user privileges on database objects if any granted.

Syntax : 

Revoke privilege_name on onject_name from {user_name};

Example:

Revoke all privileges from ram;

 

Transaction Control Language(TCL) : 

List of TCL commands :

1. COMMIT – commits a transaction.

2. ROLLBACK – rollbacks a transaction in case of any error occurs.

3. SAVEPOINT – sets a savepoint within  a transaction.

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

Syntax :

COMMIT;

Example :

insert into emp values(7782,'clark','manager',20000,7839,1500,'9-jan-81',40);

insert into emp values(7934,'miller','clerk',1300,7782,0,'23-jan-82',10);

Commit;

 

Select * from emp;

 

2. ROLLBACK : 

It is used to undo transactions  that have not already been saved to the database.

Syntax : 

ROLLBACK;

Example : 

insert into emp values(7902,'ford','analyst',30000,7566,0,'3-dec-91',10);

1 row created.

insert into emp values(7900,'james','clerk',3000,7698,100,'4-nov-81',30);

1 row created.

 savepoint A;

Savepoint created.

insert into emp values(7876,'adems','accounting',20000,7546,1000,'5-nov-81',10);

1 row created.

savepoint B;

Savepoint created.

insert into emp values(7844,'tunner','salesman',2000,7698,0,'5-jan-82',10);

1 row created.

rollback to savepoint B;

Rollback complete.

 

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

Syntax :

SAVEPPOINT savepoint_name;

Example :

insert into emp values(7902,'ford','analyst',30000,7566,0,'3-dec-91',10);

1 row created.

insert into emp values(7900,'james','clerk',3000,7698,100,'4-nov-81',30);

1 row created.

 savepoint A;