ORACLE AGGREGATE VS ANALYTICAL FUNCTIONS

ORACLE AGGREGATE VS ANALYTICAL FUNCTIONS

For AGGREGATE functions, we always use GROUP BY clause with HAVING (Optional) for grouping the data according to the necessity. Below I am taking two examples. Please carefully see the difference

MIN      : For knowing the MINIMUM value,
MAX     : For knowing the MAXIMUM value, 
SUM      : For knowing the SUM value,
AVG       : For knowing the AVERAGE value, 
COUNT : For knowing number of COUNTS

In the Example -1, it is grouping the data according to the department number so the aggregate function was working only on the department wise but in Example-2, it is grouping the data according to the employee number & department number So the result set has changed






NOW WHEN DO WE USE ANALYTICAL FUNCTIONS
If we want the MIN, MAX, SUM, AVG, COUNT of the salary according to the department wise BUT we need to show how many employees are in departments with the actual  salary also so here in this case we need to use ANALYTICAL functions like below
 
GENERAL SYNTAX
 analytic_function([ arguments ]) OVER (analytic_clause) 

analytic_clause is further divide into the optional elements
[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]

1. The query_partition_clause divides the result set into partitions, or groups, of data.
2. The operation of the analytic function is restricted to the boundary imposed by these partitions, similar to the way a GROUP BY
    (Refer the Example-2 Above) clause affects the action of an aggregate function.
3. If the query_partition_clause is omitted, the whole result set is treated as a single partition.
4. If we change the OVER clause to include a query_partition_clause based on the department

-------

SDLC

SOFTWARE DEVELOPMENT LIFE CYCLE
(SDLC)

Most of the interviewers ask about the SDLC. It is a way where we can make a plan a better software management & maintenance. We have some stages under which a SDLC works

1.     Planning (feasibility study about problems & it’s best solution)

Here the Business Analyst (the one who is putting the money or sponsoring the project), Top Management & IT department will discuss about what exact is the need of the client, what problems client has been facing continuously. A planning team gathers the requirements from the clients & will decide what to do best for them.
Charter document, Scope & Plan document we need to create under this phase after the agreement of top level people 

2.     Analysis (Define what to do)

Here the BA, Top management & IT department will define about the project i.e. the project is an application based or a website based. Once the top level people have decided then it’s a need to create a requirement document, a project plan & the testing plan for it. So BA, Project Manager (PM), Quality Analysis (QA) i.e. testing cases, Developers & the SME’s (Subject Matter Experts) i.e. these people interact with BA, end users and come with the requirement documents.

3.     Designing

Under this section, with the  help of  BA a functional design document will prepare in detail, On the basic of this functional design document the developers will prepare a technical design document on which a developer will develop the features, updated project plan by the project manager on the basis of functional desighn document & technical design document & the updated QA plans 

4.     Build

Under this section, on the basis of technical design document the developers will code the software & prepare the software for testing mode before implementation 

5.     Testing
Once the software will build by the developers, there is a need to do the proper testing of the software, So here QA will prepare some test cases and test the software properly before implementations. Once there is an assurance by the QA team & UAT (user acceptance testing i.e. the end user’s) the software will ready to deploy on the client side. 

6.     Implementation (Deployment)

Under this section, the tested product i.e. tested software will move in the environment where the end user can start using the software. That’s also called Production Environment 

7.     Maintenance
This is the last phase under which once the software will work successfully then a developer needs to maintain the consistency of the software i.e. Bug Fixes, Optimizing database and much more.

THIS IS CALLED SDLC

NOTE: IF YOU HAVE DOUBTS, KINDLY CONTACT ME VIA THIS BLOG

ORACLE ADVANCED FUNCTIONS (GENERAL FUNCTIONS)

DECODE & CASE FUNCTION
The output through Decode & Case follows IF-THEN-ELSE statement. Below are some differences in Decode & Case

NVL, NVL2, NULLIF, COALESCE  FUNCTION

NVL(String, Replace_With) : NVL function is basically use for searching NULL values. This function will search 

                            null value & replace with the mentioned value.

NVL2(String,RETURN(NOT NULL), RETURN(NULL)) : NVL2 function is also use to search the null values, this function

                                              will search the null values from the string, If it is not null then

                                              it will return first return, If NULL then second return.
 

NULLIF(Expression1, Expression2) : NULLIF function will compare the Expression1 & Expression2. If both are equal

                                   then it will return NULL other wise It will return Expression-1. Expression-1 

                                   should not be null otherwise it will also return null

COALESCE(Exp1, Exp2,...Expn) : COALESCE function is use for searching the null values again. Here it will search 

                               Null in exp1, if it is there then it will return Exp2, if Exp2 is also Null then it

                               will return Exp3 ... up to Expn. 

UID, USER, USERENV  FUNCTION

UID : UID function returns the id number for a user's session (the user who is currently logged in)

USER : USER function returns the user_id name from the current Oracle session

USERENV : USERENV function can be used to retrieve information about the current Oracle session. Although this function still exists in Oracle for
                  backwards compatibility, it is recommended that you use the SYS_CONTEXT function. We have some parameters below

SYS_CONTEXT : SYS_CONTEXT function can be used to retrieve information about the Oracle environment. Below are the parameters

