Home
Search results “Oracle nth value”
Part 1   How to find nth highest salary in sql
 
11:45
Link for all dot net and sql server video tutorial playlists http://www.youtube.com/user/kudvenkat/playlists Link for slides, code samples and text version of the video http://csharp-video-tutorials.blogspot.com/2014/05/part-1-how-to-find-nth-highest-salary_17.html This is a very common SQL Server Interview Question. There are several ways of finding the nth highest salary. By the end of this video, we will be able to answer all the following questions as well. How to find nth highest salary in SQL Server using a Sub-Query How to find nth highest salary in SQL Server using a CTE How to find the 2nd, 3rd or 15th highest salary Let's use the following Employees table for this demo Use the following script to create Employees table Create table Employees ( ID int primary key identity, FirstName nvarchar(50), LastName nvarchar(50), Gender nvarchar(50), Salary int ) GO Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000) Insert into Employees values ('Mark', 'Hastings', 'Male', 60000) Insert into Employees values ('Steve', 'Pound', 'Male', 45000) Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000) Insert into Employees values ('Philip', 'Hastings', 'Male', 45000) Insert into Employees values ('Mary', 'Lambeth', 'Female', 30000) Insert into Employees values ('Valarie', 'Vikings', 'Female', 35000) Insert into Employees values ('John', 'Stanmore', 'Male', 80000) GO To find the highest salary it is straight forward. We can simply use the Max() function as shown below. Select Max(Salary) from Employees To get the second highest salary use a sub query along with Max() function as shown below. Select Max(Salary) from Employees where Salary [ (Select Max(Salary) from Employees) To find nth highest salary using Sub-Query SELECT TOP 1 SALARY FROM ( SELECT DISTINCT TOP N SALARY FROM EMPLOYEES ORDER BY SALARY DESC ) RESULT ORDER BY SALARY To find nth highest salary using CTE WITH RESULT AS ( SELECT SALARY, DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK FROM EMPLOYEES ) SELECT TOP 1 SALARY FROM RESULT WHERE DENSERANK = N To find 2nd highest salary we can use any of the above queries. Simple replace N with 2. Similarly, to find 3rd highest salary, simple replace N with 3. Please Note: On many of the websites, you may have seen that, the following query can be used to get the nth highest salary. The below query will only work if there are no duplicates. WITH RESULT AS ( SELECT SALARY, ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS ROWNUMBER FROM EMPLOYEES ) SELECT SALARY FROM RESULT WHERE ROWNUMBER = 3
Views: 859950 kudvenkat
How to find nth Highest Salary in SQL ( 6 Methods )
 
13:57
This video describes how to find out nth highest salary, different / various methods / ways/types to find nth highest/lowest salary/income value using SQL server.Simple ways to get/extract particular amount.It give brief explanation with live/real time/practical examples.This is one of the most interview question in SQL. How to retrieve nth position of record of from table? , SQL Server interview question and answers.Query to select top max salary from given table.
Views: 27018 Tech Jago
Query to Select top three Max Salary from Table
 
03:37
In this query i'm going to demonstrate you how to find top three salaries in ORACLE 11g step by step.
Views: 3946 OCP Technology
How to return multiple values from a function in oracle pl/sql ? (without using out parameter)
 
14:19
This video demonstrates how a function can return multiple values to the calling environment, but without using multiple out parameters, the video shows an example on how we can create a object, a nested table based on the object and then returning the nested table type from the function.
Views: 17670 Kishan Mashru
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: 8900 Naresh i Technologies
Analytical Functions in oracle explained with real examples
 
25:24
This video explains analytical functions and how they are implemented in real projects. Analytical functions are somewhat similar to aggregate functions,but they offer much more. Why use analytical function ? They allow you to write fast and concise queries which otherwise will involve self join and long processing times They allow you to perform aggregate functions independently on sets of partitions. You can access values from previous rows in current row and you can restrict the window on which you want to apply this analytical function. I have given additional practice exercises along with the dataset so that you can comfortably work with analytical functions. You can find the sample problems along with dataset in the below link. http://www.internshipsfromhome.com/oracle-analytical-functions-ddl-and-dml/
Views: 15400 Tech Coach
Using Rownum to find out Nth record from First, Last and within a range in a table.
 
04:59
Using Rownum to find the first nth record and last nth record and in between record in emp table. This is the most frequesntly questions ever asked in interviews.
Views: 3159 Kiranjit Parida
How to Find 3rd Max Salary in SQL
 
