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;