Guide to Oracle 10g

Chapter 3B

By Ahmed M. Zeki for ITBIS373

Objectives
! Write SQL queries to retrieve data from a single
database table
! Create SQL queries that perform calculations on
retrieved data
! Use SQL group functions to summarize retrieved
data

2 / 50
Retrieving Data From a Single Table
! Syntax
SELECT columnname1, columnname2, …
FROM ownername.tablename If you retrieve data from a table
in your own user schema you
[WHERE search_condition]; can omit the ownername. The
table owner must have granted
privilege.
! Ex:
FROM scott.location
! Retrieve all of columns
◦ Use asterisk ( * ) as wildcard character in SELECT clause
◦ SELECT * from …

3 / 50
Retrieving Selected Column Values
SQL> SELECT s_first, s_mi, s_last
2 FROM STUDENT;

S_FIRST S S_LAST
----------------------- - ------------------------
Tammy R Jones
Jorge C Perez
John A Marsh
Mike Smith
Lisa M Johnson
Ni M Nguyen

6 rows selected.

4 / 50
Retrieving Data From a Single Table
! To retrieve all of the columns in a table use
asterisk *

SELECT *
FROM LOCATION;

! SQL*Plus shows query output one page at a time.

5 / 50
Suppressing Duplicate Rows
! SQL DISTINCT qualifier
◦ Examines query output before it appears on screen
◦ Suppresses duplicate values
SELECT f_rank FROM faculty; retrieves duplicate
values.
! SELECT DISTINCT columnname;

6 / 50
Using Search Conditions in SELECT Queries

! Use search conditions to retrieve rows matching specific

criteria
◦ Exact search conditions
● Use equality operator =
◦ Inexact search conditions
● Use inequality operators > < >= <=
! Ex:
SELECT f_first, f_mi, f_last, f_rank
FROM FACULTY
WHERE f_rank = ‘Associate’;
! Ex:
SELECT room
FROM LOCATION
WHERE bldg_code = ‘BUS’
AND CAPACITY >= 40;

7 / 50
Searching for NULL and NOT NULL Values

! To retrieve enrollment rows for courses in which

the instructor has not yet assigned a grade.
◦ WHERE columnname IS NULL
◦ WHERE columnname IS NOT NULL

8 / 50
Searching for NULL and NOT NULL Values

9 / 50
Using the IN and NOT IN Comparison
Operators
! Match data values that are members of a set of
search values.

10 / 50
Using the LIKE Comparison Operators

! Part of a character string

! Ex: find all courses with the text string MIS in the
COURSE_NO column
! WHERE column LIKE ‘character_string’
! Must contain either the percent sign (%) or
underscore (_) wildcard characters.
◦ % represents multiple characters
◦ _ represents one single character
! WHERE term_desc LIKE ‘%2006’ the last 4 char are 2006
! WHERE term_desc LIKE ‘FALL%’
! WHERE course_name LIKE ‘%Systems%’
! WHERE s_class LIKE ‘_R’
! WHERE c_sec_day LIKE ‘_T%’ ! MT, MTW and MTWRF
11 / 50
Using the LIKE Comparison Operators

12 / 50
Sorting Query Output
! Oracle does not store the rows in any particular
order.
! In the same order in which they were inserted.
! ORDER BY clause
◦ Sort query output
◦ Syntax for select with ordered results
SELECT columnname1, columnname2, …
FROM ownername.tablename
WHERE search_condition
ORDER BY sort_key_column;

13 / 50
Sorting Query Output
! Sort can be ascending (default)
◦ NUMBER: small to big
◦ Character: alphabetical order
◦ Date: older to more recent
! Descending (use DESC)

14 / 50
Sorting Query Output

15 / 50
Sorting Query Output
! Can specify multiple sort keys

16 / 50
Using Calculations in SQL Queries
! Perform many calculations directly within SQL
queries
◦ Very efficient way to perform calculations
! Create SQL queries
◦ Perform basic arithmetic calculations : / * + -
◦ Use variety of built-in functions
▪ Example:
SELECT inv_id, qoh*price
FROM inventory;

17 / 50
Using Calculations in SQL Queries

18 / 50
Date Calculations
! SYSDATE pseudocolumn
◦ Retrieves current system date

SELECT SYSDATE
FROM DUAL;
◦ If you retrieve the SYSDATE value along with other DB
columns from another table, you can omit DUAL from the
FROM clause.

! Use + and – to calculate differences between

dates
o_date + 10 (you can’t multiply or divide values of the DATE)

19 / 50
Date Calculations
! To calculate # of days between two dates SYSDATE
– o_date

20 / 50
Date Calculations

21 / 50
Interval Calculation
! The oracle 10g DBMS can perform calculations
using interval values that store elapsed time value.

22 / 50
Interval Calculation
! Ex: update the TIME_ENROLLED column every
month by adding one month to the interval value.
! SELECT s_id, time_enrolled +
TO_YMINTERVAL(‘0-1’) FROM student;

