2012

Sql questions

Sql questions and answers(IBM,POLARIS,HONEYWELL)
1. What is difference between DBMS and RDBMS?

In DBMS normalization process will not be present; there is no concept of primary and foreign key. In RDBMS normalization process will be present to check the database table consistency

2. What is Normalization?

Normalization is the process of efficiency organizing data in database

3. What is De-normalization?

De-normalization is the process of attempting the performance of a database by adding redundant data.

4. Difference between subquery and co-related subquery?

A subquery is a select statement that is embedded in a clause of another select statement. You can build powerful statements out of simple once by using sub queries.

EX: select ename, sal from emp where sal >(select sal from emp where ename ='FORD') /

A correlated subquery is a subquery that is evaluated once for each row of the outer query.

DIFFERENCE

The inner query executes first and finds a value, the outer query executes once using the value from the inner query(subquery)

Fetch by the outer query, execute the inner query using the value of the outer query, use the values resulting from the inner query to qulify or disqualify the outer query(correlated)

5. Join types with example

Join is the form of select command that contains information from two or more tables

Equi , inner ,simple join:- based on the equality condition in the where clause tables are joined only matching records are displayed, joining tables must have at least one common column with same data type and same value

Self join: - joins a table to itself as though it were two separate tables. it is used on same table, the table must have at least 2 column with same data type and value

Outer join: - outer joins gives the non-matching records along with matching records

Left outer join: - it will display the all matching records and the records which are in the left table. Even if there is no match in the right side table

Right outer join:- it will display the all matching records and the which are in the right table even if there no match in the left side table

Full outer join:- it will display matching records and non-matching records both table

6. Data types in sql

Each value in oracle is manipulated by a data type

1. Character data type: - They store character data which can alphanumeric data

2. Number data types: - Stores numeric data

3. Date time data types:- stores date time information likes dd-mon-yy 12:00:00

4. Rowid data types: - each row in the database has as address,

7. What is nvl function?

The nvl function is used to convert a null value to an actual value.

Syntax: nvl(expr1 , expr2)

Expr1 : is the source value or expr may contain null

Expr2: is the target value for converting null

8. Difference between nvl2 function and null if function

Nvl2 function contain 3 expressions , null if function contain 2 expressions

Nvl2 function: syntax: nvl2(expr1 , expr2 , expr3 )

If expr 1 is not null nvl2 returns expr2. If expr1 is null nvl2 returns expr3.

Null if function: syntax null if (expr1 ,expr2)

Compares two expressions and returns null if they are equal or the first if they are not null.

9. Difference between union and union all

Union is the sql key word used to merge the results of two or more tables using a select statement, contain same fields with removed duplicate values.

Union all does same, however it persists duplicates

10. What is data dictionary?

Data dictionary is a collection of database object it’s user , it stores privileges of oracle users.

11. What is single row function and multi row function?

Single row function

Single row functions can be categorized into five. These will be applied for each row and produces individual output for each row. Accept character data as input and can return both char and numeric values

1. Numeric functions

2. String functions

3. Date functions

4. Miscellaneous functions

5. Conversion functions

Multi row function

Multi row functions will be applied on all the rows but produce single row output

Ex: Sum, avg, max, min, count.

12. What is view and materialized view

View is a logical table, through which a selective portion of the data from one or more tables can be seen, view is stored as a select statement in the database. Views are used to restrict access to the database table

Materialized view is a physical table selective portion of the data from table’s periodically can be seen, it is used to improve the performance, useful in distributed environment

Difference

1. View is a logical table

2. View can hold the query

3. We can’t create indexes on view

4. View will create security purpose

1. Mv is a physical tale

2. Mv can hold the query with refresh data

3. We can create indexes on mv

4. Mv will create performance issues

13. What are pseudo columns?

Oracle supports several special-purpose data elements that are not actually contained in a table, but are available for use in SQL statements as if they were part of the table.

Here is a partial list of pseudo-columns in Oracle. For a complete list refer to Oracle's reference guide.

