Sql Queries for Interviews part-3

  1. Pivot function on emp table

with pivot_data as(select job,deptno,sal from emp)
SELECT * FROM PIVOT_DATA PIVOT
(sum(sal) for deptno in(10,20,30));

2. Pivot function with multiple columns

WITH relevant_data AS
(
SELECT EXTRACT (YEAR FROM hiredate) AS hireyear
, deptno
, job
FROM scott.emp
WHERE job IN (‘CLERK’, ‘MANAGER’)
)
SELECT *
FROM relevant_data
PIVOT ( COUNT (*)
FOR (job, deptno) IN ( (‘CLERK’, 10) AS clerk_10
, (‘MANAGER’, 10) AS manager_10
, (‘CLERK’, 20) AS clerk_20
, (‘MANAGER’, 20) AS manager_20
, (‘CLERK’, 30) AS clerk_30
, (‘MANAGER’, 30) AS manager_30
)
)
ORDER BY hireyear;

3. Pivot function examples

WITH T AS
( SELECT JOB FROM EMP )
SELECT * FROM T
PIVOT
( COUNT(*) FOR (JOB) IN (‘CLERK’,’SALESMAN’,’MANAGER’,’ANALYST’,’PRESIDENT’)
);
WITH T AS
(SELECT DEPTNO FROM EMP)
SELECT * FROM T
PIVOT
(COUNT(*) FOR (DEPTNO) IN (10,20,30,40));
WITH T AS
(SELECT sal,deptno FROM EMP)
SELECT * FROM T
PIVOT
( Min(sal) as minsal,
max(sal) as maxsal
for (deptno) in (10,20,30));

4. Scalar Sub query in oracle

Scalar Sub query
—————-
A subquery in the select clause of the main query is called a scalar subquery.

This is a single row, single column query, which looks just like a column or function in the select clause.

Scalar subquery characteristics
——————————–
1. A scalar subquery returns exactly one row as output.
2. If the scalar subquery finds no match, it returns NULL
3. If the scalar subquery finds more than one match, it returns an error.

Where to use scalar subqueries
———————————-

1. When your data set is small
2. When you want fast initial response time
3. When you call pl/sql from sql.
4. Remove an Outer Join
5. Aggregate from Multiple Tables
6. Inserting into tables, based on values from other tables.
example.
——–
SELECT d.deptno, d.dname, (SELECT count(*) FROM emp e
WHERE e.deptno = d.deptno) AS “Num Dept”
FROM dept d;

DEPTNO DNAME Num Dept
———- ————– ———-
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
40 OPERATIONS 0

5. Find out Missing number in a column

Missing numbers
—————-

with

present_nums as

(select 1 num from dual union all

select 2 from dual union all

select 4 from dual union all

select 6 from dual union all

select 9 from dual

)

select all_num missing_num

from (select an.num all_num,p.num

from (select level num

from dual

connect by level <= (select max(num)

from present_nums

)

) an,

present_nums p

where an.num = p.num(+)

)

where num is null

order by 1
MISSING_NUM
3
5
7
8

or

SELECT LEVEL num

FROM DUAL

CONNECT BY LEVEL <= 9

MINUS

(SELECT 1 num

FROM DUAL

UNION ALL

SELECT 2

FROM DUAL

UNION ALL

SELECT 4

FROM DUAL

UNION ALL

SELECT 6

FROM DUAL

UNION ALL

SELECT 9

FROM DUAL);

or

SQL> select level + min_id – 1 num

2 from (select min(id) min_id, max(id) max_id from t)

3 connect by level <= (max_id – min_id) + 1

4 minus

5 select id from t;
NUM

——————–

3

5

7

8

or

with

present_nums as

(select 1 num from dual union all

select 2 from dual union all

select 4 from dual union all

select 6 from dual union all

select 9 from dual

)

select listagg(to_char(num),’,’) within group (order by num) missing_list

