Using SQL Queries to Insert Update Delete and

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: q Create SQL queries that join multiple tables q Create nested SQL queries q Combine query results using SET operators q Create and use database views 2

Joining Multiple Tables q a Join – Combines data from multiple tables using foreign key references q Syntax SELECT column 1, column 2, … FROM table 1, table 2 WHERE table 1. joincolumn = table 2. 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) q 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 q 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 q Simplest type of join q Also called: Equality join, Equijoin, Natural join q VALUES in one table equal to values in other table q 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 q Display columns: appear in SELECT clause q Search columns: appear in search condition q Join columns: primary key and foreign key column on which you join the tables. q 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_id 6 AND term_desc = 'Summer 2007';

Deriving a SQL Query From a Query Design Diagram q 4 tables, 3 links q All 4 tables must be named in the FROM clause q Query must have 3 join conditions because there are 3 links q Always 1 fewer join condition than number of tables that query joins. q 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_no AND s_last = 'Jones' AND s_first = 'Tammy'; Search conditions 7

Outer Joins q Inner joins return row only if values exist in all joined tables q Outer joins return qall rows from one table (called inner table) and qonly matching rows from second table (outer table) q 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 q Query that joins table to itself q Must create table alias – Alternate name assigned to table in query’s FROM clause – Syntax: FROM table 1 alias 1, table 1 alias 2 … 9

Creating Nested Queries q 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 q 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 q Use AND and OR operators – To join search conditions associated with subqueries 13

Using SET operators to combine Query Results q 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 q 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 q 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 q Source query – Used to create view – Specify subset of single table’s columns or rows or join multiple tables q Updatable views – Can be used to update database q Syntax CREATE VIEW view_name AS source_query; – Or CREATE OR REPLACE VIEW view_name AS source_query; 17

Removing Views q DROP VIEW command – Remove view from user schema – Syntax • DROP VIEW view_name; 18