ROWNUM
For each row of data returned by a SQL query, ROWNUM will contain a number indicating the order in which the row was retrieved. For example, the first row retrieved will have a ROWNUM of 1, the second row will have a ROWNUM of 2, and so on. This approach can be useful for limiting the number of rows returned by a query. To display only ten rows of the emp table, the following SQL statement makes use of the ROWNUM pseudo-column:

SELECT  *
FROM   emp
WHERE   ROWNUM  < 11

NOTE:
ROWNUM returns a number indicating the order in which the row was retrieved from the table, but this is not always the order in which a row is displayed. For example, if a SQL statement includes an ORDER BY clause, rows will not be displayed in ROWNUM sequence, since ROWNUM is assigned before the sort operation.

CURRVAL
When using Oracle SEQUENCE values, the pseudo-column CURRVAL returns the current value of the sequence. To reference CURRVAL, it must be associated with a sequence, for example:

schema.sequence_name.CURRVAL

NEXTVAL
When using Oracle SEQUENCE values, the pseudo-column NEXTVAL returns the next value of the sequence and causes the sequence to increment by one. You can only reference NEXTVAL if it is associated with a sequence. For example:

schema.sequence_name.NEXTVAL

USER
This pseudo-column will always contain the Oracle username under which you are connected to the database.

SYSDATE
This pseudo-column will contain the current date and time. This column is a standard Oracle DATE datatype. The value represents the current time on the server not the client.

A pseudo-column is an Oracle assigned value (pseudo-field) used in the same context as an Oracle Database column, but not stored on disk. SQL and PL/SQL recognizes the following SQL pseudocolumns, which return specific data items: SYSDATE, SYSTIMESTAMP, ROWID,ROWNUM, UID, USER, LEVEL, CURRVAL, NEXTVAL, ORA_ROWSCN, etc.

Pseudo columns are not actual columns in a table but they behave like columns. For example, you can select values from a pseudo column. However, you cannot insert into, update, or delete from a pseudo column. Also note that pseudo columns are allowed in SQL statements, but not in procedural statements.

14. What is decode function

Decode will act as value by value substitution. For every value of field, it will checks for a match in a series of if/then tests.

Ex:

SQL> select sal, decode(sal,500,'Low',5000,'High','Medium') from emp;

15. What is case?

Case is similar to decode but easier to understand while going through coding

Ex:

SQL> Select sal,

Case sal

When 500 then ‘low’

When 5000 then ‘high’

Else ‘medium’

End case

From emp;

16. What is sequence and advantages?

Oracle sequence generates a series of unique numbers for numeric column of a database’s tables

17. What is inline function?

18. Difference b/w count (1) and count (*)

The function count() executes faster when given a criteria/value which is quicker to resolve in the sql processing mechanism.Thus, an integer or rowid are faster to resolve than an '*'which is a wild card symbol for all the colums in a table and hence it is more time consuming.
19. What are date functions?

Sysdate

Current_date

Current_timestamp

Systimestamp

Localtimestamp

Dbtimezone

Sessiontimezone

To_char

To_date

Add_months

Months_between

Next_day

Last_day

Extract

Greatest

Least

Round

Trunc

New_time

Coalesce

20. Lpad function and rpad functions

Lpad function to pad the left side of a column with any set of characters.

Ex: select lpad('computer',15,'*');

Rpad function to pad the right side of a column with any set of characters.

Ex: select rpad('computer',15,'*');

21. String in function

22. What is table space?

A data base divided into logically storage unit called table spaces .a table spaces is used to grouped related logical structures together. A table space consists of at least on data file.

23. Difference b/w having clause and where clause

The where clause select rows before grouping. The having clause select rows after grouping

------------ by lakshmi

sql questions

Explain normalization ?
Normalisation means refining the redundancy and maintain stablisation. there are four types of normalisation :
first normal forms, second normal forms, third normal forms and fourth Normal forms.

why you need indexing ? where that is stroed and what you mean by schema object? For what purpose we are using view?

We cant create an Index on Index.. Index is stoed in user_index table.Every object that has been created on Schema is Schema Object like Table,View etc.If we want to share the particular data to various users we have to use the virtual table for the Base table...So tht is a view.

