Home
Search results “Oracle pivot for subquery”
PIVOT Tutorial - generating dynamic column with PIVOT Interview Question SQL
 
14:14
In this lecture you will learn about PIVOT operator and using it to generate dynamic columns.PIVOT is very use full operator to convert values into columns. PIVOT: •Convert your output from (values)vertical order to horizontal(making columns) one Topic to cover: •PIVOT example •PIVOT syntax •Interview Question •PIVOT on northwind •Dynamic PIVOT query http://www.techsapphire.in/index/pivot_sql_lesson_with_generating_dynamic_columns_with_pivot/0-158
Views: 30649 techsapphire
How to use the Oracle SQL PIVOT Clause
 
11:46
PIVOT allows you to "flip a table on its side", i.e. Columns to Rows / Rows to Columns. Previously we did this with DECODE or CASE. As of Oracle 11g, Oracle Database includes the PIVOT clause (and UNPIVOT). Oracle expert Geoff Wiland from SkillBuilders will demonstrate PIVOT, UNPIVOT, including the use of aggregate functions.
Views: 11006 SkillBuilders
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: 16857 Kishan Mashru
Using Pivot clause in Oracle  PLSQL.
 
08:51
Pivot is a wonderful feature in Oracle which helps you your aggregate based sql results to be transformed from row to column where you can put your group based columns to display it's aggregate value into columns.
Views: 3890 Subhroneel Ganguly
How to use Pivot and Unpivot? (how to change rows to column and column to rows)
 
10:47
This is a video tutorial on using pivot and unpivot operators to change rows to column and columns to rows when needed, the video also demonstrates a way to change from rows to column without using the pivot operator which can be helpful if you are using and older version of oracle or some other database without the feature of pivot and unpivot. Both the operators are explained with an example for better understanding
Views: 7867 Kishan Mashru
Função Oracle PIVOT
 
08:24
Acesse o desafio: http://novidades.aprendaplsql.com/15-dias-de-funcoes-oracle A função PIVOT cria uma matriz de informações conforme as necessidades do usuário. SELECT e.manager_id, e.department_id, count(e.employee_id) FROM hr.employees e GROUP BY e.manager_id, e.department_id ORDER BY e.manager_id, e.department_id; SELECT * FROM (SELECT manager_id, department_id, employee_id FROM hr.employees ) PIVOT ( COUNT(employee_id) FOR department_id IN (10, 20, 30, 50, 60, 80, 90) ) ORDER BY manager_id SELECT e.manager_id, d.department_name, count(e.employee_id) FROM hr.employees e INNER JOIN hr.departments d ON e.department_id = d.department_id GROUP BY e.manager_id, d.department_name ORDER BY e.manager_id, d.department_name; WITH valores_pivot AS ( SELECT e.manager_id manager_id, d.department_name department_name, e.employee_id qtd_empregados FROM hr.employees e INNER JOIN hr.departments d ON e.department_id = d.department_id ) SELECT * FROM valores_pivot PIVOT ( COUNT(qtd_empregados) FOR department_name IN ('IT', 'Administration', 'Executive', 'Marketing', 'Purchasing', 'Sales', 'Shipping') ) ORDER BY manager_id; Funções Oracle: https://www.youtube.com/playlist?list=PLMaqczuL07LkjrX6Rvx6TdNM-1nbUKG8p
Views: 2429 Aprenda PL/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: 198957 kudvenkat
Hierarchical Queries (Oracle)
 
03:33
Hierarchical Queries in Oracle, Using CONNECT BY PRIOR and START WITH clause. Visual Example using a practical scenario.
Views: 5615 CodeSmith 001
What to choose for performance   SubQuery or Joins   Part 62
 
