Transcript Database Objects

Database Systems
Creating and Maintaining
Database Objects
Part 1
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 DESC 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
Inserting Date Values
 Date values must be converted
from characters to dates using the
TO_DATE function and a format
mask
 Example:
7
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'
8
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)
9
Transactions
 A transaction starts when you type one
or more commands in SQL*Plus
 A transaction ends when you issue either
the COMMIT or ROLLBACK command
SQL>COMMIT;
SQL>ROLLBACK;
10
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
11
Savepoints
 Used to mark
individual
sections of a
transaction
 You can roll
back a
transaction to
a savepoint
12
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
13
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
14
Search Condition Examples
WHERE s_name = ‘Sarah’
WHERE s_age > 18
WHERE s_class <> ‘SR’
 Text in single quotes is case sensitive
15
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
16
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;
17
Sequences
 Sequential list of numbers that is
automatically generated by the
database
 Used to generate values for
surrogate keys
18
Creating Sequences
 Syntax:
CREATE SEQUENCE sequence_name
[optional parameters];
 Example:
CREATE SEQUENCE f_id_sequence
START WITH 200;
19
Viewing Sequence Information
• Query the SEQUENCE Data Dictionary
View:
20
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
21
Pseudocolumn Examples
Pseudocolumn Output
Name
CURRVAL
Most recently retrieved
sequence value
NEXTVAL
Next value in a sequence
SYSDATE
USER
Current system date from
database server
Username of current user
22
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
23
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’);
24
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, …;
25
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
26
Granting and Revoking Object
Privileges
27
Retrieving Data From
a Single Table
 Syntax:
SELECT column1, column2, …
FROM tablename
WHERE search_condition;
28
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;
29
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
30
Suppressing Duplicate Records
 Sometimes queries retrieve
duplicate records
 To suppress duplicate
outputs, use the DISTINCT
qualifier:
SELECT DISTINCT
column1, column2, …
FROM ...
31
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
32
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;
33
Searching for
NOT NULL Records
 Use IS NOT NULL operator
SELECT s_name, s_age
FROM my_students
WHERE s_class IS NOT NULL;
34
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’);
35
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’);
36
Using the LIKE Operator
• Performs inexact searches by matching
part of a character string
WHERE fieldname LIKE character_string;
37
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%’;
38
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;
39
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;
40
Using Calculations in Queries
 Arithmetic operations on retrieved data




Addition (+)
Subtraction (-)
Multiplication (*)
Division (/)
 Example:
SELECT inv_id, qoh*price
FROM inventory;
41
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
42
Using Single-Row
Number Functions
 Example:
SELECT s_name,
TRUNC((SYSDATE - s_dob)/365)
FROM my_students;
43
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
44
Using Single-Row
Character Functions
 Example:
SELECT UPPER(s_name)
FROM my_students;
45
End of Lecture