Monday, 7 April 2014

Basics of SQL

Basics of SQL


PL/SQL is a procedural language that Oracle developed as an extension to standard SQL to provide a way to execute procedural logic on the database.

SQL, SQL*Plus, PL/SQL: What's the Difference?
This question has bedeviled many people new to Oracle. There are several products with the letters "SQL" in the title, and these three, SQL*Plus, SQL, and PL/SQL, are often used together. Because of this, it's easy to become confused as to which product is doing the work and where the work is being done. This section briefly describes each of these three products.

SQL
SQL stands for Structured Query Language. This has become the lingua franca of database access languages. It has been adopted by the International Standards Organization (ISO) and has also been adopted by the American National Standards Institute (ANSI). When you code statements such as SELECT, INSERT, UPDATE, and DELETE, SQL is the language you are using. It is a declarative language and is always executed on the database server. Often you will find yourself coding SQL statements in a development tool, such as PowerBuilder or Visual Basic, but at runtime those statements are sent to the server for execution.

PL/SQL
PL/SQL is Oracle's Procedural Language extension to SQL. It, too, usually runs on the database server, but some Oracle products such as Developer/2000 also contain a PL/SQL engine that resides on the client. Thus, you can run your PL/SQL code on either the client or the server depending on which is more appropriate for the task at hand. Unlike SQL, PL/SQL is procedural, not declarative. This means that your code specifies exactly how things get done. As in SQL, however, you need some way to send your PL/SQL code up to the server for execution. PL/SQL also enables you to embed SQL statements within its procedural code. This tight-knit relationship between PL/SQL, SQL, and SQL*Plus is the cause for some of the confusion between the products.

SQL*Plus
SQL*Plus is an interactive program that allows you to type in and execute SQL statements. It also enables you to type in PL/SQL code and send it to the server to be executed. SQL*Plus is one of the most common front ends used to develop and create stored PL/SQL procedures and functions.

What happens when you run SQL*Plus and type in a SQL statement? Where does the processing take place? What exactly does SQL*Plus do, and what does the database do? If you are in a Windows environment and you have a database server somewhere on the network, the following things happen:
   1. SQL*Plus transmits your SQL query over the network to the database server.
   2. SQL*Plus waits for a reply from the database server.
   3. The database server executes the query and transmits the results back to SQL*Plus.
   4. SQL*Plus displays the query results on your computer screen.

Even if you're not running in a networked Windows environment, the same things happen. The only difference might be that the database server and SQL*Plus are running on the same physical machine. This would be true, for example, if you were running Personal Oracle on a single PC.

PL/SQL is executed in much the same manner. Type a PL/SQL block into SQL*Plus, and it is transmitted to the database server for execution. If there are any SQL statements in the PL/SQL code, they are sent to the server's SQL engine for execution, and the results are returned back to the PL/SQL program.

SELECT Statement


The SELECT statement is used to query the database and retrieve selected data that match the criteria that you specify.
The SELECT statement has five main clauses to choose from, although, FROM is the only required clause. Each of the clauses have a vast selection of options, parameters, etc. The clauses will be listed below, but each of them will be covered in more detail later in the tutorial.

