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.

WHERE Clause Evaluation

 The WHERE clause consists of one or more conditions that evaluate independently to TRUE or FALSE. If your WHERE clause consists of multiple conditions, the conditions are separated by the logical operators AND and OR. Depending on the outcome of the individual conditions and the placement of these logical operators, Oracle will assign a final value of TRUE or FALSE to each candidate row, thereby determining whether a row will be included in the final result set.

The WHERE clause consists of two conditions separated by AND. Thus, a row will only be included if both conditions evaluate to TRUE.

Intermediate result  Final result

WHERE TRUE AND TRUE TRUE

WHERE FALSE AND FALSE FALSE

WHERE FALSE AND TRUE FALSE

WHERE TRUE AND FALSE FALSE

Demonstrates the possible outcomes if our conditions had been separated by OR rather than AND.

Intermediate result  Final result

WHERE TRUE OR TRUE TRUE

WHERE FALSE OR FALSE FALSE

WHERE FALSE OR TRUE TRUE

WHERE TRUE OR FALSE TRUE

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;

Specifying a date format

If we wish to specify a date format, there are at least two approaches we can take:

• Specify the format at the session level, in which case it applies to all implicit con- versions, and to all TO_DATE conversions for which we do not explicitly spec- ify some other format.

• Specify the format as a parameter to a TO_DATE call. The following example changes the default date format for the session, and then uses TO_DATE to convert a number to date.

  

ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYY';

Session altered.

INSERT INTO EMPLOYEE (EMP_ID, FNAME, LNAME, DEPT_ID, MANAGER_EMP_ID, SALARY, HIRE_DATE) VALUES (2304, 'John', 'Smith', 20, 1258, 20000, TO_DATE(102299));

Conditions and Expressions

A condition is comprised of one or more expressions along with one or more operators. Examples of expressions include:

•  Numbers such as 1,2,3
•  Columns such as cust_name
•  Literals such as (‘ABC’)
•  Functions UPPER (‘abc’)
•  List of expressions such as (1,2,3)
•  Subqueries

Examples of Operator include:

•  Arithmetic operators, such as +,-,* and /
• Comparison Operators, such as =,<,>=,!=, Like and In

Equality/Inequality Conditions

Most of the conditions that we use when constructing a WHERE clause will be equality conditions used to join data sets together or to isolate specific values. including:

s.supplier_id = p.supplier_id

s.name = 'Acme Industries'

supplier_id = (SELECT supplier_id  FROM supplier  WHERE name = 'Acme Industries')

In all three cases, we have a column expression followed by a comparison operator (=) followed by another expression. The conditions differ in the type of expression on the right side of the comparison operator. The first example compares one column to another, the second example compares a column to a literal, and the third example compares a column to the value returned by a subquery.

We can also build conditions that use the inequality comparison operator “!=”. Using the != operator rather than using NOT makes the query easier to understand and removes the need for the OR operator:

SELECT p.part_nbr, p.name, p.supplier_id, p.status, p.inventory_qty,  s.supplier_id, s.name

 FROM part p, supplier s

WHERE s.supplier_id = p.supplier_id  AND s.name != 'Acme Industries'  AND s.name != 'Tilton Enterprises';

While this is an improvement over the previous version, the next section shows an even cleaner way to represent the same logic.

Membership Conditions

Determining whether two expressions are identical, it is often useful to determine whether one expression can be found within a set of expressions. Using the IN operator, we can build conditions that will evaluate to TRUE if a given expression exists in a set of expressions:

s.name IN ('Acme Industries', 'Tilton Enterprises')

we may also add the NOT operator to determine whether an expression does not exist in a set of expressions:

s.name NOT IN ('Acme Industries', 'Tilton Enterprises')

SELECT part_nbr, name, supplier_id, status, inventory_qty

FROM part

WHERE  name NOT IN ('Acme Industries', 'Tilton Enterprises');

Range Conditions

Dealing with dates or numeric data, we may be look in whether a value falls within a specified range rather than whether it matches a specific value or exists in a finite set. For such cases, we may use the BETWEEN…AND operator, as in:

DELETE FROM cust_order 

WHERE order_dt BETWEEN '01-JUL-2001' AND '31-JUL-2001';

To determine whether a value lies outside a specific range, you can add the NOT operator:

