3i Infotech Papers: Sample Questions 410 - 411 of 1245

Examrace Placement Series prepares you for the toughest placement exams to top companies.

Question number: 410

» Database » MySQL

Essay Question▾

Describe in Detail

What do you mean by Correlated subquery?

Explanation

  • A correlated subquery is a SQL query that depends on values executed by an outer query in order to complete.

  • Because a related subquery requires the outer query is executed first, the correlated subquery must run once for every row in the outer query.

  • This causes correlated subqueries to be less efficient than other subqueries.

  • Ex.

The objective is to find all employees whose salary is above average for their department.

SELECT employee_number, name

FROM employees AS emp

WHERE salary > (

SELECT AVG (salary)

FROM employees

WHERE department = emp. department);

Question number: 411

» Database » MySQL

Essay Question▾

Describe in Detail

What are cursors give different types of cursors.

Explanation

Two types of cursor

  • Implicit cursor

  • Explicit cursor

Implicit Cursor:

  • Whenever an SQL statement is executed, Implicit cursors are automatically created by Oracle, when there is no explicit cursor for the statement.

  • Programmers cannot control the implicit cursors and the information in it.

  • An implicit cursor is one created “automatically” by Oracle when executibng a query.

  • Takes more code, but gives more control - for example, you can just open-fetch-close if you only want the first record and don’t care if there are others.

  • Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted.

  • For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.

Explicit Cursor:

  • Programmer-defined cursors for gaining more control over the context area.

  • Should be defined in the declaration section of the PL/SQL Block.

  • Created on a SELECT Statement which returns more than one row.

  • Created & managed by the programmer.

  • Needs every time explicit open, fetch & close.

  • Used when sql statement returns more than one row.