Relational database theory, generally credited to E. F. Codd, provides a
complete and mathematically coherent way of viewing and manipulating data.
This section contains a brief summary of important concepts. It is
necessary to understand these points in order to appreciate what is
"behind" SQL. You do need to read the material in the text as well.
A relational database may be viewed as a collection of named tables
(referred to as relations), consisting of rows (referred to as tuples) and
columns (referred to as attributes). Rows are identified by data content.
Columns are identified by names.
Within a given table, no two columns may have the same name. Within a given
table, all data contained within a given column is of the same type. A
particular column might contain product identifiers, for example. Two
different tables may contain columns with the same name. Column name
qualified by table name will still be unique.
Within a given table, no two rows may contain the same data. SQL systems
generally allow this requirement to be violated and have been criticized
for this. The only way to tell one row from another is by specifying the
values for one or more columns. Thus, if two rows do contain the same data,
they cannot be identified or manipulated individually.
The column or set of columns that can be used to differentiate one row from
another is referred to as the key. One column may serve as an adequate key.
Or it may take several. It may be necessary to view the combination of all
columns as key. A table containing duplicate rows really has no key. It is
possible for a table to contain more than one potential key. These are
called candidate keys.
Relationships between tables are implemented solely by data values. Two
rows from two different tables, each containing identical values for one or
more specified columns will be considered related. If a column or set of
columns of table A contains values referring to the key of table B, those
columns in table A are referred to as a foreign key. Significant
relationships are usually maintained by foreign keys.
Rows of a table are generally not stored in any particular sequence. Often
they will appear in a sequence reflecting the order of entry. Relational
database management systems often provide mechanisms for forcing the data
into a particular sequence. This is usually accomplished through indexing.
But,it is not a component of relational theory.
Relational algebra and relational calculus provide a mathematically
complete collection of operations for manipulating relations. The
differences between relational algebra and relational calculus are not
important to us in the context of this course. Both provide the same set of
operations: select, project, join, product, union, intersection,
difference, division. Both are based on mathematical set theory and treat
tables or relations as mathematical sets. SQL is sometimes criticized for
deviating from this theory.
NULL values are another distinguishing concept. NULL means there is no
value. NULL is not the same as zero or blank.
In this course we will emphasize the practical side of manipulating
relational data using SQL. We will make reference to relational theory when
it is appropriate.
For more detailed discussions of relational theory, see:
Relational Database Design: A Practitioner's Guide
Charles J. Wertz
CRC Press 1993
An Introduction to Database Systems, Volume I, 6th Edition
C. J. Date
Addison-Wesley 1995
The Relational Model for Database Management
E. F. Codd
Addison-Wesley 1990
Since I created these under my oracle user name, it is necessary for you to
qualify the table names with wertzcj in order to access them. (Use
WERTZCJ.SHR instead of SHR.) You can eliminate this necessity in one of two
ways.
You can create SYNONYMS under your user ID. See the discussion of
SYNONYM in another part of these notes. Because I am such a swell guy,
I have provided a script file, DISK$FACULTY:[WERTZCJ.STUDENT]SYN.SQL
that will automatically create these for you. See the discussion of
RUNNING A SAVED STATEMENT elsewhere in these notes. You run this once
only. If you try to create a synonym that already exists, the result
will be an error message to the effect that an object by that name
already exists. Once you've created the synonyms, you can use the
unqualified table names.
You could also create the tables under your own user ID. You will have
to do this for tables you intend to update. I will provide more
information about this when the time comes.
Here comes a big discussion about the preceding. There are four files
that you can access,
DISK$FACULTY:[WERTZCJ.STUDENT]STUDENT.SQL,
DISK$FACULTY:[WERTZCJ.STUDENT]MOVIES.SQL,
DISK$FACULTY:[WERTZCJ.STUDENT]EMP.SQL, and
DISK$FACULTY:[WERTZCJ.STUDENT]WATSON.SQL.
You can print them, copy them, or examine them on line.
These contain all the sql statements to create the various tables. You
should examine the content of these, especially WATSON.SQL to see how
the oracle statements differ from those in the book.
You could use the oracle start command to execute these scripts. If
you would do this, you would then have your own personal copies of all
the tables. This is different from using the synonyms to access my
copies. For the purpose of doing queries, you don't really need to do
this. Be aware, that if you have created the synonyms as described
above, you won't be able to create these tables. You'll have to drop
the appropriate synonyms first. Also note the the final statements in
each script grant access to public on these tables. If you choose to
create your own tables, you probably want to omit these statements
from your copy of the script.
Later on, we will do some labs that will involve tables of your own.
We'll deal with that when the time comes.
NOTE: When you perform an ORACLE query, the column name is usually used
as the default heading. However, these names are often truncated
to match the width of the data in the column. Do not be misled by
this. The column names shown in the following section are the
correct column names. These are the ones you must use unless you
have created aliases or synonyms for the column names.
Employee History
SQL> describe employee
Name Null? Type
------------------------------- -------- ----
EMPNO NOT NULL CHAR(3)
NAME NOT NULL CHAR(10)
JOB NOT NULL CHAR(4)
SALARY NOT NULL NUMBER(5)
COMM NUMBER(5)
DEPTNO CHAR(2)
SEX NOT NULL CHAR(1)
Each row of this table represents one employee. Empno is a unique
identifier. Name is the employees name. Job is a reference to the jobs
table. Employee compensation is a combination of salary and
commission. If an employee is eligible for commission but has received
none, comm will be zero. If an employee is not eligible for
commission, comm will be NULL. Deptno is a reference to the department
table. Sex is 'M' or 'F'.
SQL> describe department
Name Null? Type
------------------------------- -------- ----
DEPTNO NOT NULL CHAR(2)
DEPTNAME NOT NULL CHAR(15)
LOC CHAR(20)
MGR CHAR(3)
EXP_BUDG NUMBER(7)
REV_BUDG NUMBER(7)
Each row of this table represents a department. Deptno is a unique
identifier. Deptname is the department name. Loc is department
location. Mgr is a reference to the employee table identifying the
manager of the department. Exp_budg and prev_budget are current and
previous expense budgets in dollars.
SQL> describe jobs
Name Null? Type
------------------------------- -------- ----
JOB NOT NULL CHAR(4)
JOBNAME NOT NULL CHAR(15)
MINSALARY NOT NULL NUMBER(5)
MAXSALARY NOT NULL NUMBER(5)
MGRFLAG NOT NULL CHAR(1)
Each row of this table represents a job type. Job is a unique
identifier. Jobname describes the job. Minsalary and maxsalary
represent minimum and maximum salaries for the job type in dollars.
Mgrflag is "Y" if the job is considered a managerial position.
SQL> describe emplhist
Name Null? Type
------------------------------- -------- ----
EMPNO NOT NULL CHAR(3)
SEQ NOT NULL NUMBER(3)
DATE_BEG NOT NULL DATE
DATE_END DATE
SALARY NOT NULL NUMBER(5)
FRJOB NOT NULL CHAR(4)
TOJOB NOT NULL CHAR(4)
PROMO NOT NULL CHAR(1)
FRDEPT CHAR(2)
TODEPT NOT NULL CHAR(2)
Each row of this table represents one line of an employees history
with the company. A new item is added whenever salary, job, or
department change. Empno is a reference to the employee table. Seq is
a sequence number. Empno + seq serve as a unique row identifier.
Date_beg and date_end reflect the time interval covered by a
particular row. Salary reflects the employee's salary for the time
interval covered. This may or may not be the same as the salary for
the previous assignment. Frjob and tojob represent "from" and "to" job
types. If these are the same, there was no change from the previous
assignment. For a new employee, the "from job" is always "Newh" for
new hire. Promo is "Y" if the assignment change is considered a
promotion, "N" if it is not. Frdept and todept represent "from" and
"to" department identifiers. If these are the same, there was no
change from the previous assignment. In the case of a new hire, frdept
is NULL.
Movies and Students
SQL> describe movie
Name Null? Type
------------------------------- -------- ----
MOVID NOT NULL CHAR(4)
TITLE NOT NULL CHAR(36)
CAT CHAR(2)
RATING CHAR(4)
PURDATE DATE
PURCOST NUMBER(5,2)
STATUS CHAR(1)
Each row of this table represents a movie available for rental. Movid
is a unique identifier. Title is the title of the movie. Cat is a
category code. COmedy, ACtion, Children's, and HOrror are valid
categories. Rating will be "G", "PG", "PG13", "R", or "X". Purdate is
the date the store purchased the movie. Purcost is the price paid.
Status will be "R" for rented, "I" for in store, or "D" for damaged.
SQL> describe rentdetl
Name Null? Type
------------------------------- -------- ----
INVNBR NOT NULL NUMBER
MOVID NOT NULL CHAR(4)
CHARGE NOT NULL NUMBER(6,2)
Each row of this table represents one rental occurrence. Invnbr is a
reference to the renthead table. Movid is a reference to the movie
table. Invnbr + renthead form a unique identifier. Charge is the
charge for the rental.
SQL> describe renthead
Name Null? Type
------------------------------- -------- ----
INVNBR NOT NULL NUMBER
MEMID NOT NULL CHAR(3)
RENTDATE DATE
DUEDATE DATE
RETRNDTE DATE
Each row of this table is a rental "header" representing one customer
visit or transaction. Invnbr is a unique identifier. Memid is a
reference to the member table. Rentdate is the date the movie(s) were
rented. Duedate is the date they are to be returned. Retrndte is the
actual return date.
SQL> describe member
Name Null? Type
------------------------------- -------- ----
MEMID NOT NULL CHAR(3)
MEMNAME NOT NULL CHAR(10)
ADDRESS CHAR(15)
CTYST CHAR(15)
MEMDATE DATE
MEMTYPE CHAR(1)
Each row of this table represents a member or customer. Memid is a
unique identifier. Memname is member name. Address and Ctyst are
member address. Memdate is the date the member registered. Memtype is
Annual or Life.
SQL> describe student
Name Null? Type
------------------------------- -------- ----
SID NOT NULL CHAR(3)
SNAME NOT NULL CHAR(10)
SEX CHAR(1)
MAJOR CHAR(3)
GPA NUMBER(4,2)
Each row of this table represents a student. Sid is a unique
identifier. Sname is the student's name. Sex is "M" or "F". Major
indicates the student's program of study. "FIN", "MGT", "MKT" and
"CIS" are valid majors. GPA is the student's grade point average.
SQL> describe crsenrl
Name Null? Type
------------------------------- -------- ----
CRSNBR NOT NULL CHAR(6)
SID NOT NULL CHAR(3)
GRADE CHAR(1)
Each row of this table represents a course enrollment. Crsnbr is a
reference to the course table. Sid is a reference to the student
table. Crsnbr + sid is a unique identifier. Grade reflects the grade
earned.
SQL> describe course
Name Null? Type
------------------------------- -------- ----
CRSNBR NOT NULL CHAR(6)
CNAME NOT NULL CHAR(20)
CREDIT CHAR(1)
MAXENRL NUMBER
FID CHAR(3)
Each row of this table represents a course. Crsnbr is a unique
identifier. Cname is the course name. Credit is the number of credit
hours. Maxenrl is the maximum enrollment for the course. Fid is a
reference to the faculty table, identifying the instructor for the
course.
SQL> describe faculty
Name Null? Type
------------------------------- -------- ----
FID NOT NULL CHAR(3)
FNAME NOT NULL CHAR(10)
EXT CHAR(3)
DEPT CHAR(3)
RANK CHAR(4)
SALARY NUMBER
Each row of this table represents a faculty member. Fid is a unique
identifier. Fname is the faculty member's name. Ext is the telephone
extension. Dept is the faculty member's department. "FIN", "MGT",
"MKT" and "CIS" are valid departments. Rank indicates the faculty
member's rank and may be "INST", "ASST", "ASSO", or "FULL". Salary is
the faculty member's salary.
3.0 SYNTAX and REFERENCE MATERIAL
This section contains a concise discussion of the SQL language as
implemented within the ORACLE database management system.
You can and should read the material in the text and rely on the
explanations and illustrations. You should read the text first. It
will provide a good overview.
BUT, in any case where there is a discrepancy between these notes and
the text, rely on the notes. The notes are specifically geared to the
ORACLE system we will use in this course. The text is geared to a more
general audience. There are some differences between one vendor's SQL
and another's.
In addition, there are some materials on reserve in the library. See the
course outline for details.
You will be wise to review NOTATION, CONNECTING TO ORACLE, ENTERING
COMMANDS, RUNNING A COMMAND, SAVING AN SQL STATEMENT, RUNNING A SAVED
STATEMENT, and SPOOLING (and PRINTING) RESULTS before you try to do
anything.
3.1 NOTATION
Throughout most of this document, upper case is used to identify SQL
keywords and lower case is used to identify names or conditions entered by
the user.
In SELECT ord_date
FROM cust_ord
WHERE ord_num = '2345678901';
SELECT, FROM, and WHERE are SQL keywords, ord_date and ord_num are
user-specified column names, and cust_ord is a user-specified table
name.
Square brackets ( "[" and "]" ) are used to identify optional items. Do not
include the brackets when you enter a query.
In SELECT [DISTINCT] prod_id
FROM ord_item;
the optional keyword DISTINCT may be added to obtain a desired result
but its omission will not result in a syntax error.
A vertical bar ( "|" ) is used to indicate a choice and underlining is used
to indicate a default.
In SELECT [DISTINCT | ALL] prod_id
FROM ord_item;
the options are DISTINCT and ALL, one or the other may be chosen, and
the default of ALL will be used if neither is specified.
Ellipses ( "..." ) are used to specify items that may repeat.
In SELECT column_name, ...
FROM table_name;
any number of valid column names may be specified.
3.2 CONNECTING TO ORACLE
Log on to Oracle
Type $ORACLE_STUD
This sets up a "logical" connecting you to the correct "instance" of
oracle.
AFTER A BRIEF PAUSE, YOU WILL SEE THE '$' PROMPT AGAIN.
IT MAY SEEM LIKE NOTHING AT ALL HAS HAPPENED. BUT, IN FACT, A LOT HAS BEEN
DONE TO SET UP A LINKAGE WHICH ALLOWS YOU TO CONNECT OR LOG ON TO ORACLE.
YOU HAVE TO DO THIS EVERY TIME YOU LOG ON (UNLESS YOU PUT THIS COMMAND
IN YOUR LOGIN.COM FILE). BUT, AS LONG AS YOU REMAIN LOGGED ON TO THE
ALPHA, YOU DO NOT HAVE TO REPEAT ORACLE_STUD NO MATTER HOW MANY TIMES
YOU ENTER AND EXIT FROM SQLPLUS.
Should you encounter an error at this point there are a couple
possibilities.
It may be that there is a system problem. It will be necessary to
notify either me, Paul Reynolds, or the folks in TR208 if this is the
case.
THERE IS ONE THING THAT YOU CAN CHECK FIRST. ORACLE_STUD IS ACTUALLY
AN ELABORATE .COM FILE WHICH EXECUTES NUMEROUS DCL COMMANDS. IF YOU
HAVE REDEFINED ONE OF THE DCL COMMANDS, IT WILL CAUSE ORACLE_STUD TO
FAIL. IN THE PAST, A NUMBER OF STUDENTS HAVE USED A LOGIN.COM WHICH
REDEFINED THE SYMBOL "ON". THESE STUDENTS DID ENCOUNTER A PROBLEM
TRYING TO USE ORACLE. THE SOLUTION IS TO ALTER THE SYMBOL NAME IN YOUR
LOGIN.COM. (Sometimes students have LOGIN.COM files that were created
for them by someone else and do not really know what is in them. If
there is a LOGIN.COM file on your account, you should make sure you
know what it does.)
Next, type $SQLPLUS
The system should ask for your user identifier and password.
Normally, your identifier is the same as your VAX user identifier.
Your initial password is P followed by the FIRST FOUR digits of your
student identification number. YOUR ORACLE PASSWORD HAS NO DIRECT
CONNECTION TO YOUR VAX PASSWORD.
If you like, you can type $SQLPLUS {userid}/{password}
on a single line.
(Don't actually type the "{" or the "}". They are just used as a
notation to indicate that you fill in YOUR user identifier and
password.)
You should see some messages then the ORACLE prompt 'SQL>'
3.3 CHANGING YOUR PASSWORD
If you want to alter your password, type
SQL>ALTER USER {userid}
2>IDENTIFIED BY {newpassword};
It's probably a good idea to keep your ORACLE password and your VMS
password the same! (ORACLE PASSWORDS MUST START WITH A LETTER.) But there
is no particular connection between the two passwords. Changing one has no
effect on the other.
3.4 ENTERING COMMANDS
The material in this section is specific to ORACLE.
Once you have logged on to ORACLE, you should be able to enter any standard
SQL command. ORACLE expects a command to end with a semi-colon ";". If you
hit enter or return without the ";", ORACLE will give you another line
number.
Here is a sample: SQL>SELECT *
2 FROM WERTZCJ.STUDENT;
If you forget the ";", you get one blank line then ORACLE assumes you are
finished and returns you to the "SQL>" prompt. You can then either type RUN
or EDIT.
SQL>SELECT *
2 FROM WERTZCJ.STUDENT
3
SQL>
3.5 RUNNING A COMMAND
The material in this section is specific to ORACLE.
Once you terminate your SQL command, ORACLE will attempt to run the
command. After the command has been run (or has failed due to some error),
it is retained in a current command BUFFER. You can rerun the command in
the buffer by typing RUN. You can type either the abbreviation R or RUN.
The system will list the statement, then try to run it.
You can also run a command by typing a slash ( "/" ). In this case, the
system will not list the command before it is run.
3.6 EDITING A COMMAND
The material in this section is specific to ORACLE.
If you type EDIT the system editor will be invoked. You can use the editor
to make your changes. Then when you EXIT the editor you will be back to
ORACLE.
In the case of a VAX or ALPHA computer running VMS, this may be either
EDT or EVE depending on the options specified for the system and any
options you may have specified in your login.com file. If you use the
EDT editor, create a file named EDTINI.EDT in the directory you will
use for doing your SQL work. Place the one line command "C" (for
change) in this directory. This will automatically place you in "full
screen mode". Use Z to exit full screen mode, then type EXIT to
exit the editor and save your changes. If you use the EVE editor, you
do not need the EDTINI.EDT file. Pres DO then type EXIT to exit the
editor and save your changes.
You will find that invoking the editor in this manner creates a file named
AFIEDT.BUF in your current directory. You can delete this file if you like.
(The ORACLE system also provides a simple LINE oriented editor. No
details are given here because this is a relatively primitive editor.)
You can also type EDIT xyz.SQL at the ORACLE prompt. This will invoke the
editor using the file xyz.SQL instead of the statement in the BUFFER. When
the work is saved, it is saved in the file and the contents of the BUFFER
are not altered.
3.7 SAVING AN SQL STATEMENT
The material in this section is specific to ORACLE.
SAVE somename saves the current content of your current buffer in a file
named somename.SQL. SAVE may be abbreviated as SAV.
You can specify a different extension if you like.
SAVE xyx CREATE calls for the creation of a new file and results in an
error if the file already exists. CREATE may be abbreviated as CRE.
CREATE is the default SAVE option.
SAVE xyz REPLACE is used to overwrite an existing file. REPLACE may be
abbreviated as REP.
SAVE xyz APPEND adds to the end of an existing file. APPEND may be
abbreviated as APP.
IMPORTANT NOTE: When you edit the current SQL statement in the BUFFER,
this updates the buffer and the file AFIEDT.BUF. It
does not save the changes in the .SQL file. (You could
do this by invoking additional editor commands.)
3.8 RUNNING A SAVED STATEMENT
The material in this section is specific to ORACLE.
START somename retrieves and executes the statements in a file named
somename.SQL. START may be abbreviated as STA.
You can specify a different extension if you like.
This can be a file which you created via the SAVE command described
above. Or it can be created by using an editor.
The file can contain just about any valid SQL statement terminated by
";". An SQL statement in the file can also be terminated by
placing "/" as the ONLY CHARACTER on the NEXT LINE.
It can also contain a sequence of SQL statements, each terminated by
";" or "/".
IF the last statement in the file is terminated by ";" it may be run
twice.
The SET ECHO command controls whether or not the START command will list
the SQL before running it.
SET ECHO ON to cause all subsequent SQL statements to be listed
ahead of the result.
SET ECHO OFF to return to the default (SQL not listed) state.
The SQL statement is now in the buffer and may be edited if you like. Don't
forget to SAVE it again if you change it.
GET xyz will load a file named xyz.SQL into the BUFFER but will not run it.
GET LIST is the default. The content of the file will be listed as it
is loaded. LIST may be abbreviated as LIS.
GET NOLIST will suppress the listing. NOLIST may be abbreviated as
NOL.
This is similar to working on a program. You have to think about what you
are doing and make sure you save the right version of your SQL statement.
It's also a good idea to give each .SQL file a meaningful name such as
PROBLEM1.
3.9 SPOOLING (and PRINTING) RESULTS
The material in this section is specific to ORACLE.
SPOOL somename causes the responses to any queries which follow to be
written to a file named somename.LIS. SPOOL may be abbreviated as SPO.
You can specify a different extension if you like.
You can then print the file by any means you choose.
(On the ALPHA computer at SUCB, you might by type $PRINT/QUEUE=VPNWING
somename.LIS PRINT AT THE VMS $ PROMPT.)
SPOOL OFF stops the spooling.
3.10 SYNTAX OPERATORS
The material in this section is specific to ORACLE. Most of it is common to
all SQL systems. But you may want to check documentation before you try to
work with any other system.
A lot of this will make more sense if you look at it in the context of a
specific command or example. You might review it somewhat quickly
beforehand and then return to it as you work on specific examples.
() Surrounds a subquery
' Surrounds a CHAR or DATE constant
Examples 'ABC', '29-JAN-90', 'O''Brien'
" Surrounds a column name or alias that contains
special characters
3.11 SQL VALUE OPERATORS
These are listed in order of precedence.
() Overrides normal operator precedence
+ - Prefix sign for number expression
* / Multiplication & division
+ - Addition & Subtraction
|| Concatenation
3.12 SQL LOGICAL OPERATORS
These are listed in order of precedence.
() Overrides normal operator precedence
= Test for equality
!=, ^=, <> Test for inequality
> Greater than
>= Greater than or equal to
< Less than
<= Less than or equal to
IN Equal to a member of a set.
Example JOB IN
('CLERK', 'ANALYST')
returns TRUE if JOB is either 'CLERK' or 'ANALYST'
Equivalent to = ANY.
Example SAL IN
(SELECT SAL FROM EMP
WHERE DEPTNO=30)
NOT IN Not equal to any member of a set.
The opposite of IN.
Equivalent to <> ALL.
NOT IN evaluates to FALSE if any member of the set
is NULL.
ANY Compares to each value in a set.
SOME Example SAL = ANY
(SELECT SAL FROM EMP
WHERE ...)
This will evaluate to FALSE if the SUBQUERY
returns no rows.
NOTE: SAL =
(SELECT SAL FROM EMP
WHERE ....)
will cause an error if the SUBQUERY returns
multiple rows.
ALL Compares to every value in a set.
Example
SAL > ALL (SELECT SAL FROM ...)
This will evaluate to TRUE if the SUBQUERY returns
no rows.
NOTE: SAL >
(SELECT SAL FROM EMP
WHERE ....)
will cause an error if the SUBQUERY returns
multiple rows.
NOTE: SAL > ANY
(SELECT SAL FROM EMP
WHERE ....)
is TRUE if SAL is greater than the least value
returned.
SAL > ALL
(SELECT SAL FROM EMP
WHERE ....)
is TRUE if SAL is greater than the greatest value
returned.
BETWEEN >= first value and <= second value
AND
Example WHERE A BETWEEN 2 AND 5
is TRUE if the value of
A is 2, 3, 4, or 5.
WHERE A NOT BETWEEN 2 AND 5
is TRUE for any other value of A.
EXISTS True if a subquery returned at least one row
LIKE Pattern matching - use % for a string wildcard, use _
for a character wildcard
% a string of zero or more characters
_ a string of one character
All whose names begin with "S"
WHERE NAME LIKE 'S%'
All whose names end with "K"
WHERE NAME LIKE '%K'
All with four letter names that begin with "W"
WHERE NAME LIKE 'W___'
All whose titles do NOT begin with "SALES"
WHERE JOB NOT LIKE 'SALES%'
In name LIKE '%A\_B%' ESCAPE '\' the keyword ESCAPE identifies
the character that follows, ("_") in this case, as an ESCAPE
character. The appearance of the ESCAPE character in the pattern
causes the character that follows to be interpreted literally. In
the example given here, the search is for 'A_B'. If the ESCAPE
were not used, the underscore would be interpreted as a single
character wild card.
EXAMPLE
SQL>SELECT *
2>FROM PRODUCT
3>WHERE PROD_DES LIKE '%A\_B%' ESCAPE '/';
IS NULL Test for NULL
IS NOT NULL
NOTE: A = NULL doesn't do the same thing!
NOT Reverse a condition
AND A AND B is TRUE IF BOTH ARE TRUE
OR A OR B is TRUE IF EITHER IS TRUE
3.13 COMPARISON RULES
3. 14 BLANK-PADDED AND NON-PADDED SEMANTICS.
When two items of type CHAR are compared, ORACLE uses blank-
padded comparison semantics. Text literals such as 'Hi There' are
considered to be of type CHAR. This means that two items are
equal if they differ only in the number of trailing blanks.
Here, 'a ' is equal to 'a'.
If one or both of the compared items are of type VARCHAR2, ORACLE
uses non-padded comparison semantics. This means that a longer
value is greater than a shorter value even if the only difference
is that the longer value has trailing blanks.
Here, 'a ' is greater than 'a'.
SQL> describe varchartest
Name Null? Type
------------------------------- -------- ----
ID CHAR(4)
FIXED CHAR(10)
VAR VARCHAR2(10)
SQL> select * from varchartest;
ID FIXED VAR
---- ---------- ----------
row1 aa aa
row2 aa aa
SQL> select * from varchartest
where fixed=var;
ID FIXED VAR
---- ---------- ----------
row2 aa aa
SQL> select * from varchartest
where fixed>var;
ID FIXED VAR
---- ---------- ----------
row1 aa aa
SQL> select * from varchartest
where fixed = 'aa';
ID FIXED VAR
---- ---------- ----------
row1 aa aa
row2 aa aa
SQL> select * from varchartest
where var = 'aa';
ID FIXED VAR
---- ---------- ----------
row1 aa aa
3.15 NULLS
In relational theory, if one of the two values compared is NULL,
the result is UNKNOWN, not FALSE. However, SQL treats conditions
evaluating to UNKNOWN as FALSE.
As noted above, IS NULL or IS NOT NULL must always be used to
test whether or not a specific value is NULL.
Value of A Test Result
10 A IS NULL FALSE
10 A = NULL Unknown/FALSE
10 A IS NOT NULL TRUE
10 A <> NULL Unknown/FALSE
NULL A IS NULL TRUE
NULL A = NULL Unknown/FALSE
NULL A IS NOT NULL FALSE
NULL A <> NULL Unknown/FALSE
3.16 TRUTH TABLES
A B NOT A A AND B A OR B
TRUE FALSE
FALSE TRUE
Unknown/FALSE Unknown/FALSE
TRUE TRUE TRUE TRUE
TRUE FALSE FALSE TRUE
FALSE TRUE FALSE TRUE
FALSE FALSE FALSE FALSE
TRUE Unknown/FALSE Unknown/FALSE TRUE*
FALSE Unknown/FALSE Unknown/FALSE Unknown/FALSE
* This is the way SQL evaluates this. Relational and mathematical
purists find this disturbing.
3.17 NAMES
The following is additional information about SQL and ORACLE SQL in
particular.
A NAME for a table, view, synonym, column, index, or user variable MUST
Begin with a letter.
Contain only the characters A - Z, 0 - 9, _, $, and #.
Not duplicate an ORACLE reserved word.
Not duplicate the name of another database object of the same type.
Be from 1 to 30 characters long.
(Database names may not exceed 8 characters.)
Not contain a quotation mark.
3.18 RESERVED WORDS
The following is a list of ORACLE reserved words. These must never be
used as column, table, or database names. Those words shown in italics
are also reserved words for ANSI SQL.
ACCESS ADD ALL ALTER AND ANY
AS ASC AUDIT
BETWEEN BY
CHAR CHECK CLUSTER COLUMN COMMENT COMPRESS
CONNECT CREATE CURRENT
DATE DECIMAL DEFAULT DELETE DESC DISTINCT
DROP
ELSE EXCLUSIVE EXISTS
FILE FLOAT FOR FROM
GRANT GROUP
HAVING
IDENTIFIED IMMEDIATE IN INCREMENT INDEX
INITIAL INSERT INTEGER INTERSECT INTO IS
LEVEL LIKE LOCK LONG
MAXENTENTS MINUS MODE MODIFY
NOAUDIT NOCOMPRESS NOT NOWAIT NULL
NUMBER
OF OFFLINE ON ONLINE OPTION OR
ORDER
PCTFREE PRIOR PRIVILEGES PUBLIC
RAW RENAME RESOURCE REVOKE ROW ROWID
ROWLABWL ROWNUM ROWS
SELECT SESSION SET SHARE SIZE SMALLINT
START SUCCESSFUL SYNONYM SYSDATE
TABLE THEN TO TRIGGER
UID UNION UNIQUE UPDATE USER
VALIDATE VALUES VARCHAR VARCHAR2 VIEW
WHENEVER WHERE WITH
The following are additional ANSI reserved words. Even though they may
be used as names with ORACLE version 7, they are best avoided.
AVG
BEGIN
CHARACTER CLOSE COBOL COMMIT CONTINUE
COUNT CURSOR
DEC DECLARE DOUBLE
END ESCAPE EXEC
FETCH FOREIGN FORTRAN FOUND
GO GOTO GROUPS
INDICATOR INT
KEY
LANGUAGE
MAX MIN MODULE
NUMERIC
OPEN
PASCAL PLI PRECISION PRIMARY PROCEDURE
REAL REFERENCES ROLLBACK
SCHEMA SECTION SOME SQL SQLCODE SQLERROR
SUM
WORK
3.19 ORACLE DATA TYPES
Some, but not all, of these correspond to ANSI standard SQL data types. If
you are going to work with any other relational database management system,
check the vendor's documentation to verify what data types are provided.
CHAR(size) FIXED LENGTH CHARACTER data up to 256 characters
NOTE: Upper and lower case are different. 'a' and
'A' are not the same.
I'd better clarify that. When I refer to SQL objects
(names, keywords, etc.), upper and lower case don't
matter. When I refer to data they do matter.
select * from student;
and
SELECT * FROM STUDENT;
are equivalent.
But case does matter in character data.
WHERE NAME = 'JONES'
and
WHERE NAME = 'jones'
are not the same.
VARCHAR2(size) VARIABLE LENGTH CHARACTER data up to 2,000
characters.
As noted above the comparison rules for VARCHAR2
are not the same as those for CHAR.
VARCHAR(size) Presently the same as VARCHAR2. Included for
consistency with previous product releases. Use is
discouraged.
DATE A valid date from Jan 1, 4712 BC to Dec 31, 4712
AD. Dates are in the form 'DD-MON-YY'
(eg '29-JAN-90') unless modified by special
format controls
LONG LONG columns store up to 231-1 characters. Only one
long column per table is allowed. Long columns may not
be used in subqueries, functions, expressions, WHERE
clauses or indexes. A table containing a long column
may not be clustered.
LONG RAW Similar to LONG.
NUMBER Numeric data with space for up to 38 digits, not
including decimal point and sign.
Examples 123
+123
-123
123.456
1.23E2
ORACLE has its own unique internal format for
storing numeric data. Positive or negative numbers
can range from 1.0 X 10-130 to 9.9...9 X 10126.
NUMBER(precision) NUMBER column with specified number of significant
digits. Maximum is 38 digits.
NUMBER(precision, NUMBER column of specified size with
scale) specified number of decimal places. There can be
from -84 to 127 decimal places.
RAW(size) Raw binary data "size" bytes long. Maximum is 255
bytes.
LONG RAW Raw binary data. Otherwise same as long.
"
3.20 PSEUDO COLUMNS
LEVEL level in a bill of material type explosion
ROWID returns a row's id. can be used in update...where
as well as in select... for update to ensure that
only a specific row will be updated
ROWNUM returns a number indicating the sequence in which
a row was selected ... used to limit output( for
example .. where ROWNUM < 10).
SYSDATE current date and time
UID ORACLEs internal user id
USER Name of current user
3.21 ALIAS
If you get into a lengthy query, you can save typing by specifying a table
name ALIAS in the from clause.
SELECT STU.SID, FAC.FID
FROM WERTZCJ.FACULTY FAC,
WERTZCJ.STUDENT STU,
WERTZCJ.CRSENRL CRS,
WERTZCJ.COURSE SUB
WHERE STU.SID = CRS.SID
AND CRS.CRSNBR = SUB.CRSNBR
AND FAC.FID = SUB.FID;
is equivalent to
SELECT WERTZCJ.STUDENT.SID, WERTZCJ.FACULTY.FID
FROM WERTZCJ.FACULTY,
WERTZCJ.STUDENT,
WERTZCJ.CRSENRL,
WERTZCJ.COURSE
WHERE WERTZCJ.STUDENT.SID = WERTZCJ.CRSENRL.SID
AND WERTZCJ.CRSENRL.CRSNBR = WERTZCJ.COURSE.CRSNBR
AND WERTZCJ.FACULTY.FID = WERTZCJ.COURSE.FID;
This can save you a lot of typing.
An ALIAS specified in an SQL statement only has meaning within that SQL
statement. In other words, you must repeat the ALIAS in every SQL
statement.
3.22 SYNONYM
Or you can create SYNONYMs for yourself.
EXAMPLE: CREATE SYNONYM STUDENT FOR WERTZCJ.STUDENT;
This creates a permanent SYNONYM entry for you in the ORACLE dictionary. It
becomes a part of your ORACLE account. You do not have to repeat the
SYNONYM definition again. Synonyms which match the actual table names will
make it possible for you to enter statements just as they appear here.
You can eliminate a SYNONYM by DROPping it.
EXAMPLE: DROP SYNONYM STUDENT;
4.0 SELECTING ROWS AND COLUMNS (I)
4.1 OVERALL SYNTAX
The material in this section is specific to ORACLE. Most of it is common to
all SQL systems. But you may want to check documentation before you try to
work with any other system.
SELECT ...
FROM ...
[ WHERE ... ];
or
SELECT [DISTINCT] schema.table.column [column alias]
[, ...]
FROM schema.table [alias]
[ WHERE condition ... ];
NOTES: Column names may be qualified by table names as in
ord_item.ord_num. This means the ord_num column of the ord_item
table.
Table names may be qualified by a user identifier, schema name,
or database name as in wertzcj.ord_item for the ord_item table in
the wertzcj database.
Table names require qualification when the table has been created
under another user identifier or as part of a specific database.
When this is the case, the use of an ALIAS or SYNONYM can save a
lot of typing.
Column names must be qualified when multiple tables are
referenced in a single query and an unqualified name would be
ambiguous; that is when two tables contain a column with the same
name.
Column aliases are used to specify alternate names for use in the
heading which precedes the result of a query.
4.2 SELECTING ALL COLUMNS
SELECT *
FROM table;
EXAMPLE: SELECT *
FROM cust_ord;
This will list all data from the cust_ord table.
4.3 SELECTING A SPECIFIC COLUMN
SELECT column
FROM table;
EXAMPLE: SELECT prod_id
FROM product;
This will list the prod_id for every row in the product
table.
4.4 TO AVOID DUPLICATES
SELECT DISTINCT column
FROM table;
EXAMPLE: SELECT DISTINCT prod_id
from ord_item;
This will list the prod_id values contained in the ord_item
table. Each prod_id will be listed but once. If DISTINCT
were omitted, each prod_id of each order would be listed.
This would probably result in duplication of prod_id.
NOTE: In relational algebra and relational calculus, the operation
that extracts a subset of columns from a table or relation
is called project. (That's "pro ject" with a long "oh".)
Since the result of any operation in relational algebra as a
new relation and relations do not contain duplicate rows,
DISTINCT is necessary to assure a project if that is what is
desired.
NOTE: The elimination of duplicates requires the system to do
additional work. If you are absolutely positive that no
duplicates will result, you may consider omitting DISTINCT.
Example: Finding all job types that are actually in use by selecting jobs
that appear in the employee table. DISTINCT is used to avoid
duplicates.
SQL> select distinct job
2 from employee
3 /
JOB
----
Anlt
Clrk
Drvr
Mngr
Slsm
Spvr
4.5. SELECTING MULTIPLE COLUMNS
You may, of course, select multiple columns.
SELECT column_a, column_b, ...
FROM table;
EXAMPLE SELECT prod_id, prod_des
FROM product;
Column headings might be modified in the following manner
SELECT prod_id PRODUCT, prod_des DESCRIPTION
FROM product;
The Column aliases PRODUCT and DESCRIPTION will be used in
the output.
4.6 SELECTING ROWS
If you don't specify WHERE, all rows will be selected.
Use WHERE to choose specific rows.
SELECT column(s)
FROM table
WHERE condition;
NOTE: In relational algebra and relational calculus, the operation
that selects a subset of rows for a relation or table is
referred to as select. There is some possibility for
confusion here since this is not the same as the sql verb
select. Some authors, Date for example, have suggested that
it might be best call the relational algebra and calculus
operation restrict to avoid this confusion.
Refer to SQL LOGICAL OPERATORS, COMPARISON RULES, NULLS, TRUTH TABLES.
AND is evaluated before OR
WHERE DEPTNO = 30
AND JOB = 'SALESMAN'
OR SAL > 2000
refers to salesmen in department 30 or any employee who makes
over $2,000.00.
USE "(" ")" TO CLARIFY
EXAMPLES
SQL> select name, salary, sex
2 from employee
3 where sex = 'M'
4 order by name;
NAME SALARY S
---------- ---------- -
Di Salvo 2700 M
McDonnel 1625 M
Ramirez 3650 M
Reed 3500 M
Simpson 825 M
Turner 1800 M
Watson 4500 M
Wilson 1700 M
8 rows selected.
Note that the column name SEX is truncated to S because the data
is only one column wide.
SQL> select name, salary, sex
2 from employee
3 where sex = 'F'
4 order by name;
NAME SALARY S
---------- ---------- -
Allen 3800 F
Chen 2900 F
Schwartz 4250 F
Smith 2500 F
SQL> select empno, date_beg
2 from emplhist
3 where seq = '1'
4 and date_beg < '01-APR-81'
5 /
EMP DATE_BEG
--- ---------
101 01-MAR-81
109 01-APR-80
SQL> select empno, name, deptno
2 from employee
3 where deptno in ('10', '20', '30')
4 and sex = 'M'
5 /
EMP NAME DE
--- ---------- --
100 Wilson 10
103 Reed 30
105 Watson 30
The IN ('10', '20', '30') is equivalent to
deptno = '10' or deptno = '20' or deptno = '30'
SQL> select job, mgrflag, maxsalary
2 from jobs
3 where mgrflag = 'Y'
4 or maxsalary >= 3500
5 /
JOB M MAXSALARY
---- - ----------
Mngr Y 5500
Amgr Y 3000
Slsm N 5000
Spvr Y 2000
Anlt N 3500
5.0 SELECTING ROWS AND COLUMNS (II)
5.1 LIKE - Examples
SQL> select name
2 from employee
3 where name like 'A%'
4 or name like 'B%'
5 or name like 'C%'
6 or name like 'D%'
7 /
NAME
----------
Allen
Chen
Di Salvo
Observe that only names beginning with capital A, B, C, or D are
selected.
SQL> select name
2 from employee
3 where name like '%A%'
4 or name like '%B%'
5 or name like '%C%'
6 or name like '%D%'
7 or name like '%a%'
8 or name like '%b%'
9 or name like '%c%'
10 or name like '%d%'
11 /
NAME
----------
Reed
Watson
Allen
Chen
Ramirez
McDonnel
Di Salvo
Schwartz
8 rows selected.
Notice that both upper and lower case letters are tested and
the letter can appear anywhere in the name.
5.2 IS NULL - EXAMPLE
SQL> select *
2 from emplhist
3 where frdept is null
4 /
EMP SEQ DATE_BEG DATE_END SALARY FRJO TOJO P FR TO
--- ---------- --------- --------- ---------- ---- ---- - -- --
101 1 01-MAR-81 30-JUN-81 800 Newh Slsa N 40
109 1 01-APR-80 30-SEP-80 735 Newh Slsa N 40
220 1 01-MAY-82 31-AUG-82 1900 Newh Mngr N 60
Note that in this particular database, frdept is null in the case
of the employee's first job assignment. Also note that some
column names have been truncated.
5.3 ORDERING ROWS IN A SEQUENCE
In the relational model, rows have no particular sequence. Unless special
indexing techniques are employed, most relational database systems will
store the data according to the order in which it was received into the
database. The specific technique used to evaluate a query may cause the
result to appear in a particular order. This may vary from product to
product and even from one release to another of the same product.
As a result, IT IS OFTEN NECESSARY TO SPECIFY THE ORDERING OF QUERY
RESULTS.
SELECT column(s)
FROM table
ORDER BY column ASC DESC;
The column may be specified by its name or by its position in the
output list.
ASC, for ascending is the default. DESC for descending may also be
specified.
EXAMPLES
SQL> select empno, name, salary
2 from employee
3 order by salary desc
4 /
EMP NAME SALARY
--- ---------- ----------
105 Watson 4500
220 Schwartz 4250
109 Allen 3800
210 Ramirez 3650
103 Reed 3500
200 Chen 2900
215 Di Salvo 2700
101 Smith 2500
110 Turner 1800
100 Wilson 1700
213 McDonnel 1625
EMP NAME SALARY
--- ---------- ----------
214 Simpson 825
12 rows selected.
SQL> select empno, name, salary
2 from employee
3 order by 3 desc
4 /
SQL> select job, jobname, minsalary
2 from jobs
3 order by minsalary asc
4 /
JOB JOBNAME MINSALARY
---- --------------- ----------
Newh New Hire 500
Slsm Salesperson 750
Secy Secretary 800
Clrk Clerk 950
Slsa Sales Asst 950
Drvr Driver 1050
Anlt Analyst 1300
Amgr Asst Manager 1500
Spvr Supervisor 1500
Mngr Manager 2500
SELECT column(s)
FROM table
ORDER BY column_1, column_2, ...;
Sort first by column_1.
Sort by column_2 values within column_1 groups.
If DISTINCT is specified, the system may sort the result to find the
duplicates and ORDER BY may not be necessary. However, since different
database management systems and even different releases of the same
database management system may utilize different techniques it is really
necessary to specify ORDER BY if consistent results are desired.
5.4 DATE ARITHMETIC AND FUNCTIONS
The material in this section is specific to ORACLE. Other relational
database management systems may provide similar functions and abilities but
this is not well standardized across products.
The ORACLE DATE datatype incorporates CENTURY, YEAR, MONTH, DAY, HOUR,
MINUTE, and SECOND. ORACLE stores MONTH, DAY, and YEAR as a JULIAN DATE
representing the number of days since January 1, 4712 BC.
The usual default DATE format is DD-MON-YY. ORACLE will accept DATE
literals such as '04-JUL-94' and will display DATE information in this
format by default. When a DATE value specified in this manner is inserted,
the time component is set to the default value of 12:00:00 AM (midnight).
Alternative DATE and time formats may be specified with the TO_DATE and
TO_CHAR functions described below. Additional DATE manipulation functions
provided by ORACLE are also described below.
You can add and subtract DATES. UNITS are days. BUT since dates are
actually stored with hours, minutes, and seconds; fractions and multiples
of days may be specified.
Add two days
6-MAR-93 + 2 results in 8-MAR-93
Add two hours
6-MAR-93 + 2/24 results in 6-MAR-93 and 2 hours
Add fifteen seconds
6-MAR-93 + 15/(24*60*60)
Since months and years are not consistent numbers of days, use of the
appropriate functions is highly recommend.
The pseudo column SYSDATE represents the current DATE and time. This may be
inserted as a data value or used in a DATE comparison.
ADD_MONTHS(date,num) adds num months to date
EXAMPLE: ADD_MONTHS(ord_date,3) specifies three months from ord_date.
LAST_DAY(d) returns the last day of the month containing date d.
EXAMPLE: LAST_DAY(ord_due) returns the last day of the month
containing ord_due.
SYSDATE - LAST_DAY(SYSDATE) determines the number of days
remaining in the current month.
MONTHS_BETWEEN(date_1, date_2) returns the number of months between the
two dates.
If date_1 is later than date_2, the result is positive.
Otherwise, it is negative.
If date_1 and date_2 are both the same day of the month or
if each is the last day of a month, the result is an
integer. Otherwise, the result is a fraction. The fractional
component is always determined based on a 31 day month. The
time component is also considered in the calculation of this
fraction.
EXAMPLE: MONTHS_BETWEEN('01-JUL-94', '01-JUN-94') returns 1.
MONTHS_BETWEEN('01-JUL-94', '07-MAY-94') returns 1.806.
NEW_TIME(date, zone_a, zone_b) returns the date and time in zone-b
given that date represents the date and
time in zone_a.
Abbreviations used are:
AST,ADT Atlantic Standard, Daylight
BST,BDT Bering Standard, Daylight
CST,CDT Central Standard, Daylight
EST,EDT Eastern Standard, Daylight
GMT Greenwich Mean
HST,HDT Alaska-Hawaii Standard, Daylight
MST,MDT Mountain Standard, Daylight
NST NewFoundland Standard
PST,PDT Pacific Standard, Daylight
YST,YDT Yukon Standard, Daylight
NEXT_DAY(date, day) returns the date of the first day after date where day
represents a day of the week.
EXAMPLE: NEXT_DAY(ord_due, 'FRIDAY') finds the date of the friday
that comes after ord_due.
ROUND(date, [fmt]) returns date rounded according to the specified format.
If no format is given, date is rounded to the nearest
day.
Formats used are:
CC or SCC Century
SYYY, YYYY, YEAR, SYEAR, YYY, YY, Y Year. Year rounds up
on July 1.
IYYY, IYY, IY, I ISO Year.
Q Quarter. Quarter rounds up on the sixteenth day of the
second month of the quarter.
MONTH, MON, MM, RM Month. Month rounds up on the sixteenth
day.
WW Same day of the week as the first day of the
year.
W Same day of the week as the first day of the
month.
DDD, DD, J Day.
DAY, DY, D Starting day of the week.
HH, HH12, HH24 Hour.
MI Minute.
EXAMPLES:
(NOTE: DUAL is a special table containing but one row and one column. The
syntax of SQL requires a FROM clause. ORACLE provides DUAL for situations
like the following where there is no actual FROM table. Think of it as a
dummy table or placeholder.)
SQL>select ROUND(TO_DATE('02-JUN-94'), 'CC') from DUAL;
ROUND(TO_
---------
01-JAN-00
SQL>select ROUND(TO_DATE('02-JUN-94'), 'SCC') from DUAL;
ROUND(TO_
---------
01-JAN-00
SQL>select ROUND(TO_DATE('02-JUN-94'), 'I') from DUAL;
ROUND(TO_
---------
01-JAN-94
SQL>select ROUND(TO_DATE('02-JUN-94'), 'Q') from DUAL;
ROUND(TO_
---------
01-JUL-94
SQL> select ROUND(TO_DATE('02-JUN-94'), 'MONTH') from DUAL;
ROUND(TO_
---------
01-JUN-94
SQL>select ROUND(TO_DATE('02-JUN-94'), 'W') from DUAL;
ROUND(TO_
---------
01-JUN-94
SQL> select ROUND(TO_DATE('02-JUN-94'), 'HH') from DUAL
ROUND(TO_
---------
02-JUN-94
NOTE: Some date functions will return an error if the TO_DATE
function is not used as shown above.
TRUNC(date, [fmt]) returns date with the time portion truncated to the
unit specified by the format model. If fmt is omitted,
date is truncated to the nearest day. Formats are the
same as specified for ROUND.
MORE EXAMPLES:
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
---------
09-JUN-94
(This is when I ran this series of examples.)
SQL> SELECT TO_CHAR(SYSDATE) FROM DUAL;
TO_CHAR(SYSDATE)
----------------
09-JUN-94
(This doesn't seem to make a difference but it is an explicit conversion.)
SQL> SELECT TO_CHAR(SYSDATE, 'YYYY') FROM DUAL;
TO_CHAR(SYSDATE,'YYYY')
-----------------------
1994
(YYYY calls for the Year.)
SQL> SELECT TO_CHAR(SYSDATE, 'DD MONTH YYYY') FROM DUAL;
TO_CHAR(SYSDATE,'DDMONTHYYYY')
------------------------------
09 JUNE 1994
SQL> SELECT TO_CHAR(SYSDATE, 'DD-MM-YY:SSSSS') FROM DUAL;
TO_CHAR(SYSDATE,'DD-MM-YY:SSSSS')
---------------------------------
09-06-94:53724
(SSSSS calls for seconds since midnight.)
SQL> SELECT TO_CHAR(SYSDATE, 'J') FROM DUAL;
TO_CHAR(SYSDATE,'J')
--------------------
2449513
(J calls for the julian date.)
SQL> SELECT TO_CHAR(SYSDATE, 'DD-MM-YYYY:HH:MI:SS AM') FROM DUAL;
TO_CHAR(SYSDATE,'DD-MM-YYYY:HH:MI:SSAM')
---------------------------------------------------------------------------
09-06-1994:02:56:44 PM
SQL> SELECT TO_CHAR(SYSDATE, 'DDTH-MONTH-YYYY:HH:MI:SS AM') FROM DUAL;
TO_CHAR(SYSDATE,'DDTH-MONTH-YYYY:HH:MI:SSAM')
---------------------------------------------
09TH-JUNE -1994:02:57:33 PM
(HH:MI:SS AM calls for hours, minutes, seconds am/pm.)
SQL> SELECT TO_CHAR(SYSDATE, 'DDTH MONTH YYYY:HH:MI:SS AM') FROM DUAL;
TO_CHAR(SYSDATE,'DDTHMONTHYYYY:HH:MI:SSAM')
-------------------------------------------
09TH JUNE 1994:02:57:48 PM
SQL> SELECT TO_CHAR(SYSDATE, 'DDSPTH MONTH YYYY:HH:MI:SS AM') FROM DUAL;
TO_CHAR(SYSDATE,'DDSPTHMONTHYYYY:HH:MI:SSAM')
---------------------------------------------
NINTH JUNE 1994:02:58:30 PM
SQL> SELECT TO_CHAR(NEW_TIME(SYSDATE,'EST','PST'), 'DDSPTH MONTH
YYYY:HH:MI:SS AM') FROM DUAL
TO_CHAR(NEW_TIME(SYSDATE,'EST','PST'),'DDSPTHMONTHYYYY:HH:MI:SSAM')
---------------------------------------------------------------------------
NINTH JUNE 1994:12:00:44 PM
(Conversion to Pacific time.)
SQL> SELECT TO_CHAR(NEW_TIME(SYSDATE,'EST','GMT'), 'DDSPTH MONTH
YYYY:HH:MI:SS AM') FROM DUAL
TO_CHAR(NEW_TIME(SYSDATE,'EST','GMT'),'DDSPTHMONTHYYYY:HH:MI:SSAM')
---------------------------------------------------------------------------
NINTH JUNE 1994:08:01:48 PM
(Conversion to Greenwich Mean Time.)
5.5 ARITHMETIC FUNCTIONS
The material in this section is specific to ORACLE. Other relational
database management systems may provide similar functions and abilities but
this is not well standardized across products.
ABS(n) Absolute value of n
CEIL(n) Smallest integer >= n
COS(n)
COSH(n)
EXP(n)
FLOOR(n) Largest integer <= n
LN(n)
LOG(m,n)
MOD(m,n) m MOD n
POWER(m,n) m**n
ROUND(m,n) Rounds m to n decimal places
ROUND(r-sal,0) rounds to the nearest dollar
(no decimal)
SIGN(n) -1 if n < 0
0 if n = 0
1 if n > 0
SIN(n)
SINH(n)
SQRT(n) square root
TAN(n)
TANH(n)
TRUNC(n,m) truncate to m decimal places
truncate to 0 places if m is omitted
m may be negative
5.6 CHARACTER FUNCTIONS
The material in this section is specific to ORACLE. Other relational
database management systems may provide similar functions and abilities but
this is not well standardized across products.
ASCII(char) ASCII value of first char
CHR(n) The character with ASCII value n
CONCAT(char_1, char_2) Equivalent to char_1||char_2
GREATEST( ... ) examines a series of arguments and returns the one that
comes last in the collating sequence.
INITCAP(char) Capitalizes 1st letter of each word
SELECT INITCAP(sname)
FROM STUDENT;
jack smith becomes Jack Smith
smith-jones becomes Smith-Jones
smith, jack becomes Smith, Jack
The function NLS_INITCAP is provided for National
Language considerations.
INSTR(char1,char2[,n[,m]])
The position of the mth occurrence of char2 in
char1, beginning the search at position n. if
either n or m is omitted 1 is assumed.
The function INSTRB is provided for multiple byte
character set manipulations.
LEAST( ... ) examines a series of arguments and returns the one that
comes first in the collating sequence.
LENGTH(char) length of the string
The function LENGTHB is provided for multiple byte
character set manipulations.
LOWER(char) convert all letters to lower case
The function NLS_LOWER is provided for National
Language considerations.
LPAD(char1,n[,char1])
char1 left padded to length n with the sequence of
characters in char2 replicated as many times as
necessary (with blanks if char2 is omitted.)
LTRIM(char,set) removes trailing characters after the last character
not in set
REPLACE(char, srch, [repl]) returns char with every occurrence or srch
replaced by repl. If repl is omitted or NULL
all occurrences of srch are removed.
EXAMPLE: REPLACE('JACK and JUE', 'J', 'BL') returns 'BLACK and BLUE'
RPAD(char1,n[,char2])
similar to LPAD
RTRIM(char, [st]) returns char with final characters removed after the
last character not in the set st.
If st is not specified, it defaults to blank and all
trailing spaces are removed.
EXAMPLE: RTRIM('Abba Dabba//-//-//', '-/') returns 'Abba Dabba'.
SOUNDEX(char) returns a char value representing the SOUND of the
string char. ( SOUNDEX(slay) should be the same as
SOUNDEX(sleigh). It isn't infallible.)
SUBSTR(char,m[,n]) returns a substring of char starting at m and n long.
(returns all the rest of the string if n is omitted.)
The function SUBSTRB is provided for multiple byte
character set manipulations.
EXAMPLE:
SQL> select movid, title
2 from movie;
MOVI TITLE
---- ------------------------------------
1001 Back To The Future
1002 Flight of the Navigator
1003 Cobra
1004 Friday the 13th: Part IV
1005 Down and Out in Beverly Hills
1006 My Little Pony
1007 Firewalker
1008 Nightmare on Elm Street 2
1009 Ferris Bueller's Day Off
1010 Pinocchio
1011 Heartbreak Ridge
1012 Poltergeist II
1013 Ruthless People
1014 Sleeping Beauty
1015 Indiana Jones and the Temple of Doom
1016 The Fly
1017 The Boy Who Could Fly
1018 Top Gun
1019 Transformers
1020 Data Base Wizard
20 rows selected.
SQL> SELECT SUBSTR(title,1,INSTR(title,' ')-1)
2 from movie
3 /
The INSTR function finds the first ' ' in title
The SUBSTR function extracts data from the start of title up to,
but not including, the position of the blank. INSTR returns a
position. The argument for substr is a length.
Back to the Future
^
|
|------INSTR(title, ' ') finds the first ' ' at position 5
5 - 1 = 4 finds length of substring
SUBSTR(TITLE,1,INSTR(TITLE,'')-1)
------------------------------------
Back
Flight
Cobra
Friday
Down
My
Firewalker
Nightmare
Ferris
Pinocchio
Heartbreak
Poltergeist
Ruthless
Sleeping
Indiana
The
The
Top
Transformers
Data
20 rows selected.
TRANSLATE(char,from,to)
Each character from char that appears in from is
translated to the corresponding character of to.
UPPER(char) returns char translated to upper case.
The function NLS_UPPER is provided for National
Language considerations.
EXAMPLE:
SQL> select name
2 from employee
3 where upper(name) like '%A%'
4 or upper(name) like '%B%'
5 or upper(name) like '%C%'
6 or upper(name) like '%D%'
7 /
NAME
----------
Reed
Watson
Allen
Chen
Ramirez
McDonnel
Di Salvo
Schwartz
8 rows selected.
5.7 CONVERSION FUNCTIONS
The material in this section is specific to ORACLE. Other relational
database management systems may provide similar functions and abilities but
this is not well standardized across products. (Some of the special ORACLE
conversion functions relating to internal storage formats are omitted from
this list.)
CHARTOROWID(char) Converts char to a ROWID
CONVERT(char, dest_set [,source_set]) Converts a character string from
one character set to another.
HEXTORAW(char) converts hexadecimal to binary
NLSSORT is used to force comparison or sorting according to a
national language character set,
NVL(exp1, exp2) returns exp2 if and only if exp1 is NULL,
RAWTOHEX(raw) converts binary to hexadecimal
ROWIDTOCHAR(rowid) converts a ROWID to an 18 character string
TO_CHAR(n[,fmt]) converts number or date to character string. There are
numerous optional formats to specify specific forms for
the output.
TO_NUMBER(char) converts a string to a number
5.8 GROUP FUNCTIONS
The material in this section is specific to ORACLE. Other relational
database management systems may provide similar functions and abilities but
this is not well standardized across products.
In the following discussion underlining ( ALL )indicates that this is the
default.
For each function with a DISTINCT|ALL choice, the ALL option will include
all non NULL values, while the DISTINCT option will discard duplicate
values.
EXAMPLE:
AVG( [DISTINCT|ALL] n ) Average of n, ignoring NULL values
COUNT(* | [DISTINCT|ALL] expr) Number of rows where expr evaluates to
something other than NULL. * calls for
count of all selected rows.
MAX( [DISTINCT|ALL] expr) Maximum of expr
MIN( [DISTINCT|ALL] expr) Minimum of expr
STDDEV([DISTINCT|ALL] n) Standard deviation of n ignoring NULLs
SUM( [DISTINCT|ALL] n) Sum of values of n
VARIANCE( [DISTINCT|ALL] n) Variance of n ignoring null values
5.9 DECODE
The material in this section is specific to ORACLE. Other relational
database management systems may provide similar functions and abilities but
this is not well standardized across products.
DECODE( expr, search_1, result_1[, search_2, result_2, ...]
[, default])
will compare the expression expr to one or more search arguments and return
the appropriate result. If a default value is specified it will be returned
if expr matches none of the search values.
EXAMPLE: Calculation of quality points based on letter grade value
and credit hours where A = 4.0, B= 3.0, and so on.
DECODE( grade, 'A', 4.0,
'B', 3.0,
'C', 2.0,
'D', 1.0,
'E', 0.0, 0.0) * hours
Calculation of grade point average.
SUM(DECODE( grade, 'A', 4.0,
'B', 3.0,
'C', 2.0,
'D', 1.0,
'E', 0.0, 0.0)
* hours)/SUM(hours)
GROUP BY
SELECT ...
FROM ...
[ WHERE ... ]
[ GROUP BY ...
[ HAVING ... ] ]
[ ORDER BY ... ];
SELECT [DISTINCT] schema.table.column [column alias]
[, ...]
FROM schema.table [alias]
[ WHERE condition ... ];
[ GROUP BY schema.table.column [,...]
[ HAVING condition ... ] ]
[ ORDER BY schema.table.column [, ...] ];
Use to gather rows into groups.
Use one or more columns to identify criteria for forming groups.
The result will have ONE LINE FOR EACH GROUP.
If we organize employee data into groups by job type, we might visualize it
as shown below. Observe that the only thing that is constant from line to
line within each group is job.
SQL> select *
2 from employee
3 order by job;
The result of this query is shown below.
But, the lines indicate that
select ...
from employee
group by job;
will combine all rows containing the same job into a single group.
EMP NAME JOB SALARY COMM DE S
--- ---------- ---- ---------- ---------- -- -
103 Reed Anlt 3500 30 M
______________________________________________
100 Wilson Clrk 1700 10 M
110 Turner Clrk 1800 50 M
213 McDonnel Clrk 1625 60 M
______________________________________________
214 Simpson Drvr 825 60 M
______________________________________________
105 Watson Mngr 4500 0 30 M
210 Ramirez Mngr 3650 50 M
200 Chen Mngr 2900 10 F
109 Allen Mngr 3800 8000 40 F
______________________________________________
101 Smith Slsm 2500 1300 40 F
220 Schwartz Slsm 4250 5300 40 F
______________________________________________
215 Di Salvo Spvr 2700 60 M
12 rows selected.
As a result, the following will not work.
SQL> select *
2 from employee
3 group by job;
select *
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
(NOTE: Some SQL based products will accept this query. But the result
produced will not be meaningful. There is no one value within the
each "job" group for NAME, SALARY, ETC.)
But, the next one is fine.
SQL> select job, count(*)
2 from employee
3 group by job;
JOB COUNT(*)
---- ----------
Anlt 1
Clrk 3
Drvr 1
Mngr 4
Slsm 2
Spvr 1
6 rows selected.
The ONLY THINGS that can be selected are the columns specified in the GROUP
BY clause and GROUP functions.
COUNT(), MIN(), MAX(), AVG(), STDDEV(), SUM()
HAVING is used to select specific groups
SELECT dept,SUM(sal)
FROM emp
GROUP BY dept
HAVING SUM(sal) > 9000;
only lists departments with payroll in excess of $9,000.00.
SQL> select deptno, count(*)
2 from employee
3 group by deptno
4 /
DE COUNT(*)
-- ----------
10 2
30 2
40 3
50 2
60 3
SQL> select job, count(*), min(salary), max(salary), avg(salary)
2 from employee
3 group by job
4 /
JOB COUNT(*) MIN(SALARY) MAX(SALARY) AVG(SALARY)
---- ---------- ----------- ----------- -----------
Anlt 1 3500 3500 3500
Clrk 3 1625 1800 1708.33333
Drvr 1 825 825 825
Mngr 4 2900 4500 3712.5
Slsm 2 2500 4250 3375
Spvr 1 2700 2700 2700
6 rows selected.
SQL> select employee.deptno, max(deptname), count(*)
2 from employee, department
3 where employee.deptno = department.deptno
4 group by employee.deptno
5 /
DE MAX(DEPTNAME) COUNT(*)
-- --------------- ----------
10 Accounting 2
30 Research 2
40 Sales 3
50 Manufacturing 2
60 Shipping 3
6.0 JOINING TABLES
SELECT [DISTINCT] schema.table.column [column alias]
[, ...]
FROM schema.table-1 [alias_1],
schema.table_2 [alias_2]
[, ...]
[ WHERE condition ... ];
The JOIN operation brings together (concatenates) rows from two or more
tables based on some condition. The most common condition will be that a
specified column value from a row of one table matches a specified column
value from another.
SELECT student.sid, sname, crsnbr, grade
FROM student, crsenrl
WHERE student.sid = crsenrl.sid;
The rows from the student table are combined with rows from the crsenrl
table based on a matching sid.
NOTE: Since sid appears in both tables it MUST BE QUALIFIED wherever it
is used to avoid ambiguity.
There is no practical limit to the number of tables that may be joined.
But, joining several large tables can consume a lot of processing and time.
SELECT student.sid, sname, course.crsnbr, cname, grade
FROM student, crsenrl, course
WHERE student.sid = crsenrl.sid
AND course.crsnbr = crsenrl.crsnbr;
NOTE: In this example crsnbr MUST ALSO BE QUALIFIED wherever it is
referenced to avoid ambiguity.
Since an additional table is included, an additional matching
condition is NECESSARY.
As a general rule, IF N TABLES ARE JOINED, AT LEAST N-1 MATCHING CONDITIONS
ARE REQUIRED.
Additional conditions may be imposed on the result.
SELECT student.sid, sname, course.crsnbr, cname, grade
FROM student, crsenrl, course
WHERE student.sid = crsenrl.sid
AND course.crsnbr = crsenrl.crsnbr
AND grade <= 'B';
Note that in this case grade <= 'B' really calls for a grade of B or
greater since 'A' precedes 'B' in the collating sequence.
Generally speaking, the columns used for matching will represent the same
information and will represent a FOREIGN KEY relationship. This is not
required by SQL but it is more or less required if the result is to be
meaningful.
SELECT product.prod_id, qty, ord_item.u_price
FROM product, ord_item
WHERE product.prod_id = ord_num;
The above is legal SQL syntax. A result may be produced. But that result
will be MEANINGLESS.
All of the above examples specified an equal condition. This is commonly
referred to as an EQUIJOIN. A JOIN may specify an other than equal
comparison.
EXAMPLE:
SQL> COLUMN LIST FORMAT 9999.99
SQL> COLUMN SELLING FORMAT 9999.99
SQL> select product.prod_id PRODUCT, product.u_price LIST,
2 ord_num ORDNUM, ord_item.u_price SELLING
3 from product, ord_item
4 where product.prod_id = ord_item.prod_id
5 and product.u_price > ord_item.u_price;
PRODU LIST ORDNU SELLING
----- -------- ----- --------
10002 12.00 32347 11.00
10010 1000.00 22346 323.00
10010 1000.00 54321 995.00
10010 1000.00 44447 775.00
10010 1000.00 44444 700.00
10010 1000.00 33331 900.00
10010 1000.00 34323 800.00
20010 100.00 32345 95.00
20020 25.00 24323 23.00
9 rows selected.
SQL> clear columns;
6.1 INNER JOIN
The result of the INNER JOIN contains only those rows that match across
tables. Any rows with no match are dropped.
SELECT student.sid, sname, crsnbr, grade
FROM student, crsenrl
WHERE student.sid = crsenrl.sid;
Should there be a student.sid with no matching crsenrl.sid, that row will
be omitted from the result. Should there be a crsenrl.sid with no matching
student.sid, that row will be omitted from the result.
EXAMPLES:
SQL> select empno, department.deptno, deptname,
jobs.job, jobname
2 from employee, department, jobs
3 where employee.job = jobs.job
4 and employee.deptno = department.deptno
5 /
EMP DE DEPTNAME JOB JOBNAME
--- -- --------------- ---- ---------------
100 10 Accounting Clrk Clerk
101 40 Sales Slsm Salesperson
103 30 Research Anlt Analyst
105 30 Research Mngr Manager
109 40 Sales Mngr Manager
110 50 Manufacturing Clrk Clerk
200 10 Accounting Mngr Manager
210 50 Manufacturing Mngr Manager
213 60 Shipping Clrk Clerk
214 60 Shipping Drvr Driver
215 60 Shipping Spvr Supervisor
EMP DE DEPTNAME JOB JOBNAME
--- -- --------------- ---- ---------------
220 40 Sales Slsm Salesperson
12 rows selected.
SQL> select empno, department.deptno, deptname,
jobs.job, jobname
2 from employee, department, jobs
3 where employee.job = jobs.job
4 and employee.deptno = department.deptno
5 order by empno
6 /
EMP DE DEPTNAME JOB JOBNAME
--- -- --------------- ---- ---------------
100 10 Accounting Clrk Clerk
101 40 Sales Slsm Salesperson
103 30 Research Anlt Analyst
105 30 Research Mngr Manager
109 40 Sales Mngr Manager
110 50 Manufacturing Clrk Clerk
200 10 Accounting Mngr Manager
210 50 Manufacturing Mngr Manager
213 60 Shipping Clrk Clerk
214 60 Shipping Drvr Driver
215 60 Shipping Spvr Supervisor
EMP DE DEPTNAME JOB JOBNAME
--- -- --------------- ---- ---------------
220 40 Sales Slsm Salesperson
12 rows selected.
SQL> select employee.empno, name, date_beg
2 from emplhist, employee
3 where seq = '1'
4 and date_beg < '01-APR-81'
5 and emplhist.empno = employee.empno
6 /
EMP NAME DATE_BEG
--- ---------- ---------
101 Smith 01-MAR-81
109 Allen 01-APR-80
6.2 OUTER JOIN
The OUTER JOIN includes unmatched rows. In theory it would be possible to
include unmatched rows from both tables. The OUTER JOIN of student and
crsenrl might include student rows with no matching crsenrl.sid and crsenrl
rows with no matching student.sid.
Some relational database management systems do not provide the OUTER JOIN.
ORACLE provides a one-sided OUTER JOIN capability .
EXAMPLE:
SQL> select student.sid, sname, crsnbr, grade
2 from student, crsenrl
3* where student.sid = crsenrl.sid(+)
SID SNAME CRSNBR G
--- ---------- ------ -
748 MEGLIN MGT681 B
506 LEE
581 GAMBRELL
126 ANDERSON
444 LINSTERBOK CIS151 A
444 LINSTERBOK CIS411 A
(only a portion of the output is shown here.) Students with NO enrollments
appear. Crsnbr and grade are NULL for these.
OTHER relational database management systems that provide the OUTER JOIN
are likely to use a different notation.
Some systems do not provide it at all.
6.3 PRODUCT
Relational theory includes the definition of the (CARTESIAN) PRODUCT. The
PRODUCT operation combines each row of each table with every row of the
other table.
This can be obtained by omitting the matching condition from the SQL
statement.
SELECT student.sid, sname, crsnbr, grade
FROM student, crsenrl;
Since NO CONDITION is specified, the result will contain every combination
of student and crsenrl. If there are 20 student rows and 100 crsenrl rows,
the result will contain 20 X 100 = 2,000 rows.
The PRODUCT is not usually a meaningful result and often represents an
error in the SQL.
It is also possible to create a 'partial' PRODUCT.
SELECT student.sid, sname, course.crsnbr, cname, grade
FROM student, crsenrl, course
WHERE student.sid = crsenrl.sid;
This will JOIN student and crsenrl but will then form the PRODUCT of the
first result and course. This represents an error since it is not a
particularly useful result.
As a general rule, IF N TABLES ARE JOINED, AT LEAST N-1 MATCHING CONDITIONS
ARE REQUIRED IF THE RESULT IS TO MAKE SENSE.
7.0 SET OPERATIONS
7.1 UNION
The UNION, as defined in relational algebra, is the set union of two
relations. It contains every row from each relation. It is the logical sum
of two sets. Since a mathematical set contains no duplicates, the set union
contains no duplicates, and any row appearing in both results must appear
but once in the result.
An SQL UNION returns all distinct rows selected by either query.
SELECT [DISTINCT*] schema.table.column [column alias]
[, ...]
FROM schema.table [alias]
[ WHERE condition ... ]
UNION
SELECT [DISTINCT*] schema.table.column [column alias]
[, ...]
FROM schema.table [alias]
[ WHERE condition ... ]
[ UNION ... ]
[ #ORDER BY schema.table.column [, ...]];
*Since the UNION operation eliminates duplicates, DISTINCT, while
syntactically correct, is not necessary.
The columns selected for part 2 must be the SAME TYPES as the corresponding
columns in part 1.
#ORDER BY, if specified applies to the entire result of the UNION. In order
to eliminate duplicates, the system may sort the result and ORDER BY may
not be necessary. However, since different database management systems and
even different releases of the same database management system may utilize
different techniques it is really necessary to specify ORDER BY if
consistent results are desired.
If the SAME TABLE and COLUMNS are referenced by BOTH queries, you could
also write
SELECT {columns}
FROM {table}
WHERE {condition_1}
OR {condition_2};
EXAMPLE:
SQL> select name, salary, sex
2 from employee
3 where sex = 'M'
4 union
5 select name, salary, sex
6 from employee
7 where sex = 'F'
8 order by 1;
^
|
|--------- NOTE: the use of the column number instead of the
column name.
NAME SALARY S
---------- ---------- -
Allen 3800 F
Chen 2900 F
Di Salvo 2700 M
McDonnel 1625 M
Ramirez 3650 M
Reed 3500 M
Schwartz 4250 F
Simpson 825 M
Smith 2500 F
Turner 1800 M
Watson 4500 M
NAME SALARY S
---------- ---------- -
Wilson 1700 M
12 rows selected.
The above could be written as:
SQL> select name, salary, sex
2 from employee
3 where sex = 'M'
4 or sex = 'F'
5 order by 1;
SQL> select empno, name, deptno
2 from employee
3 where deptno = '10'
4 and sex = 'M'
5 union
6 select empno, name, deptno
7 from employee
8 where deptno = '20'
9 and sex = 'M'
10 union
11 select empno, name, deptno
12 from employee
13 where deptno = '30'
14 and sex = 'M'
15 /
EMP NAME DE
--- ---------- --
100 Wilson 10
103 Reed 30
105 Watson 30
The above could be written as
SQL> select empno, name, deptno
2 from employee
3 where deptno IN ('10', '20', 30')
4 and sex = 'M';
It could also be written as
SQL> select empno, name, deptno
2 from employee
3 where (deptno = '10' OR deptno = '20' OR deptno = '30)
4 and sex = 'M';
NOTE: IN the last example, the "(" and ")" are required for correct
evalualation of the query.
7.2 UNION ALL
SELECT [DISTINCT] schema.table.column [column alias]
[, ...]
FROM schema.table [alias]
[ WHERE condition ... ]
UNION ALL
SELECT [DISTINCT] schema.table.column [column alias]
[, ...]
FROM schema.table [alias]
[ WHERE condition ... ]
[ UNION ALL ... ]
[ ORDER BY schema.table.column [, ...]];
ALL specifies that all rows produced by each query will appear in the
result. Duplicates across the results will NOT be eliminated.
This can be useful in two situations:
If you are absolutely certain that the two queries will contain no
duplicates, some system overhead may be avoided by specifying ALL
since there must be a cost involved in eliminating duplicates. This
cost will be incurred even if there are no duplicates to eliminate
since the system cannot know in advance that this will be the case.
Some risk is involved in this, since if the assumption that there will
be no duplicates is invalid, the result obtained may be incorrect.
It may be that despite the fact that a true relation should not
contain duplicates, your data does, in fact, contain them. Should this
be the case, and you want the duplicates to appear in the result you
must specify ALL.
7.3 INTERSECT
The INTERSECT, as defined in relational algebra, is the set intersection of
two relations. It contains only those rows which appear in both relations.
It is the logical product of two sets. Since a mathematical set contains no
duplicates, the set intersection contains no duplicates, and any element
appearing in both sets must appear but once in the result.
An SQL INTERSECT returns all distinct rows selected by both queries.
SELECT [DISTINCT*] schema.table.column [column alias]
[, ...]
FROM schema.table [alias]
[ WHERE condition ... ]
INTERSECT
SELECT [DISTINCT*] schema.table.column [column alias]
[, ...]
FROM schema.table [alias]
[ WHERE condition ... ]
[ INTERSECT ... ]
[ #ORDER BY schema.table.column [, ...]];
*Since the INTERSECT operation eliminates duplicates, DISTINCT, while
syntactically correct, is not necessary.
The columns selected for part 2 must be the SAME TYPES as the corresponding
columns in part 1.
#ORDER BY, if specified applies to the entire result. In order to eliminate
duplicates, the system may sort the result and ORDER BY may not be
necessary. However, since different database management systems and even
different releases of the same database management system may utilize
different techniques it is really necessary to specify ORDER BY if
consistent results are desired.
If the SAME TABLE and COLUMNS are referenced by BOTH queries, you
could also write
SELECT columns
FROM table
WHERE condition_1
AND condition_2;
ORACLE provides the INTERSECT operation as described here. This is not true
of all relational database management systems.
EXAMPLE:
SQL> select empno, name, deptno
2 from employee
3 where sex = 'M'
4 intersect
5 select empno, name, deptno
6 from employee
7 where deptno in ('10', '20', '30')
8 /
EMP NAME DE
--- ---------- --
100 Wilson 10
103 Reed 30
105 Watson 30
7.4 MINUS
MINUS, as defined in relational algebra, is the set difference of two
relations. It contains only those rows which appear one relation but not
another. It is the difference between two sets. Since a mathematical set
contains no duplicates, the set difference contains no duplicates.
MINUS returns all distinct rows selected by the first query and NOT
selected by the second.
SELECT [DISTINCT*] schema.table.column [column alias]
[, ...]
FROM schema.table [alias]
[ WHERE condition ... ]
MINUS
SELECT [DISTINCT*] schema.table.column [column alias]
[, ...]
FROM schema.table [alias]
[ WHERE condition ... ]
[ MINUS ... ]
[ #ORDER BY schema.table.column [, ...]];
*Since the MINUS operation eliminates duplicates, DISTINCT, while
syntactically correct, is not necessary.
The columns selected for part 2 must be the SAME TYPES as the corresponding
columns in part 1.
#ORDER BY, if specified applies to the entire result. In order to eliminate
duplicates, the system may sort the result and ORDER BY may not be
necessary. However, since different database management systems and even
different releases of the same database management system may utilize
different techniques it is really necessary to specify ORDER BY if
consistent results are desired.
If the SAME TABLE and COLUMNS are referenced by BOTH queries, you
could also write
SELECT columns
FROM table
WHERE condition_1
AND NOT condition_2;
ORACLE provides the INTERSECT operation as described here. This is not true
of all relational database management systems.
7.5 DIVIDE
DIVIDE is not an SQL command. It is, however, defined as a component of
RELATIONAL ALGEBRA. A brief discussion is provided here for the sake of
completeness.
According to Date, "DIVIDE takes two relations, one binary and one unary,
and builds a relation consisting of all values of one attribute of the
binary relation that match (in the other attribute) all values in the unary
relation."
Yes, that's a mouthful. Here is an illustration of what it means. Suppose
parts may be stored in several different warehouses. The following is a
table indicating which parts are actually stored in which warehouses.
SQL> SELECT * FROM stock;
WH P
-- -
27 A
27 B
27 C
32 B
48 A
48 B
6 rows selected.
This can be called a binary relation because the table has two columns.
The following is a table listing all parts.
SQL> SELECT * FROM parts;
P
-
A
B
C
This can be called a unary relation because the table has one column.
The result of dividing stock by parts will be a table listing all
warehouses which stock all parts.
This might be accomplished in the following manner using SQL.
SQL> SELECT whse
2 FROM stock, parts
3 WHERE stock.part = parts.part
4 GROUP BY whse
5 HAVING COUNT(DISTINCT stock.part) =
6 (SELECT COUNT(DISTINCT part)
7 FROM parts)
8 /
WH
--
27
The remainder or list of warehouses that do not stock all parts might be
found as follows.
SELECT whse
FROM stock
WHERE whse NOT IN
(SELECT whse
FROM stock, parts
WHERE stock.part = parts.part
GROUP BY whse
HAVING COUNT(*) =
(SELECT COUNT(DISTINCT part)
FROM parts));
There are probably other ways to obtain the same effect. And, this
operation may not be particularly useful. However, including it makes this
presentation complete.
7.6 CONNECT
"Recursive" or "bill of material" relationships pose a particular problem
for database systems in general and relational database management systems
in particular. These are characterized by relationships between the same
kinds of things. One example is assemblies composed of sub-assemblies
composed of sub-assemblies ... eventually composed of parts. Another is
senior managers who supervise managers who supervise supervisors ... who
supervise employees.
One "level" of this structure might be managed by a "self join".
Extending this to multiple levels would require writing a query specific to
a particular number of levels. This would require foreknowledge of the
number of levels and a different query for each case.
While there are ways to deal with this situation when SQL is embedded in a
host program, standard SQL provides no easy way to deal with it in a simple
query.
The ORACLE CONNECT BY clause is an SQL extension that can, in some cases,
serve as a useful solution to this problem.
EXAMPLE:
SELECT *
FROM parts;
PART COMP QTY
---- ---- ----------
ROOT
AAAA ROOT 5
AAAB AAAA 3
AAAC AAAA 3
BBBB ROOT 6
BBAA BBBB 3
BBAB BBAA 3
BBAC BBAA 2
BBCC BBBB 1
9 rows selected.
SELECT part_no, comp_of, qty
FROM parts
CONNECT BY PRIOR part_no = comp_of;
PART COMP QTY
---- ---- ----------
ROOT
AAAA ROOT 5
AAAB AAAA 3
AAAC AAAA 3
BBBB ROOT 6
BBAA BBBB 3
BBAB BBAA 3
BBAC BBAA 2
BBCC BBBB 1
AAAA ROOT 5
AAAB AAAA 3
AAAC AAAA 3
AAAB AAAA 3
AAAC AAAA 3
BBBB ROOT 6
BBAA BBBB 3
BBAB BBAA 3
BBAC BBAA 2
BBCC BBBB 1
BBAA BBBB 3
BBAB BBAA 3
BBAC BBAA 2
BBAB BBAA 3
BBAC BBAA 2
BBCC BBBB 1
25 rows selected.
COLUMN PART_NUMBER FORMAT A15;
SELECT LPAD(' ',2*(LEVEL-1))||part_no PART_NUMBER, qty
FROM parts
CONNECT BY PRIOR part_no = comp_of;
CLEAR COLUMNS;
The START WITH clause identifies the starting point of the retrieval.
There are restrictions to the use of CONNECT BY. The select statement that
performs a "hierarchical" query cannot also perform a JOIN. ORDER BY will
alter the sequence of the output.
8.0 SUBQUERIES
A SUBQUERY, in effect, embeds a query within a query, using the result of
the SUBQUERY as a comparand in a WHERE clause.
8.1 SIMPLE SUBQUERY
EXAMPLE:
Find employees in the same department as employee #213. Notice that it is
not necessary to know the actual department identifier.
SQL> select name, empno
2 from employee
3 where deptno =
4 (select deptno
5 from employee
6 where empno = '213')
7 /
NAME EMP
---------- ---
McDonnel 213
Simpson 214
Di Salvo 215
This time we'll leave #213 off the list.
SQL> select name, empno
2 from employee
3 where deptno =
4 (select deptno
5 from employee
6 where empno = '213')
7 and empno <> '213'
8 /
NAME EMP
---------- ---
Simpson 214
Di Salvo 215
List Courses taken by Jones
SELECT course.crnsbr, cname
FROM course, crsenrl
WHERE course.crsnbr = crsenrl.crsnbr
AND sid =
(SELECT sid
FROM student
WHERE sname = 'Jones');
Here, the SUBQUERY finds the sid for jones.
Then the main QUERY finds the courses by searching the crsenrl table for
all enrollments with that sid.
This example using a test for equal will work only if the SUBQUERY returns
a single row. Since sid, a single item, cannot be compared to a table an
error would result if multiple rows were returned by the subquery. See the
next section for the solution to this potential problem.
8.2 SUBQUERIES WITH IN, NOT IN, ANY, ALL
If there were two students named Jones an error would result from the
above.
So we write
SELECT sid, course.crnsbr, cname
FROM course, crsenrl
WHERE course.crsnbr = crsenrl.crsnbr
AND sid IN
(SELECT sid
FROM student
WHERE sname = 'Jones');
IN tests whether or not the sid in question is in the set of sid's for the
set of students named Jones. Given that it is possible we may find more
than one Jones, it is a good idea to show sid in the output to indicate
which Jones has taken which courses.
8.3 SUBQUERIES equivalent to JOINS
We might obtain the same result as the above in the following manner.
SELECT course.crnsbr, cname, student.sid
FROM course, crsenrl, student
WHERE course.crsnbr = crsenrl.crsnbr
AND student.sid = crsenrl.sid
AND sname = 'Jones';
Find all employees who earn more than the lowest paid employee with job
type "Mngr".
SQL> select name, salary
2 from employee
3 where salary >
4 (select min(salary)
5 from employee
6 where job = 'Mngr')
7 /
NAME SALARY
---------- ----------
Reed 3500
Watson 4500
Allen 3800
Ramirez 3650
Schwartz 4250
In this database, job = "Mngr" and mgrflag = "Y" do not mean the same
thing. The following might be what is really wanted here.
SQL> select name, salary
2 from employee
3 where salary >
4 (select min(salary)
5 from employee, jobs
6 where jobs.job = employee.job
7 and mgrflag = 'Y')
8 /
NAME SALARY
---------- ----------
Reed 3500
Watson 4500
Allen 3800
Chen 2900
Ramirez 3650
Schwartz 4250
Suppose we want to incorporate commission in this calculation.
Should we write
...
where salary+ comm >
(select min(salary +comm) ...
we would not get the correct answer because some commission values are
NULL. (ORACLE would ignore these rows completely.)
The following will produce a correct answer.
SQL> select name, salary+nvl(comm,0)
2 from employee
3 where salary + nvl(comm,0) >
4 (select min(salary+nvl(comm,0))
5 from employee
6 where job = 'Mngr');
NAME SALARY+NVL(COMM,0)
---------- ------------------
Smith 3800
Reed 3500
Watson 4500
Allen 11800
Ramirez 3650
Schwartz 9550
6 rows selected.
SQL> select name, salary+nvl(comm,0)
2 from employee
3 where salary + nvl(comm,0) >
4 (select min(salary+nvl(comm,0))
5 from employee
6 where job = 'Mngr')
7 /
NAME SALARY+NVL(COMM,0)
---------- ------------------
Smith 3800
Reed 3500
Watson 4500
Allen 11800
Ramirez 3650
Schwartz 9550
6 rows selected.
SQL> select name, salary+nvl(comm,0)
2 from employee
3 where salary + nvl(comm,0) >
4 (select min(salary+nvl(comm,0))
5 from employee, jobs
6 where jobs.job = employee.job
7 and mgrflag = 'Y')
8 /
NAME SALARY+NVL(COMM,0)
---------- ------------------
Smith 3800
Reed 3500
Watson 4500
Allen 11800
Chen 2900
Ramirez 3650
Schwartz 9550
7 rows selected.
Here are some more elaborate solutions that avoid the NVL function.
SQL> select empno, name, salary, salary, 0
2 from employee
3 where comm is null
4 and salary >
5 (select min(salary)
6 from employee
7 where job = 'Mngr'
8 and comm is null)
9 union
10 select empno, name, salary+comm, salary, comm
11 from employee
12 where comm is not null
13 and salary+comm >
14 (select min(salary)
15 from employee
16 where job = 'Mngr'
17 and comm is null)
18 /
EMP NAME SALARY SALARY 0
--- ---------- ---------- ---------- ----------
101 Smith 3800 2500 1300
103 Reed 3500 3500 0
105 Watson 4500 4500 0
109 Allen 11800 3800 8000
210 Ramirez 3650 3650 0
220 Schwartz 9550 4250 5300
6 rows selected.
SQL> select empno, name, salary, salary, 0
2 from employee
3 where comm is null
4 and salary > any
5 (select salary
6 from employee
7 where job = 'Mngr'
8 and comm is null
9 union
10 select salary+comm
11 from employee
12 where job = 'Mngr'
13 and comm is not null)
14 union
15 select empno, name, salary+comm, salary, comm
16 from employee
17 where comm is not null
18 and salary+comm > any
19 (select salary
20 from employee
21 where job = 'Mngr'
22 and comm is null
23 union
24 select salary+comm
25 from employee
26 where job = 'Mngr'
27 and comm is not null)
28 /
EMP NAME SALARY SALARY 0
--- ---------- ---------- ---------- ----------
101 Smith 3800 2500 1300
103 Reed 3500 3500 0
105 Watson 4500 4500 0
109 Allen 11800 3800 8000
210 Ramirez 3650 3650 0
220 Schwartz 9550 4250 5300
6 rows selected.
8.4 NESTED SUBQUERIES
SUBQUERIES can appear within SUBQUERIES.
SELECT crnsbr, cname
FROM course
WHERE crsnbr IN
(SELECT crsnbr
FROM crsenrl
WHERE sid IN
(SELECT sid
FROM student
WHERE sname = 'Jones'));
8.5 CORRELATED SUBQUERY
In the preceding examples, each SUBQUERY produced a single result which was
then used in a test involving all rows examined by the outer query. A
CORRELATED SUBQUERY must, in effect, be reevaluated for each row examined
by the outer query.
The following example finds students with a gpa greater than the average
gpa for students in the same major.
SELECT outer.sid, outer.Sname, outer.gpa
FROM student outer
WHERE gpa >
(SELECT AVG(gpa)
FROM student inner
WHERE outer.major = inner.major);
Inner and outer are ALIASES for student.
This query compares each student's gpa to the average gpa for all students
with the same major.
It has the effect of evaluating the SUBQUERY for every row considered by
the main QUERY.
SQL> select empno, name, salary, deptno
2 from employee outer
3 where salary - 1000 >=
4 (select min(salary)
5 from employee inner
6 where outer.deptno = inner.deptno)
7 /
EMP NAME SALARY DE
--- ---------- ---------- --
105 Watson 4500 30
109 Allen 3800 40
200 Chen 2900 10
210 Ramirez 3650 50
215 Di Salvo 2700 60
220 Schwartz 4250 40
9.0 COMBINATIONS
SQL> select name, salary
2 from employee
3 where comm is null
4 and salary > any
5 (select min(salary)
6 from employee
7 where job = 'Mngr'
8 and comm is null
9 union
10 select min(salary+comm)
11 from employee
12 where job = 'Mngr'
13 and comm is not null)
14 union
15 select name, salary+comm
16 from employee
17 where comm is not null
18 and salary+comm > any
19 (select min(salary)
20 from employee
21 where job = 'Mngr'
22 and comm is null
23 union
24 select min(salary+comm)
25 from employee
26 where job = 'Mngr'
27 and comm is not null)
28 /
NAME SALARY
---------- ----------
Allen 11800
Ramirez 3650
Reed 3500
Schwartz 9550
Smith 3800
Watson 4500
6 rows selected.
SQL> select name, salary
2 from employee
3 where comm is null
4 and salary > any
5 (select min(salary)
6 from employee, jobs
7 where jobs.job = employee.job
8 and mgrflag = 'Y'
9 and comm is null
10 union
11 select min(salary+comm)
12 from employee, jobs
13 where jobs.job = employee.job
14 and mgrflag = 'Y'
15 and comm is not null)
16 union
17 select name, salary+comm
18 from employee
19 where comm is not null
20 and salary+comm > any
21 (select min(salary)
22 from employee, jobs
23 where jobs.job = employee.job
24 and mgrflag = 'Y'
25 and comm is null
26 union
27 select min(salary+comm)
28 from employee, jobs
29 where jobs.job = employee.job
30 and mgrflag = 'Y'
31 and comm is not null)
32 /
NAME SALARY
---------- ----------
Allen 11800
Chen 2900
Ramirez 3650
Reed 3500
Schwartz 9550
Smith 3800
Watson 4500
7 rows selected.
Look the next one over carefully.
SQL> select job, count(*), min(salary), max(salary),
avg(salary)
2 from employee
3 group by job
4 union
5 select job, 0, minsalary, maxsalary, 0
6 from jobs
7 where job not in
8 (select job
9 from employee)
10 /
JOB COUNT(*) MIN(SALARY) MAX(SALARY) AVG(SALARY)
---- ---------- ----------- ----------- -----------
Amgr 0 1500 3000 0
Anlt 1 3500 3500 3500
Clrk 3 1625 1800 1708.33333
Drvr 1 825 825 825
Mngr 4 2900 4500 3712.5
Newh 0 500 800 0
Secy 0 800 2200 0
Slsa 0 950 2000 0
Slsm 2 2500 4250 3375
Spvr 1 2700 2700 2700
10 rows selected.
SQL> Column CUM format 99.99;
SQL> select student.sid,
2 SUM( DECODE( grade, 'A',4.0,
3 'B',3.0,
4 'C',2.0,
5 'D',1.0, 0.0)*3.0) / (COUNT(crsnbr)*3.0) CUM
6 from student, crsenrl
7 where student.sid = crsenrl.sid
8 group by student.sid;
NOTES: "CUM" establishes an ALIAS for the column containing
the DECODE function.
The Column command sets a format for the result of the
calculation.
A clear columns command at the end removes the format
from the environment.
"