SELECT order_nbr, cust_nbr, sale_price FROM cust_order WHERE sale_price NOT BETWEEN 1000 AND 10000;

When using BETWEEN, make sure the first value is the lowest of the two values provided. Ranges may also be specified using the operators <, >, <=, and >=, although doing so requires writing two conditions rather than one. The previous query could also be expressed as:

SELECT order_nbr, cust_nbr, sale_price FROM cust_order WHERE sale_price < 1000 OR sale_price > 10000;

Matching Conditions

When dealing with character data, there are some situations where you are looking for an exact string match, and others where a partial match is sufficient. For the latter case, we can use the LIKE operator along with one or more pattern-matching characters, as in:

DELETE FROM part 

WHERE part_nbr LIKE 'ABC%';

The pattern-matching character “%” matches strings of any length, so all of the following part numbers would be deleted: 'ABC', 'ABC-123', 'ABC9999999'. If you need finer control, you can use the underscore (_) pattern-matching character to match single characters, as in:

DELETE FROM part WHERE part_nbr LIKE '_B_';

For this pattern, any part number with exactly 3 characters with a B in the middle would be deleted. Both pattern-matching characters may be utilized in numerous combinations to find the desired data.

Handling Temporal Data

A database needs to effectively and efficiently handle the storage, retrieval, and manipulation of the following types of temporal data:

• Dates

• Times

• Date and time intervals

• Time zones

Oracle’s support for temporal data is mature and efficient. Oracle8i supports convenient manipulation of date and time data. Oracle9i enhanced this support by introducing a new set of features including the support for fractional seconds, date and time intervals, and time zones.

Internal DATE Storage Format

Oracle’s DATE datatype holds date as well as time information. Regardless of the date format we use, Oracle stores dates internally in one standard format. Internal to the database a date is a fixed-length, seven-byte field. The seven bytes represent the following pieces of information:

1. The Century

2. The Year

3. The Month

4. The Day

5. The Hour

6. The Minute

7. The Second

Even though the datatype is called a DATE, it also stores the time. We choose the components to display (the date, the time, the date and the time, etc.) when we retrieve a DATE value from the database. Or, if we are fetching a DATE value into a program (e.g., a Java program) we might choose to extract the date ele- ments of interest after transferring the entire date/time value to that program.

Oracle provides two extremely useful functions to convert dates:

• TO_DATE

• TO_CHAR

As their names suggest, TO_DATE is used to convert character data, or numeric data, into a DATE value, and TO_CHAR is used to convert a DATE value into a string of characters.

Oracle SQL CASE Expressions and COALESCE Function

CASE EXPRESSIONS

A searched CASE expression evaluates a number of conditions and returns a result determined by which condition is true. The syntax for the SEARCHED CASE expression is as follows:

CASE 

WHEN C1 THEN R1 

WHEN C2 THEN R2

 ... 

WHEN CN THEN RN  ELSE RD

END

In the syntax definition, the “C”s represent conditions, and the “R”s represent results.

For Example:

SELECT p.part_nbr part_nbr, p.name part_name, s.name supplier,  CASE p.status   

WHEN 'INSTOCK' THEN 'In Stock'    WHEN 'DISC' THEN 'Discontinued'   
WHEN 'BACKORD' THEN 'Backordered'   

WHEN 'ENROUTE' THEN 'Arriving Shortly'   

WHEN 'UNAVAIL' THEN 'No Shipment Scheduled'   

ELSE 'Unknown' 

END part_status

FROM part p, supplier s

WHERE p.supplier_id = s.supplier_id;

COALESCE

The coalesce function returns the first non-null expression in the list. If all expressions evaluate to null, then the coalesce function will return null. The coalesce function will compare each value, one by one.

Syntax

COALESCE (Expr1,Expr2,…Exprn)

Example

Select COALESCE( address1, address2,address3) result

FROM Suppliers;

Oracle SQL DECODE, NVL and NVL 2 Functions

The DECODE function can be thought of as an inline IF statement. DECODE takes four or more expressions as arguments.

Syntax Of the DECODE

DECODE(Expr1, Expr2, Expr3, Expr4)

IF Expr1 = Expr2 THEN Expr3 ELSE Expr4

Expressions can be a column, a literal, a function, or even a subquery.

Let’s look at a simple example using DECODE:

SELECT lname,  DECODE(manager_emp_id, NULL, 'MANAGER', 'NON-MANAGER') emp_type

FROM employee;

NVL and NVL2

The NVL and NVL2 functions allow you to test an expression to see whether it is NULL. If an expression is NULL, you can return an alternate, non-NULL value, to use in its place. Since any of the expressions in a DECODE statement can be NULL, the NVL and NVL2 functions are actually specialized versions of DECODE.

Syntax of NVL2 Function

NVL2(Expr1, Expr2, Expr3)

IF Expr1 IS NULL THEN Expr3 ELSE Expr2

Example for NVL2:

SELECT lname,  NVL2(manager_emp_id, 'NON-MANAGER', 'MANAGER') emp_type

FROM employee;

Syntax of NVL

NVL(Expr1,Expr2)

IF Expr1 is NULL THEN Expr2

  

Example:

SELECT supplier_id

NVL(supplier_desc, supplier_name)

FROM suppliers;

Data Set Fabrication

Along with querying existing tables, inline views may be used to fabricate special- purpose data sets that don’t exist in the database. For example, we might want to aggregate orders over the last year by small, medium, and large orders, but the concept of order sizes may not have been defined in our database. We could build a table with three records to define the different sizes and their boundaries, but we only need this information for a single query, and we don’t want to clutter our data- base with dozens of small, special-purpose tables. One solution is to use set operators like UNION to construct a custom-built data set, as in:

SELECT 'SMALL' name, 0 lower_bound, 999 upper_bound

from dual UNION ALL

(SELECT 'MEDIUM' name, 1000 lower_bound, 24999 upper_bound from dual UNION ALL SELECT 'LARGE' name, 25000 lower_bound, 9999999 upper_bound from dual);

Inline Views

Most texts covering SQL define the FROM clause of a SELECT statement as containing a list of tables and/or views. In the following  query the FROM clause contains a list of data sets. In this light, it is easy to see how the FROM clause can contain tables (permanent data sets), views (virtual data sets), and SELECT statements (temporary data sets).

A SELECT statement in the FROM clause of a containing SELECT statement is referred to as an inline view it is one of the most powerful, underutilized features of Oracle SQL. Here’s a simple example:

SELECT d.dept_id, d.name, emp_cnt.tot

FROM department d, (SELECT dept_id, COUNT(*) tot  FROM employee  GROUP BY dept_id) emp_cnt WHERE d.dept_id = emp_cnt.dept_id;

 In this example, the FROM clause references the department table and an inline view called emp_cnt, which calculates the number of employees in each department. The two sets are joined using dept_id and the ID, name, and employee count are returned for each department. While this example is fairly simple, inline views allow us to do things in a single query that might otherwise require multiple select statements or a procedural language to accomplish.

Correlated Subqueries

A subquery that references one or more columns from its containing SQL statement is called a correlated subquery. A correlated subquery is executed once for each candidate row in the intermediate result set of the containing query.

For example, consider the following query, which locates all parts supplied by Acme Industries that have been purchased ten or more times since December:

SELECT p.part_nbr, p.name FROM supplier s, part p WHERE s.name = 'Acme Industries'  AND s.supplier_id = p.supplier_id  AND 10 <=   (SELECT COUNT(*)   

FROM cust_order co, line_item li  

 WHERE li.part_nbr = p.part_nbr  AND li.order_nbr = co.order_nbr      AND co.order_dt >= TO_DATE('01-DEC-2001','DD-MON-YYYY'));

The reference to p.part_nbr is what makes the subquery correlated;  values for p. part_nbr must be supplied by the containing query before the subquery can execute. If there are 10,000 parts in the part table, but only 100 are supplied by Acme Industries, the subquery will be executed once for each of the 100 rows in the intermediate result set created by joining the part and supplier table.

The UPDATE Statement

Modifications to existing data are handled by the UPDATE statement.  the UPDATE statement includes a WHERE clause in order to specify which rows should be targeted.

Syntax:

UPDATE table_name

SET column1 = value, column2 = value

WHERE some_column = some_value;

For exmple.

UPDATE customer

SET city = ‘Hamburg’

Where cust_name = ‘John’ AND Country = ‘Germany’

Non-correlated Subqueries

Non-correlated subqueries allow each row from the containing SQL statement to be compared to a set of values. Non-correlated subqueries further divided into the following three categories, depending on the number of rows and columns returned in their result set:

• Single-row, single-column subqueries

