Home
Search results “Oracle lag null”
SQL Tutorial - Window Functions - Lag and Lead
 
08:10
Another video brought to you by BeardedDev, bringing you tutorials on Business Intelligence, SQL Programming and Data Analysis. In this video I talk about using Lag and Lead in Windows Functions. This video covers the requirements to pass the exam: If you would like to follow along with the tutorial please run the SQL below: IF OBJECT_ID(N'dbo.Sales', N'U') IS NOT NULL DROP TABLE dbo.Sales; GO CREATE TABLE dbo.Sales ( Sales_Id INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_Sales_Sales_Id PRIMARY KEY , Sales_Customer_Id INT NOT NULL , Sales_Date DATETIME2 NOT NULL , Sales_Amount DECIMAL (16, 2) NOT NULL ) INSERT INTO dbo.Sales (Sales_Customer_Id, Sales_Date, Sales_Amount) VALUES (1, '20180102', 54.99) , (1, '20180103', 72.99) , (1, '20180104', 34.99) , (1, '20180115', 29.99) , (1, '20180121', 67.00) Lag and Lead are useful for performing trend analysis, in the example I show how we can display a customer spending trend. Lag will show the previous value. Lead will show the next value. Lag and Lead accept multiple parameters as demonstrated in the video: LAG([Column], [Offset], [Value if NULL]) The example of LAG and LEAD in the video can be shown by executing the below SQL query: SELECT Sales_Customer_Id , Sales_Date , LAG(Sales_Amount, 2, 0) OVER(PARTITION BY Sales_Customer_Id ORDER BY Sales_Date) AS PrevValue , Sales_Amount , LEAD(Sales_Amount, 2, 0) OVER(PARTITION BY Sales_Customer_Id ORDER BY Sales_Date) AS NextValue FROM dbo.Sales Please feel free to post comments.
Views: 1662 BeardedDev
Lead , Lag ,  First Value , Last Value ,  Keep First , Keep Last | Oracle SQL Tutorial | Mr.Murali
 
43:44
Lead , Lag , First Value , Last Value , Keep First , Keep Last in Oracle SQL | Oracle SQL Tutorial | Mr.Murali ►For Registration : https://goo.gl/r6kJbB ►Call: +91-8179191999 ► Visit Our Website: http://nareshit.in/ http://nareshit.com/ ► About NareshIT: "Naresh IT is the Best Software Training Institute for Hadoop, Salesforce, AWS, DevOps, Sprak, Data Science, Python, Tableau, RPA ,Java, C#.NET, ASP.NET, Oracle, Testing Tools, Silver light, Linq, SQL Server, Selenium, Android, iPhone, C Language, C++, PHP and Digital Marketing in Hyderabad, Chennai and Vijayawada, India which provides online and classroom training classes" ►For Registration : https://goo.gl/r6kJbB ►Call: India- 8179191999, USA- 404-232-9879 Email: [email protected] ►Our Online Training Features: 1.Training with Real-Time Experts 2.Industry Specific Scenario’s 3.Flexible Timings 4.Soft Copy of Material 5.Share Video's of each and every session. Check The Below Links: ►For Course Reg : https://goo.gl/r6kJbB ► Subscribe to Our Channel: https://goo.gl/q9ozyG ► Circle us on G+: https://plus.google.com/NareshIT ► Like us on Facebook: https://www.facebook.com/NareshIT ► Follow us on Twitter: https://twitter.com/nareshitech ► Visit Our Website: http://nareshit.in/ http://nareshit.com/
Views: 10051 Naresh i Technologies
Analytics: 18 Lag, Lead - Dealing with null
 
03:38
blog: https://connor-mcdonald.com Welcome to the KISS video series. Solving problems that typically required complicated SQL in the past, that can now be easily solved with Analytic SQL syntax. In this session, we look at using our IGNORE NULLS extension for lag and lead to solve the "fill in the gaps" problem when rows are missing data. Scripts: https://livesql.oracle.com/apex/livesql/file/content_C0SXW1PG37G8REE0VTQH5LXJ8.html
Views: 269 Connor McDonald
Oracle LAG & LEAD Functions / Arabic
 
11:23
--The LAG function is used to access data from a previous row --LAG (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause) --value_expression - Can be a column or a built-in function, except for other analytic functions. --offset - The number of rows preceeding/following the current row, from which the data is to be retrieved. The default value is 1. --default - The value returned if the offset is outside the scope of the window. The default value is NULL. -- The LEAD function is used to return data from the next row. --LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
Views: 679 khaled alkhudari
KISS series on Analytics: 18 Lag, Lead - Dealing with null
 
03:31
Welcome to the KISS video series. Solving problems that typically required complicated SQL in the past, that can now be easily solved with Analytic SQL syntax. In this session, we look at using our IGNORE NULLS extension for lag and lead to solve the "fill in the gaps" problem when rows are missing data. Scripts: https://livesql.oracle.com/apex/livesql/file/content_C0SXW1PG37G8REE0VTQH5LXJ8.html Watch the entire Analytics playlist at https://www.youtube.com/playlist?list=PLJMaoEWvHwFJDyhMLCkNSSUQWw9waFkIj ========================================­­============== Copyright © 2015 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.
Views: 551 Connor McDonald
Oracle Tutorial - is NULL and is NOT NULL
 
06:16
Oracle Tutorials - is NULL and is NOT NULL
Views: 73 Tech Acad
TSQL: Get Next (or Previous) Value
 
09:22
How to get the next (or previous) value, using a common table expression. Note that in SQL Server 2012, we have the LAG and LEAD functions and use those for previous values.
Views: 2061 SQLInSix Minutes
Lead and Lag functions in SQL Server 2012
 
