Home
Search results “Count rows oracle”
HOW TO IDENTIFY AND DELETE DUPLICATE ROWS USING ROWID AND GROUPBY IN ORACLE SQL
 
07:53
This video demonstrates examples on how to find and delete duplicate records from a table. The video gives simple and easy to understand examples on finding duplicate records from a table using group by and having clause and row_number function. It also shows the ways in which duplicates can be deleted very efficiently using the rowid of that record. You can get the code from our website http://oracleplsqlblog.com/FullBlog/FullBlog/21
Views: 10603 Kishan Mashru
ORACLE Analytic Functions for Data Warehousing 1/ Arabic
 
16:59
ORACLE Analytic Functions for Data Warehousing 1/ Arabic --analytic functions allow developers to perform tasks in SQL that were previously confined to procedural languages. -- let's start by aggregate functions ( sum, avg, count,...) select * from EMPLOYEES; select avg(salary) from employees; select DEPARTMENT_ID, avg(salary) from employees group by DEPARTMENT_ID order by 1 /* Analytic functions also operate on subsets of rows, similar to aggregate functions in GROUP BY queries, but they do not reduce the number of rows returned by the query */ select avg(salary) from employees; select employee_id, first_name,salary,(select avg(salary) from employees) avg_sal from EMPLOYEES; --analytic_function([ arguments ]) OVER (analytic_clause) --The analytic_clause breaks down into the following optional elements. --[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ] select employee_id, first_name,salary, avg(salary) over() avg_sal from EMPLOYEES; select employee_id, first_name,salary, avg(salary) over( order by EMPLOYEE_ID ) from EMPLOYEES; select DEPARTMENT_ID, avg(salary) from employees group by DEPARTMENT_ID order by 1 select employee_id, first_name,salary,DEPARTMENT_ID, avg(salary) over(PARTITION BY DEPARTMENT_ID) avg_sal_dept from EMPLOYEES; select employee_id, first_name,salary,DEPARTMENT_ID, avg(salary) over(PARTITION BY DEPARTMENT_ID order by employee_id ) from EMPLOYEES drop table concan_card; create table concan_card ( rec_serial number primary key, game_name varchar2(100), game_round number, player_name varchar2(100), game_round_score number ); insert into concan_card values(1,'GAME1',1,'ahmed', -60); insert into concan_card values(2,'GAME1',1,'khaled', 200); insert into concan_card values(3,'GAME1',1,'ali', 30); insert into concan_card values(4,'GAME1',2,'ahmed', 10); insert into concan_card values(5,'GAME1',2,'khaled', -30); insert into concan_card values(6,'GAME1',2,'ali', 100); insert into concan_card values(7,'GAME1',3,'ahmed', -30); insert into concan_card values(8,'GAME1',3,'khaled', 100); insert into concan_card values(9,'GAME1',3,'ali', 6); select * from concan_card select rec_serial,game_name ,game_round,player_name, game_round_score, sum(game_round_score) over ( PARTITION BY player_name order by player_name,game_round ) from concan_card select rec_serial,game_name ,game_round,player_name, game_round_score, sum(game_round_score) over ( PARTITION BY player_name order by rec_serial ) from concan_card select rec_serial,game_name ,game_round,player_name, game_round_score, sum(game_round_score) over ( PARTITION BY game_name,player_name order by rec_serial ) from concan_card insert into concan_card values(10,'GAME2',1,'ahmed', -60); insert into concan_card values(11,'GAME2',1,'khaled', 200); insert into concan_card values(12,'GAME2',1,'ali', 30); insert into concan_card values(13,'GAME2',2,'ahmed', 10); insert into concan_card values(14,'GAME2',2,'khaled', -30); insert into concan_card values(15,'GAME2',2,'ali', 100); insert into concan_card values(16,'GAME2',3,'ahmed', -30); insert into concan_card values(17,'GAME2',3,'khaled', 100); insert into concan_card values(18,'GAME2',3,'ali', 6); select rec_serial,game_name ,game_round,player_name, game_round_score, sum(game_round_score) over ( PARTITION BY player_name order by player_name,game_round ) from concan_card select rec_serial,game_name ,game_round,player_name, game_round_score, sum(game_round_score) over ( PARTITION BY player_name order by rec_serial ) from concan_card select rec_serial,game_name ,game_round,player_name, game_round_score, sum(game_round_score) over ( PARTITION BY game_name,player_name order by rec_serial ) from concan_card
Views: 5053 khaled alkhudari
Row Number function in SQL Server
 