indexing is used for faster search or to retrieve data faster from various table. Schema containing set of tables, basically schema means logical separation of the database. View is crated for faster retrieval of data. It's customized virtual table. we can create a single view of multiple tables. Only the drawback is..view needs to be get refreshed for retrieving updated data.

What is diffrence between Co-related sub query and nested sub query?

Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.
Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.
For example,

Correlated Subquery:

select e1.empname, e1.basicsal, e1.deptno from emp e1 where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno)

Nested Subquery:

select empname, basicsal, deptno from emp where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)
WHAT OPERATOR PERFORMS PATTERN MATCHING?
Pattern matching operator is LIKE and it has to used with two attributes
1. % and
2. _ ( underscore )
% means matches zero or more characters and under score means mathing exactly one character

What is cluster.cluster index and non cluster index ?
Clustered Index:- A Clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table may have only one clustered index.Non-Clustered Index:- A Non-Clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows in the disk. The leaf nodes of a non-clustered index does not consists of the data pages. instead the leaf node contains index rows.

How can i hide a particular table name of our schema?
you can hide the table name by creating synonyms.
e.g) you can create a synonym y for table x
create synonym y for x;

To update the sal with exception

declare
vempno number(4);
vename varchar2(20);
vsal number;
begin
select empno,ename, sal into vempno,vename, vsal from emp
where empno=&empno;
if vsal<1000 then
update emp set sal=sal+sal*0.35
where empno=vempno;
end if;
dbms_output.put_line('the emp det are:'||vename||''||vsal);
exception
when no_data_found then
dbms_output.put_line('the empno is not exists');
end;

Load data from query to excel file in a procedure

Load data from query to excel file in a procedure
Please execute this code...you can get proper result ...

emp table date in one sheet...dept data in one sheet in one excel book.
First login as sysdba and create a direcotry..and grant the permissions to your schema..
Open your my computer icon .. in 'C' drive create a folder as test_dir.
Then run this script....!!
As sys user:
Create user defined directory

GRANT CREATE ANY DIRECTORY TO Scott;

CREATE OR REPLACE DIRECTORY TEST_DIR AS 'c:\myfiles' /* directory on the Oracle database server */
/
GRANT READ, WRITE ON DIRECTORY TEST_DIR TO myuser
/
As myuser:

DECLARE
v_fh UTL_FILE.FILE_TYPE;
v_dir VARCHAR2(30) := 'TEST_DIR';
v_file VARCHAR2(30) := 'myfile.xls';
PROCEDURE run_query(p_sql IN VARCHAR2) IS
v_v_val VARCHAR2(4000);
v_n_val NUMBER;
v_d_val DATE;
v_ret NUMBER;
c NUMBER;
d NUMBER;
col_cnt INTEGER;
f BOOLEAN;
rec_tab DBMS_SQL.DESC_TAB;
col_num NUMBER;

BEGIN
c := DBMS_SQL.OPEN_CURSOR;
-- parse the SQL statement
DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
-- start execution of the SQL statement
d := DBMS_SQL.EXECUTE(c);
-- get a description of the returned columns
DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);

-- bind variables to columns

FOR j in 1..col_cnt
LOOP
CASE rec_tab(j).col_type
WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,4000);
WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
ELSE
DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,4000);
END CASE;
END LOOP;

-- Output the column headers

