The Basics of SQL and Oracle Databases

This chapter is from the book

Oracle SQL by Example, 3rd Edition

Learn More Buy

This chapter is from the book

This chapter is from the book 

Oracle SQL by Example, 3rd Edition

Learn More Buy

LAB 2.3: Editing a SQL Statement

Lab Objectives

After this lab, you will be able to:

• Edit a SQL Statement Using SQL*Plus Commands

• Edit a SQL Statement Using an Editor

• Save, Retrieve, and Run a SQL Statement in iSQL*Plus

The Line Editor

In iSQL*Plus you can easily edit your statement just as any text. Sometimes you may not have access to iSQL*Plus, therefore you must learn how to write and edit a statement using the SQL*Plus line editor.

When using SQL*Plus, you may have noticed that typing the same SQL statement over and over again to make a small change quickly becomes very tedious. You can use SQL*Plus's line editor to change your statement, indicating which line to change, then use a command to execute the change.

At the SQL prompt, type and execute the following statement to retrieve a list of course numbers:

SELECT course_no
 FROM course

SQL*Plus stores the last SQL command you typed in what is referred to as the SQL buffer. You can re-execute a statement by just pressing the "/", which reruns the command. The statement stays in the buffer until you enter another SQL command. Use the SQL*Plus LIST command, or simply the letter L, to list the contents of the buffer. The semicolon or the slash, both of which execute the statement, are not stored in the buffer. The asterisk next to the number 2 indicates this is the current line in the buffer.

SQL>LIST
 1 SELECT course_no
 2* FROM course

For example, if you want to retrieve a list of descriptions instead, simply change the column course_no to description using the line editor. To make a change, indicate to the line editor which line to make current. To change it to the first line, type the number 1 at the SQL prompt:

SQL> 1
 1* SELECT course_no

Just the first line of the two-line statement is displayed, and the asterisk indicates this is now the current line in the buffer. You can make a change to that line with the CHANGE command:

SQL>CHANGE/course_no/description

The newly changed line is presented back to you:

  1* SELECT description

The CHANGE command is followed by a forward slash, followed by the text you want to change, and separated from the new text with another forward slash. The abbreviated command for the CHANGE command is the letter C. You are now ready to execute your statement to produce the new result set. Because you are not typing the statement for the first time, you cannot use the semicolon. Type a forward slash to execute the statement instead. The forward slash will always execute the current SQL statement in the buffer. Remember that certain commands you have learned so far, such as the LIST command, are not SQL, but SQL*Plus commands. Only SQL statements are saved in the buffer, never SQL*Plus commands.

Using AN EDITOR IN SQL*PLUS for Windows

Although handy, using SQL*Plus's line editor capabilities can still be tedious, especially as your SQL statements grow in size and complexity. You may also want to save some statements for later use. This is where a text editor becomes useful. A text editor is a software program with no ability to format the text, such as with boldface or italics. Notepad, a text editor that comes with the Microsoft Windows operating systems, is one example of a text editor and is referenced in this book. Any other text editor will work just as well. For more about setting the default editor in SQL*Plus, see Appendix C, "SQL*Plus Command Reference."

To use a text editor in SQL*Plus for Windows or a SQL*Plus version with the command line interface, simply execute the EDIT or ED command. This command will invoke, or open, the default editor currently set in SQL*Plus. When you use the EDIT command at the SQL prompt, SQL*Plus will stay open in the background and your text editor will be in the foreground, automatically displaying the SQL statement in the buffer. The file already has a name, which can also be set as a default in SQL*Plus. For quick editing of statements, simply make your changes here, save the file, and exit Notepad, which brings you back to SQL*Plus. If you wish to save the file for future reference, while still in Notepad select Save As to save the file with a different name and any extension you wish. It is common to save SQL files with a .sql extension.

If your editor puts a .txt after the file name (effectively creating a myfile.sql.txt file), change the Save As type to All Files instead of Text documents (*.txt). Another way to ensure the file contains a .sql extension is to enclose the entire file name in quotes, (e.g., "myfile.sql" or if you want to include the path "c:\examples\ myfile.sql"). Figure 2.14 displays the Save As dialog in SQL*Plus.

