June 05, 2019

Top 50 Advance Java Interview Questions and Answers


Ques: 1. What do you understand by Private, Protected and Public?
 

Answer: 

Private, Protected and Public are the accessibility modifiers. Private is the most restrictive, while public is the least restrictive. There is no real difference between protected and the default type (also known as package protected) within the context of the same package, however the protected keyword allows visibility to a derived class in a different package. 

AngularJS Interview Questions and Answers


Ques: 2. What is a java package? How is it used?

Answer: 

A Java package is a naming context for classes and interfaces.

  • A package is used to create a separate name space for groups of classes and interfaces.
  • Packages are used to organize related classes and interfaces into a single API unit and to control accessibility to these classes and interfaces.

AJAX interview Questions and Answers


Ques: 3. What do you understand by EAR, JAR and WAR File?


Answer:
Enterprise Archives (EAR): An EAR file contains all the components that make up a J2EE application.

Java Archives (JAR): A JAR file encapsulates one or more Java classes, a manifest, and a descriptor. JAR files are the lowest level of archive. JAR files are used in J2EE for packaging EJBs and client-side Java Applications.

Web Archives (WAR): WAR files are like JAR files, except that they are specifically for web applications made from Servlets, JSPs, and supporting classes.

JavaScript Interview Questions and Answers


Ques: 4. How Java Source Code files are named?

Answer: 

A source code file may contain at most one public class or interface. A Java source code file takes the name of a public class or interface that is defined within the file. Source code files use the .java extension.
If a public class or interface is defined within a source code file, then the source code file must take the name of the public class or interface. If no public class or interface is defined within a source code file, then the file must take on a name that is different than its classes and interfaces.


Node.js Interview Questions and Answers


Ques: 5. What do you understand by Numeric Promotion?

Answer: 

In numerical promotion, byte, char, and short values are converted to int values. It is the conversion of a smaller numeric type to a larger numeric type, so that integer and floating-point operations may take place. if required, the int values are also converted to long values. The long and float values can be converted to double values.

JQuery Interview Questions and Answers



Ques: 6. What is the difference between Time Slicing and Preemptive Scheduling?

Answer: 

Under time slicing in java, a task executes for a predefined slice of time and then reenters the pool of ready tasks.
But under preemptive scheduling, the highest priority task executes until it enters the waiting or dead states, or a higher priority task comes into existence.

The scheduler then determines which task should execute next, based on priority and other factors.

 

Ques: 7. What do you mean by a Task's Priority? How is it used in Scheduling?

Answer: 

A task's priority is an integer value that identifies the relative order in which it should be executed with respect to other tasks. The scheduler attempts to schedule higher priority tasks before lower priority tasks.


Ques: 8. What technologies are included in J2EE?

Answer: 

The various technologies in J2EE are:

  • Enterprise JavaBeansTM (EJBsTM).
  • JavaServer PagesTM (JSPsTM).
  • Java Servlets.
  • The Java Naming and Directory InterfaceTM (JNDITM).
  • The Java Transaction API (JTA).
  • CORBA.
  • The JDBCTM data access API.

 

Ques: 9. What is the main purpose of the Wait(), Notify() and Notifyall() methods in java?

Answer: 

These methods are used to provide an efficient way for threads to wait for a shared resource. When a thread in java executes an object's wait() method, it enters the waiting state. It only enters the ready state after another thread invokes the object's notify() or notifyAll() methods.

 

Ques: 10. What happens when you invoke a Thread's interrupt method while it is Sleeping or Waiting?

Answer: 

When a task's interrupt() method is executed, the task enters the ready state. The next time the task enters the running state, an InterruptedException is thrown.

 

Ques: 11. What are the various restrictions placed on method overriding?

Answer:

  • The overriding method may not limit the access of the method it overrides.
  • Overridden methods must have the same name, argument list, and return type.
  • The overriding method may not throw any exceptions that may not be thrown by the overridden method.


Ques: 12. What do you understand by Java Swing?

Answer: 

Swing is basically a type of Toolkit which is GUI toolkit for Java. It is one part of the Java Foundation Classes (JFC). Swing includes graphical user interface (GUI) widgets such as text boxes, buttons, split-panes, and tables.
Swing supports pluggable look and feel, not by using the native platform's facilities, but by roughly emulating them. This means you can get any supported look and feel on any platform.
Swing widgets provide more sophisticated GUI components than the earlier Abstract Window Toolkit. Since they are written in pure Java, they run the same on all platforms. The advantage is uniform behavior on all platforms. The disadvantage of lightweight components is slower execution.


