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 :