Transcript Chapter 3 - Emunix Emich

Enhanced Guide to Oracle8i
Chapter 3:
Using Oracle to Add, View,
and Update Data
1
SQL Scripts
 Script: text file that contains a
sequence of SQL commands
 Running a script:
SQL> START path_to_script_file;
 Path cannot contain any blank spaces
2
Inserting a Value Into Every
Field in a Record
 Syntax:
INSERT INTO tablename VALUES
(column1_value, column2_value, …);
 You must insert a value or a NULL
placeholder for every field
 Fields must be entered in the order
they appear in the table when you
issue the DESCRIBE command
3
Inserting a Value Into Every
Field in a Record

Example:
4
Inserting Selected Table Fields
 Command to insert values for selected
record fields:
INSERT INTO tablename
(column1_name, column2_name, …)
VALUES
(column1_value, column2_value, …);
5
Inserting Selected Table Fields
 Example:
6
Format Masks


All data is stored in the database in a
standard binary format
Format masks are alphanumeric text
strings that specify the format of input
and output data



Table 3-1: Number format masks
Table 3-2: Date format masks
Table 3-3: Character format masks
7
Inserting Date Values
 Date values must be converted
from characters to dates using the
TO_DATE function and a format
mask
 Example:
8
Inserting Text Data
 Must be enclosed in single quotes
 Is case-sensitive
 To insert a string with a single quote,
type the single quote twice
 Example:
'Mike''s Motorcycle Shop'
9
Transactions
 Transaction
 Logical unit of work consisting of one or
more SQL DML commands
 INSERT, UPDATE, DELETE
 All transaction commands must succeed or
none can succeed
 Transaction results are not visible to
other users until they are “committed”
to the database
 Until a transaction is committed, it can
easily be “rolled back” (undone)
10
Transactions
 A transaction starts when you type
one or more DML commands in
SQL*Plus
 A transaction ends when you issue
either the COMMIT or ROLLBACK
command
SQL>COMMIT;
SQL>ROLLBACK;
11
Committing and Rolling Back
Data

COMMIT


Makes transaction command changes
permanent in the database and visible to
other users
ROLLBACK

Rolls back transaction command changes
and restores database to its state before
the transaction
12
Savepoints
 Used to mark
individual
sections of a
transaction
 You can roll
back a
transaction to a
savepoint
13
Updating Records
 Syntax:
UPDATE tablename
SET column1 = new_value,
column2 = new_value, …
WHERE search_condition;
 Records can be updated in only one
table at a time
 Can update multiple records if they all
match the search condition
14
Search Conditions
 Format:
WHERE fieldname operator expression
 Operators









