Using subqueries, PL-SQL Programming

Assignment Help:

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;


Related Discussions:- Using subqueries

Loop labels- iterative control, Loop Labels Like the PL/SQL blocks, loop...

Loop Labels Like the PL/SQL blocks, loops can also be labeled. The label, an undeclared identifier enclosed by double angle brackets, should appear at the beginning of the LOOP

Parameter and keyword description - update statement, Parameter and Keyword...

Parameter and Keyword Description:   table_reference: This keyword identifies the table or view that should be accessible when you execute the UPDATE statement, and for wh

Calculate the total shopper spending, Many of the reports generated from th...

Many of the reports generated from the system calculate the total dollars in purchases for a shopper. Complete the following steps to create a function named TOT_PURCH_SF that acce

Procedure, 1. Create a procedure called TAX_COST_SP to accomplish the tax c...

1. Create a procedure called TAX_COST_SP to accomplish the tax calculation task. Keep in mind that the state and subtotal values are inputs into the procedure and the procedure is

Build a purchases report that matches the general ledger, Great Plains (Mic...

Great Plains (Microsoft Dynamics) Purchases Report Project Description: I want to build a purchases report that matches the General Ledger. presently, when I join the PM20

Assignment statement in pl sql, Assignment Statement: The assignment s...

Assignment Statement: The assignment statement sets the present value of the variable, parameter, field, or element. The statement consists of an assignment target followed by

Write a program, to write a heap sort program usin pl-sql

to write a heap sort program usin pl-sql

Updating tables in sql, Updating Tables in SQL The topic of updating b...

Updating Tables in SQL The topic of updating by describing the assignment operator, ":=" in Tutorial D. SQL uses a different syntax for assignment, using the key word SET and

Parameter aliasing, Parameter Aliasing   To optimize the subprogram ca...

Parameter Aliasing   To optimize the subprogram call, the PL/SQL compiler can decide between the two techniques of the parameter passing. With the by-value techniques, the v

Cursor variables, What Are Cursor Variables  ? The Cursor variables ar...

What Are Cursor Variables  ? The Cursor variables are like C or Pascal pointers that hold the memory location (address) of some item rather of the item itself. Therefore, decl

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd