Title: ITBIS373 Database Development
1ITBIS373 Database Development
Lecture3c - Chapter 3 Using SQL Queries to
Insert, Update, Delete, and View Data
Guide to Oracle 10g
2Lesson 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
3Joining 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)
4Joining 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
5Inner 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)
7Fig 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.
10Visual Representation of the Northwoods
University Database
11(No Transcript)
12(No Transcript)
13(No Transcript)
14A 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)
17Deriving 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)
21Outer 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.
25Outer 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)
27Self-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.
28Self-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)
31Self Joins
PARENT_PROJECT
SUB_PROJECT
PROJECT
32Self Join Example
33Creating 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
34Creating Nested Queries with Subqueries that
Return a Single Value
35The query in Fig 3-54 to retrieve the names of
all students who have the same S_CLASS value as
student Jorge Perez.
36Creating 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.
37ENROLLEMENT 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)
39Using 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)
41Creating Nested Subqueries
- Nested subquery
- Subquery that contains second subquery that
specifies its search expression
42USING 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
43Ex Create a telephone directory of every student
and faculty member at Northwood's University
44INTERSECT
- 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)
46INTERSECTSome 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.
47MINUS
- 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)
50Creating 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
51Database 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
52Database Views
- Creating a view
- CREATE VIEW view_name AS
- SQL_command
- Views can be queried just like tables
- SELECT
- FROM view_name
53Simple 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
54Complex Views
- Based on query that retrieves data from multiple
tables - Can only be used to support SELECT operations
- No DML operations supported
55Creating 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
56Ex This view contains all of the FACULTY columns
except F_PIN and F_IMAGE
57Executing 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.
58Retrieving 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)
60Removing Views
- DROP VIEW command
- Remove view from user schema
- Syntax
- DROP VIEW view_name
- Ex DROP VIEW faculty_view
61Selecting 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)
64Dynamic SQL Queries
- Queries that allow users to specify search
conditions at runtime - Approaches
- Substitution Values
- Runtime Variables
65Using Substitution Values
- Created when search expression is prefaced with
an ampersand () - System then prompts user for value
66Using 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
67Using Runtime Variables
68Indexes
- 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
69Using Indexes
- Create table index AFTER table is populated with
data - Indexes make INSERT, UPDATE, and DELETE
operations slower because index must also be
maintained
70Indexing 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
71Creating Indexes
- Syntax
- CREATE INDEX index_name
- ON tablename(index_field)
72Summary
- 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
73Summary (continued)
- Can change appearance of SQLPlus environment
- Join multiple tables in SELECT query
- Inner join
- Outer join
- Nested queries
- Set operators
- Views