Getting Started With Oracle Sql

3

Getting Started with Oracle SQL

Copyright © 2004, Oracle. All rights reserved.

Objectives After completing this lesson, you should be able to do the following: • Execute a SELECT statement • Implement pseudocolumns in SQL • Utilize the different types of operators, conditions, expressions, and functions in SQL • Convert between data types • Describe how SQL treats NULLs

3-2

Copyright © 2004, Oracle. All rights reserved.

Retrieving Data by Using the SELECT Statement • • •

SELECT identifies which columns are to be displayed. FROM identifies which table contains the columns. WHERE specifies how the rows are restricted.

SELECT FROM [WHERE [GROUP BY [HAVING [ORDER BY

3-3

[DISTINCT] {*, column [alias], expr...} table(s) condition(s)] expr [, expr]...] condition] {column, expr, alias} [ASC|DESC]];

Copyright © 2004, Oracle. All rights reserved.

3-4

Copyright © 2004, Oracle. All rights reserved.

Restricting Output by Using the WHERE Clause Syntax: SELECT FROM [WHERE

[DISTINCT] {*, | column [alias], | expr...} table(s) condition(s)]

Example: SELECT FROM WHERE OR

3-5

last_name, job_id, hire_date, salary employees job_id = 'ST_CLERK' AND hire_date > '01-Jan-00' salary > 14000;

Copyright © 2004, Oracle. All rights reserved.

3-6

Copyright © 2004, Oracle. All rights reserved.

Defining Null • •

NULL represents a value that is unavailable, unassigned, unknown, or inapplicable. NULL is not the same as zero or a blank space.

SELECT department_id, employee_id, last_name, job_id, salary, commission_pct FROM employees WHERE job_id in ('ST_CLERK', 'SA_MAN') AND employee_id > 142;

3-7

Copyright © 2004, Oracle. All rights reserved.

Using Pseudocolumns SELECT FROM WHERE

ROWID, last_name employees department_id = 20;

Not found

3-8

Copyright © 2004, Oracle. All rights reserved.

Using Pseudocolumns •

ROWID returns a row’s address:

SELECT FROM WHERE

•

ROWNUM returns a number indicating the order in which the row is selected:

SELECT FROM WHERE

3-9

ROWID, last_name employees department_id = 20;

rownum, last_name, job_id, manager_id employees rownum < 5;

Copyright © 2004, Oracle. All rights reserved.

3-10

Copyright © 2004, Oracle. All rights reserved.

Manipulating Data Items by Using Operators

3-11

Operator

Meaning

LIKE

Matches a character pattern

IS NULL

Is a null

BETWEEN ...AND...

Is between two values (inclusive)

IN (set)

Matches any of a list of values

!=

Is not equal to (VAX, UNIX, PC)

^=, :=

Is not equal to (IBM)

<>

Is not equal to (all systems)

NOT LIKE

Is not like comparison string

IS NOT NULL

Is not a null

Copyright © 2004, Oracle. All rights reserved.

3-12

Copyright © 2004, Oracle. All rights reserved.

Calculating Using Arithmetic Expressions

•

Create expressions on NUMBER and DATE data by using arithmetic operators: Operator

• •

3-13

Description

Priority

*

Multiply

1

/

Divide

1

+

Add

2

–

Subtract

2

Operators of the same priority are evaluated from left to right. Parentheses are used to force prioritized evaluation and to clarify statements. Copyright © 2004, Oracle. All rights reserved.

Manipulating Strings by Using the Concatenation Operator SELECT FROM WHERE

3-14

last_name||' works as '||job_id AS "Employees" employees employee_id > 200;

Copyright © 2004, Oracle. All rights reserved.

Combining Results by Using Set Operators UNION UNION ALL A

B

A

B

A

B

A

B

INTERSECT

MINUS

3-15

Copyright © 2004, Oracle. All rights reserved.

Using the UNION Operator Display the current and previous job details of all employees, displaying each combination of employee and job ID only once: SELECT FROM UNION SELECT FROM

employee_id, job_id employees employee_id, job_id job_history;

… … 3-16

Copyright © 2004, Oracle. All rights reserved.

Using the UNION Operator

SELECT FROM UNION SELECT FROM

employee_id, job_id, department_id employees employee_id, job_id, department_id job_history;

