Interview Questions.

Q1. Difference between varchar and varchar2 statistics types?

Ans: Varchar can save upto 2000 bytes and varchar2 can keep upto 4000 bytes. Varchar will occupy area for NULL values and Varchar2 will now not occupy any area. Both are differed with appreciate to area.

Q2. In which language Oracle has been evolved?

Ans: Oracle has been evolved the usage of C Language.

Q3. What is RAW datatype?

Ans: RAW datatype is used to save values in binary facts format. The most length for a raw in a table in 32767 bytes.

Q4. What is the usage of NVL feature?

Ans: The NVL function is used to update NULL values with some other or given price. Example is –

NVL(Value, replace fee)

Q5. Whether any instructions are used for Months calculation? If so, What are they?

Ans: In Oracle, months_between feature is used to find wide variety of months among the given dates. Example is –

Months_between(Date 1, Date 2)

Q6. What are nested tables

Ans: Nested table is a information type in Oracle that's used to assist columns containing multi valued attributes. It additionally preserve whole sub desk.

Q7. What is COALESCE function?

Ans: COALESCE function is used to return the price which is about to be no longer null in the listing. If all values in the list are null, then the coalesce function will return NULL.

Coalesce(value1, value2,value3,…)

Q8. What is BLOB datatype?

Ans: A BLOB facts kind is a varying length binary string that is used to keep  gigabytes reminiscence. Length need to be laid out in Bytes for BLOB.

Q9. How do we constitute feedback in Oracle?

Ans: Comments in Oracle can be represented in two methods –

Two dashes(–) earlier than beginning of the line – Single statement

/*—— */ is used to symbolize it as comments for block of assertion

Q10. What is DML?

Ans: Data Manipulation Language (DML) is used to get right of entry to and control statistics in the existing items.  DML statements are insert, select, update and delete and it gained’t implicitly dedicate the modern-day transaction.

Q11. What is the distinction among TRANSLATE and REPLACE?

Ans: Translate is used for individual with the aid of person substitution and Replace is used replacement a unmarried individual with a word.

Q12. How will we display rows from the desk with out duplicates?

Ans: Duplicate rows may be removed by using the use of the keyword DISTINCT in the select announcement.

Q13. What is the usage of Merge Statement?

Ans: Merge assertion is used to pick rows from one or extra records supply for updating and insertion into a table or a view. It is used to mix more than one operations.

Q14. What is NULL value in oracle?

Ans: NULL price represents missing or unknown information. This is used as a place holder or represented it in as default entry to indicate that there's no real information gift.

Q15. What is USING Clause and supply instance?

Ans: The USING clause is used to specify with the column to check for equality whilst two tables are joined.[sql]Select * from employee be part of earnings using worker ID[/sql]

Employee tables be a part of with the Salary tables with the Employee ID.

HubSpot Video
 

Q16. What is key preserved desk?

Ans: A table is about to be key preserved desk if each key of the table also can be the key of the result of the be a part of. It ensures to go back most effective one reproduction of every row from the bottom desk.

Q17. What is WITH CHECK OPTION?

Ans: The WITH CHECK choice clause specifies test level to be carried out in DML statements. It is used to prevent adjustments to a view that would produce results that are not covered within the sub question.

Q18. What is using Aggregate capabilities in Oracle?

Ans: Aggregate function is a function where values of multiple rows or data are joined collectively to get a unmarried fee output. Common combination functions are –

Average

Count

Sum

Q19. What do you suggest by GROUP BY Clause?

Ans: A GROUP BY clause may be utilized in select statement wherein it'll accumulate data across more than one statistics and institution the results through one or more columns.

Q20. What is a sub question and what are the specific types of subqueries?

Ans: Sub Query is likewise referred to as as Nested Query or Inner Query that is used to get records from multiple tables. A sub question is added inside the in which clause of the principle query.

There are  distinctive types of subqueries:

Correlated sub question

A Correlated sub question can not be as impartial question but can reference column in a table indexed inside the from list of the outer question.

Non-Correlated subquery

This may be evaluated as though it were an independent question. Results of the sub query are submitted to the principle question or determine question.

Q21. What is move join?

Ans: Cross be part of is defined as the Cartesian product of facts from the tables present in the be part of. Cross be a part of will produce end result which combines every row from the first table with the each row from the second one table. 

Q22. What are temporal facts types in Oracle?

Ans: Oracle offers following temporal statistics kinds:

Date Data Type – Different codecs of Dates

TimeStamp Data Type – Different codecs of Time Stamp

Interval Data Type – Interval between dates and time

Q23. How will we create privileges in Oracle?

Ans: A privilege is nothing however proper to execute an SQL question or to get admission to any other user item. Privilege may be given as gadget privilege or consumer privilege.

[sql]GRANT user1 TO user2 WITH MANAGER OPTION;[/sql]

Q24. What is VArray?

Ans: VArray is an oracle statistics kind used to have columns containing multivalued attributes and it may maintain bounded array of values.

Q25. How will we get subject info of a table?

Ans: Describe <Table_Name> is used to get the sphere info of a distinctive table.

Q26. What is the difference between rename and alias?

Ans: Rename is a permanent name given to a table or a column whereas Alias is a temporary call given to a desk or column. Rename is not anything however alternative of name and Alias is an change name of the desk or column.