UTL_FILE.PUT_LINE(v_fh,'');
FOR j in 1..col_cnt
LOOP
UTL_FILE.PUT_LINE(v_fh,'');
UTL_FILE.PUT_LINE(v_fh,''||rec_tab(j).col_name||'');
UTL_FILE.PUT_LINE(v_fh,'');
END LOOP;
UTL_FILE.PUT_LINE(v_fh,'');
-- Output the data
LOOP
v_ret := DBMS_SQL.FETCH_ROWS(c);
EXIT WHEN v_ret = 0;
UTL_FILE.PUT_LINE(v_fh,'');
FOR j in 1..col_cnt
LOOP
CASE rec_tab(j).col_type
WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
UTL_FILE.PUT_LINE(v_fh,'');
UTL_FILE.PUT_LINE(v_fh,''||v_v_val||'');
UTL_FILE.PUT_LINE(v_fh,'');
WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
UTL_FILE.PUT_LINE(v_fh,'');
UTL_FILE.PUT_LINE(v_fh,''||to_char(v_n_val)||'');
UTL_FILE.PUT_LINE(v_fh,'');
WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
UTL_FILE.PUT_LINE(v_fh,'');
UTL_FILE.PUT_LINE(v_fh,''||to_char(v_d_val,'YYYY-MM-DD"T"HH24:MI:SS')||'');
UTL_FILE.PUT_LINE(v_fh,'');
ELSE
DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
UTL_FILE.PUT_LINE(v_fh,'');
UTL_FILE.PUT_LINE(v_fh,''||v_v_val||'');
UTL_FILE.PUT_LINE(v_fh,'');
END CASE;
END LOOP;
UTL_FILE.PUT_LINE(v_fh,'');
END LOOP;
DBMS_SQL.CLOSE_CURSOR(c);
END;
--
PROCEDURE start_workbook IS
BEGIN
UTL_FILE.PUT_LINE(v_fh,'');
UTL_FILE.PUT_LINE(v_fh,'');
END;
PROCEDURE end_workbook IS
BEGIN
UTL_FILE.PUT_LINE(v_fh,'');
END;
--
PROCEDURE start_worksheet(p_sheetname IN VARCHAR2) IS
BEGIN
UTL_FILE.PUT_LINE(v_fh,'');
UTL_FILE.PUT_LINE(v_fh,'');
END;
PROCEDURE end_worksheet IS
BEGIN
UTL_FILE.PUT_LINE(v_fh,'');
UTL_FILE.PUT_LINE(v_fh,'');
END;
--
PROCEDURE set_date_style IS
BEGIN
UTL_FILE.PUT_LINE(v_fh,'');
UTL_FILE.PUT_LINE(v_fh,'');
UTL_FILE.PUT_LINE(v_fh,'');
UTL_FILE.PUT_LINE(v_fh,'');
UTL_FILE.PUT_LINE(v_fh,'');
END;
BEGIN
v_fh := UTL_FILE.FOPEN(upper(v_dir),v_file,'w',32767);
start_workbook;
set_date_style;
start_worksheet('EMP');
run_query('select * from emp');
end_worksheet;
start_worksheet('DEPT');
run_query('select * from dept');
end_worksheet;
end_workbook;
UTL_FILE.FCLOSE(v_fh);
END;

Daily use Unix Commands

$uptime <-- To check the system uptime and we get to know that if its rebooted recently with other details.

ps -eaf | grep i<inode> <-- Fetch file using I node number.

ps -eaf | grep i check <-- to get the all inode numbers with file name in PWD.

topas <-To get the usage of kernel ,cpu,user,idle.

at now <-- to start process manually right away

> <filenname> <-- to make file size as 0 byte or Zero byte .

df -gt . <--To find where the file is mounted on or where its located with file system usage= Example like :/Home or /Shared.
where . <-- current path

df -gt <file name or /P /CDW> <-- Directly fetch exact info(file permission,size,created date) file/Dir which u metioned .

dsmc arch <File Name> <-- To takes bakup on TSM server.

chfs -a size=+10MB /home <--To add 10MB under /Home

ls -ltr <-- To get all list of files /Dir's with file size in current path.

ls -ld <Dir> <--To get the exact dir.
-----------------------------
Mount unmount nfsmount
------------------------
echo $SHELL <-- Command to find the current shell.
================================================================
Chown:
For example, the following would transfer the ownership of a file named file1 and a directory named dir1 to a new owner named alice:

chown alice file1 dir1
===============================================
SCP Copy the file from SYSMGT04 to SYSMGT02:
$pwd
$sysmgt02
$ scp vrathth@sysmgt04:/home/vrathth/file1 /home/vrathth/
vrathth@sysmgt04's password:
file1 100% 0 0.0KB/s 00:00
$ls -l
file1
==================================================
$lscfg -v <-- list all the devices with model numbers

sysmgt04: /# getconf DISK_SIZE /dev/hdisk1
140013
sysmgt04: /# who -r <-- runlevel
. run-level 2 Dec 5 13:27 2 0 S
sysmgt04: /# lsdev -Pc tape <-- list tape devices

