Using SQL Queries to Insert, Update, Delete, and View Data

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

• Chapter 3

2
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

3
Using Scripts to Create Database Tables

• One or more SQL commands saved in a text file
• Usually have .sql extension
• To run from SQLPlus
• Start full file path (c\temp\myfile.sql)
• _at_full file path (_at_c\temp\myfile.sql)
• Extension can be omitted if it is .sql

4
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)

5
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

6
Numerical Format Models
7
Date Format Models
8
Date Format Models
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')
• Use functions to convert character strings to
  intervals
• TO_YMINTERVAL('4-9') inserts a positive interval
  of 4 years, 9 months
• TO_DSINTERVAL('0 011500') inserts a positive
  interval of 4 days, 1 hour, 15 minutes, 0 seconds

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

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

12
Commit and Roll Back in SQLPlus

• 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

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
Comparison Operators
16
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

17
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

18
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

19
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

20
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_sequenc
  e.NEXTVAL)

21
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

22
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

23
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

24
Sorting Query Output

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

25
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

26
Oracle9i SQL Group Functions

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

27
Group By

• Use to group output by the field with duplicate
  values and apply group functions to the grouped
  data

28
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) gt 100

29
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...
• To change SQLPlus line and page size settings
• Select Options/Environment on menu bar
• Modify linesize and pagesize to desired values

30
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

31
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

32
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

33
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

34
Joining Via Linking Table
35
Using a Query Design Diagram

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

36
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

37
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

38
Self-Join Example
39
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

40
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

41
Nested Subquery Example
42
Using Set Operators To Combine Query Results

• Use to select data from multiple tables not
  connected with foreign key relationships

43
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

44
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)

45
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)

46
Creating and Deleting Views

• CREATE OR REPLACE VIEW view_name AS
  source_query
• DROP VIEW viewname

47
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

48
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