Transcript Joining Multiple Tables, Set Operators, Views

Using SQL Queries to Insert,
Update, Delete, and View Data:
Joining Multiple Tables
Monday 2/9/2015
© Abdou Illia
MIS 4200 - Spring 2015
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
2
Joining Multiple Tables
a Join
– Combines data from multiple tables using foreign key
references
Syntax
SELECT column1, column2, …
FROM table1, table2
WHERE table1.joincolumn = table2.joincolumn
AND search_condition(s);
SELECT s_id, s_last, f_last
FROM student, faculty
WHERE student.f_id = faculty.f_id
AND f_last IN (‘Marx’, ‘Zhulin’);
3
Joining Multiple Tables (continued)
Must qualify column name in SELECT clause
– Specify name of table that contains column followed
by period then column name
– Example: SELECT s_id, s_last, student.f_id
Join condition
– Specifies table names to be joined and column names
on which to join tables
– Example: WHERE student.f_id = faculty.f_id
4
Inner Joins
Simplest type of join
Also called: Equality join, Equijoin, Natural join
VALUES in one table equal to values in other table
Query design diagram helps get the query right
SELECT s_id, s_last, s_first, student.f_id, f_last
FROM student, faculty
WHERE student.f_id = faculty.f_id;
Could be replaced by:
FROM Student NATURAL JOIN faculty;
5
Display column, search column, join column
Display columns: appear in SELECT clause
Search columns: appear in search condition
Join columns: primary key and foreign key column
on which you join the tables.
Linkage table: contains join column to link other
tables through foreign key values.
SELECT f_last
FROM faculty, course_section, term
WHERE faculty.f_id = course_section.f_id
AND course_section.term_id = term.term_id
6
AND term_desc = 'Summer 2007';
Deriving a SQL Query From a Query
Design Diagram
 4 tables, 3 links
 All 4 tables must be named in the
FROM clause
 Query must have 3 join conditions
because there are 3 links
 Always 1 fewer join condition than
number of tables that query joins.
 If you omit one join condition, the
query creates a Cartesian product
(every row in one table is joined with
every row in other table) with more
row than expected.
SELECT course_name, grade
FROM student, enrollment, course_section, course
WHERE student.s_id = enrollment.s_id
AND enrollment.c_sec_id = course_section.c_sec_id
AND course_section.course_no = course.course_no
AND s_last = 'Jones'
AND s_first = 'Tammy';
Search conditions
7
Outer Joins
Inner joins return row only if values exist in all joined
tables
Outer joins return
all rows from one table (called inner table) and
only matching rows from second table (outer table)
Syntax: inner_table.join_col
= outer_table.join_col(+)
8
(+) operator signals Oracle to insert NULL for columns from the outer table with no matching rows in the inner table.
Self-join
Query that joins table to itself
Must create table alias
– Alternate name assigned to table in query’s FROM clause
– Syntax: FROM table1 alias1, table1 alias2 …
9
Creating Nested Queries
 Nested query
– Consists of a main query and one or more subqueries
– Main query
• First query that appears in SELECT command
– Subquery
• Retrieves values that main query’s search condition must match
 Subquery is evaluated first. Then, DBMS substitute
subquery’s output into main query.
10
Creating Nested Queries
Q: What would happen if a subquery generated more
values than the main query is expecting?
11
Creating subqueries that return multiple values
12
Using Multiple Subqueries Within a
Nested Query
 Use AND and OR operators
– To join search conditions associated with subqueries
13
Using SET operators to combine Query Results
 UNION
– Queries must have
same number of
display column in
their SELECT clause
– Corresponding display
columns must have
same data type
Note: S_LAST, S_FIRST, S_PHONE used as
display title even though there are faculty
members names displayed along with students.
14
Using SET operators to combine Query Results
 INTERSECT
– Queries must have
same number of
display column in
their SELECT clause
– Corresponding display
columns must have
same data type
– Suppresses duplicates
15
Using SET operators to combine Query Results
 MINUS
– Queries must have
same number of
display column in their
SELECT clause
– Corresponding display
columns must have
same data type
– Suppresses duplicates
– Finds difference
between two query
results
16
Creating and Using Database Views
 Source query
– Used to create view
– Specify subset of single table’s columns or rows or join
multiple tables
 Updatable views
– Can be used to update database
 Syntax
CREATE VIEW view_name
AS source_query;
– Or
CREATE OR REPLACE VIEW view_name
AS source_query;
17
Removing Views
DROP VIEW command
– Remove view from user schema
– Syntax
• DROP VIEW view_name;
18