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


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.*, ...


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
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

Joining in SQL Joining IS_CALLED and IS_ENROLLED_ON in SQL SELECT * FROM IS_CALLED NATURAL JOIN IS_ENROLLED_ON This is an example of an SQL table expression. I have been

%NOTFOUND The %NOTFOUND is logical, opposite of the %FOUND. The %NOTFOUND yields FALSE if the last fetch returned a row, or TRUE when the final fetch failed to return a row. I

Nested Tables versus Index-by Tables The Index-by tables and nested tables are just similar. For e.g.  They have similar structure and their individual elements are accessed in

WHILE-LOOP The WHILE-LOOP statement relates a condition with the series of statements enclosed by the keywords LOOP and END LOOP, as shown: WHILE condition LOOP sequence_of_sta

Read-Only Operator (+) - SQL The term read-only operator to the mathematical term function. Here I just need to add that the SQL standard reserves the term function for read-

Example of WHEN or THEN Constraints A concrete example showing how SQL supports WHEN/THEN constraints CREATE TABLE SAL_HISTORY (EmpNo CHAR (6), Salary INTEGER NOT NULL,

Using Cursor Attributes: Every cursor has 4 attributes: %NOTFOUND, %FOUND, %ISOPEN, and %ROWCOUNT. If appended to the cursor name, they return the helpful information about

Subprograms The PL/SQL has two types of subprograms known as the procedures and functions that can take parameters and be invoked. As the following example represents, a subp

Using COMMIT The COMMIT statements end the present transaction and make permanent any changes made during that transaction. Till you commit the changes, other users cannot acc