SQL INTERVIEW QUESTION-PART 1 Author: CloudVikas Published Date: 16 December 2020 Leave a Comment on SQL INTERVIEW QUESTION-PART 1 Welcome to your SQL INTERVIEW QUESTION-PART 1 1. 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.FalseTrue2. What does INSTR() and SUBSTRQ return? 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 ] ) INSTR function is used to return the location of a substring in a string. INSTR( string, substring [, Startposition [, thappearance ] ] )3. 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. we can select from pseudo columns, but we cannot insert, update, or delete their values. A pseudo column behaves like a table column, but is not actually stored in the table.4. What types of Date Datatypes exist? DATE TIMESTAMP TIMESTAMP WITH TIME ZONE • TIMESTAMP WITH LOCAL TIME ZONENoyes5. What is the difference between IN and = operator? = operator accepts only 1 value • IN Operator can accept more than 1 value.FalseTrue6. What is a DUAL table and why do we need it in DB? We Can 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 cant drop all the columns from a table. We Cant insert rows in the DUAL 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;7. What is differences between single row function and multi row function?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 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;8. 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. Difference between HAVING and WHERE clause? HAVING is used to filter out the data before the aggregation takes place. 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.10. 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. we can use WHERE condition with truncate 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.11. 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 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).12. What is difference between EXIST and IN when using in sub queries? 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. The inner query is executed last13. 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).14. What is the difference between DATE and TIMESTAMP datatype? 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 There is no difference 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.1 out of Time is Up! Author: CloudVikas
