About Oracle SQL

SELECT Statement

•Syntax :

  SELECT  * | { [DISTINCT]  column | expression [alias],…}

  FROM table;

•SELECT identifies what columns

•FROM identifies which table

In simple form, it must include

•  A SELECT clause

•       A FROM clause

In the syntax :

  SELECT  is a list of one or more columns

  *  selects all columns

  DISTINCT  suppresses duplicates

  column/expression    selects the named column or the expression

  alias  gives selected columns different headings

  FROM table  specifies the table containing the columns

•Capabilities of SQL SELECT Statements

  A SELECT Statement retrieves information from the database. Using a SELECT Statement we can do :

•  Projection :  You can use the projection capability in SQL to choose the columns in a table that you want returned by your   query.You can choose as few or as many columns of the table as you require

•     Selection :  You can use the selection capability in SQL to choose the rows in a table that you want returned by a query.   You can use various criteria to restrict the rows that you see.

•     Joining :  You can use the join capability in SQL to bring together data that is stored in different tables by creating a link   between them. You learn more about joins in a later lesson.

Selecting Columns

•All Columns

  SELECT  *

  FROM departments;

•Specific Columns

  SELECT department_id,location_id

  FROM departments;

 
Writing SQL Statements

•SQL statements are not case sensitive

•SQL statements can be on one or more lines

•Keywords cannot be abbreviated or split across lines

•Clauses are usually placed on separate lines
 •Indents are used to enhance readability

Arithmetic Operations

Operator	Description
+	Add
-	Substract
*	Multiply
/	Divide

 

Operator Precedence :   *    /     +     -

•Multiplication and division take priority over addition and substraction

•Operators of the same priority are evaluated from left to right

•Parentheses are used to force prioritized evaluation and to clarify statements

Arithmetic Operators :

  SELECT last_name, salary, salary +300

  FROM employees;

•Operator Precedence :

  SELECT last_name,salary, 12*salary+100

  FROM employees;

•Using Parentheses :

  SELECT last_name,salary, 12 * (salary + 100)

  FROM employees;

Defining a NULL Value :

•A null is a value that is unavailable, unassigned, unknown, or inapplicable.

•A null is not the same as zero or a blank space.

•Example :

  SELECT last_name,  job_id, salary, commission_pct

  FROM employees;

•Arithmetic expressions containing a null value evaluate to null

•Example :

       SELECT last_name, job_id, 12*salary*commission_pct

       FROM employees;

Examples : the following expressions give the result as NULL

1.    select 1/NULL  from dual;
2.    select NULL/NULL  from dual;
3.    select 1+NULL  from dual;

Defining a column alias

A column alias :

•Renames a column heading

•Is useful with calculations

•Immediately follows the column name

•An optional  AS keyword can be used

•Requires double quotations if it contains spaces or special characters or is case sensitive

•Example :

      SELECT last_name  name, 12*salary AS  “Annual Salary”

      FROM employees;

Concatenation Operator:

•Concatenates columns or character strings to other columns

•Operator is two vertical bars ( || )

•Creates a resultant column that is a character expression

•Example :

  SELECT last_name || job_id AS  "Employees"

  FROM     employees;

Result : 
        Employees
         KingAD_PRES
KochharAD_VP
De HaanAD_VP
HunoldIT_PROG
ErnstIT_PROG
AustinIT_PROG

Literal Character Strings :  

•A character, a number, or a date included in the SELECT list

•Date and character literal values must be enclosed within single quotation marks

•Each character string is output once for each row returned

•Literal strings included in the query are treated as a column in the SELECT list.

•Example :

SELECT last_name || ' is a ' || job_id  AS
                    "Employee Details"
        FROM    employees;        Result :
         EmployeeDetails
         King is a AD_PRES
Kochhar is a AD_VP
De Haan is a AD_VP
Hunold is a IT_PROG
Ernst is a IT_PROG
 
Eliminating DISTINCT Rows: 

•The default display is all rows, including duplicate rows

•To eliminate duplicate rows,  use the DISTINCT keyword

