1 Introduction to Databases 2 2 Basic Relational Data Model 11
Further Retrieval Facilities
Download 1.01 Mb.
|
- Navigate this page:
- 9.4Library Functions and Arithmetic Expressions
9.3Further Retrieval Facilities9.3.1 Joining Relations In the examples that have been used so far, our retrievals have been of values taken from one relation, as in “Select C# From Transaction”. However, often we have to retrieve information from two or more relations simultaneously. In other words, a number of relations names may be used in the From clause of SQL. For example, if we wish to access the relations Customer and Transaction, we may write the SQL statement as follows: Select … From Customer, Transaction Where… The target list in the Select clause may contain the attributes form various relations, as in Select Cname, Date, Qnt From Customer, Transaction Where… where, if you recall, Cname is an attribute of Customer and Date and Qnt are attributes of Transaction. Similarly, comparison expressions in the Where clause may include attribute names from various relations, Select Cname, Date, Qnt From Customer, Transaction Where (Customer.C# = Transaction.C#) And P# = 1 N T It must be noted that the two (or more) relations that must be combined on at least one common linking attribute (as in the Relational Algebra’s JOIN operator). As in the above example, the link is established on C# as in the clause Where Customer.C# = Transaction.C# 9.3.2 Alias In order to avoid a possible ambiguity in a query definition SQL also allows to use an alias for the relation name in the From clause. The alias is an alternate name that is used to identify the source relation and the attribute names may include an alias as a prefix: Suppose we use T and C as the aliases for the Transaction and Customer relations respectively. We may use these to label the attributes as in: Select ... From Customer C, Transaction T Where C.C# = T.C# And … An alias is especially useful when we wish to join a relation to itself because of grouping as in the query to “Find the names and phone numbers of customers living in the same city as the customer Codd”: Select C2.Cname, C2.Cphone From Customer C1, Customer C2 Where C2.Ccity = C1.Ccity And C1.Cname = ‘Codd’ T F   igure 9-6. Using an alias 9.4Library Functions and Arithmetic ExpressionsThe SQL Select clause (target list) may contain also so-called SQL library functions that will perform various arithmetic summaries such as to find the smallest value or to sum up the values in a specified column. The attribute name for such library functions must be derived from the relations specified in the From clause as follows:  Figure 9-7. Using a library function with SQL Select The common SQL functions available are:
Examples
Select AVG (Qnt) From Transaction Where P# = ( Select P# From Product Where Pname = ‘VDU’ ) Working first with the inner Select clause, we get a P# of 2 from the Product relation as the part number for the product named VDU. Thus the query is now reduced to Select AVG(Qnt) From Transaction Where P# = 2 Accessing the Transaction relation now would yield the following two tuples w
Select SUM (Qnt) From Transaction Where P# = ( Select P# From Product Where Pname = ‘VDU’ ) where the total value is easily computed as (30 + 20) giving 50. An asterisk (*) in the Select clause is interpreted as “all attributes names of the relations specified in the From clause”. Select * From Transaction is equivalent to Select C#, P#, Date, Qnt From Transaction Thus a query to “Get all available information on customers who bought the product VDU” can be written as: Select * From Customer Where C# In ( Select C# From Transaction Where P# In ( Select P# From Product Where Pname = ‘VDU’ ) ) The interpretation of this query would be worked out as shown in the following sequence of accesses, starting from the access of the product relation to the Transaction and finally to the Customer relation:
T (3) Get a total number of such customers who bought the product VDU, would be written as: Select COUNT (*) From Customer Where C# In ( Select C# From Transaction Where P# In ( Select P# From Product Where Pname = ‘VDU’ ) ) and this would yield a value of 2 for Count (*). Arithmetic expressions are also permitted in SQL, and the possible operations include:
Expressions may be written in the Select clause as: Select C#, P#, Qnt*Price From Transaction, Product Where Transaction.P# = Product.P# which is used to “Get a total price for each transaction” resulting in:  Arithmetic expressions, likewise, can also be used as parameters of SQL library functions. For example, “Get a total price of all VDUs sold to customers” may be written as the following SQL statement: Select SUM (Qnt*Price) From Transaction, Product Where Transaction.P# = Product.P# And Product.Pname = ‘VDU’ Work this out. You should get an answer of 60000. The attribute names for both library functions and arithmetic expressions must be derived from the relations specified in the From clause. Thus, it should be noted that the following query definition is NOT correct. Select SUM (Qnt*Price) From Transaction Where Transaction.P# = Product.P# And Product.Pname = ‘VDU’ Additionally, SQL also permits the use of library functions not only in the Select clause but also in the Where clause as a part of comparison expressions. The query to “Get all available information on such customers who bought the most expensive product” would be: Select * From Customer Where C# In ( Select C# From Transaction Where P# In ( Select P# From Product Where Price = MAX (Price) ) )
Directory: scripts scripts -> Swiver by Piers Beckley Stef Morgan scripts -> 1 Introduction 2 2 Software Engineering and Software Paradigms 5 scripts -> Theatricals 229 West 28th Street • 11th Floor • New York, ny 10001 scripts -> Host Programming scripts -> Googly written by Mehul Oza May 11, 2009 int. Day dressing room of cricketers scripts -> 4th Revision 2009 Rico Alago scripts -> The Umbrella Academy: Trade 1 Apocalypse Suite Based on the Comic Book Series by Gabriel Ba and Gerard Way scripts -> Script box for new patient green light case exceptancetm Download 1.01 Mb. Share with your friends:
|
