Home
Search results “Max over oracle sql”
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: 80182 kudvenkat
T-SQL: Over() and Partition By
 
04:17
Show how to use OVER and PARTITION BY to get groups of data with aggregation.
Views: 4677 MidnightDBA
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: 3929 radhikaravikumar
SQL TOP, RANK, DENSE_RANK, ROW_NUMBER Functions
 
08:27
Jamie King of Neumont University demonstrating various SQL ranking functions.
Views: 21755 Jamie King
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: 836081 kudvenkat
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: 54749 kudvenkat
Select Rows With Max Value By Group SQL Interview Question
 
10:06
How to select the max value from each set using group by in the free Adventure Works database. This example is done using sql server using SQL Server Management Studio. This is a common interview question. How to install SQL Server Management Studio and set up Adventure Works: https://www.youtube.com/watch?v=b6yNwGC7CY4&t=
Views: 168 Blake B
PL/SQL tutorial 80: Introduction to Native Dynamic SQL by Manish Sharma
 
04:31
RebellionRider.com presents an introduction to Native Dynamic SQL (NDS) in Oracle Database by Manish Sharma. In this tutorial, you will learn what is a Static SQL and a Dynamic SQL. Also different ways of using Dynamic SQL in Oracle Database ------------------------------------------------------------------------ ►►►LINKS◄◄◄ Blog: http://bit.ly/NDS-1 "What Is A Database Management Systems (DBMS)?" http://bit.ly/the-DBMS Previous Tutorial ► Bind Variables: https://youtu.be/TJtjo3FTBtE ►Bulk Data Processing Playlist https://www.youtube.com/playlist?list=PLL_LQvNX4xKwLcCuxb7-e8BVFxXFHCUPo ------------------------------------------------------------------------- ►Make sure you SUBSCRIBE and be the 1st one to see my videos! ------------------------------------------------------------------------- ►►►Find me on Social Media◄◄◄ Follow What I am up to as it happens on https://twitter.com/rebellionrider http://instagram.com/rebellionrider https://plus.google.com/+Rebellionrider http://in.linkedin.com/in/mannbhardwaj/ ___Facebook Official Page of Manish Sharma___ https://www.facebook.com/TheRebellionRider/ ___Facebook Official Page of RebellionRider.com___ https://www.facebook.com/RebellionRider.official/ You can also Email me at for E-mail address please check the About section Please please LIKE and SHARE my videos it makes me happy. Thanks for liking, commenting, sharing and watching more of our videos This is Manish from RebellionRider.com ♥ I LOVE ALL MY VIEWERS AND SUBSCRIBERS
Views: 4787 Manish Sharma
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: 69969 kudvenkat
ORACLE Analytic Functions for Data Warehousing 2/ Arabic
 
12:32
ORACLE Analytic Functions for Data Warehousing 2/ Arabic FIRST_VALUE last_VALUE row_number() --FIRST_VALUE and LAST_VALUE Analytic Functions select EMPLOYEE_ID, FIRST_NAME, HIRE_DATE from employees order by hire_date; select EMPLOYEE_ID, FIRST_NAME, HIRE_DATE , min(HIRE_DATE) over ( ) first_HIRE_DATE from employees select EMPLOYEE_ID, FIRST_NAME, HIRE_DATE , FIRST_VALUE(HIRE_DATE) over ( ) first_HIRE_DATE from employees order by hire_date; select EMPLOYEE_ID, FIRST_NAME, HIRE_DATE , max(HIRE_DATE ) over ( ) last_HIRE_DATE from employees select EMPLOYEE_ID, FIRST_NAME, HIRE_DATE , last_VALUE(HIRE_DATE ) over ( ) last_HIRE_DATE from employees order by HIRE_DATE drop table student_exam_result; CREATE TABLE student_exam_result (student_id number, exame_name varchar2(100), exame_try number, exame_results number ); insert into student_exam_result values(1,'calcluas',1,50); insert into student_exam_result values(1,'calcluas',2,66); insert into student_exam_result values(1,'calcluas',3,20); insert into student_exam_result values(2,'calcluas',1,66); insert into student_exam_result values(2,'calcluas',2,null); insert into student_exam_result values(2,'calcluas',3,7); insert into student_exam_result values(3,'calcluas',1,50); insert into student_exam_result values(3,'calcluas',2,98); insert into student_exam_result values(3,'calcluas',3,null); select * from student_exam_result select student_id, exame_name,exame_try,exame_results, last_value(exame_results) IGNORE NULLS over (PARTITION BY student_id, exame_name ) last_grade from student_exam_result order by student_id, exame_name, exame_try select student_id, exame_name,exame_try,exame_results, last_value(exame_results) IGNORE NULLS over (PARTITION BY student_id, exame_name ) last_grade, max(exame_results) over (PARTITION BY student_id, exame_name ) max_grade, avg(exame_results) over (PARTITION BY student_id, exame_name ) avg_grade from student_exam_result order by student_id, exame_name, exame_try ------------------------ select employee_id,first_name ,row_number() over(order by employee_id ) from employees select employee_id,first_name ,rownum from employees select employee_id,first_name ,department_id, row_number() over(partition by department_id order by employee_id ) from employees
Views: 556 khaled alkhudari
GROUP BY with Aggregate functions in SQL statement
 
