, , ,

Multi row subquery

A multi row subquery   returns one or more rows.  Since it returns multiple values, the query must use the set comparison operators (IN,ALL,ANY).   If you use a multi row subquery with the equals comparison operators, the database will return an error if more than one row is returned.

Exampe:
select last_name from employees where manager_id in
(select employee_id from employees where department_id in
(select department_id from departments where location_id in
(select location_id from locations where country_id=’UK’)));

with
You can improve the performance of this query by having Oracle9i execute the subquery only once, then simply letting Oracle9i reference it at the appropriate points in the main query. The following code block gives a better logical idea of the work Oracle must perform to give you the result. In it, the bold text represents the common parts of the subquery that are performed only once, and the places where the subquery is referenced:

SQL> with summary as
  2  (select dname, sum(sal) as dept_total
  3   from emp, dept
  4   where emp.deptno = dept.deptno
  5   group by dname)
  6  select dname, dept_total
  7  from summary
  8  where dept_total >
  9  (select sum(dept_total) * 1/3
 10   from summary)
 11  order by dept_total desc;
DNAME                DEPT_TOTAL
——————– ———-
RESEARCH                  10875

/by
, , ,

Single-Row Subqueries

The main query expects the sub query to return only one value.
Check out the following example, which should look familiar:
SQL>  select ename, deptno, sal
  2   from emp
  3   where deptno =
  4    ( select deptno
  5      from dept
  6      where loc = ‘NEW YORK’ );
ENAME         DEPTNO       SAL
———-         ———         ———
CLARK             10            2450
KING               10            5000
MILLER            10            1300
Though the above query results have 3 rows it is a single-row subquery Because, the subquery on the DEPT table to derive the output from EMP returns only one row of data.
/by
, , ,

Subqueries in SQL

Subquery or Inner query or Nested query is a query in a query. A subquery is usually added in the WHERE Clause of the sql statement. Most of the time, a subquery is used when you know how to search for a value using a SELECT statement, but do not know the exact value.

 select ename, deptno, sal
  2   from emp
  3   where deptno =
  4    ( select deptno
  5      from dept
  6      where loc = ‘NEW YORK’ );
ENAME         DEPTNO       SAL
———-           ———     ———
CLARK             10            2450
KING               10            5000
MILLER            10            1300
Subqueries can be used to obtain values for parent select statements when specific search criteria isn’t known. To do so, the where clause in the parent select statement must have a comparison operation where the unknown value being compared is determined by the result of the subquery. The inner subquery executes once, right before the main outer query executes. The subquery returns its results to the main outer query as shown in above example
Notes:
1. Subqueries must appear inside parentheses, or else Oracle will have trouble distinguishing the subquery from the parent query. You should also make sure to place subqueries on the right side of the comparison operator.
2. Subqueries are an alternate way of returning data from multiple tables.
3. Subqueries can be used with the following sql statements along with the comparision operators like =, <, >, >=, <= etc.
     SELECT
     INSERT
     UPDATE
     DELETE
Differnt Usage
IN
You can also use the in comparison, which is similar to the case statement offered in many programming languages, because resolution can be established based on the parent column’s equality with any element in the group. Let’s take a look at an example:
SQL> select ename, job, sal
  2  from emp
  3  where deptno in
  4   ( select deptno
  5     from dept
  6     where dname in
  7     (‘ACCOUNTING’, ‘SALES’));
EXISTS/NOT EXISTS
Another way of including a subquery in the where clause of a select statement is to use the exists clause. This clause enables you to test for the existence of rows in the results of a subquery, and its logical opposite is not exists. When you specify the exists operation in a where clause, you must include a subquery that satisfies the exists operation. If the subquery returns data, the exists operation returns TRUE, and a record from the parent query will be returned. If not, the exists operation returns FALSE, and no record for the parent query will be returned. Let’s look at an example in which we obtain the same listing of employees working in the New York office, only this time, we use the exists operation:
SQL> select e.ename, e.job, e.sal
  2  from emp e
  3  where exists
  4     ( select d.deptno
  5       from dept d
  6       where d.loc = ‘NEW YORK’
  7       and d.deptno = e.deptno);
ENAME      JOB              SAL
———-     ———           ———
CLARK      MANAGER        2450
KING        PRESIDENT      5000
MILLER      CLERK             1300
Correlated Subquery
A query is called correlated subquery when both the inner query and the outer query are interdependent. For every row processed by the inner query, the outer query is processed as well. The inner query depends on the outer query before it can be processed.
SELECT p.product_name FROM product p
WHERE p.product_id = (SELECT o.product_id FROM order_items o
WHERE o.product_id = p.product_id);
Listing and Writing Different Types of Subqueries
The following list identifies several different types of subqueries you may need to understand and use on the OCP exam:

  •       Single-row subqueries The main query expects the subquery to return only one value.
  •       Multirow subqueries The main query can handle situations where the subquery returns more than one value.
  •       Multiple-column subqueries A subquery that contains more than one column of return data in addition to however many rows are given in the output. These types of subqueries will be discussed later in the chapter.
  •       Inline views A subquery in a from clause used for defining an intermediate result set to query from. These types of subqueries will be discussed later in the chapter.
/by
, , ,

System Functions

NVL
The NVL() function is available in Oracle, and not in MySQL or SQL Server. This function is used to replace NULL value with another value. It is similar to the IFNULL Function in MySQL and the ISNULL Function in SQL Server.
The syntax for the NVL function is:
    NVL( string1, replace_with ) string1 is the string to test for a null value.
                                           replace_with is the value returned if string1 is null.
    NVL( column1, replace_with )
Example #1:
    select NVL(supplier_city, ‘n/a’)
    from suppliers;
The SQL statement above would return ‘n/a’ if the supplier_city field contained a null value. Otherwise, it would return the supplier_city value.
Example #2:
    select supplier_id,
    NVL(supplier_desc, supplier_name)
    from suppliers;
This SQL statement would return the supplier_name field if the supplier_desc contained a null value. Otherwise, it would return the supplier_desc.

The decode() Function

The decode() function works on the same principle as the if-then-else statement does in many common programming languages, including PL/SQL. You can pass a variable number of values into the call to the decode() function, which will appear in the column clause of your select statement. Your first item will always be the name of the column you want to decode. Next, you identify the first specific value Oracle should look for in that column. After that, you pass in the substitute you want Oracle to return if the first specific value is encountered. From there, you can then define as many specific value-substitute pairs as you would like. Once all value-substitute pairs have been defined, you can optionally specify a default value that Oracle will return if the column value doesn’t match a specified value. Take a look at the following code block to get a better idea of how this works:
SELECT decode(column_name,
              value1, substitute1,
              value2, substitute2,
              … ,
              return_default)
FROM … ;
 

Example:
Select
decode(AUTHORIZATION_STATUS,
       ‘APPROVED’, ‘APPRED’,
       ‘IN PROCESS’, ‘JUST STARTED’,
       ‘Others’)
From PO_HEADERS_ALL
SYSDATE
In Oracle/PLSQL, the sysdate function returns the current system date and time on your local database.
The syntax for the sysdate function is:    sysdate
Example #1:
Select SYSDATE FROM DUAL;
OUTPUT: 3/3/2009 2:22:27 AM

Example #2:
Select TRUNC(SYSDATE) FROM DUAL;
OUTPUT: 3/3/2009

Example #3:
Select TO_CHAR(SYSDATE,’DD-MM-YYYY’) FROM DUAL;
OUTPUT: 03-03-2009
Example #4:
select sysdate into v_date from dual;
OUTPUT: The variable called v_date will now contain the current date and time value.

Example #5:
You could also use the sysdate function in any SQL statement. For example:
    select supplier_id, sysdate
    from suppliers
    where supplier_id > 5000;
