Retrieving Data ( Interbase Tutorial # 8 )

Now we’re going to populate our database again. To avoid typing the SQL statement and executing them one by one in the Interbase Windows ISQL, type these SQL statements below in a text editor :

  connect 'c:\mydata\mydata.gdb'
  user 'NEW_USER' password 'password';

  insert into employee (id, name, age, phonenumber) 
  values ('1','Isaac Newton','30','555-5555');

  insert into employee (id, name, age, phonenumber) 
  values ('2','Blaise Pascal','25','555-1234');

  insert into employee (id, name, age, phonenumber) 
  values ('3','Archimedes','32','555-2233');

  insert into employee (id, name, age, phonenumber) 
  values ('4','George Boole','27','555-4521');

  insert into salary (salary_id, employee_id, salary, bonus) 
  values ('1','1','1000','100');

  insert into salary (salary_id, employee_id, salary, bonus) 
  values ('2','2','2000','200');

  insert into salary (salary_id, employee_id, salary, bonus) 
  values ('3','3','3000','300');

  insert into salary (salary_id, employee_id, salary, bonus) 
  values ('4','4','4000','400');

(note : if you type SQL statements in a text editor, you have to put a ‘;’ at the end of each statement) 
 
Save the statements above in a file named ‘inputdata.txt’. Then execute it in the Interbase Windows ISQL by choosing File – Run an ISQL Script.

After populating our database, now we’re going to get the data out and use them. To get the data out we use SELECT statement. The SELECT statement is the most important, because if we have a data structure but we can not get them out and use them, it will be useless. The syntax is :

  SELECT column1,column2…. FROM TableName

or

  SELECT * FROM TableName

SELECT statement has six keywords : DISTINCT, FROM, WHERE, GROUP BY, HAVING and ORDER BY.

WHERE clause

WHERE clause will limit the retrieved rows with specified conditions. Only the rows which has the specified conditions will be retrieved. The syntax is :

  SELECT column1,column2…. FROM TableName
  WHERE condition

Execute these statements below and see what happen in the output windows :

  select * from employee where age>=30

  select name, phonenumber from employee where age>=30 

  select name, phonenumber from employee where not age>=30 

  select name, phonenumber from employee where (age>=30) and (id>1)

  select * from employee where (age>30) or (age<26)

ORDER BY clause

ORDER BY clause will order the output of a SELECT statement by the specified column. OERDER BY has a syntax :

  ORDER BY column ASC

or

  ORDER BY column DESC

Execute these statements below and see what happen in the output windows :

  select * from employee order by age asc

  select * from employee order by age desc

  select * from employee where age <>

DISTINCT clause

DISTINCT clause will eliminate duplicate rows of the output of a SELECT statement. The syntax is :

  SELECT DISTINCT column FROM table

GROUP BY

GROUP BY clause will group rows according the value of specified column. The syntax is :

  SELECT column FROM table GROUP BY column

HAVING clause

Having clause will specify search condition and to use with GROUP BY clause. The syntax is :

  SELECT column FROM table GROUP BY column HAVING search_condition

Go to Previous Tutorial or Next Tutorial