Cursors

In this blog post, we will be learning about Oracle PLSQL Cursors.

Following are the topics, we will go through -

1. Cursor - What is a Cursor
2. Different types of Cursor
3. Cursor Attributes
4. Implementation of Cursor - Example
5. For Cursor Loop
6. Parameterized Cursor - Example.


Lets start -

Meaning of Cursor  -  Acts like a pointer to a context area. When Oracle starts executing any SQL Statement, it allocates an area globally in the server i.e in PGA ( Program Global Area ). This area is the Context area. Cursor acts as a handler to this area.

Types of Cursor  -  There are two types of Cursor

1. Implicit Cursor
2. Explicit Cursor


Implicit Cursor - As the name suggests, Implicit cursor is managed by Oracle Database, and these type of Cursors don't need to be open or close manually. Oracle does this for programmer.

Implicit Cursor has following attributes -

1. SQL%ROWCOUNT -  Returns Number of Rows affected by any 'Select Into', or 'Insert', 'Delete' or 'Update' statement.

2. SQL%FOUND - Returns 'TRUE' ( Boolean ),  when one or more rows is affected by any 'Select Into', or 'Insert', 'Delete' or 'Update' statement. Otherwise, it will return FALSE.

3. SQL%NOTFOUND - Returns 'TRUE' ( Boolean ),  No row is affected by any 'Select Into', or 'Insert', 'Delete' or 'Update' statement. Otherwise it will return FALSE.

4. SQL%ISOPEN - Checks if the Cursor is still open or has been closed. It would return FALSE, as Implicit Cursor is managed by Oracle and will always Close it after the operation.

5.SQL%BULK_ROWCOUNT - It is a composite attribute and suitable for FORALL Statement. It will return ZERO if, no row is affected by FORALL.

6. SQL%BULK_EXCEPTIONS - Acts like an Associative Array and good to use with FORALL Statement, and it keep tracks of any exception occurred in FORALL.




Explicit Cursor -  A cursor that needs to be defined, open and close at the end by the programmer.


Lets see some example -
Here, the explicit cursor 'test_cur' is defined in the 'declaration section', opened and closed in the block. 

I used one extra line in below example, to show implicit Cursor - as number of rows affected - test_cur%rowcount.

Cursor for loop - We will see the same example, using cursor for loop and also, we will see its advantages. Here, we have explicitly defined the Cursor.

Same example which sometimes come very handy, is as follows  - see the changes in the line, here, we have not defined the Cursor explicitly - 

Parameterized Cursor - 

Cursor can also be defined based on the parameter passed. Lets see the example, its very clear  to understand

Thank you !!

Oracle Analytical Functions

In this blog post, we will talk about Oracle Analytical Functions -

We will discuss the following one by one , as follows

• Meaning of Analytical functions
• Difference between Analytical and Aggregate functions
• Different types of Analytical function which are commonly used with syntax
• Restrictions of Analytical Function

Analytical Function , what it means -  The reason, some functions are called Analytic because, not only they can compute aggregate values for a specific region of data, but also can calculate the changes in the aggregate values over the time.

For example, an Aggregate function may give you an average salary and is always return one single row for a region of data. However, an Analytical function can give you a moving or incremental average and can return multiple row for a specific region of data. 

Changes in the averages over the time can be calculated using Analytical functions very easily. 

Most commonly used Analytical functions are as follows - 

• RANK
• DENSE_RANK
• PERCENT_RANK
• LAG
• LEAD
• FIRST_VALUE and LAST_VALUE

RANK -  Rank calculates the Rank of the data in a specified result set. Lets use Rank to find the Rank of a particular salary in employees table. See the screenshot -

Now lets try finding Rank of all the rows within each department. See, for each department, function has returned a specific rank. If employees being in the same department, gets the same salary, they will get the same rank. see, for employees 128 and 136, both of them get rank -2. 

