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
Example of NOT EXISTS in SQL Example: Use of NOT EXISTS CREATE ASSERTION Must_be_enrolled_to_take_exam_alternative1 CHECK ( NOT EXISTS (SELECT StudentId, CourseId

EXIT The EXIT statement forces a loop to done unconditionally. Whenever an EXIT statement is encountered, the loop is done immediately and controls the passes to the next statem

Project Description: I want to write some SQL statements. The things I need are between pages 5-7. The only problem is that i want it till tomorrow. Skills required is SQL

Using research notes and Oracle documentation plan and execute an upgrade of an installation of Oracle 10g to Oracle 11g release 1. To do this you must show in screen shots and wri

Example of DELETE - SQL As with UPDATE, a FOR PORTION OF clause can be specified if the target table has a defined period name, as illustrated in Example. Example: Deleting

Project Description: We organize an online system called ACPAS we have created a project called EVO that can be use by our customers to integrate their web sites with the Acpas

Cursor Variables:   To execute the multi-row query, the Oracle opens an unnamed work region that stores the processing information. You can use an explicit cursor that names

Majority of Differences among 9i, 10G, 11G :- These are some combine feature which has differences among others. Automatic Workload Repository (AWR) Drop database' s

Comparison Operators Usually, you use the comparison operators in the WHERE clause of a data manipulation statement to form the predicates, that compare one expression to anot

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