full sql updated
TRANSCRIPT
-
8/8/2019 Full SQL Updated
1/273
Chapter 1 Oracle9i: SQL 1
Chapter 1
Overview of Database Concepts
-
8/8/2019 Full SQL Updated
2/273
Chapter 1 Oracle9i: SQL 2
Chapter Objectives Identify the purpose of a database
management system (DBMS)
Distinguish a field from a record and a
column from a row
Define the three types of relationships that
can exist between entities
-
8/8/2019 Full SQL Updated
3/273
Chapter 1 Oracle9i: SQL 3
Chapter Objectives Identify the problem associated with many-
to-many relationships and the appropriate
solutions
Explain the purpose of normalization
Describe the role of a primary key
Identify partial dependency and transitive
dependency in the normalization process
-
8/8/2019 Full SQL Updated
4/273
Chapter 1 Oracle9i: SQL 4
Chapter Objectives Explain the purpose of a foreign key
Determine how to link data in different
tables through the use of a common field
Explain the purpose of a structured query
language (SQL)
-
8/8/2019 Full SQL Updated
5/273
Chapter 1 Oracle9i: SQL 5
Database Terminology Database logical structure to store
data
Database Management System
(DBMS) software used to create
and interact with the database
-
8/8/2019 Full SQL Updated
6/273
Chapter 1 Oracle9i: SQL 6
Database Components Character
Field
Record
File
-
8/8/2019 Full SQL Updated
7/273
Chapter 1 Oracle9i: SQL 7
Database Components -
Character Basic unit of data
Can be a letter, number, or special symbol
-
8/8/2019 Full SQL Updated
8/273
Chapter 1 Oracle9i:
SQL 8
Database Components - Field A group of related characters
Represents an attribute or characteristic of
an entity
Corresponds to a column in the physical
database
-
8/8/2019 Full SQL Updated
9/273
Chapter 1 Oracle9i:
SQL 9
Database Components - Record A collection of fields for one specific entity
Corresponds to a row in the physical
database
-
8/8/2019 Full SQL Updated
10/273
Chapter 1 Oracle9i:
SQL 10
Database Components - File A group of records about the same type of
entity
-
8/8/2019 Full SQL Updated
11/273
Chapter 1 Oracle9i:
SQL 11
Components Example
-
8/8/2019 Full SQL Updated
12/273
Chapter 1 Oracle9i:
SQL 12
Review of Database Design Systems Development Life Cycle (SDLC)
Normalization
-
8/8/2019 Full SQL Updated
13/273
Chapter 1 Oracle9i:
SQL 13
Systems Development Life Cycle
(SDLC) Systems investigation understanding the
problem
Systems analysis understanding the
solution
Systems design creating the logical and
physical components
-
8/8/2019 Full SQL Updated
14/273
Chapter 1 Oracle9i:
SQL 14
Systems Development Life Cycle
(SDLC) Systems implementation placing
completed system into operation
Systems maintenance and review
evaluating the implemented system
-
8/8/2019 Full SQL Updated
15/273
Chapter 1 Oracle9i:
SQL 15
Relationships
The following relationships can be
included in an E-R Model:
One-to-one
One-to-many
Many-to-many
-
8/8/2019 Full SQL Updated
16/273
Chapter 1 Oracle9i:
SQL 16
One-to-one Relationship Each occurrence of data in one entity is
represented by only one occurrence of data
in the other entity
Example: Each individual has just one
Employee id and each Employee id is
assigned to just one person
-
8/8/2019 Full SQL Updated
17/273
Chapter 1 Oracle9i:
SQL 17
One-to-many Relationship Each occurrence of data in one entity can be
represented by many occurrences of the
data in the other entity
Example: Shashank ---- Employees
-
8/8/2019 Full SQL Updated
18/273
Chapter 1 Oracle9i:
SQL 18
Many-to-many Relationship Data can have multiple occurrences in both
entities
Example: A student can take many classes
and each class is composed of many
students
Can not be included in the physical database
-
8/8/2019 Full SQL Updated
19/273
Chapter 1 Oracle9i:
SQL 19
Normalization Determines required tables and columns for
each table
Multi-step process
Used to reduce or control data redundancy
-
8/8/2019 Full SQL Updated
20/273
Chapter 1 Oracle9i:
SQL 20
Unnormalized DataContains repeating Hotels in the Oct_hotel
table
-
8/8/2019 Full SQL Updated
21/273
Chapter 1 Oracle9i:
SQL 21
First-Normal Form (1NF) Primary key is identified
Repeating groups are eliminated
-
8/8/2019 Full SQL Updated
22/273
Chapter 1 Oracle9i:
SQL 22
First-Normal Form (1NF)Oct_hotel_id is the primary key for Oct_hotel (Not null and Unique).
-
8/8/2019 Full SQL Updated
23/273
Chapter 1 Oracle9i:
SQL 23
Composite Primary Key More than one column is required to
uniquely identify a row
Can lead to partial dependency - a column
is only dependent on a portion of the
primary key
-
8/8/2019 Full SQL Updated
24/273
Chapter 1 Oracle9i:
SQL 24
Second-Normal Form (2NF) Partial dependency must be eliminated
Break the composite primary key intotwo parts, each part representing a
separate table
-
8/8/2019 Full SQL Updated
25/273
Chapter 1 Oracle9i: SQL 25
Second-Normal Form (2NF)Oct_hotel table in 2NF
-
8/8/2019 Full SQL Updated
26/273
Chapter 1 Oracle9i: SQL 26
Third-Normal Form (3NF)Partial and transitive dependency removed.
-
8/8/2019 Full SQL Updated
27/273
Chapter 1 Oracle9i: SQL 27
Summary of Normalization Steps 1NF: eliminate repeating groups, identify
primary key
2NF: table is in 1NF and partial
dependencies eliminated
3NF: table is in 2NF and transitive
dependencies eliminated
-
8/8/2019 Full SQL Updated
28/273
Chapter 1 Oracle9i: SQL 28
Linking Tables Once tables are normalized, make certain
tables are linked
Tables are linked through a common field
A common field is usually a primary key in
one table and a foreign key in the other
table
-
8/8/2019 Full SQL Updated
29/273
Chapter 1 Oracle9i: SQL 29
-
8/8/2019 Full SQL Updated
30/273
Chapter 1 Oracle9i: SQL 30
Structured Query Language
(SQL) Data sublanguage
Used to:
Create or modify tables
Add data to tables
Edit data in tables
Retrieve data from tables
-
8/8/2019 Full SQL Updated
31/273
Chapter 1 Oracle9i: SQL 31
Chapter 2
Basic SQL SELECT Statements Select * from oct_hotel;
Select * from dst_destination;
Select * from Top_destination;
-
8/8/2019 Full SQL Updated
32/273
Chapter 1 Oracle9i: SQL 32
Chapter Objectives Distinguish between an RDBMS and an
ORDBMS
Identify keywords, mandatory clauses, and
optional clauses in a SELECT statement
Select and view all columns of a table
Select and view one column of a table
-
8/8/2019 Full SQL Updated
33/273
Chapter 1 Oracle9i: SQL 33
Chapter Objectives Display multiple columns of a table
Use a column alias to clarify the contents of
a particular column
Perform basic arithmetic operations in the
SELECT clause
-
8/8/2019 Full SQL Updated
34/273
Chapter 1 Oracle9i: SQL 34
Chapter Objectives Remove duplicate lists, using either the
DISTINCT or UNIQUE keyword
Combine fields, literals, and other data
Format output
-
8/8/2019 Full SQL Updated
35/273
Chapter 1 Oracle9i: SQL 35
Relational Database Management
System (RDBMS)An RDBMS is the software program used to
create the database and it allows you to
enter, manipulate, and retrieve data
-
8/8/2019 Full SQL Updated
36/273
Chapter 1 Oracle9i: SQL 36
Object Relational Database
Management System (ORDBMS)Same as an RDBMS except it can be used
to reference objects such as maps and object
fields
-
8/8/2019 Full SQL Updated
37/273
Chapter 1 Oracle9i: SQL 37
SELECT Statement Syntax SELECT statements are used to retrieve
data from the database
Syntax gives the basic structure, or rules,
for a command
-
8/8/2019 Full SQL Updated
38/273
Chapter 1 Oracle9i: SQL 38
SELECT Statement SyntaxOptional clauses and keywords are shown in
brackets
-
8/8/2019 Full SQL Updated
39/273
Chapter 1 Oracle9i: SQL 39
SELECT Statement Syntax SELECT and FROM clauses are required
SELECT clause identifies column(s)
FROM clause identifies table(s)
Each clause begins with a keyword
-
8/8/2019 Full SQL Updated
40/273
Chapter 1 Oracle9i: SQL 40
Selecting All Data in a TableSubstitute an asterisk for the column names in a
SELECT clause
Select * from oct_hotel;
-
8/8/2019 Full SQL Updated
41/273
Chapter 1 Oracle9i: SQL 41
Selecting Column from a TableEnter column name in SELECT clause
-
8/8/2019 Full SQL Updated
42/273
Chapter 1 Oracle9i: SQL 42
Operations Within the SELECT
Statement Column alias can be used for column
headings
Perform arithmetic operations
Suppress duplicates
Concatenate data
-
8/8/2019 Full SQL Updated
43/273
Chapter 1 Oracle9i: SQL 43
Column Alias List after column heading
AS keyword is optional
Enclose in double quotation marks:
If it contains blank space(s)
If it contains special symbol(s)
To retain case
-
8/8/2019 Full SQL Updated
44/273
Chapter 1 Oracle9i: SQL 44
Column Alias Example
-
8/8/2019 Full SQL Updated
45/273
Chapter 1 Oracle9i: SQL 45
Arithmetic Operations Executed left to right
Multiplication and division are solved first
Addition and subtraction are solved last
Override order with parentheses
-
8/8/2019 Full SQL Updated
46/273
Chapter 1 Oracle9i: SQL 46
Suppressing DuplicatesEnter DISTINCT or UNIQUE after
SELECT keyword
Select distinct hotel_nme from
oct_hotel;
-
8/8/2019 Full SQL Updated
47/273
Chapter 1 Oracle9i: SQL 47
Concatenation Can combine data with string literal
Use concatenation operator, ||
Allows use of column alias
-
8/8/2019 Full SQL Updated
48/273
-
8/8/2019 Full SQL Updated
49/273
Chapter 1 Oracle9i: SQL 49
Chapter 3
Restricting Rows and Sorting
Data
-
8/8/2019 Full SQL Updated
50/273
Chapter 1 Oracle9i: SQL 50
Chapter Objectives
Use a WHERE clause to restrict the rows
returned by a query
Create a search condition using
mathematical comparison operators
Use the BETWEENAND comparison
operator to identify records within a rangeof values
-
8/8/2019 Full SQL Updated
51/273
Chapter 1 Oracle9i: SQL 51
Chapter Objectives
Specify a list of values for a search
condition using the IN comparison operator
Search for patterns using the LIKE
comparison operator
Identify the purpose of the % and _
wildcard characters
-
8/8/2019 Full SQL Updated
52/273
Chapter 1 Oracle9i: SQL 52
Chapter Objectives
Join multiple search conditions using the
appropriate logical operator
Perform searches for null values
Specify the order for the presentation of
query results, using ORDER BY, DESC,
ASC, and the SELECT clause
-
8/8/2019 Full SQL Updated
53/273
Chapter 1 Oracle9i: SQL 53
WHERE Clause
Used to retrieve rows based on a stated
condition
Requires:
Column name
Comparison operator
Value or column for comparison
Case sensitive
-
8/8/2019 Full SQL Updated
54/273
Chapter 1 Oracle9i: SQL 54
WHERE Clause Example
List WHERE clause after FROM clause
Enclose non-numeric data in single quotes
-
8/8/2019 Full SQL Updated
55/273
Chapter 1 Oracle9i: SQL 55
Comparison Operators
Indicates how the data should relate to the
given search value
-
8/8/2019 Full SQL Updated
56/273
Chapter 1 Oracle9i: SQL 56
Arithmetic Comparison
Operators
-
8/8/2019 Full SQL Updated
57/273
Chapter 1 Oracle9i: SQL 57
Other Comparison Operators
-
8/8/2019 Full SQL Updated
58/273
Chapter 1 Oracle9i: SQL 58
IN Operator
Match a value in a specified list
List must be in parentheses
Values separated by commas
-
8/8/2019 Full SQL Updated
59/273
Chapter 1 Oracle9i: SQL 59
IN Operator Example
-
8/8/2019 Full SQL Updated
60/273
Chapter 1 Oracle9i: SQL 60
LIKE Operator
Performs pattern searches
Used with wildcard characters
Underscore (_) for exactly one character in the
indicated position
Percent sign (%) to represent any number of
characters
-
8/8/2019 Full SQL Updated
61/273
Chapter 1 Oracle9i: SQL 61
LIKE Operator Example
-
8/8/2019 Full SQL Updated
62/273
Chapter 1 Oracle9i: SQL 62
Logical Operators
Used to combine conditions
Evaluated in order of NOT, AND, OR
NOT reverses meaning
AND both conditions must be TRUE
OR at least one condition must be TRUE
-
8/8/2019 Full SQL Updated
63/273
Chapter 1 Oracle9i: SQL 63
AND Logical Operator Example
-
8/8/2019 Full SQL Updated
64/273
Chapter 1 Oracle9i: SQL 64
Resolving Multiple Types of
Operators1. Arithmetic operators
2. Comparison operators
3. Logical operators
-
8/8/2019 Full SQL Updated
65/273
Chapter 1 Oracle9i: SQL 65
Treatment of NULL Values
Absence of data
Requires use of IS NULL operator
-
8/8/2019 Full SQL Updated
66/273
Chapter 1 Oracle9i: SQL 66
ORDER BY Clause
Presents data in sorted order
Ascending order is default
Use DESC keyword to override column
default
255 columns maximum
-
8/8/2019 Full SQL Updated
67/273
Chapter 1 Oracle9i: SQL 67
Sort Sequence
In ascending order, values will be listed in
the following sequence:
Numeric values
Character values
NULL values
In descending order, sequence is reversed
-
8/8/2019 Full SQL Updated
68/273
Chapter 1 Oracle9i: SQL 68
ORDER BY Example
-
8/8/2019 Full SQL Updated
69/273
Chapter 1 Oracle9i: SQL 69
Chapter 4
Joining Multiple Tables
-
8/8/2019 Full SQL Updated
70/273
Chapter 1 Oracle9i: SQL 70
Chapter Objectives
Create a Cartesian join
Create an equality join using the WHERE clause
Create an equality join using the JOIN keyword
Create a non-equality join using the WHERE
clause
Create a non-equality join using the JOINONapproach
-
8/8/2019 Full SQL Updated
71/273
Chapter 1 Oracle9i: SQL 71
Chapter Objectives
Create a self-join
Distinguish an inner join from an outer join
Create an outer join using the WHERE clause
Create an outer join using the OUTER
keyword
Use set operators to combine the results ofmultiple queries
Join three or more tables
-
8/8/2019 Full SQL Updated
72/273
Chapter 1 Oracle9i: SQL 72
Purpose of Joins
Joins are used to link tables and reconstruct
data in a relational database
Joins can be created through:
Conditions in a WHERE clause
Use of JOIN keywords in FROM clause
-
8/8/2019 Full SQL Updated
73/273
Chapter 1 Oracle9i: SQL 73
Cartesian Join
Created by omitting joining condition in the
WHERE clause or through CROSS JOIN
keywords in the FROM clause Results in every possible row combination
(m * n)
-
8/8/2019 Full SQL Updated
74/273
Chapter 1 Oracle9i: SQL 74
Cartesian Join Example:
Omitted Condition select * from oct_hotel, dst_destination;
select * from oct_hotel cross join
dst_destination;
-
8/8/2019 Full SQL Updated
75/273
Chapter 1 Oracle9i: SQL 75
Equality Join
Links rows through equivalent data that
exists in both tables
Created by:
Creating equivalency condition in the WHERE
clause
Using NATURAL JOIN, JOINUSING, orJOINON keywords in the FROM clause
-
8/8/2019 Full SQL Updated
76/273
Chapter 1 Oracle9i: SQL 76
Equality Join: WHERE Clause
Example
-
8/8/2019 Full SQL Updated
77/273
Chapter 1 Oracle9i: SQL 77
Equality Join: NATURAL JOIN
Syntax: tablenameNATURAL JOIN tablename
-
8/8/2019 Full SQL Updated
78/273
Chapter 1 Oracle9i: SQL 78
Equality Join: JOINUSING
Syntax: tablename JOIN tablenameUSING (columnname)
-
8/8/2019 Full SQL Updated
79/273
Chapter 1 Oracle9i: SQL 79
Equality Join: JOINON
Syntax: tablename JOIN tablenameON condition
-
8/8/2019 Full SQL Updated
80/273
Chapter 1 Oracle9i: SQL 80
JOIN Keyword Overview
Use NATURAL JOIN when tables have one
column in common
Use JOINUSING when tables have more thanone column in common
Use JOINON when a condition is needed to
specify a relationship other than equivalency
Using JOIN keyword frees the WHERE clause for
exclusive use in restricting rows
-
8/8/2019 Full SQL Updated
81/273
Chapter 1 Oracle9i: SQL 81
Non-Equality Joins
In WHERE clause, use any comparison
operator other than equal sign
In FROM clause, use JOINON keywordswith non-equivalent condition
-
8/8/2019 Full SQL Updated
82/273
Chapter 1 Oracle9i: SQL 82
Self-Joins
Used to link a table to itself
Requires use of column qualifier
-
8/8/2019 Full SQL Updated
83/273
Chapter 1 Oracle9i: SQL 83
Self-Join: WHERE Clause
Example
-
8/8/2019 Full SQL Updated
84/273
Chapter 1 Oracle9i: SQL 84
Outer Joins
Use to include rows that do not have a
match in the other table
In WHERE clause, include outer joinoperator (+) next to table with missing rows
to add NULL rows
In FROM clause, use FULL, LEFT, orRIGHT with OUTER JOIN keywords
-
8/8/2019 Full SQL Updated
85/273
Chapter 1 Oracle9i: SQL 85
Outer Join: WHERE Clause
Example
-
8/8/2019 Full SQL Updated
86/273
Chapter 1 Oracle9i: SQL 86
Outer Join: OUTER JOIN
Keyword Exampleselect distinct(a.geoname_id),b.name from oct_hotel a
left outer join dst_destination b on
a.geoname_id=b.geoname_id;
select distinct(a.geoname_id),b.name from oct_hotel a
right outer join dst_destination b on
a.geoname_id=b.geoname_id;
select distinct(a.geoname_id),b.name from oct_hotel a
full outer join dst_destination b on
a.geoname_id=b.geoname_id;
-
8/8/2019 Full SQL Updated
87/273
Chapter 1 Oracle9i: SQL 87
Set Operators
Used to combine the results of two or more
SELECT statements
-
8/8/2019 Full SQL Updated
88/273
Chapter 1 Oracle9i: SQL 88
Set Operator Example
select geoname_id from (select geoname_id from dst_destination
where rownum
-
8/8/2019 Full SQL Updated
89/273
Chapter 1 Oracle9i: SQL 89
Joining Three or More Tables
Same procedure as joining two tables
Will always results in one less join than the
number of tables being joined
-
8/8/2019 Full SQL Updated
90/273
Chapter 1 Oracle9i: SQL 90
Joining Three or More Tables:
Example
-
8/8/2019 Full SQL Updated
91/273
Chapter 1 Oracle9i: SQL 91
Chapter 5
Selected Single-Row Functions
-
8/8/2019 Full SQL Updated
92/273
Chapter 1 Oracle9i: SQL 92
Chapter Objectives
Use the UPPER, LOWER, and INITCAP
functions to change the case of field values
and character strings Extract a substring using the SUBSTR
function
Determine the length of a character stringusing the LENGTH function
-
8/8/2019 Full SQL Updated
93/273
Chapter 1 Oracle9i: SQL 93
Chapter Objectives
Use the LPAD and RPAD functions to pad a
string to a desired width
Use the LTRIM and RTRIM functions to removespecific characters strings
Round and truncate numeric data using the
ROUND and TRUNC functions
Calculate the number of months between twodates using the MONTHS_BETWEEN function
-
8/8/2019 Full SQL Updated
94/273
Chapter 1 Oracle9i: SQL 94
Chapter Objectives
Identify and correct problems associated withcalculations involving null values using the NVLfunction
Display dates and numbers in a specific formatwith the TO_CHAR function
Determine the current date setting using theSYSDATE keyword
Nest functions inside other functions
-
8/8/2019 Full SQL Updated
95/273
Chapter 1 Oracle9i: SQL 95
Terminology
Function predefined block of code that
accepts arguments
Single-row Function returns one row ofresults for each record processed
Multiple-row Function returns one result
per group of data processed
-
8/8/2019 Full SQL Updated
96/273
Chapter 1 Oracle9i: SQL 96
Types of Functions
-
8/8/2019 Full SQL Updated
97/273
Chapter 1 Oracle9i: SQL 97
Case Conversion Functions
Alter the case of data stored in a column or
character string
-
8/8/2019 Full SQL Updated
98/273
Chapter 1 Oracle9i: SQL 98
LOWER Function
Used to convert characters to lower-case
letters
-
8/8/2019 Full SQL Updated
99/273
Chapter 1 Oracle9i: SQL 99
UPPER Function
Used to convert characters to upper-case
letters
-
8/8/2019 Full SQL Updated
100/273
Chapter 1 Oracle9i: SQL 100
INITCAP Function
Used to convert characters to mixed-case
-
8/8/2019 Full SQL Updated
101/273
Chapter 1 Oracle9i: SQL 101
Character Manipulation
FunctionsManipulates data by extracting substrings,
counting number of characters, replacing
strings, etc.
-
8/8/2019 Full SQL Updated
102/273
Chapter 1 Oracle9i: SQL 102
SUBSTR Function
Used to return a substring, or portion of a string
-
8/8/2019 Full SQL Updated
103/273
Chapter 1 Oracle9i: SQL 103
LENGTH Function
Used to determine the number of characters in a
string
-
8/8/2019 Full SQL Updated
104/273
Chapter 1 Oracle9i: SQL 104
LPAD and RPAD Functions
Used to pad, or fill in, a character string to a fixed
width
-
8/8/2019 Full SQL Updated
105/273
Chapter 1 Oracle9i: SQL 105
LTRIM and RTRIM Functions
Used to remove a specific string of characters
-
8/8/2019 Full SQL Updated
106/273
Chapter 1 Oracle9i: SQL 106
REPLACE Function
Substitutes a string with another specified string
-
8/8/2019 Full SQL Updated
107/273
Chapter 1 Oracle9i: SQL 107
CONCAT Function
Used to concatenate two character strings
-
8/8/2019 Full SQL Updated
108/273
Chapter 1 Oracle9i: SQL 108
Number Functions
Allows for manipulation of numeric data
-
8/8/2019 Full SQL Updated
109/273
Chapter 1 Oracle9i: SQL 109
ROUND Function
Used to round numeric columns to a stated precision
-
8/8/2019 Full SQL Updated
110/273
Chapter 1 Oracle9i: SQL 110
TRUNC Function
Used to truncate a numeric value to a specific position
-
8/8/2019 Full SQL Updated
111/273
Chapter 1 Oracle9i: SQL 111
Date Functions
Used to perform date calculations or format
date values
-
8/8/2019 Full SQL Updated
112/273
Chapter 1 Oracle9i: SQL 112
MONTHS_BETWEEN Function
Determines the number of months between
two dates
-
8/8/2019 Full SQL Updated
113/273
Chapter 1 Oracle9i: SQL 113
ADD_MONTHS Function
Adds a specified number of months to a date
-
8/8/2019 Full SQL Updated
114/273
Chapter 1 Oracle9i: SQL 114
NEXT_DAY Function
Determines the next occurrence of a
specified day of the week after a given date
-
8/8/2019 Full SQL Updated
115/273
Chapter 1 Oracle9i: SQL 115
TO_DATE Function
Converts various date formats to the
internal format (DD-MON-YYYY) used by
Oracle9i
-
8/8/2019 Full SQL Updated
116/273
Chapter 1 Oracle9i: SQL 116
Format Model Elements - Dates
-
8/8/2019 Full SQL Updated
117/273
Chapter 1 Oracle9i: SQL 117
NVL Function
Substitutes a value for a NULL value
store_name Sales
Store A 300Store B NULL
Store C 150
SELECT SUM(NVL(Sales,100)) FROM Sales_Data;
-
8/8/2019 Full SQL Updated
118/273
Chapter 1 Oracle9i: SQL 118
NVL2 Function
Allows different actions based on whether a value
is NULL
-
8/8/2019 Full SQL Updated
119/273
Chapter 1 Oracle9i: SQL 119
TO_CHAR Function
Converts dates and numbers to a formatted
character string
Format Model Elements
-
8/8/2019 Full SQL Updated
120/273
Chapter 1 Oracle9i: SQL 120
Format Model Elements
Time and Number
-
8/8/2019 Full SQL Updated
121/273
Chapter 1 Oracle9i: SQL 121
Other Functions
NVL
NVL2
TO_CHAR
DECODE
SOUNDEX
-
8/8/2019 Full SQL Updated
122/273
Chapter 1 Oracle9i: SQL 122
DECODE Function
Determines action based upon values in a list
-
8/8/2019 Full SQL Updated
123/273
Chapter 1 Oracle9i: SQL 123
Nesting Functions
One function is used as an argument inside
another function
Must include all arguments for each function Inner function is resolved first, then outer function
-
8/8/2019 Full SQL Updated
124/273
Chapter 1 Oracle9i: SQL 124
Chapter 6
Group Functions
-
8/8/2019 Full SQL Updated
125/273
Chapter 1 Oracle9i: SQL 125
Chapter Objectives
Differentiate between single-row and multiple-row
functions
Use the SUM and AVG functions for numericcalculations
Use the COUNT function to return the number of
records containing non-NULL values
Use COUNT(*) to include records containingNULL values
-
8/8/2019 Full SQL Updated
126/273
Chapter 1 Oracle9i: SQL 126
Chapter Objectives
Use the MIN and MAX functions with non-numeric fields
Determine when to use the GROUP BYclause to group data
Identify when the HAVING clause shouldbe used
List the order of precedence for evaluatingWHERE, GROUP BY, and HAVINGclauses
-
8/8/2019 Full SQL Updated
127/273
Chapter 1 Oracle9i: SQL 127
Chapter Objectives
State the maximum depth for nesting group
functions
Nest a group function inside a single-rowfunction
Calculate the standard deviation and
variance of a set of data, using theSTDDEV and VARIANCE functions
-
8/8/2019 Full SQL Updated
128/273
Chapter 1 Oracle9i: SQL 128
Group Functions
Return one result per group of rows
processed
Also called multiple-row and aggregatefunctions
All group functions ignore NULL values
except COUNT(*) Use DISTINCT to suppress duplicate values
-
8/8/2019 Full SQL Updated
129/273
Chapter 1 Oracle9i: SQL 129
Group by Function
Calculates total amount stored in a numeric
column for a group of rows
-
8/8/2019 Full SQL Updated
130/273
Chapter 1 Oracle9i: SQL 130
GROUP BY Clause
Used to group data
Must be used for individual column in the
SELECT clause with a group function Cannot reference column alias
-
8/8/2019 Full SQL Updated
131/273
Chapter 1 Oracle9i: SQL 131
HAVING Clause
Having
-
8/8/2019 Full SQL Updated
132/273
Chapter 1 Oracle9i: SQL 132
Order of Clause Evaluation
When included in the same SELECT
statement, evaluated in order of:
WHEREGROUP BY
HAVING
-
8/8/2019 Full SQL Updated
133/273
Chapter 1 Oracle9i: SQL 133
Nesting Functions
Inner function resolved first
Maximum nesting depth: 2
-
8/8/2019 Full SQL Updated
134/273
Chapter 1 Oracle9i: SQL 134
Chapter 7
Subqueries
-
8/8/2019 Full SQL Updated
135/273
Chapter 1 Oracle9i: SQL 135
Chapter Objectives
Determine when it is appropriate to use a subquery
Identify which clauses can contain subqueries
Distinguish between an outer query and asubquery
Use a single-row subquery in a WHERE clause
Use a single-row subquery in a HAVING clause
Use a single-row subquery in a SELECT clause
-
8/8/2019 Full SQL Updated
136/273
Chapter 1 Oracle9i: SQL 136
Chapter Objectives
Distinguish between single-row and multiple-row comparison operators
Use a multiple-row subquery in a WHEREclause
Use a multiple-row subquery in a HAVINGclause
Use a multiple-column subquery in a WHEREclause
-
8/8/2019 Full SQL Updated
137/273
Chapter 1 Oracle9i: SQL 137
Chapter Objectives
Create an inline view using a multiple-
column subquery in a FROM clause
Compensate for NULL values in subqueries Distinguish between correlated and
uncorrelated subqueries
Nest a subquery inside another subquery
-
8/8/2019 Full SQL Updated
138/273
Chapter 1 Oracle9i: SQL 138
Subquery
Used when query is based on unknown
value
A query nested inside another query Requires SELECT and FROM clauses
Must be enclosed in parentheses
Place on right side of comparison operator
f b i
-
8/8/2019 Full SQL Updated
139/273
Chapter 1 Oracle9i: SQL 139
Types of Subqueries
Si l S b O
-
8/8/2019 Full SQL Updated
140/273
Chapter 1 Oracle9i: SQL 140
Single-Row Subquery Operators
Can only return one result to outer query
Operators include =, >, =,
Single-Row Subquery
-
8/8/2019 Full SQL Updated
141/273
Chapter 1 Oracle9i: SQL 141
Single Row Subquery
In WHERE ClauseUsed for comparison against individual data
Single-Row Subquery
-
8/8/2019 Full SQL Updated
142/273
Chapter 1 Oracle9i: SQL 142
Single Row Subquery
In HAVING ClauseRequired when returned value is compared
to grouped data
Single-Row Subquery
-
8/8/2019 Full SQL Updated
143/273
Chapter 1 Oracle9i: SQL 143
Single Row Subquery
In SELECT ClauseReplicates subquery value for each row displayed
M l i l R S b i
-
8/8/2019 Full SQL Updated
144/273
Chapter 1 Oracle9i: SQL 144
Multiple-Row Subqueries
Return more than one row of results
Require use of IN, ANY, ALL, or EXISTS
operators
ANY d ALL O t
-
8/8/2019 Full SQL Updated
145/273
Chapter 1 Oracle9i: SQL 145
ANY and ALL Operators
Combine with arithmetic operators
EXISTS O t
-
8/8/2019 Full SQL Updated
146/273
Chapter 1 Oracle9i: SQL 146
EXISTS Operator
Determines whether condition exists in subquery
Multiple-Row Subquery
-
8/8/2019 Full SQL Updated
147/273
Chapter 1 Oracle9i: SQL 147
p q y
In WHERE Clause
Multiple-Row Subquery
-
8/8/2019 Full SQL Updated
148/273
Chapter 1 Oracle9i: SQL 148
p q y
In HAVING Clause
M lti l C l S b
-
8/8/2019 Full SQL Updated
149/273
Chapter 1 Oracle9i: SQL 149
Multiple-Column Subquery
Returns more than one column in results
Can return more than one row
Column list on left side of operator must bein parentheses
Uses IN operator for WHERE and
HAVING clauses
Multiple-Column Subquery
-
8/8/2019 Full SQL Updated
150/273
Chapter 1 Oracle9i: SQL 150
p q y
In FROM ClauseCreates temporary table
Multiple-Column Subquery
-
8/8/2019 Full SQL Updated
151/273
Chapter 1 Oracle9i: SQL 151
p q y
In WHERE ClauseReturns multiple columns for evaluation
NULL V l
-
8/8/2019 Full SQL Updated
152/273
Chapter 1 Oracle9i: SQL 152
NULL Values
When subquery might return NULL values, use NVL function
Uncorrelated S bq eries
-
8/8/2019 Full SQL Updated
153/273
Chapter 1 Oracle9i: SQL 153
Uncorrelated Subqueries
Processing sequence:
Inner query executed first
Result passed to outer queryOuter query executed
Correlated Subqueries
-
8/8/2019 Full SQL Updated
154/273
Chapter 1 Oracle9i: SQL 154
Correlated Subqueries
Inner query executed once for each row
processed by outer query
Inner query references row contained inouter query
Nested Subqueries
-
8/8/2019 Full SQL Updated
155/273
Chapter 1 Oracle9i: SQL 155
Nested Subqueries
Maximum 255 subqueries if nested in
WHERE clause
No limit if nested in FROM clause Innermost subquery resolved first, then next
level, etc.
Nested Subquery Example
-
8/8/2019 Full SQL Updated
156/273
Chapter 1 Oracle9i: SQL 156
Nested Subquery Example
Innermost resolved first (3), then second level (2),
then outer query (1)
-
8/8/2019 Full SQL Updated
157/273
Chapter 1 Oracle9i: SQL 157
Chapter 8
Table Creation and Management
Chapter Objectives
-
8/8/2019 Full SQL Updated
158/273
Chapter 1 Oracle9i: SQL 158
Chapter Objectives
Create a new table using the CREATE
TABLE command
Name a new column or table Use a subquery to create a new table
Add a column to an existing table
Modify the size of a column in an existingtable
Chapter Objectives
-
8/8/2019 Full SQL Updated
159/273
Chapter 1 Oracle9i: SQL 159
Chapter Objectives
Drop a column from an existing table
Mark a column as unused, then delete it at a
later time Rename a table
Truncate a table
Drop a table
Database Table
-
8/8/2019 Full SQL Updated
160/273
Chapter 1 Oracle9i: SQL 160
Database Table
A database object
Stores data for the database
Consists of columns and rows
Created and modified through Data
Definition Language (DDL) commands
Table and Column Names
-
8/8/2019 Full SQL Updated
161/273
Chapter 1 Oracle9i: SQL 161
Table and Column Names
Maximum 30 characters - no blank spaces
Must begin with a letter
Can contain numbers, underscore (_), andnumber sign (#)
Must be unique
No reserved words allowed
Common Datatypes
-
8/8/2019 Full SQL Updated
162/273
Chapter 1 Oracle9i: SQL 162
Common Datatypes
CREATE TABLE Command
-
8/8/2019 Full SQL Updated
163/273
Chapter 1 Oracle9i: SQL 163
CREATE TABLE Command
CREATE TABLE Command
-
8/8/2019 Full SQL Updated
164/273
Chapter 1 Oracle9i: SQL 164
CREATE TABLE Command
Column definition list must be enclosed in
parentheses
Datatype must be specified for each column Maximum of 1,000 columns
CREATE TABLE Command
-
8/8/2019 Full SQL Updated
165/273
Chapter 1 Oracle9i: SQL 165
Example
DESCRIBE Command
-
8/8/2019 Full SQL Updated
166/273
Chapter 1 Oracle9i: SQL 166
DESCRIBE Command
Displays structure of specified table
Table Creation Through
-
8/8/2019 Full SQL Updated
167/273
Chapter 1 Oracle9i: SQL 167
Subqueries Can use subquery to retrieve data from
existing table
Requires use of AS keyword New column names can be assigned
CREATE TABLEAS
-
8/8/2019 Full SQL Updated
168/273
Chapter 1 Oracle9i: SQL 168
Command
CREATE TABLEAS
-
8/8/2019 Full SQL Updated
169/273
Chapter 1 Oracle9i: SQL 169
Command Example
Modifying Existing Tables
-
8/8/2019 Full SQL Updated
170/273
Chapter 1 Oracle9i: SQL 170
Modifying Existing Tables
Accomplished through ALTER TABLE
command
Use ADD clause to add a column Use MODIFY clause to change a column
Use DROP COLUMN to drop a column
ALTER TABLE Command
-
8/8/2019 Full SQL Updated
171/273
Chapter 1 Oracle9i: SQL 171
Syntax
ALTER TABLEADD
-
8/8/2019 Full SQL Updated
172/273
Chapter 1 Oracle9i: SQL 172
Command Example
ALTER TABLEMODIFY
-
8/8/2019 Full SQL Updated
173/273
Chapter 1 Oracle9i: SQL 173
Command Example
Modification Guidelines
-
8/8/2019 Full SQL Updated
174/273
Chapter 1 Oracle9i: SQL 174
Modification Guidelines
Column must be as wide as the data it
already contains
If a NUMBER column already containsdata, size cannot be decreased
Adding or changing default data does not
affect existing data
ALTER TABLEDROP
-
8/8/2019 Full SQL Updated
175/273
Chapter 1 Oracle9i: SQL 175
COLUMN Command Can only reference one column per
execution
Deletion is permanent Cannot delete last remaining column in a
table
ALTER TABLESET
-
8/8/2019 Full SQL Updated
176/273
Chapter 1 Oracle9i: SQL 176
UNUSED Command Once marked for deletion, column cannot
be restored
Storage space freed at later time
ALTER TABLEDROP
-
8/8/2019 Full SQL Updated
177/273
Chapter 1 Oracle9i: SQL 177
UNUSED CommandFrees up storage space from columns
previously marked as unused
RENAME Command
-
8/8/2019 Full SQL Updated
178/273
Chapter 1 Oracle9i: SQL 178
RENAME Command
Used to rename a table old name no longer valid
Truncating a Table
-
8/8/2019 Full SQL Updated
179/273
Chapter 1 Oracle9i: SQL 179
TRUNCATE CommandRows are deleted - structure of table remains
DROP TABLE Command
-
8/8/2019 Full SQL Updated
180/273
Chapter 1 Oracle9i: SQL 180
DROP TABLE Command
Table structure and contents are deleted
-
8/8/2019 Full SQL Updated
181/273
Chapter Objectives
-
8/8/2019 Full SQL Updated
182/273
Chapter 1 Oracle9i: SQL 182
p j
Explain the purpose of constraints in a table
Distinguish among PRIMARY KEY,
FOREIGN KEY, UNIQUE, CHECK, andNOT NULL constraints and the appropriate
use for each constraint
Distinguish between creating constraints at
the column level and table level
Chapter Objectives
-
8/8/2019 Full SQL Updated
183/273
Chapter 1 Oracle9i: SQL 183
p j
Create PRIMARY KEY constraints for a
single column and a composite primary key
Create a FOREIGN KEY constraint Create a UNIQUE constraint
Create a CHECK constraint
Chapter Objectives
-
8/8/2019 Full SQL Updated
184/273
Chapter 1 Oracle9i: SQL 184
p j
Create a NOT NULL constraint, using the
ALTER TABLEMODIFY command
Include constraints during table creation Use DISABLE and ENABLE commands
Use the DROP command
Constraints
-
8/8/2019 Full SQL Updated
185/273
Chapter 1 Oracle9i: SQL 185
Rules used to enforce business rules,
practices, and policies
Rules used to ensure accuracy and integrityof data
Constraint Types
-
8/8/2019 Full SQL Updated
186/273
Chapter 1 Oracle9i: SQL 186
yp
Naming Constraints
-
8/8/2019 Full SQL Updated
187/273
Chapter 1 Oracle9i: SQL 187
g
Use optional CONSTRAINT keyword
during creation to assign a name
Let server name constraint using defaultformat SYS_Cn
Creating Constraints
-
8/8/2019 Full SQL Updated
188/273
Chapter 1 Oracle9i: SQL 188
g
When:
During table creation
Modify existing table How:
Column level approach
Table level approach
General Syntax Column Level
-
8/8/2019 Full SQL Updated
189/273
Chapter 1 Oracle9i: SQL 189
y
If a constraint is being created at the column
level, the constraint applies to the column
specified
General Syntax Table Level
-
8/8/2019 Full SQL Updated
190/273
Chapter 1 Oracle9i: SQL 190
y
Approach can be used to create any
constraint type except NOT NULL
Required if constraint is based on multiplecolumns
Enforcement
-
8/8/2019 Full SQL Updated
191/273
Chapter 1 Oracle9i: SQL 191
All constraints are enforced at the table
level
If a data value violates a constraint, theentire row is rejected
Adding Constraints to Existing
-
8/8/2019 Full SQL Updated
192/273
Chapter 1 Oracle9i: SQL 192
Tables Added to existing table with ALTER
TABLE command
Add NOT NULL constraint using MODIFYclause
All other constraints added using ADD
clause
PRIMARY KEY Constraint
-
8/8/2019 Full SQL Updated
193/273
Chapter 1 Oracle9i: SQL 193
Ensures that columns do not contain
duplicate or NULL values
Only one per table allowed
PRIMARY KEY Constraint for
-
8/8/2019 Full SQL Updated
194/273
Chapter 1 Oracle9i: SQL 194
Composite KeyList column names within parentheses
separated by commas
FOREIGN KEY Constraint
-
8/8/2019 Full SQL Updated
195/273
Chapter 1 Oracle9i: SQL 195
Requires a value to exist in referenced
column of other table
NULL values are allowed Enforces referential integrity
Maps to the PRIMARY KEY in parent table
FOREIGN KEY Constraint -
-
8/8/2019 Full SQL Updated
196/273
Chapter 1 Oracle9i: SQL 196
Example
-
8/8/2019 Full SQL Updated
197/273
UNIQUE Constraint
-
8/8/2019 Full SQL Updated
198/273
Chapter 1 Oracle9i: SQL 198
No duplicates allowed in referenced column
NULL values are permitted
CHECK Constraint
-
8/8/2019 Full SQL Updated
199/273
Chapter 1 Oracle9i: SQL 199
Updates and additions must meet specified
condition
NOT NULL Constraint
-
8/8/2019 Full SQL Updated
200/273
Chapter 1 Oracle9i: SQL 200
Special CHECK constraint with IS NOT
NULL condition
Can only be created at column level Included in output of DESCRIBE command
Can only be added to existing table using
ALTER TABLEMODIFY command
NOT NULL Constraint Example
-
8/8/2019 Full SQL Updated
201/273
Chapter 1 Oracle9i: SQL 201
Adding Constraints During Table
-
8/8/2019 Full SQL Updated
202/273
Chapter 1 Oracle9i: SQL 202
Creation Column LevelInclude in column definition
Adding Constraints During Table
-
8/8/2019 Full SQL Updated
203/273
Chapter 1 Oracle9i: SQL 203
Creation Table LevelInclude at end of column list
Viewing Constraints
USER CONSTRAINTS
-
8/8/2019 Full SQL Updated
204/273
Chapter 1 Oracle9i: SQL 204
USER_CONSTRAINTS
Can display name, type, and condition of
CHECK constraints
Disabling/Enabling Constraints
-
8/8/2019 Full SQL Updated
205/273
Chapter 1 Oracle9i: SQL 205
Use DISABLE or ENABLE clause of
ALTER TABLE command
Dropping a Constraint
-
8/8/2019 Full SQL Updated
206/273
Chapter 1 Oracle9i: SQL 206
Constraints cannot be modified, must be
dropped and recreated
Actual syntax depends on type of constraintPRIMARY KEY - just list type of constraint
UNIQUE - include column name
All others - reference constraint name
ALTER TABLEDROP Syntax
-
8/8/2019 Full SQL Updated
207/273
Chapter 1 Oracle9i: SQL 207
-
8/8/2019 Full SQL Updated
208/273
Chapter 1 Oracle9i: SQL 208
Chapter 10
Data Manipulation
Chapter Objectives
-
8/8/2019 Full SQL Updated
209/273
Chapter 1 Oracle9i: SQL 209
Add a record to an existing table
Add a record containing a NULL value to
an existing table Use a subquery to copy records from an
existing table
Modify the existing rows within a table
Chapter Objectives
-
8/8/2019 Full SQL Updated
210/273
Chapter 1 Oracle9i: SQL 210
Use substitution variables with an UPDATE
command
Issue the transaction control statementsCOMMIT and ROLLBACK
Differentiate between DDL, DML, and
transaction control commands
Chapter Objectives
-
8/8/2019 Full SQL Updated
211/273
Chapter 1 Oracle9i: SQL 211
Delete records
Differentiate between a shared lock and an
exclusive lock Use the SELECTFOR UPDATE
command to create a shared lock
INSERT Command
-
8/8/2019 Full SQL Updated
212/273
Chapter 1 Oracle9i: SQL 212
Used to add rows to existing tables
Identify table in the INSERT INTO clause
Specify data in the VALUES clause Can only add one row at a time to a table
INSERT Command Syntax
-
8/8/2019 Full SQL Updated
213/273
Chapter 1 Oracle9i: SQL 213
Enclose non-numeric data in single quotes
If column list not provided, a value must be
assigned to each column in the table
INSERT Command Example
-
8/8/2019 Full SQL Updated
214/273
Chapter 1 Oracle9i: SQL 214
Inserting NULL Value
-
8/8/2019 Full SQL Updated
215/273
Chapter 1 Oracle9i: SQL 215
Omit column name from INSERT INTO
clause column list
Substitute two single quotation marks Use NULL keyword
Inserting Data from an Existing
Table
-
8/8/2019 Full SQL Updated
216/273
Chapter 1 Oracle9i: SQL 216
ab e
Substitute subquery for VALUES clause
Modifying Existing Rows
-
8/8/2019 Full SQL Updated
217/273
Chapter 1 Oracle9i: SQL 217
Modify rows using UPDATE command
Use UPDATE command to:
Add values to an existing rowChange existing values
UPDATE Command
-
8/8/2019 Full SQL Updated
218/273
Chapter 1 Oracle9i: SQL 218
UPDATE clause identifies table
SET clause identifies column being changed
and new value Optional WHERE clause specifies row(s) to
be changed if omitted, will update all
rows
UPDATE Command Syntax
-
8/8/2019 Full SQL Updated
219/273
Chapter 1 Oracle9i: SQL 219
UPDATE Command Example
-
8/8/2019 Full SQL Updated
220/273
Chapter 1 Oracle9i: SQL 220
Substitution Variables
-
8/8/2019 Full SQL Updated
221/273
Chapter 1 Oracle9i: SQL 221
Prompts user for value
Identified by ampersand (&) preceding
variable name Can be used to create interactive scripts
Substitution Variable Example
-
8/8/2019 Full SQL Updated
222/273
Chapter 1 Oracle9i: SQL 222
Transaction Control
-
8/8/2019 Full SQL Updated
223/273
Chapter 1 Oracle9i: SQL 223
Results of Data Manipulation Language
(DML) are not permanently updated to table
until explicit or implicit COMMIT occurs
Transaction control statements can:
Commit data through COMMIT command
Undo data changes through ROLLBACK
command
COMMIT
-
8/8/2019 Full SQL Updated
224/273
Chapter 1 Oracle9i: SQL 224
Explicit COMMIT occurs by executing
COMMIT;
Implicit COMMIT occurs when DDLcommand is executed or user properly exits
system
Permanently updates table(s) and allows
other users to view changes
ROLLBACK
-
8/8/2019 Full SQL Updated
225/273
Chapter 1 Oracle9i: SQL 225
Used to undo changes that have not been
committed
Occurs when:ROLLBACK; is executed
System restarts after crash
Deleting Rows
-
8/8/2019 Full SQL Updated
226/273
Chapter 1 Oracle9i: SQL 226
DELETE command removes a row from a table
DELETE Command Omitting
WHERE Clause
-
8/8/2019 Full SQL Updated
227/273
Chapter 1 Oracle9i: SQL 227
Omitting WHERE clause removes all rows
Table Locks
-
8/8/2019 Full SQL Updated
228/273
Chapter 1 Oracle9i: SQL 228
Prevents users from changing same data or
objects
Two types:Shared prevents DML operations on portionof table
Exclusive locks table preventing other
exclusive or shared locks
Shared Lock
-
8/8/2019 Full SQL Updated
229/273
Chapter 1 Oracle9i: SQL 229
Locks portion of table affected by DMLoperation
Implicitly occurs during UPDATE or
DELETE operations
Explicitly occurs through LOCK TABLEcommand with SHARE MODE option
Released when COMMIT (implicit orexplicit) or ROLLBACK occurs
Exclusive Lock
-
8/8/2019 Full SQL Updated
230/273
Chapter 1 Oracle9i: SQL 230
Implicitly locks table for DDL operations -
CREATE or ALTER TABLE
Explicitly locked through LOCK TABLEcommand with EXCLUSIVE MODE option
Released after execution of DDL operation
or after user exits system
SELECTFOR UPDATE
Command
-
8/8/2019 Full SQL Updated
231/273
Chapter 1 Oracle9i: SQL 231
Creates shared lock on retrieved portion of
table
Prevents one user from changing a rowwhile another user is selecting rows to be
changed
Released through implicit or explicit
commit
SELECTFOR UPDATE
Command Example
-
8/8/2019 Full SQL Updated
232/273
Chapter 1 Oracle9i: SQL 232
-
8/8/2019 Full SQL Updated
233/273
Chapter 1 Oracle9i: SQL 233
Chapter 11
Views
Chapter Objectives
-
8/8/2019 Full SQL Updated
234/273
Chapter 1 Oracle9i: SQL 234
Create a view, using CREATE VIEW
command or the CREATE OR REPLACE
VIEW command
Employ the FORCE and NO FORCE
options
State the purpose of the WITH CHECK
OPTION constraint
Chapter Objectives
-
8/8/2019 Full SQL Updated
235/273
Chapter 1 Oracle9i: SQL 235
Explain the effect of the WITH READ
ONLY option
Update a record in a simple view
Re-create a view
Explain the implication of an expression in
a view for DML operations
Update a record in a complex view
Chapter Objectives
-
8/8/2019 Full SQL Updated
236/273
Chapter 1 Oracle9i: SQL 236
Identify problems associated with adding
records to a complex view
Identify the key-preserved table underlying
a complex view
Drop a view
Explain inline views and the use of
ROWNUM to perform a TOP-N analysis
Views
-
8/8/2019 Full SQL Updated
237/273
Chapter 1 Oracle9i: SQL 237
Permanent objects that store no data
Display data contained in other tables
Two purposes:Reduce complex query requirements for novice
users
Restrict users access to sensitive data
Types of Views
-
8/8/2019 Full SQL Updated
238/273
Chapter 1 Oracle9i: SQL 238
CREATE VIEW Command
-
8/8/2019 Full SQL Updated
239/273
Chapter 1 Oracle9i: SQL 239
Use OR REPLACE if view already exists
Use FORCE if underlying table does not
exist at time of creation Provide new column names if necessary
CREATE VIEW Options
-
8/8/2019 Full SQL Updated
240/273
Chapter 1 Oracle9i: SQL 240
WITH CHECK OPTION constraint if
used, prevents data changes that will make
the data subsequently inaccessible to the
view
WITH READ ONLY prevents DML
operations
Simple View
-
8/8/2019 Full SQL Updated
241/273
Chapter 1 Oracle9i: SQL 241
Only references one table no group
functions, GROUP BY clause, or
expressions
Simple View DML Operations
-
8/8/2019 Full SQL Updated
242/273
Chapter 1 Oracle9i: SQL 242
Any DML operations are allowed through
simple views unless created with WITH
READ ONLY option
DML operations that violate constraints on
the underlying table are not allowed
Complex View
-
8/8/2019 Full SQL Updated
243/273
Chapter 1 Oracle9i: SQL 243
May contain data from multiple tables or
data created with the GROUP BY clause,
functions, or expressions
Type of DML operations allowed depends
on various factors
DML Operations - Complex
Views with Expressions
-
8/8/2019 Full SQL Updated
244/273
Chapter 1 Oracle9i: SQL 244
Values cannot be inserted into columns that
are based on arithmetic expressions
DML Operations Complex
Views from Multiple Tables
-
8/8/2019 Full SQL Updated
245/273
Chapter 1 Oracle9i: SQL 245
DML operations can not be performed on
non key-preserved tables, but they are
permitted on key-preserved tables
DML Operations Other
Complex Views
-
8/8/2019 Full SQL Updated
246/273
Chapter 1 Oracle9i: SQL 246
No DML operations are permitted on
complex views based on DISTINCT,
ROWNUM, GROUP BY, or a function
Dropping a View
-
8/8/2019 Full SQL Updated
247/273
Chapter 1 Oracle9i: SQL 247
Use DROP VIEW command
Inline View
-
8/8/2019 Full SQL Updated
248/273
Chapter 1 Oracle9i: SQL 248
Temporary table created by using subquery
in FROM clause
Can only be referenced while the command
is being executed
Most common usage TOP-N Analysis
TOP-N Analysis
-
8/8/2019 Full SQL Updated
249/273
Chapter 1 Oracle9i: SQL 249
ORDER BY included to identify top values: descending for highest values, ascending for lowest values
Extract data based on ROWNUM
-
8/8/2019 Full SQL Updated
250/273
Chapter 1 Oracle9i: SQL 250
Chapter 12
Additional Database Objects
Chapter Objectives
-
8/8/2019 Full SQL Updated
251/273
Chapter 1 Oracle9i: SQL 251
Define the purpose of a sequence and state
how it can be used by an organization
Explain why gaps may appear in the
integers generated by a sequence
Correctly use the CREATE SEQUENCE
command to create a sequence
Chapter Objectives
-
8/8/2019 Full SQL Updated
252/273
Chapter 1 Oracle9i: SQL 252
Identify which options cannot be changedby the ALTER SEQUENCE command
Use NEXTVAL and CURRVAL in an
INSERT command Explain when Oracle9i will automatically
create an index
Create an index, using the CREATEINDEX command
Chapter Objectives
-
8/8/2019 Full SQL Updated
253/273
Chapter 1 Oracle9i: SQL 253
Delete an index, using the DELETE INDEX
command
Create a PUBLIC synonym
Delete a PUBLIC synonym
Identify the contents of different versions of
views used to access the data dictionary,
based on the prefix of the view
Database Objects
-
8/8/2019 Full SQL Updated
254/273
Chapter 1 Oracle9i: SQL 254
Anything that has a name and defined
structure
Includes:
Sequence generate sequential integers
Index quickly locate specific records
Synonym alias for other database objects
Sequences
-
8/8/2019 Full SQL Updated
255/273
Chapter 1 Oracle9i: SQL 255
Used for internal control purposes by
providing sequential integers for auditing
Used to generate unique value for primary
key column no correlation with actual row
contents
CREATE SEQUENCE
Command
-
8/8/2019 Full SQL Updated
256/273
Chapter 1 Oracle9i: SQL 256
Various intervals allowed Default: 1
Can specify starting number Default: 1
CREATE SEQUENCE
Command
-
8/8/2019 Full SQL Updated
257/273
Chapter 1 Oracle9i: SQL 257
Can specify MINVALUE for decreasing
sequence, MAXVALUE for increasing
Numbers can be reused if CYCLE specified
ORDER clause for application cluster
environment
Use CACHE to pre-generate integers
Default: 20
CREATE SEQUENCE
Command Example
-
8/8/2019 Full SQL Updated
258/273
Chapter 1 Oracle9i: SQL 258
Verifying Sequence Values
-
8/8/2019 Full SQL Updated
259/273
Chapter 1 Oracle9i: SQL 259
Query USER_SEQUENCES data dictionary
view
Using Sequence Values
i
-
8/8/2019 Full SQL Updated
260/273
Chapter 1 Oracle9i: SQL 260
NEXTVAL generates integer
CURRVAL contains last integer
generated by NEXTVAL
Altering Sequence Definitions
A S Q C d
-
8/8/2019 Full SQL Updated
261/273
Chapter 1 Oracle9i: SQL 261
Use ALTER SEQUENCE command
START WITH value cannot be altered
drop sequence and re-create
Changes cannot make current integers
invalid
ALTER SEQUENCE Command
Example
-
8/8/2019 Full SQL Updated
262/273
Chapter 1 Oracle9i: SQL 262
DROP SEQUENCE Command
P i l d ff d
-
8/8/2019 Full SQL Updated
263/273
Chapter 1 Oracle9i: SQL 263
Previous values generated are not affected
by removing a sequence from a database
Indexes
S f l f d l d
-
8/8/2019 Full SQL Updated
264/273
Chapter 1 Oracle9i: SQL 264
Stores frequently referenced value and row
ID (ROWID)
Can be based on one column, multiple
columns, functions, or expressions
Creating an Index
I li itl t d b PRIMARY KEY d
-
8/8/2019 Full SQL Updated
265/273
Chapter 1 Oracle9i: SQL 265
Implicitly created by PRIMARY KEY and
UNIQUE constraints
Explicitly created by CREATE INDEX
command
CREATE INDEX Command
Example
-
8/8/2019 Full SQL Updated
266/273
Chapter 1 Oracle9i: SQL 266
Verifying an Index
I d li t d i USER INDEXES i
-
8/8/2019 Full SQL Updated
267/273
Chapter 1 Oracle9i: SQL 267
Indexes listed in USER_INDEXES view
Removing an Index
U DROP INDEX d
-
8/8/2019 Full SQL Updated
268/273
Chapter 1 Oracle9i: SQL 268
Use DROP INDEX command
Synonyms
S t li f d t b
-
8/8/2019 Full SQL Updated
269/273
Chapter 1 Oracle9i: SQL 269
Serve as permanent aliases for database
objects
Can be private or public
Private synonyms are only available to user
who created them
PUBLIC synonyms are available to all database
users
CREATE SYNONYM
Command Syntax
-
8/8/2019 Full SQL Updated
270/273
Chapter 1 Oracle9i: SQL 270
CREATE SYNONYM
Command Example
-
8/8/2019 Full SQL Updated
271/273
Chapter 1 Oracle9i: SQL 271
Deleting a SYNONYM
A private synonym can be deleted by owner
-
8/8/2019 Full SQL Updated
272/273
Chapter 1 Oracle9i: SQL 272
A private synonym can be deleted by owner
A PUBLIC synonym can only be deleted by
a user with DBA privileges
Data Dictionary
Stores information about database objects
-
8/8/2019 Full SQL Updated
273/273
Stores information about database objects
Owned by user SYS
Cannot be directly accessed by users Displays contents through data dictionary
views