ITBIS373 Database Development - PowerPoint PPT Presentation

1 / 73
About This Presentation
Title:

ITBIS373 Database Development

Description:

ITBIS373 Database Development Lecture3c - Chapter 3: Using SQL Queries to Insert, Update, Delete, and View Data Guide to Oracle 10g Lesson C Objectives After ... – PowerPoint PPT presentation

Number of Views:67
Avg rating:3.0/5.0
Slides: 74
Provided by: edub71
Category:

less

Transcript and Presenter's Notes

Title: ITBIS373 Database Development


1
ITBIS373 Database Development
Lecture3c - Chapter 3 Using SQL Queries to
Insert, Update, Delete, and View Data
Guide to Oracle 10g
2
Lesson C Objectives
  • After completing this lesson, you should be able
    to
  • Create SQL queries that join multiple tables
  • Create nested SQL queries
  • Combine query results using set operators
  • Create and use database views

3
Joining Multiple Tables
  • Join
  • Combine data from multiple database tables using
    foreign key references
  • Syntax
  • SELECT column1, column2,
  • FROM table1, table2
  • WHERE table1.joincolumn table2.joincolumn
  • AND search_condition(s)

4
Joining Multiple Tables (continued)
  • Must qualify column name in SELECT clause
  • Specify name of table that contains column
    followed by period then column name
  • Join condition
  • Specifies table names to be joined and column
    names on which to join tables
  • SQL supports multiple types of join queries

5
Inner Joins
  • Simplest type of join
  • VALUES in one table equal to values in other
    table
  • Also called
  • Equality join
  • Equijoin
  • Natural join
  • Query design diagram

6
(No Transcript)
7
Fig 3-43 to retrieve the student Id, student last
and first names, advisor ID, and advisor last
name.
8
  • In SQL queries, you can join any number of tables
    in SELECT command. When join tables, the name of
    each table in the query must appear in the FROM
    clause. This includes tables whose columns are
    display columns, which are columns that appear in
    the SELECT clause, and whose columns are search
    columns, which appear in search condition. The
    primary key and foreign key columns on which you
    join the tables are called join columns.

9
  • When you join multiple tables, sometimes you must
    join the tables using an intermediary table whose
    columns are not display or search columns, but
    whose columns are join columns that serve to join
    the two tables.
  • For example, suppose you want to create a query
    that lists the last names of all faculty members
    who teach during the Summer 2007 term.

10
Visual Representation of the Northwoods
University Database
11
(No Transcript)
12
(No Transcript)
13
(No Transcript)
14
A linking table does not contribute any as
display columns or search condition columns, but
contains join columns that link the other tables
through shared foreign key values.
15
  • Some times queries that join multiple tables can
    become complex. For example, suppose that you
    want to create a query to display the COURSE_NO
    and GRADE values for each of student Tammy
    Jones courses. This query requires you to join
    four table
  • STUDENT (to search for S_FIRST and S_LAST),
  • ENROLLMENT (to display GRADE),
  • COURSE to display COURSE_NO), and
  • COURSE_SECTION (to join ENROLLMENT to COURSE
    using the C_SEC_ID join column).

16
(No Transcript)
17
Deriving a SQL Query From a Query Design Diagram
18
(No Transcript)
19
  • If you accidentally omit a join condition in a
    multiple-table query, the output retrieves more
    rows than you expect. When you omit a join
    condition, the query creates a Cartesian product,
    whereby every row in one table is joined with
    every row in the other table. For example ,
    suppose you repeat the query to show each student
    row, along with each students advisor (see Fig
    3-43), but you omit the join condition. Every row
    in the STUDENT table (six rows) is joined with
    every row in the FACULTY table (five rows). The
    result is 6 times 5 rows, or 30 rows.

20
(No Transcript)
21
Outer Join
  • An inner join returns rows only if values exist
    in all tables that are joined. If no values exist
    for a row in one of the joined tables, the inner
    join does not retrieve the row. For example
    suppose you want to retrieve the different
    locations of the courses included in the
    COURSE_SECTION table.
  • This query requires joining rows in the LOCATION
    and COURSE_SECTION tables.
  • Not every location in the LOCATION table has an
    associated COURSE_SECTION row, so the query
    retrieves rows only for locations that have
    associated COURSE_SECTION rows.

22
(No Transcript)
23
  • SQLgt SELECT c_sec_id, location.loc_id
  • 2 From course_section, location
  • 3 Where course_section.loc.id
    location.loc_id
  • C_SEC_ID LOC_ID
  • ------------- -----------
  • 1 1
  • 11 1
  • 3 2
  • 8 3
  • 6 5
  • 7 5
  • 10 5
  • 13 5
  • 9 5
  • 4 6
  • 12 6
  • 5 6
  • 2 7