25:01
Using "GROUP BY" clause with Aggregate functions in SQL statement Check the whole "SQL Server Basics" series here: https://www.youtube.com/playlist?list=PLU9JMEzjCv14f3cWDhubPaddxRvx1reKR Check my SQL blog at: http://sqlwithmanoj.com/ Check my SQL FB Page at: https://www.facebook.com/sqlwithmanoj
Views: 36640 SQL with Manoj
Oracle LISTAGG Function
 
04:57
https://www.databasestar.com/oracle-listagg/ The Oracle LISTAGG function allows you to aggregate or combine string values from multiple rows into a single row. For example, it turns this data: FIRST_NAME Adam Brad Carrie Into this data: FIRST_NAME Adam, Brad, Carrie The same data is shown, but it’s in one row and not multiple. It’s a valuable string manipulation function, and once you know what it is and how it works, you’ll be able to use it in your queries as needed. So what does it look like? The function is: LISTAGG ( measure_expr [, delimiter]) WITHIN GROUP (order_by_clause) [OVER query_partition_clause] These parameters are: measure_expr: This is a column or expression that you want to concatenate the values of. In the example above, it was the first_name column. Mandatory. delimiter: This is the character between each of the measure_expr values. Optional, the default is a comma. order_by_clause: This is the order that the values from the measure_expr are listed. Mandatory. query_partition_clause: This allows you to use LISTAGG as an analytic function, allowing you to show LISTAGG in groups for different rows. One thing to be aware of is that the output of the Oracle LISTAGG function is limited to 4,000 bytes. If you get more than this, you’ll receive an error. The most common way around this that I’ve seen is to write a custom function and use a CLOB. Watch the video to find out more and see some examples. For more information about the Oracle LISTAGG function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-listagg/
Views: 2230 Database Star
SQL DISTINCT Within a SELECT - Retrieving Unique Values
 
03:41
Jamie King of Neumont University demonstrating use of DISTINCT within a SQL SELECT command.
Views: 21138 Jamie King
SQL MAX() Function
 
01:23
The SQL MAX() is a function, and return the largest or highest value of a numeric table column from query result.
Views: 201 suresh babu
SQL Tutorial - Window Functions - Calculate Running Totals, Averages
 
13:06
Another fantastic SQL Tutorial brought to you by BeardedDev. If you are new to working with Window Functions check out this video: https://youtu.be/H6OTMoXjNiM In this video we learn how to use Window Functions to calculate running totals and running averages. This video teaches about Window Frames: Rows Range Preceding Current Row Following Window Frames are a filtered portion of a partition. Window Functions were first introduced in SQL Server 2005 but further enhancements and support was added in SQL Server 2012. Window Functions can only be included within SELECT or ORDER BY clauses. Functions Available: Aggregate - COUNT, SUM, MIN, MAX, AVG Ranking - ROW_NUMBER, RANK, DENSE_RANK, NTILE Offset - FIRST_VALUE, LAST_VALUE, LEAD, LAG Statistical - PERCENT_RANK, CUME_DIST, PERCENTILE_CONT, PERCENTILE_DIST Windows Functions also have FRAMES ROWS RANGE Window Functions are a powerful tool within SQL Server and I am excited to bring more videos and tutorials working with Window Functions in the future. SQL: SELECT Sales_Id , Sales_Date , Sales_Total , SUM(Sales_Total) OVER(ORDER BY Sales_Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Running Total] FROM dbo.Sales_2 WHERE Sales_Cust_Id = 3 ORDER BY Sales_Date SELECT Sales_Id , Sales_Date , Sales_Total , SUM(Sales_Total) OVER(ORDER BY Sales_Date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS [Running Total] FROM dbo.Sales_2 WHERE Sales_Cust_Id = 3 ORDER BY Sales_Date SELECT Sales_Id , Sales_Date , Sales_Total , SUM(Sales_Total) OVER(ORDER BY Sales_Date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS [Running Total] FROM dbo.Sales_2 WHERE Sales_Cust_Id = 3 ORDER BY Sales_Date SELECT Sales_Id , Sales_Date , Sales_Total , SUM(Sales_Total) OVER(ORDER BY Sales_Date ROWS UNBOUNDED PRECEDING) AS [Running Total] FROM dbo.Sales_2 WHERE Sales_Cust_Id = 3 ORDER BY Sales_Date SELECT Sales_Id , Sales_Date , Sales_Total , SUM(Sales_Total) OVER(ORDER BY Sales_Date ROWS UNBOUNDED PRECEDING) AS [Running Total] , CAST(AVG(Sales_Total) OVER(ORDER BY Sales_Date ROWS UNBOUNDED PRECEDING) AS DECIMAL(8, 2)) AS [Running Average] FROM dbo.Sales_2 WHERE Sales_Cust_Id = 3 ORDER BY Sales_Date
Views: 2126 BeardedDev
SQL Server join :- Inner join,Left join,Right join and full outer join
 
