lOMoARcPSD|341 746 99

Dr.R.A.N.M Arts and Science College,Erode-09.

Department of Computer Applications
Class: III-BCA Subject: Core 8:RDBMS& Oracle
Unit – III
Working with Table: Data Management and Retrieval: DML – adding a new Row/Record –
Customized Prompts – Updating and Deleting an Existing Rows/Records – retrieving Data from
Table – Arithmetic Operations – restricting Data with WHERE clause –Sorting – Revisiting
Substitution Variables – DEFINE command – CASE structure. Functions and Grouping: Built-in
functions –Grouping Data. Multiple Tables: Joins and Set operations: Join– Set operations

WORKING WITH TABLES: DATA MANAGEMENT AND RETRIEVAL

DATA MANIPULATION LANGUAGE:

It consists of 3 statements – INSERT, UPDATE and DELETE.
INSERT – a new row is added to a table
UPDATE – data in existing rows are changed
DELETE – it removes rows from a table
SELECT – it retrieves data from tables, but it does not affect the data

ADDING A NEW ROW/RECORD

The general syntax for insert statement is
INSERT INTO table name [(column1, column2,column3, ……)]
VALUES (value1, value2, value3……);
For example,
Insert into sun(rno, name, city) values (>0989?,?sunil?,?cbe?);

ROUNDING BY INSERT:
If we insert values 543.876 in a NUMBER(6,2) column, the precision is 4, and the
scale is 2. the resulting values will be 543.88 rounded to 2 decimal places, or a scale of 2.

ENTERING NULL VALUES:

There are 2 methods for inserting a NULL value in a column.
Implicit method : in this, the column?s name is omitted from the column list in an

INSERT statement. For example,

INSERT INTO dept(deptid, deptname)
VALUES (50, >BCA?);
Explicit method : in this, the null value is used as a value for a numeric column, and
an empty string(>) is used for date or character columns. For example,
INSERT INTO dept(deptid, deptname, empid)
VALUES (51, >BCA?, NULL);

ENTERING DEFAULT VALUES:

If a default value is assigned to the column during the table?s creation, that default
value is inserted into the column. If no default value is assigned to the column, the keyword
DEFALUT will result into a null value for the column.