Another thing to notice, see in case of Department - 50, Employee - 135 got the Rank -4, but not Rank -3. Rank 3 is skipped. Similarly, Employee - 191 got the Rank - 6 but not Rank -5. Rank -5 is skipped.

Partition_by clause, is used to segregate departments here ( partition by department ) and order by clause is used to sort on the basis of salary. However, in general syntax, both partition by and order by clause are optional.

DENSE_RANK - Dense Rank will find the Rank , same as Rank does, but in case of Dense Rank, no Rank is skipped. Lets see the example, then we will discuss -

Here, Employee - 135 got the Rank -3, and Employee - 191 got the Rank - 4. No Ranking Number is skipped. That's the difference between Rank and Dense_Rank.

PERCENT_RANK - Percent Rank is another form of Rank or Dense Rank, just it returns a cumulative distribution. Lets see the example..

LAG and LEAD - LAG will give you the previous row and LEAD will return the row after the current row. Lets see the example, it will be clear -

Here the format used as LAG ( value_expression, offset, default ) over ( partition by clause order by clause )

LAG has displayed just the previous order date of the Agent, here ( offset = 1 ). When offset =2, it will display previous of previous order date and so on.

default is optional, otherwise in this example, you can use any default Date.
Hope this clears.

Similarly, LEAD will display the Next Order Date, depends upon your offset value. See the example -

FIRST_VALUE and LAST_VALUE - As the name suggests, they will return the first and last of the record upon which analytic function worked. Lets see the example, it is pretty much easy -

Windowing Clause -  Analytical functions syntax comes with an optional parameter, which is a window clause over(partition by clause , order by clause, window). Hence, some time, Analytical functions are also called Window functions.

Types of Windows , we can use are as follows -

1. Rows or Range between startpoint and endpoint.
2. startpoint or endpoint can be one of the following -

     a. Unbounded preceding - As name suggests, start point would be the beginning of the ordered set in consideration.
     b. Unbounded following - As name suggests, endpoint would be the end of the ordered set in consideration.
     c.  Current row - Points at the current position.
 

So, Window clause can look like ( for exampale )

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.


Default Windowing clause is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW


Lets see an example -

That's all folks. Thank you .

Types SQL/PLSQL Statement

In this blog, we will be learning the classification of SQL Statements -

Classifications are as follows -

1. DDL - These are Data Definition Language. Some references also say, these are Data Description Language. It mainly deals with Database structures and Objects creation. It doesn't deal with Data, only the structure of database objects. 

Exmaples of DDL Statements are as follows -

• CREATE
• DROP
• ALTER 
• TRUNCATE -  Explains below , as this is a special type.

2. DML - DML Stands for Data Manipulation Statements. It deals with the manipulation of Data in Oracle. Examples of DML are as follows -

• SELECT
• INSERT
• UPDATE
• DELETE
• MERGE
• CALL - These statements are used to CALL a function or any other subprograms.
• EXPLAN PLAN -  These are used to interpret the way of accessing the Data. Most often, we use Explan Plan for Performance Tuning.

3.  DCL - DCL Stands for Data Control Language. It deals with controlling the Access to Data. Examples of DCL are as follows - 

• GRANT
• REVOKE

4. TCL - TCL Stands for Transaction Control Language. It mainly deals with managing transactions in the Database. Transaction means any operations made on the Data. Examples of TCL are as follows -

• COMMIT
• SAVEPOINT
• ROLLBACK

Below are the frequent questions which one should be aware of - 

DDL Statements do not require explicit commit - why ?

Before starting any answer, let go back to Data Dictionary -  Data Dictionary in a Database are nothing but flatfiles which contains Metadata i.e Data about the Data. Mainly Data Dictionary contains the following -

• Names of all the Tables, along with their owners, constraints and relationships with other relational database objects, how it is organised across Partitions, if any, etc.  If you query DBA_TABLES, ALL_OBJECTS, etc, you will get these metadata details 

Please see the below screenshot - 

The reason, why DDL statements do not require explicit Commit is rather a difficult question - I will try to jot down a few points, may be that will help -

