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
Variable Declaration - SQL SQL's support for variables is very similar to Tutorial D's, except that the syntax for creating persistent  variables-base tables-is quite differen

Semidifference and NOT - SQL In this section first describe the relational difference operator, named MINUS. Example here shows SQL's closest counterpart of that operator.

Keyword and Parameter Description select_statement: This is a query which returns a result set of the rows. Its syntax is such that of select_ into_statement without the IN

Example of GROUP BY and COLLECT Operator Example: Using GROUP BY and COLLECT to obtain C_ER2 SELECT CourseId, CAST ( COLLECT (ROW (StudentId, Mark)) AS ROW (Studen

Data Types in SQL - Timestamp TIMESTAMP for values representing points in time on a specified uniform scale. DATE is used for timestamps on a scale of one day, such as DATE '2

Grouping and Ungrouping in SQL Example specifying EXAM_MARK in place of COURSE in the main FROM clause. Example: Obtaining C_ER2 from EXAM_MARK SELECT CourseId, CAST

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

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

The Package Body The package specification is implemented by the package body. That is, the package body has the definition of every cursor and the subprogram declared in the p

Use External Routines The PL/SQL is particular for the SQL transaction processing. Therefore, several tasks are more quickly completed in a lower-level language like C that is