08:11
For more such videos visit http://www.questpond.com See our other Step by Step video series below :- Learn Angular tutorial for beginners https://tinyurl.com/ycd9j895 Learn MVC Core step by step :- http://tinyurl.com/y9jt3wkv Learn MSBI Step by Step in 32 hours:- https://goo.gl/TTpFZN Learn Xamarin Mobile Programming Step by Step :- https://goo.gl/WDVFuy Learn Design Pattern Step by Step in 8 hours:- https://goo.gl/eJdn0m Learn C# Step by Step in 100 hours :- https://goo.gl/FNlqn3 Learn Data structures & algorithm in 8 hours :-https://tinyurl.com/ybx29c5s Learn SQL Server Step by Step in 16 hours:- http://tinyurl.com/ja4zmwu Learn Javascript in 2 hours :- http://tinyurl.com/zkljbdl Learn SharePoint Step by Step in 8 hours:- https://goo.gl/XQKHeP Learn TypeScript in 45 Minutes :- https://goo.gl/oRkawI Learn webpack in 50 minutes:- https://goo.gl/ab7VJi Learn Visual Studio code in 10 steps for beginners:- https://tinyurl.com/lwgv8r8 Learn Tableau step by step :- https://tinyurl.com/kh6ojyo Preparing for C# / .NET interviews start here http://www.youtube.com/watch?v=gaDn-sVLj8Q In this video we will try to understand four important concepts Inner joins,Left join,Right join and full outer joins. We are also distributing a 100 page Ebook ".Sql Server Interview Question and Answers". If you want this ebook please share this video in your facebook/twitter/linkedin account and email us on [email protected] with the shared link and we will email you the PDF.
Views: 814181 Questpond
SQL Tutorial - Window Functions - Ranking
 
17:32
Another fantastic SQL Tutorial brought to you by BeardedDev. If you are new to working with Window Functions check out this video: https://youtu.be/H6OTMoXjNiM In this video we explore ranking functions available as part of Window Functions: ROW_NUMBER() RANK() DENSE_RANK() NTILE() This tutorial shows an example of when to use each ranking function and the differences between them. Window Functions were first introduced in SQL Server 2005 but further enhancements and support was added in SQL Server 2012. We look at the OVER clause and PARTITION BY. Window Functions can only be included within SELECT or ORDER BY clauses. Functions Available: Aggregate - COUNT, SUM, MIN, MAX, AVG Ranking - ROW_NUMBER, RANK, DENSE_RANK, NTILE Offset - FIRST_VALUE, LAST_VALUE, LEAD, LAG Statistical - PERCENT_RANK, CUME_DIST, PERCENTILE_CONT, PERCENTILE_DIST Windows Functions also have FRAMES ROWS RANGE Window Functions are a powerful tool within SQL Server and I am excited to bring more videos and tutorials working with Window Functions in the future. ROW_NUMBER - unique incrementing integers RANK - same rank for same values DENSE_RANK - same rank for same values NTILE - assigns tile numbers based on number of tiles requested SQL: SELECT Sales_Id , Sales_Total , ROW_NUMBER() OVER(ORDER BY Sales_Total DESC) AS rownum , RANK() OVER(ORDER BY Sales_Total DESC) AS rnk , DENSE_RANK() OVER(ORDER BY Sales_Total DESC) AS dense , NTILE(3) OVER(ORDER BY Sales_Total DESC) AS ntle FROM dbo.Sales_2 SELECT Sales_Id , NTILE(10) OVER(ORDER BY Sales_Total DESC) AS ntle FROM dbo.Sales_2
Views: 1686 BeardedDev
SQL Aggregate functions like SQL MAX, SQL MIN, SQL COUNT And AVERAGE function
 
09:22
http://learningsqlserver2008.com SQL Aggregate Functions are necessary to summarize numerical data in a SqL Server database. We have already looked at other transact SQl statements like SELECT and UPDATE SQL statements. Those typically are used to return rows of data that matches the criteria in the WHERE clause. SQL statements using aggregate functions like MIN, COUNT, AVG, SUM or MAX however return just one value after sql server has already applied the aggregation and summarized the data. In this manner MS 2008 aGgregate fUnctions perform their operation across multiple rows of data. For today's discussion we are going to look at AdventureWorks2008 sample database in SQL Server 2008. There are a number of SQL aggregate functions including SQL min, max, avg, sum, group by, count functionS
Views: 22236 baghul
Learn Oracle SQL : The Hierarchical Query Clause
 
11:19
http://bit.ly/oracle-sql-course | Get the complete course today. Learn Oracle SQL : The Hierarchical Query Clause: In this video, I show you how to use the Hierarchical Query Clause in Oracle SQL to build a basic hierarchical query. This is an excerpt of the course "The Ultimate Oracle SQL Course" which you can get at a heavily discounted price here: http://bit.ly/oracle-sql-course ================ You can find additional Oracle SQL related articles and educational content in my blog here: http://standout-dev.com/
Views: 5390 Oracle SQL
Sql Training Online - Sql Count Function
 
