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.
CREATE OR REPLACE TYPE holder_address AS OBJECT
(
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
nomaxvalue;
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
begin
select primary_seq_for_uniqueness.nextval into :new.v_id from dual;
end;
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
(
00001,
‘I.P’,
‘Daily’,
holder_address(21, ‘Kings Street’, ‘Junkville’, TN, 12345),
holder_address(12, ‘Queens Street’, ‘Klenville’, OR, 54321)
);
INSERT INTO account_holder VALUES
(
00002,
‘Kim’,
‘Short’,
holder_address(123, ‘Pitts Lane’, ‘Adelaide’, ‘SA’, 1234),
holder_address(321, ‘Fred Forest Parkway’, ‘Reid’, ‘SA’, 4321)
);
INSERT INTO account_holder VALUES
(
00003,
‘Boris’,
‘Lungstrom’,
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,
SELECT *
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 SEQUENCE test_seq;
DROP TRIGGER test_trigger;
DROP TABLE account_holder CASCADE CONSTRAINTS;
DROP TABLE payment_detail CASCADE CONSTRAINTS;
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):
ALTER TRIGGER test_trigger DISABLE;
ALTER TRIGGER test_trigger ENABLE;
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;
Next: Conclusion >>
More Oracle Articles
More By Ben Shepherd
/ 11 
