Chapter 3: Using SQL Queries to Insert, Update, Delete, and View Data

1
Chapter 3Using SQL Queries to Insert,Update,
Delete, and View Data

• Jason C. H. Chen, Ph.D.
• Professor of MIS
• School of Business Administration
• Gonzaga University
• Spokane, WA 99258
• chen_at_jepson.gonzaga.edu

2

• Lesson A
• Manually Add, View, Update Data
• running a script SQL PLUS
• Seqences and Object Privileges
• Lesson B
• Retrieve Data, Sort, Calculations
• Functions - Number, Date, Group
• Create Column Headings, Aliases
• Create Oracle Dynamic SQL Queries
• Format Output
• Lesson C
• Queries to Join multiple tables
• SET operators, Database view, Indexes,

3
Lesson A Objectives

• Learn how to run a script to create database
  tables automatically
• Learn how to insert data into database tables
• Learn how to create database transactions and
  commit data to the database
• Create search conditions in SQL queries
• Understand how to update and delete database
  records, and how to truncate tables
• Learn how to create and use sequences to generate
  surrogate key values automatically
• Learn how to grant and revoke database object
  privileges

4
Lesson A.

• Running a SQL script
• SQLgt start c\oradata\chapter3A\emptynorthwoods.sq
  l
• SQLgt _at_ c\oradata\ chapter3A\emptyclearwater.sql
• Insert Data
• INSERT INTO lttablenamegt
• VALUES (column1 value, col2 value, ... )
• COMMIT
• ROLLBACK
• SAVEPOINT ltpoint_namegt
• ROLLBACK TO ltpoint_namegt

5
Scripts

• Scripts are simply files that contain a sequence
  of SQL and/or PL/SQL commands that can be
  executed in SQLPlus.
• Note
• SQL and PL/SQL are two languages Oracle uses.
• SQLPlus is the tool we use to access Oracle.
• SQLPlus can execute both SQL and PL/SQL
  commands plus it has its own commands.

6
Six Important Script Files

• Six files are available on the web and Jepson
  server (under Network neighborhood)
  Jepsonnt2\chen\bmis341_mbus673\OraData\ then
  look for each chapter and LESSON
• Northwood univeristy database
• dropnorthwoods.sql (drop northwoods tables, no
  DDs)
• emptynorthwoods.sql (drop all tables DDs)
• northwoods.sql (drop, then create DDs, insert
  data into database)
• Clearwater Traders database
• dropclearwater.sql (drop clearwater tables, no
  DDs)
• emptyclearwater.sql (drop all tables DDs)
• clearwater.sql (drop, then create DDs, insert
  data into database)

7
Format Models

• Used to format data retrieved from database
• Can be used to format a date to display time or a
  number to display as a currency

8
Inserting Data into Tables

• INSERT command adds new records
• Field values should match column order, or be
  specified in command
• INSERT INTO faculty (F_ID, F_LAST, F_FIRST,
  F_MI, LOC_ID) VALUES (1, 'Cox', 'Kim', 'J', 9)

See Figures 3-1 to 3-3
9
Inserting Date and Interval Values

• Use to_date function to convert a character
  string to a date
• Specify date string and matching format model
• TO_DATE('08/24/2004', 'MM/DD/YYYY')
• TO_DATE('1000 AM', 'HHMI AM')
• TO_DATE(29-Aug-05,DD-MON-YY)
• Use functions to convert character strings to
  intervals
• TO_YMINTERVAL('4-9') inserts a positive interval
  of 4 years, 9 months
• TO_DSINTERVAL(4 011500') inserts a positive
  interval of 4 days, 1 hour, 15 minutes, 0 seconds

Figure 3-4 p.95
10
Inserting LOBs

• Before inserting LOB must insert a LOB locator
• LOB locator a structure that contains
  information that identifies the LOB data type and
  points to the alternate memory location
• Write a program or use a utility to add LOB data
  to database
• Use EMPTY_BLOB() function to insert a LOB locator
• P. 96

11
Creating Transactions and Committing New Data

• Transaction series of action queries that
  represent a logical unit of work
• User can commit (save) changes
• User can roll back (discard) changes
• Pending transaction a transaction waiting to be
  committed or rolled back
• Oracle DBMS locks records associated with pending
  transactions
• Other users cannot view or modify locked records

Practice up to p.96 (Figs 3-1 to 3-4)
12
Commit and Roll Back in SQLPlus
_at_ c\OraData\Chapter3A\emptynorthwoods.sql SELECT
FROM course

• Transactions begin automatically with first
  command
• Type COMMIT to commit changes
• Type ROLLBACK to roll back changes

13
Savepoints

• A bookmark that designates the beginning of an
  individual section of a transaction
• Changes are rolled back to savepoint

Figure 3-6 (p.98)
14
Creating Search Conditions in SQL Queries

• An expression that seeks to match specific table
  records
• Used in SELECT, UPDATE and DELETE statements
• WHERE fieldname comparison_operator
  search_expression
• WHERE S_ID 1

15
Defining Search Expressions

• Character strings
• Must be enclosed in single quotes
• Case sensitive
• Dates
• Use to_date function with date string and format
  model
• Intervals
• Use to_yminterval and to_dsinterval with interval
  string format model

pp. 100-101
e.g., WHERE s_class SR WHERE s_dob
TO_DATE (01/01/1980, MM/DD/YYYY)
16
Creating Complex Search Conditions

• Combines multiple search conditions using the
  AND,OR, and NOT logical operators.
• AND both conditions must be true
• OR one or both condition must be true
• NOT opposite of actual value
• Use () to group logical operators

e.g., WHERE bldg_code CR AND capacity gt 50
17
Updating and Deleting Existing Table Records

• UPDATE
• Updates field values in one or more records in a
  table
• Only one table may be updated at a time
• UPDATE tablename SET field1 new_value1, field2
  new_value2, ... WHERE search condition
• DELETE
• Removes specific records from a database table
• If search condition is omitted, entire table data
  is removed
• DELETE FROM tablename WHERE search condition

Figures 3-7 thru 3-9, p.103-105 SELECT FROM
faculty -- what happen?
18
Truncating Tables Updating and Deleting Existing
Table Records

• Removes all table data without saving any
  rollback information
• Advantage fast way to delete table data
• Disadvantage cant be undone (rollback).
• Syntax
• TRUNCATE TABLE tablename

ALTER tablename DISABLE CONSTRAINT
constraint_name ALTER tablename ENABLE
CONSTRAINT constraint_name
TRUNCATE TABLE location Practice (note that fk
must be disabled) Figures 3-10 p.105
L, N
19
Sequences

• Sequential lists of numbers to create unique
  surrogate key values that is automatically
  generated by the DBMS
• To use a sequence
• SELECT sequence_name.NEXTVAL FROM DUAL
• INSERT INTO location LOC_ID) VALUES(loc_id_sequenc
  e.NEXTVAL)

