Tuesday 16 February 2010

Subquery and Aggregate function ( Interbase Tutorial # 10 )

Subquery

In a WHERE clause, you have a column, a comparative operator and a value to be compared with the column. Subquery provide a value from the other table that will be compared with the column you have in a WHERE clause. In some case we need a subquery for retrieving data.

For example, we want to get a name who earns 3300 a month. The problem is, we don’t have ‘name’ column in the ‘salary’ table. In this case we have to use subquery to get a value to be compared.

In ‘salary’ table we have ‘employee_id’ column which has a constraint value that refers to ‘id’ column in ‘employee’ table. Fist, we have to get an employee_id who earns 3300 from ‘salary’ table, then we compare the employee_id with ‘id’ column in ‘employee’ table to get a name. and the SQL statement will be :

  select name from employee

  where id = (select employee_id from salary where totalsalary=3300)

and the output will be :

NAME  
============================== 

Archimedes

Now, I will give you a problem :
We want to get the total salary of the employee whose phone number is 555-1234.
Figure out the SQL statement to solve this problem !

Aggregate Function

SQL provide some aggregate functions that calculate some values in columns. There are five aggregate functions that provided by SQL :

AVG 
Get the average value in a group of rows

COUNT 
Count the number of rows. You can use WHERE clause here to specify the rows you count

MIN 
Get the minimum value in a group of rows

MAX
Get the minimum value in a group of rows

SUM
Adds numeric values in a group of rows

Some examples of using aggregate function :

   select avg(age) from employee

   select avg(totalsalary) from salary

   select min(salary) from salary

   select count(totalsalary) from salary where totalsalary>2000

   select sum(totalsalary) from salary

You can try other SQL statement using aggregate functions.

Go to Previous Tutorial or Next tutorial

0 comments:

Post a Comment

These links are part of a pay per click advertising program called Infolinks. Infolinks is an In Text advertising service; they take my text and create links within it. If you hover your mouse over these double underlined links, you will see a small dialog box containing an advertisement related to the text. You can choose to move the mouse away and go on with your browsing, or to click on the box and visit the relevant ad. Click here to learn more about Infolinks Double Underline Link Ads.