ORACLE NOTES

CHAPTER TWO INTRODUCTION TO SQL EDITOR

USER

The authorize person who can use the system. According to the Oracle the person who
have the access to Oracle software.
Each user is assigned a user id and password.

USER ID:

It is called user identification. It is the string which is unique for each user for the
identification in Oracle.
All the users communicate with each other through their user id.

PASSWORD:

It is the secrete code/string which is assigned each user. The user can log in to the Oracle
by their user and password.
The difference between user id and password is that the user id will be displayed
on the screen while the password can not displayed on the screen.

LOGIN:

It is the procedure which is followed by each user to access the Oracle sofrware .
The user will be connected to the Oracle.

LOGOUT:

It is the procedure by which the user will be disconnect from Oracle

HOW TO LOGIN:

The procedure can be different for different system and version of Oracle.
But as we are using personal Oracle for windows 95, so following steps will be
followed.
1. Click the start button of Task bar OR Press CTRL+ S
2. Click the PROGRAM
3. Select the Oracle for Window 95
4. Select the SQL Plus 3.2

Compiled by http:[Link] 1
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR
Figure 1

Compiled by http:[Link] 2
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR
Following screen will be displayed

5. Enter your User id (e.g. Scott)

6. Enter your Password (Tiger)
7 Enter your Host Computer Name If then are more than one data base servers.
8. You will see the SQL prompt

SQL > -

Which in the SQL editor which is just like edlin editor of Dos. It one line edotor , It
mean you can not edit the previous command.

Compiled by http:[Link] 3
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR

The SQL editor is ready to get your command

HOW TO LOGOUT:

It is very simple to logout, just write Quit on SQL prompt and you will be disconnected
to Data base screen..
e.g.

SQL > Quit

Command Line:

SQL > -

The SQL Plus prompt means that SQL Plus is ready for you to enter a command.
The line on which the SQL > prompt appear is called command line. At the end of each
line press Enter Key.
Each line start with a line number. It mean that there can be more then one command to
process.
If you have finished the command the SQL Plus will process it and SQL prompt
will appear. It mean the SQL is ready for next command.

Compiled by http:[Link] 4
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR

TYPES OF COMMAND:

There are two types of commands you can enter on the command line.

1. SQL Command for working with information in the database.

e.g.
SELECT, DESCROBE, ALTE, UPDATE etc.

2. SQL Plus command for formatting results, Setting options , editing and
storing of SQL command.
e.g.
EDIT, RUN, SAVE, GET, COLUMN etc.

GENERAL FORMAT OF SQL COMMAND:

The general syntax of SQL command is

VERB [Parameter 1], [Parameter 2],..........[Parameter];

Where
VERB: is the reserve word of Oracle, used to perform a particular operation.

SELECT, FROM, WHERE etc.

PARAMETER: These are the values inputted to the VERB on which the operation
will be performed . In some command these are optional.
, is used to separate are parameter from other.
; A semi colon indicate the end of SQL sentence.

For Example:

SQL > Select, R no, Name

2. From Stud
3. Where Marks > 520;

in above example there are three

VERB SELEVT, FROM, WHERE

Parameters are R no, Name, Stud and Marks > 520.
; Indicate the end of SQL sentence.

Compiled by http:[Link] 5
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR
SQL Buffer:

When you enter a command, It is stored in a part of memory ( Ram) called SQL
buffer. It remains there until you enter a new command.
It mean that if you want to edit or Re-run the current command, you can do it without re
entering it.

OPERATIONS ON SQL BUFFER

Following commands are used to

 Edit
 Change
 Save
 Open
 Run
the commands without re-entering it.

LIST COMMAND

The current command will started in SQL Buffer until you enter a new command.
If you want to display the current command in SQL you can use the list command.
The list is SQL Plus command and will not be stored in the Buffer.

SYNTAX
LIST [ n , m]
n and m are optional, n is for single line or starting line number.
m is ending line number of command.

Example

1. To list the current SQL command in Buffer

SQL > List

Select R no, Name
From Stud
Where Marks > 520

SQL> -
2. To list line number 3 of current command

SQL > List 3

Where Marks > 520

SQL> -

Compiled by http:[Link] 6
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR

3. To list line number 2 to 3

SQL > List 2 , 3

From Stud
Where Marks > 520

SQL> -

TO RIUN THE COMMAND

Following are three methods to run a Query.

 Run
 ;
 @
 /

