Exforsys.com
 
Home Tutorials Oracle 9i
 

Tutorial 2: SQL *Plus Commands

 

SQL Statement Examples

Page 3 of 4


1.

Enter the following SQL statement and observe the results:
SELECT *
FROM S_EMP;

Solution
View Output

2.

Describe the table S_EMP. Make note of what kind of information you see about the table.

Solution
View Output

3.

Use the SQL*Plus buffer editing command to edit your SQL statement so that it looks like this:

SELECT dept_id, first_name||' '||last_name, salary, commission_pct
FROM S_EMP
ORDER BY dept_id;

Solution
View Output

4.

Save your script to a file called emp_sal.

Solution

5.

Enter the following SQL statement and run it. When you are prompted for dept_id, enter the value 44.

SELECT dept_id, last_name
FROM S_EMP
WHERE dept_id = '&dept_id';

Solution
View Output

6.

Customize the prompt for your statement:

ACCEPT dept_id NUMBER PROMPT Enter a department ID

Solution
View Output

7.

Using the select from exercise 4, customize the column headings so that the headings are in proper case, and columns with more than one word have split headings.

Solution
View Output

8.

Set a break on column dept_id.

Solution
View Output

9.

Compute the average salary and commission for each department.

Solution
View Output

10.

Add a title to the top of the report. Put the page number at the bottom.

Solution
View Output

11.

 


Set the report to start a new page for each department id. Display the department id in the top title, and remove the dept_id column.

Solution
View Output

12.

Send the output of your report to a file called employee.txt.

Solution
View Output




Next Page: Using SQL *Plus for Storing Temporary Values


Read Next: Tutorial 3 : Restricting and Sorting Data



 

 

Comments


Iceman said:

  hi i view the solution and output files it says

"Forbidden
You don't have permission to access /developers/articles/sqlplus_output1.html on this server."
December 23, 2008, 9:00 am

Post Your Comment:

Members Please Login
Your Name:*
e-mail ID:(required for notification)*
Image Verification: 
 
 Subscribe    

Sponsored Links

 

Subscribe via RSS


Get Daily Updates via Subscribe to Exforsys Free Training via email


Get Latest Free Training Updates delivered directly to your Inbox...

Enter your email address:


 

Subscribe to Exforsys Free Training via RSS
 

 
Partners -  Privacy and Legal Policy -  Site News -  Contact   Sitemap  

Copyright © 2000 - 2010 exforsys.com. All Rights Reserved

Page copy protected against web site content infringement by Copyscape