Figure 2.14 Use of the Notepad text editor in SQL*Plus for Windows.

Notice that when you invoke an editor, the SQL statement ends with a forward slash on a separate line at the end. SQL*Plus adds this character to the file so the file can be executed in SQL*Plus. When you invoke the editor from SQL*Plus, you can't go back to the SQL*Plus screen until you close the editor.

Type the following statement:

SELECT *
 FROM course

Now edit the file in Notepad and select Save As to save a second file with the name myfile2.sql. Exit Notepad and type and execute a new, different SQL statement:

SELECT state
 FROM zipcode

This statement is now in the buffer; however, you can execute a different SQL statement, such as the one you saved in myfile2.sql, with the START or @ command.

SQL>@myfile2

If the myfile2.sql file is stored in a directory other than the default directory, you need to specify the drive and directory name. You can also specify a valid URL such as @http://script.repository/alice/myfile2.sql.

SQL>@c:\examples\myfile2

The statement in the file runs, producing a result set. Because the file already contains a forward slash, the SQL statement is executed automatically. If you save myfile2 with an extension other than .sql, you must type the file name and extension. If you want to change myfile2 again, simply type the following. Notepad will open with myfile2.sql containing your SQL statement.

ED c:\examples\myfile2

Changing the default Directory of SQL*Plus for Windows

Whenever you execute a script or save a file in SQL*Plus without specifying a directory, it is assumed to be in the default directory. Typically, this directory is named similar to C:\oracle\product\10.1.0\Db_2\BIN or C:\oracle \ora10\BIN. To change it to a different directory, such as the c:\guest directory, you need to create a shortcut. Modify the properties of the shortcut (see Figure 2.15) on the desktop to change the Start in field to the value c:\guest and then click OK. Whenever you invoke SQL*Plus through the shortcut, the c:\guest directory will be your default directory. If you are unsure how to create a shortcut in your Windows operating system, refer to the Windows documentation that came with your system. (Another way to change your default directory is by modifying your Windows registry. Only make these modifications if you are sufficiently knowledgeable about the Windows operating system. For more information, see Oracle's SQL*Plus User's Guide and Reference Manual.)

Figure 2.15 Changing the default directory in SQL*Plus for Windows.

Copying and Pasting Statements In SQL*Plus for Windows

SQL*Plus for Windows allows you to copy and paste statements. You can open an editor such as Notepad in a separate window (without invoking it from SQL*Plus with the EDIT command) and enter your statements. Then select the text and copy the statement (CTRL + C or Copy from the Edit menu) and paste it into SQL*Plus using the Paste menu option or the CTRL + V command key.

Editing IN iSQL*PLUS

Editing a SQL statement in iSQL*Plus is rather intuitive. You can enter the statements in the input area, also called the Workspace, and make changes using the delete and backspace keys or simply cut and paste. To save a statement to a text file, click on the Save Script button. You can reload the file later using the Load Script button. iSQL*Plus has a History tab that allows you to see the previously issued statements. Alternatively, you can also click the History link.

Figure 2.16 shows the last four statements that were issued. By default, at most the last ten statements or scripts are shown; you can increase this number when choosing Preferences, Interface Configuration, and then History Size. A history is only available for the duration of your current session. If you want to retain your statements after logout, you want to consider saving them to a file.

Figure 2.16 History screen.

To run an individual statement, check the Select button and click the Load icon to bring the statement into the Workspace. You can load multiple statements at once, be sure to end each statement with a semicolon or a forward slash. You'll learn more about running multiple statements shortly.

Preferences Screen

Either the Preferences icon on top or the Preference link on the bottom of the screen displays the Preferences screen. It allows you to customize your iSQL*Plus interface and execution environmental variables. Here you can change your password, modify the number of statements retained in the History screen, or change your display output location and page setup preferences. The menu choices on the left show Script Formatting, Script Execution, and Database Administration. These options allow you to change SQL*Plus environmental variables. You can leave them at their default setting. Most of these variables have equivalent SET commands that can be executed both in SQL*Plus and iSQL*Plus. You will learn more about these settings in Chapter 13, "The Data Dictionary and Advanced SQL*Plus Commands."

