Home arrow Oracle arrow Page 3 - Working With Oracle on Windows: Part 3

Working With Oracle on Windows: Part 3

This article is a quick summary of basic SQL commands with a little touch of Oracle to prepare for application development.

Author Info:
By: Ben Shepherd
Rating: 4 stars4 stars4 stars4 stars4 stars / 14
March 10, 2003
  1. · Working With Oracle on Windows: Part 3
  2. · Getting ready to use Oracle
  3. · SQL Summary
  4. · Conclusion

print this article

Working With Oracle on Windows: Part 3 - SQL Summary
(Page 3 of 4 )

Idea of the Select Statement

The Select statement is a useful query that allows users to retrieve data from a database. There is an infinite set of commands to retrieve data and a finite set of rules. In basic cases, the sequence of the process to retrieve data –that is the query - is using the three clauses SELECT, FROM and WHERE.

SELECT is the clause that allows users to retrieve data from the query.

FROM is the clause that represents the tables required in the query.

WHERE is the clause that represents a condition that the query must undergo.

But First ...

We wish to extract data from a table that is in a relational database. Here is how this can be done. This file (i.e. bank_example.sql) will be included in the zip file attached to this article.

          v_streetNumber                      NUMBER,
          v_streetName                         VARCHAR2(30),
          v_citySuburb                          VARCHAR2(30),
          v_state                                  VARCHAR2(4),
          v_postCode                            NUMBER

CREATE TABLE account_holder
          v_id                                       NUMBER               PRIMARY_KEY,
          v_firstName                           VARCHAR2(30),
          v_lastName                            VARCHAR(40),
          v_homeAddress                      holder_address,
          v_workAddress                       holder_address,
          v_emailAddress                      VARCHAR2(50)

CREATE TABLE payment_details
          v_id                                               NUMBER       PRIMARY_KEY,
          v_holder_id                                    NUMBER,
          v_amount                                       NUMBER(7,2),
          FOREIGN KEY (v_holder_id) REFERENCES account_holder(v_id)

Notice that v_amount is a seven digit number with 2 decimal places. And that v_holder_id is linked to the v_id column in account_holder table. We now have a relational database.

Use of Sequences and Triggers

Let us put values in to these tables, but first, we will create a trigger that allows the primary key to be unique. To do this we need a sequence. So let’s create one.

create sequence primary_seq_for_uniqueness
start with 1
increment by 1

This will let us have an auto increment like sequence called primary_seq_for_uniqueness (I’d call it something smaller in length so there is less chance for typing errors in the code). This sequence you have now created is important for the trigger which will allow us to make sure that we have a unique primary key (i.e. payment). Let’s now create that trigger.

create trigger primary_trig_for_uniqueness
before insert on payment_details
for each row
select primary_seq_for_uniqueness.nextval into :new.v_id from dual;

Ok, we have a trigger that is called before an insert into the payment_details table. The trigger calls the sequence and gets a new number (i.e. 1 more than it’s last value).

Inserting data

Now we insert information into the table as follows (see insert_statement.sql in zip file),

INSERT INTO account_holder VALUES
holder_address(21, ‘Kings Street’, ‘Junkville’, TN, 12345),
holder_address(12, ‘Queens Street’, ‘Klenville’, OR, 54321)

INSERT INTO account_holder VALUES
holder_address(123, ‘Pitts Lane’, ‘Adelaide’, ‘SA’, 1234),
holder_address(321, ‘Fred Forest Parkway’, ‘Reid’, ‘SA’, 4321)

INSERT INTO account_holder VALUES
holder_address(1234, ‘Two Gorges Road’, ‘Jenki’, ‘TO’, 123),
holder_address(4321, ‘Piza Place’, ‘Jenki’, ‘TO’, 321)

INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00001, 100.50);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00001, 2004.65);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00001, 0.90);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00001, 5.65);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00002, 56.20);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00002, 102.00);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00002, 65.90);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00002, 12.60);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00002, 998.75);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00002, 45.50);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00002, 34.70);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00003, 23.20);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00003, 12.95);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00003, 13.45);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00003, 0.05);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00003, 69.75);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00003, 423.25);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00003, 581.15);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00003, 1000.00);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00003, 20.50);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00003, 54.85);

Of course, when we are working with applications we don’t need to re-enter these amounts the app will do it for us … phew!!

The Select Statement

Now we have our tables, let’s use the select statement to retrieve some data. Firstly, recall that the character * is used to indicate “all records”. So if we wish to gather all records of the holders who have paid a deposit that was greater than $1000.00, then the expression would like this

SELECT  account_holder.*
FROM account_holder, payment_detail
WHERE payment_detail.v_amount > 1000.00;

Notice that there is a list of all the tables that were required, i.e. in the FROM clause. Also, the SELECT clause uses the * character. If I wanted to display all records in both tables the SELECT statement would have been,

FROM account_holder, payment_detail
WHERE payment_detail.v_amount > 1000.00;

This statement is without the reference to a particular table in the SELECT clause.

The select statement could be used to view all tables in your system by typing,

SELECT table_name from user_tables;

The SELECT statement is a powerful tool when used correctly, so experiment with it.  You could also use built in Oracle function to produce calculations. For example, to can tally up a SUM, COUNT to number of entries and the list goes on. There are many SQL books out there, so give some of them a read and your pick up the language in no time.

Other Basic SQL Statements

To view certain user created objects,

SELECT sequence_name FROM user_sequences;
SELECT trigger_name FROM user_triggers;
SELECT table_name FROM user_tables;

To delete,

DROP TRIGGER test_trigger;

You could keep the trigger but disable it so it won't automatically populate the id column with every insert and enable it again later if you want (not wise though):


To alter a table by changing a column name simply type

ALTER TABLE payment_details RENAME COLUMN v_amount TO v_deposit;

To update a record

UPDATE account_holder SET id = 00004 WHERE id = 00003;

blog comments powered by Disqus

- Oracle Purchases Taleo to Extend Cloud Compu...
- Partitioning in Oracle. What? Why? When? Who...
- Datatypes Used in Oracle 9i
- Creating a Database in Oracle 9i
- Oracle with Doctor Janusz Getta
- Working With Oracle on Windows: Part 3
- Working With Oracle on Windows: Part 2

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials