Analytical functions – I
Group functions and Analytical functions
Group functions operate on a set of rows to give one result of a group Where as Analytic functions return multiple rows for each group.

Ex 1:- SELECT deptno, count(*)deptcount
FROM emp
GROUP BY deptno
EX 2:- SELECT empno, ename deptno,
COUNT(*) OVER (PARTITION BY deptno) deptcount
FROM emp

The PARTITION BY clause is just like group by logically breaks a single result set into groups.

In absence of any PARTITION inside the OVER( ) portion, the function acts on entire record set
Ex 3:- SELECT empno, ename deptno,
COUNT(*) OVER () deptcount
FROM emp

analytical functions – ii
order of execution in analytical functions
join
where
group by
having
analytical functions
order by
so analytic functions can only appear in the select list and in the main order by clause of the query.

Analytical functions – III
Difference between RANK and DENSE_RANKRANK and DENSE_RANK functions provide rank to the records based on some column value or expression. Both these functions provide same rank for rows with equal values. The only difference is
Dense_rank:- Rank values are not skipped in the event of ties .
Rank :- Rank values are Skipped in the event of ties
Ex :- . SELECT deptno, ename, sal,
DENSE_RANK()
OVER ( PARTITION BY deptno ORDER BY sal desc ) DENSERANK,
RANK()
OVER ( PARTITION BY deptno ORDER BY sal desc ) RANK FROM emp
ORDER BY deptno, sal DESC

Analytical Functions - IV
LAG / LEAD
These two functions are useful for comparing one row of a result set with another row of a result set.
LAG provides access to a rows which are prior to that position
LEAD provides access to a rows which are beyond that position.(Which are going to come after the current row)

Parameters
1.Expression from which value to be taken.
2.Offset:- It is index relative to the current row . Default is 1
3.Default is the value to return if the <offset> points to a row outside the partition range. Default value is null.

Ex:- SELECT deptno, ename, hiredate,
LAG(hiredate,1,NULL)
OVER (PARTITION BY deptno
ORDER BY hiredate, ename) last_hire,
LEAD(hiredate,1,NULL)
OVER (PARTITION BY deptno
ORDER BY hiredate, ename) next_hire
FROM emp
ORDER BY deptno, hiredate

Analytical functions – V
First Value / Last Value of a Group
The FIRST_VALUE and LAST_VALUE functions allow you to select the first and last rows from a group.
Ex :- How many days after the first hire of each department were the next employees hired?
SELECT empno, deptno, hiredate - FIRST_VALUE(hiredate)
OVER (PARTITION BY deptno ORDER BY hiredate) DAY_GAP
FROM emp
WHERE deptno IN (20, 30)
ORDER BY deptno, DAY_GAP;

NVL2 FUNCTION
The NVL function which checks for the existence of NULL values. NVL2 which checks for the existence of NOT NULL. The syntax for this function is as follows.

NVL2(exp1,exp2,exp3);

If exp1 is not null then the function will return exp2. Otherwise, the function will return exp3.
Example:
Select empno,ename,sal,comm,nvl2(comm,1,0)
from emp order by empno;

Analytical functions – VI
Window Clause
In analytical functions we use The PARTITION BY clause to break a single result set into groups.
We can further sub-partition the result using window clause.
Window clause works with these functions
AVG, COUNT, FIRST_VALUE, LAST_VALUE, MAX, MIN and SUM, STDDEV,VARIANCE .

We can set up windows based on two criteria:
1.Range type window:- The definition is in terms of values before or after the current ORDER..
It is not valid to use RANGE with datatypes other than numbers and dates.

Ex:- SELECT ename, hiredate, hiredate-100 hiredate_pre,
COUNT(*)
OVER (
ORDER BY hiredate ASC
RANGE 100 PRECEDING
) cnt
FROM emp
ORDER BY hiredate ASC
The range window goes back 100 days from the current row's hiredate and then counts the rows within this range

2. Row Type Window:- The windowing clause is in terms of record numbers

Ex:- SELECT deptno "Deptno", ename "Ename", sal "Sal",
SUM(SAL)
OVER (PARTITION BY deptno
ORDER BY ename
ROWS 2 PRECEDING) "Sliding Total"
FROM emp
ORDER BY deptno, ename

The above query access the 2 rows prior to the current row in a group in order to sum the salaries.

The ROW or RANGE window cannot appear together in one OVER clause.

Avoid including a HAVING clause in SELECT statements. The HAVING clause filters selected rows only after all rows have been fetched. Using a WHERE clause helps reduce overheads in sorting, summing, etc. HAVING clauses should only be used when columns with summary operations applied to them are restricted by the clause.

SELECT Deptno, AVG (sal)
FROM emp
GROUP BY deptno
HAVING deptno != 10

This query gives better performance than above query.

SELECT Deptno, AVG (sal)
FROM emp
WHERE deptno != 10
GROUP BY deptno

Difference Between Syntax Check and Semantic Check
Parsing is the first step in the processing of any statement in Oracle. Parsing is the act of breaking the submitted statement down into its component parts. Determining what type of statement it is (query, DML, DDL) and performing various checks on it.
.
The parsing process performs two main functions:
1. Syntax Check: During this process it checks
Is the statement a valid one? Does it follow SQL Syntax rules?

