sql command using oracle SQL Plus

Open oracle SQL Plus from the menu bar

 

To define editor in oracle SQL Plus

 

 

OPEN AND ENTER PASSWORD: 

  SQL*Plus: Release 9.2.0.1.0 - Production on Tue Dec 19 16:35:52 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter password: *****

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL>

 

 

clear screen:

SQL> clear scr

 

To make you screen bigger:

SQL> show pagesize
pagesize 14
SQL> show linesize
linesize 80

SQL> set pagesize 25

 

FEEDBACK ENVIRONMENT:

SQL> set feedback on
SQL> show feedback
FEEDBACK ON for 1 or more rows

FEEDBACK example:

my name is Ford, I started work on 03-DEC-81
my name is Miller, I started work on 23-JAN-82

14 rows selected.

 

SQL> set feedback off
SQL> show feedback
feedback OFF

 

SET TIME TO APPAR ON YOUR COMMAND LINE: 

 SQL> set time on
11:45:27 SQL> run mynameis

11:45:38 SQL> set time off
SQL>
SQL> 

 

SAVE TO TEMP LOCATION

SQL> select deptno as "DEPartmet", loc as "LOCation" from dept where deptno = 30
2

[Press ENter key two time without executing the sql command]

 

then list the last command execute...

SQL> list
1* select deptno as "DEPartmet", loc as "LOCation" from dept where deptno = 30

 

To save the sql command to files use save command and follow with the filename to store your command.

SQL> save departmentlocation.sql
SP2-0540: File "departmentlocation.sql" already exists.
Use "SAVE filename[.ext] REPLACE".

SQL> save departmentlocation.sql replace
Wrote file departmentlocation.sql

SQL> edit departmentlocation.sql

SQL> run departmentlocation.sql
1* select deptno as "DEPartmet", loc as "LOCation" from dept where deptno = 30

DEPartmet LOCation
---------- -------------
30 CHICAGO

SQL>

We can use single line or multi-line comment for your code or sql script:

example:

 

AFTER SQL RESTARTED YOU NEED TO USE THE:

 

SQL> help list

 LIST
 ----

 Lists one or more lines of the most recently executed SQL command
 or PL/SQL block which is stored in the SQL buffer. Enter LIST with
 no clauses to list all lines.

 L[IST] [n | n m | n  * | n LAST | * | * n | * LAST | LAST]

EXAMPLE:

SQL> list
  1* select ename, hiredate from emp

SQL> l
  1* select ename, hiredate from emp

 

SQL> help desc

 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]}


SQL> 

 EXAMPLE:

SQL> describe 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)

SQL> 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)
 

 

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
BONUS
DEPT
EMP
SALGRADE

SQL> describe 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)

SQL> 

SQL> select empno, ename from emp;

EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS

EMPNO ENAME
---------- ----------
7900 JAMES
7902 FORD
7934 MILLER

14 rows selected.

SQL> select empno, ename from emp where empno >7780
2 ;

EMPNO ENAME
---------- ----------
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER

8 rows selected.

SQL>

SORTING

SQL> select empno, ename from emp;

EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS

EMPNO ENAME
---------- ----------
7900 JAMES
7902 FORD
7934 MILLER

14 rows selected.


SQL> select empno, ename from emp where empno > 7800 order by ename;

EMPNO ENAME
---------- ----------
7876 ADAMS
7902 FORD
7900 JAMES
7839 KING
7934 MILLER
7844 TURNER

6 rows selected.

SQL> select ename, empno from emp where empno > 7800 order by ename;

ENAME EMPNO
---------- ----------
ADAMS 7876
FORD 7902
JAMES 7900
KING 7839
MILLER 7934
TURNER 7844

6 rows selected.

SQL>

SQL> select * from dept;

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

SQL> SELECT DEPTNO as id, DNAME as "DEPT. Name", LOC as Location FROM dept;

ID DEPT. Name LOCATION
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

 

SQL> SELECT
2 DEPTNO as "id"
3 DNAME as "Dept. Name",
4 LOC as Location
5 FROM dept;
DNAME as "Dept. Name",
*
ERROR at line 3:
ORA-00923: FROM keyword not found where expected

 

SQL> SELECT
2 DEPTNO as "id"
3 ,
4 DNAME as "Dept. Name",
5 LOC as location
6 FROM dept;

id Dept. Name LOCATION
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL>

 

 

EXAMPLE 2

 

When I first met you, set my eyes on you, it was so excited to see something so wonderful. I can't believe myself seing something as nice as you naked. What more, I can touch you, remove anything I want, add anything I love to, do whatever I wish...dude, that's heaven. What else in this world do you need?

And that's all for free....yeah free. In these days, there's nothing left we can enjoy for free. Even rm0.30 for pee..yes and that's only 3 minutes. Devide by rm0.30, that's rm0.10 per minute. I don't think I enjoy the smell though..hmmmm

Suarkuyak ~ the one and only

 Assalmu'alaikum sahabatku...
 1 minit untuk mengingati Allah
 Langkah 1:
 Sebutlah dengan sepenuh hati dan lidah
 yang fasih:
 SUBHANA'LLAH
 ALHAMDULI'LLAH
 LA I LAHA ILLA'LLAH
 ALLAHU AKBAR
 ASTAGHFIRU'LLAH
 LA ILAHA ILLA'LLAH, MUHAMMADUN
 RASULU'LLAH
 ALLAHUMMA SALLI WA SALLIM
 WABARIK 'ALA SAYYIDINA
 MUHAMMAD
 WA AALIHI WA SAHBIH AJMA'EEN
 Langkah 2:
 Hayatilah sedalam-dalamnya akan makna
 ayat demi ayat, perkataan demi perkataan.
 Langkah 3:
 Forward mesej ini kepada sekurang-kurangnya
 7orang sahabat.
 Hasil 1:
 Dalam tempoh satu jam anda telah berjasa
 dengan mengajak mereka untukmengingati,
 berdoa dan bermunajat kepada ALLAH
 SWT.
 Hasil 2
 Dalam tempoh satu jam anda telah berjasa
 mengajak mereka untuk mengingati dan
 berdoa kepada Nabi Muhammad SAW.
 Hasil 3:
 Dalam masa satu jam sekurang-kurangnya
 7orang telah mendoakan kesejahteraan untuk
 kamu dan di-AMIN-kan oleh para
 Malaikat.
 Wassalam