07:27
In this video we will discuss about Lead and Lag functions. Lead and Lag functions Introduced in SQL Server 2012 Lead function is used to access subsequent row data along with current row data Lag function is used to access previous row data along with current row data ORDER BY clause is required PARTITION BY clause is optional Syntax LEAD(Column_Name, Offset, Default_Value) OVER (ORDER BY Col1, Col2, ...) LAG(Column_Name, Offset, Default_Value) OVER (ORDER BY Col1, Col2, ...) Offset - Number of rows to lead or lag. Default_Value - The default value to return if the number of rows to lead or lag goes beyond first row or last row in a table or partition. If default value is not specified NULL is returned. We will use the following Employees table for the examples in this video SQL Script to create the Employees table Create Table Employees ( Id int primary key, Name nvarchar(50), Gender nvarchar(10), Salary int ) Go Insert Into Employees Values (1, 'Mark', 'Male', 1000) Insert Into Employees Values (2, 'John', 'Male', 2000) Insert Into Employees Values (3, 'Pam', 'Female', 3000) Insert Into Employees Values (4, 'Sara', 'Female', 4000) Insert Into Employees Values (5, 'Todd', 'Male', 5000) Insert Into Employees Values (6, 'Mary', 'Female', 6000) Insert Into Employees Values (7, 'Ben', 'Male', 7000) Insert Into Employees Values (8, 'Jodi', 'Female', 8000) Insert Into Employees Values (9, 'Tom', 'Male', 9000) Insert Into Employees Values (10, 'Ron', 'Male', 9500) Go Lead and Lag functions example WITHOUT partitions : This example Leads 2 rows and Lags 1 row from the current row. When you are on the first row, LEAD(Salary, 2, -1) allows you to move forward 2 rows and retrieve the salary from the 3rd row. When you are on the first row, LAG(Salary, 1, -1) allows us to move backward 1 row. Since there no rows beyond row 1, Lag function in this case returns the default value -1. When you are on the last row, LEAD(Salary, 2, -1) allows you to move forward 2 rows. Since there no rows beyond the last row 1, Lead function in this case returns the default value -1. When you are on the last row, LAG(Salary, 1, -1) allows us to move backward 1 row and retrieve the salary from the previous row. SELECT Name, Gender, Salary, LEAD(Salary, 2, -1) OVER (ORDER BY Salary) AS Lead_2, LAG(Salary, 1, -1) OVER (ORDER BY Salary) AS Lag_1 FROM Employees Lead and Lag functions example WITH partitions : Notice that in this example, Lead and Lag functions return default value if the number of rows to lead or lag goes beyond first row or last row in the partition. SELECT Name, Gender, Salary, LEAD(Salary, 2, -1) OVER (PARTITION By Gender ORDER BY Salary) AS Lead_2, LAG(Salary, 1, -1) OVER (PARTITION By Gender ORDER BY Salary) AS Lag_1 FROM Employees Text version of the video http://csharp-video-tutorials.blogspot.com/2015/10/lead-and-lag-functions-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/10/lead-and-lag-functions-in-sql-server_5.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 49514 kudvenkat
Prior Day Profit using the Lag Function - SQL Training Online
 
03:13
You can get a prior day value using the Lag Function in SQL Server. In this video I show you how to create a prior day squeeze of profit using the Windowing Lag function in TSQL. READ THE ORIGINAL ARTICLE WITH SQL SCRIPTS HERE http://www.sqltrainingonline.com/prior-day-profit-lag-function-windowing-in-sql-server/ YOUTUBE NEWS UPDATES http://www.youtube.com/user/sqltrainingonline VISIT SQLTRAININGONLINE.COM FOR TONS MORE VIDEO NEWS & TIPS http://www.sqltrainingonline.com SUBSCRIBE FOR OTHER SQL TIPS AND NEWS! http://www.youtube.com/subscription_center?add_user=sqltrainingonline SUBSCRIBE TO OUR EMAIL LIST! http://www.sqltrainingonline.com LET'S CONNECT! Google+: http://gplus.to/SQLTrainingOnline Facebook: http://facebook.com/SQLTrainingOnline Twitter: http://twitter.com/sql_by_joey Linkedin: http://linkedin.com/in/joeyblue SQLTrainingOnline: http://www.sqltrainingonline.com
Views: 981 Joey Blue
ORACLESQL10 - null, nvl, coalesce
 
04:12
Kod SQL: http://pastebin.com/VpK7RmWL Przykłady zapytań do tabel z wartościami null tzn. brakiem danych, COALESCE, NVL, NULL w Oracle jako pusty tekst.
SQL. LAG. LEAD
 
10:48
В видео показано из чего состоят конструкции LAG(предыдещее значение) и LEAD(следующее значение)
29 - SQL NULL Values - Learn SQL from www.SQLServerLog.com [HD]
 
03:01
Description: This video is about SQL NULL Values in SQL Server [HD]. You can refer complete Text Tutorial on my website: www.SQLServerLog.com You can join me on social networks: Facebook: http://www.facebook.com/SQLServerLog Linkedin: http://in.linkedin.com/in/SQLServerLog Google+: http://www.google.com/+SQLServerLogs Twitter: http://www.twitter.com/SQLServerLog
Views: 263 SQLServer Log
ORACLE Analytic Functions for Data Warehousing 2/ Arabic
 
12:32
ORACLE Analytic Functions for Data Warehousing 2/ Arabic FIRST_VALUE last_VALUE row_number() --FIRST_VALUE and LAST_VALUE Analytic Functions select EMPLOYEE_ID, FIRST_NAME, HIRE_DATE from employees order by hire_date; select EMPLOYEE_ID, FIRST_NAME, HIRE_DATE , min(HIRE_DATE) over ( ) first_HIRE_DATE from employees select EMPLOYEE_ID, FIRST_NAME, HIRE_DATE , FIRST_VALUE(HIRE_DATE) over ( ) first_HIRE_DATE from employees order by hire_date; select EMPLOYEE_ID, FIRST_NAME, HIRE_DATE , max(HIRE_DATE ) over ( ) last_HIRE_DATE from employees select EMPLOYEE_ID, FIRST_NAME, HIRE_DATE , last_VALUE(HIRE_DATE ) over ( ) last_HIRE_DATE from employees order by HIRE_DATE drop table student_exam_result; CREATE TABLE student_exam_result (student_id number, exame_name varchar2(100), exame_try number, exame_results number ); insert into student_exam_result values(1,'calcluas',1,50); insert into student_exam_result values(1,'calcluas',2,66); insert into student_exam_result values(1,'calcluas',3,20); insert into student_exam_result values(2,'calcluas',1,66); insert into student_exam_result values(2,'calcluas',2,null); insert into student_exam_result values(2,'calcluas',3,7); insert into student_exam_result values(3,'calcluas',1,50); insert into student_exam_result values(3,'calcluas',2,98); insert into student_exam_result values(3,'calcluas',3,null); select * from student_exam_result select student_id, exame_name,exame_try,exame_results, last_value(exame_results) IGNORE NULLS over (PARTITION BY student_id, exame_name ) last_grade from student_exam_result order by student_id, exame_name, exame_try select student_id, exame_name,exame_try,exame_results, last_value(exame_results) IGNORE NULLS over (PARTITION BY student_id, exame_name ) last_grade, max(exame_results) over (PARTITION BY student_id, exame_name ) max_grade, avg(exame_results) over (PARTITION BY student_id, exame_name ) avg_grade from student_exam_result order by student_id, exame_name, exame_try ------------------------ select employee_id,first_name ,row_number() over(order by employee_id ) from employees select employee_id,first_name ,rownum from employees select employee_id,first_name ,department_id, row_number() over(partition by department_id order by employee_id ) from employees
Views: 633 khaled alkhudari
LAST VALUE function in SQL Server
 
