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.
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
0 Responses to “SQL Basics - Interview Questions Part 2”
Post a Comment