Q27. What is a View?

Ans: View is a logical table which based on one or greater tables or views.  The tables upon which the view is based totally are referred to as Base Tables and it doesn’t include facts.

Q28. What is a cursor variable?

Ans: A cursor variable is associated with exclusive statements which can hold special values at run time. A cursor variable is a form of reference kind.

Q29. What are cursor attributes?

Ans: Each cursor in Oracle has set of attributes which enables an application software to check the country of the cursor. The attributes can be used to check whether cursor is opened or closed, discovered or no longer observed and also find row count.

Q30. What are SET operators?

Ans: SET operators are used with  or more queries and people operators are Union, Union All, Intersect and Minus.

Q31. How are we able to delete reproduction rows in a table?

Ans: Duplicate rows inside the table can be deleted by way of the usage of ROWID.

Q32. What are the attributes of Cursor?

Ans: Attributes of Cursor are

%FOUND

Returns NULL if cursor is open and fetch has not been done

Returns TRUE if the fetch of cursor is completed efficaciously.

Returns False if no rows are returned.

%NOT FOUND

Returns NULL if cursor is open and fetch has now not been finished

Returns False if fetch has been achieved

Returns True if no row become back

%ISOPEN

Returns real if the cursor is open

Returns false if the cursor is closed

%ROWCOUNT

Returns the range of rows fetched. It needs to be iterated via entire cursor to offer precise real be counted.

Q33. Can we shop pics within the database and if so, how it could be achieved?

Ans: Yes, we are able to save pix inside the database by using Long Raw Data type. This datatype is used to shop binary information for 2 gigabytes of period. But the table could have handiest on Long Raw information type.

Q34. What is an integrity constraint?

Ans: An integrity constraint is a declaration defined a business rule for a table column. Integrity constraints are used to ensure accuracy and consistency of facts in a database. There are kinds – Domain Integrity, Referential Integrity and Domain Integrity.

Q35. What is an ALERT?

Ans: An alert is a window which appears in the center of the display masking a part of the modern-day show.

Q36. What is hash cluster?

Ans: Hash Cluster is a method used to save the table for quicker retrieval. Apply hash fee at the table to retrieve the rows from the desk.

Q37. What are the various constraints used in Oracle?

Ans: Following are constraints used:

NULL – It is to indicate that unique column can comprise NULL values

NOT NULL – It is to signify that unique column can not contain NULL values

CHECK – Validate that values in the given column to fulfill the unique standards

DEFAULT – It is to signify the price is assigned to default cost

Q38. What is difference among SUBSTR and INSTR?

Ans: SUBSTR returns precise part of a string and INSTR affords man or woman position in which a pattern is discovered in a string.

SUBSTR returns string whereas INSTR returns numeric.

Q39. What is the parameter mode that can be passed to a manner?

Ans: IN, OUT and INOUT are the modes of parameters that may be surpassed to a process.

Q40. What are the unique Oracle Database items?

Ans: There are special facts items in Oracle –

Tables – set of factors prepared in vertical and horizontal

Views  – Virtual desk derived from one or greater tables

Indexes – Performance tuning method for processing the information

Synonyms – Alias name for tables

Sequences – Multiple customers generate specific numbers

Tablespaces – Logical storage unit in Oracle

Q41. What are the variations among LOV and List Item?

Ans: LOV is belongings while listing gadgets are considered as single item. List of gadgets is set to be a group of list of objects. A listing object can have only one column, LOV will have one or extra columns.

Q42. What are privileges and Grants?

Ans: Privileges are the rights to execute SQL statements – means Right to attach and join. Grants are given to the item so that gadgets can be accessed therefore. Grants may be provided by way of the proprietor or author of an object.

Q43. What is the distinction between $ORACLE_BASE and $ORACLE_HOME?

Ans: Oracle base is the main or root listing of an oracle while ORACLE_HOME is placed beneath base folder in which all oracle products reside.

Q44. What is the quickest question approach to fetch facts from the table?

Ans: Row can be fetched from table by way of using ROWID. Using ROW ID is the fastest query method to fetch data from the desk.

Q45. What is the most wide variety of triggers that may be applied to a single table?

Ans: 12 is the most wide variety of triggers that can be implemented to a single desk.

Q46. How to display row numbers with the information?

Ans: Display row numbers with the records numbers –

1    Select rownum, <fieldnames> from desk;

This question will show row numbers and the field values from the given table.

Q47. How can we view remaining document added to a table?

Ans: Last record can be introduced to a desk and this could be completed by –

1    Select * from (pick * from personnel order with the aid of rownum desc) where rownum<2;
 

Q48. What is the data type of DUAL table?

Ans: The DUAL table is a one-column table present in oracle database.  The table has a single VARCHAR2(1) column called DUMMY which has a value of ‘X’.

Q49. What is difference between Cartesian Join and Cross Join?

Ans: There are no differences between the join. Cartesian and Cross joins are same. Cross join gives cartesian product of two tables – Rows from first table is multiplied with another table which is called cartesian product.

Cross join without where clause gives Cartesian product.

Q50. How to display employee records who gets more salary than the average salary in the department?

Ans: This can be done by this query –

1    Select * from employee where salary>(pick out avg(salary) from dept, worker where dept.Deptno = worker.Deptno;