Fig. 3-12 (p.108)
20
VIEW and DROP SEQUENCE

• Examples
• SELECT
• FROM user_sequences
• DROP SEQUENCE loc_id_sequence
• SELECT
• FROM user_sequences

What is the purpose of using SEQUENCE?
Figures 3-12 thru 3-15 p.108-111
21
Basic SQL Concepts

• DDL (Data Definition Language)
• commands that work with the objects (tables,
  indexes, views, etc.) in the database. e..g.,
  CREATE, ALTER, DROP, and RENAME.
• DML (Data Manipulation Language)
• commands that work with the (physical) data in
  the database. e.g., SELECT, INSERT, UPDATE, and
  DELETE
• DCL (Data _______ Language)
• commands that control a database, including
  administering privileges. e.g., GRANT, REVOKE.

22
Database Object Privileges

• Privileges must be granted so that other users
  can access objects in user schema
• GRANT privilege1, privilege2,ON object_name TO
  user1,user2,...
• REVOKE privilege1, privilege2,... ON object_name
  FROM user1, user2, ...
• To grant or revoke privileges for everyone use
  PUBLIC as user

Fig. 3-16 (p. 113)
23
Practice

• Type the following command
• SELECT FROM location
• I grant the following to all of you
• GRANT SELECT ON location
• TO PUBLIC
• You type the following again
• SELECT FROM chen.location
• I revoke the following from you
• REVOKE SELECT ON location FROM PUBLIC
• You type the following again
• SELECT FROM chen.location

24
Examples of Object Privileges
25
Pseudocolumns

• Acts like a column in a database query
• Actually a command that returns a specific values
• Used to retrieve
• Current system date
• Name of the current database user
• Next value in a sequence

26
Using Pseudo-columns

• Retrieving the current system date
• SELECT SYSDATE
• FROM DUAL
• Retrieving the name of the current user
• SELECT USER
• FROM DUAL
• DUAL is a system table that is used with
  pseudo-columns

27
The Main DDL Commands

• Here are main DDL Commands
• CREATE
• DROP
• ALTER
• RENAME

28
The Main DML Commands

• There are seven main DML Commands
• INSERT
• SELECT
• UPDATE
• DELETE
• COMMIT
• ROLLBACK
• SAVEPOINT

29
Homework
Email me with one attachment to chen_at_jepson.gonza
ga.edu with subject title of bmis441_Oracle3A
Problem-Solving Cases

• HWA email to me
• 1. Run two script files emptyclearwater.sql and
  emptynorthwoods.sql
• (e.g., _at_c\oradata\chapter3A\emptyclearwater.
  sql
• _at_c \oradata\chapter3A\emptyclearwater.
  sql)
• 2. Complete 1, 2 and 3 (pp. 118-119) using
  NotePad and save them as Ch3ACase1_Lname_Fname.sq
  l (respectively) and test them successfully.(ADD
  Personal/Class/question Information)
• 3. Make sure mark (document) your script for each
  question as 1a, 1b, .. 2a, 2b, , 3a, 3b, , 3d
  (fail to do this will not receive full credits)
• 4. When you done, spool the script file (see next
  slide for spooling instructions) and email the
  file to me by the midnight before the next class.
• B Draw (use Word/Visio) E/R for Northwoods
  University database (bring hardcopy)

L
30
Correction on p. 118

• Hi Professor, I had a quick question on page 118
  of the homework.
• Part C of problem 1 says to insert records into
  ORDERS and Part D says to insert them into
  ORDER_SOURCE. But in order for it to work, I
  believe we need to insert ORDER_SOURCE before you
  insert ORDERS. Should we do Part D of problem 1
  before Part C? Thanks!
• --- 1(c)
• --- inserting records into ORDER_SOURCE
• --- 1(d)
• --- inserting records into orders

31
How to Spool your Script and Output Files

• After you tested the script file of
  Oracle3A_Lname_Fname.sql, follow the
  instructions below to spool both script and
  output files
• 1. Click File, Spool then Spool File
• 2. Enter the spooled file name
  c/Oracle3A_Spool_Lname_Fname.lst (default file
  extension)
• 3. open Ch3ACase1_Lname_Fname.sql file
• 4. copy and paste all the SQL commands (including
  all comments) to the SQLPLUS
• 5. Repeat steps 3 and 4 for the rest of script
  files
