SQL note 1

Chap 1: Basic statement
----------------------------
1. query what table you have in your own schema:

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE

NOTE:   sytax

SELECT   *|{[DISTINCT] column|expression [alias],...}
FROM     table;

After FROM keyword, it can be a result set or table name. tab is a not real table and it's in memory.


2.  basic calucaltion in query

* / + -

SQL> select ename, sal*12 from emp;

ENAME          SAL*12
---------- ----------
SMITH            9600
ALLEN           19200
WARD            15000

NOTE:  column "SAL*12" IS A PSEUDO COLUMN, because they are not the data real exist in datbase.

3. NULL value issue:
A null is a value that is unavailable, unassigned,  unknown, or inapplicable.

NOTE: NULL value should not be directly do calculation,  otherwise, it will be meanless at most of the time.

4. Column Alias
    I. use AS keyword, usually keyword AS can be skipped if the alias name is consist of numbers, alphabets, underscore

SQL> select ename, sal*12 AS anual_sal from emp;

ENAME       ANUAL_SAL
----------    ----------
SMITH                     9600

  

select ename, sal*12 anual_sal from emp;

 
   II.  use double quotes
 

SQL> select ename, sal*12 as "anual_sal" from emp;

ENAME       anual_sal
---------- ----------
SMITH            9600

NOTE: Compare those headers in red, what's the differences between them?


5. Concat

SQL> select ename || ' is managed by ' || mgr from emp;

ENAME||'IS MANAGED BY'||MGR
-----------------------------------------------------------------
SMITH is managed by 7902
ALLEN is managed by 7698

6. Remove the duplicated records --- distinct

SQL> select distinct job from emp;

JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST

7. Some command in sqlplus

SQL> desc(ribe) table_name
SQL> set linesize 120SQL> set pagesize 200

Tips:

[oracle@milo ~]$ cat /u01/app/oracle/product/10.2.0/db_1/install/portlist.ini
iSQL*Plus HTTP port number =5561
Enterprise Manager Console HTTP Port (orcl) = 1158
Enterprise Manager Agent Port (orcl) = 3938

## write to login.sql
[oracle@milo ~]$ pwd
/home/oracle

[oracle@milo ~]$ vim login.sql
set linesize 120;
set pagesize 200;

### isqlplus
isqlplusctl start
firefox  &

NOTE: isqlplus is not a good to in many occasions, because when data is very large, it will take a lot of space to hold cache. Plus is only used from Oracle 9i to Oracle 10g.