I recommend that students read the supplemental materials provided here after you complete working with Chapter 4 in textbook. The supplemental reading provides either reinforcing materials or materials not clearly available in the text.

 

SQL Defines End Results

Structured query language (SQL, pronounced either as three individual letters, or as "sequel") provides the basic means to communicate with a database. The American National Standard Institute (ANSI) has defined and standardized SQL as the standard language for relational database management system. Most database vendors including Oracle and Microsoft (SQL Server) offer their database products supporting the minimum ANSI standard. In other words, Oracle SQL conforms most of ANSI language specifications, in addition to other functions or features defined by Oracle.

SQL is the easiest computer language designed to closely emulate plain English. It is a "functional" language, which means you specify the things you want to see happen or results you want to have. Common procedural languages, such as C++ and JAVA, explicitly define the process or method in order to achieve certain results. In contrast, SQL simply defines the end result, leaving it up to database server to determine the method by which the data is obtained.

We will use one example to illustrate the important point presented above. If we like to retrieve employee name (ename) and job title (job) from data table EMP, we can simply tell the database sever what want. You may login Oracle SQL Plus using the following setting (see Chapter Prerequisites on page 68):

Username: Password: Host String:

scott tiger oracle

After you login Oracle SQL Plus, you may use the following SQL command to display data on your screen.

SQL> 2

SELECT ename, job FROM EMP;

You will see the following results immediately displayed on your computer screen.

ENAME ---------- SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER	JOB --------- CLERK SALESMAN SALESMAN MANAGER SALESMAN MANAGER MANAGER ANALYST PRESIDENT SALESMAN CLERK CLERK ANALYST CLERK
14 rows selected.

For those of you who are familiar with other procedural language, you can testify that you no longer need to define library, open data input file, read the file row-by-row till the end of file, print the lines on the screen, and finally close the file. All those detailed work is performed by the database server (technically known as parse and execution).

In summary, SQL defines the end results whereas other language defines the process. We can see that SQL can help non-programmers to get what they want in much easier way than many procedural languages.

SQL Plus is a pure Oracle product, while SQL is more universal among other database products.

 

SQL Query Syntax

The basic syntax for a SQL query from a single table is:

SELECT column1, column2, ...
FROM ownername.tablename
WHERE condition(s)
GROUP BY column
HAVING condition (used with GROUP BY)
ORDER BY column1, column2, ...

SQL commands including column names are not case sensitive. However, it is a good idea to follow the conventions in the textbook for case. This convention will help future server performance tuning and improvement.

If you login as a user other than SCOTT and you like to access a table owned by SCOTT, you will need to specify the ownername. For example, the table EMP will be scott.emp.

If you only want to display employees whose salary is higher than $1,500, you may specify the search condition as SAL>1500.

SQL> 1 2

SELECT ename, job FROM scott.emp WHERE sal>1500

The results will be as follows.

ENAME ---------- ALLEN JONES BLAKE CLARK SCOTT KING FORD	JOB --------- SALESMAN MANAGER MANAGER MANAGER ANALYST PRESIDENT ANALYST
7 rows selected.

Only those employees whose salary are higher than $1500 are selected at this time.

 

UNION

A UNION query combines two unrelated queries into a single output result. For example, a college database has a table for students and another data table for faculty. We can display names and phone numbers of students as follows:

SQL> 2

SELECT s_last, s_first, s_phone FROM student;

The following information will be displayed.

S_LAST ---------------- Miller Umato Black Mobley Sanchez Connoly	S_FIRST ------------------ Sarah Brian Daniel Amanda Ruben Michael	S_PHONE ---------- 7155559876 7155552345 7155553907 7155556902 7155558899 7155554944
6 rows selected.

 

We may do the same search for faculty as well.

 

SQL> 2

SELECT f_last, f_first, f_phone FROM faculty;

 

F_LAST --------------- Cox Blanchard Williams Sheng Brown

F_FIRST --------------- Kim John Jerry Laura Philip

F_PHONE ---------- 7155551234 7155559087 7155555412 7155556409 7155556082

 

Now, we like to list names and phone of all students and faculty in one output. We may use the following UNION query to achieve the objective:

SQL> 2 3 4 5

SELECT s_last, s_first, s_phone FROM student UNION SELECT f_last, f_first, f_phone FROM faculty;

Please note that there is no semicolon (;) after the first query. The two queries are connected by UNION. The following results will be displayed.

S_LAST -------------------- Black Blanchard Brown Connoly Cox Miller Mobley Sanchez Sheng Umato Williams	S_FIRST ------------------------- Daniel John Philip Michael Kim Sarah Amanda Ruben Laura Brian Jerry	S_PHONE ---------- 7155553907 7155559087 7155556082 7155554944 7155551234 7155559876 7155556902 7155558899 7155556409 7155552345 7155555412
11 rows selected.

If you like to practice the above example, you may download the file to your hard drive and run the SQL script in the same way as stated in Chapter prerequisites on page 68. To download, click here.

There are other UNION operators listed in the following table.

Function	Description
UNION	Combines the results of two queries and then eliminates duplicate rows.
UNION ALL	Combines the results of two queries without eliminating duplicate rows.
MINUS	Takes the rows returned by one query and eliminates those that are also returned by another query.
INTERSECT	Takes the results from two queries and returns only rows that appear in both.

The above functions are similar to logic operations.

No wonder the teacher knows so much; she has the book.

--Edgar Watson Howe

 

Mother: "What did you learn in school today, Cathy?"

Cathy: "Not much. I have to go back tomorrow."