Prepared by K.Gokul HOD of BCA(Dr.R.A.N.M

 lOMoARcPSD|341 746 99

SUBSTITUTION VARIABLES:
 The SQL language does have substitution variables, which enable us to create
an interactive SQL script.
 When we execute the script, Oracle prompts us to enter a value for the
substitution variable.
 The ampersand (&) character is used before the substitution variable in the
query.

Example

SQL> INSERT INTO dept(deptid, deptname, location) VALUES(&deptid,

2 >&deptname?, &location)
Enter value for deptid: 70
Enter value for deptname: Testing
Enter value for location: Miami

CUSTOMIZED PROMPTS:
 The SQL *Plus command ACCEPT is used for customized prompts. The
ACCEPT command does not use an ampersand in front of the variable name.
 we might to use the ACCEPT statement for a variable to be used later more
than 1 INSERT statement. The syntaxis as follows
ACCEPT variable name PROMPT >prompt message?

Example

SQL>ACCEPT deptid PROMPT >please enter department id?

Please enter department id: 34

UPDATING EXISTING ROWS/RECORDS:

 Once data are added to the tables for various entities. In SQL, the UPDATE
statementis used for such modifications to data.
 Only 1 table can be updated at a time, but it is possible to change more than 1
column at a time. The syntax is
UPDATE table name
SET column1 = new value [, column2 = new value…..]
[WHERE condition(s)];

Relational operator Meaning

= Equal to
<> or != Not equal to
> Greater than
>= Greater than or equal to
< Less than
<= Less than or equal to

Prepared by K.Gokul HOD of BCA(Dr.R.A.N.M

 lOMoARcPSD|341 746 99

DELETING EXISTING ROWS/RECORDS:

In oracle, the SQL statement DELETE is used for deleting unwanted rows. The
syntax is
DELETE [FROM] table name
[WHERE condition(s)];
for
example, SQL > DELETE FROM dept WHERE deptid=12;
1 ROW DELETED.
SQL >

RETRIEVING DATA FROM A TABLE:

The syntax is
SELECT column list FROM table name;
It is used to retrieve the data from a table.
Fro example,
SELECT DEPTID, DEPTNAME FROM DEPT;
SELECT(*);
This command is used to retrieve all the data from the particular table.
Fro example,
Select * from dept;

DISTINCT FUNCTION:
This function is used to suppress duplicate values. The word DISTINCT is used right
after the keyword SELECT and before the column name.
Sql > SELECT NAME FROM S1;
SUMI
SUMI
VINO
MATHU
MATHU
FANSI
Sql > SELECT DISTINCT name FROM S1;
SUMI
MATHU
Column alias:
The syntax is SELECT column name [AS] alias……
 Column aliases are useful, because we change the column?s heading.
 When a calculated value is displayed, the mathematical expression is not displayed as the
column heading, but the column alias is displayed.
 The column alias is written right after the column name with the optional keyword. If an alias
includes spaces or special characters or if we want to preserve its case, we must enclose it in
double quotes (< <).

COLUMN COMMAND:

Prepared by K.Gokul HOD of BCA(Dr.R.A.N.M

 lOMoARcPSD|341 746 99

Sql*Plus COLUMN command allows us to specify format column for columns. The
syntax is
COLUMN column name FORMAT format type
For
example, COLUMN state FORMAT AS
COLUMN salary FORMAT $999.999

CONCATENATION:
 The word concatenation is a common word. Concatenation means joining or linking. In SQL,
concatenation joins a column or a character string to another column.
 The result is a column that is a string or a sequence of characters. 2 vertical bars or pipe
symbols (||) are used as the concatenation operator. the 2 columns values are joined without any
spaceseparating them.
SQL > SELECT lname || fname
2 FROM
EMP; LNAME | |
FNAME
MATHU
VINO
FANSI
VIANY
2 rows selected.
SQL >

ARITHMETIC OPERATIONS:
It is used to display mathematically calculated data. These expressions use columns,
numeric values and arithmetic operators.

Operator Use
* Multiplication
/ Division
+ Addition
- Subtraction

ORDER OF OPERATION:
The order is
 Whatever is in parentheses is done first.
 Multiplication and division have higher precedence than addition and
subtraction.
 If more than 1 operator of the same precedence is present, the
operators are performed from left to right.

RESTRICTING DATA WITH A WHERE CLAUSE:

Prepared by K.Gokul HOD of BCA(Dr.R.A.N.M

 lOMoARcPSD|341 746 99

We are familiar with relational and logical operators. The logical operators AND and
OR work with 2 conditions, whereas NOT works with only1 condition. All 3 return a TRUE
or FALSE result.
Logical operator Meaning
AND Returns TRUE only if both conditions are
True
OR Returns TRUE if 1 or both conditions are
True
NOT Returns TRUE if the condition is false

Operator Meaning
IS NULL Is a null value
BETWEEN…..AND Is between a range of values (both
included)
IN Match any value from a list (an alternate
way to write OR)
LIKE Match a value using wild cards

When the relational and logical operators are used together, the order of precedence is as
follows if all operators exist.
Whatever is in parentheses is performed first
Relational operators are performed second
The NOT operator is performed first
The AND operator is performed 4th.
The OR operator is performed last.

WILD CARDS:
We can select rows that match a pattern of characters. Such a search is known as a
wild card search. There are 2 wild cards for a pattern search.

Wild card Use

% Represents zero or more characters
_(underscore) Represents any 1 character

For example
SQL > SELECT name, phone from faculty where name like >C%?;
Name pho

sumi 435
sunil 445
For example,
SQL > SELECT lname from emp where hd like >
%6_?; Lname hd
Sunil 15-apr-90

Prepared by K.Gokul HOD of BCA(Dr.R.A.N.M

 lOMoARcPSD|341 746 99

Sonia 19-apr-98

SORTING:

The ORDER BY clause is used with the SELECT query to sort rows in atable. The
rows can be sorted in ascending or descending order. The rows can be sorted based on 1 or
more columns. The syntax for that is
SELECT column list
FROM table name
[WHERE condition(s)]
[ORDER BY column expression [ASC\DESC]];
ASC stands for ascending order.
DESC stands for descending order.
Type of value Ascending sort order
Numeric Lowest to highest value
Character Alphabetical order
Date Earliest date to latest date

REVISITING SUBSTITUTION VARIABLES:

The substitution variables can be used in statements other than the INSERT statement.
They can substitute for column names, table names, expressions or text. Their use is to
generate queries by inserting them as follows.
In the SELECT statement in place of a column
name In the FROM clause in place of a table name
In the WHERE clause as a column expression or
text As an entire SELECT statement

SQL> SELECT last, first, &&columnname FROM student ORDER BY &columnname;

Enter value for columnname: MajorId

DEFINE COMMAND:

A variable can be defined at the SQL> prompt. The variable is assigned a value that isheld until the user
exits from SQL*Plus or undefines it. The syntax is
DEFINE variable [=value]
For example,
DEFINE Last = Sunil

SELECT * FROM employee WHERE lname=?&Last?;

The DEFINE Last command will return the value of the variable if it already has a value.
Otherwise oracle will display an <UNDEFINED= message.
The variable?s value can be erased with the UNDEFINE command. For example,
UNDEFINE last

Prepared by K.Gokul HOD of BCA(Dr.R.A.N.M

 lOMoARcPSD|341 746 99

CASE STRUCTURE:

CASE structure is allowed anywhere expressions are allowed in SQL statements.

SQL?S CASE structure is similar to the SELECT… ...........CASE statement in VB language
and the switch..................case statement in C++ and java. The syntax is
CASE WHEN condition1 THEN
Expression1
WHEN condiiton2
THEN
Expression2
……………..
[ELSE expression]
END

BUILT-IN FUNCTIONS

Single-Row Functions

The single-row functions take different types of arguments, work on data item from
each row, and return one value for each row. The arguments are in the form of a constant
value, variable name, column, and/or expression. The value returned by a function may be of
a different type than the argument(s) supplied. The general syntax is
Function (column/expression [, argument1, argument2,…])

Where function is the name of the function, column is a column from a table, expression is a
character string or a mathematical expression, and argument is any argument used by the
function
There are various types of single-row functions:
Character functions take a character string or character-type column as an argument and
return a character or numeric value.
Number functions take a number or number-type column as an argument and return a
numeric value.
Date functions take a date value or date-type column as an argument and return date-type
data.(Exception: The MONTHS_BETWEEN functions returns a numeric value)
Conversion functions convert value from one data type to another.
General functions perform different tasks.

Character Functions. The character function perform case conversion or character manipulation .The
case conversion character functions change a string or character-type column data?s case. For
example
UPPER(>Oracle?) ->?ORACLE?
LOWER(>DaTaBaSe SyStEmS?) ->?Database systems?
INITCAP (>DaTaBaSe SyStEmS?) ->?Database systems?
Character Function Use
UPPER(Column/expr) Converts each letter to uppercase
LOWER(Column/expr) Converts each letter to lowercase

Prepared by K.Gokul HOD of BCA(Dr.R.A.N.M

 lOMoARcPSD|341 746 99

INITCAP(Column/expr) Converts character value to the

proper

case

(i.e,first character of each word is

SUBSTR(column/expr,x,y) Returns a substring,sta rting at

character

position x,and returns y number of

CONCAT(column/expr,column/expr) Joins the first value to the second value.
SUBSTR(Column/expr,z) Returns a substring,starting at character
position z and going to the end of string
INSTR(Column/expr,c) Returns the position of the
supplied
LTRIM(Column/expr,c) Removes the leading supplied character
RTRIM(Column/expr,c) Removes the trailer supplied character

TRIM(8c9 FROM column/expr) Returns the leading and trailing

characters
TRIM(column) Returns the leading and trailing
spaces
LENGTH(column/expr) Returns the number of characters
LPAD(column/expr/n,9str9) Pads the value with >str? to the left to
a

RPAD(column/expr/n,9str9) Pads the value with >str? to the right to a

total width of n.
REPLACE(column/expr,c,r) Replaces substring c if present in
the

Numeric Functions .The numeric functions take numeric value(s) and return a numeric
value.The ROUNDfunction rounds the value,expression,or column to n decimal places.If n is
omitted ,zero decimal place is assumed.If n is negative,rounding takes place to the left side of
the decimal place,forexample
ROUND(25.465,2)=25.47
ROUND(25.465,0)=25
Round(25.465,-1)=30
The TRUNC function truncates the value,expression,or column to n decimal places.If
n is not supplied ,zero decimal place is assumed.If n is negative,truncation takes place to the
left side of the decimal place.for example,
TRUNC(25.465,2)=25.46
TRUNC (25.465,0)=25
TRUNC 25.465,-1)=20

The POWER function finds the power of a number (n p).for example

POWER(2,4)=16
POWER(5,3)=125
Prepared by K.Gokul HOD of BCA(Dr.R.A.N.M
 lOMoARcPSD|341 746 99

The ABS function returns the absolute value of a column,expression or value for example

Numeric Function USE

ROUND(column/expr,[n]) Rounds the column or expression to n decimal places
TRUNC(column/expr,[n]) Truncates the column or expression to n decimal
places
POWER(n,P) Returns n raised to power p(np)
ABS(n) Returns the absolute value of n
MOD(x,y) Returns the integer ,remainder of x/y

FLOOR(value) Returns the largest integer less than or qual to value

SIGN(value) Returns 1 for +ve,-1 for –ve and 0 for a zero
CEIL(value) Returns the smallest integer greater tha or equal to
value
The MOD function finds the intger remainder of x divided by y.It ignores the quotient.for
example,
MOD(5,2)=1
MOD(3,5)=3
MOD(8,4)=0

The SIGN function returns -1 for a negative number,1 for a positive number. And 0 for a
zero.for example,
SIGN(-50)=-1
SIGN(+43)=1
SIGN(0)=0
The FLOOR function is similar to the TRUNC function,and theCEILL function is similar to
the ROUND function.However,both take one argument instead of two,for example

FLOOR(54.7)=54
CEIL(54.3)=55

DATE-FUNCTIONS. We already know that Oracle stores dates internally with

day,month,year,century,hour,minute,and second information.The default date display format
is DD-MM-YY.There is a very useful date function called SYSDATE that does not take any
arguments.SYSDATE returns the system?s current date. For example

SELECT SYSDATE FROM DUAL;

Prepared by K.Gokul HOD of BCA(Dr.R.A.N.M

 lOMoARcPSD|341 746 99

SQL>SELECT ROUND(5.55,1),TRUNC(5.5),SIGN(−5.5),MOD(5,2),

2 ABS(−5),POWER(3,4),FLOOR(5.5),CEIL(5.5) FROM DUAL;

5.6 5 −1 1 5 81 5 6

SQL>
Figure 1-Using Numeric Functions

Prepared by K.Gokul HOD of BCA(Dr.R.A.N.M

 lOMoARcPSD|341 746 99

SQL> DESCRIBE DUAL

Name Null? Type

DUMMY VARCHAR2(1)

SQL>SELECT *FROM DUAL;

−−

This Query will display the current date.You can get more information about day,date and
time by using a format mask with SYSDATE function

Date Expression Result

Date + number Adds a number of days to a date

Date + number/24 Adds a number of hours to a date

Date - number Subtracts a number of days from a date
Date1 - Date2 Gives the number of days between two dates
*Date Arithmetic

Date Function Use

MONTHS_BETWEEN(date1,date2) Finds the number of months between two dates

NEXT_DAY(date,9day9) Find the next occurrence of a day from

the
ADD_MONTH(date,m) Adds Calendar months to a date
LAST_DAY(date) Returns the last day of the month
ROUND(date[,format]) Rounds the dates to the nearest
day,month,or

TRUNC(date[,format]) Truncates the date to the nearest day,month,or

year
EXTRACT(YEAR/MONTH/DAY FROM Extracts the year,month or day from the
date
NEXT_TIME(date,existing timezone,new Returns the date in different timez zone such as
timezone) EST or PST

OTHER FUNCTIONS: The NVL function converts a null value to an actual value supplied
as an argument. The second argument is enclosed with in the single quotation marks for
columns with DATE,CHAR, or VARCHAR2 data types.The general syntax is
NVL(column,value)

Prepared by K.Gokul HOD of BCA(Dr.R.A.N.M

 lOMoARcPSD|341 746 99

If the value in a column is null,convert it to a specified value. For example

NVL(commission,0)
NVL(HireDate,901-JAN-039)

NVL(PreReg.9None9)

CONVERSION FUNCTION: The conversion function convert data from one data type to
another .The oracle server follows some rules to convert data type implicit. For example, if
you enter a character string that includes a valid number, the oracle server can successfully
covert CHAR data to NUMBER data.if you enter a date as a string and use the default date
format DD-MON-YY, the Oracle server can perform CHAR-to-DATE conversion
successfully. Its advisable to use explicit data conversion functions for successful and reliable
queries. The three conversion functions shown below are used for explicit data type
conversion in queries

Conversion function Use

TO_CHAR(number/date[,format]) Converts a number or a date to a

VARCHAR2 valuebased on the format

TO_NUMBER(char[,format]) Converts a character value with valid digits
to a number using the format provided
TO_DATE(char[,format]) Converts a character value to date
value

based on the format provided. Default

GROUP FUNCTIONS:The group functions perform an operation on a group of rows and

return one result look at the EMPLOYEE and STUDENT tables
Group Functions Use
SUM(column) Finds the sum of all values in a
column;igno es null values
AVG(column) Finds the average ofall values in a
column;ignores null values
MAX(column/expression) Finds the maximum value; ignores the null

MIN(column/expression) Find the minimum value;ignores the null

values
COUNT(*/column /expression) Counts the number of rows including

nulls,for *;counts non null values if the

GROUPING DATA

The rows in a table can be divided into different groups to treat each
group separately.The group function can be applied to individual groups in the same fashion
they are applied to all rows.the GROUP BY clause is used for grouping data. The general
syntax is

Prepared by K.Gokul HOD of BCA(Dr.R.A.N.M

 lOMoARcPSD|341 746 99

SELECT column,groupfunction(column)
FROM table name [WHERE condition(s)]
[GROUP BY column/expression]
[ORDER BY column/expression [ASC/DESC0];

Important points to remember include:

when you include a group function and the GROUP BY clause in your query , the individual
column(s) appearing the SELECT must also appear in GROUP BY
The WHERE clause can still be used to restrict data before grouping
The WHERE clause can?t be used to restrict groups
A column alias can?t be used in a GROUP BY clause
The GROUP BY column does not have to appear in a SELECT query
When a column is used in the GROUP BY clause,the result is sorted in ascending order by
that column by default . In other words ,GROUP BY has an implied OREDER BY clause
explicitly to change the implied sort order
In oracle 9i the order of the WHERE and GROUP BY clauses in the SELECT query does not
matter,but traditionally ,the WHERE clause is written before the GROUP BY clause
HAVING clause

The HAVING clause can restrict groups.The WHERE clause restricts

rows,the GROUP BY clause groups remaining rows, the group function works on each group
an the HAVING clause keeps the groups that match the group condition.
In the sample query the WHERE clause filters out the building named
kennedy ,the rest of the rows are grouped by the building names Gandhi and Nehru, the
group function COUNT counts the number of rows in each group,and the HAVING clause
keeps groups with four or more rows that is, the Gandhi building with five rows/rooms.
The implied ascending sort with the GROUP BY clause can ve overridden by
adding an explicit ORDER BY clause to the query below figure shows outcome sorted in
ascending order by building column with the GROUP BY clause then it is reversed to
descending order by inserting the ORDER BY clause in line 5

SQL>SELECT Building ,COUNT(*) ROOMS FROM

location

2 WHERE UPPER(building)<>’KENNEDY’

3 GROUP BY building

4 HAVING COUNT(*)>=4;

*HAVING Clause
*GROUP BY sort order changed with ORDER BY
SQL>SELECT Building ,COUNT(*) FROM

location2 GROUP BY building

 lOMoARcPSD|341 746 99

3 HAVING COUNT(*)>2

BUILDING COUNT(*)

JOIN

When the required data are in more than one table, related tables are joined using a
join condition. The join condition combines a row in one table with a row in another table
based on the same values in the common columns. In most cases (but not always), the
common columns are primary key in one table and a foreign key in another table. In this
section, you will be introduces to different types of joins based on the join condition used.

CARTESIAN PRODUCT

A Cartesian product results a multitable query that does not have a WHERE clause.
The product operation joins each row in first table with each row in second table. The product
normally results in an output with a large number of rows and is not very useful. Whenever
retrieving data from more than one table, you must use one or more valid join conditions to
avoid a Cartesian product! You would perform a Cartesian product operation only if you a
looking to find all possible combinations of rows from two tables.
In figure 7-1, you will see an example of a product in which all students and faculty
members are matched unconditionally. All resulting rows are not shown in the figure. The
last and first names are selected from the STUDENT table and a name is selected from the
FACULTY table. There is no join condition issued. The result is 48 rows, because the
product of two table with m and n rows, respectively, returns m * n rows. The STUDENT
table has 6 rows and the FACULTY table has 8 rows, hence the result (6 * 8 rows). If you are
looking for each student?s last name and his or her faculty adviser?s name, you would use a
join condition using the STUDENT table?s foreign key FacultyId and the FACULTY table?s
primary key FacultyId to find matching rows.
The Cartesian product is covered in this section, but it is not a join operation. There is
no join without a join condition. In oracle, you will perform a Cartesian product by not
providing enough join conditions in the SELECT query. Remember that the number of join
conditions is one less than the number of table names used in the FROM clause. There are
four types of joins in Oracle:
1. Equijoin
2. Nonequijoin
3. Outer join
4. Self-join

EQUIJOIN

The equijoin is a join with a join condition involving common columns from two
tables. If you need to get information about a student from STUDENT table and
 lOMoARcPSD|341 746 99

corresponding information about the faculty adviser from the FACULTY table, you would
use the following syntax:

SELECT columnnames
FROM tablenames

WHERE join condition(s);

The column names include columns from both tables separated by commas, table
names are all tables used separated by commas, and join condition is a condition that includes
common columns from each table. The join condition normally (but not always) includes a
foreign key column from one table and referenced primary key column from the other table.
Suppose you want to get a student?s last name, the student?s first name, the faculty adviser?s
name and the faculty adviser?s phone number. You would get them from the STUDENT and
FACULTY tables. The common column in both tables is FacultyId, which is the foreign key
in the child STUDENT table and the primary key the parent FACULTY table. The join
condition will return the requested information from rows in two tables where the FacultyId
value is same. The rows without a match are not selected by the query.
In figure 7-2 you see that all students are picked from STUDENT table, but faculty
members are picked based on the FacultyId in the student rows. The faculty member Collins
(FacultyId = 33) is not selected because there is no match for it in the STUDENT table. On
the other hand, Chang (FacultyId = 555) is picked twice because it appears twice as a value in
the foreign key column of the STUDENT table.
The Cartesian product, as mentioned earlier, is rarely useful, equijoin is a very
important operation in database querying. Another thing to be notes is the use of
tablename.columnname. When columns are retrieved from more than one table, the use of
table name qualifier in front of the column name tells Oracle to retrieve that column from the
specified table. Oracle is pretty smart about it. If a column name exists in only one of the two
tables involved in the query, it is not necessary to use a table name as a qualifier. If a column
exists in both tables, you must use the table name qualifier. Because the join condition
usually has the same column names from two tables, the column names become ambiguous
without a qualifier. The qualifier actually improves performance because you are telling the
Oracle server where to go to find that column. Remember that the two join columns need not
have the same name.
Sometimes, the required information is in more than two tables. In this case, the
FROM clause will include all needed tables, and the WHERE clause will have more than one
join condition. If you need to join n tables, you would need n – 1 join conditions. In our
NamanNavan (N2) Corporation database, an employee?s demographic information is in the
EMPLOYEE table. The EMPLOYEE table has three foreign keys: PositionId, referencing the
POSITION table; QualId, referencing the QUALIFICATION table; and DeptId, referencing
the DEPT table. You would need to join four tables to retrieve information from all those
tables. This means the query will have 4 – 1 = 3 join conditions.
The query will look like the one shown in figure 7 -3. For simplicity, we will join
three tables using two join conditions. There is no limit on the number of join conditions
within a query.
 lOMoARcPSD|341 746 99

The multiple-join example selects information from three tables using a query with
two join conditions. If you look at the query, the table qualifiers are used quite a few times.
There is a way to shorten and simplify this query.

TABLE ALIASES

In chapter 5, you learned about column aliases, which are used for renaming column
headings in a query. Table aliases are used to avoid using lengthy table names over and over
again in a query. A table alias can be from 1 to 30 characters long. Normally very short alias
names are used to shorten the query and save some keystrokes. The table aliases appears in
the FROM clause of the SELECT query. A table name is written, followed by a space, and
then a table alias is supplied. Though they appear after the SELECT clause, alias names can
also be used as qualifiers for column names in the SELECT clause. All table aliases are valid
only in the SELECT query, where they are named and used.
In figure 7-4, you will see the query from figure 7-3 with table aliases. The results
obtained from the queries in figure 7-3 and 7-4 are similar, but the query in figure 7-4 is
shortened by the use of table aliases. In this example, we joined the EMPLOYEE table with
the QUALIFICATION tables instead of the POSITION table. The result contains seven rows
in figure 7-4 instead of eight rows, as in figure 7-3, because one of the employees does not
have a QualId.

ADDITIONAL CONDITIONS

In addition to join conditions, you may use additional conditions using the AND
operator to restrict information. Suppose you want to see the information of figure 7-4 for
employees belonging to department number 10 only. Figure 7-5 shows the use of additional
condition with the AND operator where the information is displayed for DeptId = 10 only.
The three tables are joined for employees in Department 10, which results in three rows
instead of all eight employee rows.

NONEQUIJOIN

There is no matching column in the EMPLEVEL table for the Salary column in the
EMPLOYEE table. The only possible relationship between the two tables is between the
Salary column of the EMPLOYEE table and the LowSalary and HighSalary columns in the
EMPLEVEL table. The join condition for these tables can be written using any operator other
than the = operator. That is why it is called nonequijoin.
The nonequijoin condition of figure 7-6 could have been written as:

e.Salary >= l.LowSalary AND e.Salary <= l.HighSalary;

If you look at the EMPLEVEL table, none of the salaries appears in more than one
level. In other word, there is no overlapping. None of the employees makes a salary that is
not included in the range of salaries. For these two reasons, each employee appears once in
 lOMoARcPSD|341 746 99

result. Note that none of the columns are ambiguous, so table aliases are not necessary
(though they are used here in this example).

OUTER JOIN

You saw in the equijoin that the rows from two tables are selected only if common
column values are the same in both tables. If a row in one table does not have a matching
value in the other table, it is not joined. Figure 7-2 displayed all students from the STUDENT
table and their advisers from the FACULTY table. Some of the faculty members are not any
student?s adviser, so they did not get selected. Suppose you also want to see all those faculty
adviser names. Then, you would change your query?s join condition and create a join known
as outer join.
The table that does not contain the matching values is known as the deficient table. In
our case, the deficient table is the STUDENT table, because it does not contains all the
FacultyIDs. The outer join uses the (+) operator in the join condition on the deficient side
(You will see it soon in figure 7-8, which compares equijoin and outer join on these tables.).
The (+) operator can be used on any side of the join condition, but it cannot be used on both
sides in one condition.

The general syntax is:

SELECT tablename1.columnname, tablename2.columnname

FROM tablename1, tablename2
WHERE tablename1.columname (+)= tablename2.columnname;

The join condition will look different if the (+) operator is used on the right side. For
example,

WHERE tablename1.columnname = tablename2.columnname (+);

Figure 7-7 shows an outer join using the EMPLOYEE and QUALIFICATION tables.
The outer join operator (+) is used on the QUALIFICATION side. Because it is the deficient
table or it generates a null value for the row(s) in the EMPLOYEE table without any
qualification value. The equijoin would have returned seven employees, but the outer join
also includes one extra employee without any qualification.
Figure 7-8 compares outputs from outer join and equijoin on the same tables. The
equijoin returns six students with their faculty advisers? names, whereas the outer join returns
three extra rows with faculty names. The outer join operator (+) is used on the STUDENT
table?s side, because it generates null values for faculty members with no match in the
STUDENT table.

SELF-JOIN

A self-join is joining a table to itself. It sounds meaningless, but think about using it
the following scenario: In the EMPLOYEE table, EmployeeId is the primary key column that
 lOMoARcPSD|341 746 99

describes each entity. For example, EmployeeId 200 represents employee Shaw, Jinku. The
table also has another column called Supervisor, which contains ID?s of employee
supervisors. How can you find name of the supervisor for an employee? You can look up the
supervisor ID, go to EmployeeId column to find its match, and then read the name. This is
easier said than done, however. A self-join is one join that is not so easy to understand.
When a table is joined to itself, two copies of same table are loaded or used. They are
treated like any two different tables, and a join is produced from those two copies. Let us
explain that by using the EMPLOYEE table. The following operations are performed in the
performed in the self-join figure 7-9:

 Two copies of the EMPLOYEE table are created with aliases e and s.
Set operator Use

UNION It returns all rows from both queries,but duplicate rows are not repeated .
UNION ALL It returns all rows from both queries,but displays all duplicate rows.
INTERSECT It returns all rows that appears both queries? result.
MINUS It returns rows that are returned by the first query minus rows that are

 An employee?s last name is picked from the e table, and the corresponding Supervisor
ID is retrieved.
 The matching EmployeeId id found from the s table. The first employee in the e table
does not have a supervisor and so is not picked.
 The last name from the s table is retrieved based on the EmployeeId.

In short, the table is looked at twice, once for the employee and once for the supervisor.
The Indo-US (IU) College database also contains a table, which can be used in self-join.
Table COURSE contains a PreReq column that references its own primary key, CourseId.

SET OPERATORS:

The implementation of the operations like union,intersection,difference etc is through

the use of set operators. The union incompatibility is achieved or the set operations are
performed on results from two independent queries. The output from both queries must return
the same number of columns,and respective columns must have a similar domain.
The general syntax for any set operation is

SELECT-Query1
Set operator
SELECT-Query2;
Where the Set operator is one of the four set operators described below:

returned by the second query.

The set operators UNION,INTERSECTION and MINUS are shown below using venn
diagrams. The left circle represents the first table,and the right circle represents the second
table.The shaded area is the area selected by each operation.The UNION operator selects all
rows from the first table as well as the second table,so both circles are entirely shaded.The
INTERSECTION operator selects rows present in both tables,so the common area is shaded.
 lOMoARcPSD|341 746 99

For example, we are using a table called WORKER which contains staff members of the
college and also student workers.

WorkerId Last First

00110 Borges Luz
00111 Bayer Julia
00103 Rickles Deborah
00113 Marte Noemi
00105 Khan Amir
00107 Feliciano Sandi
 lOMoARcPSD|341 746 99

WORKER Table

Union:
The UNION operator takes output from two queries and returns all rows from both
results.The duplicate rows are displayed only once. If you perform union on two very large
tables,use a where clause to filter rows .All six students rows are selected from the first
query,and four rows are selected from the second query.Two rows from the second query are
duplicate rows(ID 00103 and ID 00105), and they are not repeated.

Union
All: The UNION ALL operation is similar to the UNION operation. The only
difference is that UNION ALL operation also displays duplicate rows. If you find UNION
ALL of the STUDENT and WORKER tables, you will get six rows from the first query and
six rows from the second query (see fig-2).

SQL>SELECT Studentid ID, Last, First FROM student

2 UNION
3 SELECT Workerid, Last, First FROM worker4 /

ID LAST FIRST

00100 Diaz Jose

00101 Tyler Mickey
00102 Patel Rajesh
00103 Rickles Deborah
00104 Lee Brian
00105 Khan Amir
00107 Feliciano Sandi
00110 Borges Luz
00111 Bayer Julia
00113 Marte Noemi

10 rows
selected.

Fig-1: UNION operation

SQL>SELECT Studentid ID, Last, First FROM student

2 UNION ALL
3 SELECT Workerid, Last, First FROM worker
4/
 lOMoARcPSD|341 746 99

ID LAST FIRST

00100 Diaz Jose

00101 Tyler Mickey
00102 Patel Rajesh
00103 Rickles Deborah
00104 Lee Brian
00105 Khan Amir
00110 Borges Luz
00111 Bayer Julia
00103 Rickles Deborah
00113 Marte Noemi
00105 Khan Amir

ID LAST FIRST

00107 Feliciano Sandi

12 rows
selected.

Fig-2: UNION ALL operation.

Intersect:
The INTERSECT operation works on output from two separate queries and
returns rows that appear in both outputs. In the student and worker example, INTERSECT
will return students who are also workers at the college.
In fig-3, you see only two student rows, which are the only students appearing in
the WORKER table.

SQL>SELECT Studentid ID, Last, First FROM student

2 INTERSECT
3 SELECT Workerid, Last, First FROM worker4 /

ID LAST FIRST

00103 Rickles Deborah

00105 Khan Amir

SQL>

Fig-3 : INTERSECT operation.

 lOMoARcPSD|341 746 99

Minus:
The MINUS operation is same as the DIFFERENCE operation. When MINUS is
performed on outputs from two queries, the result is the rows in the first query?s result that
are not in the second query?s result.

Remember that the STUDENT table minus the WORKER table is not the same as the
WORKER table minus the STUDENT table.

Fig-4 is an example of a minus operation in which the result includes students who
are not workers. Fig-5 shows workers who are not students.

SQL>SELECT Studentid ID, Last, First FROM student

2 MINUS
3 SELECT Workerid, Last, First FROM worker4 /

ID LAST FIRST

00100 Diaz Jose

00101 Tyler Mickey

00102 Patel Rajesh

4 rows
selected.

Fig-4 : Student MINUS Worker.

SQL> SELECT Workerid, Last, First FROM worker

2 MINUS
3 SELECT Studentid ID, Last, First FROM student4 /

ID LAST FIRST

00107 Feliciano Sandi

00110 Borges Luz
00111 Bayer Julia
00113 Marte Noemi
 lOMoARcPSD|341 746 99

4 rows selected.

SQL>

Fig-5 : Worker MINUS Student.

Union All

The UNION ALL operation is similar to the UNION operation. The only difference is

that UNION ALL operation also displays duplicate rows. If you find UNION ALL of the STUDENT
and WORKER tables, you will get six rows from the first query and six rows from the second query
(see fig-2).

SQL>SELECT Studentid ID, Last, First FROM student

2 UNION
3 SELECT Workerid, Last, First FROM worker4 /

ID LAST FIRST

00100 Diaz Jose

00101 Tyler Mickey
00102 Patel Rajesh
00103 Rickles Deborah
00104 Lee Brian
00105 Khan Amir
00107 Feliciano Sandi
00110 Borges Luz
00111 Bayer Julia
00113 Marte Noemi

10 rows
selected.

Fig-1: UNION operation

SQL>SELECT Studentid ID, Last, First FROM student

2 UNION ALL
3 SELECT Workerid, Last, First FROM worker4 /

ID LAST FIRST

00100 Diaz Jose

 lOMoARcPSD|341 746 99

00101 Tyler Mickey

00102 Patel Rajesh
00103 Rickles Deborah
00104 Lee Brian
00105 Khan Amir
00110 Borges Luz
00111 Bayer Julia
00103 Rickles Deborah
00113 Marte Noemi
00105 Khan Amir

ID LAST FIRST

00107 Feliciano Sandi

12 rows selected.

SQL>

Fig-2: UNION ALL operation.

Intersect

The INTERSECT operation works on output from two separate queries and
returns rows

that appear in both outputs. In the student and worker example, INTERSECT
will return students whoare also workers at the college.

In fig-3, you see only two student rows, which are the only
students appearing in the WORKER table.

SQL>SELECT Studentid ID, Last, First FROM student

2 INTERSECT
3 SELECT Workerid, Last, First FROM worker
4 /

ID LAST FIRST

00103 Rickles Deborah

00105 Khan Amir

SQL>
 lOMoARcPSD|341 746 99

Fig-3 : INTERSECT operation.

Minus

The MINUS operation is same as the DIFFERENCE operation.

When MINUS is performed on outputs from two queries, the result is the
rows in the first query?s result that are not in the second query?s result.

Remember that the STUDENT table minus the WORKER table is

not the same as the WORKER table minus the STUDENT table.

Fig-4 is an example of a minus operation in which the result

includes students who are notworkers. Fig-5 shows workers who are not
students.
SQL>SELECT Studentid ID, Last, First FROM student

2 MINUS
3 SELECT Workerid, Last, First FROM worker4 /

ID LAST FIRST

00100 Diaz Jose

00101 Tyler Mickey

00102

4 rows
selected.

Fig-4 : Student MINUS Worker.

ID LAST FIRST

00107 Feliciano Sandi

00110 Borges Luz
00111 Bayer Julia
 lOMoARcPSD|341 746 99

00113 Marte Noemi

4 rows selected.

SQL>

Fig-5 : Worker MINUS Student.