-
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 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'
-
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
-
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.
-
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;
-
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 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
-
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,.......
-
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.
-
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
-
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
-
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 ...
-
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.
-
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...
-
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.
-
How can we use of select query to get the current date and time?
-
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. ...
-
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...
-
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.
-
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
SQL*Plus interview Questions
Ans