Scribd Logo
Upload

Welcome to Scribd!

  • 52 views

    OCP Join Question

    Uploaded by

    FrancesHsieh
    This document discusses using outer joins to return records that don't have a direct match between tables. It provides an example of using a left outer join to return all cities from a customers table along with customer details, including cities that have multiple customers. It also provides an example of using a left outer join to return task details from a project tasks table along with the employee in charge of any dependent tasks.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    OCP Join Question

    Uploaded by

    FrancesHsieh
    52 views2 pages
    This document discusses using outer joins to return records that don't have a direct match between tables. It provides an example of using a left outer join to return all cities from a customers table along with customer details, including cities that have multiple customers. It also provides an example of using a left outer join to return task details from a project tasks table along with the employee in charge of any dependent tasks.

    Original Description:

    OCP Join Question

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document discusses using outer joins to return records that don't have a direct match between tables. It provides an example of using a left outer join to return all cities from a customers table along with customer details, including cities that have multiple customers. It also provides an example of using a left outer join to return task details from a project tasks table along with the employee in charge of any dependent tasks.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    52 views2 pages

    OCP Join Question

    Uploaded by

    FrancesHsieh
    This document discusses using outer joins to return records that don't have a direct match between tables. It provides an example of using a left outer join to return all cities from a customers table along with customer details, including cities that have multiple customers. It also provides an example of using a left outer join to return task details from a project tasks table along with the employee in charge of any dependent tasks.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 2

    Returning Records with No Direct Match Using OUTER Joins

    Question 1: Examine the data in the CUSTOMERS table:


    CUSTNO CUSTNAME CITY
    1 KING SEATTLE
    2 GREEN BOSTON
    3 KOCHAR SEATTLE
    4 SMITH NEW YORK

    You want to list all cities that have more than one customer along with the customer details.
    Evaluate the following query:
    SQL>SELECT c1.custname, c1.city
    FROM Customers c1 __________________ Customers c2
    ON (c1.city=c2.city AND c1.custname<>c2.custname);
    Which two JOIN options can be used in the blank in the above query to give the correct output?

    select * from CUSTOMERS t;

    SELECT c1.custname, c1.city


    FROM Customers c1 JOIN Customers c2
    ON (c1.city=c2.city AND c1.custname<>c2.custname);

    SELECT c1.custname, c1.city, c2.custname, c2.city


    FROM Customers c1 LEFT OUTER JOIN Customers c2
    ON (c1.city=c2.city AND c1.custname<>c2.custname);

    SELECT c1.custname, c1.city, c2.custname, c2.city


    FROM Customers c1 FULL OUTER JOIN Customers c2
    ON (c1.city=c2.city AND c1.custname<>c2.custname);

    SELECT c1.custname, c1.city, c2.custname, c2.city


    FROM Customers c1 RIGHT OUTER JOIN Customers c2
    ON (c1.city=c2.city AND c1.custname<>c2.custname);
    Question 2: View the Exhibit and examine the data in the PROJ_TASK_DETAILS table.
    CREATE OR REPLACE VIEW proj_task_details
    (task_id, based_on,task_in_charge) AS
    SELECT 'P01',NULL,'KING' FROM dual UNION ALL
    SELECT 'P02','P01','KOCHAR' FROM dual UNION ALL
    SELECT 'P03',NULL,'GREEN' FROM dual UNION ALL
    SELECT 'P04','P03','SCOTT' FROM dual;

    The PROJ_TASK_DETAILS table stores information about tasks involved in a project and the relation between them.
    The BASED_ON column indicates dependencies between tasks. Some tasks do not depend on the completion of any
    other tasks.

    You need to generate a report showing all task IDs, the corresponding task ID they are dependent on, and the name of
    the employee in charge of the task it depends on.

    Which query would give the required result?


    A. SELECT p.task_id, p.based_on, d.task_in_charge
    FROM proj_task_details p JOIN proj_task_details d
    ON (p.based_on = d.task_id);

    B. SELECT p.task_id, p.based_on, d.task_in_charge


    FROM proj_task_details p LEFT OUTER JOIN proj_task_details d ON (p.based_on = d.task_id);

    C. SELECT p.task_id, p.based_on, d.task_in_charge


    FROM proj_task_details p FULL OUTER JOIN proj_task_details d ON (p.based_on = d.task_id);

    D. SELECT p.task_id, p.based_on, d.task_in_charge


    FROM proj_task_details p JOIN proj_task_details d
    ON (p.task_id = d.task_id);

    You might also like