05:35
last_value function in sql server 2008 sql server last_value function returns incorrect data sql server last_value function example sql server last_value function with partition example LAST_VALUE function in SQL Server In this video we will discuss LAST_VALUE function in SQL Server. LAST_VALUE function Introduced in SQL Server 2012 Retrieves the last value from the specified column ORDER BY clause is required PARTITION BY clause is optional ROWS or RANGE clause is optional, but for it to work correctly you may have to explicitly specify a value Syntax : LAST_VALUE(Column_Name) OVER (ORDER BY Col1, Col2, ...) LAST_VALUE function not working as expected : In the following example, LAST_VALUE function does not return the name of the highest paid employee. This is because we have not specified an explicit value for ROWS or RANGE clause. As a result it is using it's default value RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. SELECT Name, Gender, Salary, LAST_VALUE(Name) OVER (ORDER BY Salary) AS LastValue FROM Employees LAST_VALUE function working as expected : In the following example, LAST_VALUE function returns the name of the highest paid employee as expected. Notice we have set an explicit value for ROWS or RANGE clause to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING This tells the LAST_VALUE function that it's window starts at the first row and ends at the last row in the result set. SELECT Name, Gender, Salary, LAST_VALUE(Name) OVER (ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastValue FROM Employees LAST_VALUE function example with partitions : In the following example, LAST_VALUE function returns the name of the highest paid employee from the respective partition. SELECT Name, Gender, Salary, LAST_VALUE(Name) OVER (PARTITION BY Gender ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastValue FROM Employees Text version of the video http://csharp-video-tutorials.blogspot.com/2015/10/lastvalue-function-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/10/lastvalue-function-in-sql-server_9.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html Full SQL Server Course https://www.youtube.com/playlist?list=PL4cyC4G0M1RQ_Rm52cQ4CcOJ_T_HXeMB4 All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 32978 kudvenkat
023 LAG and LEAD function Examples
 
04:23
023 LAG and LEAD function Examples
SQL Tutorial - Scalar Functions
 
22:36
This video will teach you how to create scalar functions in SQL Server, scalar functions only return a single value and are user-defined functions. In this video you learn how to create scalar functions and how to use them within SQL. CREATE FUNCTION dbo.Multiply ( @n INT , @m INT ) RETURNS INT AS BEGIN DECLARE @Result INT SELECT @Result = @n * @m RETURN @Result END SELECT dbo.Multiply(2, 3) IF OBJECT_ID(N'fn_FullName', N'FN') IS NOT NULL DROP FUNCTION fn_FullName; GO CREATE FUNCTION fn_FullName ( @CustomerId INT ) RETURNS VARCHAR(110) AS BEGIN DECLARE @FullName VARCHAR(110) SET @FullName = (SELECT REPLACE(C_First_Name + ' ' + ISNULL(C_Middle_Name, '') + ' ' + C_Last_Name, ' ', ' ') FROM dbo.Customers WHERE C_Id = @CustomerId) RETURN @FullName END SELECT fn_FullName(C_Id) FROM dbo.Customers IF OBJECT_ID(N'fn_TotalSpend', N'FN') IS NOT NULL DROP FUNCTION fn_TotalSpend; GO CREATE FUNCTION dbo.fn_TotalSpend ( @C_Id INT ) RETURNS DECIMAL(16, 2) AS BEGIN DECLARE @TotalSpend DECIMAL(16, 2) SELECT @TotalSpend = SUM(O_Total) FROM dbo.Customers AS A INNER JOIN Orders AS B ON A.C_Id = B.O_C_Id INNER JOIN Order_Details AS C ON B.O_Id = C.OD_O_Id WHERE C_Id = @C_Id GROUP BY C_Id RETURN @TotalSpend END SELECT C_First_Name, C_Last_Name, dbo.fn_TotalSpend(C_Id) AS TotalSpend FROM dbo.Customers WHERE dbo.fn_TotalSpend(C_Id) IS NOT NULL
Views: 462 BeardedDev
ORACLE: How to get FIRST and LAST day of a month ?
 
00:56
ORACLE DATE FUNCTION - trunc(date) - last_day()
Views: 517 1Click2beDBA
Oracle Interview questions, How to calculate sum of salary without using sum function.
 
06:04
Oracle Interview questions, How to calculate sum of salary without using sum function...!!!!!
Views: 49 ORA CLE
SQL: Lag
 
04:40
In this tutorial, you'll learn how to make use of lag function in oracle sql PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7).[1] Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database. PL/SQL includes procedural language elements such as conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variables of those types, and triggers. It can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces. PL/SQL works analogously to the embedded procedural languages associated with other relational databases. For example, Sybase ASE and Microsoft SQL Server have Transact-SQL, PostgreSQL has PL/pgSQL (which emulates PL/SQL to an extent), and IBM DB2 includes SQL Procedural Language,[2] which conforms to the ISO SQL’s SQL/PSM standard. The designers of PL/SQL modeled its syntax on that of Ada. Both Ada and PL/SQL have Pascal as a common ancestor, and so PL/SQL also resembles Pascal in several aspects. However, the structure of a PL/SQL package does not resemble the basic Object Pascal program structure as implemented by a Borland Delphi or Free Pascal unit. Programmers can define public and private global data-types, constants and static variables in a PL/SQL package.[3] PL/SQL also allows for the definition of classes and instantiating these as objects in PL/SQL code. This resembles usage in object-oriented programming languages like Object Pascal, C++ and Java. PL/SQL refers to a class as an "Abstract Data Type" (ADT) or "User Defined Type" (UDT), and defines it as an Oracle SQL data-type as opposed to a PL/SQL user-defined type, allowing its use in both the Oracle SQL Engine and the Oracle PL/SQL engine. The constructor and methods of an Abstract Data Type are written in PL/SQL. The resulting Abstract Data Type can operate as an object class in PL/SQL. Such objects can also persist as column values in Oracle database tables. PL/SQL is fundamentally distinct from Transact-SQL, despite superficial similarities. Porting code from one to the other usually involves non-trivial work, not only due to the differences in the feature sets of the two languages,[4] but also due to the very significant differences in the way Oracle and SQL Server deal with concurrency and locking. There are software tools available that claim to facilitate porting including Oracle Translation Scratch Editor,[5] CEITON MSSQL/Oracle Compiler [6] and SwisSQL.[7] The StepSqlite product is a PL/SQL compiler for the popular small database SQLite. PL/SQL Program Unit A PL/SQL program unit is one of the following: PL/SQL anonymous block, procedure, function, package specification, package body, trigger, type specification, type body, library. Program units are the PL/SQL source code that is compiled, developed and ultimately executed on the database. The basic unit of a PL/SQL source program is the block, which groups together related declarations and statements. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. The declaration section is optional and may be used to define and initialize constants and variables. If a variable is not initialized then it defaults to NULL value. The optional exception-handling part is used to handle run time errors. Only the executable part is required. A block can have a label. Package Packages are groups of conceptually linked functions, procedures, variables, PL/SQL table and record TYPE statements, constants, cursors etc. The use of packages promotes re-use of code. Packages are composed of the package specification and an optional package body. The specification is the interface to the application; it declares the types, variables, constants, exceptions, cursors, and subprograms available. The body fully defines cursors and subprograms, and so implements the specification. Two advantages of packages are: Modular approach, encapsulation/hiding of business logic, security, performance improvement, re-usability. They support object-oriented programming features like function overloading and encapsulation. Using package variables one can declare session level (scoped) variables, since variables declared in the package specification have a session scope.
Views: 2106 radhikaravikumar
Analytics: 17 The LAG / LEAD clauses
 
05:59
blog: https://connor-mcdonald.com Welcome to the KISS video series. Solving problems that typically required complicated SQL in the past, that can now be easily solved with Analytic SQL syntax. In this session, we look at the lag and lead functions. The sample problem we'll solve is: Collapsing repeated rows in an order history table to show only the days where the order changed status Scripts: https://livesql.oracle.com/apex/livesql/file/content_CZUCT0MCOQZMJM7TI553HC8S9.html
Views: 389 Connor McDonald
Oracle Tutorial || Oracle|Adv Sql | Analytical Functions Part-1 by basha
 
33:24
DURGASOFT is INDIA's No.1 Software Training Center offers online training on various technologies like JAVA, .NET , ANDROID,HADOOP,TESTING TOOLS ,ADF,INFORMATICA,TABLEAU,IPHONE,OBIEE,ANJULAR JS, SAP... courses from Hyderabad & Bangalore -India with Real Time Experts. Mail us your requirements to [email protected] so that our Supporting Team will arrange Demo Sessions. Ph:Call +91-8885252627,+91-7207212428,+91-7207212427,+91-8096969696. http://durgasoft.com http://durgasoftonlinetraining.com https://www.facebook.com/durgasoftware http://durgajobs.com https://www.facebook.com/durgajobsinfo......
SQL: LEAD Function
 
06:30
In this tutorial, you'll learn how to make use of lead function in oracle sql PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7).[1] Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database. PL/SQL includes procedural language elements such as conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variables of those types, and triggers. It can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces. PL/SQL works analogously to the embedded procedural languages associated with other relational databases. For example, Sybase ASE and Microsoft SQL Server have Transact-SQL, PostgreSQL has PL/pgSQL (which emulates PL/SQL to an extent), and IBM DB2 includes SQL Procedural Language,[2] which conforms to the ISO SQL’s SQL/PSM standard. The designers of PL/SQL modeled its syntax on that of Ada. Both Ada and PL/SQL have Pascal as a common ancestor, and so PL/SQL also resembles Pascal in several aspects. However, the structure of a PL/SQL package does not resemble the basic Object Pascal program structure as implemented by a Borland Delphi or Free Pascal unit. Programmers can define public and private global data-types, constants and static variables in a PL/SQL package.[3] PL/SQL also allows for the definition of classes and instantiating these as objects in PL/SQL code. This resembles usage in object-oriented programming languages like Object Pascal, C++ and Java. PL/SQL refers to a class as an "Abstract Data Type" (ADT) or "User Defined Type" (UDT), and defines it as an Oracle SQL data-type as opposed to a PL/SQL user-defined type, allowing its use in both the Oracle SQL Engine and the Oracle PL/SQL engine. The constructor and methods of an Abstract Data Type are written in PL/SQL. The resulting Abstract Data Type can operate as an object class in PL/SQL. Such objects can also persist as column values in Oracle database tables. PL/SQL is fundamentally distinct from Transact-SQL, despite superficial similarities. Porting code from one to the other usually involves non-trivial work, not only due to the differences in the feature sets of the two languages,[4] but also due to the very significant differences in the way Oracle and SQL Server deal with concurrency and locking. There are software tools available that claim to facilitate porting including Oracle Translation Scratch Editor,[5] CEITON MSSQL/Oracle Compiler [6] and SwisSQL.[7] The StepSqlite product is a PL/SQL compiler for the popular small database SQLite. PL/SQL Program Unit A PL/SQL program unit is one of the following: PL/SQL anonymous block, procedure, function, package specification, package body, trigger, type specification, type body, library. Program units are the PL/SQL source code that is compiled, developed and ultimately executed on the database. The basic unit of a PL/SQL source program is the block, which groups together related declarations and statements. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. The declaration section is optional and may be used to define and initialize constants and variables. If a variable is not initialized then it defaults to NULL value. The optional exception-handling part is used to handle run time errors. Only the executable part is required. A block can have a label. Package Packages are groups of conceptually linked functions, procedures, variables, PL/SQL table and record TYPE statements, constants, cursors etc. The use of packages promotes re-use of code. Packages are composed of the package specification and an optional package body. The specification is the interface to the application; it declares the types, variables, constants, exceptions, cursors, and subprograms available. The body fully defines cursors and subprograms, and so implements the specification. Two advantages of packages are: Modular approach, encapsulation/hiding of business logic, security, performance improvement, re-usability. They support object-oriented programming features like function overloading and encapsulation. Using package variables one can declare session level (scoped) variables, since variables declared in the package specification have a session scope.
Views: 1990 radhikaravikumar
SQL 12c Tutorial 13  : SQL General Functions NVL , NVL2, NULLIF and COALESCE
 
09:48
SQL 12c General Functions NVL , NVL2, NULLIF and COALESCE SQL 12c Tutorial for beginners Oracle SQL 12c Tutorial NVL NVL2 NULLIF COALESCE
Views: 734 TechLake
Group Function 02 Oracle
 
01:04:18
Having Clause, Group Function, Group By Clause
SQL tutorial 51: DECODE function in Oracle Database By Manish Sharma (RebellionRider)
 
05:04
SQL Tutorial 51 Decode function in Oracle Database. ● What Is DECODE() ● DECODE ( ) vs CASE ● Syntax of DECODE() ● Query 1. String Matching using DECODE() ● Query 2. DECODE() comparing values form the COLUMN of the table Celebrating 1000 subscribers. Thanks a lot guys for all your love and support. ------------------------------------------------------------------------ ►►►LINKS◄◄◄ Blog: ------------------------------------------------------------------------- Copy Cloud referral link || Use this link to join copy cloud and get 20GB of free storage https://copy.com?r=kb4rc1 -------------------------------------------------------------------------- ►Make sure you SUBSCRIBE and be the first one to see my videos! -------------------------------------------------------------------------- Amazon Wishlist: http://bit.ly/wishlist-amazon ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ►►►Find me on Social Media◄◄◄ Follow What I am up to as it happens on https://twitter.com/rebellionrider https://www.facebook.com/imthebhardwaj http://instagram.com/rebellionrider https://plus.google.com/+Rebellionrider http://in.linkedin.com/in/mannbhardwaj/ http://rebellionrider.tumblr.com/ http://www.pinterest.com/rebellionrider/ You can also Email me at [email protected] Please please LIKE and SHARE my videos it makes me happy. Thanks for liking, commenting, sharing and watching more of our videos This is Manish from RebellionRider.com ♥ I LOVE ALL MY VIEWERS AND SUBSCRIBERS
Views: 33888 Manish Sharma
Rollup in SQL Server
 
09:01
sql server group by rollup sql server 2008 group by rollup group by with rollup example sql rollup example sql server rollup example sql server 2008 rollup examples rollup clause in sql server rollup function in sql server ROLLUP in SQL Server is used to do aggregate operation on multiple levels in hierarchy. Let us understand Rollup in SQL Server with examples. We will use the following Employees table for the examples in this video. Retrieve Salary by country along with grand total There are several ways to achieve this. The easiest way is by using Rollup with GroupBy. SELECT Country, SUM(Salary) AS TotalSalary FROM Employees GROUP BY ROLLUP(Country) The above query can also be rewritten as shown below SELECT Country, SUM(Salary) AS TotalSalary FROM Employees GROUP BY Country WITH ROLLUP We can also use UNION ALL operator along with GROUP BY SELECT Country, SUM(Salary) AS TotalSalary FROM Employees GROUP BY Country UNION ALL SELECT NULL, SUM(Salary) AS TotalSalary FROM Employees We can also use Grouping Sets to achieve the same result SELECT Country, SUM(Salary) AS TotalSalary FROM Employees GROUP BY GROUPING SETS ( (Country), () ) Let's look at another example. Group Salary by Country and Gender. Also compute the Subtotal for Country level and Grand Total as shown below. Using ROLLUP with GROUP BY SELECT Country, Gender, SUM(Salary) AS TotalSalary FROM Employees GROUP BY ROLLUP(Country, Gender) OR SELECT Country, Gender, SUM(Salary) AS TotalSalary FROM Employees GROUP BY Country, Gender WITH ROLLUP Using UNION ALL with GROUP BY SELECT Country, Gender, SUM(Salary) AS TotalSalary FROM Employees GROUP BY Country, Gender UNION ALL SELECT Country, NULL, SUM(Salary) AS TotalSalary FROM Employees GROUP BY Country UNION ALL SELECT NULL, NULL, SUM(Salary) AS TotalSalary FROM Employees Using GROUPING SETS SELECT Country, Gender, SUM(Salary) AS TotalSalary FROM Employees GROUP BY GROUPING SETS ( (Country, Gender), (Country), () ) Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/rollup-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/rollup-in-sql-server_20.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 54741 kudvenkat
Grouping function in SQL Server
 
12:49
sql server rollup grouping sql server rollup replace null sql server rollup grouping replace null In this video we will discuss the use of Grouping function in SQL Server. This is continuation to Part 104. Please watch Part 104 from SQL Server tutorial before proceeding. What is Grouping function Grouping(Column) indicates whether the column in a GROUP BY list is aggregated or not. Grouping returns 1 for aggregated or 0 for not aggregated in the result set. The following query returns 1 for aggregated or 0 for not aggregated in the result set SELECT Continent, Country, City, SUM(SaleAmount) AS TotalSales, GROUPING(Continent) AS GP_Continent, GROUPING(Country) AS GP_Country, GROUPING(City) AS GP_City FROM Sales GROUP BY ROLLUP(Continent, Country, City) What is the use of Grouping function in real world When a column is aggregated in the result set, the column will have a NULL value. If you want to replace NULL with All then this GROUPING function is very handy. SELECT CASE WHEN GROUPING(Continent) = 1 THEN 'All' ELSE ISNULL(Continent, 'Unknown') END AS Continent, CASE WHEN GROUPING(Country) = 1 THEN 'All' ELSE ISNULL(Country, 'Unknown') END AS Country, CASE WHEN GROUPING(City) = 1 THEN 'All' ELSE ISNULL(City, 'Unknown') END AS City, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY ROLLUP(Continent, Country, City) Can't I use ISNULL function instead as shown below SELECT ISNULL(Continent, 'All') AS Continent, ISNULL(Country, 'All') AS Country, ISNULL(City, 'All') AS City, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY ROLLUP(Continent, Country, City) Well, you can, but only if your data does not contain NULL values. Let me explain what I mean. At the moment the raw data in our Sales has no NULL values. Let's introduce a NULL value in the City column of the row where Id = 1 Update Sales Set City = NULL where Id = 1 Now execute the following query with ISNULL function SELECT ISNULL(Continent, 'All') AS Continent, ISNULL(Country, 'All') AS Country, ISNULL(City, 'All') AS City, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY ROLLUP(Continent, Country, City) Notice that the actuall NULL value in the raw data is also replaced with the word 'All', which is incorrect. Hence the need for Grouping function. Please note : Grouping function can be used with Rollup, Cube and Grouping Sets Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/grouping-function-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/grouping-function-in-sql-server_25.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 37662 kudvenkat
SQL Tutorial for beginners What is LEAD Function in Oracle
 
04:02
What is LEAD Function in Oracle SQL Tutorial SQL Tutorial for beginners PLSQL Tutorial PLSQL Tutorial for beginners PL/SQL Tutorial PL SQL Tutorial PL SQL Tutorial for beginners PL/SQL Tutorial for beginners Oracle SQL Tutorial
Views: 180 TechLake
Analytics: 21 Grouping ranges of data
 
04:01
blog: https://connor-mcdonald.com Welcome to the KISS video series. Solving problems that typically required complicated SQL in the past, that can now be easily solved with Analytic SQL syntax. In this session, we revisit the classical problem of identifying ranges of data within a set. Script: https://livesql.oracle.com/apex/livesql/file/content_C35KKE5TX8H1O3M2Z5TLAGAON.html
Views: 313 Connor McDonald
FIRST_VALUE and LAST_VALUE Analytic Functions in Oracle Database
 
14:51
FIRST_VALUE and LAST_VALUE in Oracle Database Oracle FIRST Function Oracle last Function
Views: 59 Adam Tech
SQL How-to NVL, NVL2 KEEP DENSE RANK
 
19:59
A video on using the switching functions NVL and NVL2 to handle null values in SQL and how to create a binary flag. Also the use of Keep dense rank to return a value from the first or last row in a rank argument. Part of the SQL How-To Series A short video on how you can import Excel data into an Oracle database using AllAround Automations PLSQL Developer Jump to NVL 5:16 -- Click on the time to jump NVL2 9:17 -- Click on the time to jump Keep dense rank 13:02 *You will need to watch the rest of the video for this* Thanks to AllAround: www.allroundautomations.com Check out my site for Associated Blog: http://scottstansfield.com/ Follow me on Twitter: twitter.com/biztechscott
Views: 116 Biz Tech Talk
How to Apply constraint After table creation in  SQL
 
03:58
How to apply constraint after table creation in Oracle sql in Hindi Language.
Views: 437 OCP Technology
LISTAGG Function in Oracle Database
 
07:19
listagg in oracle 10g opposite of listagg in oracle oracle listagg distinct oracle listagg multiple columns oracle listagg limit
Views: 64 Adam Tech
How to Code Oracle FIRST LAST Aggregate Functions
 
06:35
Learn Advanced Oracle SQL Aggregate functions FIRST and LAST. View all lessons in this tutorial at http://www.skillbuilders.com/advanced-aggregations-with-oracle-sql
Views: 809 SkillBuilders
PL/SQL: Mutating Triggers Part-1
 
06:24
In this tutorial, you'll learn... PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7).[1] Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database. PL/SQL includes procedural language elements such as conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variables of those types, and triggers. It can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces. PL/SQL works analogously to the embedded procedural languages associated with other relational databases. For example, Sybase ASE and Microsoft SQL Server have Transact-SQL, PostgreSQL has PL/pgSQL (which emulates PL/SQL to an extent), and IBM DB2 includes SQL Procedural Language,[2] which conforms to the ISO SQL’s SQL/PSM standard. The designers of PL/SQL modeled its syntax on that of Ada. Both Ada and PL/SQL have Pascal as a common ancestor, and so PL/SQL also resembles Pascal in several aspects. However, the structure of a PL/SQL package does not resemble the basic Object Pascal program structure as implemented by a Borland Delphi or Free Pascal unit. Programmers can define public and private global data-types, constants and static variables in a PL/SQL package.[3] PL/SQL also allows for the definition of classes and instantiating these as objects in PL/SQL code. This resembles usage in object-oriented programming languages like Object Pascal, C++ and Java. PL/SQL refers to a class as an "Abstract Data Type" (ADT) or "User Defined Type" (UDT), and defines it as an Oracle SQL data-type as opposed to a PL/SQL user-defined type, allowing its use in both the Oracle SQL Engine and the Oracle PL/SQL engine. The constructor and methods of an Abstract Data Type are written in PL/SQL. The resulting Abstract Data Type can operate as an object class in PL/SQL. Such objects can also persist as column values in Oracle database tables. PL/SQL is fundamentally distinct from Transact-SQL, despite superficial similarities. Porting code from one to the other usually involves non-trivial work, not only due to the differences in the feature sets of the two languages,[4] but also due to the very significant differences in the way Oracle and SQL Server deal with concurrency and locking. There are software tools available that claim to facilitate porting including Oracle Translation Scratch Editor,[5] CEITON MSSQL/Oracle Compiler [6] and SwisSQL.[7] The StepSqlite product is a PL/SQL compiler for the popular small database SQLite. PL/SQL Program Unit A PL/SQL program unit is one of the following: PL/SQL anonymous block, procedure, function, package specification, package body, trigger, type specification, type body, library. Program units are the PL/SQL source code that is compiled, developed and ultimately executed on the database. The basic unit of a PL/SQL source program is the block, which groups together related declarations and statements. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. The declaration section is optional and may be used to define and initialize constants and variables. If a variable is not initialized then it defaults to NULL value. The optional exception-handling part is used to handle run time errors. Only the executable part is required. A block can have a label. Package Packages are groups of conceptually linked functions, procedures, variables, PL/SQL table and record TYPE statements, constants, cursors etc. The use of packages promotes re-use of code. Packages are composed of the package specification and an optional package body. The specification is the interface to the application; it declares the types, variables, constants, exceptions, cursors, and subprograms available. The body fully defines cursors and subprograms, and so implements the specification. Two advantages of packages are: Modular approach, encapsulation/hiding of business logic, security, performance improvement, re-usability. They support object-oriented programming features like function overloading and encapsulation. Using package variables one can declare session level (scoped) variables, since variables declared in the package specification have a session scope.
Views: 23865 radhikaravikumar
KISS series on Analytics: 02 Options for ranking
 
04:44
Welcome to the KISS video series. Solving problems that typically required complicated SQL in the past, that can now be easily solved with Analytic SQL syntax. In this session, we look at additional ranking functions The sample problem we'll solve is: How do handle ties when ranking values with analytics using DENSE_RANK and ROW_NUMBER Scripts here: https://livesql.oracle.com/apex/livesql/s/clebs8f5n4kr2b3vjvnvvgxkn Watch the entire Analytics playlist at https://www.youtube.com/playlist?list=PLJMaoEWvHwFJDyhMLCkNSSUQWw9waFkIj ========================================­­============== Copyright © 2015 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.
Views: 2421 Connor McDonald
PL/SQL: Mutating Triggers Part-2
 
08:41
In this tutorial, you'll learn... PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7).[1] Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database. PL/SQL includes procedural language elements such as conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variables of those types, and triggers. It can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces. PL/SQL works analogously to the embedded procedural languages associated with other relational databases. For example, Sybase ASE and Microsoft SQL Server have Transact-SQL, PostgreSQL has PL/pgSQL (which emulates PL/SQL to an extent), and IBM DB2 includes SQL Procedural Language,[2] which conforms to the ISO SQL’s SQL/PSM standard. The designers of PL/SQL modeled its syntax on that of Ada. Both Ada and PL/SQL have Pascal as a common ancestor, and so PL/SQL also resembles Pascal in several aspects. However, the structure of a PL/SQL package does not resemble the basic Object Pascal program structure as implemented by a Borland Delphi or Free Pascal unit. Programmers can define public and private global data-types, constants and static variables in a PL/SQL package.[3] PL/SQL also allows for the definition of classes and instantiating these as objects in PL/SQL code. This resembles usage in object-oriented programming languages like Object Pascal, C++ and Java. PL/SQL refers to a class as an "Abstract Data Type" (ADT) or "User Defined Type" (UDT), and defines it as an Oracle SQL data-type as opposed to a PL/SQL user-defined type, allowing its use in both the Oracle SQL Engine and the Oracle PL/SQL engine. The constructor and methods of an Abstract Data Type are written in PL/SQL. The resulting Abstract Data Type can operate as an object class in PL/SQL. Such objects can also persist as column values in Oracle database tables. PL/SQL is fundamentally distinct from Transact-SQL, despite superficial similarities. Porting code from one to the other usually involves non-trivial work, not only due to the differences in the feature sets of the two languages,[4] but also due to the very significant differences in the way Oracle and SQL Server deal with concurrency and locking. There are software tools available that claim to facilitate porting including Oracle Translation Scratch Editor,[5] CEITON MSSQL/Oracle Compiler [6] and SwisSQL.[7] The StepSqlite product is a PL/SQL compiler for the popular small database SQLite. PL/SQL Program Unit A PL/SQL program unit is one of the following: PL/SQL anonymous block, procedure, function, package specification, package body, trigger, type specification, type body, library. Program units are the PL/SQL source code that is compiled, developed and ultimately executed on the database. The basic unit of a PL/SQL source program is the block, which groups together related declarations and statements. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. The declaration section is optional and may be used to define and initialize constants and variables. If a variable is not initialized then it defaults to NULL value. The optional exception-handling part is used to handle run time errors. Only the executable part is required. A block can have a label. Package Packages are groups of conceptually linked functions, procedures, variables, PL/SQL table and record TYPE statements, constants, cursors etc. The use of packages promotes re-use of code. Packages are composed of the package specification and an optional package body. The specification is the interface to the application; it declares the types, variables, constants, exceptions, cursors, and subprograms available. The body fully defines cursors and subprograms, and so implements the specification. Two advantages of packages are: Modular approach, encapsulation/hiding of business logic, security, performance improvement, re-usability. They support object-oriented programming features like function overloading and encapsulation. Using package variables one can declare session level (scoped) variables, since variables declared in the package specification have a session scope.
Views: 14285 radhikaravikumar
sql aggregate functions
 
06:36
sql aggregate functions aggregate functions sql sql analytic functions oracle sql analytic functions SQL have following aggregate functions that can be used to write queries. 1)SUM() -: return sum of columns values. 2)AVG() -: return Average of columns values. 3)COUNT() -: return count of rows according to condition in where clause. 4)COUNT(*) -: return count of rows according to condition in where clause. 5)MAX() -: return Maximum value from table. 6)MIN() -: return minimum value from table. Note : A)Aggregate functions used in SELECT and HAVING clauses. B)Aggregate functions cannot be used in a WHERE clause. SELECT SUM(SALARY) FROM EMPLOYEE SELECT AVG(SALARY) FROM EMPLOYEE SELECT COUNT(SALARY) FROM EMPLOYEE SELECT COUNT(*) FROM EMPLOYEE SELECT MAX(SALARY) FROM EMPLOYEE SELECT MIN(SALARY) FROM EMPLOYEE https://techquerypond.com https://techquerypond.wordpress.com https://twitter.com/techquerypond
Views: 474 Tech Query Pond
KISS series on Analytics: 07 Partition clause
 
03:20
Welcome to the KISS video series. Solving problems that typically required complicated SQL in the past, that can now be easily solved with Analytic SQL syntax. In this session, we take our first look at the partition clause The sample problem we'll solve is: Applying ranking functions to data when it is logically grouped into sections, in this case, populations of countries by continent Scripts: https://livesql.oracle.com/apex/livesql/file/content_ERYV6B909XK196IAKLBUQIJSN.html Watch the entire Analytics playlist at https://www.youtube.com/playlist?list=PLJMaoEWvHwFJDyhMLCkNSSUQWw9waFkIj ========================================­­­============== Copyright © 2015 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.
Views: 1165 Connor McDonald
Welcome to the KISS (Keep It Simply SQL) Analytics series
 
02:01
Welcome to the KISS video series. Solving problems that typically required complicated SQL in the past, that can now be easily solved with Analytic SQL syntax ========================================­­============== Copyright © 2015 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.
Views: 5063 Connor McDonald
Tuorial : Oracle Analytical Functions rank , denserank , top n query
 
06:41
This video gives introduction on how oracle analytical functions like rank , dense rank work. How they should be used practically. How to perform Top N Query with Analytical Functions. Sofware used can be downloaded from : www.querylight.in
Views: 1317 Query Light
SQL: TRIM function
 
