Effects of null in aggregate operator - sql, PL-SQL Programming

Assignment Help:

Effects of NULL in Aggregate Operator - SQL

Let aggop(x) be an invocation of some aggregate operator aggop in SQL, where x is an expression (usually an open expression) to be evaluated against each row of the table t determined by the context in which the invocation appears. Then aggop considers only those rows that satisfy the condition x IS NOT NULL. It follows that if aggop is EVERY or SOME and x evaluates to TRUE or FALSE for at least one row of t, then the result is either TRUE or FALSE, never UNKNOWN. However, if x evaluates to UNKNOWN for every row of t (which is true in the particular case when t is empty), then SQL's other general rule kicks in, requiring the result to be NULL, which is equivalent to UNKNOWN when it appears in the place of a BOOLEAN value.

That anomaly is to some extent compensated for, when EVERY is used in constraint declarations, by SQL's rule that a constraint is deemed to be satisfied when it evaluates to UNKNOWN. However, (SELECT SOME(TRUE) FROM (tx) AS T) is not reliable as an existence test because it evaluates to UNKNOWN if the result of tx is empty, when a constraint based on that condition would be deemed satisfied. That problem could be addressed by writing COALESCE((SELECT SOME(TRUE) FROM (tx) AS T), FALSE) or, equivalently, (SELECT SOME(TRUE) FROM (tx) AS T) IS TRUE .


Related Discussions:- Effects of null in aggregate operator - sql

Object types - syntax, Object Types An object type is a user-defined co...

Object Types An object type is a user-defined complex datatype which encapsulates the data structure along with the functions and procedures required to manipulate the data. Th

Write a pl-sql program using the implicit cursor, Question: a) Given th...

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

Stored subprograms, Stored Subprograms Normally, tools (like Oracle Fo...

Stored Subprograms Normally, tools (like Oracle Forms) which incorporate the PL/SQL engine can store subprograms locally for later, strictly local execution. Though, to become

Database values-assignments in pl/sql, Database Values You can use the S...

Database Values You can use the SELECT statement to have the Oracle assign values to a variable. For Each and every item in the select list, there must be a matching, type-compa

Keyword and parameter description - if statement, Keyword and Parameter Des...

Keyword and Parameter Description: boolean_expression: This is an expression which results the Boolean value TRUE, FALSE, & NULL. It is related with a series of statement

Library system, Hi,am developing a library system and relating all the tabl...

Hi,am developing a library system and relating all the table is somehow complex,could you kindly assist me

How exceptions propagate in pl/sql programming?, How Exceptions Propagate ?...

How Exceptions Propagate ? Whenever an exception is raised, and if the PL/SQL cannot find a handler for it in the present subprogram or block, the exception propagates. That is

Example of alternative formulation as a table constraint, Example of Altern...

Example of Alternative formulation as a table constraint Example: Alternative formulation as a table constraint ALTER TABLE EXAM_MARK ADD CONSTRAINT Must_be_enrolled_to_

Change sql file into csv for product registration, Change Sql file into CSV...

Change Sql file into CSV for product registration on Magento Project Description: I have a set of files that are in Sql format and could like for a developer to help me with

Effects of null operator, Effects of NULL Operator As a general rule-b...

Effects of NULL Operator As a general rule-but not a universal one-if NULL is an argument to an invocation of a system-defined read-only operator, then NULL is the result of t

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