Archive for March, 2007

Joining Tables

Thursday, March 29th, 2007

Joining tables brings together data from two or more tables in one query in order to get all the information you need. There are six different types of joins:

Element

Description

Cartesian Join

This join replicates each row from the first table with every row from the second table. This will display every possible combination and can be implemented by two methods:

  1. Not including a joining condition in the WHERE clause.
  2. Using the JOIN method with the CROSS JOIN keywords.

Equality Join

Through a commonly named and defined column a join can be crated. This also has two methods:

  1. Using the WHERE clause.
  2. Using the JOIN method with the NATURAL JOIN, JOIN….ON, JOIN….USING keywords.

Non-Equality Join

Joins tables when there are no equivalent rows.

  1. Using the WHERE clause.
  2. Using the JOIN method with the JOIN….ON keywords.

Self-Join

Joins a table to itself.

  1. Using the WHERE clause.
  2. Using the JOIN method with the JOIN….ON keywords.

Outer Join

Include records of a table in output when there is no matching record in the other table.

  1. Using the WHERE clause with a ( + ) operator.
  2. Using the JOIN method with the OUTER JOIN keywords with the assigned type of LEFT, RIGHT, FULL

Set Operators

Combines results of multiple SELECT statements. Includes the keywords UNION, UNION ALL, INTERSECT, and MINUS.

(more…)

Posted in Oracle, School | No Comments »

Managing and Creating Users

Saturday, March 3rd, 2007

Creating a user

Like table names and columns, a user name must follow the same rules. There is one minor difference. A user name can contain a dollar sign.

CREATE USER username
[ IDENTIFIED BY password ]
[ PASSWORD EXPIRE ]

The syntax for creating a user is pretty straight forward. The PASSWORD EXPIRE is used for when the user logs on for the first time and makes them to change their password. Once a user has been created they are not allowed to log into the system because they have no privileges at all.

(more…)

Posted in Oracle, School | No Comments »