06:35
In this tutorial, you'll learn how to use trim function PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7).[1] Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database. PL/SQL includes procedural language elements such as conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variables of those types, and triggers. It can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces. PL/SQL works analogously to the embedded procedural languages associated with other relational databases. For example, Sybase ASE and Microsoft SQL Server have Transact-SQL, PostgreSQL has PL/pgSQL (which emulates PL/SQL to an extent), and IBM DB2 includes SQL Procedural Language,[2] which conforms to the ISO SQL’s SQL/PSM standard. The designers of PL/SQL modeled its syntax on that of Ada. Both Ada and PL/SQL have Pascal as a common ancestor, and so PL/SQL also resembles Pascal in several aspects. However, the structure of a PL/SQL package does not resemble the basic Object Pascal program structure as implemented by a Borland Delphi or Free Pascal unit. Programmers can define public and private global data-types, constants and static variables in a PL/SQL package.[3] PL/SQL also allows for the definition of classes and instantiating these as objects in PL/SQL code. This resembles usage in object-oriented programming languages like Object Pascal, C++ and Java. PL/SQL refers to a class as an "Abstract Data Type" (ADT) or "User Defined Type" (UDT), and defines it as an Oracle SQL data-type as opposed to a PL/SQL user-defined type, allowing its use in both the Oracle SQL Engine and the Oracle PL/SQL engine. The constructor and methods of an Abstract Data Type are written in PL/SQL. The resulting Abstract Data Type can operate as an object class in PL/SQL. Such objects can also persist as column values in Oracle database tables. PL/SQL is fundamentally distinct from Transact-SQL, despite superficial similarities. Porting code from one to the other usually involves non-trivial work, not only due to the differences in the feature sets of the two languages,[4] but also due to the very significant differences in the way Oracle and SQL Server deal with concurrency and locking. There are software tools available that claim to facilitate porting including Oracle Translation Scratch Editor,[5] CEITON MSSQL/Oracle Compiler [6] and SwisSQL.[7] The StepSqlite product is a PL/SQL compiler for the popular small database SQLite. PL/SQL Program Unit A PL/SQL program unit is one of the following: PL/SQL anonymous block, procedure, function, package specification, package body, trigger, type specification, type body, library. Program units are the PL/SQL source code that is compiled, developed and ultimately executed on the database. The basic unit of a PL/SQL source program is the block, which groups together related declarations and statements. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. The declaration section is optional and may be used to define and initialize constants and variables. If a variable is not initialized then it defaults to NULL value. The optional exception-handling part is used to handle run time errors. Only the executable part is required. A block can have a label. Package Packages are groups of conceptually linked functions, procedures, variables, PL/SQL table and record TYPE statements, constants, cursors etc. The use of packages promotes re-use of code. Packages are composed of the package specification and an optional package body. The specification is the interface to the application; it declares the types, variables, constants, exceptions, cursors, and subprograms available. The body fully defines cursors and subprograms, and so implements the specification. Two advantages of packages are: Modular approach, encapsulation/hiding of business logic, security, performance improvement, re-usability. They support object-oriented programming features like function overloading and encapsulation. Using package variables one can declare session level (scoped) variables, since variables declared in the package specification have a session scope.
Views: 2787 radhikaravikumar
SQL: Union Vs UnionAll
 