• DDL Statements directly writes to Data Dictionary. 
• Architecture was made long back - Thinking should be like this - Nobody will try rolling back ( saving the intermediate transaction in any segments ) while creating database objects.  If they want they can drop the Structure, so no need of undo logs, etc.
• Since DDL affects Data Dictionary, its affecting query running sessions.

DELETE - DDL or DML - Many opinions are there - ?

DML deals with Manipulation of Data, theirs nothing to do with structure of the database. Keeping in mind this, we can say, DELETE deletes the Data and not the Structure of the Data. Tables remain as it is - hence, DELETE is DML.

What about TRUNCATE  - DML or DDL? 

Truncate deletes the Data ( all the Data ) from a table without saving any intermediate forms in rollback segments, thus doesn't requires explicit Commit.  Although, it doesn't act on Database structures but on Data ( deleting or manipulating the Data ), Oracle classify Truncate as DDL, not a DML. Some references say, its a special type of DML/DDL, etc.

We will talk more about Truncate in "Differences between Drop, Delete and Truncate"





Well, that's all for now - Thank You

Diff. Between Drop, Delete and Truncate

Here in this post, we will learn the differences between Drop, Delete and Truncate in SQL.

We will also see some practical examples using SQL Queries -

·        DROP –

o   DROP is a DDL Statement.

o   Removes one or more Tables from the Database along with the Database Structure.

o   We cannot drop a Table which is Referenced by a Foreign Key Constraint.

o   The Transaction cannot be Rolled back, if we Drop a Table.

·        DELETE –

o   DELETE is a DML Statement.

o   Delete all or some of the Rows of the Table, also can be clubbed with ‘where’ condition.

o   Delete involves ‘one by one’ records and thus records entry in Transaction Logs, and thus ‘Rollback’ is possible.

o   It does not change any property of the Table.

o   It is much slower than Truncate.

o   Number of Deleted Rows are returned.

·        TRUNCATE –

o   Truncate is a DDL Statement.

o   It Deletes all the Rows at a time and cannot be Roll back.

o   It is much Faster than Delete statements.

o   ‘where’ condition is not applicable in Truncate statement.

o   Number of Deleted Rows are not returned.


We will discuss on the examples below  - 

Lets take the example of emp_test and dept_test. Dept_id in dept_test is a foreign_key constraint to dept_id of emp_test table -

Table -  emp_test  has the following 
      Primary Key -  employee_id, Foreign Key - Department_id
Table - dept_test has the following
      Primary Key - department_id


Lets try to delete emp_test. See what happens -

SQL > delete emp_test;

emp_test is now empty -

Now we will rollback and see, if we can get the values back and we are getting back.

Lets see, when we delete using some condition 

Now lets try delete from dept_test using some condition. Remember we defined some foreign key constraint on department_id. Lets see -

I could not delete the row because, I tried to violate a foreign key constraint. However, we can solve this Error using 'On Delete Cascade' key word. We first need to add or ALTER the constraint and add the keyword 'On Delete Cascade'. Now, if we try to delete, it will also delete the connected rows, eliminating the constraint.


Now, lets try TRUNCATING the table emp_test. Lets see what happens -

Lets try to Roll it back and see if we can get the Data back.

So, here, nothings coming back. its an implicit commit, DDL Statement.


Now lets try DROP Statement. 

The entire table structure is gone.












Now lets try DROP dept_test. We will get a Referential Integrity constraint error, as we have a defined Foreign Key Constraint and connected to emp_test. Lets see -


Thats all for now  - Thank You !!

SQL Joins

In this Post, we are going to discuss on the different types of Joins of SQL.
Lets no buy time on the introduction, here are the important things which needs to be remember -

Note - What is the maximum number of Joins, you can do in Oracle. This was a question in Interviews. -  Well, there is no documented limit, as of now. But yes , there should be practical limit, to get benefits from Joining.