12:26
Text version of the video http://csharp-video-tutorials.blogspot.com/2013/01/what-to-choose-for-performance.html Slides http://csharp-video-tutorials.blogspot.com/2013/09/part-62-what-to-choose-for-performance.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 What to choose for performance - SubQuery or Joins According to MSDN, in sql server, in most cases, there is usually no performance difference between queries that uses sub-queries and equivalent queries using joins. For example, on my machine I have 400,000 records in tblProducts table 600,000 records in tblProductSales tables The following query, returns, the list of products that we have sold atleast once. This query is formed using sub-queries. When I execute this query I get 306,199 rows in 6 seconds Select Id, Name, Description from tblProducts where ID IN ( Select ProductId from tblProductSales ) At this stage please clean the query and execution plan cache using the following T-SQL command. CHECKPOINT; GO DBCC DROPCLEANBUFFERS; Go DBCC FREEPROCCACHE; GO Now, run the query that is formed using joins. Notice that I get the exact same 306,199 rows in 6 seconds. Select distinct tblProducts.Id, Name, Description from tblProducts inner join tblProductSales on tblProducts.Id = tblProductSales.ProductId Please Note: I have used automated sql script to insert huge amounts of this random data. Please watch Part 61 of SQL Server tutorial, in which we have discussed about this automated script. According to MSDN, in some cases where existence must be checked, a join produces better performance. Otherwise, the nested query must be processed for each result of the outer query. In such cases, a join approach would yield better results. The following query returns the products that we have not sold atleast once. This query is formed using sub-queries. When I execute this query I get 93,801 rows in 3 seconds Select Id, Name, [Description] from tblProducts where Not Exists(Select * from tblProductSales where ProductId = tblProducts.Id) When I execute the below equivalent query, that uses joins, I get the exact same 93,801 rows in 3 seconds. Select tblProducts.Id, Name, [Description] from tblProducts left join tblProductSales on tblProducts.Id = tblProductSales.ProductId where tblProductSales.ProductId IS NULL In general joins work faster than sub-queries, but in reality it all depends on the execution plan that is generated by SQL Server. It does not matter how we have written the query, SQL Server will always transform it on an execution plan. If sql server generates the same plan from both queries, we will get the same result. I would say, rather than going by theory, turn on client statistics and execution plan to see the performance of each option, and then make a decision. In a later video session we will discuss about client statistics and execution plans in detail.
Views: 89864 kudvenkat
SQL AND PLSQL INTERVIEW 12-PIVOT FUNCTION(change rows data to column data?
 
03:21
If You have Any Queries Comment Me in Comment Box or MY WHATSUP NUMBER 08508598428. Subscribe this Youtube Channel for Upcomming Videos. join My facebook Sql and PLSQL Discussion Page https://www.facebook.com/Oracle-SQL-PLSQL-Practical-Discussion-334342290310499/
Views: 259 Manikandan R
SQL Server  Generating Cross Tab Tables (Pivot)
 
07:39
6. Generating Cross Tab Tables (Pivot)
Views: 17881 Eagle
PIVOT and UNPIVOT Operators in Oracle Database 11g
 
03:03
A quick example of using the PIVOT and UNPIVOT operators introduced in Oracle Database 11g. For more information see: https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1 Website: https://oracle-base.com Blog: https://oracle-base.com/blog Twitter: https://twitter.com/oraclebase Cameo by Moans Norgaard Blog: http://wedonotuse.blogspot.co.uk/ Twitter: https://twitter.com/mnorgaard Cameo appearances are for fun, not an endorsement of the content of this video.
Views: 9755 ORACLE-BASE.com
Oracle SQL Subqueries
 
17:13
This lecture is part of a free, online course entitled "Oracle SQL - From Beginner to Pro." To check out the entire course go to www.digitalmiddleage.com
Views: 13 Rick Phillips
Pivot in sql server 2008   Part 54
 
12:25
In this video we will learn about pivot operator. Pivot is a sql server operator that can be used to turn unique values from one column, into multiple columns in the output, there by effectively rotating a table. Text version of the video http://csharp-video-tutorials.blogspot.com/2012/10/pivot-operator-in-sql-server-part-54.html Slides http://csharp-video-tutorials.blogspot.com/2013/09/part-54-pivot-in-sql-server-2008.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: 189929 kudvenkat
SQL Dynamic Pivot Example Adventure Works
 
05:16
TSQL Dynamic Pivot example in adventure works. Pivot Example: https://www.youtube.com/watch?v=HmXv76kh78M Concatenate all product ids: https://www.youtube.com/watch?v=1kfp0s-lc3M
Views: 1747 Blake B
Pivot & Unpivot: Databases for Developers #14
 
07:24
Sometimes you want to convert rows to columns. Or convert columns back into rows. Luckily SQL has two clauses to help you do this: PIVOT & UNPIVOT Using these you can easily transform your rows to columns and back again. Want more? Take the course: https://devgym.oracle.com/pls/apex/dg/class/databases-for-developers-next-level.html Read the blog post: https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot Read the docs: PIVOT: https://docs.oracle.com/en/database/oracle/oracle-database/18/dwhsg/sql-analysis-reporting-data-warehouses.html#GUID-05BB22CD-0F53-4C90-AE84-CE3F88DBD591 UNPIVOT: https://docs.oracle.com/en/database/oracle/oracle-database/18/dwhsg/sql-analysis-reporting-data-warehouses.html#GUID-B0AC9284-DDC1-4EF7-84CD-A27DF7F8DD57 Need help with SQL? Ask us over on AskTOM: https://asktom.oracle.com Twitter: https://twitter.com/ChrisRSaxon Daily SQL Twitter tips: https://twitter.com/SQLDaily All Things SQL blog: https://blogs.oracle.com/sql/ ============================ The Magic of SQL with Chris Saxon Copyright © 2015 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.
Views: 1293 The Magic of SQL
Correlated subquery in sql   Part 60
 
07:59
In this video we will discuss about Corelated Subqueries In Part 59, we discussed about 2 examples that uses subqueries. Please watch Part 59, before proceeding with this video. We will be using the same tables and queries from Part 59. In the example below, sub query is executed first and only once. The sub query resutls are then used by the outer query. In a non-corelated subquery, the subquery can be executed independently of the outer query. Select [Id], [Name], [Description] from tblProducts where Id not in (Select Distinct ProductId from tblProductSales) If the subquery depends on the outer query for its values, then that sub query is called as a correlated subquery. In the where clause of the subquery below, "ProductId" column get it's value from tblProducts table that is present in the outer query. So, here the subquery is dependent on the outer query for it's value, hence this subquery is a correlated subquery. Correlated subqueries get executed, once for every row that is selected by the outer query. corelated subquery, cannot be executed independently of the outer query. Select [Name], (Select SUM(QuantitySold) from tblProductSales where ProductId = tblProducts.Id) as TotalQuantity from tblProducts order by Name Text version of the video http://csharp-video-tutorials.blogspot.com/2013/01/correlated-subquery-in-sql-part-60.html Slides http://csharp-video-tutorials.blogspot.com/2013/09/part-60-co-related-subqueries.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: 131227 kudvenkat
109- Oracle SQL 12c: Manipulating Data- Pivoting Insert
 
13:43
تعلم اوراكل حتى الاحتراف EXAM 1Z0-061 EXAM 1Z0-071
Views: 1202 khaled alkhudari
SQL PIVOT Tutorial
 
02:04
SQL PIVOT Tutorial - Oracle 11g
Views: 2394 Niki Singh
Transpose Rows into Columns in Oracle Database
 
11:36
Transpose rows to columns in Oracle , rows into columns in Oracle
Views: 285 Adam Tech
SQL Pivot Example Adventure Works
 
12:20
SQL Pivot example in adventure works.
Views: 688 Blake B
SQL Server training :- How to implement Pivot?
 
10:28
For more such videos visit http://www.questpond.com See our other Step by Step video series below :- Learn SQL Server Step by Step in 16 hours:- http://tinyurl.com/ja4zmwu Learn Angular tutorial for beginners https://tinyurl.com/ycd9j895 Learn MVC Core step by step :- http://tinyurl.com/y9jt3wkv Learn MVC 5 Step by Step in 16 hours:- https://goo.gl/dmdakg 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 :- https://goo.gl/FNlqn3 Learn Data structures & algorithm in 8 hours :-https://tinyurl.com/ybx29c5s 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 In this video we will try to implement Simple Pivot function in SQL Server. So we will take a simple customer and product data and pivot them on the basis of customer name and product name. FYI :- This is also one of those most asked question in SQL Server interviews. We are also distributing a 100 page Ebook "Sql Server Interview Questions". 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.
SQL Server interview questions :- What are Sub Query and Co-related Queries ?
 
09:39
For more such videos visit http://www.questpond.com For more such videos subscribe https://www.youtube.com/questpondvideos?sub_confirmation=1 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 what are SQL server sub queries , co-related queries and how they differ from each other. 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: 270598 Questpond
Oracle SQL Tutorial : With Clause In Oracle
 
12:51
Oracle SQL Tutorial This oracle video will show you how to use With Clause In Oracle PL SQL. with in pl sql oracle tutorial for beginners With clause is used to simplify the complex sql queries. By using the with clause you can get faster query performance. You can simplify the complex subquery logic. WITH clause is called subquery factoring. For more tutorial please visit #techquerypond https://techquerypond.com https://techquerypond.wordpress.com https://twitter.com/techquerypond with query in oracle with query in oracle
Views: 8922 Tech Query Pond
DYNAMIC SQL WITH EXECUTE IMMEDIATE IN ORACLE PL/SQL
 
19:41
This video is out of audio and video sync, please find the Synced video uploaded on https://youtu.be/8EUQyDeWSq4 Always thought we cannot have a create statement inside a pl/sql block, well here is the answer for that, and along with that learn how to generate sql statement on the go. This video demonstrates the use of Dynamic SQL with the help of Execute Immediate. The video also demonstrates the INTO and USING clause of the execute immediate statement.
Views: 3352 Kishan Mashru
Intermediate To Advanced SQL Queries #2: Pivoting Tables
 
19:54
In this tutorial, we will cover a special case of pivoting tables in SQL using MySQL as a DBMS. Think of pivoting tables as selecting unique values and assigning them to their own columns. The solution code can be found on my website here: http://programmingforfinance.com/2017/10/pivoting-tables-with-mysql/
Views: 3404 codebliss
Hierarchical Queries in Oracle (CONNECT BY)
 
03:05
This video demonstrates one of the ways to query hierarchical data from Oracle. For more information see: https://oracle-base.com/articles/misc/hierarchical-queries Website: https://oracle-base.com Blog: https://oracle-base.com/blog Twitter: https://twitter.com/oraclebase Cameo by Arjen Visser Blog: http://blog.dbvisit.com/ Twitter: https://twitter.com/dbvisit Website: http://www.dbvisit.com/ Cameo appearances are for fun, not an endorsement of the content of this video.
Views: 27967 ORACLE-BASE.com
Pivot In SQL Server Explain in Hindi
 
10:31
PIVOT is used when we want to transfer data from row level to column level
Views: 160 Hindi Life
BAQ Subqueries - Presented 10/8/14
 
11:53
Come learn how to put queries within queries. This makes the queries easier to work with as well as more efficient.
Views: 4380 CodaBears, Inc.
Merge Statement in Oracle SQL with Example (UPDATE/DELETE or INSERT)
 
15:04
This video demonstrates how to write a Merge Statement in oracle sql, how it is useful in doing a conditional update or delete or both of them when the condition between the target table and source table is evaluated to true, and how we can insert the record into the target table when condition is evaluated to false. The video also shows the use of UPDATE, DELETE and INSERT with Merge, and also the various possible ways to use a Merge statement!!!
Views: 9555 Kishan Mashru
MS SQL case 3 pivot
 
06:47
script: "case pivot.sql" , line 105 부터 https://github.com/wonhyukc/mySQL/ 연세대학교 데분설 강의 녹화 2014 11 16 11 06 34 635
Views: 1063 정원혁
Transposing data in a report Flip rows to columns in Oracle SQL
 
04:06
In this training video we will learn load data in a report, Flip rows to columns in Oracle SQL
Views: 254 Spod ssppoodd
006 Connect By clause
 
05:09
Views: 5447 Kartik Mundhra
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: 90155 kudvenkat
TSQL: How To Use CASE WHEN Statements
 
14:15
How to use CASE WHEN statements in SQL Server.
Views: 29665 SQLInSix Minutes
Oracle DECODE Function
 
04:31
https://www.databasestar.com/oracle-decode-function/ The Oracle DECODE function lets you perform IF-THEN-ELSE functionality in your queries. It’s similar to a CASE statement. The syntax of the DECODE function is: DECODE ( expression, search, result [, search, result]... [,default] ) The parameters of the Oracle DECODE function are: - expression (mandatory): This is the value to compare. - search (mandatory): This is the value to compare against the expression. - result (mandatory): This is the return value if the search value matches the expression value. There can be multiple combinations of search and result values, and the result value is attached to the previous search value. - default (optional): If none of the search values match, then this value is returned. If this is not provided, the DECODE function will return NULL if no matches are found. If you compare this to an IF-THEN-ELSE statement, it would look like this: IF (expression = search) THEN result [ELSE IF (expression = search) THEN result] ELSE default END IF The parameters can be one of many different data types, and the return type is calculated from them. Earlier I mentioned it’s similar to CASE. So why would you use one and not the other? - DECODE is an older function, and CASE was introduced as a replacement for DECODE. - CASE offers more flexibility then DECODE - CASE is also easier to read and debug (in my opinion) The performance of these functions is the same, so if you’re considering using DECODE, I would suggest using CASE instead. For more information about the Oracle DECODE function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-decode-function/
Views: 737 Database Star
SQL 2008 series: SQL Pivot / SQL Dynamic Query
 
06:33
http://www.creativecommit.com This demo gives a brief overview of SQL pivot and constructing dynamic queries
Views: 10341 creativecommIT
Running total in SQL
 
04:21
Using a correlated subquery to produce a running total (or running balance) column.
Views: 11166 Barry Brown
DECODE FUNCTION IN ORACLE SQL
 
09:36
This video tutorial explains with proper example on how to write a decode statement, this video also demonstrates how the decode statement behaves on null values and implicit datatype conversion that happens in a decode statement.
Views: 2662 Kishan Mashru
Unpivot Operator in Oracle Database
 
05:18
UNPIVOT in Oracle, UNPIVOT Operator in Oracle Database
Views: 62 Adam Tech
TRANSFORMAR LINHAS em COLUNAS no SQL Server com o PIVOT
 
04:01
“Como TRANSFORMAR LINHAS em COLUNAS no SQL Server usando o PIVOT” Opa, tudo bem? Hoje veremos como TRANSFORMAR LINHAS em COLUNAS no SQL Server usando o PIVOT, esse comando simples que te ajudará a criar relatórios e gráficos em um passe de mágica! Em minha experiência esse tipo de conhecimento é vital para a sua carreira, porque demonstrar informações complexas de forma simples, para que gestores possam facilmente decidir a respeito, é uma habilidade que dá muito dinheiro e destaque profissional. Link do artigo completo: https://dba-pro.com/como-transformar-linhas-em-colunas-no-sql-server/ Abraço Josué
Views: 3410 DBA PRO
How to Code Oracle Analytic Functions
 
52:40
Many more free Oracle Database and SQL tutorials at http://skillbuilders.com/free-oracle-tutorials. 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: 26325 SkillBuilders
Self join in sql server - Part 14
 
11:19
In this video we will learn about 1. Self Join - Joining a table with itself 2. Self Join is not a different type of join. It can be classified as Inner Self Join, Outer Self Join (Left, Right and Full), or Cross Self Join. Text version of the video http://csharp-video-tutorials.blogspot.com/2012/08/self-join-in-sql-server-part-14.html Slides http://csharp-video-tutorials.blogspot.com/2013/08/part-14-self-join.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: 354076 kudvenkat
Cross apply and outer apply in sql server
 
08:16
cross apply sql server example sql server outer apply example sql server join table function with parameter sql server inner join table valued function In this video we will discuss cross apply and outer apply in sql server with examples. We want to retrieve all the matching rows between Department and Employee tables. This can be very easily achieved using an Inner Join as shown below. Select D.DepartmentName, E.Name, E.Gender, E.Salary from Department D Inner Join Employee E On D.Id = E.DepartmentId Now if we want to retrieve all the matching rows between Department and Employee tables + the non-matching rows from the LEFT table (Department) This can be very easily achieved using a Left Join as shown below. Select D.DepartmentName, E.Name, E.Gender, E.Salary from Department D Left Join Employee E On D.Id = E.DepartmentId Now let's assume we do not have access to the Employee table. Instead we have access to the following Table Valued function, that returns all employees belonging to a department by Department Id. Create function fn_GetEmployeesByDepartmentId(@DepartmentId int) Returns Table as Return ( Select Id, Name, Gender, Salary, DepartmentId from Employee where DepartmentId = @DepartmentId ) Go The following query returns the employees of the department with Id =1. Select * from fn_GetEmployeesByDepartmentId(1) Now if you try to perform an Inner or Left join between Department table and fn_GetEmployeesByDepartmentId() function you will get an error. Select D.DepartmentName, E.Name, E.Gender, E.Salary from Department D Inner Join fn_GetEmployeesByDepartmentId(D.Id) E On D.Id = E.DepartmentId If you execute the above query you will get the following error Msg 4104, Level 16, State 1, Line 3 The multi-part identifier "D.Id" could not be bound. This is where we use Cross Apply and Outer Apply operators. Cross Apply is semantically equivalent to Inner Join and Outer Apply is semantically equivalent to Left Outer Join. Just like Inner Join, Cross Apply retrieves only the matching rows from the Department table and fn_GetEmployeesByDepartmentId() table valued function. Select D.DepartmentName, E.Name, E.Gender, E.Salary from Department D Cross Apply fn_GetEmployeesByDepartmentId(D.Id) E Just like Left Outer Join, Outer Apply retrieves all matching rows from the Department table and fn_GetEmployeesByDepartmentId() table valued function + non-matching rows from the left table (Department) Select D.DepartmentName, E.Name, E.Gender, E.Salary from Department D Outer Apply fn_GetEmployeesByDepartmentId(D.Id) E How does Cross Apply and Outer Apply work The APPLY operator introduced in SQL Server 2005, is used to join a table to a table-valued function. The Table Valued Function on the right hand side of the APPLY operator gets called for each row from the left (also called outer table) table. Cross Apply returns only matching rows (semantically equivalent to Inner Join) Outer Apply returns matching + non-matching rows (semantically equivalent to Left Outer Join). The unmatched columns of the table valued function will be set to NULL. Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/cross-apply-and-outer-apply-in-sql.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/cross-apply-and-outer-apply-in-sql_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: 78152 kudvenkat
Função Oracle DECODE
 
10:20
Acesse o desafio: http://novidades.aprendaplsql.com/15-dias-de-funcoes-oracle Códigos da função Oracle DECODE SELECT * FROM hr.employees; SELECT first_name, salary, DECODE (job_id,'IT_PROG', salary * 1.2, salary * 1.1) as aumento_salarial FROM hr.employees; SELECT first_name, salary, DECODE (job_id,'IT_PROG', salary * 1.2, salary * 1.1) as aumento_salarial FROM hr.employees WHERE employee_id = 100 OR job_id = 'IT_PROG'; SELECT first_name, salary, DECODE (job_id,'IT_PROG', salary * 1.2, 'AD_PRES', salary * 1.3) as aumento_salarial FROM hr.employees WHERE employee_id = 100 OR job_id IN ('IT_PROG', 'AD_VP'); SELECT first_name, salary, DECODE (job_id,'IT_PROG', salary * 1.2, 'AD_PRES', salary * 1.3, salary) as aumento_salarial FROM hr.employees WHERE employee_id = 100 OR job_id IN ('IT_PROG', 'AD_VP'); https://www.youtube.com/playlist?list=PLMaqczuL07LkjrX6Rvx6TdNM-1nbUKG8p
Views: 2073 Aprenda PL/SQL
sql server convert row to column (Unpivot)
 
05:39
Please watch full video for more detail.
Views: 1652 SqlIsEasy
5 of 6  SQL Advanced with Oracle - A query with nested aggregate functions
 
03:16
This video gives an example of nested aggregate functions. A subquery with aggregate functions is placed in the WHERE clause of the outer query, which also contains aggregate functions. (5 of 6) There is also an "SQL Fundamentals" series. (Search for "databasemec" for tutorials using SQL Server.)
Views: 9245 Jennifer Kreie
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: 10552 Kishan Mashru