24
  • An outer join returns all rows from one table ,
    which is called the inner table. An outer join
    also retrieves matching rows from a second table,
    which is called the outer table.
  • The query designer specifies which table is the
    inner table and which table is the outer table.
  • In this case, because you want to retrieve all of
    the rows in the LOCATION table, you specify
    LOCATION as the inner table.

25
Outer Joins
  • Returns all rows from one table
  • Called inner table
  • And matching rows from second table
  • Called outer table
  • Syntax
  • inner_table.join_column outer_table.join_colum
    n()
  • The outer join operator () signals the DBMS to
    insert a NULL value for the columns in the outer
    table that do not have matching rows in the inner
    table.

26
(No Transcript)
27
Self-Joins
  • Sometimes a relational database table contains a
    foreign key that references a column in the same
    table. For example, at Northwood's University
    each assistant and associate professor is
    assigned to full professor who serves as the
    junior professors supervisor.

28
Self-Joins
  • To create a query that lists the names of each
    junior faculty member and the names of their
    supervisor, you must join the FACULTY table to
    itself.
  • When you create a query that joins a table to it
    self, you create a self-join.
  • To create a self-join, you must create a table
    alias and structure the query as if you are
    joining the table to a copy of itself.
  • The syntax to create a table alias in the FORM
    clause is
  • FROM table1 alias1,
  • When you create a table alias, you must the use
    the table alias, rather than the table name, to
    qualify column names in the SELECT clause and in
    join condition.
  • T o make the process easier to understand, you
    create two table aliases _FAC for faculty version
    of the table and SUPER for the supervisor version
    of the same table, as shown in Fig 3-51.

29
(No Transcript)
30
(No Transcript)
31
Self Joins
PARENT_PROJECT
SUB_PROJECT
PROJECT
32
Self Join Example
33
Creating Nested Queries
  • Nested query
  • Consists of main query and one or more subqueries
  • Main query
  • First query that appears in SELECT command
  • Subquery
  • Retrieves values that main querys search
    condition must match

34
Creating Nested Queries with Subqueries that
Return a Single Value
35
The query in Fig 3-54 to retrieve the names of
all students who have the same S_CLASS value as
student Jorge Perez.
36
Creating Subqueries that Return Multiple Values.
  • To create a nested query in which the subquery
    retrieves multiple values. You must use IN
    comparison operator instead of the equal to
    comparison operator.
  • Ex To retrieve the names of all students who
    have enrolled in the same course sections as
    Jorge Perez. Note that the main querys search
    conditions uses the IN comparison operator,
    because the sub-query returns multiple values.

37
ENROLLEMENT S_ID C_SEC_ID GRADE
PE100 1 B PE100
5 A PE100 6
B PE100 9
B JO100 1 C JO100
6 C JO100
9 C MA100 1
B JO101 5
C JO101 9 C
38
(No Transcript)
39
Using Multiple Subqueries Within a Nested Query
  • Use AND and OR operators
  • To join search conditions associated with
    subqueries.
  • Ex Fig 3-53 is used to retrieve the names of all
    students who have the same S_CLASS value as Jorge
    Perez and who have also been enrolled in course
    section with him

40
(No Transcript)
41
Creating Nested Subqueries
  • Nested subquery
  • Subquery that contains second subquery that
    specifies its search expression

42
USING SET Operator to Combine Query Results
  • UNION set operator
  • Joins output of two unrelated queries into single
    output result
  • Syntax
  • query1 UNION query2
  • Both queries must have the
  • same number of display columns in their SELECT
    clauses
  • must have the same data type
  • For example, if the display columns retuned by
    query1 are a NUMBER data column and then a
    VARCHAR2 data column, then the display columns
    returned by query2 must also be a NUMBER data
    column followed by a VARCHAR2 data column.
  • UNION ALL operator
  • Same as UNION but includes duplicate rows

43
Ex Create a telephone directory of every student
and faculty member at Northwood's University
44
INTERSECT
  • Finds intersection in two queries
  • Requires that both queries have same number and
    data types of display columns in SELECT statement
  • Automatically suppresses duplicate rows.

45
(No Transcript)
46
INTERSECTSome queries require an output that
finds the intersection, or matching rows, in two
unrelated queries. For example, suppose you need
to find a list of faculty members whose offices
are in the BUS building and who have also taught
a course in the BUS building.
47
MINUS
  • To find difference between two unrelated query
    result list.
  • As with the UNION and INTERSECT operators, the
    MINUS operator requires that both queries have
    the same number of display columns in the SELECT
    statement, and that each column in the first
    query has the same data type as corresponding
    column in the second query.