03:00
How to Find 3rd Max Salary in SQL How to find out nth highest salary from your table or say master. How to retrieve nth position of record/data of from table/master? , Query to select top 2nd,3rd, 4th max salary from given table. This session is useful to find the nth max salary from a table. It shows two simple queries to find the nth max salary from a table, How to find second,third highest figure from a table. Top salary queries in sql. Update: in video, getting 4th max salary not 3rd. In order to get 3rd need to write 2 instead 3, like we doing for 2nd max salary by writing 1 sorry for inconvenience :)
Views: 10039 Technical Train
Find nth Highest Salary | Oracle/SQL
 
29:45
Link for all dot net and sql server video tutorial playlists Link for slides, code samples and text version of the . This video describes how to find out nth highest salary, different / various methods / ways/types to find nth highest/lowest salary/income value using SQL server. A Chanel where you can learn computer related problem and technology. How to Find 2nd and 3rd Highest Salary, or 5th Highest Salary Using ORACLE SQL DEVELOPER. SQL Query to find 3rd highest salary.
Views: 102 Andrew Mcdowell
How to find out Highest ,Second highest Third highest salary and Nth highest salary in SQL Server
 
07:25
A Chanel Where you can learn about Computer Information and Technology Like Asp.Net C#,Asp.Net MVC,SQL Server,Bootstrap,Oracle and more Website design. Website : http://www.dawacit.com Facebook : https://www.facebook.com/dawacit05 Twitter : https://twitter.com/dawacit How to send email in ASP.NET C# using Gmail smtp server step by step https://youtu.be/rnQphXehu7g How to send email with attachment in Asp.Net through Gmail smtp server,simple steps https://youtu.be/mhRe_we4hhA How to create Navigation bar in Asp.Net with CSS https://youtu.be/xVgtAdxhIxk How to create website using ASP.NET very attractive design https://youtu.be/uAKWaCl-vK4 How to create login and logout in Asp.Net MVC https://youtu.be/7E8IK_eAsWE
Views: 29333 Dawa CIT
Oracle EXP Function
 
01:15
https://www.databasestar.com/oracle-mathematical-functions/ The Oracle EXP function returns the mathematical value of e raised to the nth power. EXP is short for “exponential”. The value of “e” is a mathematical constant, and it’s roughly equal to 2.71. The syntax of the EXP function is: EXP( number ) We don't need to specify the value of e, because it's a standard value (kind of like pi). The parameter for EXP is a number which represents the power to raise e to. For example, if the number is 2, then the function calculates e to the power of 2 and returns that number. For more information about the Oracle EXP function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-mathematical-functions/
Views: 118 Database Star
How to Fetch Latest record/row from the Table in Oracle Database
 
03:32
You will learn how to How to Fetch Latest record/row from the Table in Oracle Database. Realtime Scenario: This logic can be implemented to find whether the latest record should be inserted or updated to datawarehouse
How to delete duplicate records from a table in oracle
 
02:01
Let c how to remove duplicates from a table..I have one table here 'student' It has two duplicate records. 3 joe 4 robert These two records has duplicates. So I have to remove them. I can remove them using rowid functionality using one subquery. You can give different column names in GROUP BY clause. to identify the duplicate record. You can give just primary key values also. Duplicate rows are deleted now... Query used in video: delete from student where rowid not in (select min(rowid) from student group by sno,sname); Thanks for watching...:)
Views: 14855 WingsOfTechnology
Find the Second highest salary
 
12:07
This video demonstrates the various ways through which we can find the second max value from a column in table.
Views: 585 Kishan Mashru
SQL Tutorial : How To Delete Duplicate Records in SQL Oracle
 
07:43
SQL Tutorial : How To Delete Duplicate Records in SQL 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: 2827 TechLake
Oracle GREATEST Function
 
02:43
https://www.databasestar.com/oracle-greatest/ The Oracle GREATEST function is a useful function, but I don’t think it’s very well understood. It returns the greatest or largest value in a set of values. You can provide the values, and use numbers or text values. The syntax for the Oracle GREATEST function is: GREATEST(expr1 [, expr_n]) The expr1 is the first expression to use for your comparison. Expr_n is one or more expressions to use in the comparison, separated by commas. This is optional. So how is GREATEST calculated? If the parameters are numeric, the GREATEST function finds the largest number. If the parameters are characters, the function finds the latest value if they were sorted alphabetically (using their character values). What data type is returned? It depends on the parameters you provide. It could be the same as the parameters you provide, or VARCHAR2 if the parameters are all characters. You can also use the GREATEST function with dates. It’s also the opposite of the LEAST function. For more information on the GREATEST function, including the SQL used in this video and the examples, read the related post here: https://www.databasestar.com/oracle-greatest/
Views: 227 Database Star
Oracle DENSE_RANK Function
 
