-
What is CoRelated SubQuery
A subquery within a subquery
rjk1203
- Sep 19th, 2008
A Correlated Subquery?which executes?with value returned by the child Query. Unlike subquery it executes per result from child query.Ex: removing duplicate rows from a table Subquery executes?once for the entire child?query's result
-
Display deptno wise first three lowest salaries in ascending order of deptno and desc order of salary
vishnu vardhan reddy
- Sep 16th, 2008
select e.* from( select enmae,sal, dense_rank() over(partition by deptno order by sal) ranksal from emp)e where e.ra...
hiasat
- Aug 4th, 2008
SELECT empno,ename,salFROM ( select empno,ename,sal from emp order by sal asc )WHERE rownum
-
What is a self-join and when can it be used
krishnaindia2007
- Jun 22nd, 2008
Join the table to itself is called self join. It is used when you need to join the data with the same table data.Ex:- SELECT A.ENAME,A.SAL ,B.ENAME,B.SAL FROM EMP A,EMP B WHERE A.MGR = B.EMPNO
-
How to delete a duplicate records in a table without using rowid
Praveen2384
- Sep 9th, 2008
Delete from table_name where rownum in (select min(rownum) from table_name group by column2,column3);where column2 and column3 are repeating columns.
Karthik
- Mar 14th, 2007
Hi,If you have primary key Constrains in the tableFollowing is query will select the duplicate ----------------------------------------Select * from X a, X bWhere a.1 != b.1 ( Primary Key) And a 2 = b...
-
Base table of View table
How do we view the query used while creating a View table? i.e How to find the dependencies of a view table.
sinhasaurabh014
- Sep 4th, 2008
If you have toad, you can see everything. Just navigate to view throgh schema browser and check under the tabs "view". select your view and check all the properties from it.
vermapradeep1980
- Aug 30th, 2008
Try this one.. select text from user_view where view_name ='viewname'pradeep
-
Oracle Insert statement execution
Explain the internal process and steps how SQL statement is executed in Database ?
ananth.dikshit
- Aug 27th, 2008
In Order to execute any SQL statement first we need to start an oracle instance. for that we need to connect to any tool(SQL* Plus) and that will start?a user process. This user Process will contact ...
gpsaravanan202
- Dec 12th, 2007
The execution flow of sql statement is internally done by means of internal cursor.these are steps followed in executing the statementParsebindexecutefetchclose cursor....More details can be studied in oracle supplied packages concept...
-
Redundancy of data is many times benificial, Justify the statement.
artbria
- Aug 25th, 2008
A table is said to be redundant (that is data is repeated) if it is in a denormalized format. For ex, we have a table, which displays item, item no, item cost etc. which is denormalized. To avoid redu...
patilpravin_1981
- Jan 29th, 2008
A person can have same name in database therfore it is difficult to identify the records thats why DBMS provide data integrity to enter valid data & unique data enter into database. which is use to avoid data redudancey in database.
-
How many null value a unique key constraint can have?
I think there can be only one null value for a unique key constraint but some say there can be more than one null value.
ammupriyaa
- Aug 22nd, 2008
Can have any number of NULL values, since oracle?maintains internally different values for each?NULL values.
-
Ampersand in SQL Output
How to display & in Oracle SQL output
sailajabetha
- Aug 18th, 2008
Use concatenate operator ...SQL> select 'a'||'&'||'b' from dual;'A'---a&b
-
How to select the all first rows which are having same id.for example:id name salary11. suresh 1000011. suresh 1200012. rajesh 1200012. rajesh 13000now i want to get rows as below given format11. suresh 1000012. rajesh 12000
nis1981
- Aug 10th, 2008
Select t1.id, t1.name, t1.salfrom test t1, test t2where t1.id = t2.idand t1.sal < t2.sal;
-
Having following table named RATING :EmpID,ReviewSeason,CustID1,s1,c11,s1,c21,s2,c32,s2,c22,s3,c43,s3,c5Write a SQL statement, return EmpID who has been REVIEWED more than once.
Harshita
- Apr 18th, 2006
Select empID from RATING group by ReviewSeason having count(empID)>1
-
Select 1 from dualunionselect 'A' from dual; I guess the query will select zero rows. Is it wrong or right ? if wrong thenWhat will be the result set for the query?Thanks in advance.
nis1981
- Aug 3rd, 2008
Inorder to use UNION SET OPERATOR the datatype and the number of columns should match. Here you are trying to get the union of a character datatype and a number, hence you get an error.
Renuka Rajput
- Jul 29th, 2006
i would like to add one more point that not only datatypes but also structure of both the select statement should be same.if we write query asselect 1,2 from dualunionselect 1 from dualit will still results in error and the error isexpression must have same datatype as corresponding expression
-
What is the Hirerachy of SQL?
-
1) how many number of tables we can join in join concepts?is there any limitation?i think its not...2) since joins and subqueries are used for same purpose , why we have two things.is there any need
harshasp
- Jul 16th, 2008
To join ntable we need n-1 join conditions....And whenever we are not able to retrieve the data using Joins..we use Subqueries...Writing the conditions to query throught join is difficult but through subquery its easy.and performance wise Joins are good...
krishnaveni_g
- Jul 16th, 2008
we want to join n table we need n-1 join conditions. There is no limitation. Some queries not solved by using the joins we can go for subqueries. Performance wise joins is better.
-
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.
-
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..
-
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
-
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 ...
-
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.
-
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 SQL Interview Questions
Ans