Download Oracle Database 12C SQL WORKSHOP 2 - Student Guide Volume 2.pdf PDF

TitleOracle Database 12C SQL WORKSHOP 2 - Student Guide Volume 2.pdf
TagsOracle Database Sql Database Index Table (Database) Information Management
File Size5.3 MB
Total Pages258
Table of Contents
                            Oracle Database 12c: SQL  Workshop II - (Student Guide - Volume II)
Table of Contents
Lesson 10: Managing Data in Different Time Zones
	Objectives
	Lesson Agenda
	Time Zones
	TIME_ZONE Session Parameter
	CURRENT_DATE
	Comparing Date and Time in a Session’s Time Zone
	DBTIMEZONE and SESSIONTIMEZONE
	TIMESTAMP Data Types
	TIMESTAMP Fields
	Difference Between DATE and TIMESTAMP
	Comparing TIMESTAMP Data Types
	Lesson Agenda
	INTERVAL Data Types
	INTERVAL Fields
	INTERVAL YEAR TO MONTH: Example
	INTERVAL DAY TO SECOND Data Type: Example
	Lesson Agenda
	EXTRACT
	TZ_OFFSET
	FROM_TZ
	TO_TIMESTAMP
	TO_YMINTERVAL
	TO_DSINTERVAL
	Daylight Saving Time (DST)
	Quiz
	Summary
	Practice 10: Overview
Appendix A: Table Descriptions
Appendix B: Using SQL Developer
	Objectives
	What Is Oracle SQL Developer?
	Specifications of SQL Developer
	SQL Developer 3.2 Interface
	Creating a Database Connection
	Browsing Database Objects
	Displaying the Table Structure
	Browsing Files
	Creating a Schema Object
	Creating a New Table: Example
	Using the SQL Worksheet
	Executing SQL Statements
	Saving SQL Scripts
	Executing Saved Script Files: Method 1
	Executing Saved Script Files: Method 2
	Formatting the SQL Code
	Using Snippets
	Using Snippets: Example
	Using Recycle Bin
	Debugging Procedures and Functions
	Database Reporting
	Creating a User-Defined Report
	Search Engines and External Tools
	Setting Preferences
	Resetting the SQL Developer Layout
	Data Modeler in SQL Developer
	Summary
Appendix C: Using SQL*Plus
	Objectives
	SQL and SQL*Plus Interaction
	SQL Statements Versus SQL*Plus Commands
	Overview of SQL*Plus
	Logging In to SQL*Plus
	Displaying the Table Structure
	SQL*Plus Editing Commands
	Using LIST
	Using the CHANGE Command
	SQL*Plus File Commands
	Using the SAVE
	SERVEROUTPUT Command
	Using the SQL*Plus SPOOL Command
	Using the AUTOTRACE Command
	Summary
Appendix D: Commonly Used SQL Commands
	Objectives
	Basic SELECT Statement
	SELECT Statement
	WHERE Clause
	ORDER BY Clause
	GROUP BY Clause
	Data Definition Language
	CREATE TABLE Statement
	ALTER TABLE Statement
	DROP TABLE Statement
	GRANT Statement
	Privilege Types
	REVOKE Statement
	TRUNCATE TABLE Statement
	Data Manipulation Language
	INSERT Statement
	UPDATE Statement Syntax
	DELETE Statement
	Transaction Control Statements
	COMMIT Statement
	ROLLBACK Statement
	SAVEPOINT Statement
	Joins
	Types of Joins
	Qualifying Ambiguous Column Names
	Natural Join
	Equijoins
	Retrieving Records with Equijoins
	Additional Search Conditions Using the AND and WHERE Operators
	Retrieving Records with Nonequijoins
	Retrieving Records by Using the USING Clause
	Retrieving Records by Using the ON Clause
	Left Outer Join
	Right Outer Join
	Full Outer Join
	Self-Join: Example
	Cross Join
	Summary
Appendix E: Generating Reports by Grouping Related Data
	Objectives
	Review of Group Functions
	Review of the GROUP BY Clause
	Review of the HAVING Clause
	GROUP BY with ROLLUP and CUBE Operators
	ROLLUP Operator
	ROLLUP Operator: Example
	CUBE Operator
	CUBE Operator: Example
	GROUPING Function
	GROUPING Function: Example
	GROUPING SETS
	GROUPING SETS: Example
	Composite Columns
	Composite Columns: Example
	Concatenated Groupings
	Concatenated Groupings: Example
	Summary
Appendix F: Hierarchical Retrieval
	Objectives
	Sample Data from the EMPLOYEES Table
	Natural Tree Structure
	Hierarchical Queries
	Walking the Tree
	Walking the Tree: From the Bottom Up
	Walking the Tree: From the Top Down
	Ranking Rows with the LEVEL Pseudocolumn
	Formatting Hierarchical Reports Using LEVEL and LPAD
	Pruning Branches
	Summary
Appendix G: Writing Advanced Scripts
	Objectives
	Using SQL to Generate SQL
	Creating a Basic Script
	Controlling the Environment
	The Complete Picture
	Dumping the Contents of a Table to a File
	Generating a Dynamic Predicate
	Summary
Appendix H: Oracle Database Architectural Components
	Objectives
	Oracle Database Architecture: Overview
	Oracle Database Server Structures
	Connecting to the Database
	Interacting with an Oracle Database
	Oracle Memory Architecture
	Process Architecture
	Database Writer Process
	Log Writer Process
	Checkpoint Process
	System Monitor Process
	Process Monitor Process
	Oracle Database Storage Architecture
	Logical and Physical Database Structures
	Processing a SQL Statement
	Processing a Query
	Shared Pool
	Database Buffer Cache
	Program Global Area (PGA)
	Processing a DML Statement
	Redo Log Buffer
	Rollback Segment
	COMMIT Processing
	Summary of the Oracle Database Architecture
	Summary