03:52
Learn about the Count function in the SQL Language. Visit http://www.SqlTrainingOnline.com for 30 more FREE videos. The Microsoft SQL Server 2012 SQL Count Function is used to give you a total number of records in your resultset or within a group of data. You would group the data using a group by clause in the SQL Statement. You can visit me at any of the following: SQL Training Online: http://www.sqltrainingonline.com Twitter: http://www.twitter.com/sql_by_joey Google+: https://plus.google.com/#100925239624117719658/posts LinkedIn: http://www.linkedin.com/in/joeyblue Facebook: http://www.facebook.com/sqltrainingonline
Views: 20628 Joey Blue
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: 162805 Joes2Pros SQL Trainings
SQL Tutorial - Window Functions
 
12:41
Another fantastic SQL Tutorial brought to you by BeardedDev. In this video we begin to explore Window Functions and their purpose within SQL Server. Window Functions are used for performing data analysis calculations and address an important need compared to the GROUP BY clause that we are able to return the underlying data in the same query. This video shows an example of the differences between the GROUP BY clause and Window Functions. Window Functions were first introduced in SQL Server 2005 but further enhancements and support was added in SQL Server 2012. We look at the OVER clause and PARTITION BY. Window Functions can only be included within SELECT or ORDER BY clauses. Functions Available: Aggregate - COUNT, SUM, MIN, MAX, AVG Offset - FIRST_VALUE, LAST_VALUE, LEAD, LAG Statistical - PERCENT_RANK, CUME_DIST, PERCENTILE_CONT, PERCENTILE_DIST Windows Functions also have FRAMES ROWS RANGE Window Functions are a powerful tool within SQL Server and I am excited to bring more videos and tutorials working with Window Functions in the future. Code: WITH CTE AS ( SELECT Sales_Id , SUM(Line_Total) AS Total FROM Sales_Details GROUP BY Sales_Id ) SELECT * FROM CTE AS A INNER JOIN Sales_Details AS B ON A.Sales_Id = B.Sales_Id SELECT Sales_Id , Sales_Date , Item , Price , Quantity , Line_Total , COUNT(Line_Total) OVER(PARTITION BY Sales_Id) AS Line_Count , SUM(Line_Total) OVER(PARTITION BY Sales_Id) AS Sales_Total , SUM(Line_Total) OVER(PARTITION BY Sales_Date) AS Daily_Total , SUM(Line_Total) OVER() AS Total FROM Sales_Details ORDER BY Sales_Total
Views: 7477 BeardedDev
SQL in Oracle 11g Tutorial #11: Aggregate Function(MAX,MIN,SUM,AVG,STDDEV,VARIANCE) in SQL
 
03:31
SQL in Oracle 11g Tutorial #11: Aggregate Function(MAX,MIN,SUM,AVG,STDDEV,VARIANCE) in SQL. Thanks for Watching my Channel “Learn TechToTech”. Please subscribe my channel for getting first updates after uploading video.Social Media pages of Channel are: 1. My Website : www.learnfromrakesh.com 2. My Twitter: https://twitter.com/LearnTechtotech 3. Facebook Page: https://www.facebook.com/LearnTechToTech 4. Blogger : https://learntechtotech.blogspot.in/ 5. Google+ : https://learntechtotech.blogspot.in/ 6. Pinterest : https://in.pinterest.com/LearnTechToTech/ 7. LinkedIn: https://www.linkedin.com/in/Learntechtotech/ Playlist of Different Technology: 1. Operating System : https://goo.gl/q6SfrW 2. Python Programming Language : https://goo.gl/L8b5dc 3. C Language : https://goo.gl/SwvDu9 4. C language for Placement: https://goo.gl/AaQBa4 5. Java: https://goo.gl/M8F2uy 6. MySql : https://goo.gl/vdJbHQ 7. Android Mobile Application Development: https://goo.gl/M6woaT 8. Kotlin Programming Language : https://goo.gl/GXE5cd 9. Go Programming: https://goo.gl/Ua3xYW 10. Internet of Things(IoT): https://goo.gl/f2afkY 11. Oracle 11g : https://goo.gl/zds8r2 12. C++ : https://goo.gl/C1psMT
Views: 61 Learn TechToTech
Oracle SQL: Use Analytical Functions
 
