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

AND

search_condition(s)

; =

table2.joincolumn

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

AND term_desc = 'Summer 2007'; 6

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

(

+

)

(+) operator signals Oracle to insert NULL for columns from the outer table with no matching rows in the inner table.

8

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