Using subqueries, PL-SQL Programming

Using Subqueries

A subquery is a query (typically enclosed by parentheses) that appears within another SQL data manipulation statement. If evaluated, the subquery gives a value or set of values to the statement. Frequently, the subqueries are used in the WHERE clause. For illustration, the following query returns employees not located in the Chicago:

DECLARE

CURSOR c1 IS SELECT empno, ename FROM emp

WHERE deptno IN (SELECT deptno FROM dept

WHERE loc <> 'CHICAGO');

Using the subquery in the FROM clause, the query below returns the number and name of each department with five or more employees:

DECLARE

CURSOR c1 IS SELECT t1.deptno, dname, "STAFF"

FROM dept t1, (SELECT deptno, COUNT(*) "STAFF"

FROM emp GROUP BY deptno) t2

WHERE t1.deptno = t2.deptno AND "STAFF" >= 5;

While a subquery evaluate only once per table, a correlated subquery evaluate once per row. Consider the query below that returns the name and salary of each employee whose salaries exceed the departmental average. For each row in the emp table, the associated subquery calculate the average salary for that row's department. The row is returned if that row's salaries exceed the average.

DECLARE

CURSOR c1 IS SELECT deptno, ename, sal FROM emp t

WHERE sal > (SELECT AVG(sal) FROM emp WHERE t.deptno = deptno)

ORDER BY deptno;

Posted Date: 10/4/2012 3:55:24 AM | Location : United States







Related Discussions:- Using subqueries, Assignment Help, Ask Question on Using subqueries, Get Answer, Expert's Help, Using subqueries Discussions

Write discussion on Using subqueries
Your posts are moderated
Related Questions
Cursor Variables Similar to a cursor, cursor variable points to the current row in the result set of a multi-row query. But, dissimilar a cursor, a cursor variable can be opene

I would like to have a custom MS Access database designed and coded that would help me schedule my customer's orders and that would help me track my employees production output and

Predefined Exceptions The internal exception is raised implicitly whenever your PL/SQL program exceeds a system-dependent limit or violates an Oracle rule. Each & every Oracle

Declaring Cursor Variables Once a REF CURSOR type is define by you, and then you can declare the cursor variables of that type in any PL/SQL block or subprogram. In the exampl

OPEN-FOR Statement The OPEN-FOR statements execute the multi-row query related with a cursor variable. It also allocates the resources used by the Oracle to process the query a

Question: (a) The objective of query optimization is to choose the most efficient strategy for implementing a given relational query, thereby improving the system performance. On

Data Types in SQL - XML, Array, Row BINARY LARGE OBJECT for arbitrarily large bit strings. XML for XML documents and fragments. ARRAY types for arrays.

At times, Brewbean's has changed the id number for existing products. In the past, they have had to add a new product row with the new id to the BB_PRODUCT table, modify all the co

Using PRIOR and NEXT The PRIOR(n) returns the index number that precede index n in a collection. The NEXT(n) returns the index number which succeed the index n. If n has no pr

Create a Oracle procedure to produce vertical output format when selecting rows from a database table.