Ques: 13. What are the differences between Swing and AWT?

Answer: 

There are many differences between Swing and AWT:

  • AWT is heavy-weight components, but Swing is light-weight components.
  • AWT is OS dependent because it uses native components, But Swing components are OS independent.
  • Can change the look and feel in Swing which is not possible in AWT.
  • Swing takes less memory compared to AWT.
  • For drawing AWT uses screen rendering where Swing uses double buffering.

 

Ques: 14. What are the Types of Scaling?

Answer: 

In java, there are two types of scaling: Horizontal Scaling and Vertical Scaling.

Horizontal Scaling: When Clones of an application server are defined on multiple physical m/c, it is called Horizontal Scaling. The objective is to use more than one less powerful m/c more efficiently.

Vertical Scaling: When multiple server clones of an application server are defined on the same physical m/c, it is called Vertical Scaling. The objective is to use the processing power of that m/c more efficiently.

 

Ques: 15. What are the types of Class Loaders in java?

Answer: 

In java, there are three types of class loader as bootstarp class loader, extension class loader and system class loader.

Bootstrap Class Loader: Bootstrap class loader loads java’s core classes like java.lang, java.util etc. These are classes that are part of java runtime environment. Bootstrap class loader is native implementation and so they may differ across different JVMs.

 Extensions Class Loader: JAVA_HOME/jre/lib/ext contains jar packages that are extensions of standard core java classes. Extensions class loader loads classes from this ext folder. Using the system environment property java.ext.dirs you can add ‘ext’ folders and jar files to be loaded using extensions class loader.

System Class Loader: Java classes that are available in the java classpath are loaded using System class loader.



Ques: 16. What are differences between the boolean & operator and the && operator?

Answer: 

If an expression involving the Boolean & operator is evaluated, both operands are evaluated. Then the & operator is applied to the operand. When an expression involving the && operator is evaluated, the first operand is evaluated.
If the first operand returns a value of true, then the second operand is evaluated. The && operator is then applied to the first and second operands. If the first operand evaluates to false, the evaluation of the second operand is skipped.

 

Ques: 17. What do you mean by Casting?

Answer: 

In java, there are two types of casting:

1). casting between primitive numeric types: Casting between numeric types is used to convert larger values, such as double values, to smaller values, such as byte values.

2). casting between object references: Casting between object references is used to refer to an object by a compatible class, interface, or array type reference.


Ques: 18. How does a Try statement determine which Catch clause should be used to handle an exception?

Answer: 

When an exception is thrown within the body of a try statement, the catch clauses of the try statement are examined in the order in which they appear. The first catch clause that is capable of handling the exception is executed. The remaining catch clauses are ignored.

 

Ques: 19. What will happens if Remove( ) is never invoked on a session bean?

Answer: 

In case of stateful session bean, the bean may be kept in cache till either the session times out, in which case the bean is removed or when there is a requirement for memory in which case the data is cached and the bean is sent to free pool.
In case of a stateless session bean it may not matter if we call or not as in both cases nothing is done. The number of beans in cache is managed by the container.


Ques: 20. What Is Java Reflection Api?

Answer: 

Reflection is one of the most powerful api which help to work with classes, methods and variables dynamically. Basically, it inspects the class attributes at runtime. Also, we can say it provides a metadata about the class.

 

Ques: 21. What is the Collection API in java?

Answer: 

The Collection API is a set of classes and interfaces that support operation on collections of objects. These classes and interfaces are more flexible, more powerful, and more regular than the vectors, arrays, and hashtables if effectively replaces.

Example of classes: HashSet, HashMap, ArrayList, LinkedList, TreeSet and TreeMap.

Example of interfaces: Collection, Set, List and Map.



Ques: 22. What is the difference between Session and Entity Beans?

Answer: 

An entity bean represents persistent global data from the database; a session bean represents transient user-specific data that will die when the user disconnects (ends his session). Generally, the session beans implement business methods that call entity beans .


Ques: 23. What do you understand by Abstract Schema in java?

Answer: 

In java, you can specify the name of the Abstract schema name in the deployment descriptor. The queries written in EJB QL for the finder methods references this name. It is a part of an entity bean’s deployment descriptor which defines the bean’s persistent fields and their relationship. Abstract schema is specified for entity beans with container managed persistence. The information provided in this Abstract Schema is used by the container for persistence management and relationship management.


