-
Maxvalue.sql Select the Nth Highest value from a table
select level, max('col_name') from my_table where level = '&n' connect by prior ('col_name') >'col_name')group by level;Example:Given a table called emp with the following columns:-- id number-- name varchar2(20)-- sal number---- For the second highest salary:-- select level, max(sal) from emp-- where level=2-- connect by prior sal > sal-- group by level
nklpunitha
- Feb 20th, 2010
Try the below query, it will give the correct output Select * from emp where salary = (select min(salary) from (select distinct salary from emp order by salary desc) where rownum < n)
Avashisth
- Feb 20th, 2010
-- To get nth Highestselect distinct salary from (select salary,dense_rank() over(order by salary desc) as sal from Table )where sal = &n--To get nth Lowestselect distinct salaryfrom (select salary,dense_rank() over(order by salary) as sal from Table ) where sal = &n
-
What are cursor attributes?
%ROWCOUNT%NOTFOUND%FOUND%ISOPEN
narmadac
- Feb 11th, 2010
Cursor attributes when appended to the cursor name allow the users to access useful information from the retrieved rows. After opening a cursor a 'fetch' statement is used to fetch rows from the...
syam sundar
- Aug 17th, 2007
%ROWTYPE - returns number of the rows which are fetched/updated/deleted%ISOPEN - returns true if the cursor is open%FOUND - returns true if the cursor fetched any row%NOTFOUND-returns true if the cursor does not fetch any row
-
What is a cursor for loop?
Cursor For Loop is a loop where oracle implicitly declares a loop variable, the loop index that of the same record type as the cursor's record.
narmadac
- Feb 11th, 2010
A cursor for loop can be used instead of explicit cursors. A cursor for loop implicitly opens, fetches rows, closes a cursor.
g_sidhu
- Feb 6th, 2008
The cursor FOR loop is a shortcut to process explicit cursors. Implicit open, fetch, exit, and close occur. The record is implicitly declared.
-
How will you delete duplicating rows from a base table?
Delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name); ordelete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from table_name tb where ta.dv=tb.dv);
thirupathireddy
- Feb 2nd, 2010
DELETE FROM table_name a WHERE rowid > (SELECT min(rowid) FROM table_name b WHERE a.column_name=b.column_name)
syam sundar
- Aug 18th, 2007
Delete table_name t1where rowid>(select rowid from table_name t2)and t1.column1=t2.column1
-
Is there any chance to remove a column of a table
peprah
- Jan 20th, 2010
Sure why not?First of all you need to know and be sure of the table name and column you want to delete. There you are: DELETE FROM [TABLE_NAME]WHERE [ROW_NAME] = ['COLUMN_NAME']; EXA...
-
What is rep cursor?
hindol_saha
- Jan 19th, 2010
Ref cursor is used for dynamic cursor based on different where clause ,and it is used for fast processing The scope of cursor is limited to a block/ programme. here block/ programme means upto a e...
fiaskhan
- Dec 4th, 2008
Ref cursor?are used to define cursor variables. It acts as a pointer for memory area.In a single memory?location (context area) we can write any number of select statement. Mainly it is used when you want to fetch data from more than one table.
-
Average Monthly Salary
Write a query to display the average, monthly salary bill for each job type within a department.
-
Department Wise Average Salary
How to query the average salary for all the departments employing more than 3 people?
abhinav.abinav
- Jan 13th, 2010
Select deptno,avg(sal),count(*) from emp group by deptno having count(*)>3;
-
I have a table with a column city. The values are “delhi” and “hyderabad”. I need to update Hyderabad with delhi and delhi with Hyderabad. Define a query for that
emraan
- Dec 31st, 2009
Update e set a = case a when 'city' then 'hyd' when 'hyd' then 'city' endperfect and testedImran Baig+91 - 9247519684
sharmasl
- Mar 5th, 2009
Update States set city= decode(city,`Delhi`,`Hyderabad`,`Hyderabad`,`Delhi`,city);By using this query we can?inter change the?citys.Thanks,Salil Sharma
-
Difference between decode and case.in which case we are using case and in which case we are using decode?with an example.
Sirajuddin84
- Dec 21st, 2009
The main problem with DECODE is that we can compare only discrete values and not ranged values. So they introduced the CASE statements from oracle 8i onwards. This CASE was able to handle ranged value...
joyfun23
- Nov 17th, 2009
select 'RAGHAV' from dual where dummy =(select CASE when 'A' ='A'then 'X' else 'Y'end from dual) CASE Works in where clause and Decode too!!
-
Co-Related Sub Query
What are the disadvantages of co-related sub query?
sure_prince
- Dec 10th, 2009
-> It is another way of performing queries upon the data with a simulation of Joins.-> In This the information from the outer Select Statement Pratcipates As a Condition in the Inner Select Statement.-> Depending on the Supplied value the condition is qualified for the specific Record.
-
Display time with sysdate
How to display time with sysdate?
-
Ref Cursors
How many ref cursors can be dynamically opened in stored procedures for a session?
kondapallikalyan
- Nov 24th, 2009
We have two types of ref cursors1. Week typed2. Strong typedEx: Type <Ref cursor Name> is ref cursor /* this is week typed*/Ex:Type <Ref cursor Name> is ref cursor return /* this is strong type
sunraajkumar
- Jun 10th, 2009
more then one query open same cursor name at same time (use unlimited query)
-
Variable Length Record
Describe variable length record representation in files using byte string representation
-
SQL Soundx
What is soundx in SQL?
sure_prince
- Aug 14th, 2009
This is Soundex Function is useful to select the data according to the sound of
the given value but not compare with the casing of that letters.For Example:
SQL> SELECT * FROM emp WHERE Soundex(Ename)=Soundex('Analyst');
It select all the Analyst details in emp table in anuy case...sunraajkumar
- Jul 1st, 2009
Soundex function is used to find the phonetic representation of stringSyntax----------Soundex(CHAR)Query--------SELECT Empname FROM Employee WHERE SOUNDEX(EMPNAME)=SOUNDEX('SMYTHE')
-
On index why u need indexing ? where that is stroed and what u mean by schema object?for what purpose we are using view
jaspreet.nagra
- Jul 14th, 2009
Sometime the special index you get automatically with every primary key and unique constraint might not help in certain situations. If this happens then maybe it's time to explicitly create a seco...
SateeshReddy
- Oct 9th, 2006
Hello frnd,why cant we create an index on the index....i think according to the database concepts we can create it. it may called as multilevel indexes...like B trees and B+trees are examples for that...
-
What is the result ofselect * from table1, table2there is a common column between them but no where caluse has been specified
jaspreet.nagra
- Jul 14th, 2009
Assume you have following two tables:SQL> select * from aa;DAT NAME---- --------1 Amit2 JasSQL&g...
dmadhusudhanarao
- Aug 2nd, 2006
assume that 2 table are emp,deptnow write query like thisselect * from emp,deptthe out put is emp has 14 records and dept has 4 recordsthen output is 14x4=56 records you will simply.
-
SELECT Statement in CASE
How to use select statement in Case?
sunraajkumar
- Jun 3rd, 2009
Example sample query: if then else conditionSELECT empno, ename,
job, sal,
CASE job WHEN 'manager' THEN sal *0.10
WHEN 'analyst' THEN sal *0.20
WHEN 'president' THEN sal *0.30
ELSE sal END "Revised Salary" from emp; -
How you will avoid your query from using indexes?
SELECT * FROM empWhere emp_no+' '=12345;i.e you have to concatenate the column name with space within codes in the where condition.SELECT /*+ FULL(a) */ ename, emp_no from empwhere emp_no=1234;i.e using HINTS
jyothi_rayala
- Jun 11th, 2009
One way is to diable the index on the column using syntaxAlter index indexname on table_name disable.One way is to apply /*+no_index */ hint to the query For eg: select ename /*+no_index */from empwhere ename ='smith'
Srini
- Oct 10th, 2007
Its so distressing that no body could provide a better answer than a novice like me.In the where clause change the order of conditions in such a way that:? 5 = value cond1 & value =5or value + 1 = 5
-
How to compare the total salary of all employee with a individual employee salary for a particular department.
sumanta.bala
- Jun 5th, 2009
SELECT ename,deptno,sal,TRUNC (AVG (sal) OVER (PARTITION BY deptno)) avg_sal FROM scott.emp
Oracle SQL Interview Questions
Ans