from (select level num,instr(num_list,’,’||to_char(level)||’,’) is_in

from (select ‘,’||listagg(to_char(num),’,’) within group (order by num)||’,’ num_list,

max(num) last_num

from present_nums

)

connect by level <= last_num

)

where is_in = 0

MISSING_LIST
3,5,7,8

6.  How to write a trigger on a particular table column in oracle

Trigger on a perticular column in a table
——————————————

CREATE OR REPLACE TRIGGER ABC_RATE_UPD
AFTER UPDATE
OF RATE
ON ABC_PCD
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
BEGIN

UPDATE RM_TRAN_IN
SET RATE = :NEW.RATE
WHERE RM_PCD_ID = :NEW.RM_PCD_ID;
EXCEPTION
WHEN OTHERS THEN
— Consider logging the error and then re-raise
RAISE;
END ABC_RATE_UPD;

or
when clause
————
create or replace
TRIGGER ADD_CREATE_DT
after UPDATE of approved ON articles
for each row
when (new.approved = ‘Y’)
BEGIN
:new.create_dt := sysdate;
END;

if clause
———-
create or replace
TRIGGER ADD_CREATE_DT
after UPDATE of approved ON articles
for each row
BEGIN
if :new.approved = ‘Y’ then
:new.create_dt := sysdate;
end if;
END;
Multiple columns
—————–

CREATE OR REPLACE TRIGGER ABC_RATE_UPD
AFTER UPDATE
OF col1,col2 , col3
ON ABC_PCD
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
BEGIN
if updating (‘col1’) then
UPDATE RM_TRAN_IN
SET col1 = :NEW.col1
WHERE :old.id = :NEW.id;
end if;

if updating (‘col2’) then
UPDATE RM_TRAN_IN
SET col2 = :NEW.col2
WHERE :old.id = :NEW.id;
end if;

if updating (‘col3’) then
UPDATE RM_TRAN_IN
SET col3 = :NEW.col3
WHERE :old.id = :NEW.id;
end if;

EXCEPTION
WHEN OTHERS THEN
— Consider logging the error and then re-raise
RAISE;
END ABC_RATE_UPD;

7.  All table names and their size in MB in oracle

select owner, table_name, round((num_rows*avg_row_len)/(1024*1024)) MB
from all_tables
where owner not like ‘SYS%’ — Exclude system tables.
and num_rows > 0 — Ignore empty Tables.
order by MB desc — Biggest first;

8. All table names and No.of Rows in oracle

select owner, table_name, num_rows
from all_tables
where owner not like ‘SYS%’ — Exclude system tables.
and num_rows > 0 — Ignore empty Tables.
order by num_rows desc; — Biggest first;

9. How to List active/open connections in oracle

SELECT username FROM v$session
WHERE username IS NOT NULL
ORDER BY username ASC;

10.  Find out Long running queries in Oracle

SELECT sid, to_char(start_time,’hh24:mi:ss’) stime,
message,( sofar/totalwork)* 100 percent
FROM v$session_longops
WHERE sofar/totalwork < 1;
11. Find out Leader of each employee

select
deptno, empno, ename,
lead(ename) over(partition by deptno order by ename) lead_emp
from scott.emp
order by deptno, ename;

12. Diamond shape query in sql

Declare
v varchar2(250);
num pls_integer := 7; — should be odd
x pls_integer;
begin
for i in 1 .. num
loop
v := ”;
x := case when 2 * i – 1 <= num then 2 * i – 1 else 2 * (num – i) + 1 end;
for j in 1 .. x
loop
v := v || ‘* ‘;
end loop;
dbms_output.put_line(lpad(‘ ‘,num – x,’ ‘) || v);
end loop;
end;

*
* * *
* * * * *
* * * * * * *
* * * * *
* * *
*

13. Find out employees who joined same day and same date in the emp table?

