Unauthorized reproduction or distribution prohibitedฺ Copyright© 2011, Oracle and/or its affiliatesฺ

SQL Primer

ble
fe r a
ans
n - t r
o
s an
l ) ha eฺ
e p ฺc uid
@ ai nt G
r e ro tude
ฺ p ed his S
i g o e t
r
od © t2011, s
uOracle and/or its affiliates. All rights reserved.
( r
Copyright
o
r e ro nse
d lice
o Pe
d r ig
R o
 Objectives

After completing this lesson, you should be able to:

Unauthorized reproduction or distribution prohibitedฺ Copyright© 2011, Oracle and/or its affiliatesฺ

• Describe the syntax of basic SQL-92/1999 commands,

including:
– SELECT
– INSERT
– UPDATE
– DELETE ble
fe r a
– CREATE TABLE ans
n - t r
– DROP TABLE no
• Define basic SQL-92/1999 s a
l ) ha eฺ
data types
e p ฺc uid
@ ai nt G
r e ro tude
ฺ p ed his S
i g o e t
r
od © t2011, s
uOracle and/or its affiliates. All rights reserved.
( r
Copyright
o
r e ro nse
P ed lice
o
drig
Ro

Java SE 7 Programming A - 2
 Using SQL to Query Your Database

Structured query language (SQL) is:

Unauthorized reproduction or distribution prohibitedฺ Copyright© 2011, Oracle and/or its affiliatesฺ

• The ANSI standard language for operating relational

databases
• Efficient, easy to learn, and use
• Functionally complete (With SQL, you can define, retrieve,
and manipulate data in the tables.)
ble
SELECT department_name
fe r a
FROM departments;
ans
n - t r
Database
a no
h a s
ฺ c l) ideฺ
a iep t Gu
r o @ den
e d re Stu
o ฺ p t h is
d r ig use
( r o © t2011,
Copyright
o Oracle and/or its affiliates. All rights reserved.

r e ro nse
P
In a relational liceyou do not specify the access route to the tables, and you do not
eddatabase,
rigoto know how the data is arranged physically.
dneed
Ro To access the database, you execute a structured query language (SQL) statement, which is
the American National Standards Institute (ANSI) standard language for operating relational
databases. SQL is a set of statements with which all programs and users access data in an
Oracle Database. Application programs and Oracle tools often allow users access to the
database without using SQL directly, but these applications, in turn, must use SQL when
executing the user’s request.
SQL provides statements for a variety of tasks, including:
• Querying data
• Inserting, updating, and deleting rows in a table
• Creating, replacing, altering, and dropping objects
• Controlling access to the database and its objects
• Guaranteeing database consistency and integrity
SQL unifies all of the preceding tasks in one consistent language and enables you to work
with data at a logical level.

Java SE 7 Programming A - 3
 SQL Statements

SELECT
INSERT
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2011, Oracle and/or its affiliatesฺ

UPDATE Data manipulation language (DML)

DELETE
MERGE

CREATE
ALTER
DROP
Data definition language (DDL)
RENAME
ble
TRUNCATE
fe r a
COMMENT
ans
n - t r
GRANT
Data control language (DCL) a no
REVOKE
h a s
ฺ c l) ideฺ
COMMIT
ROLLBACK Transaction control a iep t Gu
SAVEPOINT r o @ den
e d re Stu
o ฺ p t h is
d r ig use
( r o © t2011,
Copyright
o Oracle and/or its affiliates. All rights reserved.

r e ro nse
P
SQL statements lice by Oracle comply with industry standards. Oracle Corporation
ed supported
rigo future compliance with evolving standards by actively involving key personnel in SQL
densures
Ro standards committees. The industry-accepted committees are ANSI and International
Standards Organization (ISO). Both ANSI and ISO have accepted SQL as the standard
language for relational databases.
Statement Description
SELECT Retrieves data from the database, enters new rows, changes existing rows, and
INSERT removes unwanted rows from tables in the database, respectively. Collectively
UPDATE known as data manipulation language (DML)
DELETE
MERGE
CREATE Sets up, changes, and removes data structures from tables. Collectively known as
ALTER data definition language (DDL)
DROP
RENAME
TRUNCATE
COMMENT
GRANT Provides or removes access rights to both the Oracle Database and the structures
REVOKE within it
COMMIT Manages the changes made by DML statements. Changes to the data can be
ROLLBACK grouped together into logical transactions
SAVEPOINT
Java SE 7 Programming A - 4
 Basic SELECT Statement
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2011, Oracle and/or its affiliatesฺ

SELECT *|{[DISTINCT] column|expression [alias],...}

FROM table;

• SELECT identifies the columns to be displayed.

• FROM identifies the table containing those columns.

ble
fe r a
ans
n - t r
o
s an
l ) ha eฺ
e p ฺc uid
@ ai nt G
r e ro tude
ฺ p ed his S
i g o e t
r
od © t2011, s
uOracle and/or its affiliates. All rights reserved.
( r
Copyright
o
r e ro nse
e
edform, laicSELECT
o P
In its simplest statement must include the following:
i g
R odr• A SELECT clause, which specifies the columns to be displayed
• A FROM clause, which identifies the table containing the columns that are listed in the
SELECT clause
In the syntax:
SELECT Is a list of one or more columns
* Selects all columns
DISTINCT Suppresses duplicates
column|expression Selects the named column or the expression
alias Gives the selected columns different headings
FROM table Specifies the table containing the columns
.

Java SE 7 Programming A - 5
 Note: Throughout this course, the words keyword, clause, and statement are used as follows:
• A keyword refers to an individual SQL element—for example, SELECT and FROM are
keywords.
• A clause is a part of a SQL statement—for example, SELECT employee_id,
last_name, and so on.
• A statement is a combination of two or more clauses—for example, SELECT * FROM
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2011, Oracle and/or its affiliatesฺ

employees

ble
fe r a
ans
n - t r
o
s an
l ) ha eฺ
e p ฺc uid
@ ai nt G
r e ro tude
ฺ p ed his S
i g o e t
r
od to u s
( r
r e ro nse
d lice
o Pe
d r ig
R o

Java SE 7 Programming A - 6
 Limiting the Rows That Are Selected

• Restrict the rows that are returned by using the WHERE

Unauthorized reproduction or distribution prohibitedฺ Copyright© 2011, Oracle and/or its affiliatesฺ

clause:
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)];