…

3-17

Copyright © 2004, Oracle. All rights reserved.

Using Expressions •

Simple expression: – – – – –

•

3-18

Column Pseudocolumn Constant Sequence number NULL

Compound expression: A combination of other expressions

Copyright © 2004, Oracle. All rights reserved.

Conditional Querying Using the CASE Expression Facilitates conditional queries by doing the work of an IF-THEN-ELSE statement: CASE expr WHEN [WHEN WHEN ELSE END

comparison_expr1 THEN return_expr1 comparison_expr2 THEN return_expr2 comparison_exprn THEN return_exprn else_expr]

Example: SELECT last_name, job_id, salary, CASE job_id WHEN 'IT_PROG' THEN 1.10*salary WHEN 'ST_CLERK' THEN 1.15*salary WHEN 'SA_REP' THEN 1.20*salary ELSE salary END "REVISED_SALARY" FROM employees;

3-19

Copyright © 2004, Oracle. All rights reserved.

3-20

Copyright © 2004, Oracle. All rights reserved.

Using Conditions

Condition

Description

EXISTS

Tests for existence of rows in a subquery

IN

Tests a value for membership in a list of values or subquery

IS EMPTY

Tests whether a specified nested table is empty, regardless of whether any elements of the collection are NULL

3-21

Copyright © 2004, Oracle. All rights reserved.

Understanding Functions

Function

Input

Function performs the operation.

arg 1 arg 2

arg n

3-22

Copyright © 2004, Oracle. All rights reserved.

Output

Result value

Two Types of SQL Functions

Function

Single-row functions

3-23

Multiple-row functions

Copyright © 2004, Oracle. All rights reserved.

Manipulating Data Items Using Single-Row Functions Character

General

Number

Single-row functions

Conversion

Datetime

Date

3-24

Copyright © 2004, Oracle. All rights reserved.

3-25

Copyright © 2004, Oracle. All rights reserved.

Using Numeric Functions •

ROUND: Rounds a value to a specified decimal – ROUND(45.926, 2)

•

45.93

TRUNC: Truncates a value to a specified decimal – TRUNC(45.926, 2)

•

MOD:

45.92

Returns remainder of division

– MOD(1600, 300)

•

ABS:

Returns the absolute value of the argument

– ABS(–15)

•

15

SIGN: Returns –1 or 1 based on the argument – SIGN(-15)

3-26

100

–1

Copyright © 2004, Oracle. All rights reserved.

Using the ROUND and TRUNC Functions

SELECT ROUND(45.923, 2), ROUND(45.923, -1), ROUND(45.923, 0) FROM dual;

SELECT FROM

3-27

TRUNC(45.923, 2), TRUNC(45.923, -1), TRUNC(45.923,0) dual;

Copyright © 2004, Oracle. All rights reserved.

3-28

Copyright © 2004, Oracle. All rights reserved.

Practice 3: Overview of Part 1 This practice covers the following topics: • Executing a SELECT statement • Implementing pseudocolumns in SQL • Utilizing the different types of operators, conditions, expressions, and functions in SQL

3-29

Copyright © 2004, Oracle. All rights reserved.

Understanding Data Types Data Type

Description

VARCHAR2(size)

Variable-length character data

CHAR(size)

Fixed-length character data

NUMBER(p,s)

Variable-length numeric data

DATE

Date and time values

LONG

Variable-length character data, up to 2 GB

CLOB

Character data, up to 128 TB

RAW and LONG RAW

Raw binary data

BLOB

Binary data, up to 128 TB

BFILE

Binary data stored in an external file, up to 128 TB A 64-base number representing the unique address of a row in its table

ROWID

3-30

Copyright © 2004, Oracle. All rights reserved.

3-31

Copyright © 2004, Oracle. All rights reserved.

3-32

Copyright © 2004, Oracle. All rights reserved.

Using Datetime Data Types In Oracle SQL, you can include the time zone in your date and time data, and provide support for fractional seconds. Three additional data types: • TIMESTAMP • TIMESTAMP WITH TIME ZONE (TSTZ) • TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) Daylight savings support for datetime data types is provided.

3-33

Copyright © 2004, Oracle. All rights reserved.

3-34

Copyright © 2004, Oracle. All rights reserved.

Using Datetime Data Types