Equal (=)
Greater than, Less than (>, <)
Greater than or Equal to (>=)
Less than or Equal to (<=)
Not equal (< >, !=, ^=
LIKE
BETWEEN
IN
NOT IN
15
Search Condition Examples
WHERE s_name = ‘Sarah’
WHERE s_age > 18
WHERE s_class <> ‘SR’
 Text in single quotes is case sensitive
16
Deleting Records
 Syntax:
DELETE FROM tablename
WHERE search_condition;
 Deletes multiple records if search
condition specifies multiple records
 If search condition is omitted, all table
records are deleted
 You can’t delete a record if it contains a
primary key value that is referenced as
a foreign key
17
Truncating Tables

Removes all table data without saving
any rollback information



Advantage: fast way to delete table data
Disadvantage: can’t be undone
Syntax:
TRUNCATE TABLE tablename;
18
Sequences
 Sequential list of numbers that is
automatically generated by the
database
 Used to generate values for
surrogate keys
19
Creating Sequences
 Syntax:
CREATE SEQUENCE sequence_name
[optional parameters];
 Example:
CREATE SEQUENCE f_id_sequence
START WITH 200;
20
Viewing Sequence Information

Query the SEQUENCE Data Dictionary
View:
21
Pseudocolumns



Acts like a column in a database query
Actually a command that returns a
specific values
Used to retrieve:



Current system date
Name of the current database user
Next value in a sequence
22
Pseudocolumn Examples
Pseudocolumn Output
Name
CURRVAL
Most recently retrieved
sequence value
NEXTVAL
Next value in a sequence
SYSDATE
Current system date from
database server
USER
Username of current user
23
Using Pseudocolumns
 Retrieving the current system date:
SELECT SYSDATE
FROM DUAL;

Retrieving the name of the current
user:
SELECT USER
FROM DUAL;
 DUAL is a system table that is used
with pseudocolumns
24
Using Pseudocolumns
With Sequences
 Accessing the next value in a sequence:
sequence_name.NEXTVAL
 Inserting a new record using a
sequence:
INSERT INTO my_faculty VALUES
(f_id_sequence.nextval, ‘Professor Jones’);
25
Object Privileges


Permissions that you can grant to other users
to allow them to access or modify your
database objects
Granting object privileges:
GRANT privilege1, privilege2, …
ON object_name
TO user1, user 2, …;

Revoking object privileges:
REVOKE privilege1, privilege2, …
ON object_name
FROM user1, user 2, …;
26
Examples of Object Privileges
Object Type
Privilege
Description
Table,
Sequence
ALTER
Allows user to change object’s structure
using the ALTER command
Table,
Sequence
DROP
Allows user to drop object
Table,
Sequence
SELECT
Allows user to view object
Table
INSERT,
UPDATE,
DELETE
Allows user to insert, update, delete table
data
Any database
object
ALL
Allows user to perform any operation on
object
27
Granting and Revoking Object
Privileges
28
Retrieving Data From
a Single Table
 Syntax:
SELECT column1, column2, …
FROM tablename
WHERE search_condition;
29
Retrieving Data From
a Single Table
 To retrieve every column in a table:
SELECT * FROM …
 To retrieve every record in a table,
omit the search condition
SELECT column1, column2, …
FROM tablename;
30
Qualifying Table Names

If you retrieve data from a table that is
owned by another user, you must
qualify the table name by prefacing it
with the owner’s name
31
Suppressing Duplicate Records
 Sometimes queries retrieve
duplicate records
 To suppress duplicate
outputs, use the DISTINCT
qualifier:
SELECT DISTINCT
column1, column2, …
FROM ...
32
Using Multiple Search
Conditions
 Combining search conditions
 AND: both conditions must be true
 OR: either condition can be true
 Combining AND and OR in a single
operation
 AND comparisons are evaluated first
 Always use parentheses to force
conditions to be evaluated in the correct
order
33
Searching for NULL Records
 NULL: not defined
 Use IS NULL search condition
SELECT s_name, s_class
FROM my_students
WHERE s_class IS NULL;
34
Searching for
NOT NULL Records
 Use IS NOT NULL operator
SELECT s_name, s_age
FROM my_students
WHERE s_class IS NOT NULL;
35
Using the IN and NOT IN
Operators

IN retrieves all values where the search
column value matches a set of values
SELECT *
FROM enrollment
WHERE grade IN (‘A’, ‘B’);
36
Using the IN and NOT IN
Operators

NOT IN retrieves all values where the
search column value matches a set of
values
SELECT *
FROM enrollment
WHERE grade NOT IN (‘A’, ‘B’);
37
Using the LIKE Operator

Performs inexact searches by matching
part of a character string
WHERE fieldname LIKE character_string;
38
Using the LIKE Operator

Character string must be in single quotes and
use wildcard characters




% represents multiple wildcard characters
_ represents a single wildcard character
Wildcard characters can be placed at beginning or
end of string
Examples:
WHERE s_class LIKE ‘_R’;
WHERE s_name LIKE ‘J%’;
39
Sorting Query Output
 Use the ORDER BY clause
 Specify sort key, which is column by
which output is sorted
SELECT s_name, s_age
FROM my_students
ORDER BY s_age;
40
Sorting Query Data
 Default sort order
 Numerical: ascending
 Character: A - Z
 Date: oldest - newest
 To force the sort order: use ASC or DESC
 Example
SELECT s_name, s_age
FROM my_students
ORDER BY s_age DESC;
41
Using Calculations in Queries
 Arithmetic operations on retrieved data




Addition (+)
Subtraction (-)
Multiplication (*)
Division (/)
 Example:
SELECT inv_id, qoh*price
FROM inventory;
42
Single-Row
Number Functions
 ABS - absolute value
 CEIL – rounds a number up to the next integer
 FLOOR – rounds a number down to the previous
integer
 MOD – returns the remainder of a number and a
divisor
 POWER - raises a number to an exponent
 ROUND - rounds a number
 SQRT – returns the square root of a value
 TRUNC - truncates a number to the nearest whole
number
43
Using Single-Row
Number Functions
 Example:
SELECT s_name,
TRUNC((SYSDATE - s_dob)/365)
FROM my_students;
44
Single-Row
Character Functions
 CONCAT – joins 2 character strings
 INITCAP – returns a string with the initial letter only
uppercase
 LENGTH – returns the length of a string
 LPAD, RPAD – returns a string with a specific
number of characters added on the left or right side
 LTRIM, RTRIM – returns a string with all instances of
a specific character trimmed from the left or right
side
 REPLACE – replaces all instances of a character with
another character
 UPPER/LOWER – returns a string in all upper/lower
case letters
45
Using Single-Row
Character Functions
 Example:
SELECT UPPER(s_name)
FROM my_students;
46
Date Arithmetic
 To find a date that is a specific
number of days before or after a
known date, add or subtract the
number from the known date
 Example:
SELECT order_date + 30
FROM cust_order;
47
Date Arithmetic
 To find the number of days
between two known dates,
subtract the later date from the
earlier date
 Example:
SELECT SYSDATE – s_dob
FROM my_students;
48
Date Functions
 ADD_MONTHS
 returns a date that is a specific number of
months after a given date
 Example:
SELECT ADD_MONTHS(SYSDATE, 6)
FROM dual;
49
Date Functions
 LAST_DATE
 Returns the date that is the last day
of the month specified in the current
date
 Example:
SELECT LAST_DATE(order_date)
FROM cust_order
WHERE order_id = 1057;
50
Date Functions
 MONTHS_BETWEEN
 Returns the number of months
between two input dates
 Example:
SELECT MONTHS_BETWEEN(order_date,
SYSDATE)
FROM cust_order
WHERE order_id = 1057;
51
Group Functions
 Used to perform an operation on a
field from a group of retrieved records





AVG (average of all retrieved values)
COUNT (number of records retrieved)
MAX (maximum value retrieved)
MIN (minimum value retrieved)
SUM (sum of all retrieved values)
52
Group Function Examples
SELECT
SELECT
SELECT
SELECT
AVG
MAX
MIN
SUM
(s_age)
(s_age)
(s_age)
(s_age)
FROM
FROM
FROM
FROM
my_students;
my_students;
my_students;
my_students;
53
Using the GROUP BY Clause
 GROUP BY must be used if some columns in
the SELECT clause are used in a group
function and some are not
 Group all fields that are not included in the
group function
 Example:
SELECT s_class, AVG(s_age)
FROM my_students
GROUP BY s_class;
54
Creating Alternate Column
Headings in SQL*Plus

Syntax:
SELECT column1 “heading1”, column2
“heading2”, …

Example:
SELECT (SYSDATE – s_dob) “Student Age”
FROM my_students;
55
Creating a Column Alias


Column alias: alternate column name
that can be referenced in the ORDER
BY and GROUP BY clauses
Syntax:
SELECT column1 AS alias1 …
 Example:
SELECT (SYSDATE – s_dob) AS age_alias
ORDER BY age_alias
56
Dynamic SQL Queries


Queries that allow users to specify
search conditions at runtime
Approaches


Substitution Values
Runtime Variables
57
Using Substitution Values


Created when search expression is
prefaced with an ampersand (&)
System then prompts user for value
58
Using Runtime Variables

Runtime variable: variable defined
in SQL*Plus environment

Syntax:
DEFINE variable_name = variable_value;

You can then substitute the variable
name for a query search condition
value
59
Using Runtime Variables

Example:
60
Formatting Data Using the
TO_CHAR Function


Used to display NUMBER and DATE values
using a specific format mask
Syntax:
TO_CHAR(fieldname, ‘format_mask’);
61
Join Queries


Retrieve data from multiple tables by
joining tables using foreign key
references
Join query types:




Inner (equality)
Outer
Self
Inequality
62
FACULTY
Inner Joins

One record is retrieved for each
matching row
63
Inner Joins
 Syntax:
SELECT column1, column2, …
FROM table1, table2
WHERE table1.join_column =
table2.join_column
Join condition
 You must include a join condition for every
link between 2 tables
64
Inner Joins
 Example:
SELECT s_name, f_name
FROM student, faculty
WHERE student.f_id =
faculty.f_id;
 If you have N tables in the FROM
clause, you must have (N - 1) join
conditions
65
Qualifying Field Names

If a field in the SELECT clause exists in
multiple tables in the FROM clause, you
must qualify the field name by prefacing
it with either table’s name
66
Process for Designing
Complex Inner Join Queries
1. Identify all of the tables involved in the
query, and label:



Display fields
Join fields
Search fields
2. Write the query




List
List
List
List
all
all
all
all
display fields in the SELECT clause
table names in the FROM clause
join condition links in the WHERE clause
search fields in the WHERE clause
67
Outer Joins


Limitation of inner joins: some records
may be omitted if corresponding
records don’t exist in one of the tables
Example: retrieve records for all
students, along with their
corresponding ENROLLMENT
information
68
Outer Joins

Student 105 (Michael Connoly) does not
have any ENROLLMENT records
69
Outer Joins

No records retrieved for Michael:
70
Outer Joins

To include records in first (inner) table,
even when they do not have matching
records in second (outer) table, place
outer join marker (+) beside outer table
name in join clause
71
Outer Joins
Outer join marker
72
Self Joins

Used to join a table to itself when the
table has a hierarchical relationship
73
Self Joins


To create a self-join, you need to create
a table alias, which gives an alternate
name to the table so you can create a
join condition
Syntax to create table alias in FROM
clause:
FROM table1 alias1, table2 alias2
74
PARENT_PROJECT
P_ID
Self Joins
PROJECT_NAME
1 Hardware Support Intranet
CLIENT_ID
MGR_ID
PARENT_P_ID
2
105
2 Hardware Support Interface
2
103
1
3 Hardware Support Database
2
102
1
4 T eller Support System
4
105
5 Internet Advertising
6
105
6 Network Design
6
104
7 Exploration Database
5
102
5
SUB_PROJECT
P_ID
PROJECT_NAME
1 Hardware Support Intranet
CLIENT_ID
MGR_ID
PARENT_P_ID
2
105
2 Hardware Support Interface
2
103
1
3 Hardware Support Database
2
102
1
4 T eller Support System
4
105
5 Internet Advertising
6
105
6 Network Design
6
104
7 Exploration Database
5
102
5
PROJECT
P_ID
PROJECT_NAME
1 Hardware Support Intranet
CLIENT_ID
MGR_ID
PARENT_P_ID
2
105
2 Hardware Support Interface
2
103
1
3 Hardware Support Database
2
102
1
4 T eller Support System
4
105
5 Internet Advertising
6
105
6 Network Design
6
104
7 Exploration Database
5
102
5
75
Self Join Example
76
Inequality Joins


Join created by placing making join
condition satisfy an inequality
condition
Only makes sense when
primary/foreign key values are not
surrogate keys
77
Inequality Joins


Join created by placing making join
condition satisfy an inequality
condition
Only makes sense when
primary/foreign key values are not
surrogate keys
78
Inequality Joins
79
Nested Queries

Created when a subquery is nested
within a main query


Main query: first query listed in SELECT
command
Subquery: retrieves one or more values
that specify the main query’s search
condition
80
Nested Query Where
Subquery Returns a Single Value

Syntax:
SELECT column1, column2, …
FROM table1, table2, …
WHERE join conditions
AND search_column1 = (SELECT column1
FROM table1, table2, …
Subquery
WHERE search and
that returns
join conditions)
one value
81
Nested Query Where
Subquery Returns Multiple Values

Syntax:
SELECT column1, column2, …
FROM table1, table2, …
WHERE join conditions
AND search_column1 IN (SELECT column1
FROM table1, table2, …
Subquery
WHERE search and
that returns
join conditions)
multiple
values
82
Using Set Operators in Queries


Performs set operations on outputs of
two unrelated queries
Both queries must have:


same number of display fields
corresponding display fields must have
same data type
83
Query Set Operators




UNION: combines results, suppresses
duplicate rows
UNION ALL: combines results, displays
duplicates
INTERSECT: finds matching rows
MINUS: returns the difference between
returned record sets
84
Selecting Records For Update


In a normal SELECT command, the retrieved
records are not locked, and are available for
other users to view, updated, and delete
Sometimes, you need to select records, and
then immediately update them based on the
retrieved values


Airline seat reservations
Inventory items for sale
85
Selecting Records For Update

Syntax:
SELECT column1, column2, …
FROM table1, table2, …
WHERE search and join conditions
FOR UPDATE OF column1, column2, …
NOWAIT;
86
Selecting Records For Update

All retrieved records are locked until you
issue a COMMIT command


Fields listed in FOR UPDATE clause are for
documentation purposes only
NOWAIT clause is optional

Makes it so when another user tries to
retrieved locked record, their system
doesn’t just “hang”
87
Database Views




Logical table based on a query
Does not physically exist in the
database
Presents data in a different format from
underlying tables
Uses:


Security
Simplifying complex queries
88
Database Views


Creating a view:
CREATE VIEW view_name AS
SQL_command;
Views can be queried just like tables:
SELECT *
FROM view_name;
89
Simple Views


Based on SQL query that retrieves data
from only one table
View can support all table DML
operations:



INSERT
UPDATE
DELETE
90
Complex Views


Based on query that retrieves data from
multiple tables
Can only be used to support SELECT
operations

No DML operations supported
91
Indexes

Index: Separate table
is maintained that
shows index keys and
physical locations of
corresponding records


In Oracle, ROWID is
translated to physical
location of row on disk
Improves response time
of searches and joins
SLName
ROWID
Brown
13387289
Jones
13879872
Smith
58925789
Helgeson
29875018
92
Using Indexes

Create table index AFTER table is
populated with data

Indexes make INSERT, UPDATE, and DELETE
operations slower because index must also be
maintained
93
Indexing Strategies

A table can have indexes on multiple fields

Create indexes based on fields used for search
or join operations



Typically, indexes only speed retrievals when <15% of
the table records are involved
Each additional index adds processing
overhead for INSERT, UPDATE, and DELETE
operations
In Oracle, primary keys are automatically
indexed
94
Creating Indexes

Syntax:
CREATE INDEX index_name
ON tablename(index_field);
95
Synonyms


Alternate name for a table
Allows you to not have to preface table
with owner’s username when you are
querying a table that belongs to
another user
96
Public Synonyms


Can only be created by a DBA
Syntax:
CREATE PUBLIC SYNONYM synonym_name
FOR owner_name.tablename;

All users with privileges to use table can
then use synonym instead of
owner_name.tablename
97
Private Synonyms



You can create private synonyms for
any tables that you have privileges to
use
Only you can use the synonym
Syntax:
CREATE SYNONYM synonym_name
FOR table_name.table_name;
98