Ques: 24. What is the use of the Finally block? Is Finally block in Java guaranteed to be called? When will it is not called?

Answer:

The code in finally block will execute even if an exception is occurred. Finally, is the block of code that executes always. Finally block is NOT called in following conditions:

  • If the JVM exits while the try or catch code is being executed, then the finally block may not execute. This may happen due to System.exit() call.
  • if the thread executing the try or catch code is interrupted or killed, the finally block may not execute even though the application as a whole continues.
  • If an exception is thrown in finally block and not handled, then remaining code in finally block may not be executed.


Ques: 25. What do you mean by Local Interface. How values will be passed to them?

Answer: 

An EJB can use local client view only if it is really guaranteed that other enterprise beans or clients will only address the bean within a single JVM. With local client view, you can do pass-by-reference, which means your bean, as well as the client, will work directly with one copy of the data. Any changes made by the bean will be seen by the client and vice versa. Pass-by-reference eliminates time/system expenses for copying data variables, which provides a performance advantage.


Ques: 26. What is the main difference between Serializable and Externalizable Interfaces?

Answer: 

Both interfaces are used for implementing serialization. But, the basic difference is Serializable interface does not have any method (it’s a marker interface) and Externalizable interface having 2 methods such as readExternal() and writeExternal(). Serializable interface is the super interface for Externalizable interface.



Ques: 27. What is the relation between Local Interfaces and Container-managed relationships?

Answer: 

To be the target of a container-managed relationship, an entity bean with container-managed persistence must provide a local interface. Entity beans that have container-managed relationships with other entity beans, must be accessed in the same local scope as those related beans, and therefore typically provide a local client view.

 

Ques: 28. What are the differences between Creating String as New() and Literal?

Answer: 

When we create string with new() Operator, it’s created in heap and not added into string pool while String created using literal are created in String pool itself which exists in PermGen area of heap.
String s = new String(“Test”);
It does not put the object in String pool, we need to call String.intern() method which is used to put  them into String pool explicitly. its only when you create String object as String literal e.g. String s = “Test” Java automatically put that into String pool.


Ques: 29. What is the main difference between Final, Finally And Finalize?

Answer: 
Final is used to apply restrictions on class, method and variable. Final class can’t be inherited, final method can’t be overridden, and final variable value can’t be changed.

Finally is used to place important code, it will be executed whether exception is handled or not.
Finalize is used to perform clean up processing just before object is garbage collected.

 

Ques: 30. How can an Object's Finalize() Method be invoked while it is reachable?

Answer: 

An object's finalize() method cannot be invoked by the garbage collector while the object is still reachable. However, an object's finalize() method may be invoked by other objects.

 

Ques: 31. What do you mean by an Object’s Lock? Which Object Have Locks?

Answer: 

A thread may execute a synchronized method of an object only after it has acquired the object’s lock. An object’s lock is a mechanism that is used by multiple threads to obtain synchronized access to the object. All objects and classes have locks. A class’s lock is acquired on the class’s Class object.


Ques: 32. What are the uses of Observer and Observable?

Answer: 

Objects that subclass the Observable class maintain a list of observers. When an Observable object is updated it invokes the update() method of each of its observers to notify the observers that it has changed state. The Observer interface is implemented by objects that observe Observable objects.

 

Ques: 33. How ConcurrentHashMap Works?

Answer:

Basically, ConcurrentHashMap locks each of the box (by default 16) which can be locked independently and thread safe for operation. The basic design of ConcurrentHashMap is to handling threading. And it does not expose the internal lock process.

 

Ques: 34. What are Transient Variables in java?

Answer: 

In Java, Transient variables can’t be serialized. For example, if a variable is declared as transient in a Serializable class and the class is written to an ObjectStream, the value of the variable can’t be written to the stream instead when the class is retrieved from the ObjectStream the value of the variable becomes null.


Ques: 35. What do you understand by JFC?

Answer: 

JFC stands for Java Foundation Classes. The Java Foundation Classes (JFC) are a set of Java class libraries provided as part of Java 2 Platform, Standard Edition (J2SE) to support building graphics user interface (GUI) and graphics functionality for client applications that will run on popular platforms such as Microsoft Windows, Linux, and Mac OSX.