02:47
http://www.informit.com/store/oracle-sql-livelessons-video-training-downloadable-9780134275741?WT.mc_id=Social_YT Oracle SQL: Use Analytical Functions is an excerpt from Oracle SQL LiveLessons Video Training -- 6+ Hours of Video -- Oracle SQL LiveLessons Video Training covers the basics of the Oracle SQL programming language. This course covers both Oracle standard SQL and the ANSI approved SQL that Oracle supports. The focus of the course is to give students working knowledge of the Oracle SQL language and at the conclusion of this course, students should be able to query the Oracle database. Description In this LiveLessons video course, Oracle ACE Director Dan Hotka will teach you how to use the SQL query language, interpret business questions into SQL code, and how to use Where clauses sub-queries, complex table join conditions, and how to create table objects and populate tables with data. Students will also learn how to access Oracle from Microsoft products such as Excel. In addition, they will learn how analytical SQL can be used for business analysis. This course will use both Toad and SQL Developer for SQL examples. About the Instructor Dan Hotka is a training specialist and an Oracle ACE director who has more than 37 years in the computer industry and more than 31 years of experience with Oracle products. His experience with the Oracle RDBMS dates back to the Oracle V4.0 days. Dan enjoys sharing his knowledge of the Oracle RDBMS. Dan is well published with 12 Oracle books and well over 200 published articles. He is also the video author for Oracle SQL Performance Tuning for Developers LiveLessons and Oracle PL/SQL Programming Fundamentals. He is frequently published in Oracle trade journals, regularly blogs, and speaks at Oracle conferences and user groups around the world. Visit his website at www.DanHotka.com. Skill Level Beginner Learn How To Use the SQL query language Interpret business questions into SQL code Understand Oracle standard SQL and ANSI SQL Work with Where clauses, sub-queries, and complex table join conditions Create table objects and populate these tables with data Access Oracle from Microsoft products such as Excel Use analytical SQL, useful for business analysts Who Should Take This Course Students who are new to SQL and want to learn Oracle SQL Course Requirements Basic knowledge of what is a database, especially RDBMS and what is a computer programming language http://www.informit.com/store/oracle-sql-livelessons-video-training-downloadable-9780134275741?WT.mc_id=Social_YT
Views: 1181 LiveLessons
Part 8   SQL Query to find department with highest number of employees
 
04:04
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-8-sql-query-to-find-department.html Scenario asked in the SQL Server Interview Based on the above two tables write a SQL Query to get the name of the Department that has got the maximum number of Employees. To answer this question it will be helpful if you the knowledge of JOINS & GROUP BY in SQL Server. We discusses these in Parts 11 & 12 of SQL Server Tutorial video series. SQL query that retrieves the department name with maximum number of employees SELECT TOP 1 DepartmentName FROM Employees JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID GROUP BY DepartmentName ORDER BY COUNT(*) DESC Scenario asked in the SQL Server Interview Based on the above two tables write a SQL Query to get the name of the Department that has got the maximum number of Employees. To answer this question it will be helpful if you the knowledge of JOINS & GROUP BY in SQL Server. We discusses these in Parts 11 & 12 of SQL Server Tutorial video series. SQL query that retrieves the department name with maximum number of employees SELECT TOP 1 DepartmentName FROM Employees JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID GROUP BY DepartmentName ORDER BY COUNT(*) DESC
Views: 112704 kudvenkat
Oracle Sql :  Lecture 40: GROUP BY with multiple columns
 
05:01
Oracle Sql: GROUP BY with multiple columns https://www.youtube.com/channel/UCnBpiBIOtRgMsk4G7Ri1jbQ
Views: 1998 Oracle SQL
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: 179991 kudvenkat
SQL Tutorial - 51: Aggregate Functions (Part-1)
 
04:48
In this tutorial we'll learn to use the COUNT, MAX & MIN aggregate functions to determine the number of records returned in a result set, the maximum value of a field and the least value of a field respectively.
Views: 21345 The Bad Tutorials
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: 467 Tech Query Pond
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: 26129 Tech Jago
SQL Tutorial - Window Functions - Ranking with Group By
 
07:14
Another fantastic SQL Tutorial brought to you by BeardedDev. If you are new to working with Windows Functions check out this video: https://youtu.be/H6OTMoXjNiM If you want to find out more about the Group By clause and working with aggregate functions check out this video: https://youtu.be/TYD6gWP3jBg If you are interested in finding out more about ranking functions available in SQL Server check out this video: https://youtu.be/xL7BtCT7ix4 In this tutorial we explore ranking functions as part of Window Functions using the Group By clause within SQL Server. In this example we go through how to rank customers based on the sum of the orders they have placed with us. The window function example also covers the difference between using Rank and Dense_Rank. SQL SELECT Sales_Cust_Id , SUM(Sales_Total) AS Total FROM dbo.Sales_2 GROUP BY Sales_Cust_Id ORDER BY Total DESC SELECT Sales_Cust_Id , SUM(Sales_Total) AS Total , RANK() OVER(ORDER BY SUM(Sales_Total) DESC) AS rnk , DENSE_RANK() OVER(ORDER BY SUM(Sales_Total) DESC) AS dnse FROM dbo.Sales_2 GROUP BY Sales_Cust_Id ORDER BY rnk
Views: 793 BeardedDev
Difference between where and having in sql server
 