Join is a way of combining two or more tables from the Database, to get the desired result set, as simple as that. Although some Interviewers might not be happy with the answer. You can go on explain different Joins to get attention.
For convenience, lets use the following two tables - Workers & Section

Table - Workers

Table - Sections

So, these are pretty simple Tables, now lets Join these two tables. You can see, both the Table contains Section ID, so we will be using the Section for Joining -
Lets Start -

• Inner Join - Inner Join will fetch the common data from the above two tables. We will join the two tables using the common Section ID. Here are the results below -

As you can see, the Sections with ID 90, 80 & 60 do not appear in the result set. Its that simple.
Note - Just to clear the concept, you can see or understand via Venn Diagrams, "common portion between the two circles".

Differences between "ON" clause and "USING" clause in Join
We can also join using "USING" clause, when we have explicit common column to join. Here, "Section_ID" present in both the tables, hence we can use "USING" clause. But, there will be a minor change in the result set. Just see below -


See, the column "Section_ID" do not appear in the result Set. This was another Interview questions and people get confused.

Lets do it via old syntax, and this will give you the same result as using "ON" clause, as follows -


What will happen if you use "JOIN" instead of "INNER JOIN" in the above queries -  Well, "JOIN" means, simple Inner Join and there's no hype or differences.

• Left Outer Join - Left outer join, will display all the results or records from your left table, along with the matched records from the right table. If some records do not match, result set will display blank for those records. Just see the example, you will understand.

Interview questions  - How will you display all the records from your first table , along with all the matched records from your second table. If matched, display the values, if not matched, then display blank. What will be your preferred way, like, "case", "decode", etc. -- Well, just use fundamentals of "left Outer Join" or "right outer Join".




You can also use the old syntax using the following query - only one simple thing to notice here -
select * from workers w, sections s where w.section_id = s.section_id(+);
'plus' sign is on the right table to indicate "left outer join". Don't get confuse here.

• Right Outer Join - Its just the opposite of left outer join. Result set will display all the records from the right table and matched records from the left table. Unmatched records of the left table will be blank. Just see the example -

For the old syntax, use the following query and notice the 'plus" sign is on left side.
select * from workers w, sections s where w.section_id (+) = s.section_id;

• Full Outer Join - This is interesting. Result set will display matched results from both left and right tables; unmatched results will be blank, from both left and right tables. See the example -

Now, do not try to use 'Plus' sign in both the sides for other syntax. Well you can't outer join both the tables -this is the error you will get.
You can also omit the "outer" keyword from the above query. The following query will give you the same result -
select * from workers w full join sections s on w.section_id = s.section_id;

• Cartesian Product or Cross Join -  As the name suggests, result set will display the cartesian product of the two tables, i.e for every record in left table, there will all records in right table, thus, if left table contains 8 records and right table have 8 record, cartesian product will fetch 8X8 records i.e. 64 records. Here is the example below -

Note - You do not have to use 'ON' or 'USING' clause , otherwise it will lead to error.
You might have noticed some other JOIN terms in the internet. Lets discuss on them to clear the confusion.

• Equi Join  - Equi Join is nothing but an Inner Join, using an 'equality' operator. This is same as using 'ON' clause on a Inner Join or Join. The following query is nothing but a Equi Join.

We have discussed this above, saying its a oracle syntax.

• Non-Equi Join - If we use the Inner Join oracle syntax, using operator , other than "=", it will be a Non-Equi Join. e.g. >=, <=, etc. Its that pretty simple.
• Natural Join - Natural Join is again a Equi join, only difference is , it will display the common column only once. This behaves the same way, as using an Inner Join using "USING" clause.

• Self Join - Self Join means Joining with itself. One table, two aliases, behaves as two different tables and Join. The most common example of Self Join, is when an "employee" table contains Employee_ID as well as their "Manager_ID", who themselves are nothing but the Employees.

Alternatively, we can use "hierarchical Queries", which I will discuss in separate Post.
Good Luck !! Any comments or questions is appreciable.

