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