-
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...
-
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 ...
-
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
-
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
-
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 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 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
-
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...
-
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...
-
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.
-
What is the sub-query
Read Best Answer
Editorial / Best Answer
Answered by: Sailaja Pasupuleti
- Dec 4th, 2005
A sub-query is a part of query. Sub-queries are useful to find unknown value(s).
Ex: To find details of employees who are getting maximum salary.
In this requirement, the maximum salary not mentioned.
Solution:
To find maximum salary,
select max(sal) from emp;
For that maximum salary the employee details,
select * from emp where sal = (select max(sal) from emp);
So, in the above query, "select max(sal) from emp" is also a query but it returns a value (unknown or not given) and compares with "sal" column. This query is called sub-query.
-
What is a join
Read Best Answer
Editorial / Best Answer
Answered by: NK
- Jun 29th, 2005
A join is a query that combines rows from two or more tables, views, or materialized views. Oracle performs a join whenever multiple tables appear in the query's FROM clause. The query's select list can select any columns from any of these tables. If any two of these tables have a column name in common, then you must qualify all references to these columns throughout the query with table names to avoid ambiguity.
Most join queries contain WHERE clause conditions that compare two columns, each from a different table. Such a condition is called a join condition. To execute a join, Oracle combines pairs of rows, each containing one row from each table, for which the join condition evaluates to TRUE. The columns in the join conditions need not also appear in the select list.
To execute a join of three or more tables, Oracle first joins two of the tables based on the join conditions comparing their columns and then joins the result to another table based on join conditions containing columns of the joined tables and the new table. Oracle continues this process until all tables are joined into the result. The optimizer determines the order in which Oracle joins tables based on the join conditions, indexes on the tables, and, in the case of the cost-based optimization approach, statistics for the tables.
csbhaskar
- Apr 7th, 2011
A join is a query that results in combining rows from two or more tables, views, or materialized views. Oracle Database performs a join whenever names of multiple tables, views or materialized views a...
nirmal1in
- Jan 3rd, 2010
Join is relation between more than one table. No of minimum join conditions required is equal to n-1 (where n is number of tables joined together)
-
How do I eliminate the duplicate rows
Naveen Kumar39
- May 3rd, 2011
Delete from emp where rowid not in(select max(rowid) from emp group by empno);
SujitKr
- Feb 24th, 2010
DELETE FROM WHERE rowid NOT IN (SELECT max(rowid) FROM GROUP BY <column name containing repeated datas>);AStable name: tcolumn name: ename,branchSQL: DELETE FROM t WHERE rowid NOT IN (SELECT MAX (rowid) FROM t GROUP BY ename,branch)Sujit
-
What are the types of SQL statement
csbhaskar
- Mar 25th, 2011
SQL statements can be subdivided into the following six types:1. Data Definition Language (DDL) statements let you to perform these tasks: CREATE,...
majidwgl
- Dec 5th, 2009
Data Definition Language.Data Manipulation Languge.Data Transaction Control Language. Data Manipulation Control Language.Data session control. Data system control. etc these are SQL types statements.
-
What is a transaction
csbhaskar
- Mar 25th, 2011
A transaction is a logical unit of database related work that contains one or more valid and executable SQL statements. A transaction begins with the first executable SQL statement and ends when it is...
-
How many ways you can delete the table records ?
In how many ways can I delete all the contents of a table and the entire table. Explain with syntax ?
ranadhee_85
- Feb 9th, 2011
we delete record in a table 2 ways 1.by using delete command syntax:delet from table name where condition. example:delete from emp where en...
mukesh chand yadav
- Dec 7th, 2010
There are two(2) ways to delete recordes from a table are as follows: 1. Delete a specific record from table. ...
-
How can we use of select query to get the current date and time?
-
What are the number datatypes allowed in a table?
ranadhee_85
- Feb 9th, 2011
There are 5 types of datatypes 1.Numeric Datatype2.character Datatype3.LargeobjectsDatatype4.Rowiddatatype5.Time/Datedatatype
krishnaindia2007
- May 15th, 2008
There is no restiction on number of datatypes allowed except for long and LOB.I table can contain only one long column.
-
What is the basic difference between primary key and (unique key along with Not NULL).
Balakrishna12207
- Feb 7th, 2011
Primarykey is a combination of Unique Key and NotNull constraints.The major difference is dependence upon the query performance.One table can have only one Primarykey, But One table can have meny comb...
-
If a view on a single base table is manipulated will the changes be reflected on the base table
sanch167
- Dec 29th, 2010
A view on a single table can be modified, however the same cannot be done on multiple tables.
rampratap409
- Dec 7th, 2006
if a view based on one table or multiple table matter is it should contain all not null columns and additionaly no calculated column then ant DML statement will cause changes in base table.
SQL*Plus interview Questions
Read Best Answer
Ans