|
SQL (Structured Query
Language) in Oracle: SQLPLUS
A. Creating a table, deleting a table and modifying a table's properties
The CREATE TABLE command
create table newspaper (feature varchar2(15) not null, section char(1), page
number);
create table phonelist (name varchar2(15) not null, page number);
create table phonelist (name varchar2(15) not null, phone number);
Table Deletion
drop table phonelist;
Getting Information about the Table
describe newspaper
A descriptive table listing out the columns and their
definitions. Output is obtained in the following format
Name | Null
? | Type
FEATURE NOT
NULL VARCHAR2(15)
SECTION
CHAR(1)
PAGE
NUMBER
Viewing / Modifiying display information for the query output for
a table
show feedback;
set feedback off;
set feedback n;
Feedback indicates the number of rows found in a SELECT
query, in form of a notation displayed for the number of rows selected, shown at
the bottom of the displayed results. The default is 6. This can be turned off
also. n is the
minimum number for this feature to work. The response to the show
query is
FEEDBACK ON for n or more rows
show numwidth;
set numwidth n;
Numwidth is the width used to display numbers. The
default numwidth is 9. If this is too large to display certain types of numbers
eg. 3 digits, then its width can be reduced. using the
set instruction.
Note that ALL Number columns will be changed to n.
There is a separate method to set individual columns. Thus in any case the
numwidth must be set to a value that is greater than n.
B. QUERYING THE DATABASE: Instructions to the Oracle Database are based on the
four following VERBS
SELECT, INSERT, UPDATE or DELETE
Using SQL to SELECT Data from the tables
Four PRIMARY Keywords are used in SQL to select
information: SELECT
& FROM used
on every query and WHERE
states the qualifiers on the information selected. ORDER
BY specifies the order in which the data must
be sorted.
Key Words (Syntax): SELECT
field/s FROM table
WHERE qualifier (AND
qualifier) ORDER BY sorting
order
order by default will returning data in ascending order
order by criteria desc will returning data in descending
order
select * from WEATHER;
select City, Temperature from WEATHER;
select City from WEATHER where Humidity = 89;
select City from WEATHER where Humidity =
89;
select City from WEATHER where Temperature =
66;
select City, Temperature from WEATHER order by Temperature;
select City, Temperature from WEATHER where Temperature > 80
order by Temperature;
select City, Temperature, Humidity from WEATHER where
Temperature > 80 and
Humidity < 70 order by
Temperature;
select City, Temperature from WEATHER order by
Temperature desc;
Single Value Test:
=
(equal), != ^= <>(not
equal), <(less
than), >(greater
than), <=(less than or equal
to), >= (greater than or equal
to)
Pattern Matching Operator: LIKE
_ one space/character
% any number of
spaces/characters
Examples:
Feature LIKE 'Mo%'
Mo are the first two characters
Feature LIKE '_ _ I%'
I is the character in third position
Feature LIKE '%o%o%'
There are two 'o' - at any location, in the expression
Identifying columns where data is missing/present
IS NULL return results
where data is missing
IS NOT NULL return
results where data is present
Examples:
select city, sampledate, precipitation from comfort where precipitation
IS NULL
select city, sampledate, precipitation from comfort where sampledate
IS NOT NULL
Logical Tests - for list/range of values
IN (*)
NOT IN (*)
BETWEEN * AND
NOT BETWEEN * AND *
Convention: * = 'a' for letter; 9 for numerical
Examples:
select city, sampledate, precipitation from comfort where precipitation between
65
and 69
select city, sampledate, precipitation from comfort where precipitation in
( 65 )
select city, sampledate, precipitation from comfort where city between
'asp'
and 'jan'
select city, sampledate, precipitation from comfort where city in
( 'japan' )
select city, sampledate, precipitation from comfort where precipitation not between
65 and
69
select city, sampledate, precipitation from comfort where city not in
( 'japan' )
Combining Logic: Using AND and OR
They can be used in a virtually unlimited number of ways.
However, they must be used carefully to get the results required.
It must be noted that AND is the stronger logical connector,
and binds the logical expression on either side more strongly than OR.
The last example illustrates how parenthesis override the normal preference.
Examples
select feature, selection, page from newspaper where section = 'A' OR section = 'B'
AND page > 2
REMARK: this returns all results from section A and requested results from section B
select feature, selection, page from newspaper where section = 'A' AND page > 2
OR section = 'B'
REMARK: this returns all results from section B and requested results from section A
select feature, selection, page from newspaper where page > 2 AND section = 'A'
OR section = 'B'
REMARK: this returns all results from section B and requested results from section A
select feature, selection, page from newspaper where page > 2 AND
(section = 'A'
OR section = 'B')
REMARK: This returns the required results - requested results from section A and B
|