Ingeniero

An

Training Guide

SQL & SQL*Plus
for
Beginners

Training Guide
SQL & SQL*Plus for Beginners

www.appltop.com
info@appltop.com

Course Introduction

Copyright © 1999 – 2004 ApplTop Solutions Limited

2

Training Guide
SQL & SQL*Plus for Beginners

www.appltop.com
info@appltop.com

Course Objectives
This course is designed to give each delegate a
basicunderstanding/awareness of the following...
• Oracle SQL
• Oracle SQL*Plus

Copyright © 1999 – 2004 ApplTop Solutions Limited

3

Training Guide
SQL & SQL*Plus for Beginners

www.appltop.com
info@appltop.com

Course Objectives
This course is designed to give each delegate a basic
understanding of the following topics...
• Oracle SQL. You will learn how to use the industry
standard tool forworking with a Relational Database.
You will also learn many other things, from
constructing simple queries to creating your own
tables.
• Oracle SQL*Plus. After the course you should have a
good understanding of SQL*Plus, from starting it, to
creating simple reports on it.
During the course there will be several exercises to
complete.

Copyright © 1999 – 2004 ApplTop Solutions Limited4

Training Guide
SQL & SQL*Plus for Beginners

www.appltop.com
info@appltop.com

Note that this guide is meant only as an introduction to
SQL and SQL*Plus and therefore much of the newer,
more advanced features available in Oracle databases
8i, 9i and 10g are not covered.

Copyright © 1999 – 2004 ApplTop Solutions Limited

5

Training Guide
SQL & SQL*Plus for Beginnerswww.appltop.com
info@appltop.com

Course Contents
1 – Getting Started

11

2 – Introduction to SQL & SQL*Plus

21

Lab 1

26

Entering SQL Commands
Basic Query Block
Selecting Specific Columns
Arithmetic Operators
Column Aliases
Concatenation
Literals
NULL Values
Duplicate Rows
Ordering Data
Row Restriction
Using Logical Operators
Using SQL Operators
Using LIKE
Negating aComparison
Multiple Conditions & Operator Precedence
Basic SQL*Plus
Summary
Lab 2

27
28
29
30
31
32
33
34
37
38
40
41
42
43
44
46
50
55
57

RDB – A quick refresher
What are SQL & SQL*Plus?

Introduction to SQL
DML or DDL?
Starting SQL*Plus

Copyright © 1999 – 2004 ApplTop Solutions Limited

12
19

22
23
24

6

Training Guide
SQL & SQL*Plus forBeginners

www.appltop.com
info@appltop.com

3 – Row & Group Functions

Row functions
Character functions
LOWER, UPPER & INITCAP
LPAD & RPAD
SUBSTR
INSTR
LTRIM/RTRIM
LENGTH
TRANSLATE
REPLACE
Number functions
ROUND
TRUNC
SIGN
CEIL & FLOOR
Mathematical functions
Oracle Dates & Date Functions
MONTHS_BETWEEN
ADD_MONTHS
NEXT_DAY
LAST_DAY
ROUND & TRUNC
Conversion FunctionsTO_CHAR & Common Format Masks
TO_NUMBER
TO_DATE
Functions that accept any kind of data
NVL
GREATEST & LEAST
DECODE
Copyright © 1999 – 2004 ApplTop Solutions Limited

58

60
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
80
81
82
83
84
85
86
89
90
91
92
93
94

7

Training Guide
SQL & SQL*Plus for Beginners

www.appltop.com
info@appltop.com

NestingFunctions
Group Functions
Group functions
Grouping Data
Omitting Groups
Summary

95
96
97
98
100
101

Lab 3

103

4 - Querying More Than One Table

104

Joins

Product
Equi join
Table Aliases
Non-Equi join
Outer join
Self join
Set Operators
UNION
INTERSECT
MINUS
Rules
Subqueries
Single Row
Multiple Row
ANY/SOME Operator
ALL Operator
Correlated
EXISTS OperatorSummary

106
107
108
109
110
111
112
113
115
116
117
118
119
120
122
123
124
126
128
129

Lab 4

131

Copyright © 1999 – 2004 ApplTop Solutions Limited

8

Training Guide
SQL & SQL*Plus for Beginners

www.appltop.com
info@appltop.com

5 - Modifying Data & the Database

132

Lab 5

165

6 – More SQL*Plus

166

Lab 6

177

Inserting new data...