! Ex: add an interval of 10 minutes to the

C_SEC_DURATION column in the
COURSE_SECTION table.
! SELECT c-sec_id, c_sec_duration +
TO_DSINTERVAL (‘0 00:10:00’)
FROM course_section;

23 / 50
Oracle 10g SQL Functions
! Built-in functions to perform calculations and
manipulate retrieved data values
! Called single-row functions
◦ Return single result for each row of data retrieved
! To use:
◦ List function name in SELECT clause followed by required
parameter in parentheses

24 / 50
Oracle 10g SQL Functions

25 / 50
Oracle 10g SQL Group Functions

26 / 50
Single-row
Character
Functions

‘Fall 1995’

27 / 50
Single-row Character Functions

28 / 50
Single-row Date Functions

29 / 50
Single-row Date Functions

30 / 50
Oracle 10g SQL Group Functions
! Display information about a group of rows.
! Display data that summarizes multiple rows.
! Returns a single result, such as a column sum.

! Examples
◦ Total number of students who enroll in a specific section.
◦ Total revenue generated from all orders that customers
place.

31 / 50
Oracle 10g SQL Group Functions
! Function name followed by column name in parentheses.

32 / 50
Oracle 10g SQL Group Functions
! Example: find the maximum enrollment for all course
sections and calculate the average, maximum and
minimum current enrollment for each course for the
summer 2007 term (i.e. TERM_ID = 6).

33 / 50
Oracle 10g SQL Group Functions
! COUNT group function works differently.
! Returns an integer that represents the number of rows
that a query returns.
! COUNT(*): calculates the total number of rows in a table
that satisfy a given search condition.
◦ The only function that includes NULL while others ignore it.
! COUNT(columnname): calculates the number of rows in
a table that satisfy a given search condition and also
contain a non-null value of a given column.

34 / 50
Oracle 10g SQL Group Functions
! Example: calculate the total number of courses in which
Lisa Johnson (S_ID = JO101) has enrolled and the total
number of courses in which she has received a grade
(GRADE is not NULL).

35 / 50
Using GROUP BY to Group Data
! If a query retrieves multiple rows and the rows in
one of the retrieved columns have duplicate
values, you can use GROUP BY to group the
output by the column with duplicate values and
apply group functions to the grouped data.

! Syntax
GROUP BY group_columnname;

! GROUP BY follows FROM clause

36 / 50
Using GROUP BY to Group Data

37 / 50
Using GROUP BY to Group Data
! All columns listed in SELECT clause must be
included in GROUP BY clause
◦ If ungrouped columns are included in the SELECT clause,
Oracle will return an error because SQL can’t display
single-row results and group function results in the same
query output.

Occurs because you attempt to mix

single rows and grouped rows in the
same query.

38 / 50
Using HAVING to Filter Group Data
! Places search condition on results of queries that
display group function calculations

! Syntax
HAVING group_function comparison_operator value

39 / 50
Using HAVING to Filter Group Data
! Example
◦ Retrieve the total capacity of each buildings that have a
capacity of less than 100.
HAVING sum(capacity) >= 100

40 / 50
Creating Alternate Column Headings
! Default: Column headings for retrieved columns are names of DB
table columns

! Alternate output heading text:

SELECT columnname1 "heading1_text ",
columnname2 "heading2_text", …

41 / 50
Creating Alternate Column Headings

! Changing the output heading changes only the

SQL*Plus output display.

! You can’t use the alternate heading to reference

the column in a GROUP BY or ORDER BY clause
in a query.

42 / 50
Creating Alternate Column Headings

! An alias is an alternate name for a query column.

Alias can be referenced in other parts of the query
such as in the GROUP BY or ORDER BY clause.
! Syntax
◦ SELECT columnname1 AS alias_name1…

Alias appears in
43 / 50
uppercase letters
Modifying the SQL*Plus Display
Environment
! A SQL*Plus page consists of a specific number of
characters per line and a specific number of lines
per page.

! The output wraps to the next line if the line

contains more characters than the page width. And
the column heading repeat after the values at the
top of each page.

44 / 50
Modifying the SQL*Plus Display
Environment

45 / 50
Modifying the SQL*Plus Display
Environment

46 / 50
Modifying the SQL*Plus Display
Environment
! Options > Environment
◦ Change the linesize and the pagesize
◦ The change will be saved until you change it.

47 / 50
Formatting Data using Format Models

! Use TO_CHAR function to convert the column to a

character string, and then apply the desired format
model to the value.
! Syntax
TO_CHAR(column_name, 'format_model')
! Note: DATE stores time as well.

48 / 50
Formatting Data using Format Models

49 / 50
Formatting Data using Format Models

! You can also use the TO_CHAR function to apply

format models to NUMBER columns.

! Example:

SELECT course_name, TO_CHAR(credits*86.95, ‘$99.999’)

FROM course
WHERE course_no = ‘MIS 101’;

50 / 50