Ques: 36. What is the main difference between Find and Select methods In EJB?

Answer: 

A select method can return a persistent field (or a collection thereof) of a related entity bean. A finder method can return only a local or remote interface (or a collection of interfaces).

A select method is defined in the entity bean class. For bean-managed persistence, a finder method is defined in the entity bean class, but for container-managed persistence it is not.
Because it is not exposed in any of the local or remote interfaces, a select method cannot be invoked by a client. It can be invoked only by the methods implemented within the entity bean class. A select method is usually invoked by either a business or a home method.


Ques: 37. If some new data has entered in the database, how can a servlet refresh automatically?

Answer: 

It depends on the scenario, how you can handle them. You need to handle this in dao layer, when doing insert operation, you can call a utility method which will load the context ServletContextListener. Because, servlets are basically used for handling request and give the response.


Ques: 38. What do you mean by In-memory replication?

Answer: 

The process by which the contents in the memory of one physical m/c are replicated in all the m/c in the cluster is called in-memory replication.


Ques: 39. What do you understand by synchronization? Why is it so Important?

Answer: 

In respect of multithreading, synchronization is the capability to control the access of multiple threads to shared resources. In the absence of synchronization, it is possible for one thread to modify a shared object while another thread is in the process of using or updating that object's value. This often leads to significant errors.

 

Ques: 40. How many Bits are used to represent Unicode, ASCII, UTF-16 and UTF-8 characters?

Answer: 

Although the ASCII character set uses only 7 bits, it is usually represented as 8 bits. Unicode requires 16 bits and ASCII require 7 bits. UTF-8 represents characters using 8, 16, and 18 bits patterns. UTF-16 uses 16-bit and larger bit patterns.



Ques: 41. What is a Clone in java?

Answer: 

In java, the copies of a server group are called Clones. But unlike a Server Group Clones are associated with a node and are real server process running in that node.


Ques: 42. How does the Garbage Collection guarantee that a program will not run out of memory?

Answer: 

In java, garbage collection does not guarantee that a program will not run out of memory. It is possible for programs to use up memory resources faster than they are garbage collected. It is also possible for programs to create objects that are not subject to garbage collection.


Ques: 43. What do you mean by AWT?

Answer: 

AWT is stands for Abstract Window Toolkit. AWT enables programmers to develop Java applications with GUI components, such as windows, and buttons. The Java Virtual Machine (JVM) is responsible for translating the AWT calls into the appropriate calls to the host operating system.


Ques: 44. What Advantage do Java's Layout Managers provide over traditional Windowing Systems?

Answer: 

Java uses layout managers to lay out components in a consistent manner across all windowing platforms. Since Java's layout managers aren't tied to absolute sizing and positioning, they are able to accommodate platform-specific differences among windowing systems.


Ques: 45. Is Decorator an EJB design pattern?

Answer: 

Decorator design pattern is not an EJB design pattern. It is the one which exhibits very low-level runtime polymorphism, for the specific and single object (Instance of the class). But not for at least for a class. It is the stuff to add specific functionality to a single & pointed object and leaves others like it unmodified. It is having close similarities like AspectJ stuff, but not with EJB stuff.


Ques: 46. What is the main purpose of the Enableevents() method?

Answer: 

The enableEvents() method is used to enable an event for a particular object. Normally, an event is enabled when a listener is added to an object for a particular event.
The enableEvents() method is used by objects that handle events by overriding their event-dispatch methods.


Ques: 47. What is the main difference between Paint() and PaintComponent()?

Answer: 

The main point is that the paint() method invokes three methods in the following order:

  • PaintComponent()
  • PaintBorder()
  • paintChildren()

As a general rule, in Swing, we should be overriding the paintComponent method unless we know what we are doing paintComponent() paints only component (panel) but paint() paints component and all its children.


Ques: 48. What do you understand by double buffering ?

Answer: 

Double buffering is the process of using two buffers rather than one to temporarily hold data being moved to and from an I/O device. Double buffering increases data transfer speed because one buffer can be filled while the other is being emptied.


Ques: 49. What is the main difference between the File and RandomAccessFile Classes?

Answer: 

The File class encapsulates the files and directories of the local file system.
The RandomAccessFile class provides the methods needed to directly access data contained in any part of a file.

 

Ques: 50. Can you Name some of the Layoutmanagers In Java?

Answer: 