SQL SET Operators

As the name suggests, a SET operator will give you a combined results of two or more queries. The point that needs to remember is all the queries should fetch the same number of columns and the data type of the columns should match.

Lets discuss, with examples to avoid confusion. We will use two tables, Workers & Labourers.

Table -  Workers

Table - Labourers

Lets start with the SET Operators -

• UNION -  If you use UNION Clause to fetch the combined results from both the above tables, result set will contain the distinct records, thus it automatically eliminates the duplicate records. See the screen shot below -

Here,  for example, "Shelley Higgins" is present in both the tables, but 'UNION' shows only once.

• UNION ALL - UNION ALL behaves the same way, as UNION does, except, it doesn't eliminate the duplicate rows. See below -

• INTERSECT - The 'Intersect" Set operator fetch the common records, from both the tables. See the results below. You might get confused with the "Inner Join". Lets sort it out -

Notice -  The result set displaying the records from 'Workers' table, as its in the left hand side of the query. Thus, intersecting portion of the 'Workers' table is fetched into the result.
Differences between "Intersect" and "Inner Join"
Now when you perform "Intersect", query parses the entire record from the table i.e. it finds out common record including all the columns. But, in case of "Inner Join", it only performs Join operation on the specified or mentioned columns that is Joining.
There is another difference - "Intersect" always gives you all the distinct records, while "Inner Join" may give you duplicates.
Note - What if we use "Inner Join" with a "Distinct" Clause -  Well, here Inner Join behaves exactly like a "Intersect", while retrieving the common columns from both the Tables. 
Lets see the result of the following query -

• MINUS - It behaves as the name suggests. Result set will eliminate the records from the first table, which are present in the second table, while doing a "Minus" of second from the first. See the results -

So, whats the difference between Full Outer Join & MINUS operator.

Can they fetch same results - No, absolutely not. 'Minus' will display the records from the left table i.e. 'from where they want to minus'. But any 'Outer Join' will display result from both tables, only the values will be NULL, if not matched.


That's enough of set operator !! Good Luck !!

SQL Aggregate Functions

In this post, we will discuss on the Aggregate Functions on SQL. To show results, we will use the following table.  Lets start -
Table - Workers ( Its a replica of Employees Table )

• MIN , MAX & AVG - These are simple functions, used to fetch minimum ( MIN ), maximum (MAX) and average of the elements. These are very simple to understand. Look at the results -

• Count(column_name ) , Count(*), count(1) - These are pretty simple. Count(*) will return the Total  Number of Rows in the table. 'Count' will return the total number of values present in that column.

Note -  Questions  - What will happen if you want to return the number of Column Values, containing  NULLS. - Well, Count will not consider the 'Null' values into account.
"Count" will always give you the Numbers of Non-Null values.
Look at the results -
What about Count(1) or Count(0), or may be Count(2) :) - Now, before proceeding, i would like to note down following points to avoid misconception.

1. Count (1) doesn't mean the 'first' column in the SQL Query. It's just an expression.
2. Count (* ) doesn't mean the same as 'Select *", following that 'all things gets loaded..", no, it's also an expression.
3. Count(any_expression) returns all non-null count, from the object. Hence, count(*), count(1) or count(0) , all the just expressions, and will give you the same result.
4. Performance wise, now-a-days, they give the same result. In earlier versions of Oracle, it might had been different, but that is also due to Oracle optimizer.

You can also check 'Execution Plan' of all these Count (s).
All screenshots present -


Questions  -  Write a SQL Query to get the Name of the Employees, who gets Maximum Salary from Each Department - Very Common Question -- Lets see the Query below -

I could have used "Exists" instead of "IN", will discuss this in "Performance Tuning".
Questions - Write an SQL Query to find out the Name of the Employee, who gets Maximum of all the Average Salary, for Each Department -  "You should not try to group by Max(avg(salary)) ", because it will lead to the error !!
Here is the query -