Transcript Lecture 4

MIS309 Database Systems
Creating and Maintaining
Database Objects
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
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;
46
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;
47
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;
48
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;
49
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;
50
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)
51
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;
52
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;
53
Creating Alternate Column
Headings in SQL*Plus
• Syntax:
SELECT column1 “heading1”, column2
“heading2”, …
• Example:
SELECT (SYSDATE – s_dob) “Student Age”
FROM my_students;
54
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
55
Dynamic SQL Queries
• Queries that allow users to specify
search conditions at runtime
• Approaches
• Substitution Values
• Runtime Variables
56
Using Substitution Values
• Created when search expression is
prefaced with an ampersand (&)
• System then prompts user for value
57
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
58
Using Runtime Variables
• Example:
59
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’);
60
Join Queries
• Retrieve data from multiple tables by
joining tables using foreign key
references
• Join query types:
•
•
•
•
Inner (equality)
Outer
Self
Inequality
61
Inner Joins
• One record is retrieved for each
matching row
62
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
63
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
64
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
65
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
66
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
67
Outer Joins

Student 105 (Michael Connoly) does not
have any ENROLLMENT records
68
Outer Joins
• No records retrieved for Michael:
69
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
70
Outer Joins
Outer join marker
71
Self Joins
• Used to join a table to itself when the
table has a hierarchical relationship
72
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
73
PARENT_PROJECT
Self Joins
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
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
74
Self Join Example
75
Inequality Joins
• Join created by placing making join
condition satisfy an inequality
condition
76
Inequality Joins
77
Nested Queries
• Created when a sub-query is nested
within a main query
• Main query: first query listed in SELECT
command
• Sub-query: retrieves one or more values
that specify the main query’s search
condition
78
Nested Query Where
Sub-query Returns a Single Value
• Syntax:
SELECT column1, column2, …
FROM table1, table2, …
WHERE join conditions
AND search_column1 = (SELECT column1
FROM table1, table2, …
Sub-query
WHERE search and
that returns
join conditions)
one value
79
Nested Query Where
Sub-query Returns Multiple Values
• Syntax:
SELECT column1, column2, …
FROM table1, table2, …
WHERE join conditions
AND search_column1 IN (SELECT column1
FROM table1, table2, …
Sub-query
WHERE search and
that returns
join conditions)
multiple
values
80
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
81
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
82
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
83
Selecting Records For Update
• Syntax:
SELECT column1, column2, …
FROM table1, table2, …
WHERE search and join conditions
FOR UPDATE OF column1, column2, …
NOWAIT;
84
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”
85
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
86
Database Views
• Creating a view:
CREATE VIEW view_name AS
SQL_command;
• Views can be queried just like tables:
SELECT *
FROM view_name;
87
Simple Views
• Based on SQL query that retrieves data
from only one table
• View can support all table operations:
• INSERT
• UPDATE
• DELETE
88
Complex Views
• Based on query that retrieves data from
multiple tables
• Can only be used to support SELECT
operations
• No table operations supported
89
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
90
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
91
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
92
Creating Indexes
• Syntax:
CREATE INDEX index_name
ON tablename(index_field);
93
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
94
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
95
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;
96
End of Lecture
Next Topic
Reporting Techniques and Strategies