AdSense

Saturday, 13 July 2013

SQL – Aggregate & Analytic Functions

An aggregate function, as the name suggests, aggregates data from several rows into a single result row. Like, Average, Count, Sum or addition, etc.

SELECT Avg(Salary) FROM emp;
This will give output as single value denoting Average salary in the organization.

Now, add
GROUP BY Dept_no;
This will give output as average salaries for all deparements.

Actually, this will give you error first. Because, all the entities used in Group By clause has to be present in the SELECT statement. So the whole statement will look as

SELECT deptno, AVG(sal) FROM emp GROUP BY deptno ORDER BY deptno;
Here is sample output
1
Thus, aggregate function also reduces the number of rows returned.

Now that you know what is aggregate function, it is simple to understand what is analytic function. Analytic function also work in similar ways as Aggregate function with Group BY clause and reduce the number of rows returned.

SELECT empno, deptno, sal, AVG(sal) OVER (PARTITION BY deptno) AS avg_dept_sal FROM emp;
2

This group of rows, also called "window", operate on set of rows defined by OVER clause. Hence, Analytic functions are also referred as Windowing functions.
Syntax:
analytic_function([ arguments ]) OVER (analytic_clause)
Explore more on this and your SQL will be awesome.

Friday, 12 July 2013

List of Analytical functions in Oracle SQL

List of Analytical functions in Oracle SQL

AVG
CORR
COVAR_POP
COVAR_SAMP
COUNT
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE
LAG
LAST
LAST_VALUE
LEAD
MAX
MIN
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT

REGR_ (Linear Regression) Functions
ROW_NUMBER
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
VAR_POP
VAR_SAMP
VARIANCE

I do believe that whatever an analytic function does can be done by native SQL, with join and sub-queries. However, the same when done by analytic function is never slower, if not faster, when compared to native SQL.
Yes offcourse, I am not considering the amount of time that spent in coding the native SQLs, testing, debugging and tuning them. That is just an additional work.

Thursday, 11 July 2013

SQL – Where, Operators and alias

The SQL Statement

The WHERE Clause is used when you want to retrieve specific information from a table, i.e. filter the data based on conditions. These conditions are specified in the Where clause of SQL statement. For example, when you want to see the information about employees in class HR department only then you apply filter as
WHERE employee_dept = 'HR"
Thus the data is restricted during retrieval and hence query is faster to execute. WHERE clause can be used along with SELECT, DELETE, UPDATE statements.

The Where clause makes use of Comparison Operators and Logical Operators. Comparison operators are used to compare the column data with specific values in a condition. Comparison Operators are also used along with the SELECT statement to filter data based on specific conditions.

Comparison Operators

  1. = equal to
  2. <>, != is not equal to
  3. < less than
  4. > greater than
  5. >= greater than or equal to
  6. <= less than or equal to


There are three Logical Operators namely, AND, OR, and NOT. These operators compare two conditions at a time to determine whether a row can be selected for the output. When retrieving data using a SELECT statement, you can use logical operators in the WHERE clause, which allows you to combine more than one condition.

Logical Operators


  1. OR - For the row to be selected at least one of the conditions must be true.
  2. AND - For a row to be selected all the specified conditions must be true.
  3. NOT - For a row to be selected the specified condition must be false.

SELECT e.emp_name FROM emp_details e WHERE emp_name = 'Sally' OR emp_name = 'Harry'
In this statement - "e" denotes an alias for table emp_details.

Wednesday, 10 July 2013

SQL – Structured Query Language for Database quering

Right now I am assuming that the readers are well aware of what Database means because I would be talking about SQL - a language used to interact with databases. The database in focus will be Oracle.

In Oracle, the SQL is divided into two basic groups: data definition language (DDL) or data manipulation language (DML). DDL is used to define objects within the database just as creating tables or indexes. DML is used to insert, update and delete data in the database. Finally, there is data retrieval, which is the SELECT statement.

Oracle makes use of SQL*Plus (SQL Star Plus) as the command line interface to the let user interact with Database. In a nutshell, SQL*Plus connects to the database, takes user SQL in and displays results to user.

The login format is:

"username/password@db_service_name"
select_statement

The db_service_name is looked up in the TNSNAMES.ora file located in ORACLE_HOME > network > admin > tnsnames.ora - which will be provided by the DBA (administrator).

Please note that SQL*Plus places each command into a buffer (opened as "ed"). You can edit the SQL*Plus buffer, but it is more efficient to create and run database scripts instead. Re-execute an immediately previous command by "/".

Before we look into each and every statement, here are some general points about SQL statements to conclude this post:

  • SQL is not case sensitive
  • Formatting and indentation only makes it readable to us humans. It makes no difference to servers in any aspect
  • Database metadata - data about data - like table names, index names, etc are stored in UPPER case. Use data is stored just how it is entered
  • SQL is not case sensitive