Transcript Lesson-A

ITBIS373 Database
Development
Lecture 3a - Chapter 3:
Using SQL Queries to Insert,
Update, Delete, and View Data
Lesson A Objectives
After completing this lesson, you should be able to:
 Run a script to create database tables automatically
 Insert data into database tables
 Create database transactions and commit data to the
database
 Create search conditions in SQL queries
 Update and delete database records and truncate
tables
Guide to Oracle 10g
2
Lesson A Objectives
(continued)


Create and use sequences to generate
surrogate key values automatically
Grant and revoke database object privileges
Guide to Oracle 10g
3
Using Scripts to Create
Database Tables

Script


Text file that contains one or more SQL
commands
Run a script

Type start at SQL prompt

Blank space
Full path and filename of script file

Guide to Oracle 10g
4
SQL Scripts

Running a script:
SQL> START path_to_script_file;

Path cannot contain any blank spaces
Guide to Oracle 10g
5


Using SQL*PLUS to Insert Data
After successfully running the script to create the tables, you
already to begin adding data to them. In business setting,
programs called forms are used to automate the data entry
process. Program developers who create forms often use the
SQL INSERT statement in the form program code to insert data
into tables.
Guide to Oracle 10g
6
Using the INSERT Command

Basic syntax for inserting into every column:
INSERT into tablename
VALUES (column1_value, column2_value,
… );

Basic syntax for inserting into selected
columns
INSERT into tablename (columnname1,
columnname2, … );
VALUES (column1_value, column2_value,
… );
Guide to Oracle 10g
7
Using the INSERT Command
(continued)

Ensure all foreign keys that new row
references have already been added to
database
Guide to Oracle 10g
8
Guide to Oracle 10g
9
Guide to Oracle 10g
10
Inserting Selected Table Fields
Guide to Oracle 10g
11
Format Models



Also called format mask
Used to specify different output format from
default
For NUMBER data types


9 represents digit
For DATE/TIMESTAMP data types

Choose formats for year day, date, etc.
Guide to Oracle 10g
12
Inserting Date and Interval
Values

Inserting values into DATE columns


Use TO_DATE function to convert string to DATE
Syntax


TO_DATE('date_string',
'date_format_model')
Inserting values into INTERVAL columns

Syntax


TO_YMINTERVAL('years-months')
TO_DSINTERVAL('days HH:MI:SS.99')
Guide to Oracle 10g
13
Inserting Date Values
Guide to Oracle 10g
14
Guide to Oracle 10g
15
Guide to Oracle 10g
16
Guide to Oracle 10g
17
Creating Transactions and
Committing New Data



When you create a new table or update the structure of an
existing table, the DBMS changes the rows immediately and
makes the change visible to other users. This is not the case
when you insert, update, or delete data rows. The commands for
operations that add, update, or delete data are called action
queries.
All three action queries need to succeed, or non of them should
succeed.
After the user enters all of the action queries in a transaction, he
or she can either COMMIT (save) all of the changes or ROLL
BACK (discard) all of the changes
Guide to Oracle 10g
18


The purpose of transaction processing is enable every user to
see a consistent view of the database. To achieve this
consistency, a user cannot view or update data values that are
part of another user’s uncommitted transaction because these
uncommitted transactions, which are called pending
transactions, might be rolled back.
The Oracle DBMS implements transaction processing by locking
data rows associated with pending transactions. When the
DBMS locks a row, other users cannot view or modify the row.
When the user commits the transaction, the DBMS releases the
lock on the rows, and other users can view and update the rows
again.
Guide to Oracle 10g
19
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;
Guide to Oracle 10g
20
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
Guide to Oracle 10g
21
Guide to Oracle 10g
22
Savepoint


Used to mark
individual
sections of a
transaction
You can roll back
a transaction to a
savepoint
Guide to Oracle 10g
23
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
Guide to Oracle 10g
24
Guide to Oracle 10g
25
Search Conditions

The general syntax of a SQL search condition
is:
WHERE columnname
comparison_operator
search_expression
Guide to Oracle 10g
26
Guide to Oracle 10g
27
Defining Search Expressions

NUMBER example


Character data example


WHERE f_id = 1
WHERE s_class = 'SR'
DATE example