•

TIMESTAMP data type:

TIMESTAMP[(fractional_seconds_precision)]

•

TIMESTAMP WITH TIME ZONE data type:

TIMESTAMP[(fractional_seconds_precision)] WITH TIME ZONE

•

TIMESTAMP WITH LOCAL TIME ZONE data type:

TIMESTAMP[(fractional_seconds_precision)] WITH LOCAL TIME ZONE

3-35

Copyright © 2004, Oracle. All rights reserved.

3-36

Copyright © 2004, Oracle. All rights reserved.

Using INTERVAL Data Types •

INTERVAL YEAR TO MONTH stores a period of time using the YEAR and MONTH datetime fields.

INTERVAL YEAR [(year_precision)] TO MONTH

•

INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds.

INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]

3-37

Copyright © 2004, Oracle. All rights reserved.

3-38

Copyright © 2004, Oracle. All rights reserved.

3-39

Copyright © 2004, Oracle. All rights reserved.

Converting Between Data Types • •

Data type conversion can be implicit or explicit. Implicit data type conversion: For assignments, the Oracle server can automatically convert the following: From

To

VARCHAR2 or CHAR NUMBER or DATE

3-40

NUMBER

VARCHAR2

DATE

VARCHAR2

Copyright © 2004, Oracle. All rights reserved.

3-41

Copyright © 2004, Oracle. All rights reserved.

Converting Data Types Explicitly

TO_NUMBER

NUMBER

CHARACTER

TO_CHAR

3-42

TO_DATE

DATE

TO_CHAR

Copyright © 2004, Oracle. All rights reserved.

Displaying a Date in a Specific Format The format model: • Must be enclosed in single quotation marks and is case sensitive • Can include any valid date format element • Has an fm element to remove padded blanks or suppress leading zeros • Is separated from the date value by a comma TO_CHAR(date, 'fmt')

3-43

Copyright © 2004, Oracle. All rights reserved.

Date Format Model Elements

3-44

YYYY

Full year in numbers

YEAR

Year spelled out

MM

Two-digit value for month

MONTH

Full name of the month

DY

Three-letter abbreviation of the day of the week

DAY

Full name of the day

Copyright © 2004, Oracle. All rights reserved.

Date Format Model Elements •

Use time elements to format the time portion of the date:

HH24:MI:SS AM

•

Add character strings by enclosing them in double quotation marks:

DD "of" MONTH

•

12 of OCTOBER

Use number suffixes to spell out numbers:

ddspth

3-45

15:45:32 p.m.

fourteenth

Copyright © 2004, Oracle. All rights reserved.

3-46

Copyright © 2004, Oracle. All rights reserved.

Using the TO_CHAR Function with Dates and Numbers SELECT last_name, TO_CHAR(hire_date, 'fmDD Month YYYY') HIREDATE FROM employees WHERE employee_id > 200;

SELECT FROM WHERE

3-47

TO_CHAR(salary,'$99,999') SALARY employees last_name = 'Kochhar';

Copyright © 2004, Oracle. All rights reserved.

3-48

Copyright © 2004, Oracle. All rights reserved.

3-49

Copyright © 2004, Oracle. All rights reserved.

Using the TO_NUMBER and TO_DATE Functions •

To convert a character string to a number format, use the TO_NUMBER function:

TO_NUMBER(char[, 'fmt'])

•

To convert a character string to a date format, use the TO_DATE function:

TO_DATE(char[, 'fmt'])

3-50

Copyright © 2004, Oracle. All rights reserved.

Summary In this lesson, you should have learned how to: • Execute a SELECT statement • Implement pseudocolumns in SQL • Utilize the different types of operators, conditions, expressions, and functions in SQL • Convert between data types • Describe how SQL treats NULLs

3-51

Copyright © 2004, Oracle. All rights reserved.

Practice 3: Overview of Part 2 This practice covers the following topics: • Converting between data types • Describing how SQL treats NULLs

3-52

Copyright © 2004, Oracle. All rights reserved.

3-53

Copyright © 2004, Oracle. All rights reserved.

3-54

Copyright © 2004, Oracle. All rights reserved.

3-55

Copyright © 2004, Oracle. All rights reserved.

3-56

Copyright © 2004, Oracle. All rights reserved.