SQL INTERVIEW QUESTION-PART 1 Author: CloudVikas Published Date: 16 December 2020 Welcome to your SQL INTERVIEW QUESTION-PART 1 1. What does INSTR() and SUBSTRQ return? SUBSTR function is used to extract a substring from a string. SUBSTR( sourcestring, start_position, [ length ] ) SUBSTR function is used to extract a string from a string. SUBSTR( sourcestring, start_position, [ length-1 ] ) INSTR function is used to return the location of a substring in a string. INSTR( string, substring [, Startposition [, thappearance ] ] )2. What is the usage of LPAD () and RPAD() functions? LPAD (left pad) and RPAD (right pad) are used to add padding characters to the left or right side of a string up to a given length. The default padding character is ",". If the string's length is greater than the required length, it will be trimmed (excess characters will be removed).3. What is difference between EXIST and IN when using in sub queries? The difference between the IN and EXISTS predicate in subquery is the way in which the query gets executed. The inner query is executed first and the list of values obtained as its result is used by the outer query. The inner query is executed for only once. The inner query is executed last4. What is differences between single row function and multi row function?Single row functions return a single result row for every row of a queried table or view. select empno, ename, to_char(sal, '9,999.99') from emp;None of themMultiple row functions return a single result row based on groups of rows, rather than on single row. select deptno, sum(sal) from emp group by deptno;both of them5. What is the difference between IN and = operator? = operator accepts only 1 value • IN Operator can accept more than 1 value.TrueFalse6. What is a DUAL table and why do we need it in DB? we can drop all the columns from a table. its output is 1 Row and 1 Column whenever we execute below query Select count(1) from dual; The DUAL table is a special one-row table present and It is suitable for use in selecting pseudo columns such as SYSDATE or USER. It can be used as a sequence generator and for performing calculations. we cant drop all the columns from a table. We Can insert rows in the DUAL table. We Cant insert rows in the DUAL table.7. What are pseudo columns? we can select from pseudo columns, but we cannot insert, update, or delete their values. we can select from pseudo columns, but we can insert, update, or delete their values. It is used to get the current data from the Oracle database row ID. It is used to get the physical address of the rule from the database like this. A pseudo column behaves like a table column, but is not actually stored in the table.8. What is difference between Delete and Truncate and Which is faster? DELETE triggers on the table are fired if there are any triggers. DELETE command is used to remove rows from a table using a WHERE clause. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. we can delete a record from more than one table simultaneously TRUNCATE removes all rows from a table. You can not roll back the data. It does not affect any of the table's indexes, triggers, or dependencies. Since no UNDO log is maintained and has no dependencies TRUCATE is faster. we can use WHERE condition with truncate9. Identify the various kinds of joins? FULL Outer Join FULL Outer Join returns all rows from the LEFT hand table and RIGHT hand table with nulls in place where the join condition is not met. Right Outer Join This type of join returns all rows from the Right hand table specified in the ON condition and only those rows from the other table where the joined fields are equal Left Outer Join This type of join returns all rows from the LEFT hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met). Inner Join INNER JOINS return all rows from multiple tables where the join condition is met.10. What are Conversion functions? Conversion functions are useful to typecast data from one datatype to other. SELECT first_name, TO_CHAR (hiredate, ’MONTH DD, YYYY') HIRE_DATE, TO_CHAR (salary, '$99999.99') Salary FROM employees SELECT TO_NUMBER('1210.73', '9999.99') FROM DUAL; SELECT TO DATE('January 15, 2016', 'Month dd, YYYY') FROM DUAL;TrueFalse11. Difference between HAVING and WHERE clause? HAVING is used to filter out the data after the aggregation takes place. WHERE is used to filter out the data before the aggregation takes place. HAVING is used to filter out the data before the aggregation takes place.12. What types of Date Datatypes exist? DATE TIMESTAMP TIMESTAMP WITH TIME ZONE • TIMESTAMP WITH LOCAL TIME ZONEyesNo13. What is the difference between DATE and TIMESTAMP datatype? TIMESTAMP can optionally store the Time Zone as well. SELECT CAST(SYSDATE AS TIMESTAMP WITH TIME ZONE) FROM DUAL; Date can store month, day, year, century, hours, minutes, and seconds. There is no difference TIMESTAMP is much more fine-grained as a measurement of time than DATE is (milliseconds vs seconds). Date math with DATE is decimal math up to 5 places deep, example TRUNC(SYSDATE)+0,99999 = 2016-03-30 2359.5914. What is the difference between CHAR and NCHAR datatype?NCHAR is used to store fixed length Unicode data. It is often used to store data in different languages. CHAR is used to store fixed-length character data. When data is stored using CHAR, it takes n bytes while NCHAR takes 2n bytes.TrueFalse1 out of Time is Up! Time's up Author: CloudVikas
