Difference Between System Level Privileges and Objects Level Privileges

System Privileges	Table Level Privileges (Object Level)
System Privileges are normally granted by a DBA to users.	Object privileges means privileges on objects such as tables, views, synonyms, procedure. These are granted by owner of the object.
This privileges allows a user to manage database and server.	This privileges allows a user to perform certain action upon certain database objects
List of privileges ·         CREATE USER ·         CREATE TABLE ·         CREATE SESSION	List of privileges ·         SELECT ·         INSERT,UPDATE,DELETE ·         EXECUTE
To get information about system level privileges ·         SELECT * FROM USER_SYS_PRIVS ·         SELECT * FROM ROLE_SYS_PRIVS	To get information about object level privileges ·         SELECT OWNER,TABLE_NAME,PRIVILEGE FROM USER_TAB_PRIVS ·         SELECT * FROM ROLE_TAB_PRIVS
Syntax GRANT privileges TO username;	Syntax GRANT privileges ON object TO username;
Example GRANT CREATE SESSION, CREATE PROCEDURE TO PARAG;	Example GRANT SELECT, INSERT ON EMPLOYEE TO PARAG;

SYSTEM LEVEL PRIVILEGES AND OBJECTS LEVEL PRIVILEGES

SQL Query to recover dropped tables.

Is it possible to recover the dropped tables?

Yes, We can recover the dropped tables using flashback query if it is not permanently deleted.

Whenever you execute any dropped table query, dropped table information are stored in recyclebin. You can execute show recyclebin, it will display objects in recyclebin.

Example

SQL> CREATE TABLE TEST(ID INT);

Table created.

SQL> INSERT INTO TEST VALUES(1001);

1 row created.

SQL> INSERT INTO TEST VALUES(1002);

1 row created.

SQL> INSERT INTO TEST VALUES(1003);

1 row created.

SQL> INSERT INTO TEST VALUES(1004);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> DROP TABLE TEST;

Table dropped.

SQL> SHOW RECYCLEBIN
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST             BIN$Q2h8H7dpTPa/+/wsarPBgA==$0 TABLE        2016-08-13:15:08:34

SQL> FLASHBACK TABLE TEST TO BEFORE DROP;

Flashback complete.

SQL> SELECT * FROM TEST;

        ID
----------
      1001
      1002
      1003
      1004


We can also clear all the object from recyclebin using purge command.

Example

SQL> DROP TABLE TEST;

Table dropped.

SQL> SHOW RECYCLEBIN
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST             BIN$WztpWgD2TOCdtIxsAv+CSw==$0 TABLE        2016-08-13:15:14:53

SQL> PURGE RECYCLEBIN;

Recyclebin purged.


SQL> SHOW RECYCLEBIN

We can also recover dropped table with new name.

Example

SQL> DROP TABLE TEST;

Table dropped.

SQL> SELECT * FROM NEWTEST;
SELECT * FROM NEWTEST
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> FLASHBACK TABLE TEST TO BEFORE DROP
  2     RENAME TO NEWTEST;

Flashback complete.

SQL> SELECT * FROM NEWTEST;

        ID
----------
      1001
      1002
      1003

      1004

SQL query for hierarchical retrieval of employees working in any organization.

To evaluates hierarchical query oracle provides PRIOR expression of CONNECT BY condition.

Example

CREATE TABLE employees 
  ( 
     empid       INT PRIMARY KEY, 
     ename       VARCHAR2(30) NOT NULL, 
     designation VARCHAR2(20), 
     parent_id   INT 
  );


SELECT * FROM EMPLOYEES;

     EMPID ENAME                          DESIGNATION           PARENT_ID