DIFFERENCES BETWEEN SQL*Plus and iSQL*PLUS

Throughout this book you will see both SQL*Plus and iSQL*Plus mentioned. For the most part the functionality between the two products is identical and does not impact on the result set, other than a different graphical output. If you are unclear if a certain SQL*Plus command performs identical in iSQL*Plus, refer to Appendix C, "SQL*Plus Command Reference."

Overall, there are a small number of differences between the two products, particularly with respect to edits of SQL statements and the display of data. You will find these differences highlighted in Table 2.1.

Unless specifically pointed out, all the mentioned SQL*Plus commands apply to both SQL*Plus and iSQL*Plus.

Table 2.1 n Differences between SQL*Plus and iSQL*Plus

Lab 2.3 Exercises

If you have access to only SQL*Plus but not iSQL*Plus or vice versa, just perform the exercises that are applicable for the specific environment. Exercises 2.3.1 and 2.3.2 use SQL*Plus only, not iSQL*Plus.

2.3.1 Edit a SQL Statement Using SQL*Plus Commands

Type and execute the following SQL statement (use SQL*Plus, not iSQL*Plus):

SELECT employer
 FROM student

1. Using SQL*Plus commands, change the column employer to registration_date and execute the statement again.

2. Using SQL*Plus commands, add a second column, phone, to the statement you changed. Display the phone column first, then the registration_date column, in the result set.

2.3.2 Edit a SQL Statement Using an Editor

Perform these exercises using SQL*Plus, not iSQL*Plus.

1. Invoke the editor and change the statement in your buffer to the following. Then save the file and execute it in SQL*Plus.

SELECT salutation, first_name, last_name, phone
 FROM instructor

2. Edit the preceding statement, which is now in your buffer, save it as inst.sql, and use the start or @ command to execute it in SQL*Plus.

3. Edit inst.sql, save it as inst.x, and use the RUN or @ command to execute it in SQL*Plus.

2.3.3 Save, Retrieve, and Run a SQL Statement in iSQL*Plus

1. Enter the following SELECT statement into the Workspace area and execute the statement. Then save the statement in a file called state_zip.sql and press the Clear button.

SELECT DISTINCT state
 FROM zipcode

2. Click the Load Script button and then the Browse... button and locate the state_zip.sql file you just saved. Then press the Load button to load it into the Workspace. Execute the statement.

3. Explain the difference between the SQL language and SQL*Plus or iSQL*Plus.

Lab 2.3 Exercise Answers

2.3.1 Answers

Type and execute the following SQL statement (use SQL*Plus, not iSQL*Plus):

SELECT employer
 FROM student

1. Using SQL*Plus commands, change the column employer to registration_date and execute the statement again.

Answer: Select the first line in the buffer, then use the CHANGE command to change employer to registration_date.

Type 1 to select the first line in the buffer:

SQL> 1
 1* SELECT employer

Then use the CHANGE command:

SQL> c/employer/registration_date
 1* SELECT registration_date

Type L to list the changed statement:

SQL> L
 1 SELECT registration_date
 2* FROM student

If you care to run the query, you can do so with the forward slash "/", which then executes the statement currently in the buffer.

2. Using SQL*Plus commands, add a second column, phone, to the statement you changed. Display the phone column first, then the registration_date column, in the result set.

Answer: You must again select the first line in the buffer, then use the CHANGE command to add the phone column to the SELECT list.

Type 1 to select the first line in the buffer:

SQL> 1
 1* SELECT registration_date

Then use the CHANGE command:

C/SELECT/SELECT phone,

Here, the CHANGE command will replace SELECT with SELECT phone, (including the comma), changing your statement to the following:

 1 SELECT phone, registration_date
 2* FROM student

The result set will display phone first, then the registration date:

PHONE           REGISTRAT
--------------- ---------
201-555-5555 	18-FEB-03
201-555-5555	22-FEB-03
...
718-555-5555	22-FEB-03
718-555-5555 	28-JAN-03