sysmgt04: /# getconf BOOT_DEVICE <-- boot device
hdisk0
==============================

Using the chgrp Command on a File
--------------------------------

# ls -l
total 12
-rw-rw-r– 1 tclark authors 2229 Jan 13 21:35 declaration.txt
-rw-rw-r– 1 tclark authors 1310 Jan 13 17:48 gettysburg.txt
-rw-rw-r– 1 tclark authors 360 Jan 13 17:48 preamble.txt
# chgrp presidents gettysburg.txt
# ls -l
total 12
-rw-rw-r– 1 tclark authors 2229 Jan 13 21:35 declaration.txt
-rw-rw-r– 1 tclark presidents 1310 Jan 13 17:48 gettysburg.txt
-rw-rw-r– 1 tclark authors 360 Jan 13 17:48 preamble.txt
======================================

Using the chgrp Command on a Directory
-------------------------------
# ls -l
total 4
-rw-rw-r– 1 tclark tclark 0 Jan 13 21:13 example1.fil
-rw-rw-r– 1 tclark tclark 0 Jan 13 21:13 example2.xxx
drwxrwxr-x 2 tclark tclark 4096 Jan 13 21:35 examples
# chgrp authors examples
# ls -l
total 4
-rw-rw-r– 1 tclark tclark 0 Jan 13 21:13 example1.fil
-rw-rw-r– 1 tclark tclark 0 Jan 13 21:13 example2.xxx
drwxrwxr-x 2 tclark authors 4096 Jan 13 21:35 examples

====================================
Change User Ownership
---------------------
The chown (change owner) command can be used to change ownership of a file or directory. The syntax is very similar to chgrp.

# ls -l
total 12
-rw-rw-r– 1 tclark authors 2229 Jan 13 21:35 declaration.txt
-rw-rw-r– 1 tclark authors 1310 Jan 13 17:48 gettysburg.txt
-rw-rw-r– 1 tclark authors 360 Jan 13 17:48 preamble.txt
# chown abe gettysburg.txt
# ls -l
total 12
-rw-rw-r– 1 tclark authors 2229 Jan 13 21:35 declaration.txt
-rw-rw-r– 1 abe authors 1310 Jan 13 17:48 gettysburg.txt
-rw-rw-r– 1 tclark authors 360 Jan 13 17:48 preamble.txt
===============================================
sysmgt04: /home/vdas9am/sample# ls -l <--copying data with file permission.
total 0
-rw-rw-r-x 1 root system 0 Apr 23 01:00 b
-rw------- 1 root system 0 Apr 23 01:14 c

sysmgt04: /home/vdas9am/sample# cp -p b c
sysmgt04: /home/vdas9am/sample# ls -l
total 0
-rw-rw-r-x 1 root system 0 Apr 23 01:00 b
-rw-rw-r-x 1 root system 0 Apr 23 01:00 c
===============================================
$istat mydir <-- to see all the details abt dir.
$. <file/Dir> <-- to excute the file or dir or Shall (after . one speace should be there)
===========
etc/inittab
tsm:2:wait <--once it start after other related process will get start if its doesn't start other related process also won't
start.

====================================================

What's the command to see the kernel version?

uname -r
uname -a <-- to see all information like machine model
uname -v <-- see OS version
------------------------------------------
When we create user then following files are edited

/etc/passwd

/etc/security/passwd

/etc/security/limits

/etc/security/user

/etc/security/.profile

/usr/lib/security/mkuser.default

/usr/lib/security/mkuser.sys

-------------------------------------------------
how to take backup in aix server

Best Rated Answer:

(1)For backup of rootvg, mksysb command can be used on command line or via smitty.

Advantage of mksysb is that it can create a 'bootable' media of the backup of rootvg.

(2) For backup of filesystems, 'backup' command can be used ( restore command to be used for restoration of the backup).

(3) The answer given regarding savevg ( and restvg ) is correct
-----------------------------------------------------
How will u create bosboot image (Except bosboot -ad /dev/diskname)?

Best Rated Answer:

There is also a command to create boot image

mkboot -c -d /dev/diskname
------------------------

