Monday, May 4, 2020
Monday, February 17, 2020
Complex Queries in SQL ( Oracle )
Complex Queries in SQL ( Oracle )
These questions are the most frequently asked in interviews.
To fetch ALTERNATE records from a table. (EVEN NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);
To select ALTERNATE records from a table. (ODD NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);
Find the 3rd MAX salary in the emp table.
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
Find the 3rd MIN salary in the emp table.
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);
Select FIRST n records from a table.
select * from emp where rownum <= &n;
Select LAST n records from a table
select * from emp minus select * from emp where rownum <= (select count(*) - &n from emp);
List dept no., Dept name for all the departments in which there are no employees in the department.
select * from dept where deptno not in (select deptno from emp);
alternate solution: select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);
altertnate solution: select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null;
How to get 3 Max salaries ?
select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;
How to get 3 Min salaries ?
select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal >= b.sal);
How to get nth max salaries ?
select distinct hiredate from emp a where &n = (select count(distinct sal) from emp b where a.sal >= b.sal);
Select DISTINCT RECORDS from emp table.
select * from emp a where rowid = (select max(rowid) from emp b where a.empno=b.empno);
How to delete duplicate rows in a table?
delete from emp a where rowid != (select max(rowid) from emp b where a.empno=b.empno);
Count of number of employees in department wise.
select count(EMPNO), b.deptno, dname from emp a, dept b where a.deptno(+)=b.deptno group by b.deptno,dname;
Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?
select ename,sal/12 as monthlysal from emp;
Select all record from emp table where deptno =10 or 40.
select * from emp where deptno=30 or deptno=10;
Select all record from emp table where deptno=30 and sal>1500.
select * from emp where deptno=30 and sal>1500;
Select all record from emp where job not in SALESMAN or CLERK.
select * from emp where job not in ('SALESMAN','CLERK');
Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'.
select * from emp where ename in('JONES','BLAKE','SCOTT','KING','FORD');
Select all records where ename starts with âSâ and its lenth is 6 char.
select * from emp where ename like'S____';
Select all records where ename may be any no of character but it should end with âRâ.
select * from emp where ename like'%R';
Count MGR and their salary in emp table.
select count(MGR),count(sal) from emp;
In emp table add comm+sal as total sal .
select ename,(sal+nvl(comm,0)) as totalsal from emp;
Select any salary <3000 from emp table.
select * from emp where sal> any(select sal from emp where sal<3000);
Select all salary <3000 from emp table.
select * from emp where sal> all(select sal from emp where sal<3000);
Select all the employee group by deptno and sal in descending order.
select ename,deptno,sal from emp order by deptno,sal desc;
How can I create an empty table emp1 with same structure as emp?
Create table emp1 as select * from emp where 1=2;
How to retrive record where sal between 1000 to 2000?
Select * from emp where sal>=1000 And sal<2000
Select all records where dept no of both emp and dept table matches.
select * from emp where exists(select * from dept where emp.deptno=dept.deptno)
If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?
(Select * from emp) Union (Select * from emp1)
How to fetch only common records from two tables emp and emp1?
(Select * from emp) Intersect (Select * from emp1)
How can I retrive all records of emp1 those should not present in emp2?
(Select * from emp) Minus (Select * from emp1)
Count the totalsa deptno wise where more than 2 employees exist.
SELECT deptno, sum(sal) As totalsal
FROM emp
GROUP BY deptno
HAVING COUNT(empno) > 2
These questions are the most frequently asked in interviews.
To fetch ALTERNATE records from a table. (EVEN NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);
To select ALTERNATE records from a table. (ODD NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);
Find the 3rd MAX salary in the emp table.
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
Find the 3rd MIN salary in the emp table.
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);
Select FIRST n records from a table.
select * from emp where rownum <= &n;
Select LAST n records from a table
select * from emp minus select * from emp where rownum <= (select count(*) - &n from emp);
List dept no., Dept name for all the departments in which there are no employees in the department.
select * from dept where deptno not in (select deptno from emp);
alternate solution: select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);
altertnate solution: select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null;
How to get 3 Max salaries ?
select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;
How to get 3 Min salaries ?
select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal >= b.sal);
How to get nth max salaries ?
select distinct hiredate from emp a where &n = (select count(distinct sal) from emp b where a.sal >= b.sal);
Select DISTINCT RECORDS from emp table.
select * from emp a where rowid = (select max(rowid) from emp b where a.empno=b.empno);
How to delete duplicate rows in a table?
delete from emp a where rowid != (select max(rowid) from emp b where a.empno=b.empno);
Count of number of employees in department wise.
select count(EMPNO), b.deptno, dname from emp a, dept b where a.deptno(+)=b.deptno group by b.deptno,dname;
Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?
select ename,sal/12 as monthlysal from emp;
Select all record from emp table where deptno =10 or 40.
select * from emp where deptno=30 or deptno=10;
Select all record from emp table where deptno=30 and sal>1500.
select * from emp where deptno=30 and sal>1500;
Select all record from emp where job not in SALESMAN or CLERK.
select * from emp where job not in ('SALESMAN','CLERK');
Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'.
select * from emp where ename in('JONES','BLAKE','SCOTT','KING','FORD');
Select all records where ename starts with âSâ and its lenth is 6 char.
select * from emp where ename like'S____';
Select all records where ename may be any no of character but it should end with âRâ.
select * from emp where ename like'%R';
Count MGR and their salary in emp table.
select count(MGR),count(sal) from emp;
In emp table add comm+sal as total sal .
select ename,(sal+nvl(comm,0)) as totalsal from emp;
Select any salary <3000 from emp table.
select * from emp where sal> any(select sal from emp where sal<3000);
Select all salary <3000 from emp table.
select * from emp where sal> all(select sal from emp where sal<3000);
Select all the employee group by deptno and sal in descending order.
select ename,deptno,sal from emp order by deptno,sal desc;
How can I create an empty table emp1 with same structure as emp?
Create table emp1 as select * from emp where 1=2;
How to retrive record where sal between 1000 to 2000?
Select * from emp where sal>=1000 And sal<2000
Select all records where dept no of both emp and dept table matches.
select * from emp where exists(select * from dept where emp.deptno=dept.deptno)
If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?
(Select * from emp) Union (Select * from emp1)
How to fetch only common records from two tables emp and emp1?
(Select * from emp) Intersect (Select * from emp1)
How can I retrive all records of emp1 those should not present in emp2?
(Select * from emp) Minus (Select * from emp1)
Count the totalsa deptno wise where more than 2 employees exist.
SELECT deptno, sum(sal) As totalsal
FROM emp
GROUP BY deptno
HAVING COUNT(empno) > 2
Wednesday, October 30, 2019
how would like to calculate table size and particular clob column size inside that table.
The size of the table and the size of the LOB are two totally different things.
Barring cases where the LOB is less than 4k and stored inline, the LOB data is stored outside the table in a separate segment.
select dbms_lob.getlength(JSON_DATA)/ 1024 / 1024 MB from ABC
MB
---------
730.1875
If you want to get the combined size of the table and of its LOB segments, you could do something like
SELECT SUM(bytes)/1024/1024 MB
FROM dba_segments
WHERE (owner = 'HONNIKERY' and
segment_name = 'HONNIKERY_CLON')
OR (owner, segment_name) IN (
SELECT owner, segment_name
FROM dba_lobs
WHERE owner = 'HONNIKERY'
AND table_name = 'HONNIKERY_CLON' )
MB
---------
730.1875
Barring cases where the LOB is less than 4k and stored inline, the LOB data is stored outside the table in a separate segment.
select dbms_lob.getlength(JSON_DATA)/ 1024 / 1024 MB from ABC
MB
---------
730.1875
If you want to get the combined size of the table and of its LOB segments, you could do something like
SELECT SUM(bytes)/1024/1024 MB
FROM dba_segments
WHERE (owner = 'HONNIKERY' and
segment_name = 'HONNIKERY_CLON')
OR (owner, segment_name) IN (
SELECT owner, segment_name
FROM dba_lobs
WHERE owner = 'HONNIKERY'
AND table_name = 'HONNIKERY_CLON' )
MB
---------
730.1875
Monday, September 9, 2019
Friday, April 26, 2019
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
Partitioned table
SQL> desc honnikery
Name Null? Type
----------------------------- -------- ------------
X NUMBER(38)
Y NUMBER(38)
and it has a couple of honnikerytitions
SQL> select honnikerytition_name
2 from dba_tab_honnikerytitions
3 where table_name = 'honnikery';
honnikeryTITION_NAME
------------------------------
P1
P2
So now I want to do the standard operation of creating a ‘template’ table which I can then use to perform an Prabhakarange honnikerytition operation.
SQL> create table Prabhakar as
2 select * from honnikery
3 where 1=0;
Table created.
SQL> desc Prabhakar
Name Null? Type
----------------------------- -------- --------------
X NUMBER(38)
Y NUMBER(38)
So now I’m ready to go…But then this happens…
SQL> alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar;
alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE PrabhakarANGE honnikeryTITION
Well…that’s odd. I created the Prabhakar table as simple create-table-as-select. Let’s try it again using the “WITHOUT VALIDATION” clause.
SQL> alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar without validation;
alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar without validation
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE PrabhakarANGE honnikeryTITION
Nope…still problems. So I go back and double check the columns
SQL> select column_name
2 from dba_tab_columns
3 where table_name = 'honnikery';
COLUMN_NAME
------------------------------
X
Y
SQL> select column_name
2 from dba_tab_columns
3 where table_name = 'Prabhakar';
COLUMN_NAME
------------------------------
X
Y
So what could be the problem ? Its a “sleeper problem”. Some time ago, I did something to the columns in my honnikerytitioned table that is no longer readily aphonnikeryent.
I dropped a column. Or more accurately, because it was a honnikerytitioned table (and presumably a large table), I set a column to unused. What Oracle has done behind the scenes is retain that column but make it invisible for “day to day” usage. We can see that by querying DBA_TAB_COLS
SQL> select column_name
2 from dba_tab_cols
3 where table_name = 'honnikery';
COLUMN_NAME
------------------------------
SYS_C00003_12121820:22:09$
Y
X
And there’s the culprit.
So am I stuck forever ? Do I have to drop the column ? Or reload the honnikery table without the unused column ? All of those things don’t sound too palatable.
No. All I need do is get the columns in my template table into a similar state.
SQL> alter table Prabhakar add Z int;
Table altered.
SQL> alter table Prabhakar set unused column Z;
Table altered.
And we can try again…
SQL> alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar;
Table altered.
More :https://connor-mcdonald.com/2013/01/20/exchange-partition-those-pesky-columns/
SQL> desc honnikery
Name Null? Type
----------------------------- -------- ------------
X NUMBER(38)
Y NUMBER(38)
and it has a couple of honnikerytitions
SQL> select honnikerytition_name
2 from dba_tab_honnikerytitions
3 where table_name = 'honnikery';
honnikeryTITION_NAME
------------------------------
P1
P2
So now I want to do the standard operation of creating a ‘template’ table which I can then use to perform an Prabhakarange honnikerytition operation.
SQL> create table Prabhakar as
2 select * from honnikery
3 where 1=0;
Table created.
SQL> desc Prabhakar
Name Null? Type
----------------------------- -------- --------------
X NUMBER(38)
Y NUMBER(38)
So now I’m ready to go…But then this happens…
SQL> alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar;
alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE PrabhakarANGE honnikeryTITION
Well…that’s odd. I created the Prabhakar table as simple create-table-as-select. Let’s try it again using the “WITHOUT VALIDATION” clause.
SQL> alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar without validation;
alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar without validation
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE PrabhakarANGE honnikeryTITION
Nope…still problems. So I go back and double check the columns
SQL> select column_name
2 from dba_tab_columns
3 where table_name = 'honnikery';
COLUMN_NAME
------------------------------
X
Y
SQL> select column_name
2 from dba_tab_columns
3 where table_name = 'Prabhakar';
COLUMN_NAME
------------------------------
X
Y
So what could be the problem ? Its a “sleeper problem”. Some time ago, I did something to the columns in my honnikerytitioned table that is no longer readily aphonnikeryent.
I dropped a column. Or more accurately, because it was a honnikerytitioned table (and presumably a large table), I set a column to unused. What Oracle has done behind the scenes is retain that column but make it invisible for “day to day” usage. We can see that by querying DBA_TAB_COLS
SQL> select column_name
2 from dba_tab_cols
3 where table_name = 'honnikery';
COLUMN_NAME
------------------------------
SYS_C00003_12121820:22:09$
Y
X
And there’s the culprit.
So am I stuck forever ? Do I have to drop the column ? Or reload the honnikery table without the unused column ? All of those things don’t sound too palatable.
No. All I need do is get the columns in my template table into a similar state.
SQL> alter table Prabhakar add Z int;
Table altered.
SQL> alter table Prabhakar set unused column Z;
Table altered.
And we can try again…
SQL> alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar;
Table altered.
More :https://connor-mcdonald.com/2013/01/20/exchange-partition-those-pesky-columns/
Friday, January 11, 2019
ORA-01502: index ‘string.string’ or partition of such index is in unusable state
ORA-01502: index ‘string.string’ or partition of such index is in unusable state
The error indicates an attempt has been made to access an index or index partition
that has been marked unusable by a direct load or by a DDL operation.
The problem usually happens when using the Direct Path for the SQL*Loader, Direct Load or DDL operations.
This requires enough temporary space to build all indexes of the table. If there is no enough space in TEMP tablespace,
all rows will still be loaded and imported, but the indices are left with STATUS = ‘INVALID’.
SELECT 'alter index '||owner||'.'||index_name ||' rebuild online nologging;'
FROM all_indexes
WHERE owner = 'HONNIKERY' AND status = 'VALID'
AND (status != 'N/A'
OR index_name IN
(SELECT index_name
FROM all_ind_partitions
WHERE status != 'USABLE'
AND (status != 'N/A'
OR index_name IN
(SELECT index_name
FROM all_ind_subpartitions
WHERE status != 'USABLE'))));
The error indicates an attempt has been made to access an index or index partition
that has been marked unusable by a direct load or by a DDL operation.
The problem usually happens when using the Direct Path for the SQL*Loader, Direct Load or DDL operations.
This requires enough temporary space to build all indexes of the table. If there is no enough space in TEMP tablespace,
all rows will still be loaded and imported, but the indices are left with STATUS = ‘INVALID’.
SELECT 'alter index '||owner||'.'||index_name ||' rebuild online nologging;'
FROM all_indexes
WHERE owner = 'HONNIKERY' AND status = 'VALID'
AND (status != 'N/A'
OR index_name IN
(SELECT index_name
FROM all_ind_partitions
WHERE status != 'USABLE'
AND (status != 'N/A'
OR index_name IN
(SELECT index_name
FROM all_ind_subpartitions
WHERE status != 'USABLE'))));
Thursday, January 10, 2019
Index Status Types in DBA_INDEXES
SELECT DISTINCT STATUS FROM DBA_INDEXES;
STATUS
-----------
N/A
UNUSABLE
VALID
ORA-01502: index ‘string.string’ or partition of such index is in unusable state
SELECT owner, index_name, tablespace_name
FROM dba_indexes
WHERE status = 'UNUSABLE';
Index partitions:
SELECT index_owner, index_name, partition_name, tablespace_name
FROM dba_ind_PARTITIONS
WHERE status = 'UNUSABLE';
The following SQL will print out a list of alter commands that can be executed to fix unusable indexes:
Indexes:
SELECT 'alter index '||index_name||' rebuild tablespace |tablespace_name ||';'
FROM dba_indexes
WHERE status = 'UNUSABLE';
Index partitions:
SELECT 'alter index '||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||';'
FROM dba_ind_partitions
WHERE status = 'UNUSABLE';
Subscribe to:
Posts (Atom)