The Basics of SQL and Oracle Databases
This chapter is from the book
This chapter is from the book
This chapter is from the book
Chapter Objectives
In this chapter, you will learn about:
-
The SQL*Plus Environment
-
The Anatomy of a SELECT Statement
-
Editing a SQL Statement
-
The WHERE Clause: Comparison and Logical Operators
-
The ORDER BY Clause
Now that you are familiar with the concepts of databases and schema diagrams, you are ready to start with hands-on exercises. You will learn the basics of SQL*Plus, the software tool that allows you to execute statements against the Oracle database. After you familiarize yourself with SQL*Plus, you will be ready to write SQL statements, or queries, to retrieve the data. SQL statements can range from very simple to highly complex; they can be a few words long or a few hundred words long. In this chapter, you begin by writing simple SQL statements, but you will be able to build longer, more complex SQL queries very quickly.
Lab 2.1: The SQL*Plus Environment
Lab Objectives
After this lab, you will be able to:
-
Identify Oracle's Client/Server Software
-
Login and Logout of SQL*Plus
Oracle software runs on many different operating systems and hardware environments. You can use the SQL*Plus software under three different architectural configurations: as a stand-alone machine, in a client–server setup, or as iSQL*Plus within a three-tier architecture. Another piece of Oracle software, called SQL*Net (Version 7), Net8 (Version 8), or Oracle Net (Version 9i and 10g), provides the required communication protocol to the server.
STAND-ALONE ENVIRONMENT
SQL*Plus may be run in a stand-alone environment, where both the SQL*Plus client software and the Oracle database software reside on the same physical machine. This is the case when you install both the Oracle database server and the SQL*Plus software on your individual computer.
Client–Server
A common setup is a client–server environment, also referred to as two-tier architecture, where a client communicates with the server. In this type of environment, Oracle's SQL*Plus tool resides on a client computer such as a PC or Unix workstation; the Oracle RDBMS software resides on a server. Figure 2.1 shows such a client–server architecture.
)
Figure
2.1 Client–server architecture.
The client sends SQL statements to the server, and the server responds back with the result set. The job of the database server involves listening and managing many clients' requests, because in this configuration there are often multiple client machines involved.
Instead of Oracle's SQL*Plus program, the client machine may run any other program with the ability to execute SQL statements against a database (e.g., Visual Basic or a custom-built Java program). For the client computer's programs to communicate with the Oracle database server, the individual client machine is typically configured with the Oracle Net software, or the client may establish an Open Database Connectivity (ODBC) connection.
Three-Tier Architecture
Starting with Oracle 8.1.7, you can use the iSQL*Plus interface in a Web browser to access the Oracle database. It performs the same actions as SQL*Plus. The advantage of iSQL*Plus is that you don't need to install and configure the SQL*Plus program or Oracle Net software on your client machine. As long as you use a compatible browser on your machine and know the URL of the Oracle HTTP server, you can access the database. As with any connection, you obviously need a valid user account and password.
Figure 2.2 shows the three-tiered architecture of an iSQL*Plus configuration. The first tier is the client's Web browser, and the middle tier is the Oracle HTTP server (Web server) that receives requests from the browser and forwards them via Oracle Net to the third tier, the Oracle database server. The Oracle Web server returns results from the database server back to the Web browser for display. The three tiers may be on one machine but are typically on three different ones.
){kind=small}
Figure
2.2 Three-tier architecture.
SQL and the Oracle Database Server
In the midst of all this software lies the SQL language. SQL commands are sent from the client software, also known as the front end, to the server, or back end. These commands send instructions to the server to tell it what services to provide. The server responds by sending back a result to the client, where it is displayed by the client software. Figure 2.3 shows a SQL statement that queries the DESCRIPTION column of the COURSE table. The SQL statement is sent to the Oracle server and the result is displayed by SQL*Plus.
)
Figure
2.3 SQL and the Oracle database server.
User ID and password
To connect to the database and communicate via SQL*Plus, you must have a user id that has been created for you. For the purposes of all examples in this book, you use the user name STUDENT and the password LEARN. Note that the user ID and password are not case sensitive.
If you have not yet created the STUDENT schema according to the instructions on the companion Web site located at http://authors.phptr.com/rischert3e, you will not be able to log in with the STUDENT user ID and the LEARN password. You may want to continue to read through this lab first, create the STUDENT schema, and then perform the exercises in this lab.
Accessing the Oracle Database Server
You can access the Oracle server through various front-end tools. This book will discuss the use of Oracle's own SQL*Plus software (available as a graphical Windows environment and as a command line interface) and the browser-based iSQL*Plus.
This lab will teach you some of the basics of SQL*Plus, as this tool is almost always found in any Oracle database environment. The log on screens for SQL*Plus and the browser-based iSQL*Plus are slightly different, but easily understood. You can use either SQL*Plus or iSQL*Plus to execute your SQL statements, or perhaps you chose another front-end query tool that also allows you to enter SQL commands. (The companion Web site to this book lists other alternative query tools.) Differences between SQL*Plus or iSQL*Plus are pointed out to you as you work through the book. You can assume that with very few exceptions the functionality of iSQL*Plus and SQL*Plus are very similar, if not identical.
When working through this book, you have a choice to use either a browser and access iSQL*Plus or use the SQL*Plus software installed on your machine.
SQL*Plus Client for Windows
If the SQL*Plus program is installed on your Windows machine, you can access it by choosing Programs, then Oracle, Application Development, and SQL Plus. This launches the program and displays the Log On dialog box similar to Figure 2.4. Enter as the User Name STUDENT and as the Password LEARN.
)
Figure
2.4 Windows graphical user interface log on dialog box.
If your database is installed on the same machine as your SQL*Plus client, you don't need to enter a value in the Host String field. If you are connecting to a remote Oracle database, enter the Oracle Net connection string supplied to you by your Oracle database administrator and recorded in your TNSNAMES.ORA file. You will learn more about this special file later.
Figure 2.5 shows how your screen looks once you have successfully connected to the server. Effectively, you have established a connection with the Oracle database as the user STUDENT. The client and the server may now communicate with each other.
)
Figure
2.5 SQL*Plus prompt.
When you see the SQL> command prompt, SQL*Plus is ready to accept your commands and you may begin to type. This is the default prompt for SQL*Plus.
To log out, either type exit or QUIT and press enter. Alternatively, you can choose Exit from the File menu or simply use your mouse to close the window.
Connecting with a Web Browser: iSQL*Plus
Instead of the SQL*Plus software program, you can also use the Web-based version called iSQL*Plus. To access the iSQL*Plus interface through your Web browser, you enter a URL. A Log on dialog similar to Figure 2.6 will appear. Here, the URL is http://scooby:5560/isqlplus and will obviously be different for your individual installation.
A valid URL to connect to iSQL*Plus is in the form of http://machine_name.domain:port/isqlplus. For example, http://mymachine.acme.com:5560/isqlplus is an example of a URL format. As part of the default Oracle installation, you will usually see the iSQL*Plus port number displayed. If you are unsure about your specific port number, try the default port 5560.
Also notice in Figure 2.6 that the domain is not shown, only the machine name scooby. Because the machine is on a local network, you can omit the domain. Instead of the name of the machine, you can enter the IP address. If your Oracle database server is on your own machine and you want to access iSQL*Plus, you can substitute localhost instead and your URL will read http://localhost:5560/isqlplus. Alternatively, you can use the IP address of 127.0.0.1.
)
Figure
2.6 iSQL*Plus login screen.
Enter the user ID and password in the appropriate boxes. You don't need to supply the Connection Identifier (also called Host string) to connect to the default database instance.
Figure 2.7 displays the screen you see once you have successfully logged in. Notice the iSQL*Plus Workspace and the message "Connected as STUDENT@orcl" on the upper right-hand side of the screen. This indicates the name of the login user, which is STUDENT, and the name of the Oracle database instance you are connected to, called ORCL.
)
Figure
2.7 iSQL*Plus Workspace.
At the Enter statements text box, also referred to as the input area, you can enter commands. If you want to run a script (e.g., the script you need to execute to generate the STUDENT schema), you can enter the path and name of the script or click the Load Script button to locate the script. Once the script is loaded into the input area, you can edit the script or simply click the Execute button to execute the script. To logout and return to the Login screen, click on the Logout icon.
Starting the iSQL*Plus Application Server
For the Windows environment, the iSQL*Plus application server is installed as a Windows service as part of the default Oracle database server installation and usually started automatically.
For other operating systems—or if you prefer to start iSQL*Plus from the command prompt—use the following syntax %oracle_home%\bin\isqlplusctl start. For example, if C:\ORACLE\ORA10 is your Windows Oracle home directory where the files for the Oracle database and application server are installed, you start the iSQL*Plus application server with this command C:\oracle\ ora10\bin\isqlplusctl start.
Refer to the companion Web site for more information on general iSQL*Plus installation and configuration questions.
COMMAND-LINE INTERFACES FOR Sql*PLus
In place of a graphical user interface such as SQL*Plus for Windows or iSQL*Plus, you may use a command-line interface. A command-line interface is available with every Oracle version. Frequently, you will use this interface in operating systems such as Linux or Unix. Even Windows has a command-line interface and you will see it displayed in Figure 2.8. All SQL*Plus and SQL commands operate for this interface just the same. Note that depending on the operating system, your editor, as well as the cut and paste commands, may be different.
)
Figure
2.8 Command line-based SQL*Plus under the Windows operating system.
To invoke SQL*Plus, you type sqlplus at the respective operating system's command prompt. For Windows you start SQL*Plus by typing C:\> sqlplus from the Windows command prompt. In this example, the username and password is supplied to start SQL*Plus. You can also enter sqlplus and you will be prompted for the user name and password or sqlplus student, which will prompt for the password.
The Remote Database and Common Log-on Problems
Often the database resides on a machine other than your client machine, or you have a choice of accessing different databases. In these cases you need to supply the name of the database in the Host String box of the Log On dialog box (see Figure 2.9) or the Connection Identifier box in iSQL*Plus. For example, to connect to a database called ITCHY you have enter this name in the Host String box.
The host string matches an entry in a file called TNSNAMES.ORA, which lists the database's IP address (or the machine name) and database instance name.
)
Figure
2.9 SQL*Plus Windows graphical user interface log on dialog box.
Essentially, the TNSNAMES.ORA file is a file containing a list of databases with their respective technical connection information. Your database administrator can help you with the configuration and setup of this file if you have a remote database setup.
Following is an excerpt of a TNSNAMES.ORA file. The entries in your file will obviously vary. If you supply the host string ITCHY at log in, SQL*Plus will look up the ITCHY entry in the TNSNAMES.ORA file. The HOST entry shows the IP address (if you use a TCP/IP network), which is listed as 169.254.147.245. Alternatively, you can enter the machine name. The SID entry identifies the name of the Oracle instance; here the instance is called ORCL. (When you install Oracle with the default options, you will be asked to supply such an instance name [SID]. A common default name is ORCL.)
ITCHY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(Host = 169.254.147.245)
(Port = 1521)
)
)
(CONNECT_DATA = (SID = ORCL)
)
)
SCRATCHY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = milly.columbia.edu)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = scraty.columbia.edu)
)
)
Your TNSNAMES.ORA file may contain an entry called default. If you do not supply a Host String in the Log On dialog box, you will be connected to the database listed under the DEFAULT option. Note, depending on your individual setup, you may at times need to specify or omit the .WORLD suffix next to the host name (such as ITCHY.WORLD or simply ITCHY) in the TNSNAMES.ORA file. Additionally, Oracle 9i and 10g installations allow the use the format of the second entry called SCRATCHY. It uses a service name instead of the SID.
If you are using an Oracle 10g client such as SQL*Plus for Windows, you can use a new feature called easyconnect. It allows you to make a connection without the entry being present in the TNSNAMES.ORA file. For example, you can connect to SCRATCHY by using this connect identifier in the Host String box: milly.columbia.edu:1521/scraty.columbia.edu. It lists the machine name called MILLY.COLUMBIA.EDU followed by the port number (the default port of the Oracle database is typically 1521), followed by the service name SCRATY.COLUMBIA.EDU.
Common log-on Problems
Although we cannot possibly list all the errors and solutions to all log-on problems, here are two very common Oracle error messages.
A TNS error usually deals with the connectivity between the server and the client. The following message is displayed if the connect identifier could not be resolved. This may be due to an invalid host string. Check the values and retry.
ORA-12154: TNS: could not resolve the connect identifier specified
The next error occurs if you entered the wrong password or user name when the Oracle server attempted to authenticate you as a valid user. Double-check the spelling of your user name, which is STUDENT, and password, which is LEARN. (If you cannot log on with this ID and password, check the readme.txt file regarding the installation of the STUDENT schema.)
ORA-01017: invalid username/password; logon denied
Exiting from SQL*Plus or isql*Plus
There are a number of ways to exit SQL*Plus. You can type EXIT or select Exit from the File menu in the SQL*Plus Windows version. For iSQL*Plus, you click the Logout icon rather than typing EXIT as this will free up system resources. Exiting ends the session and the STUDENT user is no longer connected to the database. However, there may be other client machines connected to the Oracle database; the server software continues to run, regardless of whether a client is connected to it.
CREATING THE STUDENT SCHEMA
Now that you know how to log on to the Oracle database using SQL*Plus or iSQL*Plus, this is a good time to read the readme.txt file you downloaded from the Web site located at http://authors.phptr.com/rischert3e and create the STUDENT schema if you have not already done so.
Unless specifically mentioned, we will not differentiate between SQL*Plus and iSQL*Plus commands because many are almost identical. For a list of unsupported commands see Appendix C, "SQL*Plus Command Reference."
All commands in SQL*Plus require the user to press the Enter key to execute them. In iSQL*Plus you always need to press the Execute button. The reminder to press the Enter key or the Execute button will not be included in the rest of the examples and exercises in this book.
Lab 2.1 Exercises
2.1.1 Identify Oracle's Client/Server Software
-
Identify which piece of Oracle software is the client, which is the server, and how they communicate with each other.
-
What is the role of SQL between client and server?
2.1.2 Login and Logout of SQL*Plus
-
Once you have logged into SQL*Plus (not iSQL*Plus) with the user ID STUDENT and password LEARN, what information does the SQL*Plus screen show you? (If you do not have access to SQL*Plus, please answer the question by referring to Figure 2.5.)
-
What do you learn when you type DESCRIBE student and press Enter? If you use iSQL*Plus, click the Execute button instead of pressing Enter.
-
Execute the following command and describe what you see: SHOW ALL.
Lab 2.1 Exercise Answers
2.1.1 Answers
-
Identify which piece of Oracle software is the client, which is the server, and how they communicate with each other.
-
What is the role of SQL between client and server?
Answer: SQL*Plus or the browser displaying iSQL*Plus is the client and the Oracle RDBMS is the server. In an Oracle 9i or 10g environment, Oracle Net is the protocol that facilitates the communications.
Answer: SQL commands are issued from the client, telling the server to perform specific actions. The server sends back the results of those instructions to the client software, where they are displayed.
2.1.2 Answers
-
Once you have logged into SQL*Plus (not iSQL*Plus) with the user ID STUDENT and password LEARN, what information does the SQL*Plus screen show you? (If you do not have access to SQL*Plus, please answer the question by referring to Figure 2.5.)
Answer: The screen shows which version of SQL*Plus you are using, the current date and time, Oracle copyright information, and the version of the Oracle database you are connected to. After this information is displayed, you see the SQL> command prompt. At this prompt you are able to enter commands.
PL/SQL is another Oracle language addressed in a separate book in this series Oracle PL/SQL by Example by Benjamin Rosenzweig and Elena Silvestrova (Prentice Hall, 2004).
- What do you learn when you type DESCRIBE student and press Enter? If you use iSQL*Plus, click the Execute button instead of pressing Enter.
Answer: You find out about the structure of the student table, specifically its column names, whether those columns allow nulls and the datatype of each column.
To write SQL statements, you need to know a table's column names and their datatypes. The SQL*Plus DESCRIBE command displays this information and shows if a column does not allow null values.
Many SQL*Plus commands may be abbreviated. For instance, DESCRIBE may be shortened to DESC. Retype the command using this abbreviation and compare the results. Figure 2.10 displays the result of the DESCRIBE command executed in SQL*Plus.
)
Figure
2.10 Executing the SQL*Plus DESCRIBE command.
SQL*Plus is not case sensitive; the user ID, password, and SQL*Plus commands may all be entered in either upper or lowercase, or a combination of the two. Throughout this book, they are in uppercase for easy identification. In the next lab you will learn about formatting your SQL statements and when it is appropriate to capitalize words.
Common Datatypes
Every column in Oracle must have a datatype, which determines what type of data can be stored.
DATE
The DATE datatype is used to store date and time information. By default the display format for a date is DD-MON-YY. For example, July 4, 2003 displays as 04-JUL-03. There are a number of functions you can use to change the display format or to show the time, which you will learn about in "Chapter 4, Date and Conversion Functions."
NUMBER
Columns with the datatype NUMBER only allow numerical data; no text, hyphens, or dashes are allowed. A column defined as NUMBER(5,2) can have a maximum of three digits before the decimal point and two digits after the decimal point. The first digit (5) is called the precision; the second digit (2) is referred to as the scale. The smallest allowed number is –999.99 and the largest is 999.99. A column definition with a zero scale such as NUMBER(5) or NUMBER(5,0) allows integers in the range from –99,999 to 99,999.
VARCHAR2 and CHAR
The VARCHAR2 and CHAR datatypes store alphanumeric data (e.g., text, numbers, special characters, etc.). VARCHAR2 is the variable length datatype and the most commonly used alphanumeric datatype; its maximum size is 4,000 characters. The main difference between VARCHAR2 and CHAR is that the CHAR datatype is a fixed-length datatype and any unused room is blank padded with spaces.
For example, a column defined as CHAR(10) and containing the four-character length value of JOHN in a row will have six blank characters padded at the end to make the total length 10 spaces. (If the column is stored in a VARCHAR2(10) column instead, it stores four characters only.) A CHAR column can store up to 2,000 characters.
The CLOB database allows you to store large amounts of textual data. It replaces the LONG datatype, which is desupported in Oracle 10g.
OTHER
Datatypes such as BFILE or BLOB require access through specific purpose functions in very highly specialized ways that go beyond the objectives of this book. In addition to the datatypes mentioned, Oracle also has additional datatypes to support specific national character sets (e.g., NCLOB, NVARCHAR2), intermedia datatypes, and spatial (geographic) data. Oracle also gives you the ability to create your own customized object datatypes.
Refer to Appendix I, "Oracle Datatypes," for a detailed listing of the various datatypes. For most SQL operations, you typically use the NUMBER, VARCHAR2, and various DATE-related datatypes. They are the most commonly used datatypes where the vast majority of data is stored.
-
Execute the following command and describe what you see: SHOW ALL.
Answer: You will see a list of SQL*Plus environmental variables and their current settings. Using the SET command, many of them can be changed to suit your needs for a SQL*Plus session, which is defined as the time in between when you log in and log out of SQL*Plus. When you start your next SQL*Plus session, however, all commands will be set back to their defaults.
It is important to note here that SQL*Plus commands, such as SHOW and DESCRIBE, are not part of the SQL language. You will begin to type SQL commands using the SQL*Plus tool in the next lab.
If you use iSQL*Plus, you can change the environment variables and settings by clicking the Preferences icon.
Lab 2.1 Self-Review Questions
In order to test your progress, you should be able to answer the following questions.
-
The DESC command displays column names of a table.
-
True
-
False
-
Anyone can connect to an Oracle database as long as he or she has the SQL*Plus software.
-
True
-
False
- The SQL*Plus command SHOW USER displays your login name.
-
True
-
False
-
Typing SHOW RELEASE at the prompt displays the version number of SQL*Plus you are using.
-
True
-
False
The COST column of the COURSE table is defined as NUMBER(9,2). The maximum cost of an individual course is 9,999,999.99.
-
True
-
False
Answers appear in Appendix A, Section 2.1.