PL/SQL Interview Questions- Part 5

Answer:

Advantages of Cursor are as follows:

• We can perform row-by-row processing with a cursor. It helps to validate the operations of each row.
• A cursor can provide the first few rows before the assembling of the entire result set. It helps to achieve better response time.
• A single connection can have multiple cursors open simultaneously. All the cursor result sets are available at the same time to an application, which can fetch data rows from them at will.
• Cursors can be faster in contrast to a while loop but at the cost of more overhead.

Disadvantages of using Cursor are as follows:

• A cursor in SQL is a temporary work area created in system memory. It occupies memory from a system that may be accessible for other processes. Thus, the cursor occupies more temporary storage & resources.
• Each time when the row is fetched from a cursor, it results in the network round trip. It uses more network bandwidth than a SQL statement execution such as DELETE or SELECT that only makes one round trip.
• Repeated network round trips can degrade operations speed.

Answer:

A Ref Cursor or Dynamic cursor is associated with many SELECT statements at different times. It is one of the most flexible, scalable, & powerful ways to return query results from the Oracle Database to a client application.

Answer:

The commit statement helps you to end your current transactions & make all the changes permanent performed in a transaction. It also erases the savepoints into a transaction & releases the transaction locks.

Until you commit a transaction:

• You can see the changes made during a transaction by querying the modified tables, but other users cannot see those changes. Once you commit a transaction, the changes are visible to other users’ statements that execute after the commit.
• You can undo or roll back any changes made during a transaction with a ROLLBACK statement.

Answer:

An exception refers to an identifier in PL/SQL block raised during the block execution that terminates its main body of actions. A block always terminates when PL/SQL raises the exception; however, you can perform an exception handler to perform the final actions.

There are mainly two types of exceptions:

Implicitly raised:

• Predefined Oracle server
• Non-predefined Oracle server

Explicitly raised:

• User defined

Answer:

The pragma_exception_init in PL/SQL notifies the compiler to associate an exception name with the Oracle error number. It enables you to refer to an internal exception by the name & write a specific handler for it. For example, PRAGMA EXCEPTION_INIT (oracle error number, exception name).

Answer:

When the same procedure name is repeated with parameters of different datatypes in different positions, varying in the number of parameters, it is called overloading of procedures.

Answer:

The main perks of using stored procedure are:

• Better Performance: The procedure calls are efficient & quick as stored procedures are compiled once & stored in the executable form. Thus, the response is quick. The executable code is cached automatically; hence it lowers the memory requirements.
• Higher Productivity: As the same piece of code is used repeatedly, it results in higher productivity.
• Ease of Use: To create a stored procedure, one can utilize any IDE or Integrated Development Environment. Thereby, it can be deployed on any tier of the network architecture.
• Scalability: Stored procedures increase the scalability by isolating app processing on a server.
• Maintainability: It is much easier to maintain a procedure on a server than maintaining copies on several client machines, it is because scripts are in one location.
• Security: Access to Oracle data can be restricted by enabling users to manipulate data only through a stored procedure that executes with their definer’s privileges.

Answer:

Alias refers to a user-defined alternative name given to a table or column. An alias heading appears in the upper case by the default column. To make an Alias case sensitive, enclose it in double quotation marks (“ “). “AS” Keyword before Alias name makes a SELECT clause easier to read.

Answer:

 Functions imply the methods used to perform the data operations. SQL has several in-built functions used to perform mathematical calculations, string concatenations, etc.

• Aggregate SQL Functions- In SQL, the aggregate functions perform the calculation on a group of values and return a single value. Below are some commonly used Aggregate Functions:

  Function	Description
  SUM()	It is used to return the sum of a group of values.
  COUNT()	It returns the number of rows based on a condition or without the condition.
  AVG()	It is used to calculate the average value of the numeric column.
  MIN()	It returns a minimum value of the column.
  MAX()	It returns a maximum value of the column.
  FIRST()	It is used to return the first value of a column.
  LAST()	It returns the last value of a column.

• Scalar SQL Functions- In SQL, Scalar Function is used to return a single value from a given input value.  Here is a list of some most commonly used Aggregate Functions:

  Function	Description
  LCASE()	It is used to convert the string column values to a lowercase.
  UCASE()	It is used to convert the string column values to uppercase.
  LEN()	It returns the length of text values in a column.
  MID()	It extracts the substrings in SQL from the column values with the string data type.
  ROUND()	It rounds off the numeric value to the nearest integer.
  NOW()	It is used to return the current system time & date.
  FORMAT()	It is used to format how a field should be displayed.

Answer:

