Interview SQL queries

1.   Find the Duplicate records in table.
 
   1. SELECT email, COUNT(email) AS NumOccurrences
FROM T 
GROUP BY email HAVING ( COUNT(email) > 1 ) ;
  
 2. SELECT * FROM emp a
     WHERE rowid = ( SELECT max(rowid) FROM emp
                            WHERE empno = a.empno
                            GROUP BY empno HAVING count(*) >1)

2.   Delete Duplicate records from table.
    1.  DELETE FROM table_name A 
WHERE ROWID > (SELECT min(rowid) 
FROM table_name B  
WHERE A.key_val = B.key_val);


    2. 


1.   Find the Duplicate records in table.





















Consider the below DEPT and EMPLOYEE table and answer the below queries.
DEPT
DEPTNO (NOT NULL , NUMBER(2)),
DNAME (VARCHAR2(14)),
LOC (VARCHAR2(13)

EMPLOYEE
EMPNO (NOT NULL , NUMBER(4)),
ENAME (VARCHAR2(10)),
JOB (VARCHAR2(9)),
MGR (NUMBER(4)),
HIREDATE (DATE),
SAL (NUMBER(7,2)),
COMM (NUMBER(7,2)),
DEPTNO (NUMBER(2))

MGR is the EMPno of the Employee whom the Employee reports to.DEPTNO is a foreign key
1. List all the Employees who have at least one person reporting to them.</span><br /> 

SELECT ENAME FROM EMPLOYEE WHERE EMPNO IN (SELECT MGR FROM EMPLOYEE); 

2. List the highest salary paid for each job.

SELECT JOB, MAX(SAL) FROM EMPLOYEE GROUP BY JOB

3. In which year did most people join the company? Display the year and the number of Employees 
SELECT TO_CHAR(HIREDATE,'YYYY') "YEAR", COUNT(EMPNO) "NO. OF EMPLOYEES"
FROM EMPLOYEE
GROUP BY TO_CHAR(HIREDATE,'YYYY')
HAVING COUNT(EMPNO) = ( SELECT MAX(COUNT(EMPNO))
FROM EMPLOYEE
GROUP BY TO_CHAR(HIREDATE,'YYYY'));
  
4.  Write a correlated sub-query to list out the Employees who earn more than the average salary of their department. 
SELECT ENAME,SAL
FROM EMPLOYEE E
WHERE SAL &gt; (SELECT AVG(SAL)
FROM EMPLOYEE F
WHERE E.DEPTNO = F.DEPTNO);

5. Find the nth maximum salary.  
SELECT ENAME, SAL
FROM EMPLOYEE A
WHERE &N = (SELECT COUNT (DISTINCT(SAL))
FROM EMPLOYEE B
WHERE A.SAL <=B.SAL);  

6. Select the duplicate records (Records, which are inserted, that already exist) in the EMPLOYEE table.  
SELECT * FROM EMPLOYEE A
WHERE A.EMPNO IN (SELECT EMPNO                                        
FROM EMPLOYEE                                        
GROUP BY EMPNO                                          
HAVING COUNT(EMPNO)> 1)
AND A.ROWID!=MIN (ROWID));


7.  Write a query to list the length of service of the Employees (of the form n years and m months).
SELECT ENAME "EMPLOYEE",
        TO_CHAR(TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12))
||' YEARS '|| TO_CHAR(TRUNC(MOD(MONTHS_BETWEEN
(SYSDATE, HIREDATE),12)))||' MONTHS ' "LENGTH OF SERVICE"
FROM EMPLOYEE;

Joseph Problem...

Joseph likes taking part in programming contests. His favorite problem is, of course, Joseph's problem. It is stated as follows.

Problem : There are n persons numbered from 0 to n - 1 standing in a circle. The person number k, counting from the person number 0, is executed. After that the person number k of the remaining persons is executed, counting from the person after the last executed one. The process continues until only one person is left. This person is a survivor. The problem is, given n and k detect the survivor's number in the original circle.

  Solution: You can easily write a recursive write:
 Example:(20, 3) = 4+17-1 =20 Find J (20, 3), assume that you have to know the J (19, 3) = 17, then remove the number 3 20 people who left with 19 people, and this 19 starting from the number 4, 17 the last person is left, so J (20, 3) = 4 +17-1 = 20
 

public class JoshephProblem {
    public static void main(String[] args) {
        System.out.println(">>> " + J(5, 3));
    }

    public static int J(int n, int m) {
        if (n == 2) {
            if (m % 2 == 1)
                return 2;
            else
                return 1;
        } else {
            int temp = (J(n - 1, m) + m) % n;
            if (temp == 0)
                return n;
            else
                return temp;
        }
    }
}