• 6. Click on File, Spool then Spool Off
• Do NOT double click the spooled file, with the
  note pad open, select FILE, OPEN then open the
  spooled file (since SPSSs default file extension
  is LST)

32
Lesson B Objectives

• Learn how 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

33
The Main DDL and DML Commands

• Here are main DDL Commands
• CREATE
• DROP
• ALTER
• RENAME
• There are seven main DML Commands
• INSERT
• SELECT
• UPDATE
• DELETE
• COMMIT
• ROLLBACK
• SAVEPOINT

34
SELECT
Copy two files to your floppy and run them SQLgt
_at_ c\OraData\chapter3BC\northwoods.sql SQLgt _at_
c\OraData\chapter3BC\clearwater.sql
Used to get data out of the database. Has six
main clauses but for today we will only talk
about the most important three. SELECT -
indicates what to get (fields) FROM - indicates
where to get it (table, tables) WHERE -
indicates how to filter the results
L
35
Retrieving Data from Single Database Table

• SELECT fieldname1, fieldname2, ... FROM
  ownername.tablename WHERE search_condition
• To select
• All rows omit where clause
• All fields, use SELECT FROM
• Only unique field values SELECT DISTINCT
  fieldname
• Search condition
• Use comparison and logical operators
• IS NULL/IS NOT NULL to match/exclude NULL values
• IN/NOT IN to match set values
• LIKE with wildcards and _ to match character
  strings

Practice (p.123 128) Figures 3-18 thru 3-22
36
Using Multiple Search Conditions

• Combining search conditions
• AND both conditions must be true
• OR either condition can be true
• Combining AND and OR in a single operation
• AND comparisons are evaluated first
• Always use parentheses to force conditions to be
  evaluated in the correct order for clear
  logical expression.

Practice See next slide
37
Using Multiple Search Conditions
LOC_ID BLDG_CODE ROOM CAPACITY ----------
---------- ------ ---------- 1 CR
101 150 2 CR 202
40 5 BUS 105
42 6 BUS 404 35
7 BUS 421 35 8 BUS
211 55 9 BUS 424
1 10 BUS 402
1 11 BUS 433 1 9
rows selected.
-- Version A SELECT FROM location WHERE
bldg_code 'BUS' OR bldg_code 'CR' AND
capacity gt 35 -- Version B SELECT FROM
location WHERE (bldg_code 'BUS' OR bldg_code
'CR') AND capacity gt 35
LOC_ID BLDG_CODE ROOM CAPACITY ----------
---------- ------ ---------- 1 CR
101 150 2 CR 202
40 5 BUS 105
42 8 BUS 211 55
38
Sorting Query Output
Practice (p.130-131) Figures 3-23 thru 3-24

• Use ORDER BY sort_key_field(s)
• Default order is ascending, use DESC to sort
  descending

39
Using Calculations in SQL Queries

• Calculations are performed by DBMS, result only
  sent to client
• Can use arithmetic operators (, -, , /)
• Place calculation in select clause SELECT price
  quantity FROM
• Calculations can be performed on NUMBER, DATE and
  INTERVAL fields only
• Single-row functions built in Oracle functions
  to perform calculations and manipulate retrieved
  data values

Practice (p.133) Figures 3-25 thru 3-28
40
Oracle 10g SQL Functions

• Single-row Number Functions
• Table 3-7 (p.137)
• Fig. 3-29 (p.138)
• Single-row Character Functions
• Table 3-8
• Fig. 3-30 (p. 140)
• Single-row Date Functions
• Table 3-9
• Fig. 3-31 (p. 141)

41
Oracle10g SQL Group Functions

• Group functions perform an operation on a group
  of queried rows and returns a single result

Practice SUM, COUNT Figure3-32 thru 33
42
Group By

• The group by clause is used to form groups of
  rows of a resulting table based on column
  clauses. When the group by clause is used, all
  aggregate operations are computed on the
  individual groups, not on the entire table.

Practice (p.145) Figures 3-34 35
43
GROUP BY Clauses Extra Examples
The group by clause is used to form groups of
rows of a resulting table based on column
clauses. When the group by clause is used, all
aggregate operations are computed on the
individual groups, not on the entire table.
--Figure G-0 -- NOT a group by expression
ROOM SELECT bldg_code, room, SUM(capacity), AVG(ca
pacity), MAX(capacity) FROM location WHERE
capacity gt 5 GROUP BY bldg_code
--Figure G-1 -- right version of group by SELECT
bldg_code, SUM(capacity), AVG(capacity),
MAX(capacity) FROM location WHERE capacity gt
5 GROUP BY bldg_code
44
--Figure G-1, p.141 -- the right version of group
by SELECT bldg_code, SUM(capacity), AVG(capacity),
MAX(capacity) FROM location WHERE capacity gt
5 GROUP BY bldg_code
--Figure G-2, p.141 -- the right version of group
by w/ having SELECT bldg_code, SUM(capacity)
TOTAL_CAPACITY, AVG(capacity), MAX(capacity) FROM
location WHERE capacity gt 5 GROUP BY
bldg_code HAVING AVG(capacity) gt42
Output from Figure G-1 BLDG_CODE SUM(CAPACITY)
AVG(CAPACITY) MAX(CAPACITY) ----------
------------- -------------
------------- BUS 167
41.75
55 CR 260
65 150
Output from Figure G-2 BLDG_CODE TOTAL_CAPACITY
AVG(CAPACITY) MAX(CAPACITY) ----------
------------- -------------
------------- CR
260 65
150
45
The Group by and Having Clauses
The having clause is used to eliminate certain
groups from further consideration. The following
query will produce the same results but with the
SUM capacity of at least 100.
SQLgt -- Figure 3-36, P.146 SQLgt SELECT bldg_code,
SUM(capacity) 2 FROM location 3 GROUP BY
bldg_code 4 HAVING SUM(capacity) gt
100 BLDG_CODE SUM(CAPACITY) ----------
------------- BUS 170 CR
260
L
46
SQLgt SQLgt SELECT bldg_code, room, SUM(capacity)
2 FROM location 3 GROUP BY bldg_code, room
BLDG_CODE ROOM SUM(CAPACITY) ----------
------ ------------- BUS 105
42 BUS 211 55 BUS 402
1 BUS 404
35 BUS 421 35 BUS 424
1 BUS 433
1 CR 101 150 CR 103
35 CR 105
35 CR 202 40 BLDG_CODE
ROOM SUM(CAPACITY) ---------- ------
------------- LIB 217 2 LIB
222 1 13 rows selected.
SQLgt -- Figure 3-34, P.140 SQLgt SELECT bldg_code,
SUM(capacity) 2 FROM location 3 GROUP BY
bldg_code BLDG_CODE SUM(CAPACITY) ----------
------------- BUS 170 CR
260 LIB 3
SQLgt -- Figure 3-36, P.142 SQLgt SELECT bldg_code,
SUM(capacity) 2 FROM location 3 GROUP BY
bldg_code 4 HAVING SUM(capacity) gt
100 BLDG_CODE SUM(CAPACITY) ----------
------------- BUS 170 CR
260
47
Formatting Output in SQLPlus

