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
Parameter and Keyword Description: package_name: This construct identifies the package. AUTHID Clause: This determine whether all the packaged subprograms impleme

Primary Key - SQL A PRIMARY KEY specification carries an implicit NOT NULL constraint on each column of the specified key. When more than one key constraint is required, the k

query to Find the account numbers of all customers whose balance is more than 10,000 $

Literals A literal is an explicit numeric, string, character, or Boolean value not represented by an identifier. Numeric literal 147 and the Boolean literal FALSE are some of

Avoid the NOT NULL Constraint In the PL/SQL, using the NOT NULL constraint incur a performance cost. Consider the illustration as shown below: PROCEDURE calc_m IS m NUMB

Map and Order Methods: The values of the scalar datatype like CHAR or REAL have a predefined order that allows them to be compared. While, the instances of an object type has

Overloading The PL/SQL overloads the subprogram names. That is, you can use similar name for few different subprograms as long as their formal parameters differ in the number

Parameter and Keyword Description: cursor_variable_name: This identifies a cursor variable or the parameter formerly declared within the present scope. host_cursor_va

EXECUTE Privilege To call an invoker-rights routine straightforwardly, the users should have the EXECUTE privilege on that routine. By yielding the privilege, you permit a user

Table Comparison - SQL The following definitions for relation comparisons: Let r1 and r2 be relations having the same heading. Then: r1 ⊆ r2 is true if every tuple of r1