TEC 5323 Advanced Database Technology


Supplemental Reading of Chapter 4 (Part 2)

 

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 or object-oriented 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.

 

Off the Subject:

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."

Class Index

School of Technology
College of Business & Applied Sciences
Eastern Illinois University