• Character Functions: The character function accepts the character input and/or returns both number & character values as output. SQL provides various character data types, including VARCHAR, LONG RAW, CHAR, LONG, RAW, & VARCHAR2.
• Number Functions: The number function accepts the numeric input & returns numeric values. It includes MOD, TRUNC, & ROUND.
• Date Functions: The data function operates on values of the Date data type. It returns a value of DATE data type except for the MONTHS_BETWEEN Function, which returns a number. Date Functions are ADD_MONTHS, MONTHS_BETWEEN, LAST_DAY, NEXT_DAY, ROUND, & TRUNC.

Answer:

A Dual Table contains a row with the value X & a column dummy. It is useful to return a value only once. The value can be a pseudocolumn, constant, or expression not derived from the table with the user data.

Answer:

Answer:

A subquery refers to a SELECT statement embedded in another SELECT statement’s clause. It can be placed in the WHERE HAVING & FROM clause.

Here are some guidelines for using the subqueries:

• Enclose subqueries within the parenthesis
• Set subqueries on the right side of the comparison condition.
• Use a single-row operator with the single-row subqueries & multiple-row operators with the multiple-row subqueries.

Following are the types of subqueries:

• Single-Row Subquery: These queries only return a row from the inner select statement. Its operators are: =, >, >=, <, <=, <>
• Multiple-Row Subquery: These queries return multiple rows from the inner Select statement. The Multi-Row Subquery is also multiple-column subqueries that return multiple columns from the inner select statement. Operators include: IN, ANY, ALL.

Answer:

Answer:

As its name suggests, the MERGE statement merges data from a source result set to a target table on a condition that you specify if the data from the source exists in the target already or not.

Answer:

Below are some differences between the VERIFY and FEEDBACK commands:

• VERIFY Command: You can use the VERIFY command to confirm the changes in a SQL statement having new & old values defined with Set Verify On/OFF.
• FEEDBACK Command: It represents the no. of records returned by a query.

Answer:

Types of JOIN

• Equi Join– It is a join condition containing the equality operator. The Equi Join is represented by the (=) sign. It retrieves information using the equality condition.
• Non-Equi Join– It is an inner join statement that uses an unequal operation like <>, >, <, BETWEEN, != to match rows from the different tables.
• Self Join– In this Join, a table is joined with itself.
• Cross Join- Also known as Cartesian product or Cartesian Join Cartesian product. It is a join of every row of a table to every row of another table.
• Inner Join– It returns tables’ rows that satisfy the join condition.
• Outer Join– It is similar to the Equi Join, but Oracle also returns a non-matched row from the table.
• Left Outer Join– It displays all the matching records of both tables & records in the left side table of the Join.
• Right Outer Join– It displays all the matching records of both tables & records in the right-side table of join.
• Full Outer Join-It returns all rows from both left & right-side tables of the join, extended with null if they don’t meet the join condition.
• Natural Join– It is a join that compares common columns of both the tables with each other.
• Anti Join– It returns rows from the first table where there are no matches in the second table. It is only available when performing a NOT IN subquery.
• Semi Join– It is a join where the EXISTS clause is used with the subquery. It is called a semi-join because even if the duplicate rows are returned in a subquery, only a set of matching values in an outer query is returned.

Answer:

Locking is important to securely & successfully process SQL Server transactions. It is designed to enable SQL Server to work seamlessly in multi-user environments. Locking is effective in managing transaction concurrency. Below are the types of locks:

• Exclusive lock (X): It ensures that a row or page will be reserved exclusively for a transaction that imposes the exclusive lock until the transaction holds that lock. An exclusive lock is imposed when it wants to modify a row or page data. It can only be imposed if no other exclusive or shared lock is imposed on the target already. It means that only one exclusive lock can be imposed on a row or page. Once imposed, no other locks can be imposed on that locked resource.
• Shared lock (S): When imposed, the shared lock reserves a row or page accessible only for reading; thus, all the other transactions will be prevented from modifying the locked record until the lock is active. A shared lock can be imposed by various transactions simultaneously over the same row or page so that several transactions can share data reading. The reading process itself will not affect the actual row or page data. Also, a shared lock will enable you to write operations, but no DDL changes will be allowed.
• Update lock (U): It is similar to an exclusive lock but designed to be more flexible. An update lock can be imposed on a record that has a shared lock already. It imposes another shared lock on a target row. Once the transaction which holds an update lock is ready to change data, the update lock gets transformed to an exclusive lock. The update lock is asymmetrical in regards to the shared locks. It can be imposed on a record with a shared lock, but the shared lock cannot be imposed on a record that has an update lock already.
• Intent locks (I): A transaction uses the intent lock to inform another transaction about its intention to acquire the lock. The main purpose is to ensure seamless data execution by preventing another transaction from acquiring the lock in the hierarchy object.

Answer:

A DEFAULT option in SQL helps you provide a default value to a column. The default value is added to all the new records if they don’t have any specified value. You can set the default value for table fields assigned to the Number, Memo, Date/Time, Text, Yes/No, Currency, & Hyperlink data types.

Answer:

Below are the main differences between Delete & Truncate: