UNION SET Operator

The UNION operator returns all distinct rows retrieved by two component queries. The UNION operation eliminates duplicates while merging rows retrieved by either of the component queries. The following example illustrates the UNION operation:

The Syntax of UNION Operator

<Component Query>
UNION
<Component Query>

For Example:

SELECT CUST_NBR, NAME FROM CUSTOMER

WHERE REGION_ID = 5

UNION

 SELECT C.CUST_NBR, C.NAME FROM CUSTOMER C WHERE C.CUST_NBR IN (SELECT O.CUST_NBR FROM CUST_ORDER O, EMPLOYEE E

WHERE O.SALES_EMP_ID = E.EMP_ID  AND E.LNAME = 'MARTIN');

UNION ALL SET Operator

The UNION ALL operator merges the result sets of two component queries. This operation returns rows retrieved by either of the component queries. The following example illustrates the UNION ALL operation:

The Syntax of UNION ALL is

<Component Query>
UNION ALL
<Component Query>

For Example:

SELECT CUST_NBR, NAME FROM CUSTOMER WHERE REGION_ID = 5

UNION ALL

SELECT C.CUST_NBR, C.NAME FROM CUSTOMER C WHERE C.CUST_NBR IN (SELECT O.CUST_NBR  FROM CUST_ORDER O, EMPLOYEE E

WHERE O.SALES_EMP_ID = E.EMP_ID  AND E.LNAME = 'MARTIN');

Equi-join Versus Non-Equi-join

The join condition determines whether the join is an euai-join or a non-equi join. When a join condition relates  two tables by equating the columns from the tables, it is an equi-join. When a join condition relates two tables by an operator other than equality, it is a non-equi-join.

Equi-joins are the most common join type.

For example,

SELECT S.NAME SUPPLIER_NAME, P.NAME PART_NAME

FROM SUPPLIER S, PART P

WHERE S.SUPPLIER_ID = P.SSPPLIER_ID;

There are some situations in which we need non-equi joins to get required information.

For example,

SELECT P.NAME PART_NAME, C.CLASS INV_Class

FROM PART P, INVENTORY_CLASS C

WHERE P.UNIT_COST BETWEEN C.LOW_COST AND C.HIGH_COST;

Set Operators

There are situations when we need to combine the results from two or more SELECT statements. SQL enables us to handle these requirements by using set operations. The result of each SELECT statement can be treated as a set, and SQL set operations can be applied on those sets to arrive at a final result. Oracle SQL supports the following four set operations:

• UNION ALL

• UNION

• MINUS

• INTERSECT

SQL statements containing these set operators are referred to as compound queries, and each SELECT statement in a compound query is referred to as a component query. Two SELECTs can be combined into a compound query by a set operation only if they satisfy the following two conditions:

 1. The result sets of both the queries must have the same number of columns.

 2. The datatype of each column in the second result set must match the datatype of its corresponding column in the first result set.

These conditions are also referred to as union compatibility conditions. The term union compatibility is used even though these conditions apply to other set opera tions as well. Set operations are often called vertical joins, because the result com- bines data from two or more SELECTS based on columns instead of rows. The generic syntax of a query involving a set operation is:

<component query>

{UNION | UNION ALL | MINUS | INTERSECT}

<component query>

The following list briefly describes the four set operations supported by Oracle SQL:

UNION ALL Combines the results of two SELECT statements into one result set.

UNION Combines the results of two SELECT statements into one result set, and then eliminates any duplicate rows from that result set.

MINUS Takes the result set of one SELECT statement, and removes those rows that are also returned by a second SELECT statement.

INTERSECT Returns only those rows that are returned by each of two SELECT statements.

Date and Time Intervals

Date and time interval data are an integral part of our day-to-day life. Common examples of interval data are the age of a person, the maturity period of a bond or certificate of deposit, and the warranty period of your car. Prior to Oracle9i, we all used the NUMBER datatype to represent such data, and the logic needed to deal with interval data had to be coded at the application level. Oracle9i provides two new datatypes to handle interval data:

• INTERVAL YEAR TO MONTH

• INTERVAL DAY TO SECOND

The following sections discuss the use of these datatypes.

INTERVAL YEAR TO MONTH

The INTERVAL YEAR TO MONTH type stores a period of time expressed as a num- ber of years and months. An INTERVAL YEAR TO MONTH datatype is specified as:

INTERVAL YEAR [ (precision for year) ] TO MONTH The precision specifies the number of digits in the year field. The precision can range from 0 to 9, and the default value is 2. The default precision of two allows for a max- imum interval of 99 years, 11 months.

The following example creates a table with INTERVAL YEAR TO MONTH datatype:

CREATE TABLE EVENT_HISTORY ( EVENT_ID NUMBER(10),

EVENT_DURATION INTERVAL YEAR TO MONTH);

INTERVAL DAY TO SECOND

The INTERVAL DAY TO SECOND type stores a period of time expressed as a num- ber of days, hours, minutes, seconds, and fractions of a second. An INTERVAL DAY TO SECOND datatype is specified as:

INTERVAL DAY [(precision for day)] TO SECOND [(precision for fractional seconds)]

The precision for day specifies the number of digits in the day field. This precision can range from 0 to 9, and the default value is 2. The precision for fractional seconds is the number of digits in the fractional part of second. It can range from 0 to 9, and the default value is 6.

Rounding and Truncating Dates

Rounding and truncating dates is similar in concept to the rounding and truncating of numbers, but more involved because an Oracle DATE contains date as well as time information. Use the ROUND function to round a date/time value to a specific element; use the TRUNC function to truncate a date/time value to a specific ele- ment. Following is the syntax for invoking these two functions:

ROUND(date [, format]) TRUNC(date [, format]) The syntax elements are:

date

Specifies a DATE value. format Specifies the date element to round or truncate to.

The return value depends upon the specified format, which is an optional parame- ter. If we don’t specify a format in the call to ROUND, the function returns a date by rounding the input to the nearest day. If we don’t specify a format in the call to TRUNC, that function returns a date by removing the fractional part of the day.

When using ROUND and TRUNC to round to the nearest day, or to truncate a date, the functions set the time fields of the return value to the beginning of the returned day, i.e., 12:00:00 AM (00:00:00 in HH24 format).

For example:

SELECT TO_CHAR(SYSDATE, 'DD-MON-YY HH:MI:SS AM'),     

 TO_CHAR(ROUND(SYSDATE), 'DD-MON-YY HH:MI:SS AM'),      

TO_CHAR(TRUNC(SYSDATE), 'DD-MON-YY HH:MI:SS AM');

Last Day of the Month and Next Day Function

Oracle provides a built-in function to get the last day of a month. The function is LAST_DAY,and it’s called as follows:

LAST_DAY (date)

The syntax element is:

date

Specifies a DATE value, or a string with a date in the default date format.

LAST_DAY returns the last day of the month containing the input date. For example, to find the last date of the current month, we can use the following SQL statement:

SELECT LAST_DAY(SYSDATE) "Next Payment Date" FROM DUAL;

Next Day

Oracle provides a built-in function to get the date of the next occurrence of a specified day of the week. The function is NEXT_DAY, and it’s called as follows:

NEXT_DAY (date, string) The syntax elements are:

date

Specifies a DATE value, or a string with a date in the default date format. string Specifies the name of a weekday.

To find the date of the next Friday, we can use the following SQL statement:

SELECT NEXT_DAY(SYSDATE, 'Friday') "Vacation Start Date" FROM DUAL;

Self joins

There are situations in which one row of a table is related to another row of the same table. The EMPLOYEE table is a good example. The manager of one employee is also an employee. The rows for both are in the same EMPLOYEE table. This relationship is indicated in the MANAGER_EMP_ID column:

CREATE TABLE EMPLOYEE ( EMP_ID NUMBER (4) NOT NULL PRIMARY KEY,

FNAME VARCHAR2 (15),

LNAME VARCHAR2 (15),

DEPT_ID NUMBER (2),

MANAGER_EMP_ID  NUMBER (4) REFERENCES EMPLOYEE(EMP_ID),

SALARY   NUMBER (7,2),

 HIRE_DATE DATE,

JOB_ID NUMBER (3));

To get information about an employee and his manager, we have to join the EMPLOYEE table with itself. This is achieved by specifying the EMPLOYEE table twice in the FROM clause and using two different table aliases, thereby treating EMPLOYEE as if it were two separate tables. The following example lists the name of each employee and his manager:

SELECT E.NAME EMPLOYEE, M.NAME MANAGER

FROM EMPLOYEE E, EMPLOYEE M

WHERE E.MANAGER_EMP_ID = M.EMP_ID;

GRANT Command

DCL commands are used to enforce database security in a multiple user database environment. Two types of DCL commands are GRANT and REVOKE. Only Database Administrator's or owner's of the database object can provide/remove privileges on a database object.

SQL GRANT Command

SQL GRANT is a command used to provide access or privileges on the database objects to the users.

The Syntax for the GRANT command is:

GRANT Privilege_name

ON Object_name
TO {user_name | PUBLIC | role_name}
[WITH GRANT OPTION];

• privilege_name is the access right or privilege granted to the user. Some of the access rights are ALL, EXECUTE, and SELECT.
• object_name is the name of an database object like TABLE, VIEW, STORED PROC and SEQUENCE.
• user_name is the name of the user to whom an access right is being granted.
• PUBLIC is used to grant access rights to all users.
• ROLES are a set of privileges grouped together.
• WITH GRANT OPTION - allows a user to grant access rights to other users.

For Example

GRANT SELECT ON TABLE MyTable TO Victor

MONTHS_BETWEEN Function

In most cases, to find the number of months of experience rather than the number of days. We know that dividing the number of days between two dates by 30 won’t accurately calculate the number of months between those two dates. There- fore, Oracle provides the built-in SQL function MONTHS_BETWEEN for finding the number of months between two dates. MONTHS_BETWEEN is called as follows:

MONTHS_BETWEEN (date1, date2)

The syntax elements are:

date1 Specifies the end of the time period in question. This should be either a DATE value or a string in the default date format.

date2 Specifies the beginning of the time period in question. Like date1, this should also be a DATE value or a string in the default date format.

For Example:

Select MONTHS_BETWEEN('01-JAN-01','01-AUG-01') AS MONTHS_BETWEEN FROM Dual;

REVOKE Command

The REVOKE command removes user access rights or privileges to the database objects.

The Syntax for the REVOKE command is:

REVOKE privilege_name
ON object_name
FROM {user_name |PUBLIC |role_name} 

For Example

REVOKE UPDATE

ON SALES

FROM BOB;

ADD_MONTHS Function

Adding months to a date is not as easy as adding weeks, because all months don’t have the same number of days some have 30, some 31, some 28, and at times even 29. To add one month to a date, we need to know how many days that calendar month will have. Therefore, adding months to a date by converting those months to a number of days involves lots of homework, which is error-prone. Fortunately, Oracle provides a built-in SQL function to add months to dates. This function is called ADD_MONTHS, and we call it as follows:

ADD_MONTHS (date, number)

The syntax elements are:

date

Specifies a database column defined as type DATE or a string with a date in the default date format.

Number

Specifies the number of months to add to the input date.

The following example shows the computation of an employee’s biannual review date by using ADD_MONTHS to add six months to the employee’s HIRE_DATE:

SELECT FNAME, HIRE_DATE, ADD_MONTHS(HIRE_DATE, 6) REVIEW_DATE FROM EMPLOYEE;

Inner Joins and Cartesian Product

Inner joins

An inner join returns the rows that satisfy the join condition. For example say we want to list the name and department name for each employee. To do this we would use the following query:

SELECT E.FNAME, E.LNAME, D.NAME

FROM EMPLOYEE E, DEPARTMENT D

WHERE E.DEPT_ID = D.DEPT_ID;

Cartesian Product

If we don’t specify the join condition while joining the tables, oracle combines each row from the first table with each row of the second table. This type of result set is called as a Cartesian product.

For example:

SELECT E.FNAME, E.LNAME, D.NAME

FROM EMPLOYEE E, DEPARTMENT D;

Outer Joins

Sometimes while performing a join between two tables, we need to return all the rows from one table even when there are no corresponding rows in the other table. Consider the following two tables,

Oracle provides a special type of join to include rows from one table that don’t have matching rows from the other table. This type of join is known as Outer join.

The syntax of the outer join is a bit different from that of the inner join, because it includes a special operator called the outer join operator. The outer join operator is a plus sign enclosed in parentheses, i.e., (+). This operator is used in the join condition in the WHERE clause following a field name from the table that we wish to be considered the optional table. In our suppliers and parts example, the PART table doesn’t have information for one supplier. Therefore, we will simply add a (+) operator to the join condition on the side of the PART table. The query and the result set look as follows:

SELECT S.SUPPLIER_ID, S.NAME SUPPLIER_NAME, P.PART_NBR, P.NAME PART_NAME

FROM SUPPLIER S, PART P

 WHERE S.SUPPLIER_ID = P.SUPPLIER_ID (+);

Restrictions on outer join

•  The outer join operator can appear on only one side of an expression in the join condition.
•  If a join involves more than two tables, then one table can’t be outer joined with more than one other table in the query.An outer join condition containing the (+) operator may not use the IN operator
•  An outer join condition containing the OR operator may not be combined with another condition using the OR operator.
• A condition containing the (+) operator may not involve a subquery.

Date Manipulation

Date arithmetic is an important aspect of our day-to-day life. We find the age of a person by subtracting his date of birth from today’s date. We compute the date a warranty expires by adding the warranty period to the purchase date. Drivers’ license expirations, bank interest calculation, and a host of other things all depend on date arithmetic. It is extremely important for any database to support such common date arithmetic operations.

Date Functions:

Function	Use
ADD_MONTHS	Adds months to a date
LAST_DAY	Computes the last day of the month
MONTHS_BETWEEN	Determines the number of months between two dates
NEW_TIME	Translates a time to a new time zone
NEXT_DAY	Returns the date of the next specified weekday
ROUND	Rounds a date/time value to a specified element
SYSDATE	Returns the current date and time
TO_CHAR	Converts dates to strings
TO_DATE	Converts strings and numbers to dates
TRUNC	Truncates a date/time value to a specific element

Addition

Adding two dates doesn’t make sense. However, we can add days, months, years, hours, minutes, and seconds to a date to generate a future date and time. The “+” operator allows us to add numbers to a date. The unit of a number added to a date is assumed to be days. Therefore, to find tomorrow’s date, we can 

add 1 to SYSDATE:

SELECT SYSDATE, SYSDATE+1 FROM DUAL;

The SELECT Statement

Select statement retrieves data from database. The set of data retrieved via select statement is referred to as result set.

Syntax:

Select <one or more Column names>

From <One or more table names>

Where <one or more condition apply>

The result sets generated by queries contains columns from one or more tables. While most elements in SELECT clauses will typically be simple column references, a SELECT clause may also include:

• Literal Values, such as numbers (1) or strings (‘abc’)
• Expressions, such as length * 3.14
• Functions, such as TO_DATE(’01-Jan-2013’,’DD-MON-YYYY)
• Pseudo columns, such as ROWID, ROWNUM or LEVEL

     For example

SELECT 1 num, 'abc' str

FROM customer;

Date Literals

Date Literals

DATE literals are specified in the ANSI standard as a way of representing date con- stants, and take the following form:

DATE 'YYYY-MM-DD' Note that the ANSI date literal doesn’t contain the time information. We also can’t specify a format. If we want to specify a date literal using this ANSI syntax, we must always use the YYYY-MM-DD date format.

The following example illustrates the use of a DATE literal in a SQL statement:

INSERT INTO EMPLOYEE (EMP_ID, FNAME, LNAME, DEPT_ID, MANAGER_EMP_ID, SALARY, HIRE_DATE) VALUES (2304, 'John', 'Smith', 20, 1258, 20000, DATE '1999-10-22');

AD/BC indicators and AM/PM Indicators

Oracle provides two formats, AD and BC, to characterize a year.  However, they both serve the same purpose, and we can use either of them with equivalent results. If we have used the format BC in our query, and the date we are applying this format to comes out to be an AD year, Oracle is intelligent enough to print AD instead of BC, and vice versa. 

For example:

SELECT TO_CHAR(SYSDATE, 'YYYY AD'),  TO_CHAR(SYSDATE, 'YYYY BC') FROM DUAL;

Another Example:

SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-50000), 'YYYY AD'),      

TO_CHAR(ADD_MONTHS(SYSDATE,-50000), 'YYYY BC') FROM DUAL;

AM/PM indicators

The AM/PM indicators (as well as A.M. and P.M.) behave exactly the same as the AD/BC indicators. If we have used the AM format in our query, and the time we are applying this format to comes out to be a PM time, Oracle is intelligent enough to print PM instead of AM, and vice versa. 

For example:

SELECT TO_CHAR(SYSDATE, 'HH:MI:SS AM'),      

TO_CHAR(SYSDATE, 'HH:MI:SS PM'),      

TO_CHAR(SYSDATE - 8/24, 'HH:MI:SS AM'),     

TO_CHAR(SYSDATE - 8/24, 'HH:MI:SS PM') FROM DUAL;

MINUTES: MI or MM

Many SQL beginners assume that since HH represents hours and SS represents sec- onds, MM would represent minutes, and try to write the following SQL queries to print the current time:

SELECT TO_CHAR(SYSDATE, 'HH:MM:SS') FROM DUAL;

RR Year format

With the RR year format, the first two digits of the specified year are determined based upon the last two digits of the current year and the last two digits of year specified.

The following rules apply:

• If the specified year is less than 50, and the last two digits of the current year are less than 50, then the first two digits of the return date are the same as the first two digits of the current date.

• If the specified year is less than 50, and the last two digits of the current year are greater than or equal to 50, then first two digits of the return date are 1 greater than the first two digits of the current date.

• If the specified year is greater than 50, and the last two digits of the current year are less than 50, then first two digits of the return date are 1 less than the first two digits of the current date.

• If the specified year is greater than 50, and the last two digits of the current year are greater than or equal to 50, then the first two digits of the return date are the same as the first two digits of the current date.

The following example demonstrates these rules:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR';

SELECT SYSDATE, TO_CHAR(SYSDATE,'DD-MON-YYYY') FROM DUAL;

TO_CHAR Function

The TO_CHAR function is the opposite of the TO_DATE function, and converts a date into a string of characters. Call TO_CHAR as follows:

TO_CHAR(date [,format])

The syntax elements are:

date

Specifies a PL/SQL variable or a database column of the DATE datatype. format Specifies the desired format of the output string. The format must be a valid com- bination of date format elements as described later in the section “Date Formats.”

The format is optional. When the format is not specified, the date is output in the default date format (as specified by NLS_DATE_FORMAT).

The following example uses TO_CHAR to convert an input date into a string using the default date format:

SELECT FNAME, TO_CHAR(HIRE_DATE) FROM EMPLOYEE;

Date Formats

We can display dates in a number of ways. Every country, every industry has its own standard of displaying dates. Oracle provides us with date format codes so that we can interpret and display dates in a wide variety of date formats.

A simple example of displaying a date is:

SELECT SYSDATE FROM DUAL;

By default, the date is displayed using the DD-MON-YY format. Let’s take another example to see how we can display a date in a format other than the default format:

SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY') FROM DUAL;

The example converts the date into the format 'MM/DD/YYYY' with the TO_CHAR function. There are many ways to represent a date.

Key-preserved table

A key-preserved table is the most important requirement in order for a join view to be modifiable. In a join, a table is called a key-preserved table if its keys are preserved through the join every key of the table can also be a key of the resultant join result set. Every primary key or unique key value in the base table must also be unique in the result set of the join. 

For example

CREATE VIEW V_RTLR_EMP AS

SELECT C.RTLR_NBR, C.NAME, C.CITY, E.EMP_ID, E.LNAME SALES_REP

FROM RETAILER C, EMPLOYEE E

WHERE C.SALESPERSON_ID = E.EMP_ID;

The view V_RTLR_EMP is a join of RETAILER and EMPLOYEE tables on the RETAILER.SALESPERSON_ID and EMPLOYEE.EMP_ID columns.

We must remember the following important points regarding key-preserved tables:

•  Key-preservation is a property of the table inside the join view, not the table itself independently.
• A table may be key-preserved in one join view, and may not be key-preserved in another join view. On the other hand, if we select the key column(s) of a table in the view definition, it doesn’t make that table key-preserved. In the V_RTLR_EMP view, even though we have included EMP_ID in the SELECT list, the EMPLOYEE table is not key-preserved.
• The key-preserved property of a table in a join view doesn’t depend on the data inside the table. It depends on the schema design and the relationship between the tables.