Using rename in combination with join - sql, PL-SQL Programming

Assignment Help:

Using RENAME in combination with JOIN - SQL

Example gives pairs of ids of students having the same name, by joining two renamings of IS_CALLED. Example gives an equivalent expression in SQL.

Example: Renaming and joining

Student Sid1 is called Name and so is student Sid2

SELECT *

FROM   (SELECT StudentId AS Sid1, Name FROM IS_CALLED)

     NATURAL JOIN

     (SELECT StudentId AS Sid2, Name FROM IS_CALLED)

As before, the result sagely tells us that student S1 (Anne) has the same name as herself and also shows two pairings of S1 with S5 (both named Boris). The pairing of a student id with itself can be avoided by adding WHERE Sid1 < > Sid2 to the WHERE clause. The duplicate pairings can further be avoided by using < instead of < > in this addition, but that trick assumes that an ordering is defined for type SID, which is not necessarily the case.


Related Discussions:- Using rename in combination with join - sql

Library system, Hi,am developing a library system and relating all the tabl...

Hi,am developing a library system and relating all the table is somehow complex,could you kindly assist me

Control structure, Control Structures The Control structures are the mo...

Control Structures The Control structures are the most important PL/SQL extension to the SQL. Not only does PL/SQL let you manipulate Oracle data, it lets you process the data

Opening a cursor, Opening a Cursor Opening the cursor executes the que...

Opening a Cursor Opening the cursor executes the query & identifies the result set that consists of all rows that meet the query search criteria. For the cursors declared usin

Example of tables within a table - sql, Example of Tables within a Table - ...

Example of Tables within a Table - SQL Example: Obtaining C_ER from COURSE and EXAM_MARK SELECT CourseId, CAST (TABLE (SELECT DISTINCT StudentId, Mark FROM EXAM_MARK AS EM

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

Using prior and next - collection method, Using PRIOR and NEXT The PRI...

Using PRIOR and NEXT The PRIOR(n) returns the index number that precede index n in a collection. The NEXT(n) returns the index number which succeed the index n. If n has no pr

Using extend - collection method, Using EXTEND To enlarge the size of ...

Using EXTEND To enlarge the size of a collection, use EXTEND. This process has 3 forms. The EXTEND appends one null element to a collection. And the EXTEND(n) appends n null e

Using limit - collection method, Using LIMIT For nested tables, that h...

Using LIMIT For nested tables, that have no maximum size, the LIMIT returns NULL. For varrays, the LIMIT returns the maximum number of elements that a varray can have (that yo

Forward declarations - subprograms, Forward Declarations The PL/SQL ne...

Forward Declarations The PL/SQL needs that you declare an identifier before using it. And hence, you should declare a subprogram before calling it. For illustration, the decla

Data types and representations, Data Types and Representations This ex...

Data Types and Representations This explains the concept possible representation, abbreviated possrep, and explains how these can be used in conjunction with constraints to de

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