Run
Command

This command is used to run the command which is in SQL Buffer or to re run the
command.

SYNTAX:
RUN
Example:
To re run the following command
---> SQL > Select *
From Stud ;
SQL > Run
b. This command is used indicate the end of SQL command and to process the
command.

Example:
c. To create and run the above command write
SQL > Select *
From Stud;
SQL > Run

@ File Name:

Compiled by http:[Link] 7
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR
As you can save your command as a file. @ command is used to open and run the
command which have been saved as text file.

SYNTAX:
@ File Name[ . SQL ]
File Name is the name of file which continue as query/command.
.SQL is the extension of file which is optional to specify.

Example:
If you want to run the file named [Link]
SQL > @ CR. SQL

/ COMMAND

/ can be used to run the SQL command or block of command. The difference between ;
and / is that ; indicate end and appear at the end of Query.
When / appear at the next line of last sentence of the Query.

Example:

If you want to display the structure of Stud file.

SQL > describe Stud
2. /
2. You can use / instead of Run command to re- run the command.
SQL > /

ED/ EDIT Command:

Your Host computer's system contain a text editor (e.g. Note Pad in windows 95 )
which can be used to create and edit the files.
You can run the editor without leaving the SQL Plus by ED command.
ED command the contents of the current Buffer. When you save the changes into text it
will be save in Buffer.

SYNTAX:
ED [ File name]
File Name: Is the name of file which will be edit. If not specified then current
command will be open.

Example:
If you have made a mistake in your Query and want to correct this mistake.

SQL > EDIT

Compiled by http:[Link] 8
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR
SAVE
Command

This command is used to save a SQL command on disk for later use. The current
command will be saved as text file.

SYNTAX:
Save Filename [ . SQL ]
Make sure that file name you choose does not refer to an existing file.
.SQL is added to the file name to identitify it as SQL Query file.

Example:

1. If you want to save the following command as file.

2.
SQL > Create Table Pet( Acc Number(6),Sdate Date(10), Chno Number (5),
Amount Number (6,2));

SQL > Save Submission ;

3. To Save the command

SQL > Insert Into Pet values (1001,’5-may-2000’,2096,1600.00);

SQL > Save [Link];

GET COMMAND

This command is used to retrieve a query from the disk (file) to Buffer.
If you want to work with a stored command after running some other command, You can
retrieve the command from the file in which it is stored by GET Command.

SYNTAX:
GET file [ . SQL ]
File It is the name of the file which contain the Query.
.SQL It is added automatically with the file name.

Example:
If you want to open the Query stored in Subdata file.

SQL > GET Subdata

-----------------------
-----------------------

Compiled by http:[Link] 9
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR
It is very good technique for the insertion of data. Write the INSERT command save it as
a file with save command. Retrieve the file with GET command when you want to insert
the data into file.

HELP Command

SQL Plus Help command which is used to display the on line Help. It is used to display
the useful informations about different commands.
You can enter Help command any time the SQL prompt is display.

SYNTAX:
HELP [ Command ];

HELP Without command will display a list of all SQL and SQL Plus command.
Command Help with command none will display information about particular
command.

Example:
1. If you want to get the full information about SQL

SQL > HELP;

2. If you want display the information about the DESTRIBE Command.

SQL > HELP DESCRIBE;

Compiled by http:[Link] 10
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR

CHAPTER NO 3:
TABLES
DATA TYPES:
Following are different data types available in Oracle.

 CHAR
 VARCHAR2
 NUMBER
 DATE
 LONG
 LONG RAW
 RAIN
 ROWID

1. CHAR:
Values of this data type are fixed length character string of maximum 255
characters.
Char values may consist of
A - Z(Alphabets)
a - z (Alphabets)
0 - 9 ( Digits)
+, - , % , $ , & etc Special Characters
e.g. ‘ R.N BABAR’ , ‘ H .N 69/14 [Link]’

SYNTAX:
CHAR ( W )
Where
The Maximum number of character cab stored in the column.
EXAMPLES
ENO CHAR (3)

2.VARCHAR2:
Values of this data type may consist of character or alphanumeric data of variable
length.
This field must be 1 to 2000 characters long.
SYNTAX:
VARCHAR2 ( W )
3. NUMBER:
This data type is used to store the numbers ( fixed or float point ).
It may consist of
Digits 0-9
Sign +,-
Decimal point .