04:37
In this tutorial, you'll learn what is union & unionall PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7).[1] Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database. PL/SQL includes procedural language elements such as conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variables of those types, and triggers. It can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces. PL/SQL works analogously to the embedded procedural languages associated with other relational databases. For example, Sybase ASE and Microsoft SQL Server have Transact-SQL, PostgreSQL has PL/pgSQL (which emulates PL/SQL to an extent), and IBM DB2 includes SQL Procedural Language,[2] which conforms to the ISO SQL’s SQL/PSM standard. The designers of PL/SQL modeled its syntax on that of Ada. Both Ada and PL/SQL have Pascal as a common ancestor, and so PL/SQL also resembles Pascal in several aspects. However, the structure of a PL/SQL package does not resemble the basic Object Pascal program structure as implemented by a Borland Delphi or Free Pascal unit. Programmers can define public and private global data-types, constants and static variables in a PL/SQL package.[3] PL/SQL also allows for the definition of classes and instantiating these as objects in PL/SQL code. This resembles usage in object-oriented programming languages like Object Pascal, C++ and Java. PL/SQL refers to a class as an "Abstract Data Type" (ADT) or "User Defined Type" (UDT), and defines it as an Oracle SQL data-type as opposed to a PL/SQL user-defined type, allowing its use in both the Oracle SQL Engine and the Oracle PL/SQL engine. The constructor and methods of an Abstract Data Type are written in PL/SQL. The resulting Abstract Data Type can operate as an object class in PL/SQL. Such objects can also persist as column values in Oracle database tables. PL/SQL is fundamentally distinct from Transact-SQL, despite superficial similarities. Porting code from one to the other usually involves non-trivial work, not only due to the differences in the feature sets of the two languages,[4] but also due to the very significant differences in the way Oracle and SQL Server deal with concurrency and locking. There are software tools available that claim to facilitate porting including Oracle Translation Scratch Editor,[5] CEITON MSSQL/Oracle Compiler [6] and SwisSQL.[7] The StepSqlite product is a PL/SQL compiler for the popular small database SQLite. PL/SQL Program Unit A PL/SQL program unit is one of the following: PL/SQL anonymous block, procedure, function, package specification, package body, trigger, type specification, type body, library. Program units are the PL/SQL source code that is compiled, developed and ultimately executed on the database. The basic unit of a PL/SQL source program is the block, which groups together related declarations and statements. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. The declaration section is optional and may be used to define and initialize constants and variables. If a variable is not initialized then it defaults to NULL value. The optional exception-handling part is used to handle run time errors. Only the executable part is required. A block can have a label. Package Packages are groups of conceptually linked functions, procedures, variables, PL/SQL table and record TYPE statements, constants, cursors etc. The use of packages promotes re-use of code. Packages are composed of the package specification and an optional package body. The specification is the interface to the application; it declares the types, variables, constants, exceptions, cursors, and subprograms available. The body fully defines cursors and subprograms, and so implements the specification. Two advantages of packages are: Modular approach, encapsulation/hiding of business logic, security, performance improvement, re-usability. They support object-oriented programming features like function overloading and encapsulation. Using package variables one can declare session level (scoped) variables, since variables declared in the package specification have a session scope.
Views: 1972 radhikaravikumar
PL/SQL: Brief on Associative Arrays
 