Some of the LayoutManagers in java are:

  • Flow Layout Manager
  • Grid Layout Manager
  • Box Layout Manager
  • Border Layout Manager
  • Card Layout Manager
  • GridBag Layout Manager 



May 25, 2019

Top 20 Oracle SQL Interview Questions & Answers



Ques: 1. What are the differences between the DELETE, TRUNCATE and DROP commands? 

Ans: The differences between DELETE, TRUNCATE and drop commands are as:
  • DELETE is a DML command, but TRUNCATE and DROP are DDL commands.
  • DELETE removes the records based on an optional WHERE clause, but TRUNCATE command cannot have option to filter the records to be removed
  • While using DELETE, it is possible to roll back a transaction, but with TRUNCATE and DROP transaction, roll back is not possible.
  • DELETE does not reset the identity of the table. TRUNCATE resets the identity of table i.e. the auto-increment keys are reset to 1.

  • Triggers will get fired in DELETE, but no triggers will fired in TRUNCATE and DROP statements.

  • Can use DELETE commands, whenever you want to delete specific records. Use TRUNCATE when you want table to be cleaned again and use DROP when you don’t need that table anymore.
  • TRUNCATE is faster than DELETE and uses a lesser amount of system.
  • TRUNCATE cannot be used on a table referenced by a FOREIGN KEY constraint.

  • DROP command not only removes the table from the database. Its structures, indexes, privileges, and constraints also get removed.
Syntax for DELETE, TRUNCATE and DROP commands:

DELETE FROM table_name WHERE column_name = column_value;
DELETE FROM table_name;

TRUNCATE TABLE table_name;

DROP TABLE table_name;




Ques: 2. What are the different types of statements in SQL?

Ans: Below are the different types of statements in SQL.

a). Data Definition Language (DDL):

DDL are used to define and alter/modify the structure of table or database or schema. These commands manage the design and storage of DB objects. 

CREATE:  To create DB objects in the database.
ALTER: To alter the existing structure of DB objects.
DROP: To delete existing DB objects from database.
TRUNCATE: To remove all records from a table. It frees the space allocated to those records.
RENAME: To rename database objects

b). Data Manipulation Language (DML):

These statements are used to manipulate or manage the table’s records. The basic operations carried out on the tabular data like selection, insertion new records, delete, and update the existing records:

SELECT: To select specific data from a database.
INSERT: To insert new records into a table.
UPDATE: To update existing records.
DELETE: To delete all records from a table.
MERGE: Conditionally insert or update records in table.

c). Data Control Language (DCL):

DCL commands control the level of access that users have to the database objects. DCL statements are used to create roles, grant permission and control access to the database objects.
GRANT: To provide user access for the database.
REVOKE: To revoke/withdraw the access given to user.

D). Transaction Control Language (TCL):

TCL allows you to control and manage transactions to maintain the integrity of data in oracle.
COMMIT: To save the transaction in database.
SAVEPOINT: To identify a point in a transaction to which you can rollback at a later point in time whenever required.
ROLLBACK: It restores the database to original since the last COMMIT.


Ques: 3. What are the differences between Primary Key, Unique Key, and Foreign Key?

Ans: The main differences between Primary Key, Unique Key, and Foreign Key are as:
  • The primary key cannot have a NULL value. Unique key can have one NULL values and Foreign key can have multiple NULL values. But the Foreign key shouldn’t have a null value. Else, the system will consider it as an orphan record.
  • A DB table can have only one primary key. But a table can have more than one Unique key and Foreign keys.
  • A Foreign key does not automatically create an index, clustered or non-clustered. You must manually create an index on the foreign key.
  • By default, Primary key supports clustered index. The data in the database table are physically organised in the sequence of clustered index. By default, Unique key is a unique non-clustered index.
  • Primary key can be related to another table as a Foreign Key. Unique key is not related to another table as a Foreign Key.
  • There are advantages of having a foreign key supported with a clustered index, but you get only one per table. The advantage using a clustered index is that, on selecting the parent plus all child records, it can bring all child records next to each other.


Oracle ADF Interview Questions and Answers                                 


Ques: 4. What is the main purpose of a Subquery?