• Multiple-row, single-column subqueries

• Multiple-column subqueries

Depending on the category, different sets of operators may be employed by the containing SQL statement to interact with the subquery.

Single Row, single-column subqueries

A subquery that returns a single row with a single column is treated like a scalar by the containing statement, these types of subqueries are known as scalar subqueries. The subquery may appear on either side of a condition, and the usual comparison operators (=, <, >, !=, <=, >=) are employed. The following query illustrates the utility of single-row, single-column subqueries by finding all employees earning an above-average salary. The subquery returns the average salary, and the containing query then returns all employees who earn more than that amount.

SELECT lname

FROM employee

WHERE salary > (SELECT AVG(salary)

                                     FROM EMPLOYEE);

subqueries can and can’t be utilized in SQL statements:

• The FROM clause may contain any type of non-correlated subquery.

• The SELECT and ORDER BY clauses may contain scalar subqueries.

• The GROUP BY clause may not contain subqueries.

• The START WITH and CONNECT BY clauses, used for querying hierarchical data, may contain subqueries.

Multiple Row subqueries

When a subquery returns more than one row, it is not possible to use only comparison operators, since a single value cannot be directly compared to a set of values. However, a single value can be compared to each value in a set. To accomplish this, the special keywords ANY and ALL may be used with comparison operators to determine if a value is equal to (or less than, greater than, etc.) any members of the set or all members of the set. Consider the following query:

SELECT fname, lname

FROM employee

WHERE dept_id = 3 AND salary >= ALL

                            (SELECT salary  FROM employee  WHERE dept_id = 3);

The next query uses the ANY operator to find all employees whose salary exceeds that of any top-level manager:

SELECT fname, lname

FROM employee WHERE manager_emp_id IS NOT NULL  AND salary > ANY

(SELECT salary  FROM employee  WHERE manager_emp_id IS NULL);

Multiple-Column Subqueries

While all of the previous examples compare a single column from the containing SQL statement to the result set returned by the subquery, it is also possible to issue a subquery against multiple columns. Consider the following UPDATE statement, which rolls up data from an operational table into an aggregate table:

UPDATE monthly_orders SET  tot_orders = (SELECT COUNT(*)   

FROM cust_order    

WHERE order_dt >= TO_DATE('01-NOV-2001','DD-MON-YYYY')   AND

order_dt < TO_DATE('01-DEC-2001','DD-MON-YYYY') AND

cancelled_dt IS NULL),  max_order_amt = (SELECT MAX(sale_price)

FROM cust_order  

 WHERE order_dt >= TO_DATE('01-NOV-2001','DD-MON-YYYY') AND 

order_dt < TO_DATE('01-DEC-2001','DD-MON-YYYY') AND 

cancelled_dt IS NULL),

min_order_amt = (SELECT MIN(sale_price)    

FROM cust_order    

WHERE order_dt >= TO_DATE('01-NOV-2001','DD-MON-YYYY')      AND order_dt < TO_DATE('01-DEC-2001','DD-MON-YYYY')  AND

cancelled_dt IS NULL),  tot_amt = (SELECT SUM(sale_price)   

 FROM cust_order    

WHERE order_dt >= TO_DATE('01-NOV-2001','DD-MON-YYYY')  AND

 order_dt < TO_DATE('01-DEC-2001','DD-MON-YYYY') AND cancelled_dt IS NULL) 

WHERE month = 11 and year = 2001;

Subqueries

A subquery is a SELECT statement that is nested within another SQL statement. Subqueries are executed prior to execution of the containing SQL statement, and the result set generated by the subquery is discarded after the containing SQL statement has finished execution. Thus, a subquery can be thought of as a temporary table with statement scope.

Syntactically, subqueries are enclosed within parentheses. For example, the following SELECT statement contains a simple subquery in its WHERE clause:

SELECT * FROM customer

WHERE cust_nbr = (SELECT 123 FROM dual);

The subquery in this statement is absurdly simple, and completely unnecessary, but it does serve to illustrate a point. When this statement is executed, the subquery is evaluated first. The result of that subquery then becomes a value in the WHERE clause expression

Subqueries are most often found in the WHERE clause of a SELECT, UPDATE, or DELETE statement. A subquery may either be correlated with its containing SQL statement, meaning that it references one or more columns from the containing statement, or it might reference nothing outside itself, in which case it is called a non- correlated subquery.