• The WHERE clause follows the FROM clause. e

r a bl
s fe
- t r an
n no
s a
h a
ฺ c l) ideฺ
a iep t Gu
r o @ den
e d re Stu
o ฺ p t h is
d r ig use
( r o © t2011,
Copyright
o Oracle and/or its affiliates. All rights reserved.

r e ro nse
You canPrestrict ice that are returned from the query by using the WHERE clause. A
ed the lrows
rigo clause contains a condition that must be met and it directly follows the FROM clause. If
dWHERE
Ro the condition is true, the row meeting the condition is returned.
In the syntax:
WHERE Restricts the query to rows that meet a condition

condition Is composed of column names, expressions,

constants, and a comparison operator. A condition
specifies a combination of one or more expressions and logical
(Boolean) operators, and returns a value of TRUE, FALSE, or
UNKNOWN.
The WHERE clause can compare values in columns, literal, arithmetic expressions, or
functions. It consists of three elements:
• Column name
• Comparison condition
• Column name, constant, or list of values

Java SE 7 Programming A - 7
 Using the ORDER BY Clause

• Sort the retrieved rows with the ORDER BY clause:

Unauthorized reproduction or distribution prohibitedฺ Copyright© 2011, Oracle and/or its affiliatesฺ

– ASC: Ascending order, default

– DESC: Descending order
• The ORDER BY clause comes last in the SELECT
statement:
SELECT last_name, job_id, department_id, hire_date
a b le
FROM employees
s f er
ORDER BY hire_date ; n tra
n -
no a
a
h eฺ s
c l )
i e pฺ Guid
@ a n t
o e
… d r er Stud
o ฺ pe this
d r ig use
( r o © t2011,
Copyright
o Oracle and/or its affiliates. All rights reserved.

r e ro nse
eofdrows that e
licare
o P
The order returned in a query result is undefined. The ORDER BY clause can
g
dberiused to sort the rows. However, if you use the ORDER BY clause, it must be the last clause
Ro of the SQL statement. Further, you can specify an expression, an alias, or a column position
as the sort condition.
Syntax
SELECT expr
FROM table
[WHERE condition(s)]
[ORDER BY {column, expr, numeric_position} [ASC|DESC]];
In the syntax:
ORDER BY specifies the order in which the retrieved rows are displayed
ASC orders the rows in ascending order (This is the default order.)
DESC orders the rows in descending order
If the ORDER BY clause is not used, the sort order is undefined, and the Oracle server may not
fetch rows in the same order for the same query twice. Use the ORDER BY clause to display
the rows in a specific order.
Note: Use the keywords NULLS FIRST or NULLS LAST to specify whether returned rows
containing null values should appear first or last in the ordering sequence.

Java SE 7 Programming A - 8
 INSERT Statement Syntax

• Add new rows to a table by using the INSERT statement:

Unauthorized reproduction or distribution prohibitedฺ Copyright© 2011, Oracle and/or its affiliatesฺ

INSERT INTO table [(column [, column...])]

VALUES (value [, value...]);

• With this syntax, only one row is inserted at a time.

ble
fe r a
ans
n - t r
o
s an
l ) ha eฺ
e p ฺc uid
@ ai nt G
r e ro tude
ฺ p ed his S
i g o e t
r
od © t2011, s
uOracle and/or its affiliates. All rights reserved.
( r
Copyright
o
r e ro nse
You canP ednew rows
add liceto a table by issuing the INSERT statement.
igosyntax:
dInrthe
Ro table Is the name of the table
column Is the name of the column in the table to populate
value Is the corresponding value for the column
Note: This statement with the VALUES clause adds only one row at a time to a table.

Java SE 7 Programming A - 9
 UPDATE Statement Syntax

• Modify existing values in a table with the UPDATE

Unauthorized reproduction or distribution prohibitedฺ Copyright© 2011, Oracle and/or its affiliatesฺ

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

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

fe r a
ans
n - t r
a no
h a s
ฺ c l) ideฺ
a iep t Gu
r o @ den
e d re Stu
o ฺ p t h is
d r ig use
( r o © t2011,
Copyright
o Oracle and/or its affiliates. All rights reserved.

r e ro nse
You canPmodify ice values in a table by using the UPDATE statement.
ed the lexisting
igosyntax:
dInrthe
Ro table Is the name of the table
column Is the name of the column in the table to populate
value Is the corresponding value or subquery for the column
condition Identifies the rows to be updated and is composed of column names,
expressions, constants, subqueries, and comparison operators
Confirm the update operation by querying the table to display the updated rows.
Note: In general, use the primary key column in the WHERE clause to identify a single row for
update. Using other columns can unexpectedly cause several rows to be updated. For
example, identifying a single row in the EMPLOYEES table by name is dangerous, because
more than one employee may have the same name.

Java SE 7 Programming A - 10
 DELETE Statement

You can remove existing rows from a table by using the

Unauthorized reproduction or distribution prohibitedฺ Copyright© 2011, Oracle and/or its affiliatesฺ

DELETE statement:

DELETE [FROM] table

[WHERE condition];

ble
fe r a
ans
n - t r
a no
h a s
ฺ c l) ideฺ
a iep t Gu
r o @ den
e d re Stu
o ฺ p t h is
d r ig use
( r o © t2011,
Copyright
o Oracle and/or its affiliates. All rights reserved.

r e ro nse
P
DELETE Statement ice
ed lSyntax
r o
igcan
o d
You remove existing rows from a table by using the DELETE statement.
R In the syntax:
table Is the name of the table
condition Identifies the rows to be deleted, and is composed of column names,
expressions, constants, subqueries, and comparison operators

Java SE 7 Programming A - 11
 CREATE TABLE Statement

• You must have:

Unauthorized reproduction or distribution prohibitedฺ Copyright© 2011, Oracle and/or its affiliatesฺ

– The CREATE TABLE privilege

– A storage area
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr][, ...]);

