Example of cast operator - sql, PL-SQL Programming

Assignment Help:

Example of Cast Operator

So long as CAST is used as shown, we could obtain the total marks for each exam in similar fashion, using SUM (Mark) AS TotalMarks. However, this gives NULL, instead of zero, for the courses whose exams nobody sat. That problem can be addressed by using COALESCE, as shown in Example.

Example: Give the total of marks for each exam

WITH C_ER AS (

SELECT CourseId,

CAST (

TABLE (SELECT DISTINCT StudentId, Mark

FROM EXAM_MARK AS EM

WHERE EM.CourseId = C.CourseId )

AS ROW ( StudentId SID, Mark INTEGER) MULTISET)

AS ExamResult

FROM COURSE AS C)

SELECT CourseId, (SELECT SUM (Mark)

FROM TABLE(ER (ExamResult)) AS t) AS n

FROM C_ER

Explanation

COALESCE((SELECT SUM(Mark) FROM TABLE(ER(ExamResult)) AS t),0) yields the value of the scalar subquery whenever ExamResult is nonempty, otherwise zero. SQL's COALESCE is an n-adic operator that takes a commalist of expressions of the same declared type and yields the result of the first of those expressions, in the order in which they are written, that does not evaluate to NULL. When there is no such operand, it yields NULL anyway, of course.

Related Discussions:- Example of cast operator - sql

Third step at defining type sid in sql, Third Step at defining type SID in ...

Third Step at defining type SID in SQL CREATE DOMAIN SID AS VARCHAR(5) CHECK ( VALUE IS NOT NULL AND SUBSTRING(VALUE FROM 1 FOR 1) = 'S' AND CAST('+'||SUBSTRING(VALUE

Sql operators, SQL Operators The PL/SQL uses all the SQL set, compariso...

SQL Operators The PL/SQL uses all the SQL set, comparison, and row operators in the SQL statements. This part briefly describes some of these operators. 1. Comparison Opera

Using exists - collection methods, Using EXISTS The EXISTS(n) returns ...

Using EXISTS The EXISTS(n) returns TRUE if the nth element in a collection exist. Or else, EXISTS(n) returns FALSE. Primarily, you use EXISTS with DELETE to maintain the spars

Defining ref cursor types, Defining REF CURSOR Types To make cursor va...

Defining REF CURSOR Types To make cursor variables, you take 2 steps. At first, you define a REF CURSOR type, and then declare the cursor variables of that type. You can defin

Example of not exists operator - sql, Example of NOT EXISTS Operator - SQL ...

Example of NOT EXISTS Operator - SQL Example is a translation into SQL of the corresponding example, which is included there merely to show that for any scalar comparison the

Oracle, Literature review

Literature review

Set operators - sql operators, Set Operators The Set operators combine...

Set Operators The Set operators combine the results of the two queries into one result. The INTERSECT returns all the distinct rows selected by both queries. The MINUS returns

I want to build website for funding, Project Description: I want to rebu...

Project Description: I want to rebuild it and add better content to it It will include up to 5 forms The data will be saved on SQL server and the data access layer could b

Need for dynamic sql - pl sql , Need for Dynamic SQL: You need dynamic...

Need for Dynamic SQL: You need dynamic SQL in the situations as follows: 1) You would like to execute a SQL data definition statement (like CREATE), a data control statemen

Collection methods in pl sql, Collection Methods: The collection me...

Collection Methods: The collection method is a built-in function or procedure which operates on the collections and is called using the dot notation. The methods like the C

Write Your Message!

Name

Email id

Message

Verfication Code

User Account

All Pages