Extension and and in sql, PL-SQL Programming

Assignment Help:

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


Related Discussions:- Extension and and in sql

Using delete - collection method, Using DELETE This process has three ...

Using DELETE This process has three forms. The DELETE removes all elements from the collection. DELETE(n) removes the nth element from the nested table. When n is null, then D

Features of pl/sql, Main features of PL/SQL A good way to get familiar ...

Main features of PL/SQL A good way to get familiar with PL/SQL is to look at a sample program. The below program processes an order for tennis rackets. At first, it declares a

Inserting objects in pl sql, Inserting Objects: You can use the INSERT...

Inserting Objects: You can use the INSERT statement to add objects to an object table. In the illustration below, you insert a Person object into the object table persons:

User-defined exceptions, User-Defined Exceptions The PL/SQL defines the...

User-Defined Exceptions The PL/SQL defines the exceptions of your own. Dissimilar to the predefined exceptions, the user-defined exceptions should be declared and should be rai

An active database in pl-sql, Consider the following set of database tables...

Consider the following set of database tables (same tables from Assignment 6-1). Please take note of foreign keys (most of them carry the same names as the corresponding primary ke

Loop statements, LOOP Statements The LOOP statements execute a series o...

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

Close statement in pl sql, CLOSE Statement The CLOSE statement allows ...

CLOSE Statement The CLOSE statement allows the resources held by a cursor variable or open cursor to be reused. No more rows can be fetched from the cursor variable or closed

Query optimization, 1.( /5 marks) Suppose that a B+-tree index with the sea...

1.( /5 marks) Suppose that a B+-tree index with the search key (dept_name, building) is available on relation department. What would be the best way to handle the following selecti

Using bulk collect clause- bulk bind performance improvement, Using the BUL...

Using the BULK COLLECT Clause The keywords BULK COLLECT specify the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. You can use these ke

Using trim - collection method, Using TRIM This process has two forms....

Using TRIM This process has two forms. The TRIM removes an element from the end of the collection. The TRIM(n) removes the n elements from the end of the collection. For e.g.

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