Diksha IT Training Centre

          Analytical  Functions in Oracle and it’s benefits   Hello Everyone, Today we will discuss about Analytical Functions in Oracle. Analytic functions in Oracle calculate an aggregate value based on a group of rows and return multiple rows for each group. In other word we can say Analytical functions are used to do analyze data over multiple rows and return the result in the current row. Also with   analytic functions, however, you can group the data that is used to calculate the function’s result, but show a value one each record in the result set, without grouping as well.   Advantage of Analytical Function is as below: Improve the Performance of Query Analtical Functions   are easier to write than sub queries and joins to get the same result. Easy to maintain. Before Starting of analytical function we need to understand below two points: OVER: The scope of an analytical function is defined in an OVER clause. OVER is mandatory for all analytical functions.

  Character Function SUBSTR and INSTR in Oracle   Hello Everyone , Today we will discuss about character Function SUBSTR and INSTR in Oracle SQL and its uses. In SQL Character Functions mainly operate on Character data , Character Function can be used with columns having character data.   SUBSTR :   SUBSTR functions allows you to extract a substring from a string. SUBSTR function returns a string value . If length is negative then SUBSTR function return a null value. Note:   If the position is positive, then Oracle Database counts from the beginning of char to find the first character. If the position is negative, then Oracle counts backward from the end of char Example of SUBSTR Function in Oracle SQL. To show uses of SUBSTR with different parameter I will take example from dual table and existing table in a database. To get the first 3 character we need to write below SQL query by using SUBSTR function to get the result. // GET FIRST 3 CHARACTER FROM DUAL TABLE

                Set Operator uses and Benefits in Oracle Database   Today we will discuss about different Set operators used in SQL with examples. SET operator used to select data from multiple tables as Set operators combine the results of two component queries into a single result. Queries containing set operators are called compound queries. Through SET Operator in SQL we don’t need to use Joins and make alias of table to get the common record and all distinct row selected by query. SQL Set Operators are of 4 parts which work similar to Mathematics which we learn in class 10 th  or so. 1.     UNION 2.     UNION ALL 3.     INTERSECT 4.     MINUS/EXCEPT.     Things to Remember while using SET operator: Select clause should have the same number of columns and columns must be of same data type, Column needs to be specified in the same order in the select clause.   UNION: UNION set operator use to select all distinct rows selected by either query. UNION returns al

  Data Dictionary and its importance in Oracle Database.   Today I will discuss about Data Dictionary table in Oracle Database. Data Dictionary table provide information needed by those who build systems and applications that support the data. If you know the data dictionary table names you can able to see the objects present in the database as well as also you can able to see the design , relations etc by using Data Dictionary table. Let understand about Data Dictionary first. Data Dictionary: Most important parts of an Oracle database is its data dictionary, which is a read only set of tables that provides information about the database. In Oracle database data dictionary provide useful information about the database including schema, users, privileges etc. The values in these stored dictionaries are updated automatically by Oracle anytime a statement is executed on the server that modifies the data. Note: Read-only data dictionaries can be read and queried just like any othe