SQL> edit promptexample

Enter example CODE below in text editor:

clear scr

prompt =========================================================
prompt =========================================================
prompt ===== Export employee Information by Salary =========
prompt ========== to Microsoft Excel Utility =================
prompt ========== promptexample =======================
prompt ============ Version 2.0 =========================
prompt =========================================================
prompt =========================================================
prompt =========================================================

prompt Please enter the following information:

-- Get the minimum salary
ACCEPT MinSal NUMBER PROMPT "Min Salary :"

-- Get the maximum salary
ACCEPT MaxSal NUMBER PROMPT "Max Salary :"


-- Select the required employee record
SELECT
empno as "ID",
ename as "Full Name",
hiredate as "hire On",
sal as "Sallary"
FROM
emp
WHERE
sal BETWEEN &MinSal AND &MaxSal
ORDER BY
ename;

/

The output :

SQL> start promptexample

=============================================
=============================================
===== Export employee Information by Salary ===========
========== to Microsoft Excel Utility =================
========== promptexample ================
============ Version 2.0 ===================
=============================================
==============================================
==============================================
Please enter the following information:
Min Salary :500
Max Salary :1500
old 9: sal BETWEEN &MinSal AND &MaxSal
new 9: sal BETWEEN 500 AND 1500

7876,ADAMS ,23-MAY-87, 1100
7900,JAMES ,03-DEC-81, 950
7654,MARTIN ,28-SEP-81, 1250
7934,MILLER ,23-JAN-82, 1300
7369,SMITH ,17-DEC-80, 800
7844,TURNER ,08-SEP-81, 1500
7521,WARD ,22-FEB-81, 1250
old 9: sal BETWEEN &MinSal AND &MaxSal
new 9: sal BETWEEN 500 AND 1500

7876,ADAMS ,23-MAY-87, 1100
7900,JAMES ,03-DEC-81, 950
7654,MARTIN ,28-SEP-81, 1250
7934,MILLER ,23-JAN-82, 1300
7369,SMITH ,17-DEC-80, 800
7844,TURNER ,08-SEP-81, 1500
7521,WARD ,22-FEB-81, 1250
SQL>

EDIT:

EDIT example CODE above and add code below:

OUTPUT:

=========================================================
=========================================================
===== Export employee Information by Salary ===========
========== to Microsoft Excel Utility =================
========== promptexample2 ================
============ Version 2.0 ====================
=========================================================
=========================================================
=========================================================
Please enter the following information:
Min Salary :500
Max Salary :1500
old 9: sal BETWEEN &MinSal AND &MaxSal
new 9: sal BETWEEN 500 AND 1500

ID Full Name Hire On Salary
------- ---------- ---------- ---------
007876 Adams 1987/05/23 $1100.00
007900 James 1981/12/03 $950.00
007654 Martin 1981/09/28 $1250.00
007934 Miller 1982/01/23 $1300.00
007369 Smith 1980/12/17 $800.00
007844 Turner 1981/09/08 $1500.00
007521 Ward 1981/02/22 $1250.00

7 rows selected.

SQL>

EDIT TO ADD SERVICE LENGHT:

SQL> edit promptexample3

clear scr

prompt =========================================================
prompt =========================================================
prompt ===== Export employee Information by Salary =========
prompt ========== to Microsoft Excel Utility =================
prompt ========== promptexample =======================
prompt ============ Version 2.0 =========================
prompt =========================================================
prompt =========================================================
prompt =========================================================

prompt Please enter the following information:

-- Get the minimum salary
ACCEPT MinSal NUMBER PROMPT "Min Salary :"

-- Get the maximum salary
ACCEPT MaxSal NUMBER PROMPT "Max Salary :"


-- Select the required employee record
SELECT
TO_CHAR(empno, '009999') as "ID",
INITCAP(ename) as "Full Name",
TO_CHAR(hiredate, 'yyyy/mm/dd') as "Hire On",
TO_CHAR
(
MONTHS_BETWEEN
(
SYSDATE, hiredate
),
'999'
) as "Service Lenght",
TO_CHAR(sal, '$9999.99') as "Salary"
FROM
emp
WHERE
sal BETWEEN &MinSal AND &MaxSal
ORDER BY
ename;

PROGRAM OUTPUT:

