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
Transactions in SQL BEGIN TRANSACTION, COMMIT, and ROLLBACK, SQL has the same syntax except for START in place of BEGIN. However, START TRANSACTION is used only for outermost

I would like to have a custom MS Access database designed and coded that would help me schedule my customer's orders and that would help me track my employees production output and

UPDATE Command- SQL Loosely speaking, UPDATE changes some of the column values of some existing rows of its target table. Thus, although some rows disappear from the target an

Using EXCEPTION_INIT To handle unnamed internal exceptions, you should use the OTHERS handler or the pragma EXCEPTION_INIT. The pragma is a compiler directive that can be th

Write a query to find academics that are authors and that have only ever coauthored papers with authors from institutes in the same state as their own. List their academic number,

LOOP Statements The LOOP statements execute a series of statements at multiple times. The loops enclose the series of statements that is to be repeated. The PL/SQL provides typ

The requirements as follows: Create a folder called "SECURITY" on the server and upload all your project files to that folder. Please note, the "SECURITY" folder is NOT to be IN

Use of Table Expressions - Expressing Constraint Conditions With the exception of key constraints, the examples in the theory book all explicitly reference at least one relvar

Components of an Object Type: An object type encapsulates the operations and data. Therefore, you can declare the methods and attributes in an object type specification, but no

Using Pragma RESTRICT_REFERENCES: The function called from the SQL statements should obey certain rules meant to control the side effects. To check for violation of the rules,