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.
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;
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;
"e." is required because "e" is the table alias for "emp" table. Same thing for "d." - it is required because "d" is the table alias for "dept" table.
=> "e.deptno" means the "deptno column of the table whose alias is e i.e. emp table"
=> "d.deptno" means the "deptno column of the table whose alias is d i.e. dept table"
You can simply read out the query as regular text:
Code:
select <column_list> -- select a list of columns
from emp e, dept d -- from the "emp" and "dept" tables
where e.deptno = d.deptno; -- where the "deptno" column of "emp" table equals the "deptno" column of "dept" table
=> The table alias is a shorthand for the table name. Instead of using the table name everywhere, I could use the table alias to keep the query short.
=> I did not have to use "e" and "d"; any alias would've been fine. The following query runs successfully.
Code:
select count(*) from emp x, dept y where x.deptno = y.deptno;
But it's a good practice to use a table alias that resembles or is a mnemonic for the table name.
A table alias is optional if all the tables are distinct. Without it, my query would've looked like this:
Code:
select count(*) from emp, dept where emp.deptno = dept.deptno;
However, if I use the same table more than once in the FROM clause i.e. if I join a table with itself, then the table alias is mandatory for the join. For example, in the following query, aliases are required (they do not have to be "e" and "m" though):
Code:
scott@PDBORA12C>
scott@PDBORA12C> -- List employees and their managers
scott@PDBORA12C> select e.ename as employee, m.ename as manager
2 from emp e, emp m
3 where e.mgr = m.empno;
EMPLOYEE MANAGER
---------- ----------
FORD JONES
SCOTT JONES
TURNER BLAKE
ALLEN BLAKE
WARD BLAKE
JAMES BLAKE
MARTIN BLAKE
MILLER CLARK
ADAMS SCOTT
BLAKE KING
JONES KING
CLARK KING
SMITH FORD
13 rows selected.
scott@PDBORA12C>
I must mention that there is other (ANSI specific) query syntax where the table alias could be avoided e.g. with the "USING" clause or the "NATURAL JOIN" clause.
Last edited by durden_tyler; 11-09-2014 at 09:26 AM..
Please advice to rectify below error
#!/bin/ksh
X=$(sqlplus -s user/pass << EOSQL
set serveroutput on;
set heading off feedback off serveroutput on trimout on pagesize 0
INSERT INTO TEST(df)
VALUES('a');
COMMIT;
EXIT;
EOSQL)
echo $X
echo $?
ERROR at line 2: ORA-00984: column not... (1 Reply)
Hi All ,
I have tried many times am getting syntax error on 'UNION'
can anybody tell me ...
INSERT INTO table1
(
Type
, num_items
, num_letters
, total_value
)
(select
type='1',
num_items,
num_letters=count(*),
total_value=sum(letter_value)
from
table2
where
num_items = 1 (1 Reply)
Hi, this's Pom. I'm quite a new one for shell script but I have to do sql on shell script to query some information from database.
I found a concern to get a new line...When I run my script, it retrieves all data as wondering but it's shown in one line :(
What should I do? I'm not sure that... (2 Replies)
Hi Everyone,
Can anyone pls help me out......with my requirement, i am struggling since 3 days. Please find the requirement below
my file contains below data
R1|Array/Network Resistor - VIP|V_RES_CLASS|V_MOUNT_FEATURE|SURFACE MOUNT|AND|8533.10.00.20|8533.10.00.20|
R1|Array/Network Resistor... (9 Replies)
I'm having problems with writing my sql results to a file:
sqlplus -S username/password@DB <<!!
set echo off
set verify off
set showmode off
set feedback off
set timing off
set linesize 250
set wrap off
set pagesize 0
set newpage none
set tab off
set trimspool on
set colsep... (1 Reply)
Hi ,
I used the below script to get the sql data into csv file using unix scripting.
I m getting the output into an output file but the output file is not displayed in a separe columns .
#!/bin/ksh
export FILE_PATH=/maav/home/xyz/abc/
rm $FILE_PATH/sample.csv
sqlplus -s... (2 Replies)
Hi All,
Please help me writing the below script.
I have two sql queries.
1. Select count(1),Client_id from TABLE_A group by Client_id;
2. Select count(1),Client_id from TABLE_B group by Client_id;
I need the output of above two sql queries in a single file. The output 2nd query should be... (4 Replies)
Hi I am new to using pl/sql on a unix platform and am having trouble writing to a file from within a block. Below is an example of the code that I have. I know that I need to use UTL_FILE to accomplish this; however, I keep getting errors. Can someone please help me?
I am trying to create a... (1 Reply)
I have a script that looks for all jobs that contain a particular calendar.
Some of the calendars have '&' in them and sql freaks out when it encounters that.. is there a way around this?
I have tried:
select job_name from job where run_calendar='1&15dom'
select job_name from job... (3 Replies)
Hello again... I have a request from another department to list for them all the columns and tables we use in this certain database. I have spooled the oracle stored procedured into 1 file. I need a way to write out parts of that file. The criteria is to to start the block to be written when... (0 Replies)