WHERE s_dob = TO_DATE('01/01/1980',
‘MM/DD/YYYY')
Guide to Oracle 10g
28
Guide to Oracle 10g
29
Creating Complex Search Conditions






A complex search condition combines multiple search conditions using
the AND,OR, and NOT logical operators.
EX
The following complex search condition matches all rows in which
BLDG_CODE is ‘CR’ and the capacity is greater than 50:
WHERE bldg_code =‘CR’ AND capacity >50
The following search condition matches all course section rows that
meet either on Tuesday and Thursday or on Monday, Wednesday, and
Friday (at Northwoods University):
WHERE day=‘MW’ OR day =‘UTH’
Guide to Oracle 10g
30
Deleting Table Rows

You use the SQL DELETE action to remove specific
rows from a database table, and you truncate the
table to remove all of the table rows.
The SQL DELETE Action Query
The general syntax for DELETE action query is:
DELETE FROM tablename
WHERE search condition;
EX
In the following set of steps, you delete Tammy Jones from the STUDENT
table. You specify ‘Tammy’ and ‘Jones’ in the action query’s search
condition.
Guide to Oracle 10g
31
Guide to Oracle 10g
32
Deleting Records



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
Guide to Oracle 10g
33
Truncating Tables

When you need to delete all of the rows in a
table quickly, you can truncate the table,
which means you remove all of the table data
without saving any rollback information.
TRUNCATE TABLE tablename;

You cannot truncate a table that has foreign key
constraints enabled.
Guide to Oracle 10g
34

Ex: You truncate the LOCATION table to
delete all of its rows. Recall that the LOC_ID
column in the LOCATION table is a foreign
key in both the FACULTY table and the
COURSE_SECTION table, so you must first
disable the LOC_ID foreign key constraints in
the FACULTY and COOURSE_SECTION
tables. Then you truncate the LOCATION
table.
Guide to Oracle 10g
35
Guide to Oracle 10g
36
Sequences




Sequential list of numbers that is
automatically generated by the database
Used to generate values for primary key
identifier
Has no real relationship to row to which it is
assigned other than to identify it uniquely
Surrogate key values automatically
generated using a sequence.
Guide to Oracle 10g
37
Guide to Oracle 10g
38
Creating New Sequences

CREATE SEQUENCE command


DDL command
No need to issue COMMIT command
Guide to Oracle 10g
39
General Syntax Used to Create
a New Sequence
Guide to Oracle 10g
40
Guide to Oracle 10g
41
Viewing Sequence Information

Query the SEQUENCE Data Dictionary View:
Guide to Oracle 10g
42
Using Sequences

CURRVAL


Returns most recent sequence value retrieved
during the current user session.
NEXTVAL


Next available sequence value
sequence_name.NEXTVAL
Guide to Oracle 10g
43
Guide to Oracle 10g
44
Using Sequences (continued)

DUAL


Simple table in system user schema
More efficient to retrieve pseudocolumns from
DUAL
SELECT sequence_name.NEXTVAL
FROM DUAL;

DBMS uses user sessions

To ensure that all sequence users receive unique
sequence numbers
Guide to Oracle 10g
45
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
Guide to Oracle 10g
46
Pseudocolumn Examples
Pseudocolumn
Name
Output
CURRVAL
Most recently retrieved
sequence value
NEXTVAL
Next value in a sequence
SYSDATE
Current system date from
database server
USER
Username of current user
Guide to Oracle 10g
47
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
Guide to Oracle 10g
48
EX










To create the ITEMID_SEQUENCE
SQL> CREATE SEQUENCE itemid_sequence
START WITH 996
NOMAXVALUE
NOCACHE;
sequence created
To access the next value in a sequence and use that value when
you insert a new data record, use the following general command:
INSERT INTO <table name>
VALUES(<owner user name>_<sequence name>.NEXTVAL,<field2
data value>,<field3 data value>,…
This command assumes that the primary key associated with
the sequence is the first table field.
Guide to Oracle 10g
49











SQL> INSERT INTO item
VALUES(itemid_sequence.NEXTVAL,
’Heavey duty day pack’, ‘outdoor gear’);
SQL> Select itemid, itemdesc
From item;
ITEMID
ITEMDESC
894
------897
------559
------786
-----996
Heavy duty day pack
Guide to Oracle 10g
50













SQL > Insert into item
values(item_sequence.nextval,’mountain parka’,’clothing’)
ITEMID
ITEMDESC
894
------897
------559
------786
-----996
Heavy duty day pack
997
Mountain parka
SQL> select itemid_sequence.nextval
from dual;
Nextval
998
Guide to Oracle 10g
51
Deleting Sequences



To delete a sequence from the database, you
use the DROP SEQUENCE DDL command.
Ex: to drop LOC_ID_SEQUENCE, you use
the command
DROP SEQUENCE loc_id_sequence;
Guide to Oracle 10g
52
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, …;
Guide to Oracle 10g
53
Examples of Object Privileges
Privilege
Description
ALTER
Allows user to change object’s
structure using the ALTER
command
DROP
Allows user to drop object
SELECT
Allows user to view object
INSERT, Allows user to insert, update, delete
UPDATE, table data
DELETE
ALL
Allows user to perform any
operation on object
Guide to Oracle 10g
54
Guide to Oracle 10g
55
Guide to Oracle 10g
56
Guide to Oracle 10g
57