07:24
sql server row_number example sql server row number by partition sql server row_number over partition by order by In this video we will discuss Row_Number function in SQL Server. This is continuation to Part 108. Please watch Part 108 from SQL Server tutorial before proceeding. Row_Number function Introduced in SQL Server 2005 Returns the sequential number of a row starting at 1 ORDER BY clause is required PARTITION BY clause is optional When the data is partitioned, row number is reset to 1 when the partition changes Syntax : ROW_NUMBER() OVER (ORDER BY Col1, Col2) Row_Number function without PARTITION BY : In this example, data is not partitioned, so ROW_NUMBER will provide a consecutive numbering for all the rows in the table based on the order of rows imposed by the ORDER BY clause. SELECT Name, Gender, Salary, ROW_NUMBER() OVER (ORDER BY Gender) AS RowNumber FROM Employees Please note : If ORDER BY clause is not specified you will get the following error The function 'ROW_NUMBER' must have an OVER clause with ORDER BY Row_Number function with PARTITION BY : In this example, data is partitioned by Gender, so ROW_NUMBER will provide a consecutive numbering only for the rows with in a parttion. When the partition changes the row number is reset to 1. SELECT Name, Gender, Salary, ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Gender) AS RowNumber FROM Employees Use case for Row_Number function : Deleting all duplicate rows except one from a sql server table. Discussed in detail in Part 4 of SQL Server Interview Questions and Answers video series. Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/rownumber-function-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/rownumber-function-in-sql-server_30.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: 85484 kudvenkat
Oracle SQL Developer Tutorial For Beginners   85   Find the Total Number of Rows
 
00:52
Oracle SQL Developer Tutorial For Beginners Series. This course introduces Oracle SQL Development for its subscribers. Currently this is based on Oracle 12c. The test environment is in Windows 10.
Views: 659 Sam Dhanasekaran
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: 11001 Naresh i Technologies
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: 20450 Kishan Mashru
ROWID VS ROWNUM IN ORACLE
 
09:21
This Video explains the difference between ROWID and ROWNUM using real project examples. ROWID provides the unique physical address where the row is being stored. ROWNUM indicates the order in which the data was returned from the select query.
Views: 3329 Tech Coach
ORACLE SQL INTERVIEW QUESTION : HOW TO SELECT ODD OR EVEN ROWS FROM A TABLE IN ORACLE
 
03:52
This video tutorial answers the frequently asked interview question, how to select only odd or even records in oracle sql, after watching this video you will understand more on the topic and will be able to answer this interview question with a lot of confidence.
Views: 4522 Kishan Mashru
SQL Aggregation queries using Group By, Sum, Count and Having
 
10:01
From SQL Queries Joes 2 Pros (Vol2) ch4.1. Learn up to write aggregated queries.
Views: 177610 Joes2Pros SQL Trainings
XMLTABLE : Convert XML into Rows and Columns using SQL
 
03:11
This video gives a quick overview of the XMLTABLE functionality, which can be used to convert XML documents in to rows and columns in SQL. For more information see: https://oracle-base.com/articles/misc/xmltable-convert-xml-data-into-rows-and-columns-using-sql https://oracle-base.com/articles/misc/sqlxml-sqlx-generating-xml-content-using-sql Website: https://oracle-base.com Blog: https://oracle-base.com/blog Twitter: https://twitter.com/oraclebase Cameo by Mike Hichwa : Twitter : https://twitter.com/mikehichwa1 Cameo appearances are for fun, not an endorsement of the content of this video. All trademarks, product names and logos are the property of their respective owners.
Views: 23039 ORACLE-BASE.com
Counting Data (Introduction to Oracle SQL)
 
