Oracle SQL: The Basics
- Lab 2.1 The SQL Execution Environment
- Lab 2.2 The Anatomy of a SELECT Statement
- Lab 2.3 An Introduction to SQL*Plus
This chapter is from the book
This chapter is from the book
This chapter is from the book
Now that you are familiar with the concepts of databases and schema diagrams, you are ready to start with hands-on exercises. In this chapter, you will learn the basics of the SQL language and use SQL Developer and SQL*Plus, two Oracle provided software tools that allow you to execute statements against the Oracle database.
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 Execution Environment
This lab provides you with an understanding of the basic SQL language commands. You will learn how to establish connectivity to the database server and begin executing SQL commands.
Oracle software runs on many different operating systems and hardware environments. The machine on which the Oracle database software resides is called the Oracle database server. A variety of tools are available to access data from the database server. In this chapter, you will be introduced to two Oracle-provided tools: SQL Developer and SQL*Plus.
The most striking difference between SQL Developer and SQL*Plus is the interface. SQL*Plus has an arcane command-line interface with old-style editing and display options.
SQL Developer is a recent addition to the Oracle tool set. It is included in the latest Oracle releases or can be downloaded free from Oracle's Web site. SQL Developer's graphical user interface greatly simplifies SQL statement execution and overall database access.
Because SQL Developer is a much easier environment to use than SQL*Plus, you start with this execution environment first to learn the basics of the SQL language.
In subsequent labs, you will explore some of the basics of SQL*Plus. While SQL*Plus seems quite outdated, you cannot ignore decades of SQL*Plus usage. It has been part of Oracle since its early beginnings and will continue to be shipped with every installation on every platform. You will find it useful to have some rudimentary knowledge of SQL*Plus. Therefore, this book describes both tools.
However, the focus of this book is learning the SQL language; the tool you use is simply the environment within which to execute the SQL language commands. Therefore, not all details of these tools are covered. Furthermore, you may also consider one of the many third-party tools available to execute your statements. No matter what tool becomes your favorite, it is beneficial to know both SQL Developer and SQL*Plus as they are found with almost every Oracle installation.
For a list of the many easy-to-use third-party tools, see Appendix H, "Resources."
Accessing the Oracle Database Server
You can access the Oracle server through various front-end tools. This lab teaches you some of the basics of SQL Developer first. This tool is Oracle's newest database query and development tool. It is also by far easier to learn and use than SQL*Plus, which is covered in Lab 2.3, "An Introduction to SQL*Plus."
The differences between SQL Developer and SQL*Plus are pointed out to you as you work through the book. You can assume, with very few exceptions, that the functionality is very similar, if not identical. You might want to use SQL Developer for execution of your SQL statements in this book because it is a more user-friendly tool than SQL*Plus for a beginning SQL user.
Getting Started with SQL Developer
Oracle SQL Developer provides a convenient way to perform essential database tasks. The tool enhances productivity and simplifies database development tasks by providing a graphical interface for executing SQL statements, browsing, and creating and updating database objects. SQL Developer connects to any Oracle database, version 9.2.0.1 and later. You can also create database connections for non-Oracle databases.
If your Oracle software installation did not come with the tool already installed, you can download the latest version of SQL Developer from Oracle's Web site. Oracle does not charge a license fee for SQL Developer. This tool is written in Java, thus providing a uniform interface across the Windows, Linux, and MAC OS X platforms. Furthermore, SQL Developer's default database connection uses a thin Java Database Connectivity (JDBC) driver, so there is no requirement for a full Oracle client software installation involving Oracle Net. This simplifies the configuration and minimizes the footprint. With a quick unzip and execution of the file, the installation is a breeze.
SQL and the Oracle Database Server
In the midst of all this software lies the SQL language. SQL commands are sent from SQL Developer, also known as the client, or front end, to the server, or back end. These commands send instructions to the server to tell the server what services to provide. The server responds by returning a result to the client, which then displays the output. Figure 2.1 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 returned to the front end, which then formats and displays the output, as appropriate.
)
Figure 2.1 SQL and the Oracle database server
You may run SQL Developer and your database on the same machine. Typically, this is the case when you install both the Oracle database server and SQL Developer on your individual computer.
The client, whether SQL Developer or SQL*Plus, sends SQL statements to the server, and the server responds with the result set. The job of the database server involves listening and managing many clients' requests, because there are often multiple client machines involved.
The means of communication is established either via the Oracle Net software, a JDBC driver, or an ODBC driver.
Creating a Database Connection for SQL Developer
Before you can send your first SQL statement to the database, you need to create a connection to the database server. A connection consists of a username, password, and connection string or hostname. This connection authenticates you to log in to the Oracle database.
When you first evoke SQL Developer, you a see screen similar to Figure 2.2. The screen is divided into several panes. The left pane, labeled Connections, allows for a list of database connections.
)
Figure 2.2 The Connections window in SQL Developer
The name of the displayed connection is local. This database connection refers to a database installed on the same machine as SQL Developer. You can rename this connection by right-clicking the connection name, choosing Rename Connection, and providing a new name.
To create a new database connection, right-click the Connections node and choose New Connection (see Figure 2.3).
)
Figure 2.3 Creating a new database connection
You can add a new connection name, such as StudentConnection, and assign a username and password. For the purposes of the examples in this book, use the username student and the password learn.
Starting with Oracle 11g, the password is case-sensitive by default.
If you have not yet created the STUDENT schema (also referred to as the STUDENT user account) according to the instructions on the companion Web site, you will not be able to log in. Before you perform the lab exercises, you might want to finish reading this lab first, visit the Web site located at www.oraclesqlbyexample.com, and then create the STUDENT schema.
Choose as the Role option default. The Connection Type should be Basic, which uses the thin JDBC driver to connect; this is probably the simplest option. If you choose TNS, an entry is required in the TNSNAMES.ORA file, and the Oracle Net client must be installed. In Lab 2.3, we will discuss the TNSNAMES.ORA file as part of that lab's SQL*Plus connectivity topics.
Additional connection information consists of the name of the host (also called the machine name), the default port where the database will listen for connection requests (usually 1521), and either the Service name or the System ID (SID, to identify a particular database on the machine). Here the default name for the SID is orcl.
The Test button allows you determine whether this connection works. A "Success" status message appears above the Help button if the connection is successful. If your test is unsuccessful, you have probably chosen an incorrect hostname and/or SID.
The hostname is the machine name or the IP address of the machine on which the database resides. In Figure 2.4, the database is installed on the host machine called localhost. If your database resides on a computer different from the one on which you're running SQL Developer, the name of the machine on which the Oracle server is installed should be entered here.
)
Figure 2.4 New / Select Database Connection dialog box
When you click the Save button, you see the connection name added to the Connections window, as shown in Figure 2.5.
)
Figure 2.5 List of connections
When you double-click the connection name, you are connected to the database, using the appropriate user account and password. If you did not check the Save Password box when you created StudentConnection, you are prompted for it each time you open the connection.
For the majority of the exercises in this book, you will use the StudentConnection.
You can modify the connection information by right-clicking on the Student Connection node and choosing Properties from the context menu.
Exploring Database Table Objects
When you expand the StudentConnection node by clicking the plus sign, you see a list of database objects available to this user (see Figure 2.6). This pane, called the Connections navigator, is a tree-based object browser.
)
Figure 2.6 Column definition of the COURSE table
If you right-click on a node within the Connections navigator, a context-sensitive menu appears. For each object type, the menu varies, presenting you with choices to create, alter, drop, or manage the various objects. We will discuss the different object menus in detail in the chapters related to each object type.
For now, we will focus on the table objects. When you double-click an individual table node, you see various tabs displayed that provide details about the table.
COLUMNS TAB
The Columns tab displays a list of the columns, together with each column's data type. You can see whether the column allows null values, the primary key definition, and any column comments. In the Primary Key column, the value 1 indicates that this is the first column in the primary key. For the COURSE table, you can see in Figure 2.6 that the primary key consists of one column: COURSE_NO.
DATA TAB
A click on the Data tab displays the data stored in the table. This tab also contains functionality to modify the data. You will learn how to make changes to data in Chapter 11, "Insert, Update, and Delete."
CONSTRAINTS TAB
The Constraints tab is useful for determining foreign key relationships of the table with other tables and for showing the validation constraints that exists. Chapter 12, "Create, Alter, and Drop Tables" explains these topics in detail.
GRANTS TAB
The Grants tab provides details about who has access privileges to the object; this is discussed in Chapter 15, "Security."
STATISTICS TAB
The Statistics tab shows columns and table statistics, such as the number of rows, the number of distinct values for each column, and so on. The Oracle database uses these values to optimize the execution of SQL statements. Chapter 18, "SQL Optimization," expands on how these statistics affect performance.
TRIGGERS, DEPENDENCIES, INDEXES, FLASHBACK, AND SQL TABS
The Triggers, Dependencies, Indexes, and SQL tabs are discussed in Chapter 12 as well as Chapter 13, "Indexes, Sequences, and Views." You will find out what triggers are associated with a table and the event on which the trigger fires. Any indexes that are created for the tables display in the Index tab. The Dependencies tab shows any views or objects that are dependent on this table. The Flashback tab allows you to see the previous data values before a data manipulation occurred at a specific time in the past; this will be discussed in Chapter 11. The SQL Tab shows the SQL to re-create the DDL for the table and its associated objects.
DETAILS TAB
The Details tab lists various details of a table, such as the date and time the table was created, the last date and time statistics were collected, and so on. You will learn more about this in Chapter 14, "The Data Dictionary, Scripting, and Reporting."
Reviewing the various tabs for a table allows you to glance at the important characteristics of a table. To explore another table, you double-click that table's node to replace the COURSE table's information with the new table's information. If you do not want to replace the display, you can click the red Push Pin icon to freeze the display.
The SQL Developer Worksheet
Aside from clicking the Data tab, another way to display data is by using the SQL language. The command to retrieve rows is the SELECT command. You enter SQL statements into the SQL Worksheet. The easiest way to open a worksheet is by clicking the SQL Worksheet icon in the toolbar, as shown in Figure 2.7.
)
Figure 2.7 Open SQL Worksheet icon
Another way to open the worksheet is to right-click the connection name and choose Open SQL Worksheet, or you can choose Tools from the top menu bar and then SQL Worksheet.
The Connection dialog box (see Figure 2.8) allows you to select the database connection for this worksheet. The plus sign brings up the dialog box to create a new connection, and the Pencil icon facilitates editing of an existing database connection.
)
Figure 2.8 Select Connection dialog box
As you become more familiar with SQL Developer, you will find that there are many ways to perform the same action, using different menu choices. In addition to the menu on the top of the screen, there are context-sensitive menus and icons for frequently performed tasks.
When a connection is selected, the SQL Worksheet tab description shows the name of the connection on the top. You can execute SQL statements using the StudentConnection by entering a command in the SQL Worksheet.
You can open multiple worksheets within SQL Developer by clicking the Open SQL Worksheet icon again. Each additional worksheet can hold different SQL statements and result sets. The worksheet tab will display the unique name of the connection on the top. For example, in Figure 2.9, a second worksheet for this connection is shown as StudentConnection~1.
)
Figure 2.9 Multiple worksheets and their respective connections
The StudentConnection and StudentConnection~1 worksheets share the same database session. A session is an individual connection to the Oracle database server, which starts as soon as the user is logged in and authenticated. The session ends when the user disconnects or exits. Chapter 11 provides a more detailed discussion on sessions and their effect on the read consistency and locking of data during data manipulations.
Another tab, such as the TeacherConnection tab in Figure 2.9, represents another connection that may use a different database and/or login name.
Figure 2.10 shows the different panes within SQL Developer. You are already familiar with the Connections navigator on the left of the screen. As a separate tab next to it, you see the Reports navigator, which contains many supplied data dictionary reports, as discussed in Chapter 14.
)
Figure 2.10 The various SQL Developer panes
The result of your SQL statement execution displays in the Results window, which shows the effect of the SQL statement execution. You can see a number of tabs, and SQL Developer displays most of your statement results in the Results tab. The Script Output tab shows the result of a script run (a collection of SQL statements). The Explain and the Autotrace tabs show the execution plan of a SQL statement and give an indication of how efficiently Oracle may execute your command; we discuss these tabs in Chapter 18. The DBMS Output and OWA (Oracle Web Agent) Output tabs are relevant if you execute PL/SQL statements. (See the companion book Oracle PL/SQL by Example, 4th edition, by Benjamin Rosenzweig and Elena Silvestrova Rakhimov; Prentice Hall, 2008.)
The Connections list on the right of the SQL Worksheet allows you to switch to another connection for the current worksheet. You can execute the same statement against a different connection by choosing the connection name from the Connections list drop-down menu.
Below the Results window, you may see tabs such as Messages, Logging Page, and Data Editor. Depending on the action you are taking, you see feedback information in these tabs. You will see examples of messages in the Data Editor when you manipulate data via SQL Developer in Chapter 11.
ENTERING A SQL STATEMENT
You enter a SQL statement in the SQL Worksheet window. The following SELECT statement retrieves all the columns and rows from the COURSE table.
SELECT *
FROM course
To execute the command, you click the green triangle. When your mouse hovers over the triangle, a ToolTip displays a description and alternative F9 function (see Figure 2.11).
)
Figure 2.11 The Execute Statement icon
THE RESULTS TAB
The Results tab (see Figure 2.12) displays the data of the COURSE table. The left side of the Results tab shows an ordered listing of numbers, which represent the order of the rows in the Results window. These row numbers are not part of the database table; they are only for display within this window. On top of the Results tab are the column names from the COURSE table. You can scroll to the right to see any additional columns and scroll down to all the rows. You can adjust the width of individual columns and drag the column order around without having to change the SQL statement.
)
Figure 2.12 The SQL statement and corresponding result
On the bottom left of the screen, the status indicates how many records the statement returned to SQL Developer. If the bar is not visible, you can display it by choosing View, Status Bar.
Commonly Used Data Types
As you saw on the Data tab in SQL Developer, every column in Oracle has a data type, which determines what type of data can be stored. You need to know about the data types in order to use some of the comparison operators discussed in the next chapter.
DATE
The DATE data type stores date and time information. Depending on your setup, the default display format for a date may be DD-MON-YY. For example, July 4, 2009, displays as 04-JUL-09. There are a number of functions you can use to change the display format or to show the time. You also have menu options in SQL Developer for customizing the display. You will learn more about these topics in Chapter 5, "Date and Conversion Functions."
NUMBER
Columns with the data type NUMBER allow only numeric data; no text, hyphens, or dashes are permitted. 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 data types store alphanumeric data (for example, text, numbers, special characters). VARCHAR2 is the variable-length data type and the most commonly used alphanumeric data type; its maximum size is 4,000 characters. The main difference between VARCHAR2 and CHAR is that the CHAR data type is a fixed-length data type, and any unused room is blank padded with spaces.
For example, a column defined as CHAR(10) and containing the four-character-length value 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.
If you want to store data containing more than 4,000 characters, you need to consider the CLOB data type, which allows you to store large amounts of textual data. It replaces the formerly used LONG data type, which is supported only for backward compatibility.
OTHER DATA TYPES
The data types BLOB and BFILE are binary data types that deal with access to multimedia content such as movies, images, or music. The main difference between these two data types is how the data is stored within the Oracle database. The BLOB data type stores the content inside the Oracle database, whereas the BFILE data type stores only a reference to the file location directory and the file name.
In order to access the binary content of the data, you need to use highly specific functions that go beyond the objectives of this book. In addition to the data types mentioned, Oracle provides data types to support specific national character sets (for example, NCLOB, NVARCHAR2), intermedia (image, audio, video) data types, and spatial (geographic) data. Oracle also gives you the ability to create your own customized object data types.
Refer to Appendix I, "Oracle Data Types," for a detailed list of the various data types. For most SQL operations, you typically use the NUMBER, VARCHAR2, and various DATE-related data types. They are the most commonly used data types, where the vast majority of data is stored.
Now that you know how to log on to the Oracle database, this is a good time to read the readme file you downloaded from the Web site located at www.oraclesqlbyexample.com and create the STUDENT schema if you have not already done so.
Lab 2.1 Exercises
-
How does the Oracle server communicate with the client?
-
In SQL Developer, expand the Tables node below the StudentConnection to reveal the different tables available to the STUDENT user. Double-click the INSTRUCTOR table. Then double-click the GRADE table. Is the information regarding the INSTRUCTOR table still visible?
-
What happens when you type DESCRIBE student in the SQL Worksheet pane and then click the Execute Statement icon?
Lab 2.1 Exercise Answers
-
How does the Oracle server communicate with the client?
ANSWER: SQL Developer and SQL*Plus are examples of client programs, and the Oracle database is the server. Various protocols, such as Oracle Net and JDBC, facilitate communication between the server and the client.
The client issues SQL commands, telling the server to perform specific actions. The server sends back the results of those instructions to the client software, where they are displayed.
-
In SQL Developer, expand the Tables node below StudentConnection to reveal the different tables available to the STUDENT user. Double-click the INSTRUCTOR table. Then double-click the GRADE table. Is the information regarding the INSTRUCTOR table still visible?
ANSWER: The GRADE table information replaces the INSTRUCTOR tab. A click on the Push Pin icon (see Figure 2.13) keeps the object's information displayed.
Figure 2.13 The Column tab icons
The icon next to the Push Pin is the Edit icon. Clicking the Edit icon allows you to modify the table's column definitions, add and modify constraints, and so on. You will learn about these options in Chapter 12, which explores the different choices and their effects on the entry and storage of the data.
Next to the Edit icon is the Refresh icon, which re-queries the database for the latest updates on the given object. The Actions menu provides additional options to modify the table and column properties.
-
What happens when you type DESCRIBE student in the SQL Worksheet pane and then click the Execute Statement icon?
ANSWER: The DESCRIBE command displays the structure of the STUDENT table, listing the columns, data types, and null allowed characteristics. The result of the command displays in the Scripts Output tab, not the Results tab (see Figure 2.14).
Figure 2.14 The DESCRIBE command
The DESCRIBE command is actually a SQL*Plus command, not a command in the SQL language. It lets you quickly show the structure of a table. SQL Developer accepts and executes many of the SQL*Plus commands.
Because this is a SQL*Plus command, the Script Output tab, not the Results tab, shows the output. The Scripts Output tab displays the result in a similar fixed-character fashion to SQL*Plus. You also get results in this tab if you click the Run Script icon (F5); this functionality tries to emulate SQL*Plus as much as possible.
Compared to the SQL*Plus DESCRIBE command, SQL Developer's Columns tab provides significantly more detailed information at once. Another way to display the Columns tab is by using the SQL Developer's Popup Describe menu option. You access the Popup Describe menu option by placing your cursor on a table in the SQL Worksheet and then right-click for the context menu (see Figure 2.15).
Figure 2.15 The Popup Describe menu option
)
)
)
Lab 2.1 Quiz
In order to test your progress, you should be able to answer the following questions.
-
Anyone can connect to an Oracle database, as long as he or she has the SQL Developer or SQL*Plus software.
_______
a) True
_______
b) False
-
When you establish a connection using SQL Developer, the hostname is the machine name or IP address where the database resides.
_______
a) True
_______
b) False
-
SQL*Plus is available with every version of Oracle.
_______
a) True
_______
b) False
-
More than one user can be connected to a database at the same time.
_______
a) True
_______
b) 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.
_______
a) True
_______
b) False
-
You can store at most 4,000 characters in a VARCHAR2 column.
_______
a) True
_______
b) False
ANSWERS APPEAR IN APPENDIX A.