Example of not exists operator - sql, PL-SQL Programming

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 there is an alternative formulation using IS_EMPTY.

Example: MAX_ENROLMENTS expressed using an invocation of NOT EXISTS

CREATE ASSERTION MAX_ENROLMENTS_alternative1

CHECK (NOT EXISTS (SELECT *

FROM (VALUES (SELECT COUNT (*)

FROM IS_ENROLLED_ON)) AS

V (N)

WHERE V.N > 20000));

Explanation

  • VALUES ( SELECT COUNT(*) FROM IS_ENROLLED_ON ) denotes a table with just one column, unnamed, in whose single row the value of that column is the number of rows in the current value of IS_ENROLLED_ON. The SELECT expression is parenthesized to make it into a scalar subquery and given as the argument to an invocation of VALUES, which makes the number denoted by that scalar subquery into a one-row, one-column table. (Actually, it might be safer to place an extra pair of parentheses around the SELECT expression here. Although VALUES 1 and VALUES (1) are equivalent, it might not be clear as to which role the single parentheses are taking: do they denote a scalar subquery, as I have assumed, or are they the optional ones surrounding a single table expression? If the latter, we would expect a syntax error.)
  • AS V (N) defines the range variable V to refer to what in this case is just the single row of that table, and also assigns the name N to its only column.
  •  WHERE V.N > 20000 operates on that one-row, one-column table to yield a table of heading (N INTEGER) that is empty if and only if the single row in that one-row, one-column table fails to satisfy the condition N > 20000. Thus, the result is empty only when the number of enrolments is in fact no greater than the maximum allowed.
Posted Date: 1/18/2013 7:44:45 AM | Location : United States







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

Write discussion on Example of not exists operator - sql
Your posts are moderated
Related Questions
Indeterminacy in SQL Some SQL expressions are actually not function invocations at all in the mathematical sense, being indeterminate-invocations operating on identical input

Multiset types - SQL An SQL multiset is what in mathematics is also known as a bag-something like a set except that the same element can appear more than once. The body of an

Anatomy of a Command Figure, showing a simple SQL command, is almost identical to its counterpart in the theory book. The only difference arises from the fact that SQL uses a

How Calls Are Resolved? The figure shows that how the PL/SQL compiler resolves the subprogram calls. When the compiler encounters the procedure or function call, it tries to di

I have a Pascal Source file that needs to be compiled into a Service. In addition, there are various functions (Pascal Procedures I guess) that need to be created to Read and Write

Parameter and Keyword Description:   table_reference: This identifies a table or view which should be available when you execute the INSERT statement, and for that you sho

Tautology - Equivalences Rules: If there Tautologies are not all the time as much easy to note as the one above so than we can use these truth tables to be definite that a sta

RETURN Statement The RETURN statement instantly completes the execution of a subprogram and returns control to the caller. The Execution then resumes with the statement below t

Quantification in SQL To quantify something, as the theory book has it, is to state its quantity, to say how many of it there are. For example, in Tutorial D the expression CO

Running the PL/SQL Wrapper To run the PL/SQL Wrapper, go through the wrap command at your operating system prompt by using the syntax as shown: wrap iname=input_file [oname=