Tuesday, 16 February 2010

Subquery and Aggregate function ( Interbase Tutorial # 10 )


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 :



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 :

Get the average value in a group of rows

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

Get the minimum value in a group of rows

Get the minimum value in a group of rows

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


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.