Recap of Week 5

More Fun with Oracle! This week we focussed on joins. What they are, and how to use them. We also looked at Unions, Intersects and the like. Here is a breakdown:

  • Joins: used to bring to different tables together as a single piece of information. For example, assume that you have the following tables.

    Click   for the SQL code to create these tables first:

                          Owner                                           Pet
+-----------------+-----------------+-----+       +-------+---------------------------+---------+ 
| Lname           | Fname           | OID*|       | PNum* | PName        |  Birthdate | OID (FK)|
+-----------------+-----------------+-----+       +-------+---------------------------+---------+
| Miller          | Amos            |  1  |       | 1     | Wolfy        | 01-13-01   |    1    |
+-----------------+-----------------+-----+       +-------+---------------------------+---------+
| Yoder           | Levi            |  2  |       | 2     | Buttercup    | 08-12-96   |    1    |
+-----------------+-----------------+-----+       +-------+---------------------------+---------+
| Stutzman        | Muhammed        |  3  |       | 3     | Horse 1      | 02-13-02   |    2    |
+-----------------+-----------------+-----+       +-------+---------------------------+---------+
| Miller          | Andy            |  4  |       | 4     | ChickenLittle| 03-12-02   |    2    |
+-----------------+-----------------+-----+       +-------+---------------------------+---------+
                                                  | 5     | Llama Bean   | 04-21-97   |    3    |
                                                  +-------+---------------------------+---------+

      Now, looking at the data is awkward (imaging if we had 12 tables to look at!). Especially for an end user, they don't want to decode anything. They just want to see:

      Amos Miller, Wolfy,     01-13-01
Amos Miller, Buttercup, 08-12-96

      We can accomplish this through a join. Types of Joins:
    • Cross (Cartesian Product)-This is a mess, and not likely to be used. To accomplish this, you can simply state:
      SELECT * FROM owner,pet; and you will see every possible combination of owner-pet without any sense.
    • Inner (Natural or equi-join)- instead of everything lumped together, you match up using a common column. This case, it makes sense to use the OID (owner ID) as the common field.
      **Notice that Andy Miller does not show up!! because it he has no pet listed in the Pet table.
    • Outer Joins- How do we get to see Andy's name pop-up even without him having an animal? Glad you asked. For that, we will use an outer join. An Outer Join lists every row of one table and matching rows from another.
      • Left (Outer) *=   To query the pet and owner tables, and see Andy listed, we will use the syntax
        SELECT * FROM Owner, Pet WHERE Owner.oid *= pet.oid which happens to be the same as the following line:
        SELECT* FROM owner LEFT OUTER JOIN pet ON owner.oid = pet.oid
        This makes sure that all records to the left side (owner here) will be included even if they don't have a match in pet.
      • Right (Outer) =*   Same thing as above, but we can list the Pet table first.
        SELECT * FROM Pet, Owner WHERE pet.oid =* Owner.oid which is the same as
        SELECT * FROM pet LEFT OUTER JOIN owner ON pet.oid = owner.oid
      • Full (Outer)* * This would bring all records from left table and right table. But unlike the Cartesian Join, this will organize the records where they match. An example of that syntax would be:
        SELECT owner.oid, COUNT(pet.oid) FROM owner LEFT OUTER JOIN pet ON owner.oid = pet.oid GROUP BY owner.oid
      • Self Self Joins are a great way to keep your sanity. Imagine that you have an employee table. In that table you would have employeeID, Name, and Manager. The ManagerID is the same as that manager's employeeID. Example:
        Employees
        EmpID EmpName ManID
        9221 Ben Dover (null)
        9222 Ima Gnutt 9221
        9223 Carrot Top 9221
        9224 Mary Mary 9222
        9225 Billy Bob 9222
        Using table name aliases, It would look something like this:
        SELECT E1.EmpName AS Employee, E2.EmpName AS Manager FROM Employees AS E1 INNER JOIN Employees AS E2 ON E1.ManID = E2.EmpID
        Scary? We'll go over this in class if need-be.
  • Unions Used to glue 2 or more queries together. PetID=2 OR OwnerID=1
  • Intersect Use with 2 or more queries to bring back what is common in both. PetID=2 AND OwnerID=1
  • Minus Narrows down your initial query results. Like "show me all pets owned by Miller" now from that "take away anything born before 1996".

This is the homework, look at the code I emailed to you. See you next week.