SQL INTERVIEW QUESTION-PART 1 Author: CloudVikas Published Date: 16 December 2020 Welcome to your SQL INTERVIEW QUESTION-PART 1 1. What is the difference between IN and = operator? = operator accepts only 1 value • IN Operator can accept more than 1 value.TrueFalse2. What are pseudo columns? we can select from pseudo columns, but we cannot 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 can insert, update, or delete their values.3. What is the difference between DATE and TIMESTAMP datatype? Date can store month, day, year, century, hours, minutes, and seconds. 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.59 TIMESTAMP can optionally store the Time Zone as well. SELECT CAST(SYSDATE AS TIMESTAMP WITH TIME ZONE) FROM DUAL; There is no difference4. 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. 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). 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 equal5. What is difference between Delete and Truncate and Which is faster? 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. 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. DELETE triggers on the table are fired if there are any triggers. we can delete a record from more than one table simultaneously we can use WHERE condition with truncate6. What is a DUAL table and why do we need it in DB? we cant drop all the columns from a table. 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; We Cant insert rows in the DUAL 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.7. What does INSTR() and SUBSTRQ return? INSTR function is used to return the location of a substring in a string. INSTR( string, substring [, Startposition [, thappearance ] ] ) SUBSTR function is used to extract a string from a string. SUBSTR( sourcestring, start_position, [ length-1 ] ) SUBSTR function is used to extract a substring from a string. SUBSTR( sourcestring, start_position, [ length ] )8. 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.FalseTrue9. Difference between HAVING and WHERE clause? HAVING is used to filter out the data after the aggregation takes place. HAVING is used to filter out the data before the aggregation takes place. WHERE is used to filter out the data before the aggregation takes place.10. 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.11. What types of Date Datatypes exist? DATE TIMESTAMP TIMESTAMP WITH TIME ZONE • TIMESTAMP WITH LOCAL TIME ZONENoyes12. 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;FalseTrue13. What is the usage of LPAD () and RPAD() functions? 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). 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.14. 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 themboth 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;1 out of Time is Up! Time's up Author: CloudVikas
