|
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
|