Subqueries are useful because they allow comparisons to be made without changing the size of the result set.

The DELETE statement

The delete statement facilitates the removal of data from the database. Like the SELECT statement, the DELETE statement contains a WHERE clause that specifies the conditions used to identify rows to be deleted. If we neglect to add a WHERE clause to our DELETE statement, all rows will be deleted from the target table.

Syntax:

DELETE <Column1, Column2,..>

 FROM table_name

Where <one or more condition apply>

The following statement will delete all employees with the last name of ‘Smith’ from the employee table:

DELETE FROM employee

WHERE lname = 'Smith';

In some cases, the values needed for one or more of the conditions in your WHERE clause exist in another table. For example, your company may decide to outsource its accounting functions, thereby necessitating the removal of all Accounting personnel from the employee table:

DELETE FROM employee

WHERE dept_id = (SELECT dept_id  FROM department  WHERE name = 'Accounting');

The INSERT Statement

The INSERT statement is the mechanism for loading data into your database. Data can be inserted into only one table at a time, although the data being loaded into the table can be pulled from one or more additional tables. When inserting data into a table, we do not need to provide values for every column in the table; however, we need to be aware of the columns that require non-NULL* values and the ones that do not.

Syntax:

INSERT INTO table_name (Column1,Column2,…)

Values (value1,value2,...)

Eg. INSERT INTO employee (emp_id, lname, dept_id)

VALUES (101, 'Smith', 2);

The VALUES clause must contain the same number of elements as the column list, and the data types must match the column definitions.

Sometimes, the data to be inserted needs to be retrieved from one or more tables. In this case we can feed the result set from a SELECT statement directly into an INSERT statement, as in:

INSERT INTO employee (emp_id, fname, lname, dept_id, hire_date) SELECT 101, 'Dave', 'Smith', d.dept_id, SYSDATE

FROM department d

WHERE d.name = 'Accounting';

ANSI Outer Join Syntax

The ANSI outer join syntax doesn't use the outer join operator (+) in the join condition; rather, it specifies the join type in the FROM clause. The syntax of ANSI outer join is:

FROM table1 { LEFT | RIGHT | FULL } [OUTER] JOIN table2 The syntax elements are:

table1, table2 specifies the tables on which you are performing the outer join.

        

LEFT Specifies that the results be generated using all rows from table1. For those rows in table1 that don’t have corresponding rows in table2, NULLs are returned in the result set for the table2 columns. This is the equivalent of specifying (+) on the table2 side of the join condition in the traditional syntax.

        

RIGHT Specifies that the results be generated using all rows from table2. For those rows in table2 that don’t have corresponding rows in table1, NULLs are returned in the result set for the table1 columns. This is the equivalent of specifying (+) on the table1 side of the join condition in the traditional syntax.

FULL Specifies that the results be generated using all rows from table1 and table2. For those rows in table1 that don’t have corresponding rows in table2, NULLs are returned in the result set for the table2 columns. Additionally, for those rows in table2 that don’t have corresponding rows in table1, NULLs are returned in the result set for the table1 columns. There is no equivalent in the traditional syntax for a FULL OUTER JOIN.

        

OUTER Specifies that you are performing an OUTER join. This keyword is optional. If you use LEFT, RIGHT, or FULL, Oracle automatically assumes an outer join. The OUTER is for completeness sake, and complements the INNER keyword.

To perform a LEFT OUTER JOIN between the DEPARTMENT and LOCATION tables, you can use:

SELECT D.DEPT_ID, D.NAME, L.REGIONAL_GROUP

 FROM DEPARTMENT D LEFT OUTER JOIN LOCATION L ON D.LOCATION_ID = L.LOCATION_ID;

To perform a RIGHT OUTER JOIN between the DEPARTMENT and LOCATION tables, you can use:

SELECT D.DEPT_ID, D.NAME, L.REGIONAL_GROUP

FROM DEPARTMENT D RIGHT OUTER JOIN LOCATION L ON D.LOCATION_ID = L.LOCATION_ID;

If you want to include the departments without a location, as well as the locations without a department, you need to do a full outer join

SELECT D.DEPT_ID, D.NAME, L.REGIONAL_GROUP

FROM DEPARTMENT D FULL OUTER JOIN LOCATION L ON D.LOCATION_ID = L.LOCATION_ID;