Appendix I: Regular Expression Support
	Objectives
	What Are Regular Expressions?
	Benefits of Using Regular Expressions
	Using the Regular Expressions Functions and Conditions in SQL and PL/SQL
	What Are Metacharacters?
	Using Metacharacters with Regular Expressions
	Regular Expressions Functions and Conditions: Syntax
	Performing a Basic Search by Using the REGEXP_LIKE Condition
	Replacing Patterns by Using the REGEXP_REPLACE Function
	Finding Patterns by Using the REGEXP_INSTR Function
	Extracting Substrings by Using the REGEXP_SUBSTR Function
	Subexpressions
	Using Subexpressions with Regular Expression Support
	Why Access the nth Subexpression?
	REGEXP_SUBSTR: Example
	Using the REGEXP_COUNT Function
	Regular Expressions and Check Constraints: Examples
	Quiz
	Summary
                        
Document Text Contents
Page 1

Oracle Database 12c : SQL

Workshop II



Student Guide - Volume II

D80194GC10

Edition 1.0

August 2013

D83186

O
ra

cl
e

U
ni

ve
rs

ity
a

nd
E

ga
bi

S
ol

ut
io

ns
u

se
o

nl
y

T
H

E
S

E
e

K
IT

M
A

T
E

R
IA

LS
A

R
E

F
O

R
Y

O
U

R
U

S
E

IN
T

H
IS

C
LA

S
S

R
O

O
M

O
N

LY
.

C
O

P
Y

IN
G

e
K

IT
M

A
T

E
R

IA
LS

F
R

O
M

T
H

IS
C

O
M

P
U

T
E

R
IS

S
T

R
IC

T
LY

P
R

O
H

IB
IT

E
D

Page 2

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Disclaimer

This document contains proprietary information and is protected by copyright and
other intellectual property laws. You may copy and print this document solely for your
own use in an Oracle training course. The document may not be modified or altered
in any way. Except where your use constitutes "fair use" under copyright law, you
may not use, share, download, upload, copy, print, display, perform, reproduce,
publish, license, post, transmit, or distribute this document in whole or in part without
the express authorization of Oracle.

The information contained in this document is subject to change without notice. If you
find any problems in the document, please report them in writing to: Oracle University,
500 Oracle Parkway, Redwood Shores, California 94065 USA. This document is not
warranted to be error-free.

Restricted Rights Notice


If this documentation is delivered to the United States Government or anyone using
the documentation on behalf of the United States Government, the following notice is
applicable:

U.S. GOVERNMENT RIGHTS
The U.S. Government’s rights to use, modify, reproduce, release, perform, display, or
disclose these training materials are restricted by the terms of the applicable Oracle
license agreement and/or the applicable U.S. Government contract.


Trademark Notice


Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names
may be trademarks of their respective owners.




Author
Dimpi Rani Sarmah


Technical Contributors
and Reviewers
Nancy Greenberg

Swarnapriya Shridhar

Bryan Roberts,

Laszlo Czinkoczki

KimSeong Loh

Brent Dayley

Jim Spiller

Christopher Wensley

Maheshwari Krishnamurthy

Daniel Milne

Michael Almeida

Diganta Choudhury

Anjulaponni Azhagulekshmi
Subbiahpillai

Manish Pawar

Clair Bennett

Yanti Chang

Joel Goodman

Gerlinde Frenzen


Publisher
Sujatha Nagendra

Page 129

The INSERT statement adds rows to a table. Make sure to insert a new row containing values
for each column and to list the values in the default order of the columns in the table.
Optionally, you can also list the columns in the INSERT statement.

For example:
INSERT INTO job_history (employee_id, start_date, end_date,

job_id)
VALUES (120,'25-JUL-06','12-FEB_08','AC_ACCOUNT');

The syntax discussed in the slide allows you to insert a single row at a time. The VALUES
keyword assigns the values of expressions to the corresponding columns in the column list.

Oracle Database 12c: SQL Workshop II D - 17

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

INSERT Statement

• Use the INSERT statement to add new rows to a table.

• Syntax:

• Example:

INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);

INSERT INTO departments
VALUES (200,'Development',104,1400);

Page 130

The UPDATE statement modifies the existing values in a table. Confirm the update operation
by querying the table to display the updated rows. You can modify a specific row or rows by
specifying the WHERE clause.

For example:
UPDATE employees

SET salary = 17500

WHERE employee_id = 102;

In general, use the primary key column in the WHERE clause to identify the row to update. For
example, to update a specific row in the employees table, use employee_id to identify the
row instead of employee_name, because more than one employee may have the same
name.

Note: Typically, the condition keyword is composed of column names, expressions,
constants, subqueries, and comparison operators.

Oracle Database 12c: SQL Workshop II D - 18

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

UPDATE Statement Syntax

• Use the UPDATE statement to modify the existing rows in a
table.

• Update more than one row at a time (if required).

• Example:

• Specify SET column_name= NULL to update
a column value to NULL.

UPDATE table
SET column = value [, column = value, ...]
[WHERE condition];

UPDATE copy_emp
SET

Page 257

In this appendix, you have learned to use the regular expression support features. Regular
expression support is available in both SQL and PL/SQL.

Oracle Database 12c: SQL Workshop II I - 21

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Summary

In this appendix, you should have learned how to use regular
expressions to search for, match, and replace strings.

Similer Documents