Transcript USING SQL QUERIES TO INSERT, UPDATE, DELETE, AND VIEW …

Using SQL Queries to Insert,
Update, Delete, and View Data
Chapter 3
A Guide to Oracle9i
1
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
A Guide to Oracle9i
2
Using Scripts to Create Database
Tables
• One or more SQL commands saved in a text
file
• Usually have .sql extension
• To run from SQL*Plus:
– Start full file path (c:\temp\myfile.sql)
– @full file path (@c:\temp\myfile.sql)
– Extension can be omitted if it is .sql
A Guide to Oracle9i
3
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);
A Guide to Oracle9i
4
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
A Guide to Oracle9i
5
Numerical Format Models
A Guide to Oracle9i
6
Date Format Models
A Guide to Oracle9i
7
Date Format Models
A Guide to Oracle9i
8
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('10:00 AM', 'HH:MI AM')
• Use functions to convert character strings to intervals
– TO_YMINTERVAL('4-9') inserts a positive interval of 4 years, 9
months
– TO_DSINTERVAL('0 01:15:00') inserts a positive interval of 4 days,
1 hour, 15 minutes, 0 seconds
A Guide to Oracle9i
9
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
A Guide to Oracle9i
10
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
A Guide to Oracle9i
11
Commit and Roll Back in SQL*Plus
• Transactions begin automatically with first command
• Type COMMIT to commit changes
• Type ROLLBACK to roll back changes
A Guide to Oracle9i
12
Savepoints
• A bookmark that designates the beginning of an individual
section of a transaction
• Changes are rolled back to savepoint
A Guide to Oracle9i
13
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
A Guide to Oracle9i
14
Comparison Operators
A Guide to Oracle9i
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
A Guide to Oracle9i
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
A Guide to Oracle9i
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;
A Guide to Oracle9i
18
Updating and Deleting
Existing Table Records
• TRUNCATE
– Removes all of the table data without saving any
rollback information
– Must disable foreign key constraints before
truncating table
– TRUNCATE TABLE tablename;
A Guide to Oracle9i
19
Sequences
• Sequential lists of numbers to create unique surrogate
key values
• To use a sequence:
– SELECT sequence_name.NEXTVAL FROM DUAL;
– INSERT INTO location LOC_ID)
VALUES(loc_id_sequence.NEXTVAL);
A Guide to Oracle9i
20
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
A Guide to Oracle9i
21
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
A Guide to Oracle9i
22
Retrieving Data from
a 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
A Guide to Oracle9i
23
Sorting Query Output
• Use ORDER BY sort_key_field(s)
• Default order is ascending, use DESC to sort descending
A Guide to Oracle9i
24
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
A Guide to Oracle9i
25
Oracle9i SQL Group Functions
• Group functions: perform an operation on a group of queried
rows and returns a single result
A Guide to Oracle9i
26
Group By
• Use to group output by the field with duplicate values and apply
group functions to the grouped data
A Guide to Oracle9i
27
Having
• Use to place a search condition on results of group function
calculations
• Like “WHERE” for group functions
• HAVING group_function comparison_operator value
• HAVING SUM(capacity) >= 100
A Guide to Oracle9i
28
Formatting Output in SQL*Plus
• 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...
• To change SQL*Plus line and page size settings
– Select Options/Environment on menu bar
– Modify linesize and pagesize to desired values
A Guide to Oracle9i
29
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;
A Guide to Oracle9i
30
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
A Guide to Oracle9i
31
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
A Guide to Oracle9i
32
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
• Returns results only if records exist in both tables
A Guide to Oracle9i
33
Joining Via Linking Table
A Guide to Oracle9i
34
Using a Query Design Diagram
• Helpful for creating complicated queries
• Can use a formula to derive actual query from diagram
A Guide to Oracle9i
35
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
A Guide to Oracle9i
36
Self-Join
• A query that joins a table to itself
• Used when a table has a foreign key relationship to itself
(usually parent-child relationship)
• Must create a table alias and structure the query as if you are
joining the table to a copy of itself
• FROM table1 alias1, ...
• Use alias, not table name for select and where clauses
A Guide to Oracle9i
37
Self-Join Example
A Guide to Oracle9i
38
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 query’s search condition
must match
A Guide to Oracle9i
39
Creating Nested Queries
• Nested queries can return single or multiple values
– To match single values use = operator
– To match multiple values use IN operator
• Subqueries can be nested to more than one level (nested
subqueries)
• Nested subqueries are slower than joins and should be used
sparingly
A Guide to Oracle9i
40
Nested Subquery Example
A Guide to Oracle9i
41
Using Set Operators
To Combine Query Results
• Use to select data from multiple tables not connected with
foreign key relationships
A Guide to Oracle9i
42
Set Operators
•
query1 OPERATOR query2; (where operator is UNION, UNION ALL,
INTERSECT, or MINUS)
•
Both queries must have same number of select fields and same data
types in same order
•
UNION suppresses duplicate values
•
UNION ALL includes duplicate values
•
INTERSECT takes only matching fields
•
MINUS takes only fields in query1 not query2
A Guide to Oracle9i
43
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 table’s fields or records
– can join multiple tables
• Can be used to enforce security (user has access to view but
not underlying table)
A Guide to Oracle9i
44
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)
A Guide to Oracle9i
45
Creating and Deleting Views
• CREATE OR REPLACE VIEW view_name AS source_query;
• DROP VIEW viewname;
A Guide to Oracle9i
46
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
A Guide to Oracle9i
47
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
A Guide to Oracle9i
48