Avatto>>DATA SCIENTIST>>SHORT QUESTIONS>>SQL Tricky Interview Questions
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.
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.
Select rollno, count (rollno) from Student
Group by rollno
Having count (rollno)>1
Order by count (rollno) desc;
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’;
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