06:36
sql server where vs having sql server group by where having group by having sql server In this vide we will discuss the difference between where and having caluses in sql server. Let us understand the difference with an example. To calculate total sales by product, we would write a GROUP BY query as shown below SELECT Product, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY Product Now if we want to find only those products where the total sales amount is greater than $1000, we will use HAVING clause to filter products SELECT Product, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY Product HAVING SUM(SaleAmount) ] 1000 If we use WHERE clause instead of HAVING clause, we will get a syntax error. This is because the WHERE clause doesn’t work with aggregate functions like sum, min, max, avg, etc. SELECT Product, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY Product WHERE SUM(SaleAmount) ] 1000 So in short, the difference is WHERE clause cannot be used with aggregates where as HAVING can. However, there are other differences as well that we need to keep in mind when using WHERE and HAVING clauses. WHERE clause filters rows before aggregate calculations are performed where as HAVING clause filters rows after aggregate calculations are performed. Let us understand this with an example. Total sales of iPhone and Speakers can be calculated by using either WHERE or HAVING clause Calculate Total sales of iPhone and Speakers using WHERE clause : In this example the WHERE clause retrieves only iPhone and Speaker products and then performs the sum. SELECT Product, SUM(SaleAmount) AS TotalSales FROM Sales WHERE Product in ('iPhone', 'Speakers') GROUP BY Product Calculate Total sales of iPhone and Speakers using HAVING clause : This example retrieves all rows from Sales table, performs the sum and then removes all products except iPhone and Speakers. SELECT Product, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY Product HAVING Product in ('iPhone', 'Speakers') So from a performance standpoint, HAVING is slower than WHERE and should be avoided when possible. Another difference is WHERE comes before GROUP BY and HAVING comes after GROUP BY. Difference between WHERE and Having 1. WHERE clause cannot be used with aggregates where as HAVING can. This means WHERE clause is used for filtering individual rows where as HAVING clause is used to filter groups. 2. WHERE comes before GROUP BY. This means WHERE clause filters rows before aggregate calculations are performed. HAVING comes after GROUP BY. This means HAVING clause filters rows after aggregate calculations are performed. So from a performance standpoint, HAVING is slower than WHERE and should be avoided when possible. 3. WHERE and HAVING can be used together in a SELECT query. In this case WHERE clause is applied first to filter individual rows. The rows are then grouped and aggregate calculations are performed, and then the HAVING clause filters the groups. Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/difference-between-where-and-having-in.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/difference-between-where-and-having-in_15.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: 63894 kudvenkat
SQL Server Aggregate functions and the OVER clause
 
57:32
Follow the Books: http://amzn.to/1RjjJCf SQL Server Training for the Microsoft 70-461 exam. This session includes Aggregate functions and the windowing functions (OVER, PARTITION BY). Slides and scripts available at: www.AaronBuma.com
Views: 3749 Aaron Buma
RANK Aggregate Function in SQL with an Example
 
01:40
RANK() Aggregate Function: RANK calculates the rank of a value in a group of values. The return type is NUMBER. Query used in video: select RANK(5000) WITHIN GROUP (ORDER BY sal) "Rank" from emp;
Views: 1035 WingsOfTechnology
SQL vs NoSQL or MySQL vs MongoDB
 
21:30
SQL or NoSQL? MySQL vs MongoDB? Which database is better? Which one should you use? Understanding the differences is important, so let's take a closer look! Don't miss the article on this video: https://academind.com/learn/web-dev/sql-vs-nosql/ Take the full MongoDB course for just $13.99: https://www.udemy.com/mongodb-the-complete-developers-guide/?couponCode=ACAD_YT Want to use MongoDB in a real project? Take my MEAN (Mongo, Express, Angular, Node) course: https://www.udemy.com/angular-2-and-nodejs-the-practical-guide/?couponCode=ACAD_M ---------- More on SQL Horizontal Scaling: https://stackoverflow.com/questions/27157227/can-relational-database-scale-horizontally Want to learn something totally different? Check out all other courses: https://academind.com/learn/our-courses ---------- • You can follow Max on Twitter (@maxedapps). • And you should of course also follow @academind_real. • You can also find us on Facebook.(https://www.facebook.com/academindchannel/) • Or visit our Website (https://www.academind.com) and subscribe to our newsletter! See you in the videos! ---------- Academind is your source for online education in the areas of web development, frontend web development, backend web development, programming, coding and data science! No matter if you are looking for a tutorial, a course, a crash course, an introduction, an online tutorial or any related video, we try our best to offer you the content you are looking for. Our topics include Angular, React, Vue, Html, CSS, JavaScript, TypeScript, Redux, Nuxt.js, RxJs, Bootstrap, Laravel, Node.js, Progressive Web Apps (PWA), Ionic, React Native, Regular Expressions (RegEx), Stencil, Power BI, Amazon Web Services (AWS), Firebase or other topics, make sure to have a look at this channel or at academind.com to find the learning resource of your choice!
Views: 135625 Academind
Running Sum using Oracle
 
08:35
Running Sum using Oracle
Views: 1024 CONNECT TO LEARN
SQL Scenario 1
 
