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
Initializing and Referencing Collections Until you initialize a collection, a nested table or varray is automatically null (i.e. the collection itself is null, not its elements)

Using COUNT The COUNT returns the number of elements that a collection presently contains. For instance, when a varray projects contains 15 elements, then the following IF con

i NEED THE QURIES TO SOME OF THE PROBLEMS USING plsql

Magento change address format depending on store Project Description: What I need is that depending on the store in which the customer bought the address should change the fo

Creating a Table Syantax: CREATE TABLE ENROLMENT (StudentId SID, Name   VARCHAR (30) NOT NULL, CourseId CID, PRIMARY KEY (StudentId, CourseId)) ; Explan

Authorisations - Privileges As relational theory is silent on the issue of authorisation, it offers nothing with which SQL's vast edifice in support of what it calls privilege

Example of Tables within a Table - SQL Example: Obtaining C_ER from COURSE and EXAM_MARK SELECT CourseId, CAST (TABLE (SELECT DISTINCT StudentId, Mark FROM EXAM_MARK AS EM

BEGIN Parameter Description in pl sql: BEGIN: This keyword signals the beginning of the executable section of a PL/SQL block, that contains executable statements. The execut

Aggregate Operators SQL Supports all of the aggregate operators mentioned in the theory book and many more besides. The syntax, however, involves an unusual trick that SQL cal

Tautologies: Above given table allows us to read the truth of the connectives in the next manner. Just expect we are looking at row three. It means this says that, if there P