SQL INTERVIEW QUESTION-PART 1 Author: CloudVikas Published Date: 16 December 2020 Welcome to your SQL INTERVIEW QUESTION-PART 1 1. What are pseudo columns? 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. we can select from pseudo columns, but we cannot insert, update, or delete their values.2. 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 ] ] )3. 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. Inner Join INNER JOINS return all rows from multiple tables where the join condition is 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).4. What types of Date Datatypes exist? DATE TIMESTAMP TIMESTAMP WITH TIME ZONE • TIMESTAMP WITH LOCAL TIME ZONENoyes5. What is the usage of LPAD () and RPAD() functions? If the string's length is greater than the required length, it will be trimmed (excess characters will be removed). 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 ",".6. What is the difference between IN and = operator? = operator accepts only 1 value • IN Operator can accept more than 1 value.TrueFalse7. What is differences between single row function and multi row function?None of themSingle 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;Multiple 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 them8. 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;TrueFalse9. What is the difference between DATE and TIMESTAMP datatype? Date can store month, day, year, century, hours, minutes, and seconds. There is no difference TIMESTAMP can optionally store the Time Zone as well. SELECT CAST(SYSDATE AS TIMESTAMP WITH TIME ZONE) FROM DUAL; 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.5910. 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.11. 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.FalseTrue12. What is a DUAL table and why do we need it in DB? its output is 1 Row and 1 Column whenever we execute below query Select count(1) from dual; We Cant insert rows in the DUAL table. we cant drop all the columns from a table. we can drop all the columns from a table. 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 Can insert rows in the DUAL table.13. What is difference between Delete and Truncate and Which is faster? 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 DELETE triggers on the table are fired if there are any triggers. we can use WHERE condition with truncate 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.14. What is difference between EXIST and IN when using in sub queries? The inner query is executed last 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 difference between the IN and EXISTS predicate in subquery is the way in which the query gets executed.1 out of Time is Up! Time's up Author: CloudVikas