• To change default column headings
• Specify alternate column headings SELECT
  fieldname1 "heading1_text", fieldname2
  "heading2_text", ...
• Use an alias for column headings SELECT
  fieldname1 AS alias_name1...
• Figs 3-37 3-38 (p.147)
• To change SQLPlus line and page size settings
  (p.149)
• Select Options/Environment on menu bar
• Modify linesize and pagesize to desired values
• You may also type the following commands at SQLgt
• SQLgt SET LINESIZE 120
• SQLgt SET PAGESIZE 40
• Figs 3-39 3-40 (p.149-150)

48
Formatting Numbers and Dates

• Use to_char function with format models
• TO_CHAR(field_name, 'format_model')
• SELECT inv_id, TO_CHAR(inv_price, '99,999.99')
  FROM inventory WHERE item_id 1

Practice (p.152) Figures 3-41
49
Examples on TO_CHAR
SELECT TO_CHAR(SYSDATE, 'HHMISS, DD-MON-YYYY')
today FROM DUAL Sample Output TODAY ----------
----------- 122047, 26-SEP-2005
SELECT TO_CHAR(SYSDATE, 'HH24MISS,
DD-MON-YYYY') today, trunc(sysdate),
round(sysdate) FROM DUAL Sample Output TODAY
TRUNC(SYS ROUND(SYS ----------------
----- --------- --------- 121930, 26-SEP-2005
26-SEP-05 27-SEP-05
50
Advanced SQL Commands
Clear screen column ltfieldnamegt format a15
wrap column ltfieldnamegt format a15 word column
ltfieldnamegt format a20 word heading description
for the field column ltfieldnamegt format a15
truncate column ltfieldnamegt format 99999
wrap e.g.,
column inv_size format a5 heading 'SIZE' column
inv_qohinv_price format 99,999.99 heading
'Total Price' SELECT inv_id, inv_size, color,
inv_qoh inv_price FROM inventory where item_id
3 AND inv_size 'S'
INV_ID SIZE COLOR Total
Price ---------- ----- --------------------
----------- 3 S Khaki
4,492.50 6 S Navy
4,163.05
51
Formatting Output
--Extra file customer_report.sql TTITLE CENTER
'Clearwater Traders' SKIP 1 - CENTER
'' SKIP 2 - LEFT 'Customer
Report' BTITLE CENTER ' Confidential
' SET LINESIZE 100 SET PAGESIZE 25 COLUMN c_id
HEADING 'ID' FORMAT 999 COLUMN c_last HEADING
'Last Name' FORMAT A10 COLUMN c_first HEADING
'First Name' FORMAT A10 COLUMN c_mi HEADING 'MI'
FORMAT A2 COLUMN c_address HEADING 'Address'
FORMAT A18 COLUMN c_city HEADING 'City' FORMAT
A12 COLUMN c_state HEADING 'St' COLUMN c_zip
HEADING 'Zip' FORMAT A5 COLUMN c_dphone HEADING
'Day Phone' FORMAT A12 COLUMN c_ephone HEADING
'Eve. Phone' FORMAT A12 SELECT c_id,c_last,
c_first, c_mi, c_address, c_city, c_state, c_zip,
c_dphone, c_ephone FROM customer
Hyphen (-) continuation TTITLE OFF BTITILE
OFF COLUMN price HEADING Current Price FORMAT
9,999.99 COLUMN color HEADING Color FORMAT A10
52

Clearwater Traders
Customer Report
ID Last Name First Name MI Address
City St Zip Day Phone Eve.
Phone ---- ---------- ---------- --
------------------ ------------ -- -----
------------ ----------- 1 Harris Paula
E 1156 Water Street, Osseo WI 54705
7155558943 7155559035
Apt. 3 2 Garcia Maria H 2211
Pine Drive Radisson WI 54867 7155558332
7155558332 3 Miller Lee 699
Pluto St. NW Silver Lake WI 53821 7155554978
7155559002 4 Chang Alissa R 987
Durham Rd. Apple Valley MN 55712 7155557651
7155550087 5 Edwards Mitch M 4204
Garner Street Washburn WI 54891 7155558243
7155556975 6 Nelson Kyle E 232 Echo
Rd. Minnetonka MN 55438 7151113333
7155552222
Confidential
TTITLE CENTER 'Clearwater Traders' SKIP 1
- CENTER '' SKIP 2 - LEFT
'Customer Report' BTITLE CENTER '
Confidential '
53
General Form of SQLs SELECT
SELECT DISTINCT ltexpressiongt ,
ltexpressiongt FROM lttablenamegt ltaliasgt ,
lttablenamegt ltaliasgt WHERE ltsearch_conditiongt
AND ltother conditiongt OR ltother
condition ORDER BY ltcolumngt ,
ltcolumngtDESC GROUP BY ltcolumngt ,
ltcolumngt HAVING ltconditiongt
Note that is optional
is also optional besides the first required one.
54
Break ! (Ch. 3-B)Problem Solving Cases
In class exercise - 1,3,5 p. 151 - see spool
instruction on the slide in Ch-3A HW - run the
following two commands _at_c\oradata\chapter3BC\cle
arwater.sql _at_c \oradata\chapter3BC\clearwater.sq
l Output should be formated in a very good
manner. (ONLY print important fields for 2,4) -
Oracle 3B (ALL even numbers) pp. 156-157
Scripting file name Ch3Bcase_Lname_Fname.sql
Spooled file name Oracle3B_Spool_Lname_Fname.l
st
Email me with the spooled file using attachment
to chen_at_jepson.gonzaga.edu with subject title of
bmis441_Oracle3B or mbus673_Oracle3B
55
Using Runtime Variables

• Runtime variable variable defined in SQLPlus
  environment
• Syntax
• DEFINE variable_name variable_value
• You can then substitute the variable name for a
  query search condition value
• Syntax
• UNDEFINE variable_name variable_value

Practice Your turn!!
56
Using Runtime Variables - ACCEPT
-- File name bldg_capacity.sql ACCEPT
max_capacity PROMPT 'Enter the maximum capacity
needed' ACCEPT desired_bldg_code PROMPT 'Enter
the building code for the desired
building' SELECT bldg_code, room, capacity FROM
location WHERE capacity gt max_capacity AND
bldg_code 'desired_bldg_code'
ACCEPT only can be run from the command line.
SQLgt start C\OraData\chapter3BC\bldg_capacity.sql
Enter the maximum capacity needed45 Enter the
building code for the desired buildingCR old
3 WHERE capacity gt max_capacity new 3 WHERE
capacity gt 45 old 4 AND bldg_code
'desired_bldg_code' new 4 AND bldg_code
'CR' BLDG_CODE ROOM CAPACITY ----------
------ ---------- CR 101 150
57
Lesson C Objectives

• Learn how to create SQL queries that join
  multiple tables
• Learn how to create nested SQL queries
• Understand how to combine query results using set
  operators
• Create and use database views

58
Lesson CUsing Queries to Join Multiple Tables

• Running a SQL script
• SQLgt start c\OraData\chapter3BC\northwoods.sql
• SQLgt _at_ c\OraData\chapter3BC\clearwater.sql

• Retrieve data from multiple tables by joining
  tables using foreign key references
• Join query types
• Inner (equality/natural)
• Outer
• Self
• Inequality

59
Joining Multiple Tables

• Join combine data from multiple database tables
  using foreign key references
• SELECT field1, field2, ... FROM table1, table2
  WHERE table1.joinfield table2.joinfield AND
  search_condition(s)
• If tables share field names, must prefix field in
  select with table name (table1.field1,
  table2.field1)
• Join condition part of where clause indicating
  how tables are related (table1.foreign_key
  table2.primary key)
• Search conditions can be added to join condition
  using AND operator

60
Inner Join

• Join two tables based on values in one table
  being equal to values in another table
• Also known as equality join, equijoin or natural
  join
• One record is retrieved for each matching row
• Returns results only if records exist in both
  tables

Marx Zhulin Langley Brown Sealy
61
Inner Join (cont.)
Marx Zhulin Langley Brown Sealy
S_ID S_LAST S_FIRST F_ID F_LAST ------
-------- -------- ---- -------- JO100 Jones
Tammy 1 Marx PE100 Perez Jorge 1
Marx MA100 Marsh John 1 Marx SM100
Smith Mike 2 Zhulin JO101 Johnson
Lisa 4 Brown NG100 Nguyen Ni 3
Langley 6 rows selected.
-- Chapter 3, Figure 3-43, SELECT s_id, s_last,
s_first, student.f_id, f_last FROM student,
faculty WHERE student.f_id faculty.f_id
62
Natural Join Inner Join

• It can be used when the tables have a single
  commonly named and defined column.

S_ID S_LAST S_FIRST F_ID F_LAST ------
-------- -------- ---- -------- JO100 Jones
Tammy 1 Marx PE100 Perez Jorge 1
Marx MA100 Marsh John 1 Marx SM100
Smith Mike 2 Zhulin JO101 Johnson
Lisa 4 Brown NG100 Nguyen Ni 3
Langley 6 rows selected.
-- Chapter 3, Figure 3-43, SELECT s_id, s_last,
s_first, student.f_id, f_last FROM student,
faculty WHERE student.f_id faculty.f_id
-- Chapter 3, Figure 3-43, SELECT s_id, s_last,
s_first, f_id, f_last FROM student NATURAL JOIN
faculty
63
Joining Via Linking Table

• Query (p.162) List the last names of all faculty
  members who teach during the Summer 2007

Marx Zhulin Langley Brown Sealy

• You must include a join condition for every link
  between 2 tables
• If you have N tables in the FROM clause, you must
  have ??? join conditions

64
Joining Via Linking Table (cont.)
Marx Zhulin Langley Brown Sealy
-- Chapter 3, Figure 3-45, p.163 SELECT
f_last FROM faculty, course_section, term WHERE
faculty.f_id course_section.f_id AND
course_section.term_id term.term_id AND
term_desc 'Summer 2007'
F_LAST -------- Marx Zhulin Langley
Can you use NATURAL JOIN here? Why? How to write
a SQL query successfully and efficiently?
65
Using a Query Design Diagram

• Helpful for creating complicated queries
• Can use a formula to derive actual query from
  diagram

66
Query Display Course Name and GRADE for each of
Tammy Jones courses

• ?S_ID (j)
• C_SEC_ID (j)
• GRADE (d)

• ?COURSE_NO (j)
• COURSE_NAME (d)

Figure 3-46 Join query design diagram
j join s search d display
You can derive your query from the diagram by
following these steps 1. Place the display
fields in the SELECT clause 2. List all of the
tables in the FROM clause 3. Include the links in
join conditions in the WHERE clause 4. Include
all of the search fields in the WHERE clause
67
Cartesian Product
If you accidentally omit a join condition in a
multiple-table query, the result is a Cartesian
product, whereby every row in one table is joined
with every row in the other table. (see Figure
3-48)
--Figure 3-48, p.161 column s_id format
9999 column s_last format A9 column s_first
format A7 column f_id format 9999 column f_last
format A10 SELECT s_id, s_last, s_first,
student.f_id, f_last FROM student, faculty
68
Is the solution right?
SQLgt SELECT s_id, s_last, s_first, student.f_id,
f_last 2 FROM student, faculty S_ID S_LAST
S_FIRST F_ID F_LAST ----- --------- -------
----- --------- 100 Miller Sarah 1
Cox 101 Umato Brian 1 Cox 102 Black
Daniel 1 Cox 103 Mobley Amanda
2 Cox 104 Sanchez Ruben 4 Cox 105
Connoly Michael 3 Cox 100 Miller Sarah
1 Blanchard 101 Umato Brian 1
Blanchard 102 Black Daniel 1
Blanchard 103 Mobley Amanda 2
Blanchard 104 Sanchez Ruben 4
Blanchard S_ID S_LAST S_FIRST F_ID
F_LAST ----- --------- ------- ----- ---------
105 Connoly Michael 3 Blanchard 100
Miller Sarah 1 Williams 101 Umato
Brian 1 Williams 102 Black Daniel
1 Williams 103 Mobley Amanda 2
Williams 104 Sanchez Ruben 4 Williams
105 Connoly Michael 3 Williams
100 Miller Sarah 1 Sheng 101 Umato
Brian 1 Sheng 102 Black Daniel
1 Sheng 103 Mobley Amanda 2 Sheng
S_ID S_LAST S_FIRST F_ID F_LAST -----
--------- ------- ----- --------- 104 Sanchez
Ruben 4 Sheng 105 Connoly Michael 3
Sheng 100 Miller Sarah 1 Brown 101
Umato Brian 1 Brown 102 Black
Daniel 1 Brown 103 Mobley Amanda 2
Brown 104 Sanchez Ruben 4 Brown 105
Connoly Michael 3 Brown 30 rows selected.
S_ID S_LAST S_FIRST F_ID F_LAST -----
--------- ------- ----- ---------- 100 Miller
Sarah 1 Cox 101 Umato Brian 1
Cox 102 Black Daniel 1 Cox 103
Mobley Amanda 2 Blanchard 104 Sanchez
Ruben 4 Sheng 105 Connoly Michael 3
Williams 6 rows selected.
-- Chapter 3, Figure 3-43, SELECT s_id, s_last,
s_first, student.f_id, f_last FROM student,
faculty WHERE student.f_id faculty.f_id
69
Is the solution right?
SQLgt SELECT s_id, s_last, s_first, student.f_id,
f_last 2 FROM student, faculty S_ID S_LAST
S_FIRST F_ID F_LAST ------ --------- -------
----- -------- JO100 Jones Tammy 1
Marx PE100 Perez Jorge 1 Marx MA100
Marsh John 1 Marx SM100 Smith
Mike 2 Marx JO101 Johnson Lisa 4
Marx NG100 Nguyen Ni 3 Marx JO100
Jones Tammy 1 Zhulin PE100 Perez
Jorge 1 Zhulin MA100 Marsh John
1 Zhulin SM100 Smith Mike 2
Zhulin JO101 Johnson Lisa 4
Zhulin NG100 Nguyen Ni 3
Zhulin JO100 Jones Tammy 1
Langley PE100 Perez Jorge 1
Langley MA100 Marsh John 1
Langley SM100 Smith Mike 2
Langley JO101 Johnson Lisa 4
Langley NG100 Nguyen Ni 3 Langley
JO100 Jones Tammy 1 Brown PE100 Perez
Jorge 1 Brown MA100 Marsh John
1 Brown SM100 Smith Mike 2
Brown S_ID S_LAST S_FIRST F_ID
F_LAST ------ --------- ------- -----
-------- JO101 Johnson Lisa 4
Brown NG100 Nguyen Ni 3 Brown JO100
Jones Tammy 1 Sealy PE100 Perez
Jorge 1 Sealy MA100 Marsh John
1 Sealy SM100 Smith Mike 2
Sealy JO101 Johnson Lisa 4 Sealy NG100
Nguyen Ni 3 Sealy 30 rows selected.
S_ID S_LAST S_FIRST F_ID F_LAST ------
--------- ------- ----- -------- JO100 Jones
Tammy 1 Marx PE100 Perez Jorge 1
Marx MA100 Marsh John 1 Marx SM100
Smith Mike 2 Zhulin JO101 Johnson
Lisa 4 Brown NG100 Nguyen Ni
3 Langley 6 rows selected.
-- Chapter 3, Figure 3-43, SELECT s_id, s_last,
s_first, student.f_id, f_last FROM student,
faculty WHERE student.f_id faculty.f_id
70
Outer Join

• Returns all rows in one table and matching rows
  in joined table
• Inner table all rows are returned
• Outer table matching rows are returned
• Outer table marked with a in join condition
• inner_table.join_field outer_table.join_field()
  
• Null values are inserted for fields in outer
  table that are not found

71
-- chapter 3C, Figure 3-50 p168 -- OUTER JOIN
(slightly different from the text) SELECT
c_sec_id, location.loc_id FROM course_section,
location WHERE course_section.loc_id()
location.loc_id
-- chapter 3C, Figure 3-49 p167 SELECT c_sec_id,
location.loc_id FROM course_section,
location WHERE course_section.loc_id
location.loc_id
C_SEC_ID LOC_ID ---------- ----------
1 1 2 7 3
2 4 6 5
6 6 5 7 5
8 3 9 5
10 5 11 1 12
6 13 5
8 12 10
4 9
13 11 20 rows
selected.
C_SEC_ID LOC_ID ---------- ----------
1 1 2 7 3
2 4 6 5
6 6 5 7 5
8 3 9 5
10 5 11 1 12
6 13 5 13 rows selected.
Then, Figures 3-49 3-50
72
Creating Nested Queries

• Used to select results based on the result of a
  query
• Consists of a main query and one or more
  subqueries.
• Main query first query that appears in the
  SELECT command
• Subquery retrieves values that the main querys
  search condition must match

that return one value
73
Query Subquery Returns Single Value List all
students who have the same S_CLASS value as
student Jorge Perez.
Figure 3-54 p.172
SELECT s_last, s_first FROM student WHERE s_class

SELECT s_class FROM student
WHERE s_last 'Perez' AND s_first
'Jorge'
74
Nested Query WhereSubquery Returns Multiple
Values

• Syntax
• SELECT column1, column2,
• FROM table1, table2,
• WHERE join conditions
• AND search_column1 IN

• (SELECT column1
• FROM table1, table2,
• WHERE search and
• join conditions)

Subquery that returns multiple values
Practice Figures 3-55 3-56
75
Nested Subquery Example
Query List the names of students who have taken
courses with Amanda Mobley in the CR building.
S_LAST S_FIRST -----------
------------------- --------- Johnson
Lisa Jones
Tammy Marsh John Perez
Jorge
76
Using Set Operators to Combine Query Results

• Performs set operations on outputs of two
  unrelated queries
• They all require that both queries
• have the same number of display fields in the
  SELECT statement, and that
• each field in the first query has the same data
  type as the corresponding column in the second
  query.
• ltquery 1gt ltSET Operatorgt ltquery 2gt

77
Oracle 10g/SQL Set Operators (p. 170)

• Use to select data from multiple tables not
  connected with foreign key relationships (Table
  3-12)

• UNION (see Figure 3-58, 3-59)
• returns all rows from both queries, but ONLY
  displays duplicate rows once
• UNION ALL (see Figure 3-60)
• returns all (duplicate) rows from both queries,
  and displays ALL duplicate rows
• INTERSECT
• returns all matching rows that are returned by
  both queries
• MINUS
• returns all rows returned by the first query
  minus the matching rows returned by the second
  query

78
SQLgt SELECT s_last, s_first, s_phone, f_last,
f_first, f_phone FROM student,
faculty S_LAST S_FIRST S_PHONE F_LAST
F_FIRST F_PHONE ------- ------- ----------
------- ------- ---------- Jones Tammy
7155559876 Marx Teresa 4075921695 Perez
Jorge 7155552345 Marx Teresa
4075921695 Marsh John 7155553907 Marx
Teresa 4075921695 Smith Mike 7155556902
Marx Teresa 4075921695 Johnson Lisa
7155558899 Marx Teresa 4075921695 Nguyen Ni
7155554944 Marx Teresa 4075921695 Jones
Tammy 7155559876 Zhulin Mark
4073875682 Perez Jorge 7155552345 Zhulin
Mark 4073875682 Marsh John 7155553907
Zhulin Mark 4073875682 Smith Mike
7155556902 Zhulin Mark 4073875682 Johnson
Lisa 7155558899 Zhulin Mark 4073875682
Smith Mike 7155556902 Brown Jonnel
4078101155 S_LAST S_FIRST S_PHONE F_LAST
F_FIRST F_PHONE ------- ------- ----------
------- ------- ---------- Johnson Lisa
7155558899 Brown Jonnel 4078101155 Nguyen Ni
7155554944 Brown Jonnel 4078101155 Jones
Tammy 7155559876 Sealy James
4079817153 Perez Jorge 7155552345 Sealy
James 4079817153 Marsh John 7155553907
Sealy James 4079817153 Smith Mike
7155556902 Sealy James 4079817153 Johnson
Lisa 7155558899 Sealy James
4079817153 Nguyen Ni 7155554944 Sealy
James 4079817153 30 rows selected.
SQLgt --Figure 3-58, p.177 SQLgt SELECT s_last,
s_first, s_phone FROM student UNION
SELECT f_last, f_first, f_phone FROM
faculty S_LAST S_FIRST S_PHONE -------
------- ---------- Brown Jonnel
4078101155 Johnson Lisa 7155558899 Jones
Tammy 7155559876 Langley Colin
4075928719 Marsh John 7155553907 Marx
Teresa 4075921695 Nguyen Ni
7155554944 Perez Jorge 7155552345 Sealy
James 4079817153 Smith Mike
7155556902 Zhulin Mark 4073875682 11 rows
selected.
Fig. 3-59, 3-60 (p. 178)
79
Query A list of faculty members whose offices
are in the BUS building. (Extra example)

• FACULTY
• ?f_first (d)
• f_last (d)
• loc_id (j)

• LOCATION
• ?loc_id (j)
• bldg_code (s)

SELECT f_first, f_last FROM faculty,
location WHERE faculty.loc_id
location.loc_id AND bldg_code 'BUS'
80
Query A list of faculty members who have taught
a course in the BUS building.

• FACULTY
• ?f_first (d)
• f_last (d)
• f_id (j)

• COURSE_
• SECTION
• ?f_id (j)
• loc_id (j)

• LOCATION
• ?loc_id (j)
• bldg_code (s)

SELECT DISTINCT f_first, f_last FROM faculty,
location, course_section WHERE faculty.f_id
course_section.f_id AND location.loc_id
course_section.loc_id AND bldg_code 'BUS'
81
Query A list of faculty members whose offices
are in the BUS building and who have taught a
course in the BUS building. (extra example)
SELECT f_first, f_last FROM faculty,
location WHERE faculty.loc_id
location.loc_id AND bldg_code 'BUS'
Office in BUS
_______________
SELECT f_first, f_last FROM faculty, location,
course_section WHERE faculty.f_id
course_section.f_id AND location.loc_id
course_section.loc_id AND bldg_code 'BUS'
Taught courses in BUS
82
And NEXT ...
Query A list of faculty members who have taught
a course in the BUS building, but whose office
are NOT located in the BUS (Fig.3-61 p.173)
SELECT f_first, f_last FROM faculty,
location WHERE faculty.loc_id
location.loc_id AND bldg_code 'BUS'
Taught courses in BUS
_____________
SELECT f_first, f_last FROM faculty, location,
course_section WHERE faculty.f_id
course_section.f_id AND location.loc_id
course_section.loc_id AND bldg_code 'BUS'
Office in BUS
83
Database Views
A database view is a logical (virtual) table
based on a query.
Table-1
Table-N
Table-2
It does not store data, but presents it in a
format different from the one in which it is
stored in the underlying tables.
With the database view, you can view database
records, but you cant insert new records or
modify or delete exiting records.
database
Single view table
Database ____
Query
Output Report, Graphs
84
Database View (p.175)

• CREATE VIEW ltview_namegt AS
• ltview query specificationgt
• e.g.,
• --Figure 3-63, p.176
• SELECT view_name FROM user_views
• CREATE OR REPLACE VIEW faculty_view AS
• SELECT f_id, f_last, f_first, f_mi, loc_id,
  f_phone, f_rank
• FROM faculty
• SELECT view_name FROM user_views
• SELECT FROM faculty_view

What are gains?

• DELETING VIEWS
• DROP VIEW viewname
• DROP VIEW faculty_view

--EXTRA for VIEW p.176 SELECT f_last, f_first,
bldg_code, room FROM faculty_view, location WHERE
faculty_view.loc_id location.loc_id
85
Creating and Using Database Views

• Similar to storing the result of a query in the
  database
• Based on a source query that
• can specify a subset of a single tables fields
  or records
• can join multiple tables
• Can be used to enforce security (user has access
  to view but not underlying table)

86
Creating and Using Database Views

• Views can be updateable if
• SELECT clause contains only fieldnames, no
  functions or calculations
• cannot contain the ORDER BY, DISTINCT, or GROUP
  BY clauses, group functions, or set operators
• search condition cannot contain a nested query
• Views are used like tables for selecting,
  inserting, updating and deleting data (only
  updatable views can be modified)

87
Database View (cont.)

• SELECT view_name FROM user_views
• SELECT view_name FROM ALL_VIEWS
• WHERE ownerSYSTEM
• DROP VIEW ltview_namegt
• CREATE OR REPLACE VIEW ltview_namegt AS
• ltview query specificationgt

88
Summary

• Use INSERT commands to add data
• NUMBER, DATE and INTERVAL data types can be
  converted to and from character strings using
  format models
• Database changes are made within a transaction
  that can be committed or rolled back
• Use search conditions to specify records to
  update, delete or select
• Arithmetic, logical, grouping, and built-in
  Oracle functions can be used to specify search
  conditions and manipulate data
• Query output can be formatted by modifying SELECT
  clause

89
Summary

• Results from more than one table related through
  foreign key relationships can be combined in a
  join
• Results from more than one unrelated table can be
  combined using set operators
• Queries can be saved by creating a view
• Views can be used like tables to select, insert,
  update and delete data

90
Exercise Time

• In class exercises 5 (View) p. 188
• HW (use appropriate column statements)
• 1,2, 4,6,9,10 p. 188 (1 and 2 must draw
  required Query Design Diagramusing WORD, see my
  ppt). The output should be organized (formatted)
  in a good way (HOW?).
• Scripting file names 3CCase_Lname_Fname.sql
• First, spool it with the output (use SIZE of 10
  to save papers) (Oracle3C_Spool_Lname_Fname.LST).
  Then, use of the MS/WORD to include/DRAW
  required figures (1 and 2 only) for queries with
  multiple tables. You may use font of Courier
  new and format commands to align the output.
  The file name (see my sample files on the
  Blackboard Oracle3C_Complete_DEMO_chen_Jason.doc)
  
• Oracle3C_Complete_Lname_Fname.doc

Email ONLY the .doc file to me with attachment
to chen_at_jepson.gonzaga.edu with subject title of
bmis441_Oracle3C or mbus673_Oracle3C