Compiled by http:[Link] 11
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR
The maximum size of this field can up to 40 digits. The decimal point and sign will not
be count as digit.
SYNTAX:
a, NUMBER ( W )
b, NUMBER ( W , D )
W specify total number of digits allowed.
D specify total number of digits after decimal point.
Example:
NUMBER (6)
NUMBER (6,2)

4. DATE:
This data type is used to store the date and optional time. The standard formed for
date is dd-mon-yy
DD day number
mon first three characters of month name
yy year.
e.g.
03-may-72
It can store date values from January 1,4712 BC to December 31, 4712 AD.
5. LONG:
This data type can store variable length characters strings containing upto 65535
characters. No more than are column can be define as LONG per table.
SYNTAX:
LONG
6. LONG RAW:
The field of this data type is used to store the Binary data upto 2 Gega Byte.
7. RAW :
This field of this data type is used to store the Binary data up to 255 bytes.

8. ROWID:
Hexa decimal string representing a unique address of row in a table. It is used to
give each record with in your table a unique values. It automatic increment field of
your table.

Compiled by http:[Link] 12
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR

HOW TO CREAT A TABLE

Create Table Command
This command is used to create a structure of Data Base file.
It specify the following informations

* The name of the table

* The name of each column
* The type of data to be stored in each column
* The width of each column
* Other optional information ( data constraints )
SYNTAX:
CREATE TABLE tablename
( Column1 data type(size), Column2 data type(size),......);

Where

Table name: is the user defined name for the table to be created. It follow all the
naming rules.
Column: are the columns names
Data type: is the type of data for specified column
Size: The width of each column
Example:
1. To create a table named MFEE having your columns
Acc Up to 6 digits
date data of submission
Chno Up to 6 digits
Amount Fractional value 5 before decimal and 2 after decimal digits.

SQL > CREATE TABLE MFEE

(Acc Number(6), SDATE DATE, CHNO NUMBER(6),
AMOUNT NUMBER(7,2 ) );

2. To create a table named MFEESTRUCT having your columns

CCODE Up to 3 characters
DESCRIPTION up to 30 characters
EFEE Up to 3 digits
AFEE Up to 3 digits
RFEE Up to 3 digits
PMFEE Up to 3 digits
MFEE Up to 3 digits

Compiled by http:[Link] 13
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR
SQL> CREATE TABLE MFEESTRUCT (CCODE VARCHAR2 (3),
DESRIPTION VARCHAR2(30),EFEE NUMBER(3) , AFEE NUMBER(3),
RFEE NUMBER(3),PMFEE NUMBER(4),MFEE NUMBER(3));

3. To create table named MPER having the following columns

ACC Up to 3 digits
NAME up to 30 characters
FNAME up to 30 characters
DOB Date
HNO Up to 6 characters
STREET Up to 15 characters
CITY Up to 20 characters
PNO 6 digits

SQL> CREATE TABLE MPER (ACC NUMBER(6), CCODE CHARACTER

(3), NAME CHARACTER(30), FNAME CHARACTER (30), DOB DATE,
HNO CHARACTER(6), STREET CHARACTER(15), CITY CHARACTER
(20), PNO NUMBER(6));

4. To create the table named MPER1 having following columns

ACC Up to 3 digits
CCODE Up to 3 Characters
QUALIF Upto 3 characters
PYEAR Date
OMARKS Up to 4 digits
TMARKS Up to 4 digits
BOARDUNI Up to 20 characters
SESSION Upto 1 character

SQL> CREATE TABLE MPER1 (ACC NUMBER(6), CCODE CHARACTER

(3), PYEAR DATE, OMARKS NUMBER (4), TMARKS NUMBER (4),
BOARDUNI CHARACTER (20), SESSION CHARACTER(1));

CREATING TABLE FROM TABLE:

When you went to create a new by using the columns and data type of existing
table you can create table command AS SELECT option.
It is to note that if existing table containe the records then these records will also
be copied into new table.
SYNTAX:
CREAT TABLE new table name [ colum name 1,…..]
AS SELECT colum1, colum2,………
FROM existing table name;

Compiled by http:[Link] 14
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR
WHERE
New table Will be the name of new table.
Column name1,.. are the name of the columns if you to change those with existing
table.
Column 1,….. are the names of column you want to select for new table.
Existing table name the name of the table from which you want to creat new table.
EXAMPLE:

1. We want to creat a new table named FEES from the existing table MFEE
SQL > CREAT TABLE FEES
AS SELECT .ACC , AMOUNT
FROM MFEE;

2. We want to creat a new table named PMFEE with columns course code, Monthly,
Admission from existing MFEE STRUCT.
SQL > CREAT TABLE PMFEE
Course code , Monthly , Admission
AS SELECT CODE, PMFEE,AFEE
FROM MFEESTRUCT;

DESCRIB COMMAND

This command is used to display the structure of specified Table.

It provide the following informations about the Table.
[Link]: List of columns names in the table.
[Link]: Whether the specified column can have null value or not.
[Link] type/Width: The data type and width of the column.
SYNTAX:
DESCRIBE Table name;
EXAMPLE:
To display the structure of MFEE table.
SQL > DESCRIBE MFEE

NAME NULL? DATA TYPE

ACC NUMBER(6)
SDATE DATE
CH NO NUMBER(6)
AMOUNT NUMBER(6,2)

How to Insert Data into Table:

The insert command is used to insert the into specified Table.
Then are two ways to insert row in the Table.
a, Insert data from user into Table.
b. Insert data from Table into another Table.

Compiled by http:[Link] 15
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR
INSERT INTO COMMAND

This command is used to insert the row into the specified table.
SYNTAX:
INSERT INTO Table name
[(Column name , Column name2,…….)]
VALUES ( Valeu1, Value2, Value3,…..);
WHERE

Table Name: is the table name to which the data will be Inserted.
Column name:These are optional. These are columns name is which the data will be
inserted. If not specified values to all columns will be inserted.
Value: are the constants values which are inserted into columns.
EXAMPLE:
To insert the row in the MFEE table as ACC=1 , SDATE=’10-OCT-99’,
CHNO=1301 and Amount=2800.00

SQL > INSERT INTO MFEE

Values ( 1,’10-OCT-99’,1301,2800.00);

To insert a row into MFEE STRUCT Table as Ccode = ‘PDP’,

DESCRIPTION=’office automation’, AFEE=500 and PMFEE=800

SQL > INSERT INTO MFEE STRUT

(CCODE, DESCRIPTION, AFEE, PMFEE)
VALUES(‘PDP’,’OFFICE AUTOMATION’,500,800);

RULES:
1. The number of columns names and values must be equale.
2. The data type of columns and type of values must match.
3. The character and date values must be in single qoutes.

EXERCISE
Insert the following data into table MFEE.
ACC SDATE CHNO AMOUNT

1 10-OCT-99 1215 2800.00

2 01-OCT-99 1215 2800.00
3 8-OCT-99 1215 2800.00
4 16-OCT-99 1215 2800.00
5 4-OCT-99 1215 2800.00
6 7-OCT-99 1215 2800.00
7 3-OCT-99 1215 2800.00
8 20-OCT-99 1215 2800.00
1 4-NOV-99 1215 800.00

Compiled by http:[Link] 16
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR
3 14-NOV-99 1215 840.00
4 4-NOV-99 1215 800.00
6 6-NOV-99 1215 800.00
8 10-NOV-99 1215 800.00

INSERT DATA FROM TABLE

You can also insert data from a table to another table.
SYNTAX:
INSERT INTO Destination Table[(CL1,CL2,….)]
SELECT Column1,Column2,……
FROM sure table;
WHERE
Destination Table The name of the table to which the records will be copied.
CL1,CL2,…. Are the columns names of the destination table.
These are optional.
Colunm1,Column2 Are the names of the columns of source table
Which will be copied to destination table columns.
Source Table The type and width of the source and destination columns must
match.
EXAMPLE:
1. You want to copy the record of MFEE to FEEs Table
SQL > INSERT INTO FEES (ACC,Amount)
FROM MFEE;
2. To copy the second from MFEESTRUCT table to PMFEE.
SQL > INSERT INTO PMFEE (Ccode, PMFEE,AFEE) SELECT Ccode,
PMFEE,AFEE
FROM MFEESTRUCT;

RECORDING COMMENTS ABOUT CABLES AND COLUMNS

Comment command:
This command is used to store the comments about the data are
[Link] is value able feature since a comment may be needed at latter use to
determine what the table is for.

SYNTAX:
COMMENT ON TABLE table name
IS ‘Comments’
WHERE
Table name Name of the table to which the comments will be recorded
EXAMPLE:
1. To record the comments for the table MFEE