10:52
In this tutorial, you'll learn the points to be noted on AA PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7).[1] Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database. PL/SQL includes procedural language elements such as conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variables of those types, and triggers. It can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces. PL/SQL works analogously to the embedded procedural languages associated with other relational databases. For example, Sybase ASE and Microsoft SQL Server have Transact-SQL, PostgreSQL has PL/pgSQL (which emulates PL/SQL to an extent), and IBM DB2 includes SQL Procedural Language,[2] which conforms to the ISO SQL’s SQL/PSM standard. The designers of PL/SQL modeled its syntax on that of Ada. Both Ada and PL/SQL have Pascal as a common ancestor, and so PL/SQL also resembles Pascal in several aspects. However, the structure of a PL/SQL package does not resemble the basic Object Pascal program structure as implemented by a Borland Delphi or Free Pascal unit. Programmers can define public and private global data-types, constants and static variables in a PL/SQL package.[3] PL/SQL also allows for the definition of classes and instantiating these as objects in PL/SQL code. This resembles usage in object-oriented programming languages like Object Pascal, C++ and Java. PL/SQL refers to a class as an "Abstract Data Type" (ADT) or "User Defined Type" (UDT), and defines it as an Oracle SQL data-type as opposed to a PL/SQL user-defined type, allowing its use in both the Oracle SQL Engine and the Oracle PL/SQL engine. The constructor and methods of an Abstract Data Type are written in PL/SQL. The resulting Abstract Data Type can operate as an object class in PL/SQL. Such objects can also persist as column values in Oracle database tables. PL/SQL is fundamentally distinct from Transact-SQL, despite superficial similarities. Porting code from one to the other usually involves non-trivial work, not only due to the differences in the feature sets of the two languages,[4] but also due to the very significant differences in the way Oracle and SQL Server deal with concurrency and locking. There are software tools available that claim to facilitate porting including Oracle Translation Scratch Editor,[5] CEITON MSSQL/Oracle Compiler [6] and SwisSQL.[7] The StepSqlite product is a PL/SQL compiler for the popular small database SQLite. PL/SQL Program Unit A PL/SQL program unit is one of the following: PL/SQL anonymous block, procedure, function, package specification, package body, trigger, type specification, type body, library. Program units are the PL/SQL source code that is compiled, developed and ultimately executed on the database. The basic unit of a PL/SQL source program is the block, which groups together related declarations and statements. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. The declaration section is optional and may be used to define and initialize constants and variables. If a variable is not initialized then it defaults to NULL value. The optional exception-handling part is used to handle run time errors. Only the executable part is required. A block can have a label. Package Packages are groups of conceptually linked functions, procedures, variables, PL/SQL table and record TYPE statements, constants, cursors etc. The use of packages promotes re-use of code. Packages are composed of the package specification and an optional package body. The specification is the interface to the application; it declares the types, variables, constants, exceptions, cursors, and subprograms available. The body fully defines cursors and subprograms, and so implements the specification. Two advantages of packages are: Modular approach, encapsulation/hiding of business logic, security, performance improvement, re-usability. They support object-oriented programming features like function overloading and encapsulation. Using package variables one can declare session level (scoped) variables, since variables declared in the package specification have a session scope.
Views: 6467 radhikaravikumar
SQL Analytics - Part 1.5 Windows
 
