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