select a.ename ||
‘ was hired on the same month and weekday as ‘||
b.ename as msg
from emp a, emp b
where to_char(a.hiredate,’DMON’) =
to_char(b.hiredate,’DMON’)
and a.empno < b.empno
order by a.ename;

14. Find out Next Hiredate and no.of days difference between them.

select empno,ename,hiredate,hiredate-next_hd
from (select deptno,ename,hiredate,lead(hiredate) over (order by hiredate) next_hd
from emp) order by hiredate;

15.  Find out Sum sal with each dept and Job in oracle

select deptno, job,
case grouping(deptno)||grouping(job)
when ’00’ then ‘TOTAL BY DEPT AND JOB’
when ’10’ then ‘TOTAL BY JOB’
when ’01’ then ‘TOTAL BY DEPT’
when ’11’ then ‘GRAND TOTAL FOR TABLE’
end category,
sum(sal) sal
from emp
group by cube(deptno,job)
order by deptno,grouping(job),grouping(deptno);

 

 

Sql Queries for Interviews Part-2

  1. Compare two tables if any data difference

SELECT * FROM
(
(SELECT * FROM EMP
MINUS
SELECT * FROM EMP1)
UNION ALL
(SELECT * FROM EMP1
MINUS
SELECT * FROM EMP));

2. Sys_connect_by_path using emp table

select empno, ename, sys_connect_by_path(ename,’ -> ‘) tree from emp connect by prior empno=mgr order by mgr nulls first;

3.  Union all with order by clause

select *
from (select id,name
from dum
order by id)
union all
select *
from (select id,name
from dum1
order by id desc);

4. Find out Third maximum salary from emp table

Select rownum,empno,ename,sal from (select * from emp order by sal desc) group by rownum,empno,ename,sal having rownum=’&n’;

or

select level,max(sal) from emp where level in (3,4,5) connect by prior sal>sal group by level order by level;

or

select ename,sal from emp where sal in (select max(sal) from emp where sal <(select max(sal) from emp where sal<(select max(sal) from emp)));

5.How we can eliminate duplicates without using distinct command?

select * from tale_name a where rowid >any(select rowid from table_name b where a.col1=b.col1)

Or

select unique * from table_name;

6. How to insert records with same values into a table

SQL> CREATE TABLE sample(id NUMBER, text VARCHAR2(20));

Table created.

SQL> INSERT INTO sample SELECT rownum , ‘Prabhu’ FROM dual CONNECT BY LEVEL <= 10000;

10000 rows created.

7. Display Parent table , Child table and relationship

SELECT cons.owner AS child_owner, cons.table_name AS child_table,
cons.constraint_name constaint_name,
cons.constraint_type constraint_type, col.owner parent_owner,
col.table_name parent_table, col.column_name column_name
FROM dba_cons_columns col, dba_constraints cons
WHERE cons.r_owner = col.owner
AND cons.r_constraint_name = col.constraint_name
AND col.owner = ‘SCOTT’
ORDER BY child_table;

8. Find out Total no.of rows in a package body

select DISTINCT NAME, MAX(LINE)+1 from user_source A where type=’PACKAGE BODY’ GROUP BY NAME;

or

select DISTINCT NAME, LINE,TEXT from user_source A where type=’PACKAGE BODY’;

9. Display 20*20 matrix data report using SQL

with data as (
select level n from dual connect by level <= 400
)
select * from (
select ceil(row_number() over(order by n)/20) ceiling,
mod(row_number() over(order by n)-1, 20) col,
n
from data
)
pivot(max(n) for col in(
0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19 ));

10. Find each minute of day.

select to_date(’27-OCT-2014 12:00′,’dd-MON-yyyy hh24:mi’)+rownum/(24*60)
from dual
connect by level<=(to_date(’28-OCT-2014 12:00′,’dd-MON-yyyy hh24:mi’)-to_date(’27-OCT-2014 12:00′,’dd-MON-yyyy hh24:mi’))*24*60;

Or