268 rows selected.

The CHANGE command looks for the first occurrence, from left to right, of the text you wish to change. When it locates it, it replaces this occurrence with the new text you wish to change it to.

Other Useful Line Editor Commands

Besides the CHANGE and LIST commands, the SQL*Plus line editor has a number of other commands. For example, to add another column to the SQL statement you use the APPEND command. The statement currently in the buffer lists as follows:

SQL> L
1 SELECT phone, registration_date
2* FROM student

First choose the line to which you want to add at the end, then use the A command and add the text you want to append. In the following example the ", last_name" text was added to the statement.

SQL> 1
 1* SELECT phone, registration_date
SQL> A , last_name
 1* SELECT phone, registration_date, last_name

Another useful command is the INPUT command; it adds a new line after the current line. To insert the text ", first_name" on the next line, use the INPUT or I command. SQL*Plus prompts you for a new line and you enter the text and press Enter. SQL*Plus prompts you once more for another new line and if you are finished adding, you press Enter again to indicate that you are done.

SQL> 1
 1* SELECT phone, registration_date, last_name
SQL> I
 2i , first_name
 3i
SQL> L
 1 SELECT phone, registration_date, last_name
 2 , first_name
 3* FROM student

If you need to insert the line before line 1, enter a 0 (zero) followed by a space and text. Use the DEL command if you want to delete lines in the buffer. To delete line 2, you enter:

SQL> DEL 2
SQL> L
 1 SELECT phone, registration_date, last_name
 2* FROM student

You can also save the statement using the SQL*Plus SAVE command. In the next example, the SQL query is saved in the c:\guest directory under the file name myexample.sql; if you don't specify the extension, by default it will be .sql.

SQL> SAVE c:\guest\myexample
Created file c:\guest\myexample

You do not need to type a semicolon or forward slash, it will automatically be added. The statement can now be run either with the START or @ command. If you subsequently write other SQL statements and the statement is no longer in the SQL buffer, you can load it back into the buffer with the GET command. (The .sql extension is optional). You can then re-execute the statement with the forward slash.

SQL> GET c:\guest\myexample
 1 SELECT phone, registration_date, last_name
 2* FROM student

2.3.2 Answers

1. Invoke the editor and change the statement in your buffer to the following. Then save the file and execute it in SQL*Plus.

SELECT salutation, first_name, last_name, phone
 FROM instructor

Answer: Use the EDIT command to edit the file and execute the changed statement in SQL*Plus with the forward slash.

2. Edit the preceding statement, which is now in your buffer, save it as inst.sql, and use the START or @ command to execute it in SQL*Plus.

Answer: Use the EDIT command to edit the file and save it as inst.sql. Execute the changed statement in SQL*Plus with the START or @ command.

SQL>@inst.sql

3. Edit inst.sql, save it as inst.x, and use the START or @ command to execute it in SQL*Plus.

Answer: At the SQL prompt, type EDIT,edit the file in your editor, save the file as inst.x, exit the editor, type at the SQL>prompt the command @inst.x to execute the changed statement.

Because you saved the file with an extension other than .sql, you must explicitly reference both the file name and its extension. If you want to edit this file, you must type EDIT inst.x at the SQL prompt.

2.3.3 Answers

1. Enter the following SELECT statement into the Workspace area and execute the statement. Then save the statement in a file called state_zip.sql and press the Clear button.

SELECT DISTINCT state

 FROM zipcode

Answer: When you execute this statement, it returns a list of the state abbreviations from the ZIPCODE table. When you click on the Save Script button, a message box informs you that the file is transferred from the Web browser to your individual computer. Click the Save button to save it on your computer (see Figure 2.17).

Figure 2.17 Save a file in iSQL*Plus.

After you click the Save button you are prompted to enter the file name. You see a suggested file name, but change it to state_zip.sql instead and change the Save as type to "All Files". The term script is just another word for command file containing one or multiple commands.

If you click the Clear button, the input area and output area are cleared, but note the SQL buffer is not cleared and it will still list the last statement if you were to enter the LIST command.