07:22
In an earlier podcast we introduced the concepts of windows in this podcast we are going to look a little deeper at how analytical windows work. Copyright © 2013 Oracle and/or its affiliates. Oracle® is a registered trademark of Oracle and/or its affiliates. All rights reserved. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the "Materials"). The Materials are provided "as is" without any warranty of any kind, either express or implied, including without limitation warranties of merchantability, fitness for a particular purpose, and non-infringement.
Analytic Functions: Databases for Developers #13
 
05:42
Analytic or windowing functions are one of the most powerful features of SQL. They allow you do to things like: - Add running totals - Generate row numbers - Calculate moving averages This video explains how to use them to analyze your data. Want to learn more? Take the course! https://devgym.oracle.com/pls/apex/dg/class/databases-for-developers-next-level.html Further reading: Analytic functions in the docs: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Analytic-Functions.html Analytic SQL video series: https://www.youtube.com/watch?v=0cjxYMxa1e4&list=PLJMaoEWvHwFIUwMrF4HLnRksF0H8DHGtt Need help with SQL? Ask us over on AskTOM: https://asktom.oracle.com Twitter: https://twitter.com/ChrisRSaxon Daily SQL Twitter tips: https://twitter.com/SQLDaily All Things SQL blog: https://blogs.oracle.com/sql/ ============================ The Magic of SQL with Chris Saxon Copyright © 2015 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.
Views: 960 The Magic of SQL
Difference between cube and rollup in SQL Server
 
05:38
difference between cube and rollup in sql server 2008 sql server rollup vs cube difference between cube and rollup in sql server 2005 rollup and cube in sql server 2005 In this video we will discuss the difference between cube and rollup in SQL Server. CUBE generates a result set that shows aggregates for all combinations of values in the selected columns, where as ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns. Let us understand this difference with an example. ROLLUP(Continent, Country, City) produces Sum of Salary for the following hierarchy Continent, Country, City Continent, Country, Continent () CUBE(Continent, Country, City) produces Sum of Salary for all the following column combinations Continent, Country, City Continent, Country, Continent, City Continent Country, City Country, City () SELECT Continent, Country, City, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY ROLLUP(Continent, Country, City) SELECT Continent, Country, City, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY CUBE(Continent, Country, City) You won't see any difference when you use ROLLUP and CUBE on a single column. Both the following queries produces the same output. SELECT Continent, Sum(SaleAmount) AS TotalSales FROM Sales GROUP BY ROLLUP(Continent) -- OR SELECT Continent, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY CUBE(Continent) Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/difference-between-cube-and-rollup-in.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/difference-between-cube-and-rollup-in_24.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 43212 kudvenkat

Alverine citrate 60 mg simethicone 300mg neurontin
Effexor xr 75mg effectiveness
Ditropan 5mg tab what is it used for
Conectar 2 computadoras con cable de red windows 7
Cover letter examples for administrative assistant jobs