Arithmetic Functions
Other functions are designed to perform specialized mathematical functions, such as those used in scientific applications such as sine and logarithms. These operations are commonly referred to as arithmetic or number operations. The functions falling into this category are listed next. These functions are not all that is available in Oracle, but rather they are the most commonly used ones that will likely appear on OCP Exam 1:abs(x) Obtains the absolute value for a number. For example, the absolute value of -1 is 1, whereas the absolute value of 6 is 6.
round(x,y) Rounds x to the decimal precision of y. If y is negative, it rounds to the precision of y places to the left of the decimal point. For example, round(134.345,1) = 134.3, round(134.345,0) = 134, round(134.345,-1) = 130. This can also be used on DATE columns.
ceil(x) Similar to executing round on an integer (for example, round(x,0)), except ceil always rounds up. For example, ceil(1.4) = 2. Note that rounding up on negative numbers produces a value closer to zero (for example, ceil(-1.6) = -1, not -2).
floor(x) Similar to ceil, except floor always rounds down. For example, floor(1.6) = 1. Note that rounding down on negative numbers produces a value further away from zero (for example, floor (-1.6) = -2, not -1).
mod(x,y) The modulus of x, defined in long division as the integer remainder when x is divided by y until no further whole number can be produced. For example, mod(10,3) = 1, and mod(10,2) = 0.
Sign(x) Displays an integer value corresponding to the sign of x: 1 if x is positive, – 1 if x is negative.

sqrt(x) The square root of x.
  
trunc(x,y) Truncates x to the decimal precision of y. If y is negative, it truncates to y number of places to the left of the decimal point. This can also be used on DATE columns.
vsize(x) The storage size in bytes for value x.
Text Functions
Several functions in Oracle manipulate text strings. These functions are similar in concept to nvl() and decode() in that they can perform a change on a piece of data, but the functions in this family can change only VARCHAR2 and CHAR data. Here are some examples:   
lpad(x,y[,z]) and rpad(x,y[,z]) Return data in string or column x padded on the left or right side, respectively, to width y. The optional value z indicates the character(s) that lpad() or rpad() use to pad the column data. If no character z is specified, a space is used.
    
lower(x), upper(x), and initcap(x) Return data in string or column x in lowercase or uppercase characters, respectively, or change the initial letter in the data from column x to a capital letter.
     
length(x) Returns the number of characters in string or column x.
     
substr(x,y[,z]) Returns a substring of string or column x, starting at the character in position number y to the end, which is optionally defined by the character appearing in position z of the string. For example, substr(‘ABCDEFG’,3,4) returns CDEF.
    
instr(x,y) Determines whether a substring y given can be found in string x. For example, instr(‘CORPORATE FLOOR’,’OR’) returns 2.
The trim() Function A single-row function called trim() behaves like a combination of ltrim() and rtrim(). The trim() function accepts a string describing the data you would like to trim from a column value using the following syntax: trim([[keyword ]’x’ from] column). Here keyword is replaced by leading, trailing, or both, or it’s omitted. Also, x is replaced with the character to be trimmed, or it’s omitted. If x is omitted, Oracle assumes it must trim whitespace. Finally, column is the name of the column in the table to be trimmed. Note that trim() only removes trailing or leading instances of the character specified. If that character appears somewhere in the string, trim() will not remove it.
Conversion Functions
Conversion functions are designed to convert data from one datatype format to another. These functions do not actually modify the stored data in the table itself; they just return the converted values to the SQL*Plus session. Figure 2-1 displays how information can get converted from one datatype to another using various functions. Several different conversion functions are available in the Oracle database, as listed here:
to_char(x) Converts the value x to a character or converts a date to a character string using formatting conventions (see “Date-Formatting Conventions” subtopic below).
to_number(x) Converts nonnumeric value x to a number.
to_date(x[,y]) Converts the nondate value x to a date using the format specified by y.
to_multi_byte(x) Converts the single-byte character string x to multibyte characters according to national language standards.

to_single_byte(x) Converts the multibyte character string x to single-byte characters according to national language standards.
chartorowid(x) Converts the string of characters x into an Oracle ROWID.
rowidtochar(x) Converts the ROWID value into the string of characters x of VARCHAR2 datatype.
hextoraw(x) Converts the hexadecimal (base-16) value x into a raw (binary) format.
rawtohex(x) Converts the raw (binary) value x into a hexadecimal (base-16) format.
convert(x[,y[,z]]) Executes a conversion of alphanumeric string x from the current character set (optionally specified as z) to the one specified by y.
translate(x,y,z) Executes a simple value conversion for character or numeric string x into something else based on the conversion factors y and z.

/by
, , ,