Parameter	Explanation	Oracle 9i	Oracle 10g	Oracle 11g
ACTION	Returns the position in the module	No	Yes	Yes
AUDITED_CURSORID	Returns the cursor ID of the SQL that triggered the audit	Yes	Yes	Yes
AUTHENTICATED_IDENTITY	Returns the identity used in authentication	No	Yes	Yes
AUTHENTICATION_DATA	Authentication data	Yes	Yes	Yes
AUTHENTICATION_METHOD	Returns the method of authentication	No	Yes	Yes
AUTHENTICATION_TYPE	Describes how the user was authenticated. Can be one of the following values: Database, OS, Network, or Proxy	Yes	No	No
BG_JOB_ID	If the session was established by an Oracle background process, this parameter will return the Job ID. Otherwise, it will return NULL.	Yes	Yes	Yes
CLIENT_IDENTIFIER	Returns the client identifier (global context)	Yes	Yes	Yes
CLIENT_INFO	User session information	Yes	Yes	Yes
CURRENT_BIND	Bind variables for fine-grained auditing	No	Yes	Yes
CURRENT_SCHEMA	Returns the default schema used in the current schema	Yes	Yes	Yes
CURRENT_SCHEMAID	Returns the identifier of the default schema used in the current schema	Yes	Yes	Yes
CURRENT_SQL	Returns the SQL that triggered the audit event	Yes	Yes	Yes
CURRENT_SQL_LENGTH	Returns the length of the current SQL statement that triggered the audit event	No	Yes	Yes
CURRENT_USER	Name of the current user	Yes	No	No
CURRENT_USERID	Userid of the current user	Yes	No	No
DB_DOMAIN	Domain of the database from the DB_DOMAIN initialization parameter	Yes	Yes	Yes
DB_NAME	Name of the database from the DB_NAME initialization parameter	Yes	Yes	Yes
DB_UNIQUE_NAME	Name of the database from the DB_UNIQUE_NAME initialization parameter	No	Yes	Yes
ENTRYID	Available auditing entry identifier	Yes	Yes	Yes
ENTERPRISE_IDENTITY	Returns the user's enterprise-wide identity	No	Yes	Yes
EXTERNAL_NAME	External of the database user	Yes	No	No
FG_JOB_ID	If the session was established by a client foreground process, this parameter will return the Job ID. Otherwise, it will return NULL.	Yes	Yes	Yes
GLOBAL_CONTEXT_MEMORY	The number used in the System Global Area by the globally accessed context	Yes	Yes	Yes
GLOBAL_UID	The global user ID from Oracle Internet Directory for enterprise security logins. Returns NULL for all other logins.	No	No	Yes
HOST	Name of the host machine from which the client has connected	Yes	Yes	Yes
IDENTIFICATION_TYPE	Returns the way the user's schema was created	No	Yes	Yes
INSTANCE	The identifier number of the current instance	Yes	Yes	Yes
INSTANCE_NAME	The name of the current instance	No	Yes	Yes
IP_ADDRESS	IP address of the machine from which the client has connected	Yes	Yes	Yes
ISDBA	Returns TRUE if the user has DBA privileges. Otherwise, it will return FALSE.	Yes	Yes	Yes
LANG	The ISO abbreviate for the language	Yes	Yes	Yes
LANGUAGE	The language, territory, and character of the session. In the following format: language_territory.characterset	Yes	Yes	Yes
MODULE	Returns the appplication name set through DBMS_APPLICATION_INFO package or OCI	No	Yes	Yes
NETWORK_PROTOCOL	Network protocol used	Yes	Yes	Yes
NLS_CALENDAR	The calendar of the current session	Yes	Yes	Yes
NLS_CURRENCY	The currency of the current session	Yes	Yes	Yes
NLS_DATE_FORMAT	The date format for the current session	Yes	Yes	Yes
NLS_DATE_LANGUAGE	The language used for dates	Yes	Yes	Yes
NLS_SORT	BINARY or the linguistic sort basis	Yes	Yes	Yes
NLS_TERRITORY	The territory of the current session	Yes	Yes	Yes
OS_USER	The OS username for the user logged in	Yes	Yes	Yes
POLICY_INVOKER	The invoker of row-level security policy functions	No	Yes	Yes
PROXY_ENTERPRISE_IDENTITY	The Oracle Internet Directory DN when the proxy user is an enterprise user	No	Yes	Yes
PROXY_GLOBAL_UID	The global user ID from Oracle Internet Directory for enterprise user security proxy users. Returns NULL for all other proxy users.	No	Yes	Yes
PROXY_USER	The name of the user who opened the current session on behalf of SESSION_USER	Yes	Yes	Yes
PROXY_USERID	The identifier of the user who opened the current session on behalf of SESSION_USER	Yes	Yes	Yes
SERVER_HOST	The host name of the machine where the instance is running	No	Yes	Yes
SERVICE_NAME	The name of the service that the session is connected to	No	Yes	Yes
SESSION_USER	The database user name of the user logged in	Yes	Yes	Yes
SESSION_USERID	The database identifier of the user logged in	Yes	Yes	Yes
SESSIONID	The identifier of the auditing session	Yes	Yes	Yes
SID	Session number	No	Yes	Yes
STATEMENTID	The auditing statement identifier	No	Yes	Yes
TERMINAL	The OS identifier of the current session	Yes	Yes	Yes

NOTE: IF YOU HAVE DOUBTS, KINDLY CONTACT ME VIA THIS BLOG