set operators UNION,UNION ALL,INTERSECT,MINUS Privileges , Roles

 

SET OPERATORS

·        A set operator combines the result of two or more queries into one result.

·        Queries containing set operator are called compound queries.

·        No of columns and datatypes of the columns being selected must be same as all select statements used in the query and names of the columns need not be same.

·        Types of set operators

1.      UNION

2.      UNION ALL

3.      INTERSECT

4.      MINUS

1.UNION: - it will be written all rows from multiple queries by eliminating duplicate rows if any.

Ø  Select deptno, job from emp where deptno=10 UNION Select deptno, job from emp where deptno=20;

2.UNION ALL: - it will be written all rows from multiple queries by including duplicate rows if any.

Ø  Select deptno, job from emp where deptno=10 UNION ALL Select deptno, job from emp where deptno=20;

3.INTERSECT: - it written all common rows from multiple queries

Ø  Select deptno, job from emp where deptno=10 INTERSECT Select deptno, job from emp where deptno=20;

4.MINUS: - it written rows from 1^st query that are not present in the second query.

Ø  Select deptno, job from emp where deptno=10 MINUS Select deptno, job from emp where deptno=20;

PRIVILEGES

·        Privileges are the right or permission to execute particular SQL statements. The database administrator (DBA) is high level user the ability to grant user access to the database and its objects.

·        The users use the system privileges to gain access to the database and object privileges to manipulate the content of the objects in the database.

·        Users can also give privileges to grant, revoke privileges to the other user or ROLEs

·        Types of privileges

à 1.System privileges: - it allows a user to perform certain actions with in the database.

à 2.Object privileges: - it allows a user to perform certain actions on the database objects.

·        USER_TAB_PRIVS_MADE : - it will store information about privileges on schema object.

·        USER_COL_PRIVS_MADE : - it will store information about privileges on columns object.

 

 

ROLEs in oracle

à A role is named group of Related privileges that can be granted to user.

à Advantage: - Rather than assigning privileges one by one directly to the user we can create ‘ROLE’ then assigning all privileges to that ROLE and GRANT that ROLE to multiple users and roles.

à When you add or delete privileges to that role, all users and roles assigned that role automatically receive or loose that privileges.

à A role can be assigned with password

à Creating simple ROLE

Create role sales_manager;

à Creating a ROLE with password

Create role sales_manager identified by salesaudit;

Here ‘salesaudit’ is password of ROLE sales_manager

à To change password of a ROLE

Alter role sales_manager identified by sales;

Here password changed to sales

à Grant privileges to that ROLE

Grant create table, insert table to sales_manager;

à Grant ROLE to the user

Grant sales_manager to system;

Here system is one user.

à Revoke privilege from a ROLE

Revoke sales_manager from system;

à Dropping a ROLE

Drop role sales_manager;

      Please watch and subscribe my YOUTUBE Channel

                Kamal SQL CLASSES in Telugu