2. Click the Load Script button and then the Browse... button and locate the state_zip.sql file you just saved. Then press the Load button to load it into the Workspace. Execute the statement.

Answer: When you click the Browse... button, you will see a dialog box that displays the directory and file name similar to Figure 2.18. Then you need to press the Load button to transfer the file into the Workspace for execution. Afterwards, you can click the Execute button to run the statement. It is useful to save a file, if you want to retain the statement. iSQL*Plus retains a history of SQL statements you can access after you have cleared the screeen, but it is no longer available after your session ends.

Figure 2.18 The Choose file dialog in iSQL*Plus.

Running Multiple Statements in iSQL*Plus

You can run multiple SQL statements in iSQL*Plus. For example, if you want to run the following two statements, you either place them in a script file or simply type them into the input area. Just be sure to end every statement with either a semicolon or a forward slash at the beginning of a separate line. Note: you don't need a semicolon or forward slash for the last statement unless you run the statements inside a script file. In this case you must end each statement with either one. Therefore, it is a good habit to place either the semicolon or forward slash after each statement.

In Figure 2.19, you see two query results. One shows the distinct zip codes for all instructors, and the second result is a listing of first and last names for all students. The second statement does not quite fit on one screen, but you can scroll down to see the rest.

Figure 2.19 Executing multiple SQL statements.

Be careful, do not add both a semicolon and a forward slash to the same statement, otherwise it will be executed twice. For example, the next SQL statement will be executed twice.

SELECT DISTINCT zip
 FROM zipcode;
 /

COMMENTS IN SQL SCRIPTS

Placing comments or remarks into a SQL script is very useful when you revisit the script later. It helps document the purpose, thoughts, and ideas or simply lists the author and creation date. You must identify the comment; otherwise you will receive an error when running the command. You can distinguish between two different types of comments: a single-line comment denoted with double dashes or a multiline comment spawning multiple lines, which starts with an opening comment like this, /*, and ends with a closing comment, which looks like this, */.

Following is an example of a script file that includes comments, but comments can also be embedded within the SQL statement itself.

/* Multi-line comment
Homework #2
By: Kirsten Soehner
Date created: 4/30/2002
*/


-- Answer #1: This is a single-line comment!
SELECT DISTINCT state
 FROM zipcode;


-- Answer #2:
COL cost FORMAT $9,999.99
SELECT DISTINCT cost
 FROM course;


-- Answer #3:
SELECT instructor_id, -- Comment within a SQL statement!
 zip
 FROM instructor;

Note that SQL*Plus also has a REMARK command abbreviated as REM that allows single-line comments. This command is not recognized as a comment when your SQL statement is executed in an environment other than SQL*Plus or iSQL*Plus; it can also not be embedded in a SQL statement. Use the single-line and double-line comments mentioned previously instead!

3. Explain the difference between the SQL language and SQL*Plus or iSQL*Plus.

Answer: SQL is a language that allows you to retrieve, manipulate, define, and control access to the database. SQL*Plus and iSQL*Plus are environments in which to execute the SQL statements and display the results.

iSQL*Plus is the Web-based version of SQL*Plus and both programs are Oracle proprietary products. You can use other software programs to execute your SQL statements against an Oracle database. If you want to format your results in special ways use the SQL*Plus commands such as COLUMN FORMAT. If you don't execute the commands in iSQL*Plus or SQL*Plus these formatting commands are not available and you will need to use specific SQL functions to achieve somewhat similar results. Some of these SQL functions are discussed in the next chapter.

Lab 2.3 Self-Review Questions

In order to test your progress, you should be able to answer the following questions.

1. You can save a SQL statement to the SQL buffer for it to be referenced later.

1. True

2. False

2. After typing a SQL statement, you can execute it with either the semicolon or the forward slash.

1. True

2. False

3. You cannot save a .sql file to the A: drive.

1. True

2. False

4. The SQL*Plus START command can execute what is in the SQL buffer.

1. True

2. False

Answers appear in Appendix A, Section 2.3.