1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

ORACLE Tutorials

Working with SQL* PLUS

SQL* PLUS

Logging In to SQL*Plus:
How to invoke SQL*Plus depends on which type of operating system or windows environment you are running:

To login through a windows environment:

1- Click Start Programs Oracle- OraHome81 Application
Development SQL Plus.
2- Fill in username, password and database.

Where username & password = (W or M + student ID) for the first login and database = business (W for women and M for men).

For all of these samples we use the next username, password And database.

Sqlplus

UserName:        scott
Password:        tiger
Host String:    business

Leaving SQL*Plus:-
When you are done working with SQL*Plus and wish to return to the operating system, enter the EXIT or QUIT command at the SQL* Plus command prompt.
SQL>exit;
OR
SQL>quit;

Entering and Executing Commands:
You can enter three kinds of commands at the command
Prompt:
* SQL commands, for working with information in the database.
* PL/SQL blocks, also for working with information in the database.
* SQL*Plus commands, for formatting query results, setting options, and editing and storing SQL commands and PL/SQL.

Getting Help:
To get online help for SQL*Plus commands, type HELP at the command prompt followed by the name of the command.

For example:
SQL>help change;
SQL>help list;

Ending a SQL command:
You can end a SQL command in one of three ways:
?    with a semicolon (;) .
?    with a slash (/) on a line by itself.
?    with a blank line.

How to change your password:
You can change your password using one of the following statements from SQL * Plus prompt:

1. SQL> password;
Changing password for W4444444
Old password: ********
New password: ****
Retype new password: ****

OR

2. SQL>Alter user W4444444 identified by MIS;
User altered.

Where W4444444 is username & MIS is new password.


Execute the following SELECT statement to see what?s in your user space:

1. SQL> select * from tab;

TNAME TABTYPE
DEPARTMENT TABLE
COLLEGE TABLE

2. SQL> Describe College;

Name Null?      Type
CNUMBER NUMBER (2)
CNAME VARCHAR2 (20(
LOC VARCHAR2 (20(

3. SQL>describe department;
Name Null? Type
DNUMBER NUMBER (2)
DNAME VARCHAR2 (20)
CNAME VARCHAR2 (20)

4. To see the data inserted into COLLEGE & DEPARTMENT tables execute the following SELECT statements:

SQL>select * from college;
CNUMBER CNAME LOC
--------- -------------------- --------------------
1 Business UOS
3 Engineering UOS
4 Sciences UOS

SQL>select * from department;
DNUMBER DNAME CNAME
-------- -------------------- --------------------
21 MIS Business
22 ACCT Business
23 MRKTG Business
24 FINAN Business
25 ECONO Business

To display table structure use the following:

To list the column definitions of the three columns in the sample table DEPT, enter
SQL>describe dept;
The following output results:

Name     Type
--------    -------------
DEPTNO     NUMBER(2)
DNAME     VARCHAR2(14)
LOC     VARCHAR2(13)

OR

SQL>desc dept;

The same output result:

Name     Type
--------    -------------
DEPTNO     NUMBER(2)
DNAME     VARCHAR2(14)
LOC     VARCHAR2(13)


Interpreting an Error Message
For example, if you misspell the name of a table while entering a command, an error message will tell you that the table or view does not exist:

SQL> DESCRIBE DPT
ERROR:
ORA-04043: object DPT does not exist

MANIPULATING COMMANDS

Editing commands:
The next table shows that allow you to edit or examine or change the command
in the buffer without re-entering the command.

Command    Abbreviation    Purpose
APPEND text    A tex    Add text at the end of a line
CHANGE/old/new    C/old/new    Change old to new in a line
CHANGE/ text    C / text    Delete text from a line
CLEAR BUFFER     CL BUFF    Delete all lines
DEL    (None)    Delete a line
INPUT    I    Add one or more lines
INPUT text    I text    Add a line consisting of text
LIST    L    List all lines into SQL buffer
LIST n    L n OR n    List one line
List *    L *    List the current line
LIST LAST    L LAST    List the last line
LIST m n    L m n    List arrange of lines (m to n)





Editing Commands with a System Editor
Your host computer's operating system may have one or more text editors that you can use to create and edit host system files. Text editors perform the same general functions as the SQL*Plus editing commands, but you may find them more familiar.
You can run your host operating system's default text editor without leaving SQL*Plus by entering the med command:
SQL> EDIT
EDIT loads the contents of the buffer into your system's default text editor. You can then edit the text with the text editor's commands. When you tell the text editor to save edited text and then exit, the text is loaded back into the buffer.


SAVING COMMANDS FOR Later USE
Through SQL * Plus, you can store one or more commands in a file, called a command file After you create a command file you can retrieve, edit and run it; Use command files to save commands for use over time, especially complex commands or PL/SQL blocks.
Storing Commands in Command Files
To save the current SQL command or PL/SQL block for later use, enter the SAVE command, followed with a file name:
SQL> SAVE file_name;
You can save the last SQL statement by listing it and then execute SAVE command, or
By entering your SQL statement and then execute SAVE command as shown at the next
Sample SQL*Plus adds the extension .sql for files saved from SQL*Plus.

SQL> SELECT DEPTNO, ENAME, SAL
2 FROM EMP
3 WHERE DEPTNO = 10
4* ORDER BY SAL DESC

SQL> SAVE DEPTINFO
Created file DEPTINFO

Retrieving Command Files:
Now suppose you need to retrieve the DEPTINFO file in a later session .You can retrieve the file by entering the GET <? filename> command. To retrieve the file DEPTINFO enter:

SQL>get deptinfo;
1 SELECT DEPTNO, ENAME, SAL
2 FROM EMP
3 WHERE DEPTNO = 10
4* ORDER BY SAL DESC



Placing Comments in Command Files
You can enter comments in a command file in one of three ways:
?    Using the SQL * Plus REMARK command.
?     Using the SQL * Plus delimiters, /*?*/.
?    Using ANSI/ISO (American National Standards Institute/International Standards Organization) comments ,--.
Anything that is identified in one of these ways as a comment is not parsed or executed by SQL * Plus.

Using the REMARK Command
Use the REMARK command on a line by itself in the command file, followed by comments on the same line. To continue the comments on additional lines, enter additional REMARK commands.

Do not place a REMARK command between different lines of a single SQL command.

SQL> REMARK Commissions report
1 REMARK to be run monthly.

Using /*...*/
The comments can span multiple lines, but cannot be nested within one another:

SQL>/* Commissions report
1    To be run monthly. */


****************************

Reviews and Comments


PMP, CAPM, PMI is a registered certification mark of the Project Management Institute, Inc

Copyright © www.techfaq360.com 2016


About US | Contact US | Privacy Policy | Terms and Conditions  | Website disclaimer  | Cancellation and Refund Policy  | Shipping & Delivery Policy