Ans: To provide the result of a secondary query to main query for processing the desired records, This is the main purpose of subquery. A Subquery is always executed first and passes its result to the main query. This data acts as a filter condition in the main query to further restrict the data to be retrieved. A Subquery also called as Nested query is a query within another SQL query and embedded within the WHERE clause. Subqueries work with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, and BETWEEN.
The important properties of a Subquery can be:
  • It can contain more than one column in SELECT clause only if the main query has multiple columns.
  • We cannot use ORDER BY in a Subquery.
  • Use GROUP BY which performs the same function as ORDER BY.
  • Always write a Subquery within a parenthesis.
  • We cannot use BETWEEN operator with a subquery but, can use within a Subquery.
  • You can nest Subqueries up to 32 levels.
Ques: 5. What are the different types of subquery?

Ans: In SQL, there are two types of subquery:

1. Correlated subquery: In a SQL database query, a correlated subquery is a subquery that uses values from the outer query in order to complete. Because a correlated subquery requires the outer query to be executed first, the correlated subquery must run once for every row in the outer query.

2. Non-Correlated Subquery: A Non-correlated subquery is a subquery in which both outer query and inner query are independent to each other.


Ques: 6. What is the difference between Union and Union ALL?

Ans: UNION and UNION ALL merges the contents of two structurally-compatible tables into a single combined table. 
  • The difference between UNION and UNION ALL set operators, is that UNION will remove duplicate records whereas UNION ALL will include duplicate records of the table.
  • The performance of UNION ALL is better than UNION as UNION requires the server to do additional work of removing duplicates. 
For performance reasons, it is recommended to use UNION ALL is the scenarios when it is certain that there will be no duplicates or cases where having duplicates is not a problem.

Ques: 7. In SQL, what is the main difference between CHAR and VARCHAR2 datatype?

Ans: Char and Varchar2, both are used for characters datatype but varchar2 is used for character strings of variable length whereas Char is used for strings of fixed length.

For example, char (20) can only store 20 characters and will not be able to store a string of any other length whereas varchar2(20) can store any data string of length up to 20, i.e. 18,15,8,2.

Ques: 8. Can you List some of the popular built-in functions, available in SQL?

Ans: A lot of built-in functions are available in SQL. Some of the popular and important built-in functions are as:
  • AVG(): Returns the average value.
  • COUNT(): Returns the number of rows.
  • REPLACE(): Replaces the given value in specified string.
  • MONTHS_BETWEEN (): Number of months between the two dates.
  • MAX(): It gives the largest value as output.
  • MIN(): It gives the smallest value as output.
  • SUM(): Outputs the Sum.
  • UPPER(): Converts a value to upper case.
  • LOWER(): Converts a value to lower case.
  • SUBSTR(): Returns a substring from the specified position and number of characters; default returns to end of string.
  • LENGTH(): Returns the length of a text field.
  • ROUND(): Round Off a numeric field to the number of decimals specified.
  • INSTR(): Returns the position of the nth occurrence of string2 in given string.
  • ADD_MONTHS (d, n): Add the number of months in given date.
  • TO_CHAR (): Return string of number converted to character string as specified by the format. 
Ques: 9. Are NULL values same as that of zero or a blank space? 

Ans: A field with a NULL value is a field with no value. NULL value represents a value which is unknown, unavailable, not assigned or not applicable. A NULL value is not at all same as that of zero or a blank space, whereas a zero is a number and blank space is a character. . A field with a NULL value is one that has been left blank during record creation.



Ques: 10. What is the key difference between ‘HAVING’ CLAUSE and ‘WHERE’ CLAUSE?

Ans: WHERE clause can be used only with SELECT statement after table name. HAVING clause is usually used in a GROUP BY clause and whenever GROUP BY is not used, HAVING behaves like a WHERE clause. WHERE clause is optional in SELECT statement, but if it used it will be applied to each row to fetch data from table. Having Clause is only used with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are a part of the GROUP BY function in a query.


Oracle RDMS Interview Questions and Answers


Ques: 11. What is Self-Join and Cross-Join?


Ans: A self-join is a join in which a table is joined with itself, especially when the table has a Foreign Key which references its own Primary Key. A Cross join produces a result set which is the number of rows in the first table multiplied by a number of rows in the second table if no WHERE clause is used along with Cross join. This kind of result is known as Cartesian Product. If suppose, where clause is used in cross join then the query will work like an Inner join.



Ques: 12. What is Collation? And what are the types of collation sensitivity?

