-
How to use comand "COMMIT"and "ROLLBACK" in a transacsion?
joshmohit
- Oct 26th, 2010
Commit;commit work;commit work 'comment';rollback;rollback work;rollback to savepoint ;
narendra kumar
- Dec 6th, 2006
COMMIT:it is used to make the manipulations ENABLE permanently on the table where as ROLLBACK is used to DISABLE the effect of the queries(which were typed a bit above it) on ...
-
How can we order the column of day (mon to sun)such that monday comes first then tues and so no.Suppose we have retrieved day out of hiredate column
katgeektalk
- Aug 26th, 2010
Select hiredate, to_char(hiredate, 'Day') "DAY OF THE WEEK"from scott.emporder by to_char(hiredate-1, 'D')
dj_dj_dj
- Mar 22nd, 2010
Select to_char(hire_date, 'day')from table_nameorder by to_char(hire_date -1 ,'day');RegardsDharmendra Jaiswal
-
Display the records between two range I know the nvl function only allows the same data type(ie. number or char or date Nvl(comm, 0)), if commission is null then the text “Not Applicable” want to display, instead of blank space. How do I write the query
katgeektalk
- Aug 26th, 2010
If the values in the field comm both NULL and 0 the query should beselect empno, decode(nvl2(comm, to_char(comm), 'N/A'), '0', 'N/A')from scott.empNVL2 to convert NULL value into N/ADecode to convert 0 value into N/A
emraan
- Dec 29th, 2009
1) SELECT e.*, nvl2(comm, 'Not Null', 'Null') FROM emp e 2) SELECT e.*, decode(comm, '', 'Is Null', 'Is not Null') FROM emp e
-
Explian the 18 character long ROWID? How it gets constructed? How we will identify each part of ROWID?
sandip01
- May 25th, 2010
ROWID is internally constructed when a new row is added.BBBBBB.RRRRRR.FFFFFFBBBBBB - Represents the blocks in the datafile.RRRRRR - Represents the rows in the block.FFFFFF - Represents the file no.
imtiazali
- Jun 5th, 2009
Rowid and Rownum are psuedocolumns for every table Oracle provides unique rowid, rownum.Rowid are in alphanumeric, rowid are 18 character, for every rowid last 3 character will be changed.
-
Default Constraint
What is default constraint? How we you apply it?
Sam Kolta
- Apr 25th, 2010
1. What is default constraint? Constraints are specific values that used to limit the type of data that can go into a table by ensure certain columns have a true condition when data is inserting, modi...
gdurga
- Jun 15th, 2009
A coulumn can be given a default value by using DEFAULT option. This option prevents null values from entering the column if a row is inserted with out a value in to the table.Ex: .......Hire_date DATE DEFAULT SYSDATE,.......
-
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
-
Find out nth highest salary from emp table
abhi_only12000
- Jan 2nd, 2010
SELECT ename, sal FROM (select ename,sal,dense_rank() OVER (order by sal desc) dr FROM emp) WHERE dr =n;
chirla_pavan
- Sep 12th, 2008
Select * from employee where 1=(select count(*) from employee as e where employee.sal<e.sal)
-
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 Multiple Transactions can be controlled ?
abhi_only12000
- Jan 2nd, 2010
Multiple transaction can be controlled by three ways:1. COMMIT : Make changes done in transaction permanent.2. ROLLBACK : Rollbacks the state of da...
rampratap409
- Dec 4th, 2006
Use savepoint after each DML statement. If using procedure/function then do not give commit in them. commit in proc/function will erase your savepoints.
-
How to grant all permission of one user to another user.
abhi_only12000
- Jan 2nd, 2010
Grant all privileges of user_name1 to user_name2 with grant option.where user_name1 is the user of which we have to pass privileges,and user_name2 is the user to which we are assign the privileges,and grant option is nothing but it allows the user_name2 to pass all privileges to another.
mukesh
- Mar 20th, 2007
SELECT 'GRANT ' ||privilege ||' ON '||table_schema||'.'||table_name ||' TO '||USER ||' ;' FROM ALL_TAB_PRIVSWHERE grantee ='TILITS' -- user name that has already access
-
To display the all employee name with end of ‘S’ without using like operator ?
goutamgeek
- Dec 23rd, 2009
select * from table_name where substr(first_name,length(first_name),1)='S'
-
Display the number value in Words
bhaskar1981buie
- Dec 4th, 2007
The number which have precesion can be spelled in words by this way..SELECT DECODE(SUBSTR(LPAD('1256932.1',DECODE(INSTR('1256932.1','.'),0,11,DECODE(LENGTH('1256932.1')...
-
To view installed Oracle version information
Answer posted by Scott on 2005-05-25 18:41:26: From the SQL> prompt, type the following: select * from v$version;
-
What are the different types of SQL
gdurga
- Jun 18th, 2009
1. DDL: Data Definition Language2. DML: Data Manipulation Language3. TCL: Transaction Control Language4. DCL: Data Control Language5. DQL: Data Query Language
-
What are the types of SQL Statement ?
Data Definition Language : CREATE,ALTER,DROP,TRUNCATE,REVOKE,NO AUDIT & COMMIT.Data Manipulation Language: INSERT,UPDATE,DELETE,LOCK TABLE,EXPLAIN PLAN & SELECT.Transactional Control: COMMIT & ROLLBACKSession Control: ALTERSESSION & SET ROLESystem Control : ALTER SYSTEM.
gdurga
- Jun 15th, 2009
DDL: Data Defintion LanguageDML: Data Manipulation LanguageTCL: Transaction Control LanguageDCL: Data Control Language
-
The use of HAVING , WHERE and GROUPBY in one SQL
thumatinagaraju
- Oct 23rd, 2006
Hiin where condition we cann't use group function but having clause allows it, both r used 4 data restriction only.group by is used 4 grouping similar items.
-
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 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
-
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
-
Implicit Cursor attributes
Haraprasad
- Nov 3rd, 2008
SQL%ISOPEN exists. Oracle always evaluates this to FALSE as PL/SQL closes implicit cursors immediately after they are executed.
rampratap409
- Dec 7th, 2006
sql%isopen is there but it will always return "false" for implicit cursor
SQL*Plus interview Questions
Ans