SQL > COMMENT ON TABLE MFEE

2. IS ‘This table will store the fee records of the students’ ;

Compiled by http:[Link] 17
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR
2. To store the comments on the table MFEESTRUCT

SQL > COMMENT ON TABLE MFEE

2. IS ‘This table will store the Fee structure of different courses’;

You can also record the comments about the Columns

SYNTAX:
COMMENT ON COLUMN [Link]
IS ‘Comments’;
WHERE
Table name Name of the table to which the column belong
Column Name of the column to which the comments will be recorded

EXAMPLE:
2. To record the comments for the column ACC of table MFEE

SQL > COMMENT ON COLUMN [Link]

3. IS ‘These are the Account Numbers of the students’;

3. To store the comments on the column AFEE of table MFEESTRUCT

SQL > COMMENT ON COLUMN MFEESTRUCT. AFEE

2. IS ‘This is the Admission Fee of the Course’ ;

Compiled by http:[Link] 18
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR
CHAPTER NO 4

RETRIEVING AND VIEWING THE DATA FROM TABLE

SELECT FROM WHERE COMMAND

This command is used to retrieve and display the data from the data from the
Table and display on the screen.

SYNTAX
SELECT [options] [column1, column2, ……..,column n] [*]
FROM Tablename
[WHERE condition ]
where
Options are the operation on selection of the Columns to be
displayed.
DISTINCT The option will display the column values which not
similar.
Column The names of the column , the values of which will be
displayed on the screen.
Table name The name of the Table from the data will be retrieved.
Condition It is the expression which will result in either True or False.
The expression will match the Column values if matched
then the Row will be displayed.
e.g ACC > 5
NAME = ‘NAWAZ’
The WHERE clause is optional.

MANY FACES OF SELECT COMMAND

 Global Data Extract
 Retrieving Data Of Specified Columns
 Retrieving Unique Data Of Specified Columns
 Retrieving Sorted Data Of Specified Columns
 Slecting A Set Of Data From The Table
 Display Computed Results

Global Data Extract

To display the full data of the Table you can use the select command without
WHERE clause.

EXAMPLES
To display the data from the Table MFEE

SQL> SELECT *
2. FROM MFEE;

Compiled by http:[Link] 19
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR

ACC SDATE CHNO AMOUNT

1 10-OCT-99 1215 2800.00

2 01-OCT-99 1215 2800.00
3 8-OCT-99 1215 2800.00
4 16-OCT-99 1215 2800.00
5 4-OCT-99 1215 2800.00
6 7-OCT-99 1215 2800.00
7 3-OCT-99 1215 2800.00
8 20-OCT-99 1215 2800.00
1 4-NOV-99 1215 800.00
3 14-NOV-99 1215 840.00
4 4-NOV-99 1215 800.00
6 6-NOV-99 1215 800.00
8 10-NOV-99 1215 800.00

Retrieving Data Of Specified Columns

To display the values of the specified columns yu can write the name of
the columns in the SELECT clause

EXAMPLES
To display the column values of ACC and AMOUNT

SQL> SELECT ACC, AMOUNT

2. FROM MFEE;

ACC AMOUNT
1 2800.00
2 2800.00
3 2800.00
4 2800.00
5 2800.00
6 2800.00
7 2800.00
8 2800.00
1 800.00
3 840.00
4 800.00
6 800.00
8 800.00

Compiled by http:[Link] 20
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR
Retrieving Unique Data Of Specified Columns

To display the Unique values of the specied column use the DISRINCT with the
column name.

EXAMPLE
To display the unique values of the column ACC

SQL> SELECT DISTINCT (ACC)

2. FROM MFEE;

ACC
1
2
3
4
5
6
7
8

Retrieving Sorted Data Of Specified Columns

To display the sorted of the Table on the basis of specified column you can use
the ORDER BY cluase with the specified column name in FROM clause

EXAMPLE
To display the Sorted rows of the Table MFEE on the basis of AMOUNT column

SQL > SELECT *

2. FROM MFEE ORDER BY AMOUNT;

ACC SDATE CHNO AMOUNT

1 4-Nov-99 1215 800
4 4-Nov-99 1215 800
6 6-Nov-99 1215 800
8 10-Nov-99 1215 800
3 14-Nov-99 1215 840
1 10-Oct-99 1215 2800
2 1-Oct-99 1215 2800
3 8-Oct-99 1215 2800
4 16-Oct-99 1215 2800
5 4-Oct-99 1215 2800
6 7-Oct-99 1215 2800
7 3-Oct-99 1215 2800
8 20-Oct-99 1215 2800