WITH params AS
(
SELECT TO_DATE (’27-Oct-2014 04:00′ ,’DD-Mon-YYYY HH24:MI’) AS start_time
, TO_DATE (’28-Oct-2014 04:04′ ,’DD-Mon-YYYY HH24:MI’) AS end_time
FROM dual
)
, all_minutes AS
(
SELECT start_time + ( (LEVEL – 1)
/ (24 * 60)
) AS m
FROM params
CONNECT BY LEVEL <= 1 + ( (end_time – start_time)
* 24 * 60
)
)
SELECT *
FROM all_minutes
ORDER BY m;

11. Find out table names in a stored procedure

select
referenced_owner,
referenced_name,
referenced_type
from
all_dependencies
where
name= ‘KPR_BULK’
and
owner = ‘SCOTT’
order by
referenced_owner, referenced_name, referenced_type;

12. Find out table column name from stored procedures

SELECT DISTINCT type, name
FROM dba_source
WHERE owner = ‘SCOTT’
AND UPPER(text) LIKE ‘%EMPNO%’;

12. How to stop Sql loader execution

Control + C.

13. With out using Listagg, wm_concat, sys_connect_by_path how to display comma separated values

select
deptno ,
max( decode( val_number, 1 , ename, null ) ) ||
max( decode( val_number, 2 , ‘,’ || ename, null ) ) ||
max( decode( val_number, 3 , ‘,’ || ename, null ) ) ||
max( decode( val_number, 4 , ‘,’ || ename, null ) ) ||
max( decode( val_number, 5 , ‘,’ || ename, null ) ) ||
max( decode( val_number, 6 , ‘,’ || ename, null ) ) as string

from
( select
deptno,
row_number() over ( partition by deptno order by ename ) as val_number ,
ename
from emp
WHERE ename IS NOT NULL
) ta
group by deptno
order by deptno ;

or

select deptno, string
from
(
select
deptno ,
row_number() over ( partition by deptno order by ename ) as position ,
ename ||
lead( ‘,’||ename, 1 ) over ( partition by deptno order by ename ) ||
lead( ‘,’||ename, 2 ) over ( partition by deptno order by ename ) ||
lead( ‘,’||ename, 3 ) over ( partition by deptno order by ename ) ||
lead( ‘,’||ename, 4 ) over ( partition by deptno order by ename ) ||
lead( ‘,’||ename, 5 ) over ( partition by deptno order by ename )
as string
from emp
WHERE ename IS NOT NULL
)
where position = 1
order by deptno ;

14. How to display remaining days of the month from sysdate

SELECT LAST_DAY (SYSDATE) – SYSDATE FROM DUAL;

SYSDATE = 13-07-2014

15. Each employee and his manager names

SELECT e.empno
, e.ename
, e.mgr
, m.ename AS mname
FROM scott.emp e
LEFT OUTER JOIN scott.emp m ON e.mgr = m.empno
ORDER BY e.ename;

16.  Find out 10 largest objects in the database

SELECT * FROM
(
select
SEGMENT_NAME,
SEGMENT_TYPE,
BYTES/1024/1024/1024 GB,
TABLESPACE_NAME
from
dba_segments
order by 3 desc
) WHERE
ROWNUM <= 10

Sql Queries for Interviews Part-1

1 . Each dept wise maximum salary with emp details.

select * from (
select * from emp where sal in (select max(sal) from emp group by deptno) order by deptno);

2. Each dept wise minimum salary with emp details.

select * from (
select * from emp where sal in (select min(sal) from emp group by deptno) order by deptno);

3. Nth maximum salary from emp table.

select level,max(sal) from emp where level in (5,6,7) connect by prior sal>sal group by level order by level;

4.  Nth rows from emp table

select * from (
select rownum r, e.* from emp e
) mytable
where r in (4,11,14);

5. Bottom 5 employees based on their salary

SELECT ename, sal
FROM ( SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal) sal_dense_rank
FROM emp )
WHERE sal_dense_rank <= 5;