SQL*PLUSCOMMNANDS

SQL*PLUSCOMMNANDS

These commands does not require statement terminator and applicable to the sessions , those will be automatically cleared when session was closed.

BREAK

This will be used to breakup the data depending on the grouping.

Syntax:

Break or bre [on <column_name> on report]

COMPUTE

This will be used to perform group functions on the data.

Syntax:

Compute or comp [group_function of column_name on breaking_column_name or

report]

TTITLE

This will give the top title for your report. You can on or off the ttitle.

Syntax:

Ttitle or ttit [left | center | right] title_name skip n other_characters

Ttitle or ttit [on or off]

BTITLE

This will give the bottom title for your report. You can on or off the btitle.

Syntax:

Btitle or btit [left | center | right] title_name skip n other_characters

Btitle or btit [on or off]

Ex:

SQL> bre on deptno skip 1 on report

SQL> comp sum of sal on deptno

SQL> comp sum of sal on report

SQL> ttitle center 'EMPLOYEE DETAILS' skip1 center '----------------'

SQL> btitle center '** THANKQ **'

SQL> select * from emp order by deptno;

Output:

EMPLOYEE DETAILS

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

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7839 KING PRESIDENT 17-NOV-81 5000

7934 MILLER CLERK 7782 23-JAN-82 1300

---------- **********

8750 sum

7369 SMITH CLERK 7902 17-DEC-80 800 20

7876 ADAMS CLERK 7788 23-MAY-87 1100

7902 FORD ANALYST 7566 03-DEC-81 3000

7788 SCOTT ANALYST 7566 19-APR-87 3000

7566 JONES MANAGER 7839 02-APR-81 2975

---------- **********

10875 sum

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400

7900 JAMES CLERK 7698 03-DEC-81 950

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0

7521 WARD SALESMAN 7698 22-FEB-81 1250 500

---------- **********

9400 sum

----------

sum 29025

** THANKQ **

CLEAR

This will clear the existing buffers or break or computations or columns formatting.

Syntax:

Clear or cle buffer | bre | comp | col;

Ex:

SQL> clear buffer

Buffer cleared

SQL> clear bre

Breaks cleared

SQL> clear comp

Computes cleared

SQL> clear col

Columns cleared

CHANGE

This will be used to replace any strings in SQL statements.

Syntax:

Change or c/old_string/new_string

If the old_string repeats many times then new_string replaces the first string only.

Ex:

SQL> select * from det;

select * from det

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> c/det/dept

1* select * from dept

SQL> /

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH ALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

COLUMN

This will be used to increase or decrease the width of the table columns.

Syntax:

Column or col <column_name> format <num_format|text_format>

Ex:

SQL> col deptno format 999

SQL> col dname format a10

SAVE

This will be used to save your current SQL statement as SQL Script file.

Syntax:

Save or sav <file_name>.[extension] replace or rep

If you want to save the filename with existing filename the you have to use replace option.

By default it will take sql as the extension.

Ex:

SQL> save ss

Created file ss.sql

SQL> save ss replace

Wrote file ss.sql

EXECUTE

This will be used to execute stored subprograms or packaged subprograms.

Syntax:

Execute or exec <subprogram_name>

Ex:

SQL> exec sample_proc

SPOOL

This will record the data when you spool on, upto when you say spool off. By default it will give lst as extension.

Syntax:

Spool on | off | out | <file_name>.[Extension]

Ex:

SQL> spool on

SQL> select * from dept;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> spool off

SQL> ed on.lst

SQL> select * from dept;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> spool off

LIST

This will give the current SQL statement.

Syntax:

List or li [start_line_number] [end_line_number]

Ex:

SQL> select

2 *

3 from

4 dept;

SQL> list

1 select

2 *

3 from

4* dept

SQL> list 1

1* select

SQL> list 3

3* from

SQL> list 1 3

1 select

2 *

3* from

INPUT

This will insert the new line to the current SQL statement.

Syntax:

Input or in <string>

Ex:

SQL> select *

SQL> list

1* select *

SQL> input from dept

SQL> list

1 select *

2* from dept

APPEND

