Thursday, 18 February 2010

Retrieving Data from Two Table ( Interbase Tutorial # 11 )

We already have two tables in our database. Each table contains some populations. Now let’s see what we have in each table using SELECT statement.

    select * from employee


  1 Isaac Newton   30 555-5555  
  2 Blaise Pascal  25 555-1234  
  3 Archimedes     32 555-2233  
  4 George Boole   27 555-4521

    select * from salary


         1             1     1000   100        1100 
         2             2     2000   200        2200 
         3             3     3000   300        3300 
         4             4     4000   400        4400

Now we’re going to get employee’s name and their total salary. It will be a little bit complicated, because the ‘name’ column is in ‘employee’ table and the ‘totalsalary’ column is in ‘salary’ table. But don’t be worry, we can also retrieve data from two table using SQL statement. And the statement is :

    select name, totalsalary from employee, salary 
    where = salary.employee_id

The output is :

NAME                 TOTALSALARY

Isaac Newton                          1100
Blaise Pascal                           2200
Archimedes                            3300
George Boole                          4400

The WHERE clause is for comparing the value of ‘id’ column in ‘employee’ table with the value of ‘employee_id’ in ‘salary’ table.

Now we’re going to get names and their total salaries of employee whose age is 30 year above. The statement will be :

select name, totalsalary from employee, salary 
where age>=30 and = salary.employee_id

The output is :


Isaac Newton        1100 
Archimedes          3300 

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.