Compiled by http:[Link] 21
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR

Slecting A Set Of Data From The Table

To display a set of the Rows of the Table You can specify the condition in the
WHERE cluase on which the ROWS of the Table will be selected. If row matches the
condition it will be displayed .

CONDITION

It is the expression which result in either TRUE or FALSE.

FORMAT
Value1 Operator Value2
Where
Value can be any constant , columns or variable

OPERATOR
Following are the Relational operators , Can be used in the condition

OPERATOR MEANING
= Equal to
!= , <> Not Equal To
> Greater Than
< Less than
>= Greater Than Or Equal To
<= Less than Or Equal To

EXAMPLES
To display the record of ACC no 1
SQL> SELECT *
2. FROM MFEE;
3. WHERE ACC = 1;

ACC SDATE CHNO AMOUNT

1 4-Nov-99 1215 800
1 10-Oct-99 1215 2800

To display the amounts greater than 800

SQL> SELECT *
2. FROM MFEE;
3. WHERE AMOUNT > 800;

ACC SDATE CHNO AMOUNT

3 14-Nov-99 1215 840
1 10-Oct-99 1215 2800
2 1-Oct-99 1215 2800
3 8-Oct-99 1215 2800
4 16-Oct-99 1215 2800

Compiled by http:[Link] 22
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR
5 4-Oct-99 1215 2800
6 7-Oct-99 1215 2800
7 3-Oct-99 1215 2800
8 20-Oct-99 1215 2800

To display the AMOUNTS paid on date 10/10/1999.

SQL> SELECT *
2. FROM MFEE;
3. WHERE SDATE = ‘10-OCT-99’ ;

ACC SDATE CHNO AMOUNT

1 10-Oct-99 1215 2800

LOGICAL OPERATORS

There are three logical operators used with conditions

NOT
AND
OR

NOT Operator
This operator is used to inverse the result of the condition
For example
You want to display the row of the ACC is 1

SQL> SELECT *
2. FROM MFEE
3. WHERE ACC=5;

You want to display the all rows except the rwos without ACC 1

SQL> SELECT *
2. FROM MFEE
3. WHERE NOT ACC=1;

EXPRESSION NOT EXPRESSION

TRUE FALSE
FALSE TRUE

AND Operator

Compiled by http:[Link] 23
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR
The result of the AND will be true if the result of the all conditions are True. The
possible combinations of the two conditions can be

EXP1 EXP2 EXAMPLE RESULT

FALSE FALSE 5 > 10 AND 10=20 FASLE
FALSE TRUE 5 > 10 AND 20=20 FASLE
TRUE FALSE 5 < 10 AND 10=20 FASLE
TRUE TRUE 5 0> 10 AND 20=20 TRUE

OTHER OPERATORS

BTWEEN V1 AND V2 Select the rows which match the Range.

Example
SQL> SELECT *
2. FROM MFEE;
3. WHERE AMOUNT BETWEEN 800.00 AND 1000.00;

IN (V1,V2,V3…...) Any of a list of values

Example
SQL> SELECT *
2. FROM MFEE;
3. WHERE ACC IN (1,2,5) ;

IS NULL Is a null value

Example
SQL> SELECT *
2. FROM MFEE;
3. WHERE SDATE IS NULL;

LIKE [%][ _ ] Matches the character pattern

% any sequence of zero or more characters
_ any single character
Example
SQL> SELECT *
2. FROM MPER;
3. WHERE CCODE LIKE ‘D%’;

SQL> SELECT *
2. FROM MPER;
3. WHERE NAME LIKE ‘M%’;

Compiled by http:[Link] 24
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR
SQL> SELECT *
2. FROM MPER;
3. WHERE CCODE LIKE ‘D_S’;

DELETION OPERATIONS

DELETE FROM COMMAND

This command is used to delete entire rows of the table or to delete the specified rows
which match the given condition

SYNTAX

DELETE FROM Tablename

[WHERE Condition]

Where
Table Name Is the name of the Table from which the rows will be deleted .
Conndition The expression which matches the rows if matched that would be
deleted.
If WHERE cluase is not mentioned then all records would be
deleted.
EXAMPLES
To delete all rows from the Table T1