• You specify: e
– The table name r a bl
s fe
tran
– The column name, column data type, and column size
n -
n o
s a
) a
h eฺ
c l
i e pฺ Guid
@ a n t
o e
d r er Stud
o ฺ pe this
d r ig use
( r o © t2011,
Copyright
o Oracle and/or its affiliates. All rights reserved.

r e ro nse
P
You createetables ce data by executing the SQL CREATE TABLE statement. This
d tolistore
rigo is one of the DDL statements that are a subset of the SQL statements used to
dstatement
Ro create, modify, or remove Oracle Database structures. These statements have an immediate
effect on the database and they also record information in the data dictionary.
To create a table, a user must have the CREATE TABLE privilege and a storage area in which
to create objects. The database administrator (DBA) uses data control language (DCL)
statements to grant privileges to users.
In the syntax:
schema Is the same as the owner’s name
table Is the name of the table
DEFAULT expr Specifies a default value if a value is omitted in the INSERT
statement
column Is the name of the column
datatype Is the column’s data type and length

Java SE 7 Programming A - 12
 Defining Constraints

• Syntax:
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2011, Oracle and/or its affiliatesฺ

CREATE TABLE [schema.]table

(column datatype [DEFAULT expr]
[column_constraint],
...
[table_constraint][,...]);

• Column-level constraint syntax: e

r a bl
column [CONSTRAINT constraint_name] constraint_type, s fe
- t r an
• Table-level constraint syntax: n no
s a
column,...
h a
[CONSTRAINT constraint_name] constraint_type
ฺ c l) ideฺ
(column, ...),
a iep t Gu
r o @ den
e d re Stu
o ฺ p t h is
d r ig use
( r o © t2011,
Copyright
o Oracle and/or its affiliates. All rights reserved.

r e ro nse
P
The slide gives lice for defining constraints when creating a table. You can create
ed the syntax
rigo at either the column level or table level. Constraints defined at the column level
dconstraints
Ro are included when the column is defined. Table-level constraints are defined at the end of the
table definition and must refer to the column or columns on which the constraint pertains in a
set of parentheses. It is mainly the syntax that differentiates the two; otherwise, functionally, a
column-level constraint is the same as a table-level constraint.
NOT NULL constraints must be defined at the column level.
Constraints that apply to more than one column must be defined at the table level.

Java SE 7 Programming A - 13
 In the syntax:
schema Is the same as the owner’s name
table Is the name of the table
DEFAULT expr Specifies a default value to be used if a value is omitted in the
INSERT statement
column Is the name of the column
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2011, Oracle and/or its affiliatesฺ

datatype Is the column’s data type and length

column_constraint Is an integrity constraint as part of the column definition
table_constraint Is an integrity constraint as part of the table definition

ble
fe r a
ans
n - t r
o
s an
l ) ha eฺ
e p ฺc uid
@ ai nt G
r e ro tude
ฺ p ed his S
i g o e t
r
od to u s
( r
r e ro nse
d lice
o Pe
d r ig
R o

Java SE 7 Programming A - 14
 Defining Constraints

• Example of a column-level constraint:

Unauthorized reproduction or distribution prohibitedฺ Copyright© 2011, Oracle and/or its affiliatesฺ

CREATE TABLE employees(

employee_id NUMBER(6)
CONSTRAINT emp_emp_id_pk PRIMARY KEY,
1
first_name VARCHAR2(20),
...);
• Example of a table-level constraint: e
r a bl
CREATE TABLE employees(
ns fe
employee_id NUMBER(6),
- t r a
o n
an 2
first_name VARCHAR2(20),
... s
ha eฺ
job_id VARCHAR2(10) NOT NULL, l )
ฺc uid
CONSTRAINT emp_emp_id_pk
i e p
@a
PRIMARY KEY (EMPLOYEE_ID)); nt G
r e ro tude
ฺ p ed his S
i g o e t
r
od © t2011, s
uOracle and/or its affiliates. All rights reserved.
( r
Copyright
o
r e ro nse
d lice
eare
o P
Constraints usually created at the same time as the table. Constraints can be added to a
g
i after its creation and also be temporarily disabled.
odr
table
R Both examples in the slide create a primary key constraint on the EMPLOYEE_ID column of
the EMPLOYEES table.
1. The first example uses the column-level syntax to define the constraint.
2. The second example uses the table-level syntax to define the constraint.
More details about the primary key constraint are provided later in this lesson.

Java SE 7 Programming A - 15
 Including Constraints

• Constraints enforce rules at the table level.

Unauthorized reproduction or distribution prohibitedฺ Copyright© 2011, Oracle and/or its affiliatesฺ

• Constraints prevent the deletion of a table if there

are dependencies.
• The following constraint types are valid:
– NOT NULL
– UNIQUE
ble
– PRIMARY KEY
fe r a
– FOREIGN KEY ans
n - t r
– CHECK
a no
h a s
ฺ c l) ideฺ
a iep t Gu
r o @ den
e d re Stu
o ฺ p t h is
d r ig use
( r o © t2011,
Copyright
o Oracle and/or its affiliates. All rights reserved.

r e ro nse
e d lice
Constraints
o P
d r
The
igOracle server uses constraints to prevent invalid data entry into tables.
R o
You can use constraints to do the following:
• Enforce rules on the data in a table whenever a row is inserted, updated, or deleted from
that table. The constraint must be satisfied for the operation to succeed.
• Prevent the deletion of a table if there are dependencies from other tables.
• Provide rules for Oracle tools, such as Oracle Developer.

Java SE 7 Programming A - 16
 Data Integrity Constraints

Constraint Description
NOT NULL Specifies that the column cannot contain a null value

UNIQUE Specifies a column or combination of columns whose values

must be unique for all rows in the table
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2011, Oracle and/or its affiliatesฺ

PRIMARY KEY Uniquely identifies each row of the table

FOREIGN KEY Establishes and enforces a referential integrity between the
column and a column of the referenced table such that values
in one table match values in another table.
CHECK Specifies a condition that must be true

ble
fe r a
ans
n - t r
o
s an
l ) ha eฺ
e p ฺc uid
@ ai nt G
r e ro tude
ฺ p ed his S
i g o e t
r
od to u s
( r
r e ro nse
d lice
o Pe
d r ig
R o

Java SE 7 Programming A - 17
 Data Types

Data Type Description

Unauthorized reproduction or distribution prohibitedฺ Copyright© 2011, Oracle and/or its affiliatesฺ

VARCHAR2(size) Variable-length character data

CHAR(size) Fixed-length character data
NUMBER(p,s) Variable-length numeric data
DATE Date and time values
Variable-length character data (up to 2 GB)
LONG
ble
CLOB Character data (up to 4 GB) fe r a
an s
RAW and LONG Raw binary data
n - t r
RAW
a no
BLOB Binary data (up to 4 GB)
h a s
BFILE Binary data stored in an external ฺ c l)file (upidtoe4ฺ GB)
i e p Gu
ROWID a
A base-64 number system representing
@ n t the unique
r o d e
e d re Stu
address of a row in its table

