1 / 15

Data Control Language

Data Control Language. Grant, Revoke. Privileges. Select Insert Update Delete Reference. Grant Command. Give the permission to others user. Syntax Grant <privileges> on <tablename> to <username> Example SQL>Grant select on emp to user1;. Example.

halee-flores
Download Presentation

Data Control Language

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data Control Language Grant, Revoke

  2. Privileges • Select • Insert • Update • Delete • Reference

  3. Grant Command • Give the permission to others user. Syntax Grant <privileges> on <tablename> to <username> Example SQL>Grant select on emp to user1;

  4. Example SQL> Grant select, insert on emp to user2; SQL> Grant update(comm) on emp to user3; SQL> grant update (salary, comm) on emp to user4; SQL> Grant select (dno=10) on emp to user5;

  5. Revoke Command • Cancel the permission Syntax Revoke <privileges> on <tablename> from <username> Example SQL>Revoke select on emp from user1;

  6. Example SQL> Revoke select, insert on emp from user2; SQL> Revoke update(comm) on emp from user3; SQL> Revoke update (salary, comm) on emp from user4; SQL> Revoke select (dno=10) on emp from user5;

  7. Example SQL> Grant select, insert, update, Delete on emp to user2; SQL> Revoke insert, Delete on emp from user2;

  8. Transaction Control Language Commit, Rollback, Savepoint, Grant,Revoke • The TCL statements give you flexibility to undo transactions or write transactions to the disk • Transactions provide consistency in case of a system failure.

  9. Commit Current transaction and writes all changes permanent to the disk. • Savepoint Marks a point in the current transaction • Rollback to [savepoint n] • Undoing all changes • if n to savepoint undo the n position

  10. Example SQL> insert into emp values ( &empno, &ename, &salary, &dno, &comm); SQL>/ (input some record) SQL> select * from emp; SQL> Commit; SQL> Delete from emp where comm>=2500; SQL> Select * from emp; SQL> Rollback; SQL> Select * from emp; SQL> Savepoint x; SQL> delete from emp where dno=10; SQL> Roolback to x;

  11. Set Operations • Union • Intersect • Minus

  12. Union • Returns all distinct rows from both queries. Example SQL> Select * from emp1 union select * from emp2; A={ 1,5,6,3} b= {6,8,4,3,2} AUB = { 1,5, 6,3,8,4,2} SQL> select city from salesman union select city from customer;

  13. UNION ALL • Display including duplicates record Example SQL> Select * from emp1 union all select * from emp2; A={ 1,5,6,3} b= {6,8,4,3,2} AUB = { 1,5, 6,3,6,8,4,3,2} SQL> select city from salesman union all select city from customer;

  14. Intersect • Returns common rows selected by both queries Example SQL> Select * from emp1 intersect select * from emp2; A={ 1,5,6,3} b= {6,8,4,3,2} AUB = { 6,3 } SQL> select city from salesman union select city from customer;

  15. Minus • Returns all distinct rows that

More Related