-
Sub query and correlated sub query
What is the difference between sub query and correlated sub query
krishnaindia2007
- Jun 20th, 2008
In sub query the inner query is executed only once. Depeding upon the results of inner query , outer query is evaluated.In correlated subquery the inner query is evaluated once for each row processed by the parent statement or outer query.
tariq_oracle
- Apr 13th, 2008
subquery: required in those cases where requisted data are not known and always depend on inner qu...
-
Table and Column Level Locks
How to handle the table level locks and column level locks?
krishnaindia2007
- Jun 20th, 2008
When you are performing INSERT , DELETE , UPDATE operations on a table it implicity aquires row level locks. If a transaction obtains a row lock for a row, the transaction also aquires a table l...
-
Copying table with constraints and data
How to copy a table with constraints and data to another table.
krishnaindia2007
- Jun 21st, 2008
Using the following query you may copy table structure and data but you can't copy constraints.Create table emp_copy as select * from emp;
ugly1
- Jan 25th, 2008
below shown query will copy table with constraints and data into new table. here table_copy is the new table and old_table is the table whose data and constraints are to be copied.create table table_copy as (select * from old_table)
-
Constraint and Index
What is difference between constraint and index? How do use them in SQL query?
krishnaindia2007
- Jun 21st, 2008
Constraint is used to restrict invalid and inconsistent data entry into the table.Index is used for faster retrieval of data.Both are entirely different concepts.
srinivasa.dinesh
- Jan 29th, 2008
Constraints are rules applied to Table/Columns of a table. Constraints are one type of restriction which are useful while data is manipulated.Index are ordered list of rows of tables. Index are useful while faster retrival of data.Hope this anwsers to your question.Cheers,Srini.
-
View sysdate
How can we view sysdate for n number of times
krishnaindia2007
- Jun 21st, 2008
Select sysdate from user_tables where rownum <=50; Here you can use any table name that contains more than 50 rows.
-
CBO & RBO
What is CBO and RBO? What is diff between these two?
krishnaindia2007
- Jun 21st, 2008
The job of optimizer is to decide the most efficient method to execute a query. There are two types of optimizers in oracle RBO or Rule Based Optimizer since v6 CBO or Cost Based Optimizer sinc...
-
Hi1. State the order in which the trigger will get fired2.I have an doubt in trigger. I created two trigger. trigger name is only different but structure is same. I want to know the order in which these two trigger will get fired
krishnaindia2007
- Jun 22nd, 2008
1.Firing sequence of Database triggers on multiple rows Before statement trigger Before row trigger for first row After row trigger for first row Befo...
Prashanth
- Apr 24th, 2007
The one that is created last executes first
-
How to write the CSV command in SQL?
krishnaindia2007
- Jun 22nd, 2008
Spool the out put to text file as follows.SET HEADING OFF;SET FEEDBACK OFF;SPOOL EXAMPLE.TXTSELECT EMPNO || ','||ENAME||','||SAL FROM EMP;SPOOL OFF;SET FEEDBACK ON;SET HEADING ON;
Prashant Amburle
- Nov 4th, 2006
Try below wiil resolve the problemspool c:a.txtselect tname||','||tabtype from tab;spool offopen a.txt
-
I have two difference database (Not Table) in SQL. and Now I want to connect/Transfer daily basis data from one database table to another database table. Please tell me How to connect and tranfer data using Visual Basic.
Arun James
- Nov 8th, 2006
You can use a single query to do it ...Use the ordinary INSERT INTO Table2 SELECT col1, col2 .. FROM Table1But only consideration should be done is the two different DataBases .. Suppose Say our appl...
-
May i know about ref cursor?.
krishnaindia2007
- Jun 22nd, 2008
Ref cursor is basically a data type. A variable declared based on that data type is called cursor variable. The main advantage of using this variable is we can dynamically assosiate with different queries at runtime depending upon our requirement.
tariq_oracle
- Jun 8th, 2008
1. This is a dynamic cursor which can holds multiple value at run time2. Actually this is nothing but a data type of cursor variable
-
How can i spellout the number. but the number like $24 and output will be twenty four doller
Kranthi Reddy
- Oct 6th, 2006
Please find the sample below.select 24||'$', decode (substr('24$',1,1),1,'One',2,'Two',3,'Three',4,'Four','Others')|| decode (su...
-
What is the difference between %type and %rowtype?
krishnaindia2007
- Jun 22nd, 2008
%tye and %rowtype provide data independence and allows programs to adapt to database changes due to new business requirement.%type is used to declare a variable with same datatype as that of...
BASVARAJ KOLUR
- Mar 9th, 2007
the %type will return datatyp attached to refernced column or itemex: v_a emp.empid%type;it will return datatype attched to empid of emp table. the %rowtype will return all datatype attached ever...
-
Can a function take OUT parameters. If not why?
krishnaindia2007
- Jun 22nd, 2008
A cusor can tabke out parameter. But never use OUT and IN OUT parameters in functions. The main purpose of function is to take zero or more parameters and return a single value.
BASAVARAJ KOLUR
- Mar 9th, 2007
function can take out parameter and in out parameter in that case it returns values to calling program with return value also accroding to me its not good programing style to u...
-
What is select statement to spell out SALARY?(Query should work upto 5 million)
krishnaindia2007
- Jun 22nd, 2008
SELECT TO_CHAR(TO_DATE(11111,'J'),'JSP')||' DOLLARS' FROM DUALThis will work for the values in between 1 and 5373484 only.
-
What purpose we are using cursors? why you need cursor? why i write into clause when i want to write select statement.when you are using out and inout paramaters mode in procedure......pls explain ...
krishnaindia2007
- Jun 22nd, 2008
>>what purpose we are using cursors? why you need cursor?Whenever we are processing sql statement oracle allots PRIVATE SQL AREA OR WORK AREA to store porcessed information. Cursor is a pointer ...
srinadh
- Sep 9th, 2006
in stored procedure & function we can retrive the data sequentially we use cursor
-
Oracle long datatype
I'm using oracle9i to create a table which stores lyrics in long datatype. But if i store a long string of text and display it from the table, it displays only 80 chars. So someone help me how to store the whole lyrics txt file
ramyaselvi16
- Jun 24th, 2008
Variable length character values up to 2 gigabytes. Only one LONG column is allowed per table. You cannot use LONG datatype in functions, WHERE clause of SELECT, in indexing and subqueries.
-
Why order by clause maintains column number values instead of column names ?
rahul.katke
- Jun 24th, 2008
The column numbers are used so that you can do the order by without keying in all the column names in the order by clause. For eg.select empno, ename from emp order by empno; -- qry 1could b...
krishnaindia2007
- May 16th, 2008
There is no relation between column_id in user_tab_columns and column number in order by clause.For exampleSelect ename,job, sal from emp order by 1;Here it will arrange data in the order of employee ...
-
Arrange Date Week Wise
How to arrange date in week wise like from Monday to Sunday?
madhupriya.nukala
- Jun 26th, 2008
SELECT TO_CHAR(hiredate,'DAY') FROM emp ORDER BY to_char(hiredate-1,'D');
jannaat
- Apr 28th, 2008
Consider there is table H and you have 3 columns name week date so sql statement is select name ,week,date from H group by week order by date
-
What is rep cursor?
ravi.8311
- Jun 30th, 2008
Ref cursor is dynamic and no need to declare variables.ref cursor as two types 1.stong cursor(it has return value)2.week cursor (it has no return value)
bharatdwajmishra
- Jun 23rd, 2008
I know about REF cursor..it is one datatype , which by we return data to web pages fetch by java.. but REP cursor is new foe me..
-
There are 10 rows in Table A and 0 Rows in table B. Select * from A,BHow many rows will be retrieved.
10 rows
jasvinderss
- Jun 30th, 2008
There could be two cases(1) if you supply the query as Select * from A,Byou have not mentioned any join condition in this case 10 rows will be selected.(2)if you supply a join condition with the above queries with a common filed between them, then in that case no rows will be selcted.
nandk.sharma
- Jun 27th, 2008
No rows will be retrieved.The Cartesian product will only yield records when there is atleast one record in both the tables i.e. A and B.
Oracle SQL Interview Questions
Ans