SQL Tricky Interview Questions

What is the difference between NVL and NVL2?

In SQL, NVL() converts a null value to an actual value. Data types that can be used are date, character, and number. The data type must match with each other i.e. expr1 and expr2 must of the same data type. NVL (expr1, expr2) expr1 is the source value or expression that may contain a null. expr2 is the target value for converting the null. In SQL, NVL() converts a null value to an actual value. Data types that can be used are date, character, and number. The data type must match with each other i.e. expr1 and expr2 must of the same data type. NVL (expr1, expr2) expr1 is the source value or expression that may contain a null. expr2 is the target value for converting the null.

What is the function of COALESCE()?

The COALESCE() function examines the first expression if the first expression is not null, it returns that expression; Otherwise, it does a COALESCE of the remaining expressions.
The advantage of the COALESCE() function over the NVL() function is that the COALESCE function can take multiple alternate values. In simple words COALESCE() function returns the first non-null expression in the list.

How to find count of duplicate rows?

Select rollno, count (rollno) from Student
Group by rollno
Having count (rollno)>1
Order by count (rollno) desc;

How to calculate number of rows in table without using count function?

EmpNo	EName	Job	MGR	HireDate	Sal	Comm	DeotNo
1234	Amit	Waiter	8382	19-Oct-18	50000	500	50
5678	Ashish	Analyst	8635	2-Nov-18	60000	200	51

SELECT table_name, num_rows
FROM user_tables
WHERE table_name=’Employee’;

What is the use of FETCH command?

EmpNo	EName	Job	MGR	HireDate	Sal	Comm	DeptNo
1234	Amit	Waiter	8382	19-Oct-18	50000	500	50
5678	Ashish	Analyst	8635	2-Nov-18	60000	200	51

The FETCH argument is used to return a set of a number of rows. FETCH can’t be used itself, it is used in conjunction with OFFSET.
SELECT column_name(s)
FROM table_name
ORDER BY column_name
OFFSET rows_to_skip
FETCH NEXT number_of_rows ROWS ONLY;

Description

SQL Tricky Interview Questions can be used by any candidate who is preparing for Data Scientist Interview
All candidates who have to appear for the IT Officer can also refer to this short questions answers section.
You can also get access to the SQL Tricky Interview ebook.
SQL Tricky Interview Questions can be used in the preparation of B.Sc (IT) , M. Sc (IT), BCA, MCA and various other exams.
You can also download pdf for these questions Answers.
This section can also be used for the preparation of VIVA of various exams like B.Sc (computer science), M. Sc (computer science), BCA, MCA and many more.
SQL Tricky Interview Questions can be used to gain a credit score in various undergraduate and postgraduate courses like B.com, M.Com, MBA, BBA and many more.