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:
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:
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:
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:
=> 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:
So *theoretically* you could join these two tables on the "deptno" column.
Which means your SQL query could be something like this:
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?
Great explanation. Thanks a mill. Just one question; where did the "e." and "d." come from?
"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:
=> 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.
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:
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):
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)