Example for syntax error
select from where 2;
select from where 2
*
ERROR at line 1:
ORA-00936: missing expression

2.Semantic Analysis: Is the statement valid in light of the objects in the database (do the tables and columns referenced exist). Whether the particular user has access to the objects or not ? Are there ambiguities in the statement ? For example if there are two tables T1 and T2 and both have a column X, the query ?select X from T1, T2 where ? is ambiguous, we don’t know which table to get X from. And so on.

Ex :- select * from not_a_table;
select * from not_a_table
*
ERROR at line 1:
ORA-00942: table or view does not exist

difference between hard and soft parsing
oracle uses a piece of memory called the shared pool to enable sharing of sql statements. The shared pool is a piece of memory in the system global area (sga) and is maintained by the oracle database.
After completing first two phases of parsing i.e. Syntax and semantic checks it looks in the shared pool to see if that same exact query has already been processed by another session. If found, the parsed representation will be picked up and the statement executed immediately. This is called soft parsing.
If not found, then it has to go for next two steps in the process, that of optimization (this involves finding an optimal execution path for the statement) and row source generation. This entire process is called hard parsing.
A soft parse will save a considerable amount of cpu cycles when running your query. It is especially important that developers write and design queries that take advantage of soft parses so that parsing phase can skip the optimization and row source generation functions, which are very cpu intensive and a point of contention. If a high percentage of your queries are being hard-parsed, your system will function slowly, and in some cases, not at all.

To print matrix report in sql
SELECT job,
sum(decode(deptno,10,sal)) DEPT10,
sum(decode(deptno,20,sal)) DEPT20,
sum(decode(deptno,30,sal)) DEPT30,
sum(decode(deptno,40,sal)) DEPT40
FROM scott.emp
GROUP BY job

While practicing commands you may disturb defualt tables emp, dept etc. To bring it to the normal form find demobld.sql and execute it.

Example:-
@ d:\oracle\oracle9ids\tools\dbtab90\demobld.sql

Where ever it is possible use UNION ALL , which gives better performance than UNION.
UNION first sorts records, then merge the records and finally eliminates duplicate records. Where as UNION ALL simply returns all rows including duplicates and does not have to perform any sort, merge or filter.

If the FETCH statement in cursors fail to return a row, no exception is raised.
To detect the failure, you must use the cursor attribute %FOUND or %NOTFOUND.
Do not use NO_DATA_FOUND exception in cursors.

Data Dictionary – I
It is a series of table and views that contain information about the structures and users in the database.
It provides
- User names of oracle users.
- The rights and Privileges they have been granted
- Names of database objects
- Constraints applied to a table
- Who accessed and updated database objects

The data dictionary is created in the database when we give create database command.

The information available in data dictionary tables is very difficult to understand. So it provides views in a form that is easier for a users to understand. All these tables and views are owned by SYS.

Data Dictionary Provides three types of views
USER_XXX :- Objects owned by the user and that can be accessed
ALL_XXX :- List of all objects that the user has access to
DBA_XXX :- Users with DBA privilege only can access these views and can access any object in the database

Data Dictionary – II
Useful synonyms for data dictionary views most of the data dictionary views have long names. Public synonyms have been provided for convenient access.
Synonym name synonym for table
cat user_catalog
clu user_clusters
dict dictionary
ind user_indexes
obj user_objects
seq user_sequences
sys user_synonyms
tab user_tables
you can directly use these synonyms in queries
select * from obj

Data dictionary – III
Some dictionary views do not use prefixes user, all, dba. Examples for such views are
dictionary - list of all dictionary objects accessible to the user
dict_columns - lists columns in dictionary objects accessible to the user
role_role_privs - roles which are granted to roles
role_sys_privs - system privileges granted to roles role_tab_privs - table privileges granted to roles
session_privs - privileges which current user has set session_roles - roles which current user currently has enabled

How to use apostrophe?
When the apostrophe/single quote is at the start of the string, you need to enter 3 single quotes for Oracle to display a quote symbol.

SQL> SELECT '''Hi There' Message FROM dual;

MESSAGE
--------------
'Hi There

In the middle of the string, you need to enter 2 single quotes

SQL> SELECT 'He''s always the first to arrive' Message FROM dual;

MESSAGE
-------------------------------
He's always the first to arrive

Single quote is at the end of a string, you need to enter 3 single quotes
SQL> SELECT 'Smiths''' FROM dual;

'SMITHS
-------
Smiths'
If you were to concatenate an apostrophe/single quote in a string, you need to enter 4 single quotes
SQL> SELECT 'There' || '''' || 's Henry' Message FROM dual;

MESSAGE
-------------
There's Henry

Is this thread only for SQL/PL-SQL ?

__________________
Lack of WILL POWER has caused more failure than
lack of INTELLIGENCE or ABILITY.

-sutnarcha-

Hi,

SQL every where!!!

i hope to get tip(s) from testing issues in near future!!!

Thanks & Regards
Trainee_tester