Note:
Most of these examples are taken from the standard tables emp, dept
that comes with Oracle Editions. So that it would be easy for every
developer.
1) Question: Can you give all dates from start of this month
Answer: ( I took sep-2015 as example)
SELECT C_DATE,TO_CHAR(C_DATE,'DAY')
FROM
(
SELECT TO_DATE('01-SEP-2015','DD-MON-YYYY')+LEVEL-1 C_DATE
FROM DUAL
CONNECT BY LEVEL <=
CEIL(SYSDATE - TO_DATE('01-SEP-2015','DD-MON-YYYY'))
)
This query is used in the following scenario
2) Question :Can I get product wise sales in this month. The query should return 0 as quantity when there is no records for that product for that day.
This is tricky because we need to get the date even if that day is not present in main product table. So from above query we will get all dates and do a OUTER JOIN with product table and NVL with 0 for the sum.
Note: This need a product table . Parking this solution for JOINS
3) Question : Can you give 5th (nth) highest salaried employee .
This is very common question and some time it may be asked not to test your knowledge but as a casual question to start interview. Incorrect answer may give a bad thought. But explaining the details like importance of the "order by" clause usage before the "rownum" would give a good impression.
select * from
( select empno,ename,sal,rownum rnk from
( select * from emp
order by sal desc, ename asc
)
)
where rnk =5;
Alternatives using RANK and DENSE_RANK
----------------------------------------------
Using RANK or DENSE_RANK is little different as these functions will rank equal salaries at the same rank. So be careful about the question. So to avoid same rank to two employees I have used additional clause of "ename asc" . To find the difference between rank and dense_rank remove the clause"ename asc"
in the below query.
select empno,ename,sal,
rank() over (order by sal desc,ename asc) rnk,
dense_rank() over (order by sal desc,ename asc) dense_rnk from emp
4) Question: In continuation to RANK , Can we print alternate records from highest salaried
Typically, the output should be 1 , 3, 5th ...highest salaried employees.
Solution: Simply add the MOD function to the ranck.
select * from
( select empno,ename,sal,rownum rnk from
( select * from emp order by sal desc, ename asc )
)
where mod(rnk,2)=1
5) Question: Transpose rows to Columns in a table for a report
Typical Reporting tools would take data from summary tables.
To know department wise sum of salaries in a single row you can use either of following queries
11i oracle supports PIVOT to deal with Transpose:
------------
select * from (select deptno,sal from emp)
pivot (sum(sal) DEPT_EXPENSE FOR deptno IN (10 as A,20 as B,30 as C));
General
----------
select sum(decode(deptno,10,sal,0)) as A_DEPT_EXPENSE,
sum(decode(deptno,20,sal,0)) as B_DEPT_EXPENSE,
sum(decode(deptno,30,sal,0)) as C_DEPT_EXPENSE
from emp;
Both the queries applied on the standard emp table provide following output
A_DEPT_EXPENSE B_DEPT_EXPENSE C_DEPT_EXPENSE
8750 10875 9400
This is very useful in analytical queries.
6) Question: Cartesian product of the SQL
The general question would be I have a table A and B with 2 and 3 rows respectively.
How many rows or what is the output of "Select A.*,B.* from A,B;"
As we have to common column that impose any restrictions the result would be 6 rows as cartesian product.
7) Question: Remove Duplicate records from table
Most common question for SQL developers.
Normal way
--------------
DELETE FROM emp A WHERE ROWID >
( SELECT min(rowid) FROM emp B WHERE A.empno = B.empno);
Note : in the inner where clause you have to give all the key columns or all columns
if there is no key with a.col1 = b.col1 and a.col2=b.col2..and so on .
Better approach
-----------------
1) create table t as select distinct * from emp;
2) drop table emp;
3) rename t to emp;
Note: This is faster because it has less memory cleaning activity.
Assuming all integrity constaints taken care.
When using PLSQL collections
--------------------
DECLARE
type testtab IS TABLE OF VARCHAR2(10);
fist testtab;
second testtab;
BEGIN
fist := testtab('a', 'b', 'c', 'd', 'c', 'b', 'a');
second := SET(fist);
IF (second.count = fist.count) THEN
dbms_output.put_line('no duplicates');
ELSE
--Duplicates case
second := SET(fist MULTISET UNION DISTINCT second);
END IF;
END;
8) Question: Difference betwen IN and EXIST
EXIST clause is not liked by many programmers because of its syntax. But generally it gives better performance over the IN clause because it mandates JOIN and that can be used on an indexed column.
All the following query will give same result that is KING's direct reportees.
---------------------------
select * from emp e where mgr in (select empno from emp where ename = 'KING');
select * from emp e1,(select empno from emp where ename = 'KING') e2
where e1.mgr = e2.empno;
select * from emp e where exists (select 0 from emp e2 where
e.mgr = e2.empno and ename = 'KING');
select 0 , 1 , null or 'X' in inner query all are same.
--------------------------------------------------------------
select * from emp e where exists (select 'X' from emp e2 where
e.mgr = e2.empno and ename = 'KING');
select * from emp e where exists (select 1 from emp e2 where
e.mgr = e2.empno and ename = 'KING');
select * from emp e where exists (select null from emp e2 where
e.mgr = e2.empno and ename = 'KING');
Simple points to remember here ,
i) IN is driven by inner query table -- So gives good performance if inner table is small
ii) IN is two sequential queries -- So goes with sub-query followed by outer query
iii) EXIST is driven by outer query -- Similar to co-related sub - query
iv) EXIST mandates a join -- So we have chance to use the indexed join column.
v) Just forget what is in select 0 , 1 ,null or 'X' in EXIST it will automatically relates it
to condition column in the outer column.Its just alias or generic of the column you
are expecting in the INNER query. This clears the confusion with IN
9) Question: NOT IN and NOT EXIST
Here the main difference is in the way of dealing with NULL. Other than this almost answers are similar to the difference between IN and EXIST as above. NOT IN is avoided by many developers because its inability in handling NULLs.
Example:
select * from emp where empno not in (select mgr from emp)
-- No rows. Means all employees are managers
select * from emp e1 where not exists
(select null from emp e2 where e2.mgr=e1.empno)
-- Returns some rows means employees who are not managers.
Note: the difference is due to NULL
10) Question: Find Medians of salary , overall and dept wise
There needs a query to find salary median to know over all and dept wise.
This helps in reports.
Solution:
select empno,ename,sal,deptno,
percentile_disc(0.5) within group ( order by sal) over() median,
percentile_disc(0.5) within group ( order by sal)
over(partition by deptno) dept_median
from emp order by 4
11) Question: How do I return multiple values from a PLSQL Function
Return a collection instead of single value.
Example:
TYPE new_type is record(RES pls_integer, RESERVED_SEAT_NO pls_integer);
Create function.....
RETURN new_type AS new_type_variable
1) Question: Can you give all dates from start of this month
Answer: ( I took sep-2015 as example)
SELECT C_DATE,TO_CHAR(C_DATE,'DAY')
FROM
(
SELECT TO_DATE('01-SEP-2015','DD-MON-YYYY')+LEVEL-1 C_DATE
FROM DUAL
CONNECT BY LEVEL <=
CEIL(SYSDATE - TO_DATE('01-SEP-2015','DD-MON-YYYY'))
)
This query is used in the following scenario
2) Question :Can I get product wise sales in this month. The query should return 0 as quantity when there is no records for that product for that day.
This is tricky because we need to get the date even if that day is not present in main product table. So from above query we will get all dates and do a OUTER JOIN with product table and NVL with 0 for the sum.
Note: This need a product table . Parking this solution for JOINS
3) Question : Can you give 5th (nth) highest salaried employee .
This is very common question and some time it may be asked not to test your knowledge but as a casual question to start interview. Incorrect answer may give a bad thought. But explaining the details like importance of the "order by" clause usage before the "rownum" would give a good impression.
select * from
( select empno,ename,sal,rownum rnk from
( select * from emp
order by sal desc, ename asc
)
)
where rnk =5;
Alternatives using RANK and DENSE_RANK
----------------------------------------------
Using RANK or DENSE_RANK is little different as these functions will rank equal salaries at the same rank. So be careful about the question. So to avoid same rank to two employees I have used additional clause of "ename asc" . To find the difference between rank and dense_rank remove the clause"ename asc"
in the below query.
select empno,ename,sal,
rank() over (order by sal desc,ename asc) rnk,
dense_rank() over (order by sal desc,ename asc) dense_rnk from emp
4) Question: In continuation to RANK , Can we print alternate records from highest salaried
Typically, the output should be 1 , 3, 5th ...highest salaried employees.
Solution: Simply add the MOD function to the ranck.
select * from
( select empno,ename,sal,rownum rnk from
( select * from emp order by sal desc, ename asc )
)
where mod(rnk,2)=1
5) Question: Transpose rows to Columns in a table for a report
Typical Reporting tools would take data from summary tables.
To know department wise sum of salaries in a single row you can use either of following queries
11i oracle supports PIVOT to deal with Transpose:
------------
select * from (select deptno,sal from emp)
pivot (sum(sal) DEPT_EXPENSE FOR deptno IN (10 as A,20 as B,30 as C));
General
----------
select sum(decode(deptno,10,sal,0)) as A_DEPT_EXPENSE,
sum(decode(deptno,20,sal,0)) as B_DEPT_EXPENSE,
sum(decode(deptno,30,sal,0)) as C_DEPT_EXPENSE
from emp;
Both the queries applied on the standard emp table provide following output
A_DEPT_EXPENSE B_DEPT_EXPENSE C_DEPT_EXPENSE
8750 10875 9400
This is very useful in analytical queries.
6) Question: Cartesian product of the SQL
The general question would be I have a table A and B with 2 and 3 rows respectively.
How many rows or what is the output of "Select A.*,B.* from A,B;"
As we have to common column that impose any restrictions the result would be 6 rows as cartesian product.
7) Question: Remove Duplicate records from table
Most common question for SQL developers.
Normal way
--------------
DELETE FROM emp A WHERE ROWID >
( SELECT min(rowid) FROM emp B WHERE A.empno = B.empno);
Note : in the inner where clause you have to give all the key columns or all columns
if there is no key with a.col1 = b.col1 and a.col2=b.col2..and so on .
Better approach
-----------------
1) create table t as select distinct * from emp;
2) drop table emp;
3) rename t to emp;
Note: This is faster because it has less memory cleaning activity.
Assuming all integrity constaints taken care.
When using PLSQL collections
--------------------
DECLARE
type testtab IS TABLE OF VARCHAR2(10);
fist testtab;
second testtab;
BEGIN
fist := testtab('a', 'b', 'c', 'd', 'c', 'b', 'a');
second := SET(fist);
IF (second.count = fist.count) THEN
dbms_output.put_line('no duplicates');
ELSE
--Duplicates case
second := SET(fist MULTISET UNION DISTINCT second);
END IF;
END;
8) Question: Difference betwen IN and EXIST
EXIST clause is not liked by many programmers because of its syntax. But generally it gives better performance over the IN clause because it mandates JOIN and that can be used on an indexed column.
All the following query will give same result that is KING's direct reportees.
---------------------------
select * from emp e where mgr in (select empno from emp where ename = 'KING');
select * from emp e1,(select empno from emp where ename = 'KING') e2
where e1.mgr = e2.empno;
select * from emp e where exists (select 0 from emp e2 where
e.mgr = e2.empno and ename = 'KING');
select 0 , 1 , null or 'X' in inner query all are same.
--------------------------------------------------------------
select * from emp e where exists (select 'X' from emp e2 where
e.mgr = e2.empno and ename = 'KING');
select * from emp e where exists (select 1 from emp e2 where
e.mgr = e2.empno and ename = 'KING');
select * from emp e where exists (select null from emp e2 where
e.mgr = e2.empno and ename = 'KING');
Simple points to remember here ,
i) IN is driven by inner query table -- So gives good performance if inner table is small
ii) IN is two sequential queries -- So goes with sub-query followed by outer query
iii) EXIST is driven by outer query -- Similar to co-related sub - query
iv) EXIST mandates a join -- So we have chance to use the indexed join column.
v) Just forget what is in select 0 , 1 ,null or 'X' in EXIST it will automatically relates it
to condition column in the outer column.Its just alias or generic of the column you
are expecting in the INNER query. This clears the confusion with IN
9) Question: NOT IN and NOT EXIST
Here the main difference is in the way of dealing with NULL. Other than this almost answers are similar to the difference between IN and EXIST as above. NOT IN is avoided by many developers because its inability in handling NULLs.
Example:
select * from emp where empno not in (select mgr from emp)
-- No rows. Means all employees are managers
select * from emp e1 where not exists
(select null from emp e2 where e2.mgr=e1.empno)
-- Returns some rows means employees who are not managers.
Note: the difference is due to NULL
10) Question: Find Medians of salary , overall and dept wise
There needs a query to find salary median to know over all and dept wise.
This helps in reports.
Solution:
select empno,ename,sal,deptno,
percentile_disc(0.5) within group ( order by sal) over() median,
percentile_disc(0.5) within group ( order by sal)
over(partition by deptno) dept_median
from emp order by 4
11) Question: How do I return multiple values from a PLSQL Function
Return a collection instead of single value.
Example:
TYPE new_type is record(RES pls_integer, RESERVED_SEAT_NO pls_integer);
Create function.....
RETURN new_type AS new_type_variable
No comments:
Post a Comment