•Example :

  SELECT  DISTINCT  department_id

  FROM employees;

 
Functions:   

SQL FUNCTIONS :

•  Perform calculations on data

•  Modify individual data items

•  Manipulate output for groups of rows

•  Format dates and numbers for display

•  Convert column data type

•  They some times take arguments and always return a value.

Query: select first_name, lower(first_name), upper(first_name), initcap(first_name) from employees;
Result :

Functions are two types : 

 1.Single Row Functions
          2.Multiple Row Functions

•Character functions :   Accept character input and can return both characters and number values

• Number functions :  Accept numeric input and return numeric values

• Date functions  :  Operate on values of the DATE data type

• Conversion functions : Convert a value from one data type to another

• General functions : 

  -  NVL

  -  NVL2

  -  NULLIF

  -  COALSECE

  -  CASE

  -  DECODE

Example for : NVL, NVL2
Query : SELECT NVL(null,100), NVL(NULL,NULL), NVL(10,50), NVL2(10,20,30), NVL2(NULL,40,50) FROM dual;
Result :

Single Row Functions

•   Manipulate data items

•   Accept arguments and return one value

•   Act on each row returned

•   Return one result per row

•   May modify the data type

•   Can be nested

•   Accept arguments which can be a column or an   expression function_name [ (arg1, arg2, …) ]

•   Can be used in SELECT, WHERE, and ORDER BY   clauses

The argument can be :

•  User supplied constant

•   Variable value

•   Column name

•   Expression

 

Character Functions : 

  function  purpose

  LOWER(col/expr)  Converts alpha character values to lower case

  UPPER (col/expr)   Converts alpha character values to upper case

  INITCAP (col/expr)   Converts alpha character values to upper case for the first letter of each word, all other letters in   lowercase

  CONCAT(col1 , col2)  Concatenates the first character value to the second character value; equivalent to concatenation   operator ( || )

  SUBSTR(col, m [,n] )  Returns specified characters from character value started at character position m, n characters long

  If m is negative, the count starts from end of the character value.

  If n is omitted, all characters to the end of the string are returned

  LENGTH(col)  returns the number of characters in the expression

  INSTR(col,’string’ [,m], [n])    returns the numeric position of a named string. Optionally, we can provide a position m to start   searching, and the occurrence n of the string. M and N default to 1.

  LPAD(col, n, ‘string’)  Pads the character value right justified to a total width of n character positions

  RPAD(col, n, ‘string’)  Pads the character value left justified to a total width of n character positions

  TRIM(leading/trailing/both,  Enables you to trim heading or trailing characters (or both) from a character string.

           trim_character FROM

  trim_source)

  REPLACE(text, search_string,  Searches a text expression for a character string and, if found, replaces it with specified

  replacement-string)  string.

Arithmetic Operators :  
Arithmetic Operators :  

About OAF

About Oracle SQL

1. Oracle SQL Count Function

The COUNT function returns the number of rows in a query.
The syntax for the COUNT function is:

SELECT COUNT(expression)
FROM tables
WHERE predicates;

COUNT(*) -- Returns the total number of records , irrespective of whether null or not null


 Count(<columname>) -- Returns the total number of not null records based on the given <columnname>.

Count(1) is same as Count(*)

About : Count(1) and COUNT(*)

Since the COUNT function will return the same results regardless of what NOT NULL field(s) you include as the COUNT function parameters (ie: within the brackets), you can change the syntax of the COUNT function to COUNT(1) to get better performance as the database engine will not have to fetch back the data fields.
For example, based on the example above, the following syntax would result in better performance:

SELECT department, COUNT(1) as "No OF Employees"
FROM employees
WHERE salary > 10
GROUP BY department;

Now, the COUNT function does not need to retrieve all fields from the employees table as it had to when you used the COUNT(*) syntax. It will merely retrieve the numeric value of 1 for each record that meets your criteria

 Ex : select count('sridhar') from dual;

Returns : 1

2. Oracle SQL SUM Function

The SUM function returns the summed value of an expression.
The syntax for the SUM function is:

SELECT SUM(expression )
FROM tables
WHERE predicates;

expression can be a numeric field or formula.

Ex : select sum(1) from dual;
Returns : 1


Ex : select sum(1,2,3) from dual;

Returns : Error  : ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"

Ex : select sum(1+2+3) from dual;  Returns : 6

Ex : select 1+2+3 from dual;  Returns : 6

3. Oracle SQL Cursor                                        


A cursor is a mechanism by which you can assign a name to a "select statement" and manipulate the information within that SQL statement.

PL/SQL uses two types of cursors: implicit and explicit. PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including queries that return only one row. However, for queries that return more than one row, you must declare an explicit cursor, use a cursor FOR loop, or use the BULK COLLECT clause.

There are 3 types of cursors :
Implicit, Explicit, Ref Cursor

Explicit Cursor vs Implicit Cursor

When it comes to databases, a cursor is a control structure that allows traversing over the records in a database. A cursor provides a mechanism to assign a name to a SQL select statement and then it can be used to manipulate the information within that SQL statement. Implicit cursors are automatically created and used every time a Select statement is issued in PL/SQL, when there is no explicitly defined cursor. Explicit cursors, as the name suggests, are defined explicitly by the developer. In PL/SQL an explicit cursor is actually a named query defined using the key word cursor.

What is Implicit Cursor?

Implicit cursors are automatically created and used by Oracle each time a select statement is issued. If an implicit cursor is used, the Database Management System (DBMS) will perform the open, fetch and close operations automatically. Implicit cursors should be used only with SQL statements that return a single row. If the SQL statement returns more than one row, using an implicit cursor will introduce an error. An implicit cursor is automatically associated with each Data Manipulation Language (DML) statements, namely INSERT, UPDATE and DELETE statements. Also, an implicit cursor is used to process SELECT INTO statements. When fetching data using implicit cursors NO_DATA_FOUND exception can be raised when the SQL statement returns no data. Furthermore, implicit cursors can raise TOO_MANY_ROWS exceptions when the SQL statement returns more than one row.

What is Explicit Cursor?

As mentioned earlier, explicit cursors are queries defined using a name. An explicit cursor can be thought of as a pointer to a set of records and the pointer can be moved forward within the set of records. Explicit cursors provide the user the complete control over opening, closing and fetching data. Also, multiple rows can be fetched using an explicit cursor. Explicit cursors can also take parameters just like any function or procedure so that the variables in the cursor can be changed each time it is executed. In addition, explicit cursors allow you to fetch a whole row in to a PL/SQL record variable. When using an explicit cursor, first it needs to be declared using a name. Cursor attributes can be accessed using the name given to cursor. After declaring, cursor needs to be opened first. Then fetching can be started. If multiple rows need to be fetched, the fetching operation needs to be done inside a loop. Finally, the cursor needs to be closed.

Difference Between Explicit Cursor and Implicit Cursor

The main difference between the implicit cursor and explicit cursor is that an explicit cursor needs to be defined explicitly by providing a name while implicit cursors are automatically created when you issue a select statement. Furthermore, multiple rows can be fetched using explicit cursors while implicit cursors can only fetch a single row. Also NO_DATA_FOUND and TOO_MANY_ROWS exceptions are not raised when using explicit cursors, as opposed to implicit cursors. In essence, implicit cursors are more vulnerable to data errors and provide less programmatic control than explicit cursors. Also, implicit cursors are considered less efficient than explicit cursors.

REF CURSOR
A REF CURSOR is basically a data type.  A variable created based on such a data type is generally called a cursor variable.  A cursor variable can be associated with different queries at run-time.  The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).
Let us start with a small sub-program as follows:
declare  type r_cursor is REF CURSOR;  c_emp r_cursor;  en emp.ename%type;begin  open c_emp for select ename from emp;  loop      fetch c_emp into en;      exit when c_emp%notfound;      dbms_output.put_line(en);  end loop;  close c_emp;end;

4. Oracle SET Operators

Minus,Intersect, Union , Union ALL

Minus
Intersect
Union
Union ALL

Difference between : Union and Union ALL

Union :