Introduction to SQL *Plus Oracle SQL Interface

1
Introduction to SQL PlusOracle SQL Interface
Database Systems
2
Connect to Oracle SQL Plus

• In MS Windows, open Run SQL Command Line from
  the start menu.
• Type, Connect
• Enter hr for the name and password or enter
  system and the admin password you created
  during the Oracle install.

3
The SQL Command Prompt
4
Show all Tables
5
(No Transcript)
6
Introduction to SQL Plus
Oracles proprietary SQL is named SQLPlus.
Besides the standard SQL statements, it provides
an environment where you can create, save, run
SQL statements and do other chores.
Some common SQLPlus Commands REMARK SET
HEADSEP TTITLE(top title)
BTITLE COLUMN BREAK ON
COMPUTE SUM SET LINESIZE n SET
PAGESIZE n SPOOL SET PAUSE (ON
OFF)
7
Examples
8

• The SQLPlus Editor. In Windows, the NotePad is
  typically assigned as the SQLPlus Editor.
• SQLPlus always keeps the last SQL statement
  (not command) in the editor. If you do a mistake,
  you mat activate the editor to make correction.
  The command to activate the editor is Edit or
  simply Ed. When you save the edited file, by
  default, the system will save it in a file named
  afidt.buf.

9
Edit the statement, save and close the file. You
will be back to the SQL screen. Issue a /
Command. The system will run the saved statement.
No semicolon
10
The ED command
11
New query will remove the past query from the
edit buffer
12
Get Files to Run
13
Introduction to SQLPlus Scripts

• A Script file is a text file which may contain
  several SQL statements and some selected SQLPlus
  commands. Rather than doing the queries
  interactively, we may write the statements in a
  text file and then run the statements in batch
  mode.
• We will use the Notepad to develop a simple
  script. WE will save it as myScript1.txt in some
  directory. Then we will run the script in
  SQLPlus using the START or _at_ command.

14
Script is a text file
15
(No Transcript)
16
(No Transcript)
17
Run Example Script
18
(No Transcript)
19
Copying a table to another table (with data
structure)
20
Copying the structure of a table to another table
(without data)
The BETWEEN Operator
21
Parameter Query
22
The ACCEPT command
Like a variable
23
(No Transcript)
24
(No Transcript)
25
A list of employees with budgeted compensation.
Budget is (fixed salary quotacommRate)
26
Some employees do not have fixed salary. In that
case we want the budget to be quotacopmmRate. If
some employees do not have quota, we want to
treat it as 0.
27
Using Special Pseudo-columns
28
End of Introduction
More commands and information can be found in the
Oracle Documentation available online.