SQL> DELETE FROM T1;

To delete the rows which have the acc greater than 6

SQL> DELETE FROM T1

2. WHERE ACC > 6;

DROP TABLE Command

This command is used to delete the Table along with data in the Table.

SYNTAX
DROP TABLE Tablename

Where Table name Is the name of the table to be deleted

EXAMPLE
To delete the table T1 .

SQL> DROP TABLE T1;

Compiled by http:[Link] 25
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR

TO CHANGE THE STRUCTURE OF THE TABLE

ALTER TABLE COMMAND

This command is used to change the structure of the existing table. The following are the
restriction for changing the structure of the

 The Table should be empty

 The name of the column can not be changed
 The name of the Table can not be changed

The can be two operations on the table to change the structure

1. To change the data type and width of the Column of the Table
2. To add the new Columns in the Table
We will discuss the two operations separately

To Change the Data type or width of the Column

SYNTAX
ALTER TABLE Table name
MODIFY (Column1 Data type (size), Column2 Data type (size), …..)
Where
Table name is the name of the Table who’s structure is to be changed
Column is the name of the Column who’s structure is to be changed
Data Type can be NUMBER, CHAR, VARCHAR2, ROWID, RAW, LONG,
LONG RAW
Size is the whole unsigned number

EXAMPLES

If we want to change the data type of the Column ACC of Table FEES to CHAR
and size 4.
SQL> ALTER TABLE FEES
MODIFY (ACC CHAR(4) );

If we want to change the size of Column AMOUNT of Table FEES to 8.3 .

SQL> ALTER TABLE FEES

MODIFY (AMOUNT NUMBER (8,3) );

Now display the structure of the table FEES

SQL> DESCRIBE FEES

Compiled by http:[Link] 26
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR
NAME NULL? DATA TYPE

ACC CHAR(4)
AMOUNT NUMBER(8,3)

To add the new Columns in the Table

We can also add new columns in the existing table , These columns will be
inserted to the Right side of the Table

SYNTAX

SQL> ALTER TABLE Table Name

ADD ( Column1 data type (size), Column2 data type (size),……..);

Where
Table name is the name of the Table who’s structure is to be changed
Column is the name of the Column which will be inserted into Table
Data Type can be NUMBER, CHAR, VARCHAR2, ROWID, RAW, LONG,
LONG RAW
Size is the whole unsigned number

If we want to add new Column address in Table FEES of data type

VARCHAR2 and size 20 and Mnth as DATE data type.

EXAMPLES
SQL> ALTER TABLE FEES
ADD ( ADDRESS VARCHAR2(20), MNTH DATE);

To view the structure of the Table

SQL> DESCRIBE FEES
NAME NULL? DATA TYPE
ACC CHAR(4)
AMOUNT NUMBER(8,3)
ADDRESS VARCHAR2(20)
MNTH DATE

UPDATE SET COMMAND

This command is used to change the contents of the table. This command will
replace the values in the specified column with new values. We specify a condition to
select a row/ group of rows in which the value will be replaced .

SYNTAX

UPDATE TBLE Table name

SET column1= value1, column2=value2, …….

Compiled by http:[Link] 27
 ORACLE NOTES
CHAPTER TWO INTRODUCTION TO SQL EDITOR
WHERE CONDITION

Where

Table name is the name of the Table who’s structure is to be changed

Column are the names of the Column who’s value is to be replaced
Value are the constants /values which would be replaced with existing
values which satisfy the given condition.
Condition is the logical condition , which select the row/ group of rows in
which the value will be replaced.
EXAMPLES

If want to replace the SDATE value ’10-OCT-99’ with ’20-NOV-99’ in row

which have ACC 1 in table MFEE.

SQL> UPDATE TABLE MFEE

SET SDATE=’20-NOV-‘
WHERE ACC=1;

If want to replace the values

ACC SDATE CHNO AMOUNT
5 4-OCT-99 1215 2800.00
with
5 8-OCT-99 1218 800.00

SQL> UPDATE TABLE MFEE

SET SDATE=’20-NOV-‘, CHNO=1218, AMOUNT=800.00
WHERE ACC=5;
If we want to replace AMOUNT Column values 2800 with 1950
SQL> UPDATE TABLE MFEE
SET AMOUNT=1950.00
WHERE ACC=2800.00;

Compiled by http:[Link] 28