6. Weekstart date and Weekend date based on sysdate

SELECT TRUNC(SYSDATE, ‘DAY’) week_start,
TRUNC(SYSDATE, ‘DAY’) + 6 week_end
FROM dual;

7. Dept wise Max(sal)  and Min(sal)

select * from (
select deptno,sal,ename
from emp where sal in (select min(sal) from emp
group by deptno) order by deptno)
union
select * from(
select deptno,sal,ename
from emp where sal in (select max(sal) from emp
group by deptno)order by deptno);

8. First day and Last day of each month

select
trunc(add_months(sysdate,level-1),’MM’) first_day,
last_day(add_months(sysdate,level-1)) last_day
from dual
connect by level<=12;

9. Mininum number of employees each dept wise

select deptno,count(*) from emp
group by deptno
having count(*)=(select min(cnt)
from (
select count(*) cnt from emp group by deptno)
);

10. Maximum number of employees each dept wise

select deptno,count(*) from emp
group by deptno
having count(*)=(select max(cnt)
from (
select count(*) cnt from emp group by deptno)
);

11. Employees who are joined in the same date.

Select E.* from emp e where hiredate in (select hiredate from emp group by hiredate having count (hiredate)>1);

12.  Display calendar months

SELECT to_char(to_date( level,’mm’), ‘MONTH’) Months FROM DUAL CONNECT BY LEVEL <=12;

13. Display previous years from current years

select extract(year from sysdate) – (level-1) as years
from dual
connect by level <=5
order by years;

14. Same weekday and date of last year

select
to_char(sysdate, ‘fmDy DD Month YYYY’) this_year
, to_char(next_day(trunc(add_months(sysdate, -12), ‘iw’), to_char(sysdate, ‘DY’)), ‘fmDy DD Month YYYY’) last_year
from dual;

15. Name in triangle

select substr(s,1,level) from (select ‘prabhakar’ s from dual) connect by level<=length(s);

16. Only max(sal) and Min(sal) records

select * from emp
where sal in (select max(sal) from emp)
union
select * from emp
where sal in (select min(sal) from emp);

17. Except max(sal) and Min(sal) from emp table

select * from (
select e.* from emp e
where sal NOT IN (select max(sal) from Emp))
intersect
select * from (
select e.* from emp e
where sal NOT IN (select min(sal) from Emp));

18.  Update gender column in a table

Update sex T Set Gender=(
Select Gender from sex B where T.Gender!=B.Gender and rownum=1);

19. Second avg(sal) from emp table

select * from (select ename,sal from emp where sal  > (select avg(sal) from emp )  order by sal)  where rownum<=1;

20. Cricket Team select query

WITH teams AS (SELECT ‘IND’ team_name FROM DUAL UNION ALL
SELECT ‘AUS’ team_name FROM DUAL UNION ALL
SELECT ‘END’ team_name FROM DUAL UNION ALL
SELECT ‘SA’ team_name FROM DUAL UNION ALL
SELECT ‘WI’ team_name FROM DUAL UNION ALL
SELECT ‘NZ’ team_name FROM DUAL
)
SELECT t1.team_name
,t2.team_name
FROM teams t1
,teams t2
WHERE t1.team_name <= t2.team_name;

21. Without using union display distinct values from two tables

select distinct (no) from a full outer join b using(no) order by no;

22. Without using minus display common values from two tables

select distinct (no) from a natural join b;

23. Display last two records from emp table

select * from (select e.*,rownum r from emp e)
where r >(select count(*)-2 from emp);

24. Display two min.sal  records from emp

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

25. Display Last 1 hour transactions

select count(*) from my_table where last_updated_date >= (sysdate-1/24);

25. Display last 10 mins. transations

select *
from mytable
where lastupdatedDate > sysdate – (10/1440);

 

 

 

 

 

Continue reading Sql Queries for Interviews Part-1