=========================================================
=========================================================
===== Export employee Information by Salary ===========
========== to Microsoft Excel Utility =================
========== promptexample3 ================
============ Version 2.0 ====================
=========================================================
=========================================================
=========================================================
Please enter the following information:
Min Salary :500
Max Salary :1500
old 17: sal BETWEEN &MinSal AND &MaxSal
new 17: sal BETWEEN 500 AND 1500

ID Full Name Hire On Serv Salary
------- ---------- ---------- ---- ---------
007876 Adams 1987/05/23 235 $1100.00
007900 James 1981/12/03 301 $950.00
007654 Martin 1981/09/28 303 $1250.00
007934 Miller 1982/01/23 299 $1300.00
007369 Smith 1980/12/17 312 $800.00
007844 Turner 1981/09/08 303 $1500.00
007521 Ward 1981/02/22 310 $1250.00

7 rows selected.

SQL>

NOTE: The Service Lenght is still in month e.g 299 month need to convert to year ( 12 month to make year).

EDIT the code to

clear scr

prompt =========================================================
prompt =========================================================
prompt ===== Export employee Information by Salary =========
prompt ========== to Microsoft Excel Utility =================
prompt ========== promptexample =======================
prompt ============ Version 2.0 =========================
prompt =========================================================
prompt =========================================================
prompt =========================================================

prompt Please enter the following information:

-- Get the minimum salary
ACCEPT MinSal NUMBER PROMPT "Min Salary :"

-- Get the maximum salary
ACCEPT MaxSal NUMBER PROMPT "Max Salary :"


-- Select the required employee record
SELECT
TO_CHAR(empno, '009999') as "ID",
INITCAP(ename) as "Full Name",
TO_CHAR(hiredate, 'yyyy/mm/dd') as "Hire On",
TO_CHAR
(
MONTHS_BETWEEN
(
SYSDATE, hiredate
)/12, /* to make to a year */
'999'
) as "Service Lenght",
TO_CHAR(sal, '$9999.99') as "Salary"
FROM
emp
WHERE
sal BETWEEN &MinSal AND &MaxSal
ORDER BY
ename;

RUN SQL QUERY AND SAVE OUTPUT TO CSV (EXCEL) FORMAT:

SQL> spool on
SQL> set pagesize 0 feedback off colsep ','
SQL> spool c:\promptexample3.csv
SQL> start promptexample3

=========================================================
=========================================================
===== Export employee Information by Salary ===========
========== to Microsoft Excel Utility =================
========== promptexample3 ================
============ Version 2.0 ====================
=========================================================
=========================================================
=========================================================
Please enter the following information:
Min Salary :500 /* Enter Value */
Max Salary :1500 /* Enter Value */
old 17: sal BETWEEN &MinSal AND &MaxSal
new 17: sal BETWEEN 500 AND 1500
007876,Adams ,1987/05/23, 20, $1100.00
007900,James ,1981/12/03, 25, $950.00
007654,Martin ,1981/09/28, 25, $1250.00
007934,Miller ,1982/01/23, 25, $1300.00
007369,Smith ,1980/12/17, 26, $800.00
007844,Turner ,1981/09/08, 25, $1500.00
007521,Ward ,1981/02/22, 26, $1250.00
SQL> spool off
SQL>

OUTPUT FILE IN C: DRIVE:

SPOOL on the program:

clear scr
prompt =========================================================
prompt =========================================================
prompt ===== Export employee Information by Salary ===========
prompt ========== to Microsoft Excel Utility =================
prompt ========== promptexample4 ================
prompt ============ Version 2.0 ====================
prompt =========================================================
prompt =========================================================
prompt =========================================================

prompt Please enter the following information:
-- Get the minimum salary
ACCEPT MinSal NUMBER PROMPT "Min Salary :"
-- Get the maximum salary
ACCEPT MaxSal NUMBER PROMPT "Max Salary :"
-- Begin SPOOLing output to file
SPOOL c:\promptexample4.csv
SET heading on feedback off pagesize 25 colsep ','

-- Select the required employee record
SELECT
TO_CHAR(empno, '009999') as "ID",
INITCAP(ename) as "Full Name",
TO_CHAR(hiredate, 'yyyy/mm/dd') as "Hire On",
TO_CHAR
(
MONTHS_BETWEEN
(
SYSDATE, hiredate
)/12,
'999'
) as "Service Lenght",
TO_CHAR(sal, '$9999.99') as "Salary"
FROM
emp
WHERE
sal BETWEEN &MinSal AND &MaxSal
ORDER BY
ename;
-- End of SPOOLing and save to data file.
SPOOL OFF