---------- ------------------------------ -------------------- ----------
         1 P.P.TYAG VALLABH SWAMIJI       SECRETARY
         2 Dr.J.N.Shah                    DIRECTOR                      1
         3 Dr.G.D.Acharya                 PRINCIPAL                     2
         4 Dr.S.R.VARMA                   PRINCIPAL                     2
         5 Dr.K.R.VADALIYA                PRINCIPAL                     2
         6 Dr.G.C.JOSHI                   PRINCIPAL                     2
         7 Dr.A.U.PATEL                   PRINCIPAL                     2
         8 Dr.NISHANT VACHHANI            HOD                           4
         9 Dr.ASHISH KOTHARI              HOD                           3
        10 PROF.J.N.RATHOD                HOD                           3
        11 PROF.P.C.SHUKLA                HOD                           4
        12 DR.STAVAN PATEL                HOD                           7
        13 PROF.ANKIT FALDU               ASSISTANT PROFESSOR          11
        14 DR.PRATIK VANZARA              ASSISTANT PROFESSOR          12
        15 PROF.VIVEK VYAS                ASSISTANT PROFESSOR          11
        16 PROF.KALPESH POPAT             ASSISTANT PROFESSOR          11
        17 PROF.PRAKASH GUJARATI          ASSISTANT PROFESSOR          12
        18 PROF.DIVYESH GOHEL             ASSISTANT PROFESSOR          12
        19 DR.AMIT RAJDEV                 ASSISTANT PROFESSOR           8
        20 PROF.ANKIT GANDHI              ASSISTANT PROFESSOR           8


20 rows selected.

SELECT Sys_connect_by_path(ename, '-') "Path" 
FROM   employees 
START WITH Upper(ename) = 'P.P.TYAG VALLABH SWAMIJI' 
CONNECT BY PRIOR empid = parent_id 

SELECT ename, designation, LEVEL 
FROM   employees 
START WITH Upper(ename) = 'P.P.TYAG VALLABH SWAMIJI' 
CONNECT BY PRIOR empid = parent_id 

How to Delete duplicate email_ids from table using SQL.

Sometimes when we upload any csv data or import any data may be a chance that our table have unique column id but having duplicate emails. In that case we have to delete duplicate emails.

SQL> CREATE TABLE EMPLOYEE(EMPNO INT PRIMARY KEY,
  2                        ENAME VARCHAR2(30) NOT NULL,
  3                        EMAIL_ID VARCHAR2(20));

Table created.

SQL> INSERT INTO EMPLOYEE VALUES(1001,'Parag C Shukla','pcs@gmail.com');

1 row created.

SQL> INSERT INTO EMPLOYEE VALUES(1002,'Kapil K Shukla','kks@gmail.com');

1 row created.

SQL> INSERT INTO EMPLOYEE VALUES(1003,'Patni Computer System','pcs@gmail.com');

1 row created.

SQL> INSERT INTO EMPLOYEE VALUES(1004,'Kalpataru Knowledge System','kks@gmail.com');

1 row created.

SQL> INSERT INTO EMPLOYEE VALUES(1005,'Deven J Patel','djp@gmail.com');

1 row created.

SQL> INSERT INTO EMPLOYEE VALUES(1006,'Hariom Tatsat','djp@gmail.com');

1 row created.

Following query help you to delete the duplicate email id from table.

DELETE
FROM EMPLOYEE
WHERE ROWID IN
   (SELECT ROWID FROM
        (SELECT EMPNO,ENAME,EMAIL_ID,RANK() OVER(PARTITION BY EMAIL_ID ORDER BY EMPNO)RANK
        FROM EMPLOYEE)T
     WHERE RANK>1 );

SQL Query to perform result analysis of the subjects

SQL QUERY TO PERFORM RESULT ANALYSIS OF THE SUBJECTS GRADE WISE. Query to COUNT TOTAL NO OF AA, AB, BB, BC, CC AND FF(FAIL).

