Q.1- How we can update gender column data from 'M' to 'F' and 'F' to 'M' in emp table?
Ans:- By using decode or case function
UPDATE EMP SET gender=DECODE(gender,'M','F','F','M');
OR
UPDATE EMP SET gender = CASE WHEN gender='M' THEN 'F' ELSE 'F' END;
Q.2-How we can get 2nd highest salary without using Analytical Function ?
Ans:-select max(sal) from emp where sal<>(select max(sal) from emp);
Q.3-How we can get the employee details of 2nd highest salary without using Analytical Function ?
Ans:- select * from emp where sal=(select max(sal) from emp
where sal<(select max(sal) from emp));
Q.4- Find the employee details whose salary is greater than avg salary of his department ?
Ans:-select * from emp a where a.sal>(select avg(sal) from emp b where a.deptno=b.deptno);
Q.5- Find the employee details whose salary is greater than avg salary of employees with same job ?
Ans:-select * from emp a where a.sal>(select avg(sal) from emp b where a.job=b.job) ;
Q.6- Write a query for deleting duplicate records on the basis of empno & ename ?
Ans:-delete from emp where rowid not in (select max(rowid) from emp
group by empno,ename) ;
Q.7- What are the database objects ?
Ans:-Below are the database objects
1) Table
2) View
3) Sequence
4) Synonym
5) Index
6) Tablespace
Q.8- What is the difference between delete and truncate ?
Ans:-Difference between delete and truncate are below:-
a) Delete is a DML Command while as Truncate is a DDL Command
b) Delete locks table row while as Truncate locks entire table
c) We can use "where condition" with delete while as we can't user "where
condition" with Truncate
d) DML Trigger fired during the delete while as DML Trigger doesn't fire during the Truncate
e) We can rollback delete operation while as we can't rollback Truncate operation
Q.9- What is the difference between Primary Key and Unique Key ?
Ans:-Difference between Primary Key and Unique Key are below:-
a) Only 1 Primary key can be defined in one table while as unique
key can be defined more than 1 in a table
b) Primary never accept null value while as Unique key can
accept multiple null values
c) On the creation of Primary Key,clustered index created on table
while as On the creation of Unique Key ,non-clustered index created on table
d) Primary key is used for identify a record while a Unique key is
used to prevent duplicate records in a table
Q.10- What is the difference between case and decode ?
Ans:- a) Case can work with relational operator while decode can't work
with relational operator.
b) Case can work with operator like,between,exists, in while decode
can't work with these operators
c) case can be used in PLSQL with same syntax individually while
decode can't work individually in PLSQL
d) Both treat NULL differently.
Q.11- What is the difference between replace and translate ?
Ans:- a) Translate works character by character while replace works
string by string.
Q.12- What is the difference between trunc and round ?
Ans:- Round(float_value,number) function looks decimal digit on position number+1,if this digit is 5 or greater than 5 then it add 1 on last decimal digit .
Trunc(float_value,number) function, select value upto number decimal position.
Q.13- What is the difference between rank and dense_rank ?
Ans:-a) rank and dense_rank both are Analytical function and using for
ranking. Both giving same rank number while getting same value
more than one times but rank skip next rank number by n-1 while
dense_rank does not skip the next rank number.
Q.14- What is the difference between lag and lead ?
Ans:- a) lag and lead both are analytical function, Lead is used get the next
value of same column while Lag is used to get the previous value of same column.
Q.15- What is the difference between view and synonym ?
Ans:- a) synonym can be created for any Oracle objects like
table,view,sequence,function,procedure etc. while view can be
created for only table,view or synonym.
b) synonym can be private or public while view can be read only or
update-able.
c) View is logical table while synonym is a alternate name for object
Q.16- What is the difference between varchar and varchar2 ?
Ans:- a) Varchar take space null string while varchar2 is not taking space
for null string.
b) Varchar store maximum 2000 byte while varchar2 can store
maximum 4000 byte.
Q.17- What are pseudo column available in Oracle ?
Ans:-Below are the pseudo column available in the Oracle
1) Rownum
2) Rowid
3) Level
4) Currval
5) Nextval
Q.18- What are constraint available in Oracle ?
Ans:-Below are the constraint available in the Oracle
1) Primary Key
2) Unique Key
3) Not Null
4) Check Constraint
5) Foreign Key
Q.19- What is the Simple Sub-Query & correlated Sub-Query ?
Ans:- Simple Sub-Query is Query that can be executed independently and parent query executed on the result of Simple Sub-Query.
Example:-select * from emp where deptno in (select deptno from dept);
correlated Sub-Query is a query that is dependent on parent query and executed for every records of parent query . It can't be executed independently.
Example:-select a.* from emp a where a.sal>(select avg(b.sal) from emp b where a.deptno=b.deptno);
Q.20-Write a query that display Odd number of records?
Ans:-select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
Ans:- By using decode or case function
UPDATE EMP SET gender=DECODE(gender,'M','F','F','M');
OR
UPDATE EMP SET gender = CASE WHEN gender='M' THEN 'F' ELSE 'F' END;
Q.2-How we can get 2nd highest salary without using Analytical Function ?
Ans:-select max(sal) from emp where sal<>(select max(sal) from emp);
Q.3-How we can get the employee details of 2nd highest salary without using Analytical Function ?
Ans:- select * from emp where sal=(select max(sal) from emp
where sal<(select max(sal) from emp));
Q.4- Find the employee details whose salary is greater than avg salary of his department ?
Ans:-select * from emp a where a.sal>(select avg(sal) from emp b where a.deptno=b.deptno);
Q.5- Find the employee details whose salary is greater than avg salary of employees with same job ?
Ans:-select * from emp a where a.sal>(select avg(sal) from emp b where a.job=b.job) ;
Q.6- Write a query for deleting duplicate records on the basis of empno & ename ?
Ans:-delete from emp where rowid not in (select max(rowid) from emp
group by empno,ename) ;
Q.7- What are the database objects ?
Ans:-Below are the database objects
1) Table
2) View
3) Sequence
4) Synonym
5) Index
6) Tablespace
Q.8- What is the difference between delete and truncate ?
Ans:-Difference between delete and truncate are below:-
a) Delete is a DML Command while as Truncate is a DDL Command
b) Delete locks table row while as Truncate locks entire table
c) We can use "where condition" with delete while as we can't user "where
condition" with Truncate
d) DML Trigger fired during the delete while as DML Trigger doesn't fire during the Truncate
e) We can rollback delete operation while as we can't rollback Truncate operation
Q.9- What is the difference between Primary Key and Unique Key ?
Ans:-Difference between Primary Key and Unique Key are below:-
a) Only 1 Primary key can be defined in one table while as unique
key can be defined more than 1 in a table
b) Primary never accept null value while as Unique key can
accept multiple null values
c) On the creation of Primary Key,clustered index created on table
while as On the creation of Unique Key ,non-clustered index created on table
d) Primary key is used for identify a record while a Unique key is
used to prevent duplicate records in a table
Q.10- What is the difference between case and decode ?
Ans:- a) Case can work with relational operator while decode can't work
with relational operator.
b) Case can work with operator like,between,exists, in while decode
can't work with these operators
c) case can be used in PLSQL with same syntax individually while
decode can't work individually in PLSQL
d) Both treat NULL differently.
Q.11- What is the difference between replace and translate ?
Ans:- a) Translate works character by character while replace works
string by string.
Q.12- What is the difference between trunc and round ?
Ans:- Round(float_value,number) function looks decimal digit on position number+1,if this digit is 5 or greater than 5 then it add 1 on last decimal digit .
Trunc(float_value,number) function, select value upto number decimal position.
Q.13- What is the difference between rank and dense_rank ?
Ans:-a) rank and dense_rank both are Analytical function and using for
ranking. Both giving same rank number while getting same value
more than one times but rank skip next rank number by n-1 while
dense_rank does not skip the next rank number.
Q.14- What is the difference between lag and lead ?
Ans:- a) lag and lead both are analytical function, Lead is used get the next
value of same column while Lag is used to get the previous value of same column.
Q.15- What is the difference between view and synonym ?
Ans:- a) synonym can be created for any Oracle objects like
table,view,sequence,function,procedure etc. while view can be
created for only table,view or synonym.
b) synonym can be private or public while view can be read only or
update-able.
c) View is logical table while synonym is a alternate name for object
Q.16- What is the difference between varchar and varchar2 ?
Ans:- a) Varchar take space null string while varchar2 is not taking space
for null string.
b) Varchar store maximum 2000 byte while varchar2 can store
maximum 4000 byte.
Q.17- What are pseudo column available in Oracle ?
Ans:-Below are the pseudo column available in the Oracle
1) Rownum
2) Rowid
3) Level
4) Currval
5) Nextval
Q.18- What are constraint available in Oracle ?
Ans:-Below are the constraint available in the Oracle
1) Primary Key
2) Unique Key
3) Not Null
4) Check Constraint
5) Foreign Key
Q.19- What is the Simple Sub-Query & correlated Sub-Query ?
Ans:- Simple Sub-Query is Query that can be executed independently and parent query executed on the result of Simple Sub-Query.
Example:-select * from emp where deptno in (select deptno from dept);
correlated Sub-Query is a query that is dependent on parent query and executed for every records of parent query . It can't be executed independently.
Example:-select a.* from emp a where a.sal>(select avg(b.sal) from emp b where a.deptno=b.deptno);
Q.20-Write a query that display Odd number of records?
Ans:-select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
No comments:
Post a Comment