04:02
Learn how to count data and records using a function. The full Introduction to Oracle SQL course is available here: https://www.databasestar.com/introduction-to-oracle-sql-course/
Views: 128 Database Star
Oracle SQL Developer Tutorial For Beginners  15   SELECT COUNT
 
00:47
Oracle SQL Developer Tutorial For Beginners Series. This course introduces Oracle SQL Development for its subscribers. Currently this is based on Oracle 12c. The test environment is in Windows 10.
Views: 3036 Sam Dhanasekaran
Calculated Fields using values in other rows
 
05:07
Another little tip on Oracle BI based on a question I once got asked How to use formulas in calculated fields to compare values from different rows, using a function called MSUMS
Views: 3693 Edward's Oracle Blog
Table size ,Number of records,Disk usage in SQL Database,clear logs,cache
 
06:51
This clip will show you how to check each Table size, Number of records, Disk usage in SQL Database. You can see how to write the query to get details of all tables in terms of size, records, space utilization and free space on disk for tables. http://daynamicsaxaptatutorials.blogspot.com This is axapta ERP blog for Technical and functional fields and includes Microsoft Dynamics Axapta tutorials and Dynamics Axapta Coverage. This blog also contains x++ code help for Ax developer and solution of technical and functional daily issues. This blog is specific for Microsoft dynamics programming. Enterprise portal, SharePoint services, business connectors and Enterprise Resource Planning applications and sql database.It will help to get Microsoft Business Solutions.
Views: 945 Santosh kumar Singh
Transpose Rows into Columns in Oracle Database
 
11:36
Transpose rows to columns in Oracle , rows into columns in Oracle
Views: 294 Adam Tech
APEX27 of 30: Organize IG report: change column order, group & count rows. Issue with  NULL & COUNT.
 
09:53
APEX 18.1 video 27 of 30: Do some housekeeping: Assign new pages to page groups. See some more features in the interactive grid and interactive report pages for organizing and summarizing data. Reorder columns. Use the “control break” and “count” features. See an issue using COUNT when a field might have null values (no data).
Views: 314 Jennifer Kreie
Oracle sql Interview Question : How to change rows to column
 
09:18
This video tutorial talks about the frequently asked oracle sql interview question of changing rows to the column. Based on the given scenario, we are asked to change the presentation of data in a table with 3 rows and 2 column to 3 columns and 2 rows. Decode Function in oracle https://youtu.be/YUAjPMjqMws Pivot and unpivot in oracle https://youtu.be/6s0DI1YcWIM
Views: 16971 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: 4051 TechLake
Part 6   Transform rows into columns in sql server
 
08:53
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/06/part-6-transform-rows-into-columns-in.html This is another common sql server interview question. We will be using Countries table in this example. SQL to create the table Create Table Countries ( Country nvarchar(50), City nvarchar(50) ) GO Insert into Countries values ('USA','New York') Insert into Countries values ('USA','Houston') Insert into Countries values ('USA','Dallas') Insert into Countries values ('India','Hyderabad') Insert into Countries values ('India','Bangalore') Insert into Countries values ('India','New Delhi') Insert into Countries values ('UK','London') Insert into Countries values ('UK','Birmingham') Insert into Countries values ('UK','Manchester') Here is the interview question. Write a sql query to transpose rows to columns. Using PIVOT operator we can very easily transform rows to columns. Select Country, City1, City2, City3 From ( Select Country, City, 'City'+ cast(row_number() over(partition by Country order by Country) as varchar(10)) ColumnSequence from Countries ) Temp pivot ( max(City) for ColumnSequence in (City1, City2, City3) ) Piv
Views: 199331 kudvenkat
Create a Function to Count Records
 
10:56
More information at: http://prof-oracle.blogspot.com/2018/01/function-to-count-records.html
Views: 19 Gove Allen
How to Convert Rows into Columns in Oracle | Oracle | Oracle Tutorials
 
01:58
Converting rows into Columns. Transpose rows in to columns.
Pivot Table and its advanced properties in OBIEE 11g and 12C
 
