Saturday, June 18, 2011

SQL Basics - Interview Questions Part 2


16.Difference between Char and Varchar2?
Varchar2  is similar to char but can store available number of characters and while querying  the table  varchar2 will  trims  the extra spaces and fetches  the rows that exactly match the criteria.
17.Difference between LOB  and LONG data types?
The maximum size of  an LOB is 4GB. It will support random access to data where in LONG maximum size is 2GB. It will support sequential  access to data.
18.Single Row functions: It will work on single row and give result for all the rows.
Ex: to_char, to_date etc.
19.Group Functions: It will work on group of  rows in a table  and gives  a single row result. Ex: Sum(), Avg(), min(), max().. Etc.
20.String Handling Functions?
Instr     it   returns the  position   of   the string  where  it   occur  according   to   the parameters.
Instrb – instr and instrb returns same but in the form of bytes.
Substr – It returns the portion  of  a string depending on the parameters from and to.
Substrb – Substr and Substrb returns the same thing  but Substrb returns in  the form of bytes
21.Sign: Sign is a function it will take numbers, as inputs and it will give
 i.   1 for positive integer
ii.    -1 for negative integer
 iii.    0 for ZERO
SQL> Select sign(-1234) from dual;             O/P: -1
22.Differences between UNION and UNION ALL?
Union:  The values  of  the first  query are returned with  the values  of  the  second query eliminating the duplicates.
Union All: The values of the first query are returned with the values of the second query including the duplicates.
23.Difference between NVL and NVL2 functions?
NVL  is used  to  fill  a NULL  value  to known value.  NVL2  will  identify  the  NULL values  and Filled values  it  returns exp3 if it  is null otherwise it  returns  exp2. We have to pass 3 parameters for NVL2 and 2 parameters for NVL.
24.How can we compare range of values with out using the CASE?
By using Decode with in Decode.
25.Can we Decode with in a Decode?
YES
26.Decode and Case Difference?
Case compares a Range of  values and Decode will work as if else statement.
27.Difference between Replace and Translate?
Replace  is  used  to  replace   the whole  string   and  we can  pass  null  values  in replace.  Translate  is used  to  translate  character-by-character here we  have to pass the three parameters.
28.Difference between where and having clause?
Where used  to specify  condition  and used to restrict  the data. Having  used  to specify the condition on grouped results and used to filter the data.
29.Difference between IN and EXISTS clause?
EXISTS gives the status of  the  inner query.  If the  inner  query  is success  then  it returns true other wise it returns false and IN will compare the list of values.
30.Difference between subquery and correlated subquery?
Query with  in a query  is subquery.  Inner query will  executes first  and based  on the result  the outer query will  be displayed.  Correlated  subquery outer  query will executes first and then inner query will be executed.

0 Responses to “SQL Basics - Interview Questions Part 2”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.