-
What is correlated sub-query
Read Best Answer
Editorial / Best Answer
rampratap409- Member Since Sep-2006 | Dec 7th, 2006
when a sub query executes for each row its called a corelated sub query, in other words when a sub query takes the input from outer query for executation then execute and passes the result to outer query then outer query executes.
sravan1
- Dec 13th, 2006
In correlated subqueries, the information from the outer select statement participates as a condition in the inner select statement.Eg : Select deptno, ename, sal from emp a where sal> (select avg(sal) from emp b where a.deptno =b.deptno)order by deptno;
rampratap409
- Dec 7th, 2006
when a sub query executes for each row its called a corelated sub query, in other words when a sub query takes the input from outer query for executation then execute and passes the result to outer query then outer query executes.
-
Difference between SUBSTR and INSTR
Read Best Answer
Editorial / Best Answer
Answered by: Shiv Mangal Rahi
- Jul 24th, 2006
Hi All,
INSTR function finds the numeric starting position of a string within a string.
As eg.
Select INSTR('Mississippi','i',3,3) test1,
INSTR('Mississippi','i',1,3) test2,
INSTR('Mississippi','i',-2,3) test3
from dual;
Its output would be like this
Test1 Test2 Test3
___________________________________________
11 8 2
SUBSTR function returns the section of thte specified string, specified by numeric character positions.
As eg.
Select SUBSTR('The Three Musketeers',1,3) from dual;
will return 'The'.
Shiv Mangal Rahi
- Jul 24th, 2006
Hi All,INSTR function finds the numeric starting position of a string within a string.As eg.Select INSTR('Mississippi','i',3,3) test1, INSTR('Mississipp...
Nagarajan.R
- Jul 18th, 2005
INSTR (String1,String2(n,(m)), INSTR returns the position of the mth occurrence of the string 2 in string1. The search begins from nth position of string1. SUBSTR (String1 n,m)&nb...
-
Explain UNION, MINUS, UNION ALL and INTERSECT
Answer posted by Scott on 2005-05-25 18:32:51: UNION - the values of the first query are returned with the values of the second query eliminating duplicates. MINUS - the values of the first query are returned with duplicates values of the second query removed from the first query. UNION ALL - the values of both queries are returned including all duplicates INTERSECT - only the duplicate...
Read Best Answer
Editorial / Best Answer
rampratap409- Member Since Sep-2006 | Dec 7th, 2006
UNION: Take the common record once( No Duplicate)
UNION ALL : Takes Duplicates also
Minus: Result of first query - result of second query
examle
select * from emp where rownum<=9;
minus
select * from emp where rownum<9;
this will display 9th row of emp
Intersect: common result of both query
rampratap409
- Dec 7th, 2006
UNION: Take the common record once( No Duplicate)UNION ALL : Takes Duplicates alsoMinus: Result of first query - result of second queryexamleselect * from emp where rownum<=9;minusselect * fr...
Atal
- Sep 16th, 2005
UNION: Take the common record once( No Duplicate)UNION ALL : Takes Duplicates alsoExampl : Say there is EMP having 14 reconds then...select count(1) from (select ename from emp) returns 14select...
-
What is the fastest way of accessing a row in a table
Read Best Answer
Editorial / Best Answer
Indu- Member Since Sep-2005 | Sep 15th, 2005
Quote
A ROWID is created by Oracle for each new row in every table, it is a pseudo column that has a value for every row in a table. The ROWID gives us the physical address of a row and is the fastest way to access any row. The ROWID contains 3 bits of information, they are :-
- The block within the database file.
- Row # within the block.
- Database file ID.
An example could be :-
000088C9.0191.0002
The ROWID has three important uses, it is :-
- The fastest path to any row.
- A way of speeding the COMMIT process in application code.
- Unique identifiers of any given row.
Unquote
Indu
- Sep 15th, 2005
Quote A ROWID is created by Oracle for each new row in every table, it is a pseudo column that has a value for every row in a table. The ROWID gives us the physical address of a row and is the f...
Sajida
- Jun 10th, 2005
By using rowid
-
What is an integrity constraint
Read Best Answer
Editorial / Best Answer
Shivani Goel- Member Since Jun-2009 | Jun 23rd, 2009
Integrity constraint is that which depend upon the parent and child relationship in which parent column have the primary constraint and child column have the foreign key constraint.
You cannot delete the any value from column that have foreign key constraint until you never delete its related primary key column value, that is a integrity constraint.
If you try to do this it will show you error related to integrity constraint.Shivani Goel
- Jun 23rd, 2009
Integrity constraint is that which depend upon the parent and child relationship in which parent column have the primary constraint and child column have the foreign key constraint. You cannot d...
Rossitza Kanovska
- Aug 9th, 2005
An integrity constraint is a rule that restricts the values in a database. There are six types: A NOT NULL constraint prohibits a database value from being null. A unique constraint prohibi...
-
What is ON DELETE CASCADE
Read Best Answer
Editorial / Best Answer
Answered by: bharath
- May 30th, 2005
when the user deletes a record in the master table , all the corresponding records in the detail table along with the records in the master table will be deleted.
G.D.S.Prasad
- Jun 21st, 2008
ON DELETE CASCADE option permits deletions of referenced key values in the parent table and automatically deletes dependent rows in the child table to maintain referential integrity.
deva
- Sep 20th, 2006
Its not with the data of the table, rather with the associated constraints of the table
-
What are the data types allowed in a table
Read Best Answer
Editorial / Best Answer
Answered by: rajesh
- Sep 14th, 2005
these are the data types used in sql tablebinary Bigint bit Char datetime decimal Float image Int Money nchar Ntext nvarchar Numeric Real smalldatetime smallint smallmoney sql_variant sysname text timestamp tinyint varbinary varchar uniqueidentifier gdurga
- Jun 15th, 2009
Dec(p,s), desimal(p,s), double precision, foat, int, inteager, real, smallint, numeric(p,s), number(p,s), char(size), varchar2(size), long, raw, long raw, date, timestamp, urowid(size), rowid, boolean, nchar(size) , nvarchar2(size), bfile, blob, clob, nclob...
rajesh
- Sep 14th, 2005
binaryBigintbitChardatetimedecimalFloatimageIntMoneyncharNtextnvarcharNumericRealsmalldatetimesmallintsmallmoneysql_variantsysnametexttimestamptinyintvarbinaryvarcharuniqueidentifierthese are the data types used in sql table
-
How many LONG columns are allowed in a table
Read Best Answer
Editorial / Best Answer
Gayathri Panchangam- Member Since Aug-2008 | Sep 5th, 2008
Only one column can have LONG data type. And that column cannot be used in WHERE clause or in ORDER BY clause. Also we cannot add primary key constraint to that column only not to the whole table, that means we will give primary key to any column in the table except the column having the long data type. If we give, we get the error ORA-02269: key column cannot be of LONG datatype.
Gayathri Panchangam
- Sep 5th, 2008
Only one column can have LONG data type. And that column cannot be used in WHERE clause or in ORDER BY clause. Also we cannot add primary key constraint to that column only not to the whole table, tha...
Nikhil_4_Oracle
- Mar 5th, 2007
Hey all, You can have only one column of long in table , And this table can`t have Primary key Constraint. --Nikhil
-
HOw to get /select the nth row from the table ?how to select first n rows ,last n rows from a table
sandip.dhopat
- Jun 9th, 2011
Select nth Row:select * from(select t1.c1, t1.c2, .... , rank(col_name) over (order by col_name) rnkfrom table_name t1)where rnk = n;To select top n rows in a order.select * from(select t1.c1, t1.c2, ...
p.mamta
- Aug 30th, 2010
How to get the nth row from the table ?select distinct(Salary),* from Employee A where n=(select count(distinct(Salary)) from Employee b where b.Salary <= a.Salary);how to select first ...
-
What is referential integrity constraint
Read Best Answer
Editorial / Best Answer
Shivani Goel- Member Since Jun-2009 | Jun 23rd, 2009
Referential integrity constraint is that which depend upon the parent and child relationship. In this one of the column have the primary key constraint and one of the column of another table have the foreign key constraint. So you cannot delete the column value which having the foreign key constraint until you never delete its related primary key column value from parent table. If you tried to do this it will show you error related to referential integrity constraint. But if you still want to delete value from child table but never want to delete parent table column value then that purpose you can use 'on delete cascade' option.
kierthi
- Aug 16th, 2011
A feature provided by relational database management systems (RDBMS's) that prevents users or applications from entering inconsistent data. Most RDBMS's have various referential integrity rules that y...
Shivani Goel
- Jun 23rd, 2009
Referential integrity constraint is that which depend upon the parent and child relationship. In this one of the column have the primary key constraint and one of the column of another table h...
-
What are the more common pseudo-columns
Answer posted by Scott on 2005-05-25 18:43:29: SELECT rownum, rowid FROM sometable
-
How many autonumber columns are allowed?
shailendra
- Aug 22nd, 2012
You can add 1024 column in the one table.
-
Explain the different types of joins
Read Best Answer
Editorial / Best Answer
krishna420- Member Since Jul-2008 | Jul 23rd, 2008
There are 5 types of joins available in sql server
1) inner join
2) outer join( left outer join,Right outer join)
3) Full Join
4) Cross Join
5) Self Join
1) inner Join : Two tables having the common column values go with inner join.
Example:
select * from emp inner join empsalary
on
emp.empid=empsalary.empid
2) outer join:
Outer join has two sub types left outer join,Right outer join
Left outer join: Taking Left side values of the table checking into right side
select * from emp left outer join empsalary
on emp.empid=empsalary.empid
Right Outer join:Taking Right side values of the table checking left side
select * from emp right outer join empsalary
on emp.empid=empsalary.empid
3) Full join :
Full join is combined with left outer join ,Right outer join.
select * from emp full join employee
on emp.empid=employee.empid
4) cross Join: cross join is an carteasion product ,its like matrix format here it wont come on condidtion
select * from emp cross join employee
Self join :
Self join can check within the table called self join.zunaid
- Oct 12th, 2012
Different types of joins
Natural join
Inner join
outer join
cross join
equi join
non equi join
self joinvamsi krishna devineni
- Nov 27th, 2011
Mainly we uses some joins in the real time environment.. that is 1) Inner join 2) left outer join 3) right outer join 4) full outer join 5) cross join i will explain in easy way so that u can underst...
-
What is ROWID
Read Best Answer
Editorial / Best Answer
Answered by: Zimmy
- Jul 10th, 2005
ROWID is the psedo columns indicate the stored location of the data physically in the database.
Ravi
- Jul 3rd, 2013
ROWID: It is pseudo column,it has physical address location of row in ORACLE database
The format is: OOOOOO.FFF.BBBBBB.RRR
OOOOOO is the object ID
FFF is the file number
BBBBBB is the block number
RRR is the row numbervikas
- Dec 21st, 2012
But according to you it has 16 digits ... and according to my oracle 10g it is showing 18 digits. like AAAC9EAAEAAAABXAAA .. why??
-
What is difference between TRUNCATE & DELETE
Answer posted by Scott on 2005-05-25 18:30:04: TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server. DELETE is a DML command and can be rolled back. Both commands accomplish identical tasks (removing all data from a table), but TRUNCATE is much faster.
Read Best Answer
Editorial / Best Answer
Answered by: Prangya Sahu
- Nov 21st, 2005
1>TRUNCATE is a DDL command whereas DELETE is a DML command.
2>TRUNCATE is much faster than DELETE.
Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.
3>You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.
4>In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.
5>You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause.
Shahida Sultana
- Mar 6th, 2014
TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesnt use as much undo space as a DELETE.
saravanan
- Sep 18th, 2012
Delete & truncate command will delete entries from a table.. where as we can delete a single entry using delete but incase of truncate it wont be possible..
using truncate will delete the log files,buffer storage in a table permanently but incase of delete it wont -
-
-
How many types of joins are there in oracle and what are the difference between them
Biswadeepak Swain
- Jun 3rd, 2015
This is not Cartesian products that one is Cartesian products. Thanks
abhi_only12000
- Jan 2nd, 2010
In Oracle we can catogrise joins in following ways -(1) Equi Join (Query having equal sign for condition)select empno, ename, dnamefrom emp join depton emp.deptno = dept.deptno;(2) Natural Join : both...
-
If an unique key constraint on DATE column is created, will it validate the rows that are inserted with SYSDATE ?
It won't, Because SYSDATE format contains time attached with it.
Ashima Chhabra
- Jun 20th, 2015
No...you can save a single row with the sysdate but not more than 1 on same date because format of date is YYYY-MM-DD and format of datetime is YYYY-MM-DD hh:mm:ss[.nnn] and format of smalldatetime is YYYY-MM-DD hh:mm:ss
rampratap409
- Dec 8th, 2006
yes with unique constraint on date column we can have same date becuase internally date is stored as dd-mon-yyyy hh:mi:ss
-
If a View on a single base table is manipulated will the changes be reflected on the base table ?
If changes are made to the tables which are base tables of a view will the changes be reference on the view.
SQL*Plus interview Questions
Ans