03:47
Scripts to test CREATE TABLE PEOPLE_DETAILS ( NAME VARCHAR(100), OCCUPATION VARCHAR(100) ); INSERT INTO PEOPLE_DETAILS VALUES ('SAMANTHA' , 'DOCTOR'); INSERT INTO PEOPLE_DETAILS VALUES ('JULIA' , 'ACTOR'); INSERT INTO PEOPLE_DETAILS VALUES ('MARIA' , 'ACTOR'); INSERT INTO PEOPLE_DETAILS VALUES ('MEERA' , 'SINGER'); INSERT INTO PEOPLE_DETAILS VALUES ('ASHLEY' , 'PROFESSOR'); INSERT INTO PEOPLE_DETAILS VALUES ('KETTY' , 'PROFESSOR'); INSERT INTO PEOPLE_DETAILS VALUES ('CHRISTEEN' , 'PROFESSOR'); INSERT INTO PEOPLE_DETAILS VALUES ('JANE' , 'ACTOR'); INSERT INTO PEOPLE_DETAILS VALUES ('JENNY' , 'DOCTOR'); INSERT INTO PEOPLE_DETAILS VALUES ('PRIYA' , 'SINGER'); COMMIT; SELECT * FROM PEOPLE_DETAILS; SELECT CASE WHEN OCCUPATION = 'DOCTOR' THEN NAME END AS NAME1, CASE WHEN OCCUPATION = 'PROFESSOR' THEN NAME END AS NAME2, CASE WHEN OCCUPATION = 'SINGER' THEN NAME END AS NAME3, CASE WHEN OCCUPATION = 'ACTOR' THEN NAME END AS NAME4, RANK() OVER (PARTITION BY OCCUPATION ORDER BY NAME)RN FROM PEOPLE_DETAILS; SELECT MAX(NAME1),MAX(NAME2),MAX(NAME3),MAX(NAME4) FROM ( SELECT CASE WHEN OCCUPATION = 'DOCTOR' THEN NAME END AS NAME1, CASE WHEN OCCUPATION = 'PROFESSOR' THEN NAME END AS NAME2, CASE WHEN OCCUPATION = 'SINGER' THEN NAME END AS NAME3, CASE WHEN OCCUPATION = 'ACTOR' THEN NAME END AS NAME4, RANK() OVER (PARTITION BY OCCUPATION ORDER BY NAME)RN FROM PEOPLE_DETAILS) GROUP BY RN ORDER BY RN;
Views: 5564 raj raj
Aggregate Functions in SQL Server
 
47:37
Website:https://www.eclasess.com sum(),max(),min(),avg(),count() functions in sql server Group by ,having,order by clauses
SQL: Analytical Functions
 
09:20
In this tutorial, you'll learn how to use the analytical functions..
Views: 35553 radhikaravikumar
MIN, MAX, COUNT, AVG, SUM data pada SQL
 
03:34
menggunakan syntax MIN, MAX, COUNT, AVG, SUM data pada SQL. Semoga Bermanfaat. http://qrimu.com/
Views: 95 Qrimu Official
The Having Clause in SQL
 
01:15
Follow this easy step by step tutorial to learn how to use "Having" clause in SQL and see how we can give multiple conditions in one query. Don't forget to check out our site http://howtech.tv/ for more free how-to videos! http://youtube.com/ithowtovids - our feed http://www.facebook.com/howtechtv - join us on facebook https://plus.google.com/103440382717658277879 - our group in Google+ In SQL, Having clause is used to filter the records according to our requirements. Basically this clause applies the condition on a group which is created by using the "Group by" keyword. In this tutorial we will learn to use the HAVING clause in SQL Step 1- Employee Table First of all, fetch all records of employee table For that write select * from EMP and execute it. Just consider the employee table below. Suppose, if we want to see which department is getting the salary greater than $2500, we will write a query containing Having clause. Step 2-Preparing Query Start by using the "select" keyword, followed by the name of the column is required to be filtered, which in this case is the Department Number. After that, we will use the Max keyword which will return the highest value. In the parenthesis, we will enter the column name, from where we want to retrieve the highest value. Over here, we will get the data from the Salary column. Now we need to mention from which table the data has to be retrieved from. For that we will use the "from" keyword followed by the name of the table which is EMP in our case. Since we want to group the output according to the Department number over here, we will use the "group by" clause over here followed by the column name which is DEPTNO. After that, we will use SQL Having clause to filter out the maximum salaries. Therefore after the Having clause, we will input a condition, that the max salary should be greater than $2500. The complete query would be : select deptno,MAX(sal) from EMP group by DEPTNO having MAX(sal) 2500 Step 3- Execution Let us execute the above written query. We will observe the output is according to the conditions we provided in the query, that is, it shows the salaries greater than 2450 and these are also grouped with the Department Number. And that is how we can use Having clause in SQL.
Row Number Vs Rank Vs Dense Rank
 
07:04
This video shows the difference between row_number, rank and dense_rank analytical functions in oracle database with appropriate easy to understand examples, along with it the video demonstrates the different output obtained when using either of the three functions. Apart from simple explanation of the function we also show how partition by and order by clause can be used with the functions for tacking day to day scenarios. If you want more such videos of exciting and amazing 'difference between' concepts, check out the links below : union and union all : https://youtu.be/n9FqQOd8liY char and varchar2 : https://youtu.be/039qzwjWf4k replace and translate : https://youtu.be/HKYF77BGzOE procedure and function : https://youtu.be/q3LmOenL120 in and exists : https://youtu.be/REX4IjRYlFw delete and truncate : https://youtu.be/u76wMm2byXo %type and %rowtype : https://youtu.be/MlLUFeZ_3eM
Views: 2348 Kishan Mashru
SQL Tip #6: Simple SELECT statement using aggregate functions MIN, MAX, AVG
 
