-
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 -
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 -
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??
-
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 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 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)
Ans