SQL Interview Questions

SQL QUERIES :-

-----------------

* How to create a copy of table from original table in Oracle? 

create table emp2 as select * from emp;

* How to find 2nd highest salary ?

select max(sal) from emp where sal < (select max(sal) from emp);

                                               or

select * from emp where sal = (select max(sal) from emp where sal < (select max(sal) from   emp));

* How to find 3rd highest salary ?

select max(sal) from emp where sal < (select max(sal) from emp where sal < (select  max  (sal) from emp));

                                                   or

select * from emp where sal = (select max(sal) from emp where sal < (select max(sal) from   emp where sal < (select max  (sal) from emp)));

* How to find 2nd lowest salary ?

select min(sal) from emp where sal > (select min(sal) from emp);

                                                  or

select * from emp where sal = (select min(sal) from emp where sal > (select min(sal) from emp));

* How to find 3rd lowest salary ?

(select min(sal) from emp where sal > (select min(sal) from emp where sal > (select min(sal) from emp));

                                                     or

select * from emp where sal = (select min(sal) from emp where sal > (select min(sal) from emp where sal > (select min(sal)   from emp)));

* How to find first half rows in table ?

select * from emp where rownum <= (select count(*)/2 from emp);

* How to find last half rows in table ?

select * from emp minus select * from emp where rownum <= (select count(*)/2 from emp);

* How to find first 3 highest salaries from emp table ?

select * from (select * from emp order by sal desc) where rownum<=3;

* How to find first 3 lowest salaries from emp table ?

select * from (select * from emp order by sal) where rownum<=3;

* Display the employees who joined in the year 1981.
SELECT * FROM EMP
WHERE
TO_CHAR(HIREDATE,'YYYY')='1981';


* Display the employees who joined before 1981.
SELECT * FROM EMP
WHERE
TO_CHAR(HIREDATE,'YYYY') < '1981';


* Display the employees who does not belong to department 10.
SELECT * FROM EMP
WHERE
DEPTNO NOT IN (10);


* Display the average salaries of all the employees who works as ANALYST.
SELECT AVG(SAL) FROM EMP
WHERE
JOB='ANALYST';


* Display all the details of the employees whose commission is more than their salary
SELECT * FROM EMP
WHERE
COMM > SAL;


* Display the employees whose salary is more than 3000 after giving 25% increment.
SELECT * FROM EMP
WHERE
SAL+SAL*25/100 > 3000;


* Display the name of the employees, those having six characters to their name.
SELECT * FROM EMP
WHERE
LENGTH(ENAME)=6;


* Display the employees who joined in the month January.
SELECT * FROM EMP
WHERE
TO_CHAR(HIREDATE,'MON')='MAY';


* Display the name of employees and their manager separated by the string 'works for'.
SELECT E.ENAME || ' WORKS FOR ' || M.ENAME
FROM EMP E, EMP M
WHERE
E.MGR = M.EMPNO;

* Display the employees whose experience is more than 27 years.
SELECT * FROM EMPLOYEES
WHERE
HIRE_DATE - SYSDATE < 27

* Employees who earn more than 100 as daily salary
SELECT * FROM EMP WHERE SAL/30 > 100;

* Write a query in SQL to list all the employees joined on 1st may 91
SELECT * FROM EMP WHERE HIREDATE = '1-MAY-91';

* Write a query in SQL to list the employees who are retiring after 31-Dec-99 after completion of 8 years of service period.
SELECT emp_name FROM employees
WHERE hire_date + interval '96 months' > '1999-12-31';

* Write a query in SQL to list those employees whose salary is an odd value.
SELECT * FROM EMP WHERE MOD(SAL,2)=1;

* Display all the records but common records only once from both the table EMP and EMP1
SELECT * FROM EMP
UNION
SELECT * FROM EMP1;


* Fetch only common records from two tables emp and emp1
SELECT * FROM EMP
INTERSECT
SELECT * FROM EMP1;


* Display all records of emp1 those should not present in emp2
SELECT * FROM EMP
MINUS
SELECT * FROM EMP1;

SQL SUB-QUERIES :-

--------------------- 

* Query to Display only duplicates from a table ?

SELECT * FROM EMP

WHERE

EMPNO IN

(SELECT COUNT(EMPNO) FROM EMP

GROUP BY EMPNO

HAVING

COUNT(EMPNO)>1);

* How do you delete duplicate records ?

DELETE FROM EMP

WHERE

EMPNO IN

(SELECT COUNT(EMPNO) FROM EMP

GROUP BY EMPNO

HAVING

COUNT(EMPNO)>1);

* Query to delete duplicates only single instance should be present.

SELECT * FROM EMP 

WHERE EMPNO NOT IN 

(SELECT MIN(EMPNO) FROM EMP 

GROUP BY EMPNO)

* Write a query in SQL to display all the details of managers.
SELECT * FROM EMPLOYEES
WHERE EMPLOYEE_ID IN
(SELECT MANAGER_ID FROM EMPLOYEES);

* Write a query in SQL to display the employee ID, name, job name, hire date, and experience of all the managers.

SELECT EMPNO, ENAME, JOB, HIREDATE FROM EMP 

WHERE EMPNO IN

(SELECT EMPNO FROM EMP WHERE JOB = 'MANAGER')

* Write a query in SQL to list all the employees of grade 2 and 3.
SELECT * FROM EMPLOYEES, SALARY_GRADE
WHERE GRADE IN (2,3);

* Write a query in SQL to list all the employees where salary between min & min salary, grade is 2 and 3.
SELECT * FROM EMPLOYEES, SALARY_GRADE
WHERE SALARY BETWEEN MIN_SAL AND MAX_SAL
AND GRADE IN (2,3);

(OR)

SELECT * FROM EMPLOYEES E, SALARY_GRADE S
WHERE E.SALARY BETWEEN S.MIN_SAL AND S.MAX_SAL
AND S.GRADE IN (2,3);

* Write a query in SQL to display all the employees of grade 4 and 5 who are working as ANALYST or MANAGER.
SELECT * FROM EMPLOYEES E, SALARY_GRADE S
WHERE GRADE IN (4,5)
AND JOB_NAME IN ('ANALYST','MANAGER');

* Write a query in SQL to list the details of the employees whose salary is more than the salary of JONES.

SELECT * FROM EMP WHERE SAL > 

(SELECT SAL FROM EMP WHERE ENAME = 'JONES')

* Write a query in SQL to list the employees who works in the same designation as CLARK.

SELECT * FROM EMP WHERE JOB =

(SELECT JOB FROM EMP WHERE ENAME = 'CLARK')

* List the employees who are senior to KING

SELECT * FROM EMP WHERE HIREDATE >

(SELECT HIREDATE FROM EMP WHERE ENAME = 'KING')

 

SQL INTERVIEW QUESTIONS :-

---------------------------------------------
* What are the different comparison conditions in oracle ? What is the importance of them ?
- ALL, ANY and SOME are different Comparison Conditions in SQL.
- ALL comparison condition is used to compare to a subquery, It should be preceded by =,>,<,>=,<=, operators in sql.
- ALL operator returns true, if all of the subquery values meet the condition.
 

* How do you tune or optimize sql queries ?
- select field name instead of using select *
- avoid using distinct command

- Use Order by at the end of statement or comment it (--)

- User Indexes for make data retrieval faster

- User View or Materialized views     
- create joins using inner joins not by where clause

* What is the difference between Order by and Group by ?
Group by is used with Aggregate functions and arrange the data into columns.
Order by is used to sort data in either ascending and decending order.


* What is the difference between Having clause and Where clause ?
- Where clause cannot be used with aggregates, but the having clause can be used.
- Apart from SELECT queries, you can use WHERE clause with UPDATE and DELETE clause but HAVING clause can only be used with SELECT query.
- WHERE clause is used for filtering rows and it applies on each and every row, while HAVING clause is used to filter groups in SQL.
- One syntax level difference between WHERE and HAVING clause is that, former is used before GROUP BY clause, while later is used after GROUP BY clause


* What is the difference between Aggregate and Analytical Functions ?
- The functions SUM, COUNT, AVG, MIN, MAX are the common aggregate functions, the result of which does not depend on the order of the records, where as Analytical functions like LEAD, LAG, RANK, DENSE_RANK, ROW_NUMBER, FIRST, FIRST VALUE, LAST, LAST VALUE depends on order of records.
- Aggregate functions Return a single output by calculating values from the table, where as Analytic functions Return the same number of rows as the input. 


* What is a Partitioning clause ?
- It is used to breakup or divide table data into many partitions or parts.
- The PARTITION BY clause is a sub clause of the OVER clause.




* What is difference between index and bitmap index ?

* How do you join two tables with SQL inner join ?
SELECT EMPNO, ENAME FROM EMP
INNER JOIN DEPT
ON EMP.DEPTNO=DEPT.DEPTNO;

or

SELECT * FROM EMP E, DEPT D

WHERE E.DEPTNO=D.DEPTNO 

AND DNAME ='ACCOUNTING'

* How do you join two tables with mulitple conditions with SQL inner join ?
SELECT EMPNO, ENAME FROM EMP
INNER JOIN DEPT
ON EMP.DEPTNO=DEPT.DEPTNO
AND EMP.DNAME=DEPT.DNAME; 

* How do you join more than two tables with SQL inner join ?
Select * from Emp 
Inner Join Dept
on Emp.deptno = Dept.deptno
Inner join Emp1
On Dept.deptno = Emp1.deptno;


* How to get Nth Max Salary?

SELECT * FROM

(SELECT *, DENSE_RANK() OVER (ORDER BY SAL DESC) AS HSAL

FROM EMP ) AS E

WHERE HSAL=5;

OR 

SELECT * FROM

(SELECT E.*, DENSE_RANK() OVER (ORDER BY SAL DESC) AS HSAL

FROM EMP E) AS E

WHERE HSAL=5;

* Query to display unique Job from Emp table.
SELECT DISTINCT JOB FROM EMP;

* Query to display unique Job from Emp table without using Distinct keyword
SELECT JOB FROM EMP GROUP BY JOB;

* Difference between delete and truncate command ?
We can rollback the data, when we use delete command but in the case of truncate its not possible.
Delete removes only the rows from table but it cannot delete space containing the table where as truncate deletes all the rows and free the space containing the table
We can use where clause in delete but in the case of truncate its not possible.

* Subquery or join is better based on performance ?
JOINs are faster than sub-queries and it is very rare for a sub-query to be faster.

* What is the functional difference between oracle and sql ?
- Language used by Oracle is PL/SQL (Procedural Language/ SQL), where as Language used by SQL Server is T-SQL (Transact-SQL).
- In Oracle, no transaction is committed until DBA explicitly issues COMMIT command, where as If BEGIN TRANSACTION and COMMIT commands are not specified, it executes and commit each command individually.
- The syntax of the commands used by Oracle and SQL also differs.
- In Oracle, the procedures, functions and variables are encapsulated into Packages. However, SQL does not have Packages.

* Difference between stop and abort function ?
When you issue a stop command on a session, the integration service first stops reading the data from the sources. It continues processing and writing data to the targets and then commits the data.
Abort command is handled the same way as the stop command, except that the abort command has timeout period of 60 seconds. If the Integration Service cannot finish processing and committing data within the timeout period, it kills the DTM process and terminates the session.


* All types of Joins and their outputs
 
Example 1:-

    EMP_NO EMP_NAME             JOB_NAME       MGR_ID    DEPT_NO
    ----------     -------------------- ---------- ---------- ----------
      1234 Alex                 Clerk            4567         15
      2345 Jack                 Consultant       3456         25
      3456 Paul                 Manager          1234         15
      4567 Jenefer              Engineer         2345         45

   DEPT_NO DEP_NAME   LOCATION
    ---------- ---------- ----------
        15 FINANCE    PARIS
        25 MARKETING  LONDON
        35 HR         DELHI

Innerjoin: 2
Left join: 4
Rightjoin: 3
Full join: 5

Example 2:-

TableX    TableY
------    ------
A    C
B    D
C    E
D    F

Innerjoin output:

X    Y
--    --
C    C
D    D

Leftjoin output:

X    Y
--    --
A    NULL
B    NULL
C    C
D    D

Rightjoin output:

X    Y
--    --
C    C
D    D
NULL    E
NULL    F 

* HOW DO YOU SWAP GENDER IN ORACLE ?
UPDATE GENDER
SET GENDER = CASE GENDER WHEN 'MALE' THEN 'FEMALE'
ELSE 'MALE'
END;

* What the different Operator available in SQL?

Arithmetic Operators 

Comparison Operators

Logical Operators

" + - * / % " are the list of Arithmetic Operators

" = > < >= <= !< !> " are the list of Comparison Operators

" ALL AND ANY SOME LIKE IN BETWEEN NOT EXISTS OR NULL " are the list of Logical Operators.

* How to combine two commands in SQL?

UNION, EXCEPT and INTERSECT Clause are used to combine the results of two SELECT statements.