Here is the format of the SELECT statement:
SELECT [ALL | DISTINCT] column1[,column2]
FROM table1[,table2]
[WHERE "conditions"]
[GROUP BY "column-list"]
[HAVING "conditions]
[ORDER BY "column-list" [ASC | DESC] ]
SELECT column_name(s)
FROM table_name
and

SELECT * FROM table_name

DISTINCT Clause
The DISTINCT clause allows you to remove duplicates from the result set. The DISTINCT clause can only be used with select statements.
The syntax for the DISTINCT clause is:
SELECT DISTINCT columns     FROM tables     WHERE predicates;

Example #1
Let's take a look at a very simple example. 
SELECT DISTINCT city FROM suppliers;
This SQL statement would return all unique cities from the suppliers table.

Example #2
The DISTINCT clause can be used with more than one field.
For example:SELECT DISTINCT city, state FROM suppliers;
This select statement would return each unique city and state combination. In this case, the distinct applies to each field listed after the DISTINCT keyword.

Table Joins


The typical database contains many tables. Some smaller databases may have only a dozen or so tables, whereas other databases may have hundreds or even thousands. The common factor, however, is that few databases have just one table containing everything you need. Therefore, you usually have to draw data from multiple tables together in a meaningful way. To show data from multiple tables in one query, Oracle allows you to perform table joins.

Here are the two rules you need to remember for table joins. Data from two (or more) tables can be joined, if the same column (under the same or a different name) appears in both tables, and the column is the primary key (or part of that key) in one of the tables. Having a common column in two tables implies a relationship between the two tables. The nature of that relationship is determined by which table uses the column as a primary key. This begs the question, what is a primary key? A primary key is a column in a table used for identifying the uniqueness of each row in a table. The table in which the column appears as a primary key is referred to as the parent table in this relationship (sometimes also called the master table), whereas the column that references the other table in the relationship is often called the child table (sometimes also called the detail table). The common column appearing in the child table is referred to as a foreign key.
Join Syntax
Let's look at an example of a join statement using the Oracle traditional syntax, where we join the contents of the EMP and DEPT tables together to obtain a listing of all employees, along with the names of the departments they work for:
SQL> select e.ename, e.deptno, d.dname
    2  from emp e, dept d
    3  where e.deptno = d.deptno;
ENAME        DEPTNO      DNAME
----------   ---------   --------------
SMITH        20          RESEARCH
ALLEN        30          SALES
WARD         30          SALES
JONES        20          RESEARCH

Note the many important components in this table join. Listing two tables in the from clause clearly indicates that a table join is taking place. Note also that each table name is followed by a letter: E for EMP or D for DEPT. This demonstrates an interesting concept—just as columns can have aliases, so too can tables. The aliases serve an important purpose—they prevent Oracle from getting confused about which table to use when listing the data in the DEPTNO column. Remember, EMP and DEPT both have a column named DEPTNO

You can also avoid ambiguity in table joins by prefixing references to the columns with the table names, but this often requires extra coding. You can also give the column two different names, but then you might forget that the relationship exists between the two tables. It's just better to use aliases! Notice something else, though. Neither the alias nor the full table name needs to be specified for columns appearing in only one table. Take a look at another example:
SQL> select ename, emp.deptno, dname
  2  from emp, dept
  3  where emp.deptno = dept.deptno;

How Many Comparisons Do You Need?
When using Oracle syntax for table joins, a query on data from more than two tables must contain the right number of equality operations to avoid a Cartesian product. To avoid confusion, use this simple rule: If the number of tables to be joined equals N, include at least N-1 equality conditions in the select statement so that each common column is referenced at least once. Similarly, if you are using the ANSI/ISO syntax for table joins, you need to use N-1 join tablename on join_condition clauses for every N tables being joined.

For N joined tables using Oracle or ANSI/ISO syntax for table joins, you need at least N-1 equijoin conditions in the where clause of your select statement or N-1 join tablename on join_condition clauses in order to avoid a Cartesian product, respectively.

Cartesian Products
Notice also that our where clause includes a comparison on DEPTNO linking data in EMP to that of DEPT. Without this link, the output would have included all data from EMP and DEPT, jumbled together in a mess called a Cartesian product. Cartesian products are big, meaningless listings of output that are nearly never what you want. They are formed when you omit a join condition in your SQL statement, which causes Oracle to join all rows in the first table to all rows in the second table. Let's look at a simple example in which we attempt to join two tables, each with three rows, using a select statement with no where clause, resulting in output with nine rows:

SQL> select a.col1, b.col_2
  2  from example_1 a, example_2 b;
     COL1 COL_2
--------- ------------------------------
        1 one
        2 one
        3 one
        1 two
        2 two
        3 two
You must always remember to include join conditions in join queries to avoid Cartesian products. But take note of another important fact. Although we know that where clauses can contain comparison operations other than equality, to avoid Cartesian products, you must always use equality operations in a comparison joining data from two tables. If you want to use another comparison operation, you must first join the information using an equality comparison and then perform the other comparison somewhere else in the where clause. This is why table join operations are also sometimes referred to as equijoins. Take a look at the following example that shows proper construction of a table join, where the information being joined is compared further using a nonequality operation to eliminate the employees from accounting:

SQL> select ename, emp.deptno, dname
  2  from emp, dept
  3  where emp.deptno = dept.deptno
  4  and dept.deptno > 10;

ANSI/ISO Join Syntax (Oracle9i and higher)
In Oracle9i, Oracle introduces strengthened support for ANSI/ISO join syntax. To join the contents of two tables together in a single result according to that syntax, we must include a join tablename on join_condition in our SQL statement. If we wanted to perform the same table join as before using this new syntax, our SQL statement would look like the following:
Select ename, emp.deptno, dname
from emp join dept
on emp.deptno = dept.deptno;
ENAME         DEPTNO     DNAME
----------    ---------  -------------
SMITH         20         RESEARCH
ALLEN         30         SALES
WARD          30         SALES
JONES         20         RESEARCH

Note how different this is from Oracle syntax. First, ANSI/ISO syntax separates join comparisons from all other comparisons by using a special keyword, on, to indicate what the join comparison is. You can still include a where clause in your ANSI/ISO-compliant join query, the only difference is that the where clause will contain only those additional conditions you want to use for filtering your data. You also do not list all your tables being queried in one from clause. Instead, you use the join clause directly after the from clause to identify the table being joined.

Never combine Oracle's join syntax with ANSI/ISO's join syntax! Also, there are no performance differences between Oracle join syntax and ANSI/ISO join syntax.

Cartesian Products: An ANSI/ISO Perspective
In some cases, you might actually want to retrieve a Cartesian product, particularly in financial applications where you have a table of numbers that needs to be cross-multiplied with another table of numbers for statistical analysis purposes. ANSI/ISO makes a provision in its syntax for producing Cartesian products through the use of a cross-join. A cross-join is produced when you use the cross keyword in your ANSI/ISO-compliant join query. Recall from a previous example that we produced a Cartesian product by omitting the where clause when joining two sample tables, each containing three rows, to produce nine rows of output. We can produce this same result in ANSI/ISO SQL by using the cross keyword, as shown here in bold:

Select col1, col_2
from example_1 cross join example_2;
COL1      COL_2
--------- -------------
 1        one
 2        one
 3        one
 1        two
 2        two
 3        two
 1        three

Natural Joins

One additional type of join you need to know about for OCP is the natural join. A natural join is a join between two tables where Oracle joins the tables according to the column(s) in the two tables sharing the same name (naturally!). Natural joins are executed whenever the natural keyword is present. Let's look at an example. Recall our use of the EMP and DEPT tables from our discussion above. Let's take a quick look at the column listings for both tables:
SQL> describe emp
Name                        Null         Type
--------------------------  ---------    ------------
EMPNO                       NOT NULL     NUMBER(4)
ENAME                                    VARCHAR2(10)
JOB                                      VARCHAR2(9)
MGR                                      NUMBER(4)

SQL> describe dept
Name                        Null        Type
--------------------------  ---------   ------------
DEPTNO                      NOT NULL    NUMBER(2)
DNAME                                   VARCHAR2(14)
LOC                                     VARCHAR2(13)

As you can see, DEPTNO is the only column in common between these two tables, and appropriately enough, it has the same name in both tables. This combination of facts makes our join query of EMP and DEPT tables a perfect candidate for a natural join. Take a look and see:
Select ename, deptno, dname
from emp natural join dept;
ENAME         DEPTNO     DNAME
----------    ---------  --------------
SMITH         20         RESEARCH
ALLEN         30         SALES
WARD          30         SALES

Outer Joins
Outer joins extend the capacity of Oracle queries to include handling of situations where you want to see information from tables even when no corresponding records exist in the common column.  The purpose of an outer join is to include non-matching rows, and the outer join returns these missing columns as NULL values.

Left Outer Join
A left outer join will return all the rows that an inner join returns plus one row for each of the other rows in the first table that did not have a match in the second table.

Suppose you want to find all employees and the projects they are currently responsible for. You want to see those employees that are not currently in charge of a project as well. The following query will return a list of all employees whose names are greater than 'S', along with their assigned project numbers.

  SELECT EMPNO, LASTNAME, PROJNO
    FROM CORPDATA.EMPLOYEE LEFT OUTER JOIN CORPDATA.PROJECT
          ON EMPNO = RESPEMP
    WHERE LASTNAME > 'S'

The result of this query contains some employees that do not have a project number. They are listed in the query, but have the null value returned for their project number.
EMPNO     LASTNAME     PROJNO
000020    THOMPSON     PL2100
000100    SPENSER      OP2010
000170    YOSHIMURA    -
000250    SMITH        AD3112

In oracle we can specify(in Oracle 8i or prior vesrion this was the only option as they were not supporting the ANSI syntex) left outer join by putting a (+) sign on the right of the column which can have NULL data corresponding to non-NULL values in the column values from the other table.
example:  
SQL> select    last_name,   department_name
from   employees e,   departments d
where   e.department_id(+) = d.department_id;

Right Outer Join
A right outer join will return all the rows that an inner join returns plus one row for each of the other rows in the second table that did not have a match in the first table. It is the same as a left outer join with the tables specified in the opposite order.
The query that was used as the left outer join example could be rewritten as a right outer join as follows:

Earlier version of outer join
SQL> select e.ename, e.deptno, d.dname
 from dept d, emp e
 where d.deptno = e.deptno (+);
ANSI/ISO version
SQL> select e.ename, e.deptno, d.dname
 from emp e right outer join dept d
on d.deptno = e.deptno;

Full Outer Joins
Oracle9i and higher
Oracle9i also makes it possible for you to easily execute a full outer join, including all records from the tables that would have been displayed if you had used both the left outer join or right outer join clauses. Let's take a look at an example:
SQL> select e.ename, e.deptno, d.dname
2 from emp e full outer join dept d
3 on d.deptno = e.deptno;


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".

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 thedecode() 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.

Subqueries


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.

DDL statements


Data definition language (DDL) refers to the subgroup of SQL statements that create, alter, or drop database objects.

This sub-category of SQL statements is of particular interest to database architects or database administrators who must define an original database design and who must respond to requirements and extend a database design. It is also of interest to database application developers, because there are times when the easiest way to meet an application requirement is to extend an existing database object definition.

In general DDL statements begin with one of the following keywords: CREATE, ALTER, or DROP. Examples of DDL statements for creating database objects include: CREATE TABLE, CREATE TRIGGER, CREATE PROCEDURE, and CREATE SEQUENCE. These statements generally contain multiple clauses used to define the characteristics and behavior of the database object. Examples of DDL statements for altering database objects include: ALTER TABLE, and ALTER PROCEDURE. These statements generally are used to alter a characteristic of a database object.

DDL statements can be executed from a variety of interactive and application interfaces although they are most commonly executed in scripts or from integrated development environments that support database and database object design.

Describing Tables
The best way to think of a table for most Oracle beginners is to envision a spreadsheet containing several records of data. Across the top, try to see a horizontal list of column names that label the values in these columns. Each record listed across the table is called a row. In SQL*Plus, the command describe enables you to obtain a basic listing of characteristics about the table.
SQL> DESCRIBE po_headers_all
Name                       Type           Nullable Default                                                                                                Comments
-------------------------- -------------- -------- ---------------------------
PO_HEADER_ID               NUMBER                                                                                                                                 
AGENT_ID                   NUMBER(9)                                                                                                                              
TYPE_LOOKUP_CODE           VARCHAR2(25)                                                                                                                           
LAST_UPDATE_DATE           DATE                                                                                                                                   
LAST_UPDATED_BY            NUMBER                                                                                                                                 
SEGMENT1                   VARCHAR2(20)   R2(1)

Commenting Objects
You can also add comments to a table or column using the comment command. This is useful especially for large databases where you want others to understand some specific bits of information about a table, such as the type of information stored in the table. An example of using this command to add comments to a table appears in the following block:
SQL> comment on table employee is
  2  'This is a table containing employees';
Comment created.
You can see how to use the comment command for adding comments on table columns in the following code block:
SQL> comment on column employee.empid is
  2  'unique text identifier for employees';
Comment created.
Tip     
Comment information on tables is stored in an object called USER_TAB_COMMENTS, whereas comment information for columns is stored in a different database object, called USER_COL_COMMENTS. These objects are part of the Oracle data dictionary. You'll find out more about the Oracle data dictionary later in the book.

DML Statements


Data Manipulation Language (DML) is a family of computer languages used by computer programs database users to retrieve, insert, delete and update data in a database.
Currently the most popular data manipulation language is that of SQL, which is used to retrieve and manipulate data in a Relational database. Other forms of DML are those used by IMS/DLI, CODASYL databases (such as IDMS), and others.
Data manipulation languages were initially only used by computer programs, but (with the advent of SQL) have come to be used by people, as well.
Data Manipulation Language (DML) is used to retrieve, insert and modify database information. These commands will be used by all database users during the routine operation of the database. Let's take a brief look at the basic DML commands:
Data Manipulation Languages have their functional capability organized by the initial word in a statement, which is almost always a verb. In the case of SQL, these verbs are:
    * Select
    * Insert
    * Update
    * Delete

Transaction Control


One of the great benefits Oracle provides you is the ability to make changes in database using SQL statements and then decide later whether we want to save or discard them. Oracle enables you to execute a series of data-change statements together as one logical unit of work, called a transaction, that's terminated when you decide to save or discard the work. A transaction begins with your first executable SQL statement. Some advantages for offering transaction processing in Oracle include the following:
  • Transactions enable you to ensure read-consistency to the point in time a transaction began for all users in the Oracle database.
  • Transactions enable you to preview changes before making them permanent in Oracle.
  • Transactions enable you to group logically related SQL statements into one logical unit of work.
Transaction processing consists of a set of controls that enable a user issuing an insert, update, or delete statement to declare a beginning to the series of data-change statements he or she will issue. When the user has finished making the changes to the database, the user can save the data to the database by explicitly ending the transaction. Alternatively, if a mistake is made at any point during the transaction, the user can have the database discard the changes made to the database in favor of the way the data existed before the transaction.
The commands that define transactions are as follows:
  1. Set transaction Initiates the beginning of a transaction and sets key features. This command is optional. A transaction will be started automatically when you start SQL*Plus, commit the previous transaction, or roll back the previous transaction.
  2. Commit Ends the current transaction by saving database changes and starts a new transaction.
  3. Rollback Ends the current transaction by discarding database changes and starts a new transaction.
  4. Savepoint Defines breakpoints for the transaction to enable partial rollbacks.
  5. Locks

Set transaction

This command can be used to define the beginning of a transaction. If any change is made to the database after the set transaction command is issued but before the transaction is ended, all changes made will be considered part of that transaction. The set transaction statement is not required, because a transaction begins under the following circumstances:
  • As soon as you log onto Oracle via SQL*Plus and execute the first command
  • Immediately after issuing a rollback or commit statement to end a transaction
  • When the user exits SQL*Plus
  • When the system crashes
  • When a data control language command such as alter database is issued
By default, a transaction will provide both read and write access unless you override this default by issuing set transaction read only. You can set the transaction isolation level with set transaction as well. The set transaction isolation level serializable command specifies serializable transaction isolation mode as defined in SQL92. If a serializable transaction contains data manipulation language (DML) that attempts to update any resource that may have been updated in a transaction uncommitted at the start of the serializable transaction, the DML statement fails. The set transaction isolation level read committed command is the default Oracle transaction behavior. If the transaction contains DML that requires row locks held by another transaction, the DML statement waits until the row locks are released

Commit
The commit statement in transaction processing represents the point in time where the user has made all the changes he or she wants to have logically grouped together, and because no mistakes have been made, the user is ready to save the work. The work keyword is an extraneous word in the commit syntax that is designed for readability.
Issuing a commit statement also implicitly begins a new transaction on the database because it closes the current transaction and starts a new one. By issuing a commit, data changes are made permanent in the database. The previous state of the data is lost. All users can view the data, and all savepoints are erased. It is important also to understand that an implicit commit occurs on the database when a user exits SQL*Plus or issues a data-definition language (DDL) command such as a create table statement, used to create a database object, or an alter table statement, used to alter a database object.
The following is an example:
SQL> COMMIT;
Commit complete.
SQL> COMMIT WORK;
Commit complete.

Rollback
  If you have at any point issued a data-change statement you don't want, you can discard the changes made to the database with the use of the rollback statement. The previous state of the data is restored. Locks on the affected rows are released. After the rollback command is issued, a new transaction is started implicitly by the database session. In addition to rollbacks executed when the rollback statement is issued, implicit rollback statements are conducted when a statement fails for any reason or if the user cancels a statement with the CTRL-C cancel command. The following is an example:
SQL> ROLLBACK;
Rollback complete

Savepoint
In some cases involving long transactions or transactions that involve many data changes, you may not want to scrap all your changes simply because the last statement issued contains unwanted changes. Savepoints are special operations that enable you to divide the work of a transaction into different segments. You can execute rollbacks to the savepoint only, leaving prior changes intact. Savepoints are great for situations where part of the transaction needs to be recovered in an uncommitted transaction. At the point the rollback to savepoint so_far_so_good statement completes in the following code block, only changes made before the savepoint was defined are kept when the commit statement is issued:

UPDATE products
SET quantity = 55 WHERE product# = 59495;

SAVEPOINT so_far_so_good;
//Savepoint created.

 UPDATE spanky.products SET quantity = 504;

ROLLBACK TO SAVEPOINT so_far_so_good;

COMMIT;

Locks
The final aspect of the Oracle database that enables the user to employ transaction processing is the lock, the mechanism by which Oracle prevents data from being changed by more than one user at a time. Several different types of locks are available, each with its own level of scope. Locks available on a database are categorized into table-level locks and row-level locks.
A table-level lock enables only the user holding the lock to change any piece of row data in the table, during which time no other users can make changes anywhere on the table. A table lock can be held in any of several modes: row share (RS), row exclusive (RX), share (S), share row exclusive (SRX), and exclusive (X). The restrictiveness of a table lock's mode determines the modes in which other table locks on the same table can be obtained and held.

A row-level lock gives the user the exclusive ability to change data in one or more rows of the table. However, any row in the table that is not held by the row-level lock can be changed by another user

Tip 
An update statement acquires a special row-level lock called a row-exclusive lock, which means that for the period of time the update statement is executing, no other user in the database can view or change the data in the row. The same goes for delete or insert operations. Another update statement—the select for update statement—acquires a more lenient lock called the share row lock. This lock means that for the period of time the update statement is changing the data in the rows of the table, no other user may change that row, but users may look at the data in the row as it changes.

Other Database Objects


Some of the objects that are part of the relational database produced by Oracle and that are used in the functions just mentioned are as follows:
  • Tables, views, and synonyms Used to store and access data. Tables are the basic unit of storage in Oracle. Views logically represent subsets of data from one or more tables. Synonyms provide alternate names for database objects.
  • Indexes and the Oracle RDBMS Used to speed access to data.
  • Sequences Used for generating numbers for various purposes.
  • Triggers and integrity constraints Used to maintain the validity of data entered.
  • Privileges, roles, and profiles Used to manage database access and usage.
  • Packages, procedures, and functions Application PL/SQL code used in the database.

User Access Control


In this chapter, you will learn about and demonstrate knowledge in the following areas of user access and privileges in the Oracle database:
  • Creating users
  • Granting and revoking object privileges
  • Using roles to manage database access

The basic Oracle database security model consists of two parts. The first part consists of password authentication for all users of the Oracle database. Password authentication is available either directly from the Oracle server or from the operating system supporting the Oracle database. When Oracle's own authentication system is used, password information is stored in Oracle in an encrypted format. The second part of the Oracle security model consists of controlling which database objects a user may access, the level of access a user may have to these objects, and whether a user has the authority to place new objects into the Oracle database. At a high level, these controls are referred to as privileges. We'll talk about privileges and database access later in this section.

Create Users
The most basic version of the command for creating users defines only the user we want to create, along with a password, as seen in the following example:

Create User USERNAME identified by PASSWORD;
create user turner identified by ike;

Tip : 
The user does not have privileges at this point. The DBA can then grant privileges to the user. The privileges determine the actions that the user can do with the objects in the database. Also, usernames can be up to 30 characters in length and can contain alphanumeric characters as well as the $, #, and _ characters.

Posted by at No comments:
Subscribe to: Posts (Atom)