Thursday 4 February 2010

Inserting, Reading, Updating and Deleting Data ( Interbase Tutorial # 4 )

We already have a database ‘mydata.gdb’, and it contains a table ‘employee’. Now we’re going to populate our database.

Inserting Data

The syntax for inserting data into a table is :

INSERT INTO TableName (field1,field2,field3,….)
VALUES (‘value1’,’value2’,’value3’,…)


Go to the Interbase Windows ISQL from the start menu. Connect to your database. Insert first record into your table ‘employee’ by typing these SQL codes in the SQL statement area :

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

then execute the codes. Now you have your first record in your table :

Id : 1
Name : Isaac Newton
Age : 30
Phonenumber : 555-5555

Now insert records below into your table ‘employee’ with the same way :

Id : 2
Name : Blaise Pascal
Age : 25
Phonenumber : 555-1234

Id : 3
Name : Archimedes
Age : 32
Phonenumber : 555-2233

Id : 4
Name : George Boole
Age : 27
Phonenumber : 555-4521

You have to type the same SQL codes for each record. If you’ve got hundreds of records, it will be a nightmare to do this. And I won’t do this. Of course there is more civilized way to do this. Soon I will share how to create a “user friendly interface”.

Reading Data

We have four records in our table, now we’re reading the record using this syntax :

SELECT column1,column2…. FROM TableName

If you want to read all columns of your records, the syntax is :

SELECT * FROM TableName

Example1 : if you want to read all columns in table ‘employee’ the code will be

select * from employee

Example2 : If you only want to read column ‘name’ and ‘age’ of your table, the codes will be

select name, age from employee

Updating Data

If you want to update your data in the table use this syntax :

UPDATE TableName
SET column = value
WHERE condition


WHERE is optional. If there’s no WHERE, all the records will be updated. Otherwise, it will make restrictive change to the record which is in the condition.

Example 3 : You want to increase all ages of your record by 1

update employee set age = age + 1


Example 4 : If you want to change Archimedes’ phone number, you have to use WHERE clause

update employee set phonenumber = ‘555-7777’
where name=’Archimedes’


Deleting Data

To delete data from your table use this syntax :

DELETE from TableName
WHERE condition

If there is no WHERE clause, all the record will be deleted from the table. If you use WHERE clause, only a record which has the condition will be deleted.

Example 5 : You want to delete employee whose age is above 30

delete from employee where age > 30

Committing Your Work

After you execute some SQL codes, you need to commit your work. If you don’t, other users won’t see the changes you have made. Commit your work by choosing File – Commit work.

Go to Previous Tutorial or NextTutorial

3 comments:

  1. thanks for sharing this, just what i was searching for.

    www.n8fan.net

    ReplyDelete
  2. I definitely liked reading everything that is posted on your website. Where else could anyone get that type of facts in such a perfect way of writing?

    www.triciajoy.com

    ReplyDelete
  3. You must take part in a contest for among the finest blogs on the web. I will suggest this site! online casino games

    ReplyDelete

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.