08:22
This video shows you advanced properties of pivot table in obiee 11g and obiee 12c
Find duplicate records by using Group by and Having clause in SQL Server - TSQL Tutorial Part 132
 
07:44
How to find duplicate records by using Group by and Having clause in SQL Server Scenario: You are working as SQL Server developer. You need to write the query for dbo.Customer table that should return the duplicate records and count of duplicate record. Solution: You can use Group by and Count aggregate function to find all the duplicate records in table and count. Let's say that we have dbo.Customer table with below definition and data. We would like to find the record if it is duplicate by columns FName,LName and CountryShortName. Link to scripts used in SQL Server / TSQL Tutorial Video http://www.techbrothersit.com/2017/03/how-to-find-duplicate-records-by-using.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 2016 DBA Tutorial SQL Server / TSQL Tutorial ( Beginner to Advance) http://www.techbrothersit.com/
Views: 3491 TechBrothersIT
Tutorial#28 How to sort rows in oracle database| SQL Order by Clause
 
04:59
In this Video we will learn about how to sort our data from the table with the help of Order by Clause Or The ORDER BY Clause is used to sort data obtained from a table through a SELECT Query or ORDER BY descending order in SQL or Understanding the use of Order BY clause in SQL In this series we cover the following topics: SQL basics, create table oracle, SQL functions, SQL queries, SQL server, SQL developer installation, Oracle database installation, SQL Statement, OCA, Data Types, Types of data types, SQL Logical Operator, SQL Function,Join- Inner Join, Outer join, right outer join, left outer join, full outer join, self-join, cross join, View, SubQuery, Set Operator. follow me on: Facebook Page: https://www.facebook.com/LrnWthr-319371861902642/?ref=bookmarks Contacts Email: [email protected] Instagram: https://www.instagram.com/lrnwthr/ Twitter: https://twitter.com/LrnWthR
Views: 77 EqualConnect Coach
9. How to count the number of rows affected after DML write operations
 
12:19
This video explains you that how you can count the number of rows affected after DML write operations. Oracle is being used as the database.
Views: 54 Eduphilics
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: 349528 kudvenkat
ORACLE SQL 6: Distinct & Count
 
08:31
http://studyandshare.wordpress.com/ http://www.facebook.com/groups/studyandsharevn/
Views: 930 studyandsharevn
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: 35419 kudvenkat
Create  a Table,inserting record & find out count of records  in Oracle Database 10g using SQL Plus
 
05:27
How to Create a Table,inserting record & find out count of records in Oracle Database 10g using SQL Plus
Views: 38 Technical Aashique
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: 92161 The Bad Tutorials
Oracle Partition By clause, Oracle Analytic Functions tutorial
 
09:09
More about analytic functions, visit http://www.learn-with-video-tutorials.com Oracle Analytic Functions and Over By Partition clause enable calculating cumulative and moving aggregates returning multiple rows for each group.
Window functions in SQL Server
 