SELECT 'C_LANG' SUBJECT,
    SUM(CASE WHEN C_LANG BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AA,
    SUM(CASE WHEN C_LANG BETWEEN 75 AND 84 THEN 1 ELSE 0 END) AB,
    SUM(CASE WHEN C_LANG BETWEEN 65 AND 74 THEN 1 ELSE 0 END) BB,
    SUM(CASE WHEN C_LANG BETWEEN 55 AND 64 THEN 1 ELSE 0 END) BC,
    SUM(CASE WHEN C_LANG BETWEEN 50 AND 54 THEN 1 ELSE 0 END) CC,
    SUM(CASE WHEN C_LANG < 50 THEN 1 ELSE 0 END) FF, COUNT(C_LANG) TOTAL
FROM STUDENT
UNION ALL
SELECT 'ORACLE' SUBJECT, 
    SUM(CASE WHEN ORACLE BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AA,
    SUM(CASE WHEN ORACLE BETWEEN 75 AND 84 THEN 1 ELSE 0 END) AB,
    SUM(CASE WHEN ORACLE BETWEEN 65 AND 74 THEN 1 ELSE 0 END) BB,
    SUM(CASE WHEN ORACLE BETWEEN 55 AND 64 THEN 1 ELSE 0 END) BC,
    SUM(CASE WHEN ORACLE BETWEEN 50 AND 54 THEN 1 ELSE 0 END) CC,
    SUM(CASE WHEN ORACLE < 50 THEN 1 ELSE 0 END) FF, COUNT(ORACLE) TOTAL
FROM STUDENT
UNION ALL
SELECT 'JAVA' SUBJECT, 
    SUM(CASE WHEN JAVA BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AA,
    SUM(CASE WHEN JAVA BETWEEN 75 AND 84 THEN 1 ELSE 0 END) AB,
    SUM(CASE WHEN JAVA BETWEEN 65 AND 74 THEN 1 ELSE 0 END) BB,
    SUM(CASE WHEN JAVA BETWEEN 55 AND 64 THEN 1 ELSE 0 END) BC,
    SUM(CASE WHEN JAVA BETWEEN 50 AND 54 THEN 1 ELSE 0 END) CC,
    SUM(CASE WHEN JAVA < 50 THEN 1 ELSE 0 END) FF, COUNT(JAVA) TOTAL
FROM STUDENT;

SQL Query for Grade Calculations

SQL Query to calculate grade of subjects based on marks.

CREATE TABLE STUDENT(ROLLNO NUMBER(3) PRIMARY KEY,
         NAME VARCHAR2(50),
         C_LANG NUMBER(3) CHECK(C_LANG BETWEEN 0 AND 100),
         JAVA NUMBER(3) CHECK(JAVA BETWEEN 0 AND 100),
         ORACLE NUMBER(3) CHECK(ORACLE BETWEEN 0 AND 100));





INSERT INTO STUDENT
VALUES(101,
       'PARAG SHUKLA',
       56,
       77,
       87);


INSERT INTO STUDENT
VALUES(102,
       'KAPIL SHUKLA',
       87,
       67,
       54);


INSERT INTO STUDENT
VALUES(103,
       'DEVEN PATEL',
       76,
       70,
       68);


INSERT INTO STUDENT
VALUES(104,
       'MUNO',
       48,
       78,
       30);


INSERT INTO STUDENT
VALUES(105,
       'CHAKO',
       45,
       75,
       57);

SELECT ROLLNO,
       CASE
         WHEN C_LANG BETWEEN 85 AND 100 THEN 'AA'
         WHEN C_LANG BETWEEN 75 AND 84 THEN 'AB'
           WHEN C_LANG BETWEEN 65 AND 74 THEN 'BB'
           WHEN C_LANG BETWEEN 55 AND 64 THEN 'BC'
           WHEN C_LANG BETWEEN 50 AND 54 THEN 'CC'
           WHEN C_LANG <50 THEN 'FF'
       END AS C_LANG,
       CASE
         WHEN ORACLE BETWEEN 85 AND 100 THEN 'AA'
           WHEN ORACLE BETWEEN 75 AND 84 THEN 'AB'
           WHEN ORACLE BETWEEN 65 AND 74 THEN 'BB'
           WHEN ORACLE BETWEEN 55 AND 64 THEN 'BC'
           WHEN ORACLE BETWEEN 50 AND 54 THEN 'CC'
           WHEN ORACLE <50 THEN 'FF'
       END AS ORACLE,
       CASE
         WHEN JAVA BETWEEN 85 AND 100 THEN 'AA'
           WHEN JAVA BETWEEN 75 AND 84 THEN 'AB'
           WHEN JAVA BETWEEN 65 AND 74 THEN 'BB'
           WHEN JAVA BETWEEN 55 AND 64 THEN 'BC'
           WHEN JAVA BETWEEN 50 AND 54 THEN 'CC'
           WHEN JAVA <50 THEN 'FF'
       END AS JAVA,
       NAME
FROM STUDENT;