Group By Clause
|
Partition By Clause
|
Reduces the no. of records
|
No. of records will not be reduced
|
Any non group by column is not allowed in the select clause.
Example :
SELECT deptno ,COUNT(*) DEPT_COUNT FROM emp GROUP BY deptno; |
Any non group by column is
allowed in the select clause. Example:
SELECT empno, deptno ,COUNT(*) OVER (PARTITION BY deptno) DEPT_COUNT FROM emp; |
Group by actually groups the result set returning one row per
group.
|
Analytic functions (Partition by) give aggregate result they do
not group the result set means They return the group value multiple times
with each record.
|
Oracle World
This blog will help you to know the basic of oracle DataBase and some important clause of Data Base Objects.
Monday 12 September 2016
Difference between Group By and Partition By.
Wednesday 15 June 2016
How to find duplicate records if one of the column has unique value.
SELECT Count(*)
FROM (
SELECT DISTINCT
Col1
, Col2
, Col3
FROM table_name
) As AAA
Thursday 2 June 2016
Difference Between IN and EXISTS
IN
|
EXIST
|
|
1
|
IN is faster than EXISTS, when the sub-query results is very small.
|
EXISTS is much faster than IN, when the sub-query
results is very large.
|
2
|
IN can't compare anything with null, so it has no result
for null. For Example:Select *
from Table where Table_ID in (select null) Above one would return empty. |
EXISTS can compare everything with null.
Select *
from Table where exists (select null). Above query will return all the records |
3
|
If you are using
the IN operator, the SQL engine will scan all
records fetched from the inner query
|
On the other hand if
we are using EXISTS, the SQL engine will stop the scanning
process as soon as it found a match.
|
4
|
If Most of the
filter criteria is in subquery then better to use IN
|
If most of the
filter criteria is in main query then better to use EXISTS.
|
Sunday 29 May 2016
Difference Between VIEW and Materialized View
View
|
Materialized
view
|
1.
In Views query result is not stored in the disk or database.
|
1. Materialized view allows to store
query result in disk or table.
|
2. When we create view using any
table, rowid of view is same as original table.
|
2. In case of Materialized view rowid
is different.
|
3.
In case of View we always get latest data.
|
3. Materialized view we need to
refresh the view for getting latest data.
|
4. In case of view we don't need
extra trigger or some automatic method to refresh the view.
|
4. In case of Materialized view we
need extra trigger or some automatic method so that we can keep Materialized view refreshed.
|
Difference Between CASE and DECODE
CASE
|
DECODE
|
1.CASE expression complies with ANSI
SQL
|
1. DECODE is specific to Database
vendors (like Oracle, SQL Server)
|
2. Case can compare discrete as well
as Range value
|
2. DECODE compare discrete values not
Range
|
3. CASE can work with logical
operators, predicates and searchable subqueries.
|
3. DECODE can work with only scaler
values
|
4. CASE is a statement.
|
4. DECODE is a function.
|
5. CASE expects datatype consistency.
|
5. DECODE does not expects datatype consistency.
|
Monday 14 March 2016
Cumulative Sum or LAG function Example
SELECT EMPNO, ENAME, SAL, NVL(SAL,0)+LAG(SAL,1,0) OVER (ORDER BY SAL) SUM_SAL
FROM EMP
ORDER BY SAL
EMPNO | ENAME | SAL | SUM_SAL |
7369 | SMITH | 800 | 800 |
7900 | JAMES | 950 | 1750 |
7876 | ADAMS | 1100 | 2050 |
7521 | MORGAN | 1250 | 2350 |
7654 | MARTIN | 1250 | 2500 |
7934 | MILLER | 1300 | 2550 |
7844 | TURNER | 1500 | 2800 |
7499 | SMALL | 1600 | 3100 |
7782 | FOOTE | 2450 | 4050 |
7698 | BLAKE | 2850 | 5300 |
7566 | NORGAARD | 2975 | 5825 |
7788 | SCOTT | 3000 | 5975 |
7902 | LOFSTROM | 3000 | 6000 |
7839 | ELLISON | 5000 | 8000 |
Saturday 13 June 2015
Popular Personal interview Question
Question: What is your weakness?
Ans: I concentrate on one thing at a time.
Question: Reason behind leaving your last job?
Ans: Looking for better opportunities and enhancing my skill set.
Question: what are your strengths?
Ans: I am a quick learner and a great team player.
Question: why should we hire you?
Ans: If you hire me it will be a great platform to show my skills. Whatever goals I set ensure to complete them within stipulated time.
Question: Do you consider yourself successful?
Ans: Yes. Apart from appraisals I think I have earned a bunch of good colleagues in my life.
Question: Why have you been unemployed for such a long time?
Ans: I enrolled myself for advanced personality development course along with some freelance work.
Question: How long would you expect to work for us if hired?
Ans: As long as I feel challenged professionally.
Question: Describe your management style.
Ans: I will be constantly keeping tab of assigned work with my subordinates and seniors, finishing the assigned job before deadlines.
Question: Are you a team player?
Ans: Yes, the team which I was a part of have successfully completed the projects within the deadlines.
Question: What position do you prefer on a team working on a project?
Ans: It doesn't matter till I learn something new in every project.
Question: What irritates you about co-worker?
Ans: I believe in team work, even if I find anything irritating, I try to avoid it unless it personally affect me.
Question: Tell me about your ability to work under pressure?
Ans: I keep my cool while working under pressure, Multi tasking and patience usually helps in this kind of situations.
Question: How do you see yourself five years from now?
Ans: I see myself in a senior position managing important portfolio of this company.
Question: what are your expectation from this job?
Ans: Professional advancement & good future.
Question: Do you have any question for us?
Ans: When can I join?
Ans: I concentrate on one thing at a time.
Question: Reason behind leaving your last job?
Ans: Looking for better opportunities and enhancing my skill set.
Question: what are your strengths?
Ans: I am a quick learner and a great team player.
Question: why should we hire you?
Ans: If you hire me it will be a great platform to show my skills. Whatever goals I set ensure to complete them within stipulated time.
Question: Do you consider yourself successful?
Ans: Yes. Apart from appraisals I think I have earned a bunch of good colleagues in my life.
Question: Why have you been unemployed for such a long time?
Ans: I enrolled myself for advanced personality development course along with some freelance work.
Question: How long would you expect to work for us if hired?
Ans: As long as I feel challenged professionally.
Question: Describe your management style.
Ans: I will be constantly keeping tab of assigned work with my subordinates and seniors, finishing the assigned job before deadlines.
Question: Are you a team player?
Ans: Yes, the team which I was a part of have successfully completed the projects within the deadlines.
Question: What position do you prefer on a team working on a project?
Ans: It doesn't matter till I learn something new in every project.
Question: What irritates you about co-worker?
Ans: I believe in team work, even if I find anything irritating, I try to avoid it unless it personally affect me.
Question: Tell me about your ability to work under pressure?
Ans: I keep my cool while working under pressure, Multi tasking and patience usually helps in this kind of situations.
Question: How do you see yourself five years from now?
Ans: I see myself in a senior position managing important portfolio of this company.
Question: what are your expectation from this job?
Ans: Professional advancement & good future.
Question: Do you have any question for us?
Ans: When can I join?
Thursday 9 October 2014
MINUS/EXCEPT in Microsoft Access
Microsoft Access is not supporting Minus or Except.
If , we want to find the difference in data between two tables we can use the below syntax in Access.
--MINUS IN ACCESS
SELECT
*
FROM
Table1
WHERE
(((Exists (SELECT * FROM Table2 WHERE Table1.field1 =
Table2.field1 AND Table1.field2 =Table1.field2………………))=False));
Example:
Suppose we have two table name SRC and TGT and we want to find difference of the records which are not in target.
In Oracle we can write the simple query and can find the difference.
--Minus in SQL
SELECT EMPNO,ENAME FROM SRC
MINUS
SELECDT EMPNO,ENAME FROM TGT;
But, Since ACCESS is not supporting Minus/Except. So we can find the difference between SRC and TGT table Using Below SQL.
SELECT EMPNO,ENAME FROM
SRC
WHERE
(((Exists (SELECT EMPNO,ENAME FROM TGT WHERE SRC.EMPNO =
TGT.EMPNO AND SRC.ENAME =TGT.ENAME))=False));
Monday 1 September 2014
Populate SCD3 from SCD2.
SELECT * FROM
(
SELECT A.SK,A.EID,A.ST_DT,A.ED_DT FROM EMP2 A
INNER JOIN
(SELECT MAX(ED_DT) AS N_ED_DT , EID FROM EMP2 GROUP BY EID) B
ON A.ST_DT=B.N_ED_DT
UNION ALL
SELECT A.SK,A.EID,A.ST_DT,A.ED_DT FROM EMP2 A
INNER JOIN
(SELECT MAX(ED_DT) AS N_ED_DT , EID FROM EMP2 GROUP BY EID) B
ON A.ED_DT=B.N_ED_DT
) C
ORDER BY C.EID
SELECT * FROM
(
SELECT A.SK,A.EID,A.ST_DT,A.ED_DT FROM EMP2 A
INNER JOIN
(SELECT MAX(ED_DT) AS N_ED_DT , EID FROM EMP2 GROUP BY EID) B
ON A.ST_DT=B.N_ED_DT
UNION ALL
SELECT A.SK,A.EID,A.ST_DT,A.ED_DT FROM EMP2 A
INNER JOIN
(SELECT MAX(ED_DT) AS N_ED_DT , EID FROM EMP2 GROUP BY EID) B
ON A.ED_DT=B.N_ED_DT
) C
ORDER BY C.EID
Wednesday 25 June 2014
Virtual Column in Where clause
Question: Can we call virtual column in the where clause.
Answer: Yes, we can.
Description:
Suppose we have the Standard EMP table (as per
oracle Scott user) and want to assign the job title on the basis of SAL column
if SAL between 1000 and 3001 then JOB_TITLE is 'JUNIOR' and if SAL between 3001
and 5000 then JOB_TITLE is 'SENIOR' else JOB_TITLE is 'TRAINEE'.
Now, we want to fetch the records that have the JOB_TITLE as
‘TRAINEE’ only. Here JOB_TITLE is the
virtual column (this is not physically in EMP table), so we can’t filter
directly in WHERE clause.
Ok let’s try,
SELECT EMPNO, ENAME, SAL, DEPTNO,
CASE
WHEN SAL >=1000 AND SAL<=3000 THEN 'JUNIOR'
WHEN SAL >=3001 AND SAL<=5000 THEN 'SENIOR'
ELSE 'TRAINEE'
END AS JOB_TITLE
FROM EMP
WHERE JOB_TITLE = 'TRAINEE';
The above query will return the below ERROR.
Error: Invalid column name 'JOB_TTILE'.
To resolve this Error we can write the same query in
different manner.
Method 1
SELECT EMPNO, ENAME, SAL, DEPTNO,
CASE
WHEN SAL >=1000 AND SAL<=3000 THEN 'JUNIOR'
WHEN SAL >=3001 AND SAL<=5000 THEN 'SENIOR'
ELSE 'TRAINEE'
END AS JOB_TITLE
FROM EMP
WHERE
(CASE
WHEN SAL >=1000 AND SAL<=3000 THEN 'JUNIOR'
WHEN SAL >=3001 AND SAL<=5000 THEN 'SENIOR'
ELSE 'TRAINEE'
END AS JOB_TITLE) =
'TRAINEE'
Method 2
SELECT EMPNO, ENAME, SAL, DEPTNO, JOB_TITLE
FROM
(
SELECT EMPNO, ENAME, SAL, DEPTNO,
CASE
WHEN SAL >=5000 AND SAL<=10000 THEN 'JUNIOR'
WHEN SAL >=10001 AND
SAL<=20000 THEN 'SENIOR'
ELSE 'TRAINEE'
END AS JOB_TITLE
FROM EMP
)
WHERE JOB_TITLE='TRAINEE'
In the Method 1 we try to take full virtual
column in WHERE clause and in Method 2 we try to solve this problem with the IN
LINE view.
Subscribe to:
Posts (Atom)