Q.41-How can we fetch all the records from two tables but common records only once?
Ans:- By using UNION set operator.
Example:-
Select * from emp
UNION
Select * from emp1;
Q.42- What is the difference between NVL and NVL2 ?
Ans:- NVL function is used to place the value for NULL.
NVL2 function is used to place the values for NULL and NOT NULL .
Example:- select empno,ename,job,sal,comm,nvl(comm,10) from emp;
Above query will display the value of comm is 10 where comm is null.
select empno,ename,job,sal,comm,nvl2(comm,10,20) from emp;
Above query will display the value of comm is 20 where comm is null and 10 where comm is not null.
Q.43- How can I create an empty table emp1 with same structure as emp?
Ans:- Create table emp1 as select * from emp where 1=2;
Q.44- Find the 3rd MAX salary in the emp table ?
Ans:- select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
Q.45- Find the 3rd MIN salary in the emp table ?
Ans:- select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal >= e2.sal);
Q.46- How can we select first n records from emp table?
Ans:- select * from emp where rownum <= &n;
Q.47- How can we select last n records from emp table?
Ans:- select * from emp
minus
select * from emp where rownum <= (select count(*) - &n from emp);
Q.48- Howmany LONG datatypes columns are allowed in a table ? Can we use LONG datatypes column in "WHERE" or in "ORDER BY"?
Ans:- Only 1 LONG datatype column allowed in a Table.
No. We can't use LONG datatype column in "Where" clause or in "Order by" clause.
Q.49-How can we find duplicate records in a table ?
Ans:-select empno,count(*) from emp group by empno having count(*)>1;
Note:- Firstly you have to decide on which column/columns basis duplicate you want.
If more than one columns you have decided then you have put all decided column in
"select" and in "group by".
Q.50-Can we create Primary Key and Foreign Key in a same table ?
Ans:-Yes
Q.51-Find the all employees who have at least one person reporting to them ?
Ans:-select ename from emp where empno in (select mgr from emp);
Q.51-What will be result of below query ?
select 10 from dual minus select 3 from dual;
Ans:- Result will be
10
Q52. How can we convert Julian Date to date ?
Ans :- Using ‘JSP’ format string
SQL > select to_char(to_date(2456317,’JSP’),’dd-Mon-yyyy’) as day from dual;
DAY
————
24-Jan-2013
Q53. How can we convert date to Julian Date format ?
Ans :- Using ‘J’ format string
SQL > select to_char(to_date(’24-Jan-2013′,’dd-mon-yyyy’),’J’) as julian from dual;
JULIAN
——-
2456317
Q54. msissdn_master table has msisdn column with some data start with 0 that data need to update with 91 in place of 0 , How can we do that ?
Ans :- By below update query we can do that
update master_msisdn
set msisdn = case when substr(msisdn,1,1)=0 then '91'||substr(msisdn,2);
commit;
Q55.How we can pass more than 1000 values in "IN" Operator?
Ans:-For doing this , Firstly values need to insert into temporary table then select values from temporary table in "IN" operator
Example :-
select * from tab1 where col1 in (select col from temp_tab);
Q56. EMP and SALGRADE tables are there with below structure
EMP
SALGRADE
How we can get the data for employee with empno, ename and grade ?
Ans:- Below is the query for same
select a.empno,a.ename,b.grade from emp a , salgrade b
where a.sal between b.minsal and b.maxsal;
Q57. What is the difference between clustered and a non-clustered index?
Ans:-A clustered index is a type of index where record reordered on same way records present in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
A Nonclustered index is a type of index in which the logical order of the index does not match the physical stored order of the rows on disk.
Q58. How can we view last record added to a table?
Ans:- Select * from (select * from employees order by rownum desc) where rownum
Q59. How can we display row numbers with the records?
Ans:- select rownum, a.* from emp a;
Ans:- By using UNION set operator.
Example:-
Select * from emp
UNION
Select * from emp1;
Q.42- What is the difference between NVL and NVL2 ?
Ans:- NVL function is used to place the value for NULL.
NVL2 function is used to place the values for NULL and NOT NULL .
Example:- select empno,ename,job,sal,comm,nvl(comm,10) from emp;
Above query will display the value of comm is 10 where comm is null.
select empno,ename,job,sal,comm,nvl2(comm,10,20) from emp;
Above query will display the value of comm is 20 where comm is null and 10 where comm is not null.
Q.43- How can I create an empty table emp1 with same structure as emp?
Ans:- Create table emp1 as select * from emp where 1=2;
Q.44- Find the 3rd MAX salary in the emp table ?
Ans:- select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
Q.45- Find the 3rd MIN salary in the emp table ?
Ans:- select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal >= e2.sal);
Q.46- How can we select first n records from emp table?
Ans:- select * from emp where rownum <= &n;
Q.47- How can we select last n records from emp table?
Ans:- select * from emp
minus
select * from emp where rownum <= (select count(*) - &n from emp);
Q.48- Howmany LONG datatypes columns are allowed in a table ? Can we use LONG datatypes column in "WHERE" or in "ORDER BY"?
Ans:- Only 1 LONG datatype column allowed in a Table.
No. We can't use LONG datatype column in "Where" clause or in "Order by" clause.
Q.49-How can we find duplicate records in a table ?
Ans:-select empno,count(*) from emp group by empno having count(*)>1;
Note:- Firstly you have to decide on which column/columns basis duplicate you want.
If more than one columns you have decided then you have put all decided column in
"select" and in "group by".
Q.50-Can we create Primary Key and Foreign Key in a same table ?
Ans:-Yes
Q.51-Find the all employees who have at least one person reporting to them ?
Ans:-select ename from emp where empno in (select mgr from emp);
Q.51-What will be result of below query ?
select 10 from dual minus select 3 from dual;
Ans:- Result will be
10
Q52. How can we convert Julian Date to date ?
Ans :- Using ‘JSP’ format string
SQL > select to_char(to_date(2456317,’JSP’),’dd-Mon-yyyy’) as day from dual;
DAY
————
24-Jan-2013
Q53. How can we convert date to Julian Date format ?
Ans :- Using ‘J’ format string
SQL > select to_char(to_date(’24-Jan-2013′,’dd-mon-yyyy’),’J’) as julian from dual;
JULIAN
——-
2456317
Q54. msissdn_master table has msisdn column with some data start with 0 that data need to update with 91 in place of 0 , How can we do that ?
Ans :- By below update query we can do that
update master_msisdn
set msisdn = case when substr(msisdn,1,1)=0 then '91'||substr(msisdn,2);
commit;
Q55.How we can pass more than 1000 values in "IN" Operator?
Ans:-For doing this , Firstly values need to insert into temporary table then select values from temporary table in "IN" operator
Example :-
select * from tab1 where col1 in (select col from temp_tab);
Q56. EMP and SALGRADE tables are there with below structure
EMP
SALGRADE
How we can get the data for employee with empno, ename and grade ?
Ans:- Below is the query for same
select a.empno,a.ename,b.grade from emp a , salgrade b
where a.sal between b.minsal and b.maxsal;
Q57. What is the difference between clustered and a non-clustered index?
Ans:-A clustered index is a type of index where record reordered on same way records present in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
A Nonclustered index is a type of index in which the logical order of the index does not match the physical stored order of the rows on disk.
Q58. How can we view last record added to a table?
Ans:- Select * from (select * from employees order by rownum desc) where rownum
Q59. How can we display row numbers with the records?
Ans:- select rownum, a.* from emp a;
No comments:
Post a Comment