UNION & INTERSECT

UNION Query
The UNION query allows you to combine the result sets of 2 or more “select” queries.

  • It removes duplicate rows between the various “select” statements.
  • Each SQL statement within the UNION query must have the same number of fields in the result sets with similar data types.

The syntax for a UNION query is:
Select field1, field2, . field_n   from tables
UNION
Select field1, field2, . field_n   from tables;
Example #1
The following is an example of a UNION query:
Select supplier_id from suppliers
UNION
Select supplier_id from orders;
In this example, if a supplier_id appeared in both the suppliers and orders table, it would appear once in your result set. The UNION removes duplicates.

Example #2 – With ORDER BY Clause
The following is a UNION query that uses an ORDER BY clause:
Select supplier_id, supplier_name from suppliers where supplier_id > 2000
UNION
Select company_id, company_name from companies where company_id > 1000
ORDER BY 2;
Since the column names are different between the two “select” statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we’ve sorted the results by supplier_name / company_name in ascending order, as denoted by the “ORDER BY 2”.

UNION ALL Query
The UNION ALL query allows you to combine the result sets of 2 or more “select” queries. It returns all rows (even if the row exists in more than one of the “select” statements).
Each SQL statement within the UNION ALL query must have the same number of fields in the result sets with similar data types.
The syntax for a UNION ALL query is:
Select field1, field2, . field_n from tables
UNION ALL
Select field1, field2, . field_n from tables;
Example #1
The following is an example of a UNION ALL query:
Select supplier_id from suppliers
UNION ALL
Select supplier_id from orders;
If a supplier_id appeared in both the suppliers and orders table, it would appear multiple times in your result set. The UNION ALL does not remove duplicates.
Example #2 – With ORDER BY Clause
The following is a UNION query that uses an ORDER BY clause:
Select supplier_id, supplier_name from suppliers where supplier_id > 2000
UNION ALL
Select company_id, company_name from companies where company_id > 1000
ORDER BY 2;
Since the column names are different between the two “select” statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we’ve sorted the results by supplier_name / company_name in ascending order, as denoted by the “ORDER BY 2”.
INTERSECT Query
The INTERSECT query allows you to return the results of 2 or more “select” queries. However, it only returns the rows selected by all queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.
Each SQL statement within the INTERSECT query must have the same number of fields in the result sets with similar data types.
The syntax for an INTERSECT query is:
Select field1, field2, . field_n from tables
INTERSECT
Select field1, field2, . field_n from tables;
Example #1
The following is an example of an INTERSECT query:
Select supplier_id from suppliers
INTERSECT
Select supplier_id from orders;
In this example, if a supplier_id appeared in both the suppliers and orders table, it would appear in your result set.
Example #2 – With ORDER BY Clause
The following is an INTERSECT query that uses an ORDER BY clause:
Select supplier_id, supplier_name from suppliers where supplier_id > 2000
INTERSECT
Select company_id, company_name from companies where company_id > 1000
ORDER BY 2;
Since the column names are different between the two “select” statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we’ve sorted the results by supplier_name / company_name in ascending order, as denoted by the “ORDER BY 2”.
MINUS Query
The MINUS query returns all rows in the first query that are not returned in the second query.
Each SQL statement within the MINUS query must have the same number of fields in the result sets with similar data types.
The syntax for an MINUS query is:
Select field1, field2, . field_n from tables
MINUS
Select field1, field2, . field_n from tables;
Example #1
The following is an example of an MINUS query:
Select supplier_id from suppliers
MINUS
Select supplier_id from orders;
In this example, the SQL would return all supplier_id values that are in the suppliers table and not in the orders table. What this means is that if a supplier_id value existed in the suppliers table and also existed in the orders table, the supplier_id value would not appear in this result set.
Example #2 – With ORDER BY Clause
The following is an MINUS query that uses an ORDER BY clause:
Select supplier_id, supplier_name from suppliers where supplier_id > 2000
MINUS
Select company_id, company_name from companies where company_id > 1000
ORDER BY 2;
Since the column names are different between the two “select” statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we’ve sorted the results by supplier_name / company_name in ascending order, as denoted by the “ORDER BY 2”.

/by