04:09
https://www.databasestar.com/oracle-dense_rank/ The Oracle DENSE_RANK function allows you to calculate a rank of a row in a group of rows. It returns this rank as a NUMBER value. This function can be used as either an analytic or an aggregate function, and the syntax depends on how you use it. As an aggregate function it looks like this: DENSE_RANK ( expr, [expr(n)] ) WITHIN GROUP ( ORDER BY (order_expr [ASC|DESC] [NULLS FIRST|LAST] ) These parameters are: - expr: this is the value to search for in a particular table. You can specify multiple search values, and just separate them by a comma. - order_expr: this is used to order the expr values by, because you get a different results depending on what order you rank the values in. - NULLS FIRST or LAST: this is where you can specify if you want to have NULL values appear at the top of your ranking order or at the bottom. As an analytic function, it looks like this: DENSE_RANK() OVER ( [query_partition_clause] order_by_clause) The parameters are: - query_partition_clause: this is the expression that the ranking is “grouped” by, as analytic functions allow you to group data within rows. - order_by_clause: this is the expression to order your results by to determine a rank. For this function, if two records have the same sort or rank position, they will have the same RANK value. It won’t cause a gap in the rankings, unlike the RANK function which does cause a gap. For more information about the Oracle DENSE_RANK function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-dense_rank/
Views: 255 Database Star
SQL query to find first record and last record in single query
 
08:55
SQL query to find first record and last record in single query
Views: 8132 SQL Guru
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: 774 SkillBuilders
Difference between rows and range
 