48
(No Transcript)
49
(No Transcript)
50
Creating and Using Database Views
  • Source query
  • Used to create view
  • Specify subset of single tables columns or rows
    or join multiple tables
  • Updatable views
  • Can be used to update database

51
Database Views
  • Logical table based on a query
  • Does not physically exist in the database as
    table
  • Presents data in a different format from
    underlying tables
  • Uses
  • Security
  • Simplifying complex queries

52
Database Views
  • Creating a view
  • CREATE VIEW view_name AS
  • SQL_command
  • Views can be queried just like tables
  • SELECT
  • FROM view_name

53
Simple Views
  • Based on SQL query that retrieves data from only
    one table
  • View can support all table DML operations
  • INSERT
  • UPDATE
  • DELETE
  • Can also execute update action queries and delete
    action queries using view
  • Just as with database table

54
Complex Views
  • Based on query that retrieves data from multiple
    tables
  • Can only be used to support SELECT operations
  • No DML operations supported

55
Creating Views
  • If there is possibility that you have already
    created a view using a specific name, you can use
    the following command to create or replace the
    existing view according to this syntax.
  • CREATE OR REPLACE VIEW view_name
  • AS source_query

56
Ex This view contains all of the FACULTY columns
except F_PIN and F_IMAGE
57
Executing Action Queries Using Views
  • 1- To insert a row using the FACULTY_VIEW
  • INSERT INTO faculty_view VALUE
    (6,may,Lisa,I,11,7155552508,INST)
  • 2- Type SELECT FROM faculty_view to determine
    whether the new faculty member is included in the
    table.
  • Type DELETE FROM faculty_view WHERE f-last
    MAY to delete the new faculty member.

58
Retrieving Rows from Views.
  • You can query a view using a SELECT statement,
    just as with a database table, and use the view
    in complex queries that involve join operations
    and subqueries.
  • You create a query that joins FACULTY_VIEW with
    the LOCATION to list the names of each faculty
    member, along with the building code and room
    number of the faculty members office.

59
(No Transcript)
60
Removing Views
  • DROP VIEW command
  • Remove view from user schema
  • Syntax
  • DROP VIEW view_name
  • Ex DROP VIEW faculty_view

61
Selecting Records For Update
  • Syntax
  • SELECT column1, column2,
  • FROM table1, table2,
  • WHERE search and join conditions
  • FOR UPDATE OF column1, column2,
  • NOWAIT

62
  • The NOWAIT command causes the system to generate
    an error message immediately if another user has
    previously locked the selected records. If the
    NOWAIT command is omitted, the system forces the
    user to wait until the requested records are
    unlocked, and the user can do no further
    processing.

63
(No Transcript)
64
Dynamic SQL Queries
  • Queries that allow users to specify search
    conditions at runtime
  • Approaches
  • Substitution Values
  • Runtime Variables

65
Using Substitution Values
  • Created when search expression is prefaced with
    an ampersand ()
  • System then prompts user for value

66
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

67
Using Runtime Variables
  • Example

68
Indexes
  • Index Separate table is maintained that shows
    index keys and physical locations of
    corresponding records
  • In Oracle, ROWID is translated to physical
    location of row on disk
  • Improves response time of searches and joins

SLName ROWID
Brown 13387289
Jones 13879872
Smith 58925789
Helgeson 29875018
69
Using Indexes
  • Create table index AFTER table is populated with
    data
  • Indexes make INSERT, UPDATE, and DELETE
    operations slower because index must also be
    maintained

70
Indexing Strategies
  • A table can have indexes on multiple fields
  • Create indexes based on fields used for search or
    join operations
  • Typically, indexes only speed retrievals when
    lt15 of the table records are involved
  • Each additional index adds processing overhead
    for INSERT, UPDATE, and DELETE operations
  • In Oracle, primary keys are automatically indexed

71
Creating Indexes
  • Syntax
  • CREATE INDEX index_name
  • ON tablename(index_field)

72
Summary
  • INSERT action query
  • SQL search condition
  • Match one or more database rows
  • UPDATE action query
  • DELETE command
  • SELECT query
  • DISTINCT qualifier
  • Single row and group functions

73
Summary (continued)
  • Can change appearance of SQLPlus environment
  • Join multiple tables in SELECT query
  • Inner join
  • Outer join
  • Nested queries
  • Set operators
  • Views
Write a Comment
User Comments (0)
About PowerShow.com