Transcript Lesson-B

ITBIS373 Database
Development
Lecture3b - Chapter 3:
Using SQL Queries to
Insert, Update, Delete, and
View Data
Guide to Oracle 10g
Lesson B Objectives
After completing this lesson, you should be
able to:
 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
Guide to Oracle 10g
2
Retrieving Data From a Single
Database Table

Syntax
SELECT columnname1, columnname2, …
FROM ownername.tablename
[WHERE search_condition];

Retrieve all of columns


Use asterisk ( * ) as wildcard character in
SELECT clause
SELECT * from …
Guide to Oracle 10g
3
Suppressing Duplicate Rows

SQL DISTINCT qualifier



Examines query output before it appears on
screen
Suppresses duplicate values
Syntax

SELECT DISTINCT columnname;
Guide to Oracle 10g
4
Guide to Oracle 10g
5
Using Search Conditions in
SELECT Queries

Use search conditions to retrieve rows
matching specific criteria

Exact search conditions


Inexact search conditions


Use equality operator
Use inequality operators
Search for NULL or NOT NULL values


WHERE columnname IS NULL
WHERE columnname IS NOT NULL
Guide to Oracle 10g
6
Guide to Oracle 10g
7
Using Search Conditions in
SELECT Queries (continued)

IN comparison operator

Match data values that are members of a set of
search values
Guide to Oracle 10g
8
Guide to Oracle 10g
9

LIKE operator


Use to match part of character string
Syntax
 WHERE columnname LIKE 'string'
 Character string should contain wildcard character %, or _,
or both.
 The percent sign (%) wildcard character represents multiple
characters. If you place (%) on the left edge of the
character string to be matched, the DBMS searches for an
exact match on the far-right characters and allows an exact
match for the characters represented by(%).
Guide to Oracle 10g
10



For example, the search condition WHERE term_desc LIKE
‘%2006’ retrieves all term rows in which the last four characters
in TERM_DESC column 2006. The DBMS ignores the characters
on the left side of the character string up to the substring 2006.
The search condition WHERE term_desc LIKE ‘Fall%’ retrieves
all term rows in which the first four characters are Fall, regardless
of the value of the rest of the string.
The search condition WHERE course_name LIKE’%Systems%’
retrieves every row in the COURSE table in which the
COURSENAME column contains the character string Systems
any where in the string.
Guide to Oracle 10g
11


The underscore ( _ ) wildcard character represents a single
character. For example, the search condition WHERE s_class
LIKE ‘_R’ retrieves all values for S_CLASS in which the first
character can be any value, but the second character must be
the letter R.
You can use the underscor ( _ ) and percent sign (%) wildcard
characters together in a single search condition. For example,
the search condition WHERE c_sec-day LIKE ‘_T%’ retrieves
all course sections that meet on Tuesday, provided exactly one
character proceeds T in the C_SEC_DAY column. The search
condition ignores all of the characters that follow T in the column
table value, so the query retrieves values such as MT, MTW, and
MTWRF.
Guide to Oracle 10g
12
Guide to Oracle 10g
13
Sorting Query Output

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;


Sort can be ascending or descending
Can specify multiple sort keys
Guide to Oracle 10g
14
Guide to Oracle 10g
15
Guide to Oracle 10g
16
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
Guide to Oracle 10g
17

Arithmetic operations on retrieved data





Addition (+)
Subtraction (-)
Multiplication (*)
Division (/)
Example:
SELECT inv_id, qoh*price
FROM inventory;
Guide to Oracle 10g
18
Guide to Oracle 10g
19
Performing Arithmetic
Calculations

Perform arithmetic calculations on columns
that have data types




SYSDATE pseudocolumn


NUMBER
DATE
INTERVAL
Retrieves current system date
Use + and – to calculate differences between
dates
Guide to Oracle 10g
20
Guide to Oracle 10g
21
Guide to Oracle 10g
22
Interval Calculation

The oracle 10g DBMS can perform
calculations using interval values that store
elapsed time value.
Guide to Oracle 10g
23

Ex: suppose you need to update the
TIME_ENROLLED column every month by adding
one month to the interval value.

SELECT s_id, time_enrolled+TO_YMINTERVAL(‘01’) FROM student;
Similarity, you use the following query to 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;
Guide to Oracle 10g
24
Oracle 10g SQL Functions


Built-in functions 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
Guide to Oracle 10g
25
Guide to Oracle 10g
26
Guide to Oracle 10g
27
Single-row Character
Functions
Guide to Oracle 10g
28
Guide to Oracle 10g
29
Guide to Oracle 10g
30
Single-row Data Functions
Guide to Oracle 10g
31
Guide to Oracle 10g
32
Oracle 10g SQL Group
Functions

Group function



Performs operation on group of queried rows
Returns single result such as column sum
To use:

List function name followed by column name in
parentheses
Guide to Oracle 10g
33
Guide to Oracle 10g
34
Guide to Oracle 10g
35
Using the COUNT Group
Function

The COUNT group function returns an
integer that represents the number of rows
that a query returns. The COUNT(*) version
of this function calculates the total number of
rows in a table that satisfy a given search
condition. The COUNT(*) function is the only
group function in Table3-10 that include NULL
values. The other functions ignore NULL
values.
Guide to Oracle 10g
36
Guide to Oracle 10g
37
Using the GROUP BY Clause
to Group Data

GROUP BY clause




Group output by column with duplicate values
Apply group functions to grouped data
Syntax

GROUP BY group_columnname;

Follows FROM clause
All columns listed in SELECT clause must be
included in GROUP BY clause
Guide to Oracle 10g
38
Guide to Oracle 10g
39
Guide to Oracle 10g
40
Guide to Oracle 10g
41
Using the HAVING Clause to
Filter Grouped Data

HAVING clause


Syntax


Place search condition on results of queries that
display group function calculations
HAVING group_function
comparison_operator value
Example

HAVING sum(capacity) >= 100
Guide to Oracle 10g
42
Guide to Oracle 10g
43
Creating Alternate Column
Headings


Column headings for retrieved columns are names of database
table columns
Specify alternate output heading text
SELECT columnname1 "heading1_text ",
columnname2 "heading2_text", …
Guide to Oracle 10g
44
Creating Alternate Column
Headings (continued)

Alias
 Alternate name for query column
 Syntax
 SELECT columnname1 AS alias_name1…
Guide to Oracle 10g
45
Formatting Data Using Format
Models

TO_CHAR function



Convert column to character string
Apply desired format model to value
Syntax


TO_CHAR(column_name, 'format_model')
Use for data types



DATE
INTERVAL
NUMBER
Guide to Oracle 10g
46
Guide to Oracle 10g
47