05:52
range vs rows in sql server difference between rows clause and range clause in sql server range clause vs rows clause in sql server sql server running total query running total example in sql server In this video we will discuss the difference between rows and range in SQL Server. This is continuation to Part 116. Please watch Part 116 from SQL Server tutorial before proceeding. Let us understand the difference with an example. We will use the following Employees table in this demo. SQL Script to create the Employees table Create Table Employees ( Id int primary key, Name nvarchar(50), Salary int ) Go Insert Into Employees Values (1, 'Mark', 1000) Insert Into Employees Values (2, 'John', 2000) Insert Into Employees Values (3, 'Pam', 3000) Insert Into Employees Values (4, 'Sara', 4000) Insert Into Employees Values (5, 'Todd', 5000) Go Calculate the running total of Salary and display it against every employee row The following query calculates the running total. We have not specified an explicit value for ROWS or RANGE clause. SELECT Name, Salary, SUM(Salary) OVER(ORDER BY Salary) AS RunningTotal FROM Employees So the above query is using the default value which is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW This means the above query can be re-written using an explicit value for ROWS or RANGE clause as shown below. SELECT Name, Salary, SUM(Salary) OVER(ORDER BY Salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal FROM Employees We can also achieve the same result, by replacing RANGE with ROWS SELECT Name, Salary, SUM(Salary) OVER(ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal FROM Employees What is the difference between ROWS and RANGE To understand the difference we need some duplicate values for the Salary column in the Employees table. Execute the following UPDATE script to introduce duplicate values in the Salary column Update Employees set Salary = 1000 where Id = 2 Update Employees set Salary = 3000 where Id = 4 Go Now execute the following query. Notice that we get the running total as expected. SELECT Name, Salary, SUM(Salary) OVER(ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal FROM Employees The following query uses RANGE instead of ROWS SELECT Name, Salary, SUM(Salary) OVER(ORDER BY Salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal FROM Employees Notice we don't get the running total as expected. So, the main difference between ROWS and RANGE is in the way duplicate rows are treated. ROWS treat duplicates as distinct values, where as RANGE treats them as a single entity. All together side by side. The following query shows how running total changes 1. When no value is specified for ROWS or RANGE clause 2. When RANGE clause is used explicitly with it's default value 3. When ROWS clause is used instead of RANGE clause SELECT Name, Salary, SUM(Salary) OVER(ORDER BY Salary) AS [Default], SUM(Salary) OVER(ORDER BY Salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Range], SUM(Salary) OVER(ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Rows] FROM Employees Text version of the video http://csharp-video-tutorials.blogspot.com/2015/10/difference-between-rows-and-range.html Slides http://csharp-video-tutorials.blogspot.com/2015/10/difference-between-rows-and-range_8.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: 32824 kudvenkat
Difference between Row Number, Rank and Dense Rank | Oracle SQL Tutorial | Mr.Murali
 
20:46
►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: 8852 Naresh i Technologies
Oracle DECODE Function
 
04:31
https://www.databasestar.com/oracle-decode-function/ The Oracle DECODE function lets you perform IF-THEN-ELSE functionality in your queries. It’s similar to a CASE statement. The syntax of the DECODE function is: DECODE ( expression, search, result [, search, result]... [,default] ) The parameters of the Oracle DECODE function are: - expression (mandatory): This is the value to compare. - search (mandatory): This is the value to compare against the expression. - result (mandatory): This is the return value if the search value matches the expression value. There can be multiple combinations of search and result values, and the result value is attached to the previous search value. - default (optional): If none of the search values match, then this value is returned. If this is not provided, the DECODE function will return NULL if no matches are found. If you compare this to an IF-THEN-ELSE statement, it would look like this: IF (expression = search) THEN result [ELSE IF (expression = search) THEN result] ELSE default END IF The parameters can be one of many different data types, and the return type is calculated from them. Earlier I mentioned it’s similar to CASE. So why would you use one and not the other? - DECODE is an older function, and CASE was introduced as a replacement for DECODE. - CASE offers more flexibility then DECODE - CASE is also easier to read and debug (in my opinion) The performance of these functions is the same, so if you’re considering using DECODE, I would suggest using CASE instead. For more information about the Oracle DECODE function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-decode-function/
Views: 420 Database Star
FIRST VALUE function in SQL Server
 
02:48
In this video we will discuss FIRST_VALUE function in SQL Server FIRST_VALUE function Introduced in SQL Server 2012 Retrieves the first value from the specified column ORDER BY clause is required PARTITION BY clause is optional Syntax : FIRST_VALUE(Column_Name) OVER (ORDER BY Col1, Col2, ...) FIRST_VALUE function example WITHOUT partitions : In the following example, FIRST_VALUE function returns the name of the lowest paid employee from the entire table. SELECT Name, Gender, Salary, FIRST_VALUE(Name) OVER (ORDER BY Salary) AS FirstValue FROM Employees FIRST_VALUE function example WITH partitions : In the following example, FIRST_VALUE function returns the name of the lowest paid employee from the respective partition. SELECT Name, Gender, Salary, FIRST_VALUE(Name) OVER (PARTITION BY Gender ORDER BY Salary) AS FirstValue FROM Employees Text version of the video http://csharp-video-tutorials.blogspot.com/2015/10/firstvalue-function-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/10/firstvalue-function-in-sql-server_6.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: 32275 kudvenkat
Rank and Dense Rank in SQL Server
 
10:08
rank and dense_rank example difference between rank and dense_rank with example rank vs dense_rank in sql server 2008 sql server difference between rank and dense_rank In this video we will discuss Rank and Dense_Rank functions in SQL Server Rank and Dense_Rank functions Introduced in SQL Server 2005 Returns a rank starting at 1 based on the ordering of rows imposed by the ORDER BY clause ORDER BY clause is required PARTITION BY clause is optional When the data is partitioned, rank is reset to 1 when the partition changes Difference between Rank and Dense_Rank functions Rank function skips ranking(s) if there is a tie where as Dense_Rank will not. For example : If you have 2 rows at rank 1 and you have 5 rows in total. RANK() returns - 1, 1, 3, 4, 5 DENSE_RANK returns - 1, 1, 2, 3, 4 Syntax : RANK() OVER (ORDER BY Col1, Col2, ...) DENSE_RANK() OVER (ORDER BY Col1, Col2, ...) RANK() and DENSE_RANK() functions without PARTITION BY clause : In this example, data is not partitioned, so RANK() function provides a consecutive numbering except when there is a tie. Rank 2 is skipped as there are 2 rows at rank 1. The third row gets rank 3. DENSE_RANK() on the other hand will not skip ranks if there is a tie. The first 2 rows get rank 1. Third row gets rank 2. SELECT Name, Salary, Gender, RANK() OVER (ORDER BY Salary DESC) AS [Rank], DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank FROM Employees RANK() and DENSE_RANK() functions with PARTITION BY clause : Notice when the partition changes from Female to Male Rank is reset to 1 SELECT Name, Salary, Gender, RANK() OVER (PARTITION BY Gender ORDER BY Salary DESC) AS [Rank], DENSE_RANK() OVER (PARTITION BY Gender ORDER BY Salary DESC) AS DenseRank FROM Employees Use case for RANK and DENSE_RANK functions : Both these functions can be used to find Nth highest salary. However, which function to use depends on what you want to do when there is a tie. Let me explain with an example. If there are 2 employees with the FIRST highest salary, there are 2 different business cases 1. If your business case is, not to produce any result for the SECOND highest salary, then use RANK function 2. If your business case is to return the next Salary after the tied rows as the SECOND highest Salary, then use DENSE_RANK function Since we have 2 Employees with the FIRST highest salary. Rank() function will not return any rows for the SECOND highest Salary. WITH Result AS ( SELECT Salary, RANK() OVER (ORDER BY Salary DESC) AS Salary_Rank FROM Employees ) SELECT TOP 1 Salary FROM Result WHERE Salary_Rank = 2 Though we have 2 Employees with the FIRST highest salary. Dense_Rank() function returns, the next Salary after the tied rows as the SECOND highest Salary WITH Result AS ( SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS Salary_Rank FROM Employees ) SELECT TOP 1 Salary FROM Result WHERE Salary_Rank = 2 You can also use RANK and DENSE_RANK functions to find the Nth highest Salary among Male or Female employee groups. The following query finds the 3rd highest salary amount paid among the Female employees group WITH Result AS ( SELECT Salary, Gender, DENSE_RANK() OVER (PARTITION BY Gender ORDER BY Salary DESC) AS Salary_Rank FROM Employees ) SELECT TOP 1 Salary FROM Result WHERE Salary_Rank = 3 AND Gender = 'Female' Text version of the video http://csharp-video-tutorials.blogspot.com/2015/10/rank-and-denserank-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/10/rank-and-denserank-in-sql-server_1.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: 71724 kudvenkat
SQL Interview Question to Find 2nd Highest Salary
 
03:15
Learn the concept to one of the most frequently asked SQL/Oracle Interview questions to find the Nth Highest/Lowest value from a table. Thanks for watching!
Views: 8574 Disha Saxena
How to get Top / Bottom N Records from SQL Server Table in SQL Server - TSQL Tutorial Part 113
 
03:34
SQL Server / T-SQL Tutorial Scenario: How to get Top / Bottom N Records from SQL Server Table in SQL Server? You are working as SQL Server or TSQL Developer, you need to get top/bottom x rows from a table. How would you do that? Link to scripts used in SQL Server / TSQL Tutorial Video http://www.techbrothersit.com/2016/06/how-to-get-top-bottom-n-records-from.html Check out our website for Different SQL Server, MSBI tutorials and interview questions such as SQL Server Reporting Services(SSRS) Tutorial SQL Server Integration Services(SSIS) Tutorial SQL Server DBA Tutorial SQL Server / TSQL Tutorial ( Beginner to Advance) http://www.techbrothersit.com/
Views: 3683 TechBrothersIT
SQL Tutorial - 21: The UPDATE Query
 
06:12
In this tutorial we'll check out the UPDATE Query which is used to update/change records in a table.
Views: 83234 The Bad Tutorials
Null value in oracle
 
09:08
this video describes null concept in oracle. join the group and share your knowledge and ask your doubts. https://www.facebook.com/groups/940425699338535/
Nth highest salary of an employee and Top 3 salaried employees
 
10:42
Oracle SQL PLSQL and Unix Shell Scripting
Views: 1749 Sridhar Raghavan
How to Delete the Duplicate Records in oracle
 
03:44
http://oracletemple.blogspot.in/ How to Delete the duplicate records How delete the duplicate rows ? Tips: - Always take extra caution while deleting records. 1. First identify the duplicates using select. 2. Double verify those are actual ‘duplicates’ or not 3. Take backup if necessary 4. Apply commit only if you are sure. -~-~~-~~~-~~-~- Please watch: "difference between delete and truncate" https://www.youtube.com/watch?v=7b6wQ3Qumgg -~-~~-~~~-~~-~- ------------------------------------------------------------------ Below is link for Useful Pl/SQL Books http://goo.gl/XMy0tt ---------------------------------------------------------- Watch More... What is Parameterized Cursor in Oracle https://www.youtube.com/watch?v=JMKwHlVi6-A What is autonomous transaction in Oracle. https://www.youtube.com/watch?v=gyvFajpfoWE What are Constraints available in Oracle. https://www.youtube.com/watch?v=WivhdLXQklQ What is Mutation Error and How to avoid mutation. https://www.youtube.com/watch?v=CbWNCyW18Bs 5 Effective way to delete Duplicate rows https://www.youtube.com/watch?v=1wtTtnc87Oc What is Difference between Delete and Truncate https://www.youtube.com/watch?v=7b6wQ3Qumgg
Views: 21896 Ram Gupta
How to calculate second highest salary in sql in Hindi
 
03:50
In this video i'm going to show you how to get a second last highest values from your table, in this query i'm using group function and subquery together.
Views: 2047 OCP Technology
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: 32007 kudvenkat
SQL:Max/Min Functions
 
03:38
In this tutorial, you'll learn how to use max min functions in sql queries. 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: 4062 radhikaravikumar
LAG analytical function in plsql.
 
14:18
How to use LAG analytical function in plsql to find the nth nearest record from the top.Watch this pl sql tutorial on lag analytical functions in oracle to find out records with nth lagging values.
Views: 498 Subhroneel Ganguly
PL/SQL:NVL/NVL2/Coalesce function
 
05:26
In this tutorial, you'll learn the difference between NVL,NVL2 &Coalesce functions 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: 4014 radhikaravikumar
Over clause in SQL Server
 
09:13
over partition by in sql server 2008 sql server over clause partition partition by clause in sql server 2008 over partition by clause in sql In this video we will discuss the power and use of Over clause in SQL Server. The OVER clause combined with PARTITION BY is used to break up data into partitions. Syntax : function (...) OVER (PARTITION BY col1, Col2, ...) The specified function operates for each partition. For example : COUNT(Gender) OVER (PARTITION BY Gender) will partition the data by GENDER i.e there will 2 partitions (Male and Female) and then the COUNT() function is applied over each partition. Any of the following functions can be used. Please note this is not the complete list. COUNT(), AVG(), SUM(), MIN(), MAX(), ROW_NUMBER(), RANK(), DENSE_RANK() etc. Example : SQl Script to create 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', 5000) Insert Into Employees Values (2, 'John', 'Male', 4500) Insert Into Employees Values (3, 'Pam', 'Female', 5500) Insert Into Employees Values (4, 'Sara', 'Female', 4000) Insert Into Employees Values (5, 'Todd', 'Male', 3500) Insert Into Employees Values (6, 'Mary', 'Female', 5000) Insert Into Employees Values (7, 'Ben', 'Male', 6500) Insert Into Employees Values (8, 'Jodi', 'Female', 7000) Insert Into Employees Values (9, 'Tom', 'Male', 5500) Insert Into Employees Values (10, 'Ron', 'Male', 5000) Go Write a query to retrieve total count of employees by Gender. Also in the result we want Average, Minimum and Maximum salary by Gender. This can be very easily achieved using a simple GROUP BY query as show below. SELECT Gender, COUNT(*) AS GenderTotal, AVG(Salary) AS AvgSal, MIN(Salary) AS MinSal, MAX(Salary) AS MaxSal FROM Employees GROUP BY Gender What if we want non-aggregated values (like employee Name and Salary) in result set along with aggregated values You cannot include non-aggregated columns in the GROUP BY query. SELECT Name, Salary, Gender, COUNT(*) AS GenderTotal, AVG(Salary) AS AvgSal, MIN(Salary) AS MinSal, MAX(Salary) AS MaxSal FROM Employees GROUP BY Gender The above query will result in the following error : Column 'Employees.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause One way to achieve this is by including the aggregations in a subquery and then JOINING it with the main query as shown in the example below. Look at the amount of T-SQL code we have to write. SELECT Name, Salary, Employees.Gender, Genders.GenderTotals, Genders.AvgSal, Genders.MinSal, Genders.MaxSal FROM Employees INNER JOIN (SELECT Gender, COUNT(*) AS GenderTotals, AVG(Salary) AS AvgSal, MIN(Salary) AS MinSal, MAX(Salary) AS MaxSal FROM Employees GROUP BY Gender) AS Genders ON Genders.Gender = Employees.Gender Better way of doing this is by using the OVER clause combined with PARTITION BY SELECT Name, Salary, Gender, COUNT(Gender) OVER(PARTITION BY Gender) AS GenderTotals, AVG(Salary) OVER(PARTITION BY Gender) AS AvgSal, MIN(Salary) OVER(PARTITION BY Gender) AS MinSal, MAX(Salary) OVER(PARTITION BY Gender) AS MaxSal FROM Employees Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/over-clause-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/over-clause-in-sql-server_29.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: 82246 kudvenkat
Oracle SQL MEDIAN Function Examples and Guide
 
03:28
https://www.databasestar.com/median-mean-mode-in-oracle/ In this video, you’ll learn what the term “median” means, and how to generate an Oracle SQL median value. What is median? It’s the middle value in a list of values, after the values have been sorted in ascending order. It’s kind of like an average, but it’s different, as the average finds the total of the values divided by the number of values and will often not be one of the values in the list. Median will usually be the value in the list that is the middle point. So, how can you calculate the median in Oracle SQL? We use a function called MEDIAN. And in this video, I’ll show you how to use the function and see some examples. For more information on the MEDIAN function, including the SQL used in this video, read the related article here: https://www.databasestar.com/median-mean-mode-in-oracle/
Views: 161 Database Star
IS NOT NULL, IS NULL, NVL FUNCTION in oracle sql
 
03:04
Oracle sql tutorial, How to select, retrieve rows with null values, not null values and NVL function in oracle SQL
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: 640 khaled alkhudari
Oracle Performance Tuning - Lead and Lag
 
04:19
A brief overview about Lead and Lag analytic function in Oracle
Views: 1293 Saurabh Kumar
Filtering on Date Values using the SQL WHERE Clause (Introduction to Oracle SQL)
 
03:36
How to filter on date values in SQL using the SQL WHERE clause. The full Introduction to Oracle SQL course is available here: https://www.databasestar.com/introduction-to-oracle-sql-course/
Views: 192 Database Star
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: 47 Adam Tech
What Oracle SQL analytic functions are?
 
04:20
In this training video we will learn how to usу analytic SQL functions in Oracle
Views: 57 Spod ssppoodd
ORACLE SQL AND PL/SQL INTERVIEW QUESTION : DELETE DUPLICATE RECORDS FROM  A TABLE(3 ways)
 
13:15
This video tutorial demonstrates about how we can delete duplicate records from a table, the video shows 3 different ways to delete duplicate record from a table, one using Group by, second one by using correlated joins and third on by using rownum analytical function You can get the code from our website http://oracleplsqlblog.com/FullBlog/FullBlog/21
Views: 3740 Kishan Mashru
SQL: Extract function
 
03:38
In this tutorial, you'll learn how to make use of extract 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: 3601 radhikaravikumar
Part 4   Delete duplicate rows in sql
 
03:26
Link for all dot net and sql server video tutorial playlists http://www.youtube.com/user/kudvenkat/playlists Link for slides, code samples and text version of the video http://csharp-video-tutorials.blogspot.com/2014/05/part-4-delete-duplicate-rows-in-sql.html In this video, we will discuss deleting all duplicate rows except one from a sql server table. SQL Script to create Employees table Create table Employees ( ID int, FirstName nvarchar(50), LastName nvarchar(50), Gender nvarchar(50), Salary int ) GO Insert into Employees values (1, 'Mark', 'Hastings', 'Male', 60000) Insert into Employees values (1, 'Mark', 'Hastings', 'Male', 60000) Insert into Employees values (1, 'Mark', 'Hastings', 'Male', 60000) Insert into Employees values (2, 'Mary', 'Lambeth', 'Female', 30000) Insert into Employees values (2, 'Mary', 'Lambeth', 'Female', 30000) Insert into Employees values (3, 'Ben', 'Hoskins', 'Male', 70000) Insert into Employees values (3, 'Ben', 'Hoskins', 'Male', 70000) Insert into Employees values (3, 'Ben', 'Hoskins', 'Male', 70000) The delete query should delete all duplicate rows except one. Here is the SQL query that does the job. PARTITION BY divides the query result set into partitions. WITH EmployeesCTE AS ( SELECT *, ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID) AS RowNumber FROM Employees ) DELETE FROM EmployeesCTE WHERE RowNumber ] 1
Views: 321189 kudvenkat

dating cdvs
dating belgian guys
best gay dating apps uk
internet dating second date
scientific methods of dating fossils