11:00
sql server window function example window function sql server example sql server rows range clause sql server rows between 1 preceding and 1 following In this video we will discuss window functions in SQL Server In SQL Server we have different categories of window functions Aggregate functions - AVG, SUM, COUNT, MIN, MAX etc.. Ranking functions - RANK, DENSE_RANK, ROW_NUMBER etc.. Analytic functions - LEAD, LAG, FIRST_VALUE, LAST_VALUE etc... OVER Clause defines the partitioning and ordering of a rows (i.e a window) for the above functions to operate on. Hence these functions are called window functions. The OVER clause accepts the following three arguments to define a window for these functions to operate on. ORDER BY : Defines the logical order of the rows PARTITION BY : Divides the query result set into partitions. The window function is applied to each partition separately. ROWSor RANGE clause : Further limits the rows within the partition by specifying start and end points within the partition. The default for ROWS or RANGE clause is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW Let us understand the use of ROWS or RANGE clause with an example. Compute average salary and display it against every employee We might think the following query would do the job. SELECT Name, Gender, Salary, AVG(Salary) OVER(ORDER BY Salary) AS Average FROM Employees As you can see from the result, the above query does not produce the overall salary average. It produces the average of the current row and the rows preceeding the current row. This is because, the default value of ROWS or RANGE clause (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) is applied. To fix this, provide an explicit value for ROWS or RANGE clause as shown below. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING tells the window function to operate on the set of rows starting from the first row in the partition to the last row in the partition. SELECT Name, Gender, Salary, AVG(Salary) OVER(ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Average FROM Employees The same result can also be achieved by using RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING Well, what is the difference between ROWS and RANGE We will discuss this in a later video The following query can be used if you want to compute the average salary of 1. The current row 2. One row PRECEDING the current row and 3. One row FOLLOWING the current row SELECT Name, Gender, Salary, AVG(Salary) OVER(ORDER BY Salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS Average FROM Employees Text version of the video http://csharp-video-tutorials.blogspot.com/2015/10/window-functions-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/10/window-functions-in-sql-server_7.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: 62067 kudvenkat
Oracle Table Creation Inserting Records Null Values 1
 
13:31
DURGASOFT is INDIA's No.1 Software Training Center offers online training on various technologies like JAVA, .NET , ANDROID,HADOOP,TESTING TOOLS , ADF, INFORMATICA, 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......
How to find duplicate records in table sql
 
06:32
There are various times when we need to find duplicate records in SQL Server. It is possible to find duplicates using DISTINCT, ROW NUMBER as well as the GROUP BY approach. ------------- May be you like this -------------- umbraco playlist : https://www.youtube.com/watch?v=zoefdruzmGE&list=PLv0T7WlllnSnYRedhwL8x-atkVxrSdd3m Angularjs playlist : https://www.youtube.com/watch?v=KXjD1RiAz1U&list=PLv0T7WlllnSkCfNftTNY8tWqCFWT0sIMd jquery playlist : https://www.youtube.com/watch?v=jVncasz_iyw&list=PLv0T7WlllnSkkNahiiWu_GMXq49X5ccfe F Wcf playlist : https://www.youtube.com/watch?v=NB4k0RC6vl0&list=PLv0T7WlllnSmcGHacEQnLgKvitcJEsSl6 c# playlist: https://www.youtube.com/watch?v=ZrP0NKbsr8s&list=PLv0T7WlllnSl9pZWMaMGsyoAmqZtW4gM0 Duplicate records can create problems sometimes when displaying reports or performing a Multiple Insert update. Finding duplicate records in a database needs further investigation. In some cases, duplicate records are positive, but it all depends on the data and the database design as well. For example, if a customer has ordered the same product twice on the same date with the the same shipping and billing address, then this may result in a duplicate record.
Views: 15685 Go Freelancer
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: 90386 kudvenkat
Tutorial#53  Count and Sum  Aggregate Function in Oracle SQL Database| Group by Function in SQL
 
09:01
Explaining How to get Count and Sum Value in Oracle Database in others words what is the aggregate function in Oracle or what are the types of aggregate function in SQL An Aggregate function is a function where the values of multiple rows are grouped together to form a single value of more significant meaning or measurements such as a set, a bag or a list or Aggregate Function in Oracle SQL Database or Aggregate Function in SQL or How to use Count and sum Aggregate Function in Oracle or Types of Aggregate function in SQL Assignment: Assignment link will be available soon: SQL basics, create table oracle, SQL functions, SQL queries, SQL server, SQL developer installation, Oracle database installation, SQL Statement, OCA, Data Types, Types of data types, SQL Logical Operator, SQL Function,Join- Inner Join, Outer join, right outer join, left outer join, full outer join, self-join, cross join, View, SubQuery, Set Operator. Follow me on: Facebook Page: https://www.facebook.com/LrnWthr-319371861902642/?ref=bookmarks Contacts Email: [email protected] Instagram: https://www.instagram.com/equalconnect/ Twitter: https://twitter.com/LrnWthR
Views: 27 EqualConnect Coach
Reporting aggregted data using group function in bangla Part-8||oracle taught
 
55:35
SQL has numerous predefined aggregate functions that can be used to write queries to produce exactly this kind of information.The GROUP BY clause specifies how to group rows from a data table when aggregating information, while the HAVING clause filters out rows that do not belong in specified groups. Aggregate functions perform a variety of actions such as counting all the rows in a table, averaging a column's data, and summing numeric data. Aggregates can also search a table to find the highest "MAX" or lowest "MIN" values in a column. As with other types of queries, you can restrict, or filter out the rows these functions act on with the WHERE clause. For example, if a manager needs to know how many employees work in an organization, the aggregate function named COUNT(*) can be used to produce this information.The COUNT(*) function shown in the below SELECT statement counts all rows in a table.
Views: 14 ORACLE TAUGHT
How to Create a Increament Serial Number Row in MySQL
 
02:02
how to generate sequence number in mysql how to add serial number in sql query how to update serial number in sql query mysql sequence function generate serial number in sql query oracle how to generate serial number in oracle report mysql insert sequential numbers how to generate auto increment serial number in sql server Website : http://intactabode.com/ linkedin https://www.linkedin.com/in/suryam-jy-ab471815b/ Google Plus https://plus.google.com/u/0/106052339846662160710 Twitter https://twitter.com/jysuryam Facebook https://www.facebook.com/intactabode/
Views: 538 Intact Abode
Oracle Tutorial - Duplicate a table with Records and without Records
 
04:28
Oracle Tutorials - Duplicate a Table with Records and without Records
Views: 43 Tech Acad
How to Select Even or Odd Rows from a Table in SQL Server
 
03:18
How to show only even or odd rows in sql For more SQL Server Tutorial Visit: http://subnol.blogspot.com/ #subnol #sqlfighter
Views: 6517 SQL Fighter
oracle mysql select max and return multiple values
 
06:25
Code and details: http://blog.softhints.com/oracle-mysql-select-max-and-return-multiple-values/ another example on max N per group http://blog.softhints.com/mysql-select-n-max-values-per-group/ Mysql group by column and return N max/min rows per group: * get max per group * get minimum per group * use additional constraints * return N per group --------------------------------------------------------------------------------------------------------------------------------------------------------------- If you really find this channel useful and enjoy the content, you're welcome to support me and this channel with a small donation via PayPal and Bitcoin. PayPal donation https://www.paypal.me/fantasyan Bitcoin: 1DBZu6N9JTpRDdc9QChLZnX3v2iVRaQ4ym Programming is a fun! :) Site: www.softhints.com Facebook: www.facebook.com/Softhints/ Twitter: www.twitter.com/SoftwareHints
Views: 35 Softhints
Oracle Database11g tutorials 10 || SQL Single Row Function (SQL Functions ) by Pravendra Gaur
 
18:13
Oracle Database11g tutorials 10 || SQL Single Row Function (SQL Functions ) by Pravendra Gaur. Like subscribe my channel Pravendra Gaur , I will teach you how to crack interviews and many other technologies. Like me on facebook https://www.facebook.com/pravendragaur.biz/ Email : [email protected] Oracel single row function. Character functions Upper Lower Initcap Concat Substr Instr Length Lpad Soundex Ltrim Rtrim Replace Regexp_count
Views: 100 Pravendra Gaur
How Does the Phyiscal Location of Rows Affect Indexes?: Finding All the Red Sweets Part 2
 
09:25
In part one of the red candy series, Chris compared the efficiency of using a index range scan and full table scan to access data. He found that a full table scan was more efficient when fetching more rows than there are table blocks. This analysis made a big assumption however. It worked on the presumption that there was no correlation between the order of candies in the document and which the bags they were in. In this episode tests this assumption. Chris looks at how the physical order of rows in a table can affect the efficiency of indexes on it. He discusses how Oracle tracks this via the clustering factor. ============================ 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: 3363 The Magic of SQL
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: 479 Tech Query Pond
Oracle Database11g tutorials 13 || SQL substr function / SQL substring function
 
06:34
link of SQL substr blog : http://www.rebellionrider.com/SQL-substr-function.htm SQL substr function SQL Substr function will return a sub string of a specified length from the source string beginning at a given position. Tool used in this tutorial is command prompt. This tutorial series is part of SQL expert exam certification training. if you are preparing for SQL certification you can use my tutorials. This SQL Tutorial is a part of free training. Copy Cloud referral link || Use this link to join copy cloud and get 20GB of free storage https://copy.com?r=j7eYO7 Contacts E-Mail [email protected] Twitter https://twitter.com/rebellionrider Instagram http://instagram.com/rebellionrider Facebook. https://www.facebook.com/imthebhardwaj Linkedin. http://in.linkedin.com/in/mannbhardwaj/ Thanks for linking, commenting, sharing and watching more of our videos This is Manish from RebellionRider.com ---------------------------------------------------------------------------- As the name suggests SQL Substr function will return substring from a given source string. Let's see the Syntax Substr (source_string, start_pos, Substr_length) As we can see SQL substr function takes 3 parameters. First one is Source string from which you want to extract the segment. Second parameter is Starting position for sub string from the Source string. And the third parameter is Substr_length which is the length for the substring. First two parameters are mandatory to specify while third one is optional. So we can say. SQL Substr function will return a sub string of a specified length from the source string beginning at a given position. First parameter source string can be of any data type CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB whereas both start_pos, Substr_length parameters must be number data type. The returning result of SQL Substr function is of same data type of source string. Let's see an example of SQL Substr function. SELECT substr('www.RebellionRider.com',5,14) FROM dual; Here in this query url of my website www.RebellionRider.com is our source string with the total length of 22 characters, Now, I want to extract the name of my website that is, RebellionRider. So if you count the total length of the name of the website, it is 14. That's why I have specified 14 as my third parameter of SQL Substr function which is substr_length. Also the name of the website RebellionRider is starting from 5th position therefore I have specified 5 at second parameter of SQL substr function which is strt_pos or starting position. Execute it. Here is our result RebellionRider When starting position is larger than the length of source string. In this case SQL Substr function will return NULL as a result. Let's do an example. SELECT substr('www.RebellionRider.com',23,14) FROM dual; As you can see here I have specified 23 at starting position and the total length of our source string is 22 characters Let's execute And the result is Null. Second scenario When the Substr_length is greater than source string In this case the segment return is the substring from starting position to the end of the string. For example SELECT substr('www.RebellionRider.com',5,23) FROM dual; Our starting position is at 5 means at the first R of RebellionRider and length of substring is set to 23 which is greater than the length of source string that is 22. Execute. As you can see we get a substring from first R of RebellionRider till the end of the source String. Third scenario When you supply numeric or arithmetic expression or a DATE instead of character as Source string to SQL Substr function In this scenario If you have supplied a numeric string instead of character as source string, the oracle engine casts them as a character when they occur as parameter to SQL Substr function. And if you have supplied Arithmetic expression or a DATE then The Oracle engine first solves or evaluates the Arithmetic expression or the DATE Then casts them as a character. Means if you have arithmetic expression in your source string then oracle will first solve it and then change or say cast the value of its result into character. Let's see some example. SELECT substr(50000-7,2,4) FROM dual; Oracle first evaluates the arithmetic expression that is 50000-7 equals to 49993. And then oracle engine casts this result 49993 into a character string. Means 49993 will be a 5 characters string. Starting position of substring is 2, that means from the first 9 of 49993 We specified the length of substring is 4 so we must get 9993 as our result. Let's check execute
Views: 81827 Manish Sharma
SQL 099 Column Functions, COUNT or How can I count the number of column rows?
 
02:03
Explains Column Function COUNT or How to count the number of column rows. From http://ComputerBasedTrainingInc.com SQL Course. Learn by doing SQL commands for ANSI Standard SQL, Access, DB2, MySQL, Oracle, PostgreSQL, and SQL Server.
Views: 1143 cbtinc
Filtering Data with WHERE (Introduction to Oracle SQL)
 
03:49
How to restrict the rows you display with the WHERE clause. The full Introduction to Oracle SQL course is available here: https://www.databasestar.com/introduction-to-oracle-sql-course/
Views: 230 Database Star