o ฺ p t h is
d r ig use
( r o © t2011,
Copyright
o Oracle and/or its affiliates. All rights reserved.

r e ro nse
e d lice
Data TypesP
d r igoyou identify a column for a table, you need to provide a data type for the column. There
When
Ro are several data types available:
Data Type Description
VARCHAR2(size) Variable-length character data (A maximum size must be
specified: minimum size is 1; maximum size is 4,000.)
CHAR [(size)] Fixed-length character data of length size bytes (Default and
minimum size is 1; maximum size is 2,000.)
NUMBER [(p,s)] Number having precision p and scale s (Precision is the total
number of decimal digits and scale is the number of digits to
the right of the decimal point; precision can range from 1 to
38, and scale can range from –84 to 127.)
DATE Date and time values to the nearest second between January 1,
4712 B.C., and December 31, 9999 A.D.
LONG Variable-length character data (up to 2 GB)

CLOB Character data (up to 4 GB)

Java SE 7 Programming A - 18
 Data T ype Description
RA W ( si z e ) R aw binary data of length size (A m ax im um size m ust be specified: m axim um
size is 2,000.)
LO N G R A W R aw binary data of variable length (up to 2 G B )
BL O B B inary data (up to 4 G B )
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2011, Oracle and/or its affiliatesฺ

BF I L E B inary data stored in an extern al file (up to 4 GB )

RO W I D A base-64 nu mber system rep resenting the unique address of a row in its table

Guidelines
• A LONG column is not copied when a table is created using a subquery.
• A LONG column cannot be included in a GROUP BY or an ORDER BY clause.
• Only one LONG column can be used per table. ble
fe r a
• No constraints can be defined on a LONG column.
an s
• You might want to use a CLOB column rather than a LONG column. n - t r
o
s an
l ) ha eฺ
e p ฺc uid
@ ai nt G
r e ro tude
ฺ p ed his S
i g o e t
r
od to u s
( r
r e ro nse
d lice
o Pe
d r ig
R o

Java SE 7 Programming A - 19
 Dropping a Table

• Moves a table to the recycle bin

Unauthorized reproduction or distribution prohibitedฺ Copyright© 2011, Oracle and/or its affiliatesฺ

• Removes the table and all its data entirely if the PURGE
clause is specified
• Invalidates dependent objects and removes object
privileges on the table

DROP TABLE dept80; ble

fe r a
ans
n - t r
a no
h a s
ฺ c l) ideฺ
a iep t Gu
r o @ den
e d re Stu
o ฺ p t h is
d r ig use
( r o © t2011,
Copyright
o Oracle and/or its affiliates. All rights reserved.

r e ro nse
P
The DROP eTABLE lice moves a table to the recycle bin or removes the table and all its
d statement
o the database entirely. Unless you specify the PURGE clause, the DROP TABLE
rigfrom
ddata
Ro statement does not result in space being released back to the tablespace for use by other
objects, and the space continues to count toward the user’s space quota. Dropping a table
invalidates the dependent objects and removes object privileges on the table.
When you drop a table, the database loses all the data in the table and all the indexes
associated
with it.
Syntax
DROP TABLE table [PURGE]
In the syntax, table is the name of the table.
Guidelines
• All the data is deleted from the table.
• Any views and synonyms remain, but are invalid.
• Any pending transactions are committed.
• Only the creator of the table or a user with the DROP ANY TABLE privilege can remove a
table.

Java SE 7 Programming A - 20
 Summary

In this lesson, you should have learned how to:

Unauthorized reproduction or distribution prohibitedฺ Copyright© 2011, Oracle and/or its affiliatesฺ

• Describe the syntax of basic SQL-92/1999 commands,

including:
– SELECT
– INSERT
– UPDATE
– DELETE ble
fe r a
– CREATE TABLE ans
n - t r
– DROP TABLE no
• Define basic SQL-92/1999 data types ha s a
ฺ c l) ideฺ
a iep t Gu
r o @ den
e d re Stu
o ฺ p t h is
d r ig use
( r o © t2011,
Copyright
o Oracle and/or its affiliates. All rights reserved.

r e ro nse
P ed lice
o
drig
Ro

Java SE 7 Programming A - 21
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2011, Oracle and/or its affiliatesฺ

Ro d r ig
o Pe
r e
( r
d lice
r i
ro nse
g o ฺ p
od to u s e
r
t
e
@
ed his S
e p
ro tude
l )
ai nt G
s
ฺc uid
ha eฺ
an
on - t r an
s
fe r a bl
e