Create database user and DDL DML DCL Commands Material by KAMAL

 

SIMPLE SQL NOTES

Data Base- Collection of related records and store the data in the form of tables which consists of rows and columns.

Creating user steps

->Alter session set “_oracle_script”=true;

->Create user Raj identified by 123456;

->Grant connect, resource to Raj;

Change password of user

->Alter user Raj identified by 1234;

Dropping user steps

->revoke connect, resource from Raj;

->Drop user Raj;

Mainly SQL divided into 5 types

1.DDL (Data Definition language) These are implicit commands no need to commit – Create, Alter, Drop, Truncate,          

    Rename.

2.DML (Data Manipulation language) These are explicit commands so we must commit – Insert, Update, Delete.

3.TCL (Transaction control language) – Commit, Rollback, Savepoint.

4.DQL/DRL (Data query language/Data Retrieval language) – Select.

5.DCL (Data control language) – Grant, Revoke.

1.DDL commands: -

->Create table emp (empno number, ename varchar2(50), doj date);

->Alter table emp add (sal number (10,2));   

    Alter table emp modify (sal number (16,2));

    Alter table emp drop(sal);

    Alter table emp Rename column sal to salary;

->Drop table emp;

->Truncate table emp;

->Rename emp to employee;

2.DML commands: -

->Insert into emp values(101,’kamal’,’04-Jun-2021’,20000);

   Insert into emo (empno, ename) values (101,’kamal’);

->Update emp set sal=10000 where empno=101;

   Update emp set ename=’kamalraj’ where empno=101;

->Delete from emp where empno=102;

5. DCL commands: -

->Grant select, insert on emp to Raj;

   Grant All on emp to Raj; All means (all commands)

   Grant All on emp to public;

   Grant insert (empno, ename) on emp to Raj;

->Revoke All on emp from Public;

   Revoke insert on emp from Raj, system;

   Revoke select, insert, delete, update on emp from raj;