Database-MySQL [3i Infotech Placement]: Sample Questions 58 - 59 of 162

Glide to success with Doorsteptutor material for competitive exams : get questions, notes, tests, video lectures and more- for all subjects of your exam.

Question 58


Describe in Detail


What do you mean by Correlated subquery?


  • 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 59


Describe in Detail


What are cursors give different types of cursors.


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.

Developed by: