Using sql queries to insert update delete and view data
Download
1 / 48

Using SQL Queries to Insert, Update, Delete, and View Data - PowerPoint PPT Presentation


  • 72 Views
  • Uploaded on

Using SQL Queries to Insert, Update, Delete, and View Data. Chapter 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Using SQL Queries to Insert, Update, Delete, and View Data' - faraji


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Using sql queries to insert update delete and view data

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

Chapter 3

A Guide to Oracle9i


Lesson a objectives
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


Using scripts to create database tables
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


Inserting data into tables
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


Format models
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


Numerical format models
Numerical Format Models

A Guide to Oracle9i


Date format models
Date Format Models

A Guide to Oracle9i


Date format models1
Date Format Models

A Guide to Oracle9i


Inserting date and interval values
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


Inserting lobs
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


Creating transactions and committing new data
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


Commit and roll back in sql plus
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


Savepoints
Savepoints

  • A bookmark that designates the beginning of an individual section of a transaction

  • Changes are rolled back to savepoint

A Guide to Oracle9i


Creating search conditions in sql queries
Creating Search Conditions in SQL Queries

  • An expression that seeks to match specific table records

  • Used in SELECT, UPDATE and DELETE statements

  • WHERE fieldnamecomparison_operatorsearch_expression

  • WHERE S_ID = 1

A Guide to Oracle9i


Comparison operators
Comparison Operators

A Guide to Oracle9i


Defining search expressions
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


Creating complex search conditions
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


Updating and deleting existing table records
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


Updating and deleting existing table records1
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


Sequences
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


Database object privileges
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


Lesson b objectives
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


Retrieving data from a single database table
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


Sorting query output
Sorting Query Output

  • Use ORDER BY sort_key_field(s)

  • Default order is ascending, use DESC to sort descending

A Guide to Oracle9i


Using calculations in sql queries
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


Oracle9 i sql group functions
Oracle9i SQL Group Functions

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

A Guide to Oracle9i


Group by
Group By

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

A Guide to Oracle9i


Having
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


Formatting output in sql plus
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


Formatting numbers and dates
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


Lesson c objectives
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


Joining multiple tables
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


Inner join
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


Joining via linking table
Joining Via Linking Table

A Guide to Oracle9i


Using a query design diagram
Using a Query Design Diagram

  • Helpful for creating complicated queries

  • Can use a formula to derive actual query from diagram

A Guide to Oracle9i


Outer join
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


Self join
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


Self join example
Self-Join Example

A Guide to Oracle9i


Creating nested queries
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

    • Subqueryretrieves values that the main query’s search condition must match

A Guide to Oracle9i


Creating nested queries1
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


Nested subquery example
Nested Subquery Example

A Guide to Oracle9i


Using set operators to combine query results
Using Set Operators To Combine Query Results

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

A Guide to Oracle9i


Set operators
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


Creating and using database views
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


Creating and using database views1
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


Creating and deleting views
Creating and Deleting Views

  • CREATE OR REPLACE VIEW view_name AS source_query;

  • DROP VIEW viewname;

A Guide to Oracle9i


Summary
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


Summary1
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


ad