Basic
Walkthrough Oracle Part-3
This
section will help you walk through development of simplest database program
(utility). I will make it to happen so smoothly towards the end of this part
you will not even believe that the utility is ready for use.
Of
course, intention to keep it simple, will prevent us from having Graphical User
Interface (GUI); still the main purpose of looking closely at Oracle will be
served and along with the knowledge (know how) there will be another part,
which is called amusement while learning. If we have to enjoy the product (Oracle) which is free for trial, we must learn main features in smallest possible time; which is
different person to person.
So, let us start. As we tried few commands in previous part, I will prefer to take you along with me to a nice short development tour. Mostly I will use windows command line, which means that for purpose of understanding commands, I will be using sqlplus. Don’t worry if you are not familiar with one or other term, because I will be showing snapshots to help you get used to.
First of all I would like to create a table with few columns. The
definition is shown below. I will leave it up to you to see and guess which
column is used for what data.
|
CREATE TABLE
ADDRESS ( FNAME CHAR(16) NOT
NULL, MNAME CHAR(12), LNAME CHAR(16) NOT
NULL, PHONE CHAR(15), CELL CHAR(15), ADDR CHAR(35) ) |
This will create the table in the current
database that you are connected to.
|
Table created. |
After creating table, I would
emphasize on creating a primary key on First Name (FNAME) column. This will
help us keep consistent data in our database. The key will
be created as combination of two columns, FNAME and LNAME. Which basically help
us search through the data when there are really big number of rows and will
help us keep distinct data.
|
ALTER TABLE
ADDRESS ADD CONSTRAINT My_FNAME_LNAME_PK PRIMARY KEY( FNAME, LNAME) Table altered. |
So,
if we create primary key as a combination of FNAME and LNAME, there can’t be
two identical combinations. For example, you can add ‘Tai
Tang’ first time, but in subsequent entries ‘Tai
Tang’ will not be accepted.
After
creating primary key, you will insert some data. You can do this manually
because as a sample data of imaginary records, there will be only four rows for
our hands on. So, when you will enter data at sqlplus console, following will be scene.
|
insert into
address values ('Tai','','Tang','647-647-6477','416-614-6144','64 Pluto Way
Brampton,ON,CA') 1 row created. insert into
address values ('Sabar','','Raikoti','905-509-5099','419-914-9144','1032
Mavis Rd Mississauga,ON,CA') 1 row created. insert into
address values ('David','M.','Brown','905-264-2644','647-746-7466','1216
Morning Star Drive Miss,ON,CA') 1 row created. insert into
address values
('Dilshaad','Sufi','Akhtar','905-932-9322','416-417-4177','3456 Horner Ave
Etobicoke,ON,CA') 1 row created. |
 |
If
you want to check how primary key stops wrong data creep in try to enter same
row as row #1 again.
|
insert into address
values ('Tai','','Tang','647-647-6477','416-614-6144','64 Pluto Way
Brampton,ON,CA') insert into address values
('Tai','','Tang','647-647-6477','416-614-6144','64 Pluto Way Brampton,ON,CA') * ERROR
at line 1: ORA-00001:
unique constraint (USER1.MY_FNAME_LNAME_PK) violated |
This will prevent duplicate data and if
you really need two similar names exist in the table you need to set primary
key more widely. Try eliminating ‘a’
from ‘Tang’.
|
insert into
address values ( 'Tai','','Tng','647-647-6477','416-614-6144','64 Pluto Way 1
row created. |
Now
it allows. The check is done only on participating key columns. Others can have
duplicate data.
For our Address Book utility this
table is set up. Further we will see few commands to retrieve data. Try some
simple SQL to retrieve data in ADDRESS Table:
|
Select
Fname,Lname,Phone,Cell from address FNAME LNAME PHONE CELL ----------------
---------------- --------------- --------------- Tai Tang 647-647-6477 416-614-6144 Sabar Raikoti 905-509-5099 419-914-9144 David Brown 905-264-2644 647-746-7466 Dilshaad Akhtar 905-932-9322 416-417-4177 Tai Tng 647-647-6477 416-614-6144 |
Ops, I wanted only David, he called me
last night.
|
Select
Fname,Lname,Phone,Cell from address where fname=’David’ SQL> select
Fname,Lname,Phone,Cell from address where fname='David'; FNAME LNAME PHONE CELL ----------------
---------------- --------------- --------------- David Brown 905-264-2644 647-746-7466 |
So,
it’s easy to select one out of many. While comparing strings you have to
specify the correct case for all letters or you need to do comparison using
some function as below.
|
Select
Fname,Lname,Phone,Cell from address where upper(fname)=’DAVID’ |
|
SQL> Select
Fname,Lname,Phone,Cell from address where upper(fname)='DAVID'; FNAME LNAME PHONE CELL ----------------
---------------- --------------- --------------- David Brown 905-264-2644 647-746-7466 |
Another situation, I think the gentleman
called me three weeks ago; mm I can’t recall the name but phone number was
416-509…… may be 647-509 . . .. Well I remember middle three digits were 509 ..
. a kind of… let me try
|
Select
Fname,Lname,Phone,Cell from address where phone like (‘%509%’) |
|
SQL> Select
Fname,Lname,Phone,Cell from address where phone like ('%509%'); FNAME LNAME PHONE CELL ----------------
---------------- --------------- --------------- Sabar Raikoti 905-509-5099 419-914-9144 |
Oh,
yeah, he was a nice guy. I gotta help him, let me call him.
There will be many situations; when you may remember one of the
properties associated with a contact and then you can get hold of his/her
number. Your friend’s friend once met with you and he was talking about some
vacancies that are about to open at his workplace. He actually liked chat with
you and had promised to recommend your name. They have a reputed setup and just
yesterday you heard that S & R Software is hiring. How will you get to him because your friend
has gone back home for a long break?
|
1 row created. |
Oh yes, you could hardly remember that you had
entered this fact in the database (Table). Your database was new and you were
really interested to enter everything if you could. You only remember
‘promised’ word and nothing else. Let us try.
|
Select Fname,Lname,Phone,Cell
from address where
mname like (‘%promised%’); |
|
SQL> Select
Fname,Lname,Phone,Cell from address where mname like ('%promised%'); no rows
selected |
Well don’t despair. Give another try.
|
Select
Fname,Lname,Phone,Cell from address where addr like (‘%promised%’); SQL> Select
Fname,Lname,Phone,Cell from address where addr like ('%promised%'); FNAME LNAME PHONE CELL ----------------
---------------- --------------- --------------- John walker 419-932-9322 647-417-4879 |
You were lucky, because Oracle
had helped? No; lucky, because you had entered something in your table. Now you
have his name and go ahead call him, he liked chat with you.
The best thing about database (or
data table) is that once you create it, you can keep adding new contact for ever.
There are simple setups to double check that the data that you are entering is
valid. I mean no mistake or error. This is done at database level and you do
not need to develop the software. Triggers and some constraints will help us do
some sanity checks. We will talk about triggers and other constraints in next
part.
Actually, for our walk through the
table ADDRESS is ready. But I believe that, because we have a hi-fi database
system, why not try some house-keeping kind of things. I mean few features that
allow us to maintain our database in top position. We are ok for now and will
discuss Triggers and some of constraints in next part.
No comments:
Post a Comment