-
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 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 is the output of SIGN function
sharmasl
- Mar 23rd, 2009
For - values it return (-1);for 0 it return (0);for + values it return (+1);
qwe
- Mar 29th, 2007
SIGN (a): Returns 1 if a is positive ,-1 if a is less than 0 and 0 if a is 0
-
What is a corelated subquery
G.D.S.Prasad
- Jun 21st, 2008
It is a subquery which is evaluated once for each row processed by parent statement.
globalanil18
- Mar 31st, 2007
Hi Guys,The Oracle Server performs a correlated subquery when the subquery references a column from a table reffered to in the parent statement. A correlated subquery is evaluated once for each row pr...
-
How do you set AutoComit off for DDL commands in Oracle Server? Is this Possible..?
vishakha_jain81
- Aug 12th, 2008
For DDL commands, We cannot do autocommit off. It is modify structure?of database object such as table, view and these commands have automatically commit.Set autocommit on/off is used for DML.
-
What values are stored internally for time (hour,minute ,second) if the date format is dd-mon-yy??
shanthi_9881
- May 8th, 2008
In sqlserver 2005 we can use as follows to retreive the date part of the given date or sys dateselect datepart(dd,getdate())
Dharmendra Gohil
- Sep 26th, 2006
Date Functions: =============Oracle stores dates in an internal numeric format:century,year,month,day,hours,minutes and secondsDefault display format is DD-MON-YYYYOracle stores...
-
What is the condition to be follwed for the natural join syntax to be correct??
abhi_only12000
- Jan 2nd, 2010
A natural join will join two datasets on all matching column names, regardless of whether the columns are actually related in anything other than name.SELECT d.dname, d.loc, e.ename, e.job FROM dept d...
vishakha_jain81
- Aug 7th, 2008
It implicitly construct the join condition on the 2 tables taking all the column with same name into consideration.Natural join is also called blind join.select? employee_id,last_name,department_name from employees natural join departments;
-
How many autonumber columns are allowed?
shailendra
- Aug 22nd, 2012
You can add 1024 column in the one table.
-
-
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??
-
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
-
Can a view be updated/inserted/deleted
sweetytrivedi
- Jun 24th, 2008
If a view is based upon a single table then DML operations can be performed on it, while if a view is based upon more than two tables then it is a complex view and DML operations can be performed using instead of triggers.
Srikanth
- Nov 7th, 2006
A view can be manpulated by using instead of triggers
-
Disable Restricted Session
How do you get back into SQLPlus to disable restricted session if you enabled.
-
What is difference between CHAR and VARCHAR2
Read Best Answer
Editorial / Best Answer
Answered by: srinivasan
- Aug 16th, 2005
A varchar2 datatype, when stored in a database table, uses only the space
allocated to it. If you have a varchar2(1999) and put 50 bytes in the table, we
will use 52 bytes (leading length byte).
A char datatype, when stored in a database table, always uses the maximum length
and is blank padded. If you have char(1999) and put 50 bytes into it, it will
consume 2001 bytes (leading length field is present on char's as well).
In the database -- a CHAR is a VARCHAR that is blank padded to its maximum
length.
Nitin Yadav
- Nov 9th, 2017
Char is fixed and varchar 2 is variable.
gopalakrishna
- Jul 28th, 2015
Char is a data type based on the given size memory is used
varchar2 based on the given characters memory is used -
Display Odd/ Even number of records
sharmasl
- Mar 23rd, 2009
These are the querys:- FOR Odd number of records: select * from emp where (rowid ,1) in (select rowid, mod(rownum,2) from emp); FOR Even number of records: select * from emp where (rowid ,0) in (select rowid, mod(rownum, 2) from emp); try.........................
nandk.sharma
- Jun 30th, 2008
We can use the below query to get the odd record of the table --select r from (SELECT rownum r FROM EMP)where mod(r,2) != 0We can use the below query to get the even record of the table --select r from (SELECT rownum r FROM EMP)where mod(r,2) = 0
-
How to drop the column in a table
sharmasl
- Mar 23rd, 2009
use the following syntex for drop the columnalter table emp drop column ename;
NARENDRA
- Dec 12th, 2006
Alter table?TABLENAME drop column?COLUMNNAME Generally rollback cant be followed are implemented along with these DDL statements. The commit statement automatically involves.
-
What is the difference between Inner and equi join?
supratim.kundu
- Mar 4th, 2010
An equi join is used to?retrieve?all the columns from both the tables. An inner join is used to?retrieve selected columns from tables.
patilpravin_1981
- Jan 28th, 2008
Inner jon Or equi join is The query that retrives the rows that exact match using (=) sign in where clause
-
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)
-
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.
SQL*Plus interview Questions
Ans