Example of coalesce operator - sql, PL-SQL Programming

Example of COALESCE operator

Example: Give the total of marks for each exam (simplified solution)

SELECT CourseId,

COALESCE ((SELECT SUM (Mark)

FROM EXAM_MARK AS EM

WHERE EM.CourseId = C.CourseId ),

 0) AS TotalMark

FROM COURSE AS C

Explanation

  • The first operand to the invocation of COALESCE is a scalar subquery similar to Aggregate Operators. Recall the need to enclose the SELECT expression in parentheses, without which it would denote a table rather than a number.
Posted Date: 1/18/2013 6:38:57 AM | Location : United States







Related Discussions:- Example of coalesce operator - sql, Assignment Help, Ask Question on Example of coalesce operator - sql, Get Answer, Expert's Help, Example of coalesce operator - sql Discussions

Write discussion on Example of coalesce operator - sql
Your posts are moderated
Related Questions
Consider the following set of database tables (same tables from Assignment 6-1). Please take note of foreign keys (most of them carry the same names as the corresponding primary ke

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

Using SAVEPOINT The SAVEPOINT names and marks the present point in the processing of a transaction. Used with the ROLLBACK TO statement, the savepoints undo parts of a transac

Use Bulk Binds If SQL statements execute inside a loop using the collection elements as bind variables, context switching between the PL/SQL & SQL engines can slow down the ex

Question: a) Given the following relation: Location(loc_id, bldg_code, room, capacity) The underlined field is a primary key. (i) Write a PL/SQL program using the impl

Semidifference via EXCEPT and JOIN - SQL SELECT * FROM (SELECT StudentId FROM IS_CALLED WHERE Name = 'Devinder' EXCEPT DISTINCT CORRESPONDING SELECT StudentId

Main features of PL/SQL A good way to get familiar with PL/SQL is to look at a sample program. The below program processes an order for tennis rackets. At first, it declares a

Mixed Notation The fourth procedure call shows that you can mix the positional and named notation. In this situation, the first parameter uses the positional notation, & the s

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

Boolean Values Only the values TRUE, FALSE, & NULL can be assigned to a Boolean variable. For illustration, given the declaration DECLARE done BOOLEAN; the following statements