Programming

Originally Posted by durden_tyler

Your Oracle database instance comprises of multiple "schemas" or "database users", each of which has its own password. A "schema" is a logical container of database objects - tables, views, indexes, sequences, packages etc.

Each Oracle client installation is shipped with a client command-line utility called "sqlplus" (or SQL*Plus - its GUI incarnation).
"DESCRIBE <table_name>" is actually a sqlplus *command* and not an *option*. What it does is - it lists the column definitions for the table <table_name>. You can use it to describe views or packages as well. The "help" command provides more information:

Code:

scott@PDBORA12C>
scott@PDBORA12C> help describe

 DESCRIBE
 --------

 Lists the column definitions for a table, view, or synonym,
 or the specifications for a function or procedure.

 DESC[RIBE] {[schema.]object[@connect_identifier]}

scott@PDBORA12C>

Notice that "RIBE" is within brackets "[ ]" so that part is optional, which means you can use the short form "desc" for "describe".
Also, the commands and table names are case-insensitive in Oracle.

In the following example, I describe the "EMP" table:

Code:

scott@PDBORA12C>
scott@PDBORA12C> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

scott@PDBORA12C>

It tells me that the "emp" table has 8 columns as listed under "Name", with the datatypes as listed under "Type". It also tells me that the "Empno" column cannot be "NULL" which means it must have a value in every row of this table. (Oracle will not allow you to insert the row otherwise.)

Now I describe the "Dept" (department) table:

Code:

scott@PDBORA12C>
scott@PDBORA12C> desc dept
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

scott@PDBORA12C>

Note that the "deptno" column is in both the tables. And logically, it makes sense as well.
=> The "Dept" table contains a list of departments, one per row with a Department Number in each row. And I can see that:

Code:

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

=> The "Emp" table contains a list of employees, one per row.
Now, each employee should be in a department. That value, in each row, would be in the "Deptno" column of the "Emp" table.
And I can see that too:

Code:

     EMPNO ENAME      JOB              SAL     DEPTNO
---------- ---------- --------- ---------- ----------
      7499 ALLEN      SALESMAN        1600         30  -- ALLEN is in SALES
      7654 MARTIN     SALESMAN        1250         30  -- MARTIN is in SALES
      7782 CLARK      MANAGER         2450         10  -- CLARK is in ACCOUNTING
      7788 SCOTT      ANALYST         3000         20  -- SCOTT is in RESEARCH

So *theoretically* you could join these two tables on the "deptno" column.
Which means your SQL query could be something like this:

Code:

select <column_list>
   from emp e, dept d
 where e.deptno = d.deptno;

The <column_list> is a comma separated list of columns that you want to fetch from any or both the tables.

I said *theoretically* because there is much more to question - "on which columns should I join two tables?" To answer that, you have to understand the concepts of keys, primary/foreign keys and ER-Diagrams. That's a whole new beast in itself and there's a lot of literature on the Internet for that.

In a well designed system, for the above example, the "deptno" column would be a primary key in the "dept" table and a foreign key in the "emp" table.

So, to answer your questions:

In order to join two tables, check for the common columns. And see if those columns make sense logically. If they do, then you could join the two tables on those columns.
If the common columns have a primary key - foreign key relationship, then those are the *only* columns you should use for joining the tables.

It's a list of columns in the table with their data types and nullability values. The "describe" command does not tell you if the column is a primary or foreign key.

Originally Posted by bbbngowc

Great explanation. Thanks a mill. Just one question; where did the "e." and "d." come from?

Code:

select <column_list>
   from emp e, dept d
 where e.deptno = d.deptno;