Sql-2

Introduction Although SQL is called a query language, it is capable of more than just getting data off relations in the databases. It can also handle calculation, data updates and even data definitions - add new data, change existing data, delete or create new structures. Thus SQL is capable of:

1. Data Query The contents of the database are accessed via a set of commands whereby useful information is returned to the end user (these SQL facilities were discussed in the previous Lesson 9)

2. Calculation over tuples in a datatabase Collections of data can be analyzed in such a way that only results of calculations performed on data in the database are returned to the end user.

3. Data Maintenance The data within the relations can be created, corrected, deleted and modified

4. Data Definition The structure of the database and its relations can be defined and created This lesson discusses the issues 2 - 4 above.

10.2 Library Functions and Arithmetic Expressions The 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:


Using a library function with SQL Select The common SQL functions available are:

Examples (1) Get the average quantity of VDUs per transaction 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

where the average quantity value is easily computed as (30+20)/2 which is 25. (2) Get the total quantity of VDUs transacted would similarly be expressed as 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:

Working through 3 nested Selects The outcome would be the following relation

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:

1. addition +
2. subtraction -
3. multiplication *
4. division /

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) ) )
10.3 Grouping of Data
10.3.1 Grouping Tuples
Usually, the result of a library function is calculated for the whole relation. For example, consider wanting to find the total number of transactions,
Select Count (*) From Transaction

However, sometimes we need to calculate a library function, not for the entire relation, but only for a subset of it. Such subsets of tuples are called groups. For instance, in the relation Transaction, a collection of tuples with the same value of attribute C# is a "group". In this case, C# is called "Group By" attribute.

Grouping by customer numbers The form of the Group By clause is Group By