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

Using SQL Queries to Insert,
Update, Delete, and View Data
Chapter 3
Guide to Oracle10G
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
Guide to Oracle10G
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)
Ex: start c:\temp\myfile.sql
@full file path (@c:\temp\myfile.sql)
Ex: @c:\temp\myfile.sql
Extension can be omitted if it is .sql
Guide to Oracle10G
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);
Guide to Oracle10G
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
Guide to Oracle10G
5
Numerical Format Models
Guide to Oracle10G
6
Date Format Models
Guide to Oracle10G
7
Date Format Models
Guide to Oracle10G
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')
Guide to Oracle10G
9
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
Guide to Oracle10G
10
Commit and Roll Back in SQL*Plus
• Transactions begin automatically with first command
• Type COMMIT to commit changes
• Type ROLLBACK to roll back changes
Guide to Oracle10G
11
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
Guide to Oracle10G
12
Comparison Operators
Guide to Oracle10G
13
Defining Search Expressions
• Character strings
– Must be enclosed in single quotes
– Case sensitive
• Dates
– Use to_date function with date string and format model
Guide to Oracle10G
14
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
Guide to Oracle10G
15
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;
Guide to Oracle10G
16
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;
Guide to Oracle10G
17
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);
Guide to Oracle10G
18
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
Guide to Oracle10G
19
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
Guide to Oracle10G
20
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
Guide to Oracle10G
21
Sorting Query Output
• Use ORDER BY sort_key_field(s)
• Default order is ascending, use DESC to sort descending
Guide to Oracle10G
22
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
Guide to Oracle10G
23
Oracle9i SQL Group Functions
• Group functions: perform an operation on a group of queried
rows and returns a single result
Guide to Oracle10G
24
Group By
• Use to group output by the field with duplicate values and apply
group functions to the grouped data
Guide to Oracle10G
25
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
Guide to Oracle10G
26
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
Guide to Oracle10G
27
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;
Guide to Oracle10G
28
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
Guide to Oracle10G
29
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
Guide to Oracle10G
30
Process for Designing
Complex Inner Join Queries
1. Identify all of the tables involved in the query, and label:



Display fields
Join fields
Search fields
2. Write the query




List all display fields in the SELECT clause
List all table names in the FROM clause
List all join condition links in the WHERE clause
List all search fields in the WHERE clause
Guide to Oracle10G
31
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
Guide to Oracle10G
32
Using a Query Design Diagram
• Helpful for creating complicated queries
• Can use a formula to derive actual query from diagram
Guide to Oracle10G
33
Outer Joins
• 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
Guide to Oracle10G
34
Outer Joins
• Limitation of inner joins: some records may be omitted if
corresponding records don’t exist in one of the tables
• Example: retrieve records for all students, along with their
corresponding ENROLLMENT information
Guide to Oracle10G
35
Outer Joins

Student 105 (Michael Connoly) does not have any
ENROLLMENT records
Guide to Oracle10G
36
Outer Joins
•
No records retrieved for Michael:
Guide to Oracle10G
37
Outer Joins

To include records in first (inner) table, even when they do not
have matching records in second (outer) table, place outer join
marker (+) beside outer table name in join clause
Guide to Oracle10G
38
Outer Joins
Guide to Oracle10G
39
Using Set Operators
To Combine Query Results
• Use to select data from multiple tables not connected with
foreign key relationships
Guide to Oracle10G
40
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
Guide to Oracle10G
41
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)
Guide to Oracle10G
42
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)
Guide to Oracle10G
43
Creating and Deleting Views
• CREATE OR REPLACE VIEW view_name AS source_query;
• DROP VIEW viewname;
Guide to Oracle10G
44
Summary
• Use INSERT commands to add data
• NUMBER, DATE 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
Guide to Oracle10G
45
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
Guide to Oracle10G
46