Extension and and in sql, PL-SQL Programming

Extension and AND in SQL

The theory book gives the following simple example of relational extension in Tutorial D:

EXTEND IS_CALLED ADD ( FirstLetter ( Name ) AS Initial )

Assuming the user-defined operator FirstLetter is available to the SQL user, this can be expressed easily in SQL but there is a strange quirk in the grammar at play here:

SELECT IC.*, FirstLetter ( Name ) AS Initial

FROM IS_CALLED AS IC

Note very carefully that we have to qualify the * using the range variable, IC, which in this case ranges over the rows of the current value of IS_CALLED. When we use a SELECT clause to "add columns" to the table on which it operates, it seems obvious to write * to specify that every column of that operand table is required and to follow it with a commalist of expressions denoting the additional columns. For some reason the official SQL grammar does not allow additional columns to accompany an unadorned *. When the FROM clause contains several entries, pure extension becomes more difficult to express in SQL. For example, if the FROM clause defines range variables T1, T2, and T3, we could write SELECT T1.*, T2.*, T3.* ..., but that would defeat the purpose. To be able to write just a single * to denote all the columns of the FROM table, we would have to resort to something like

SELECT T.*, ...

FROM (SELECT * FROM T1, T2, T3 WHERE ... ) AS T

Posted Date: 1/18/2013 5:25:56 AM | Location : United States







Related Discussions:- Extension and and in sql, Assignment Help, Ask Question on Extension and and in sql, Get Answer, Expert's Help, Extension and and in sql Discussions

Write discussion on Extension and and in sql
Your posts are moderated
Related Questions
Using Operator REF: You can retrieve refs by using the operator REF that, like VALUE, takes as its argument a correlation variable. In the illustration below, you retrieve one

Parameter Modes   To define the behavior of formal parameters you use the parameter modes. The 3 parameter modes, IN, OUT, & IN OUT, can be used with any subprogram. Though, a

Declaring Records Whenever you define a RECORD type, you may declare records of that type, as the illustration shows: DECLARE TYPE StockItem IS RECORD ( item_no INTEG

Using NOT NULL Besides assigning an initial value, the declarations can impose the NOT NULL constraint, as the example below shows: acct_id INTEGER(4) NOT NULL := 9999; You ca

Effect of Anonymous Columns Now, recall that a VALUES expression denotes a table with undefined column names. If an initial value is to be specified when a base table is creat

query to Find the account numbers of all customers whose balance is more than 10,000 $

Authorisations - Privileges As relational theory is silent on the issue of authorisation, it offers nothing with which SQL's vast edifice in support of what it calls privilege

Keys in SQL SQL support for keys in the following respects: SQL does not require at least one key for every base table. If no key is explicitly declared, then KEY {ALL B

UNION without CORRESPONDING - SQL The use of UNION without CORRESPONDING. Example is merely by omitting CORRESPONDING, but only because the operands have identical SELECT clau

Pl/sql Conditional Control: IF statements Frequently, it is necessary to take the alternative actions depending on the circumstances. The IF statement execute a series of statem