04:06
In this example we demonstrate how to create a MS SQL Server SELECT statement that uses the functions MIN, MAX, AVG, SUM. These are known as aggregate functions.
Views: 660 ITCorpsTV
RANK Analytic Function in SQL with an Example
 
05:37
RANK() Analytic Function: RANK calculates the rank of a value in a group of values. The return type is NUMBER. If two or more rows tie for a rank, each tied rows receives the same rank. For example, if the two top employees have the same salary value, they are both ranked one. The employee with the next highest salary is ranked number three, because there are two rows that are ranked higher. Therefore, the RANK function does not always return consecutive integers. The sort order that is used for the whole query determines the order in which the rows appear in a result set. PARTITION BY clause: Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition. ORDER BY clause: Defines the logical order of the rows within each partition of the result set. That is, it specifies the logical order in which the window functioncalculation is performed. Query used in Video: select ename,sal,deptno,RANK() OVER(PARTITION BY deptno ORDER BY sal) from emp; Video Explanation: See here 6th row assigned the value '6',not '5' because 4th and 5th rows have same value for salary. Its the same for 12 and 13 rows also. we did not mention anything on OVER clause except ORDER BY. If you want apply RANK() by department wise, we can use PARTITION BY clause. PARTION BY works like GROUP BY in aggregates!
Views: 6565 WingsOfTechnology
SQL Server video :- Explain aggregate functions,group by clause and having keyword ?
 
09:25
For more such videos visit http://www.questpond.com See our other Step by Step video series below :- Learn Angular tutorial for beginners https://tinyurl.com/ycd9j895 Learn MVC Core step by step :- http://tinyurl.com/y9jt3wkv Learn MSBI Step by Step in 32 hours:- https://goo.gl/TTpFZN Learn Xamarin Mobile Programming Step by Step :- https://goo.gl/WDVFuy Learn Design Pattern Step by Step in 8 hours:- https://goo.gl/eJdn0m Learn C# Step by Step in 100 hours :- https://goo.gl/FNlqn3 Learn Data structures & algorithm in 8 hours :-https://tinyurl.com/ybx29c5s Learn SQL Server Step by Step in 16 hours:- http://tinyurl.com/ja4zmwu Learn Javascript in 2 hours :- http://tinyurl.com/zkljbdl Learn SharePoint Step by Step in 8 hours:- https://goo.gl/XQKHeP Learn TypeScript in 45 Minutes :- https://goo.gl/oRkawI Learn webpack in 50 minutes:- https://goo.gl/ab7VJi Learn Visual Studio code in 10 steps for beginners:- https://tinyurl.com/lwgv8r8 Learn Tableau step by step :- https://tinyurl.com/kh6ojyo Preparing for C# / .NET interviews start here http://www.youtube.com/watch?v=gaDn-sVLj8Q visit for 600+ videos @ http://www.questpond.com In this video we will try to understand aggregate function,group by clause and having keyword. This is also one of the favorite SQL Server interview questions as well. We are also distributing a 100 page Ebook ".NET Interview Question and Answers". If you want this ebook please share this video in your facebook/twitter/linkedin account and email us on [email protected] with the shared link and we will email you the PDF.
Views: 172073 Questpond
Oracle Analytic Functions
 
09:33
Learn Advanced Oracle Analytic Functions. SQL is a language that allows you to perform queries to answer a wide range of questions about your data. While standard SQL is quite powerful, there are many questions that it cannot answer at all or that it can only answer with complex and inefficient SQL workarounds. Questions about running totals, percent of total, and ranking within a group fall into this area. Also, more complex issues such as Top-N within a group and aggregates over a range also fall in this arena. These questions can be easily answered via a new class of SQL statements called Analytic Functions. This presentation will show you how to use Oracle's Analytic Function extensions to SQL to answer these questions simply and efficiently.
Views: 4841 SkillBuilders
SQL Inner Join Multiple Tables with SUM Tutorial - SQL 2008/2012/2016/2017
 
18:03
Use inner joins with multiple tables to create reports. Uses aliases, and the SUM aggregate function to demonstrate the flexibility of the join statement. Get the SQL script to create the database and run the exercies in the video here: https://www.qewbi.com/sql/scripts/qewbijoins.zip
Views: 130348 Edward Kench
How to find Top Three Salaries from employee table ?
 
07:27
Hello guys in this video i m showing to you how to retrieve the top most salary from table. #TopThreeSalaries Oracle database Unbeatable,Unbreakable Platform..
Views: 7031 Oracle World
SQL. SUM() OVER. Сумма накопленным итогом и общая сумма по полю.
 
09:54
В видео рассказывается, как посчитать сумму накопленным итогом, а так же общую сумму по полю. P.S. задавайте вопросы в комментариях и высказывайте свое мнение и пожелания
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: 8487 Disha Saxena