This will adds a new string to the existing string in the SQL statement without any space.

Syntax:

Append or app <string>

Ex:

SQL> select *

SQL> list

1* select *

SQL> append from dept

1* select * from dept

SQL> list

1* select * from dept

DELETE

This will delete the current SQL statement lines.

Syntax:

Delete or del <start_line_number> [<end_line_number>]

Ex:

SQL> select

2 *

3 from

4 dept

5 where

6 deptno

7 >10;

SQL> list

1 select

2 *

3 from

4 dept

5 where

6 deptno

7* >10

SQL> del 1

SQL> list

1 *

2 from

3 dept

4 where

5 deptno

6* >10

SQL> del 2

SQL> list

1 *

2 dept

3 where

4 deptno

5* >10

SQL> del 2 4

SQL> list

1 *

2* >10

SQL> del

SQL> list

1 *

VARIABLE

This will be used to declare a variable.

Syntax:

Variable or var <variable_name> <variable_type>

Ex:

SQL> var dept_name varchar(15)

SQL> select dname into dept_name from dept where deptno = 10;

PRINT

This will be used to print the output of the variables that will be declared at SQL level.

Syntax:

Print <variable_name>

Ex:

SQL> print dept_name

DEPT_NAME

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

ACCOUNTING

START

This will be used to execute SQL scripts.

Syntax:

start <filename_name>.sql

Ex:

SQL> start ss.sql

SQL> @ss.sql -- this will execute sql script files only.

HOST

This will be used to interact with the OS level from SQL.

Syntax:

Host [operation]

Ex:

SQL> host

SQL> host dir

SHOW

Using this, you can see several commands that use the set command and status.

Syntax:

Show all | <set_command>

Ex:

SQL> show all

appinfo is OFF and set to "SQL*Plus"

arraysize 15

autocommit OFF

autoprint OFF

autorecovery OFF

autotrace OFF

blockterminator "." (hex 2e)

btitle OFF and is the first few characters of the next SELECT statement

cmdsep OFF

colsep " "

compatibility version NATIVE

concat "." (hex 2e)

copycommit 0

COPYTYPECHECK is ON

define "&" (hex 26)

describe DEPTH 1 LINENUM OFF INDENT ON

echo OFF

editfile "afiedt.buf"

embedded OFF

escape OFF

FEEDBACK ON for 6 or more rows

flagger OFF

flush ON

SQL> sho verify

verify OFF

RUN

This will runs the command in the buffer.

Syntax:

Run | /

Ex:

SQL> run

SQL> /

STORE

This will save all the set command statuses in a file.

Syntax:

Store set <filename>.[extension] [create] | [replace] | [append]

Ex:

SQL> store set my_settings.scmd

Created file my_settings.scmd

SQL> store set my_settings.cmd replace

Wrote file my_settings.cmd

SQL> store set my_settings.cmd append

Appended file to my_settings.cmd

FOLD_AFTER

This will fold the columns one after the other.

Syntax:

Column <column_name> fold_after [no_of_lines]

Ex:

SQL> col deptno fold_after 1

SQL> col dname fold_after 1

SQL> col loc fold_after 1

SQL> set heading off

SQL> select * from dept;

ACCOUNTING

NEW YORK

RESEARCH

DALLAS

SALES

CHICAGO

OPERATIONS

BOSTON

FOLD_BEFORE

This will fold the columns one before the other.

Syntax:

Column <column_name> fold_before [no_of_lines]

DEFINE

This will give the list of all the variables currently defined.

Syntax:

Define [variable_name]

Ex:

SQL> define

DEFINE _DATE = "16-MAY-07" (CHAR)

DEFINE _CONNECT_IDENTIFIER = "oracle" (CHAR)

DEFINE _USER = "SCOTT" (CHAR)

DEFINE _PRIVILEGE = "" (CHAR)

DEFINE _SQLPLUS_RELEASE = "1001000200" (CHAR)

DEFINE _EDITOR = "Notepad" (CHAR)

DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release

10.1.0.2.0 – Production With the Partitioning, OLAP and

Data Mining options" (CHAR)

DEFINE _O_RELEASE = "1001000200" (CHAR)

Pages