-
How to get 1,4,7,10.....rows from a table
Arjunya
- Apr 29th, 2010
SELECT XFROM YWHERE MOD(X,3) = 1;Where X is the column name and Y is the table name.
ganne
- Aug 2nd, 2006
Select rownum,empno from emp group by rownum,empno having mod(rownum,3)=1;
-
What are various privileges that a user can grant to another user?
SELECTCONNECTRESOURCES
ankitagujrati28
- Apr 30th, 2010
A user can give all database privilages to another user, which are also has that user.
-
What is difference between SUBSTR and INSTR?
SUBSTR returns a specified portion of a string eg SUBSTR('BCDEF',4) output BCDEINSTR provides character position in which a pattern is found in a string. eg INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-')
Nicsmart
- Mar 31st, 2010
SUBSTR------The substr functions allows you to extract a substring from a string.The syntax for the substr function is:substr( string, start_position, [ length ] )string is the source string. st...
ashish88
- Jun 10th, 2008
hi Buddy, Substr only give the sub part of the stringSubstr(String,'start postion','length')Substr('ABCDEFAG',3,7); /*out put is =cdefag */Instr to give only possio...
-
What a SELECT FOR UPDATE cursor represent.[ANSWER]SELECT......FROM......FOR......UPDATE[OF column-reference][NOWAIT] The processing done in a fetch loop modifies the rows that have been retrieved by the cursor. A convenient way of modifying the rows is done by a method with two parts: the FOR UPDATE clause in the cursor declaration, WHERE CURRENT OF CLAUSE in an UPDATE or declaration statement.
Nicsmart
- Mar 31st, 2010
The SELECT....FOR UPDATE [OF column_reference ][NOWAIT] statement identifies the rows that will be updated or deleted, then locks each row in the result set. This is useful when we want to base an upd...
g_sidhu
- Feb 6th, 2008
The SELECT ... FOR UPDATE statement identifies the rows that will be updated or deleted, then locks each row in the result set. This is useful when you want to base an update on the existing values in...
-
Where we use nested tables in our real time
Nicsmart
- Mar 31st, 2010
• Use when working with an unbounded list that needs to increase dynamically • Use when you need to store in the database and operate on elements individually
paul
- Sep 2nd, 2006
Hi,Nested Tables are collector objects (only attributes, i.e. no behaviour defined), that can be re-used in the same way as a class in java or c++ (without functions / procedures). In RDBMS...
-
Explain what is mutation and what is mutating table and how this mutation problem is solved in a table
Nicsmart
- Mar 31st, 2010
A mutating table is a table that is currently being modified by an INSERT,UPDATE,DELETE statement or a table that might need to be updated by the effects of a declarative DELETE CASCADE referential in...
Nasim
- Aug 24th, 2007
One solution also exist:create a row level trigger and insert/update the data and take it in collections( varray or nested table or associative array). Then write a after statement level trigger and fetch that data.
-
Null Value
How to handle Null value in WHERE Condition?
taaznyonker
- Mar 31st, 2010
Use "IS NULL" to check whether value is null or "IS NOT NULL" to check the value is not NULL
nikhilanshuman
- Mar 16th, 2010
You should use "IS NULL" or "IS NOT NULL" in the where clause.For example :select DEPT_NAME from DEPT where DEPT_CD IS NOT NULL;The above query returns all those department names from the table "DEPT" where the column "DEPT_CD" is not null.
-
Difference Between Hash Join & Merge Join
Merge Join :Oracle performs a join between two sets of row data using the mergejoin algorithm. The inputs are two separate sets of row data. Output isthe results of the join. Oracle reads rows from both inputs in analternating fashion and merges together matching rows in order togenerate output. The two inputs are sorted on join column.Hash Join :Oracle performs a join between two sets...
mvg_mca
- Mar 24th, 2010
A merge join basically sorts all relevant rows in the first table by the join key, and also sorts the relevant rows in the second table by the join key, and then merges these sorted rows.A hash join (...
Manoj Seemar
- Nov 2nd, 2007
Merge Joins Sort merge joins can be used to join rows from two independent sources. Hash joins generally perform better than sort merge joins. On the other hand, sort merge joins can perform better th...
-
My problem is i have a two tablestable1customercustid custnametable2custaddrcustaddrid custaddrstr custid datei have different records for each custid in custaddr table. say for example for the first customer we have 6 records in custaddr table. so i want the latest dated record from the custaddr table. ie to find the maximum date. pls somebody can write the query for this, i have tried but not able to find the max of the dates.thanks in advance
rk
- Apr 24th, 2006
select c.*, ca.custaddrstr,ca.custdate from customer c, custaddr cawhere c.CUSTID = ca.CUSTIDand ca.CUSTDATE in (select max(custdate) from custaddrgroup by custid)
-
Maximum Limit for IN Operator
What is the maximum limit for IN operator on SQL?
-
NOCOPY mode
What is the purpose of NOCOPY mode in procedures?
nikhilanshuman
- Mar 16th, 2010
When a parameter is passed as an IN variable, it is passed by reference. Since it will not change, PL/SQL uses the passed variable in the procedure/function. When variables are passed in OUT or INOUT ...
-
SQL System Time
How to get System time in SQL?
kondapallikalyan
- Nov 24th, 2009
We have Functions1. current_timestamp2. systimestampEx: Select current_timestamp from dual;Ex:Select systimestamp from dual;
-
Write a query that display the records based on condition. excluding the record that meets the condition Eg... Select all employees who has same salary as empno 20. My query should return all the rows excluding empno20.
kirandstyle
- May 5th, 2008
Hi dude,if suppose there is a Jobhistory table which contains the following columns in itsay...empno,ename,salary,startdate,enddate,positionhere startdate is the date where an employee starts his new ...
-
Create a query that displays the employees' last names and commision amounts.If an employee does not earn commmission, put "No Commission." Label the column COMM.
pollysingh544
- Feb 22nd, 2010
Select last name,decode(commision,0,'No Commision',commision) comm from employee
-
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.
Oracle SQL Interview Questions
Ans