Ans: Character data is sorted using rules that define the correct character sequence along with options for specifying case-sensitivity, character width, accent marks and Kana character types. Collation is defined as a set of rules that determine how character data can be sorted as well as compared. Different types of collation sensitivity are as follows:
Case Sensitivity: A and a and B and b.
Kana Sensitivity: Japanese Kana characters.
Width Sensitivity: Single byte character and double byte character.
Accent Sensitivity.





Ques: 13. What is difference between unique and distinct?

Ans: Functionality wise, there is no difference between Unique and distinct keywords. But the only difference is that unique is applied before insertion and retrieval. It consists of non-duplicate values. If unique constraint is given it does not take duplicate values. Distinct is used in retrieval it gives the suppressed row (for example, if two rows are same it will show single row and non-duplicate row) therefore distinct is the combination of suppressed duplicate and non-duplicate rows.

So, specify DISTINCT or UNIQUE if you want Oracle to return only one copy of each set of duplicate rows selected (these two keywords are synonymous). Duplicate rows are those with matching values for each expression in the select list.


Ques: 14. What will be the output of following Query?

Select case when NULL=NULL then ‘Joan’ Else ‘Emily’ from dual;

Ans: In SQL null value cannot not be equal to itself. So, NULL=NULL is false and the output of above query is ‘Emily’.


Ques: 15. What do you understand by “REFRESH” options of Materialized view?

Ans:
REFRESH ON COMMIT:
This option commits the data in materialized views immediately after data insertion and commits in table.  This option is known as incremental refresh option. Materialized View is not fully refreshed with this option.

REFRESH ON DEMAND:
Using this option, you can add the condition for refreshing data in materialized views. You can refresh the data using fast (incremental approach), Complete and Force options.


Ques: 16. What is the main difference between 'BETWEEN' and 'IN' condition operators?

Ans: In SQL, BETWEEN operator is used to display rows based on a range of values in a row whereas the IN operator is used to check for values contained in a specific set of values.
Example of BETWEEN:
SELECT * FROM Employees where EMPLOYEE_ID BETWEEN 100 AND 150;

Example of IN: 

SELECT * FROM Employees where EMPLOYEE_ID IN (100,110,150);


Ques: 17. What is the difference Between Substr And Instr ?

Ans: INSTR (String1,String2(n,(m)),INSTR returns the position of the mth occurrence of the string 2 instring1. The search begins from nth position of string1.SUBSTR (String1 n,m)SUBSTR returns a character string of size m in string1, starting from nth position of string1.

For example:
SELECT INSTR('Oracle SQL Developer','e',1,2) FROM DUAL;
Output: 13

SELECT SUBSTR('Oracle SQL Developer',8,3) FROM DUAL;
Output: SQL


Ques: 18. What is a View? What are its advantages and disadvantages?

Ans: View is a pre-complied SQL query which is used to select data from one or more tables. A view is like a table, but it doesn't physically store data. View is a good way to present data in a format if you use that query quite often. View can also be used to restrict users from accessing the tables directly. A View is a virtual table which contains data from one or more tables. It selects only required values thus restricting the access to table data. And it also makes complex queries a bit easier.

Advantages of using Views:
  • It enables viewing data without storing the data in an object.
  • Restrict the view of a table by hiding some of its columns.
  • Join two or more tables and display it as a single object.
  • Restrict the access of a table so that nobody can insert rows in the table without permission.
Disadvantages of Views:
  • Cannot apply DML statements on it.
  • A View becomes inactive if a table that is a part of View gets dropped.
  • It is an object and hence, it consumes memory.

Ques: 19. What is the main difference between SQL and PL/SQL?

Ans: SQL (Structured Query Language) is a non-procedural language that interacts with the database and is used for database manipulation using the Data Definition Language (DDL) and Data Manipulation Language (DML) statements. Control statements cannot be used in SQL, which is compiled and executed statement by statement at the run time (late-binding).

PL/SQL is a programming language that allows the usage of Cursor Control statements and Transaction Control statements, such as if...then...else. It integrates with SQL functions and statements to interact and manipulate with the database. Unlike SQL, PL/SQL statements are compiled and processed as a block of code into the machine-readable code, which is executed at run time (early binding); and therefore, improves the performance.


Ques: 20. What is a coalesce function?

Ans: Coalesce function is used to return the first not NULL value out of the multiple values or expressions passed to the coalesce function as parameters.

Example-
COALESCE(NULL, NULL, 5, 'Technical') will return the value 5.

COALESCE(NULL, NULL, NULL) will return NULL value as no not NULL value is encountered in the parameters list.