Q.21-Write a query that display Even number of records?
Ans:-select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp);
Q.22- Which one is faster between IN and EXISTS and why?
Ans:- EXISTS is more faster than IN since EXISTS return Boolean and IN return values.
Q.23-Which Operator is used for dealing with NULL value?
Ans:- IS and IS NOT Operator is used for dealing with NULL values.
Q.24-Find all department which have more than 3 employees
Ans:-select * from dept where deptno in (select deptno from (
select deptno,count(*) count from emp group by deptno having count(*)>3));
Q.25-Display the name of employee who joined on same date
Ans.-select a.* from emp a,emp b
where a.hiredate=b.hiredate and a.empno<>b.empno;
Q.26- Display the employee information whose salary is greater that his manager salary
Ans:-select a.* from emp a where a.sal>(select b.sal from emp b where b.empno=a.mgr);
Q27:- Display the employee details who joined company before 10 years from today
Ans:-select * from emp where months_between(hiredate,sysdate)<(10*12);
Q.28- What is the difference between SUBSTR and INSTR ?
Ans:- SUBSTR is used get the sub-string from main string ,It return string.
While INSTR return the number and used for getting the position of
sub-string/character in main string
Q.29- What is the difference between UNION and UNION ALL ?
Ans:- "Union" return unique records from both queries while as "Union all" show the all records from both queries means "Union all" does not eliminate the duplicate records while as "Union" eliminate the duplicate records.
Q.30- What is the difference between WHERE and HAVING ?
Ans:-HAVING and WHERE , both are used to filter the records .HAVING filter the records of "group by" result while WHERE is used to filter records before group by.
"Where" is records filter while as "Having" is the filter for group by result.
Where can not apply on grouping function while having can apply on grouping function
Example:- select deptno,job,count(*) from emp
where job in ('MANAGER','SALESMAN','CLERK')
group by deptno,job having count(*)>1;
Here we can use having with any of column deptno, job and count(*) while where condition can not apply with count(*) .
Q.31- What is View ?
Ans:- View is a logical table created from table,view or synonym . View doesn't take any storage except query stored on data dictionary.
Q.32-What is the fastest way to fetch data from the table ?
Ans:-Access by "ROWID" is the fastest way to fetch data from table.
Q.33-What is the dual table in Oracle?
Ans:-Dual is a table with only 1 column and 1 record . Column name is DUMMY and record value is X . Datatype of column is varchar2(1). This table is used for calculations .
Q.34- How we can fetch common records from two tables ?
Ans:- By INTERSECT set Operator
Q.35- Which operator is used in query for pattern matching?
Ans:- Like operator is used for pattern matching with ( _ and % ) wildcards.
Q.36- If EMP table has 14 records and DEPT table has 5 records then howmany records will be there in below query ?
select * from emp,dept;
Ans:- There will be 14x5=70 records as no join condition mentioned in query so cross join will be there.
Q.37- One Table "PARTS" has a column part_type_id with following values:-
1, 0, 0, 1, 1, 1, 1, 0, 0, 1, 0, 1, 0, 1, 0, 1
Write a query to add 2 where part_type_id is 0 and add 3 where part_type_id is not equal to 0.
Ans:- update PARTS set part_type_id = case when part_type_id = 0 then part_type_id+2 else part_type_id+3 end;
Q.38- Write a SQL query using UNION ALL that uses the WHERE clause to eliminate duplicates.
Ans:- Below is example query for same
select * from emp where deptno =10
union all
select * from emp where deptno!=10;
Q.39-How can we fetch only common records from two tables ?
Ans:- By using INTERSECT set operator.
Example:-
Select * from emp
INTERSECT
Select * from emp1;
Q.40-How can we retrieve all records of tab1 those are not present in tab2?
Ans:- By using MINUS set operator.
Example:-
Select * from emp
MINUS
Select * from emp1;
No comments:
Post a Comment