Page 95
Week 1
Day 5
Introduction to
Structured Query
Language (SQL)
To develop an Oracle database application, it is essential that you develop a working knowledge of Structured Query Language (SQL). SQL is a powerful language that differs from traditional third-generation languages (3GLs), such as C and Pascal, in several significant ways:
- SQL is a nonprocedural language. You use SQL to tell Oracle what data to retrieve or modify without telling it how to do its job.
- SQL does not provide any flow-of-control programming constructs, function definitions, do-loops, or if-then-else statements. However, as you will see on Day 9, "Programming an Oracle Database with PL/SQL," Oracle provides procedural language extensions to SQL through a product called PL/SQL.
Page 96
- SQL provides a fixed set of datatypes; you cannot define new datatypes. Unlike modern programming languages that enable you to define a datatype for a specific purpose, SQL forces you to choose from a set of predefined datatypes when you create or modify a column. In the future, though, you can expect Oracle to release an object-oriented database that will enable you to define datatypes specific to the application you're building.
One similarity between SQL and a traditional programming language is that both usually give you more than one way to accomplish the same goal—particularly when retrieving information. Various SQL statements may achieve the same results (but differ in efficiency or clarity).
Retrieving and Modifying Data
At the highest level, SQL statements can be broadly categorized as follows into three types:
- Data Manipulation Language (DML), which retrieves or modifies data
- Data Definition Language (DDL), which defines the structure of the data
- Data Control Language (DCL), which defines the privileges granted to database users
The category of DML contains four basic statements:
- SELECT, which retrieves rows from a table
- INSERT, which adds rows to a table
- UPDATE, which modifies existing rows in a table
- DELETE, which removes rows from a table
These statements are used most often by application developers. DDL and DCL statements are commonly used by a database designer and database administrator for establishing the database structures used by an application.
SQL Grammar
Here are some grammatical requirements to keep in mind when you're working with SQL.
- Every SQL statement is terminated by a semicolon.
- An SQL statement can be entered on one line or split across several lines for clarity. Most of the examples in this book split statements into readable portions.
- SQL isn't case sensitive; you can mix uppercase and lowercase when referencing SQL keywords (such as SELECT and INSERT), table names, and column names.
Page 97
However, case does matter when referring to the contents of a column; if you ask for all customers whose last names begin with a and all customer names are stored in uppercase, you won't retrieve any rows at all.
| NOTE |
Most of the examples in this chapter were produced with SQL*Plus. Of course, you can use whatever tool you prefer to enter SQL statements—for instance, SQL*Plus, SQL Worksheet, or Discoverer/2000. For the sake of brevity, I have removed the repeated column headings that SQL*Plus produces. |
Syntax of the SELECT Statement
Of the four DML statements, the SELECT statement is the one that is executed most often in a real application because records are usually read more often than they are changed. A SELECT statement also can exist as a subquery in an UPDATE, INSERT, or DELETE statement, but that topic is discussed on Day 6, "Using SQL to Modify Data."
The SELECT statement is a tremendously powerful tool, and its syntax is complicated because of the many ways that tables, columns, functions, and operators can be combined into legal statements. Therefore, instead of looking at the full syntax of the SELECT statement, this section starts with some basic examples of the SELECT statement.
At a minimum, a SELECT statement contains the following two elements:
- The select list, a list of columns or expressions to be retrieved
- The FROM clause, the table from which to retrieve the rows
A Simple SELECT Statement
| NOTE |
If you want to work through the examples in this lesson, follow the directions in this book's Introduction that describe the installation of the sample database. |
A simple SELECT statement—a query that retrieves only the Student ID from the Student table—is shown in Listing 5.1.
Page 98
Listing 5.1. An initial SELECT statement.
SQL> select Student_ID 2 from Student; STUDENT_ID -------------------- 10231300 10231301 10231302 10231305 10231306 10231308 10231309 10231322 10231314 10231315 10231317 10231319 10231320 10231325 10231326 10231328 10231329 10231327 10231330 10231316 10231303 10231310 10231318 10231324 10231304 10231312 10231321 10231313 10231311 10231323 10231307 31 rows selected.
If you want to retrieve both the Student ID and the student's last name, simply list the columns in the desired order, as shown in Listing 5.2.
Page 99
Listing 5.2. Retrieving multiple columns.
SQL> select Student_ID, Last_Name 2 from Student; STUDENT_ID LAST_NAME -------------------- ------------------------ 10231300 SMYTHE 10231301 HAN 10231302 GORDON 10231305 REYNOLDS 10231306 PARKER 10231308 TANAKA 10231309 COEN 10231322 NEWTON 10231314 PURCELL 10231315 JACKEL 10231317 ROSEN 10231319 MALLARD 10231320 GUSSEY 10231325 MICHAELS 10231326 DEURRE 10231328 WONG 10231329 ABBOT 10231327 POSEN 10231330 CLAUSEN 10231316 GOMEZ 10231303 MASSEY 10231310 FERGUSON 10231318 BING 10231324 ANASTATIA 10231304 CHIN 10231312 HOLMES 10231321 PLOWCHARD 10231313 JACKSON 10231311 FERNANDEZ 10231323 PINKWATER 10231307 MABEISI 31 rows selected.
If you want to retrieve all columns in a table, you can use an SQL shortcut (the *), as shown in Figure 5.1.
Page 100
Figure 5.1.
Selecting all the
columns in a table
with SQL*Plus.
)
The Select List
If the select list contains multiple columns, the columns must be separated by commas. The select list also can contain valid expressions that may or may not contain columns. In addition, you can use a column more than once in a select list. The query in Listing 5.3 is completely valid.
Listing 5.3. Retrieving a column more than once.
SQL> select Student_ID, Student_ID 2 from Student; STUDENT_ID STUDENT_ID -------------------- -------------------- 10231300 10231300 10231301 10231301 10231302 10231302 10231305 10231305 10231306 10231306 10231308 10231308 10231309 10231309 10231322 10231322 10231314 10231314 10231315 10231315 10231317 10231317 10231319 10231319 10231320 10231320