-
Differentiate between TRUNCATE and DELETE
TRUNCATE deletes much faster than DELETETRUNCATEDELETEIt is a DDL statementIt is a DML statementIt is a one way trip,cannot ROLLBACKOne can RollbackDoesn't have selective features (where clause)HasDoesn't fire database triggersDoesIt requires disabling of referential constraints.Does not require
amarparash
- Sep 27th, 2008
DELETE is a DML command so it works on row level, where TRUNCATE is a DDL command, so it works on object level, TRUNCATE actually deletes the table and recreates it, so whatever people say that it works faster or frees occupied space or high water mark or oil mark.The actual internal work was that.
Shaveta Chawla
- Sep 7th, 2007
One main freature is when we have used auto increment clause the would return to next value after delete statement. e.g: if id =1000then after delete statement when we insert any record the value will be 1001 but if we use truncate statement then auto increment value will come to 0 positon
-
What is the difference b/w ORACLE and SQL
..........
sampra
- Mar 6th, 2012
SQL is a language to insert update delete the record where oracle is a database
vijaypati
- Mar 3rd, 2010
SQL stands for Structured Query Language. SQL is used for accessing and manipulating databases. SQL can execute queries against a database. SQL can Retrive, insert, update & delete from ...
-
Difference between decode and case.in which case we are using case and in which case we are using decode?with an example.
Sirajuddin84
- Dec 21st, 2009
The main problem with DECODE is that we can compare only discrete values and not ranged values. So they introduced the CASE statements from oracle 8i onwards. This CASE was able to handle ranged value...
joyfun23
- Nov 17th, 2009
select 'RAGHAV' from dual where dummy =(select CASE when 'A' ='A'then 'X' else 'Y'end from dual) CASE Works in where clause and Decode too!!
-
Replace and Translate
How and in what way REPLACE is different from TRANSLATE?
zahar
- Jun 10th, 2011
REPLACE : replace function is: replace( string1, string_to_replace, [ replacement_string ] )string1 is the string to replace a sequence of characters with another set of characters.string_to_repl...
sandip88
- Feb 9th, 2010
Translate does character by character substitution,where replace can substitute a singele character with a word
-
Which is more faster - IN or EXISTS?
EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.
rohitosu
- Mar 9th, 2012
As a rule of thumb IN should be used when the sub query will return small result set. It should be kept in mind that the sub query should not return null values. EXISTS should be used when the result set returns large result set. Other factors that might affect query performance is the indexes
Vineet Arya
- Sep 17th, 2011
Which is more faster - IN or EXISTS? It totally depends upon the query. For some queries Exist is efficient and for some IN is efficient Exits is correlated sub query, in which the outer query run f...
-
Create a query that displays the employees' last names and commision amounts.If an employee does not earn commmission, put "No Commission." Label the column COMM.
pollysingh544
- Feb 22nd, 2010
Select last name,decode(commision,0,'No Commision',commision) comm from employee
-
Consider that we r having table name emp....now the question is 1.how should we print the odd rows from that table?2.how should we print the rows where comm=NULL.Reply ASAP
jayshree13
- May 13th, 2011
Ans:1. select * from emp where (rowid,1) in (select rowid,mod(rownum,2) from emp);Ans:2: select * from emp where comm is null;
vishnu vardhan reddy
- Sep 16th, 2008
Select e.* from( select rownum rn ,ename,sal from emp) e where mod(rn,2)=0
-
Find manager for employee
ID employee department manager----------------------------------------------1 Suresh c++ NULL 2 Suresh c++ NUll3 Suresh c++ 25 Sarathy testing 26 Rajaraman c# 17 joe Flash 1I have the employee detail table ,In that i want to find the manager for each employee .The employee...
Read Best Answer
Editorial / Best Answer
mktiwary- Member Since Jun-2008 | Jun 6th, 2008
If you take the normal Oracle Employee (emp) table which consists of columns such as Employee Number (EMPNO) and Manager Number (MGR) then the query to find managers for all the employee is:
select e.ename as Employee, m.ename as Manager from emp e, emp m
where e.MGR = m.EMPNOpoojamohan
- Sep 25th, 2012
How to get the old manager name ,if the employee has been changed to other manager?
-
How to add new column in the existing table at our desired location?
panwar_5
- Jan 8th, 2011
This can be done eaisly using the keyword after,suppose you want to add a column "name" after the field "id".Alter table emp add name varchar2 after id
Ramkiran_79
- Aug 6th, 2010
You cannot have user defined syntaxes in Oracle, still it is implemented, following error will be thrown. 1* alter table employee add (age number(10)) after enameSQL> /alter table employee add (a...
-
Produce a List of 5 Highest Revenue Transactions
You need to produce a list of the five highest revenue transactions from the Orders table in the Sales database. The Orders table is defined as follows:CREATE TABLE Orders ( OrderID Int IDENTITY NOT NULL, SalesPersonID Int NOT NULL, RegionID Int NOT NULL, OrderDate Datetime NOT NULL, OrderAmount Int NOT NULL ) Which statement will produce the report correctly?...
kethinenisarath
- Mar 8th, 2011
A List of 5 Highest Revenue Transactions select * from (SELECT OrderID, SalesPersonID, RegionID, OrderDate, OrderAmount, dense_rank() over(partition by ORDERID order by OrderAmount desc) "...
krishsidd
- Nov 25th, 2010
insert into orders values(101,12301,11,sysdate,1000); insert into orders values(102,12302,12,sysdate,500); insert into orders values(103,12303,13,sysdate,1700); insert into orders values(104,12304,...
-
Avoid Data Duplication
If in a table we dont use primary key or any other unique key then how to avoid duplication of data?
-
What are various constraints used in SQL?
NULLNOT NULLCHECKDEFAULT
-
What is a pseudo column. Give some examples?
It is a column that is not an actual column in the table.eg USER, UID, SYSDATE, ROWNUM, ROWID, NULL, AND LEVEL.
Amol Kulkarni
- Dec 3rd, 2016
Pseudo columns are used to pass additional information on a row. A pseudo column is not actually part of a row like any other columns. but exists due to the row property, they cannot be updated or mod...
-
Suppose there are two fields in table(Employee) say Name and Salary and there are in total 100 records in table. Now my query gives the name of an employee whose salary is 10th among 100 salaries. this query should be solve in SQL not in PL/SQL
abhikum.402
- Aug 13th, 2012
Select emp_name from (select *,Dense_RANK()over (order by Emp_sal) SalayRank from Emp_a)A
where SalayRank=10 -
Can we use aggregate function in another aggregate functionsuppose we got count of a column by using the group by clause so we get different counts in the same statement how to get the maximum of count
gkbiswal
- Sep 29th, 2007
We can use aggregate function inside another aggregate function.Example:SQL> select max(Count(*)) from emp group by deptno;MAX(COUNT(*))------------- 6DB Version: Oracle9i Enterprise Edition Release 9.0.1.1.1
How can you retrieve the numeric values from varchar2 type column?
Hi Friends, One table is there. It is having varchar2 type column and this column having both numeric and alphanumeric values but I want to retrieve only numeric related values from that table. If it is possible then say the answer plz... I will wait for ur tremendous answer...
Write a query that display the records based on condition. excluding the record that meets the condition Eg... Select all employees who has same salary as empno 20. My query should return all the rows excluding empno20.
kirandstyle
- May 5th, 2008
Hi dude,if suppose there is a Jobhistory table which contains the following columns in itsay...empno,ename,salary,startdate,enddate,positionhere startdate is the date where an employee starts his new ...
How many codd's rules are supporting by the oracle?.Plz clarify this.
Sampath Kumar
- May 24th, 2016
Does Oracle 12c supports these 12 Codd Rules
aaravindkumar
- Dec 13th, 2007
The only rule that is not satisfied by oracle db is the systamatic treatment of NULL values. NULL in oracle is treated as absence of a value or unknown status (in case of comparision of values) and it...
Syntax for getting the table witout any contents
Read Best Answer
Ans