HomeОбразованиеRelated VideosMore From: kudvenkat

Subqueries in sql Part 59

613 ratings | 192164 views
In this video we will discuss about subqueries in sql server. Let us understand subqueris with an example. Please create the required tables and insert sample data using the script below. Create Table tblProducts ( [Id] int identity primary key, [Name] nvarchar(50), [Description] nvarchar(250) ) Create Table tblProductSales ( Id int primary key identity, ProductId int foreign key references tblProducts(Id), UnitPrice int, QuantitySold int ) Insert into tblProducts values ('TV', '52 inch black color LCD TV') Insert into tblProducts values ('Laptop', 'Very thin black color acer laptop') Insert into tblProducts values ('Desktop', 'HP high performance desktop') Insert into tblProductSales values(3, 450, 5) Insert into tblProductSales values(2, 250, 7) Insert into tblProductSales values(3, 450, 4) Insert into tblProductSales values(3, 450, 9) Write a query to retrieve products that are not at all sold? This can be very easily achieved using subquery as shown below. Select [Id], [Name], [Description] from tblProducts where Id not in (Select Distinct ProductId from tblProductSales) Most of the times subqueries can be very easily replaced with joins. The above query is rewritten using joins and produces the same results. Select tblProducts.[Id], [Name], [Description] from tblProducts left join tblProductSales on tblProducts.Id = tblProductSales.ProductId where tblProductSales.ProductId IS NULL In this example, we have seen how to use a subquery in the where clause. Let us now discuss about using a sub query in the SELECT clause. Write a query to retrieve the NAME and TOTALQUANTITY sold, using a subquery. Select [Name], (Select SUM(QuantitySold) from tblProductSales where ProductId = tblProducts.Id) as TotalQuantity from tblProducts order by Name Query with an equivalent join that produces the same result. Select [Name], SUM(QuantitySold) as TotalQuantity from tblProducts left join tblProductSales on tblProducts.Id = tblProductSales.ProductId group by [Name] order by Name From these examples, it should be very clear that, a subquery is simply a select statement, that returns a single value and can be nested inside a SELECT, UPDATE, INSERT, or DELETE statement. It is also possible to nest a subquery inside another subquery. According to MSDN, subqueries can be nested upto 32 levels. Subqueries are always encolsed in paranthesis and are also called as inner queries, and the query containing the subquery is called as outer query. The columns from a table that is present only inside a subquery, cannot be used in the SELECT list of the outer query. Next Video: What to choose for performance? Queries that involve a subquery or a join Text version of the video http://csharp-video-tutorials.blogspot.com/2013/01/subqueries-in-sql-part-59.html Slides http://csharp-video-tutorials.blogspot.com/2013/09/part-59-subqueries-in-sql-server.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
Html code for embedding videos on your blog
Text Comments (57)
Te Ar (2 years ago)
I appreciate your efforts in preparing and uploading these videos which i found very helpful and very explanatory. I get excited to watch the videos and try the syntaxs. Thank you Venkat, keep uploading such invaluable videos. If you can make videos that can help in tackling the microsoft certification preparation topics, that would be great.
kudvenkat (2 years ago)
Thank you very much for taking time to give feedback. This means a lot. I am very glad you found the videos useful. Sure, I will record and upload as soon as I can. Thank you very much for your patience. I have organised all the Dot Net & SQL Server videos in to playlists, which could be useful to you https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd If you need DVDs or to download all the videos for offline viewing please visit http://www.pragimtech.com/kudvenkat_dvd.aspx Slides and Text Version of the videos can be found on my blog http://csharp-video-tutorials.blogspot.com Tips to effectively use my youtube channel. https://www.youtube.com/watch?v=y780MwhY70s If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel. http://www.youtube.com/subscription_center?add_user=kudvenkat If you like these videos, please click on the THUMBS UP button below the video. May I ask you for a favor. I want these tutorials to be helpful for as many people as possible. Please share the link with your friends and family who you think would also benefit from them. Good Luck Venkat
Bobo z (3 months ago)
all your videos are excellent if you release updated versions of your courses on udemy or another site I will buy them guaranteed. I highly recommend his c# videos as well.
Dipti Sheth (3 months ago)
amazing videos sir, please keep uploading videos and we will keep watching :)
Youth Collections (6 months ago)
Sir, I want your database script. So from i would get that?plz....
Aakanksha Gupta (10 months ago)
While writing the second query where we need to find the name and no of quantity sold, why have you used left join?
vijay .vj (1 year ago)
Thanks for these videos. Can u make s video on nested subquery?
Karthik Bharath (1 year ago)
You are awesome
Brijesh Mavani (1 year ago)
thankyou venkat it's verry helpfull
Kelly Freet (1 year ago)
Great examples, keep up the good work!
salman abdulkarim (1 year ago)
what an explanation , thanks sir <3 <3
best course of SQL, thanks man
rajini kanth (1 year ago)
sir,your class good but one suggestion but the veiw is not clear can you zoom it and show
Krzysztof S (1 year ago)
Thank you Venkat. This is next topic i have learned sth new. Keep goin' I'm going further and further learning SQL with your videos.
Bestodds1 (1 year ago)
Thank you so very much for your very clear and understandable examples. They caused me to look at your other playlists and I plan to be watching many, MANY more of your tutorials. Again, thank you!
Gautam Maru (1 year ago)
hi all, I am students, and I am working on my project. I have to create below requirements : 1. Prepare the specifications of semi structured extensions to your Database. Minimal Requirements: 3 extensions in data model and 5 operations on XMP data. Each extension of data model should be described with: name, affected relations, the method of storage of XML used i.e. CLOB, XMLType, CHAR. Each description of operations on semi structured data should be described with: name, corresponding SQL operation from workload, tools used for implementation i.e. SAX, DOM, XPATH, XQUERY. 2. Implementation of at least 3 semi structured extensions to data model. 3.Implementation of at least 5 operations on semi structured data. Document the execution times with semi structured extensions. I created database and I use SQL SERVER 2012. So can any one tell me what should I do for these ? Plz give me good example and explain me ..thank you .
Bhanu Prakash (7 months ago)
crazy fellow
Poonam Negi (1 year ago)
Hi Venkat, Your videos are very simple yet very helpful. You make things really easy for people to understand.Keep up the good work Cheers :)
Sarika Biwalkar (1 year ago)
This video clearly and concisely explains subqueries thoroughly. The explanation is awesome and simple to understand. Thank you, Sir! Your videos are really good to understand the concepts of .Net and SQL Server.
NK (1 year ago)
sir plz make some toturials on php's framework laravel....
Parvez Mullah (2 years ago)
Thank you sir. your videos are very helpful.
Justin Li (2 years ago)
this man is a hero, a legend and a god!!
ALLCPL (5 months ago)
:)
Slobodan Djuric (2 years ago)
Dear Sir, you made excelent examples. Very intuitive and helpful. Far better than the most I found via internet. Hope you will continue with great work Best wishes
Salman Shaikh (2 years ago)
Thanks a lot for posting th queries. One thing some might be surprised to see in this lecture Note my table names are just having "3" in the name because I already have same name tables in my database. select Name, (select SUM(QuantitySold) from tblProductSales3 where ProductId = tblProducts3.Id) as QtySold from tblProducts3 order by Name // note no group by used here!! This is because in the Products3 table we have Name only once so there is no need to group by Select [Name], SUM(QuantitySold) as TotalQuantity from tblProducts3 left join tblProductSales3 on tblProducts3.Id = tblProductSales3.ProductId group by [Name] //here we used grp by because the join result will contain repeated names order by Name hope this helped!
Muhammad Yasir (2 years ago)
Select distinct Gender, (Select Sum(Salary) From tblEmployee emp1 Where emp1.Gender = tblEmployee.Gender) As SumOfSalaries From tblEmployee; Select distinct Gender, (Select Sum(Salary) From tblEmployee Where Gender = tblEmployee.Gender) As SumOfSalaries From tblEmployee; +kudvenkat plz tell me why these 2 queries return different result. Shouldn't they return the same?
Rahul Tiwari (2 years ago)
Sir , please make some lecture for Case statements
Prashant Shivhare (2 years ago)
i saw a all videos related sql but i want to know more queries in sql please help me to know more queries in sql .
Kris Maly (3 years ago)
Revisiting. Thanks for educating the community Thanks a lot
DailyTech (3 years ago)
hello Kudvenkat having a minor mistakes with quires use COUNT as compare to SUM. SUM gives the wrong answer
Andrew Keller (3 years ago)
+DailyTech I think you may be mistaken. He gets the correct quantity for Quantities sold. ie. Count would give 3 Desktops when there is 18.
Erin G (3 years ago)
Hi! Your video is very helpful. When you asked for a query that returned the products and the total amount sold, I paused the video and wrote my own code and got the same answer. Could you tell me how efficient or inefficient my code is. Did I stray from the topic? Select b.Name, ISNULL(a.Totalsold, 0) AS TotalSold From     (select ProductID, SUM(quantitysold) as Totalsold     from tbleProductSales     group by ProductID)as a RIGHT join tbleProducts as b on a.ProductID =b.ID; Thanks!
Hoan Le (3 years ago)
thanks you very much
Tav Harega (4 years ago)
Good one! Many thanks from Romania
Deepu R (4 years ago)
Thanks much its helpful :)
#Stuti# #Tehri# (4 years ago)
nice explanation sir
kalyan chakravarthy (4 years ago)
HI Venkat ! We Can Also Use this query ? ----------------------------------------------------------------------------------------------------------- Select ProductId= Case When ProductId=2 Then 'LapTop'  When ProductId=3 Then 'Desktop' Else 'TV' End ,Sum(QuantitySold)as QtySold from tblProductSales group by ProductId 
Surampudi Tpdevi (4 years ago)
i like it venkat..
krismaly (5 years ago)
I enjoyed this video and I recommend others to watch. Thanks a lot FRIENDS please note Mr Venkat's videos are good and ready for references, interviews, and day to day use in your work. I love it
kudvenkat (5 years ago)
You are very welcome. For email alerts, when new videos are uploaded, please subscribe to my channel. In the description of this video, I have included the link for ASP .NET, C#, and SQL Server playlists. All the videos are arranged in logical sequence in these playlists, which could be useful to you. Please share the link with your friends who you think would also benefit from them. If you like these videos, please click on the THUMBS UP button below the video.
Vazha Abuselidze (5 years ago)
Thanks!!
abdullahi mg (5 years ago)
i enjoy it sir; may God bless you
kudvenkat (5 years ago)
Hi Imran, Thank you very much for taking time to give feedback. I am really glad you found these videos useful. If you want to receive email alerts, when new videos are uploaded, please feel free to subscribe to my youtube channel. May I ask you for a favour. I want these tutorials to be helpful for as many people as possible. Please free to share the link with your friends and family who you think would also benefit from them. Good Luck.
Muhammad Imran Mirza (5 years ago)
God bless you Venkat .... you made the life easier ... I learned a lot from your videos ....
Alkesh Dodia (5 years ago)
hi Venkat, can you upload more videos on SQL server on how to admin. thanks a lot to you for all the turorial you have uploaded so far. I really wanted to learn SQL server & your videos have been a great help. Your method of expaining is superb. so it would be so nice of you to put some more tutorials as i want to become expert on Sql..
thank you)))))) it helped me a lot))))))
bhavaya dudeja (5 years ago)
hi venkat would u please tell me what are the measures we have to take while using subquery like what column we have to take in inner query and outer query and is it possible to take common take on both side
kudvenkat (5 years ago)
Hi Jeewan, Our next topic in asp.net will be grid view control. At the moment I am finishing up with caching in asp.net. We will cover from the basics to advanced stuff on grid view. Thank you very much for your patience.
Jeewan Maharjan (5 years ago)
@Shyamsunder Puram Yeah,I agree with shyamsundar , Sir Venkat should cover in depth topic of Gridview and Datagrid.. We hope sir Venkat would take it into consideration
kudvenkat (5 years ago)
Thank you very much for the feedback. I am glad these videos are helpful to you. If you want to receive email alerts when new videos are uploaded by me, feel free to subscribe to my youtube channel.
JUMAINE RUMONSUK (5 years ago)
am very much impressed with your lessons.am up and learning at a better pace.Sql is a subject near to my heart.
Shyamsunder Puram (5 years ago)
iam waiting for gridview tutorial from you but you are giving more importent value to un necessary user requests they are diverting you to make videos what they like? i think they are have some experience in .net .they are leading you please take care.
kudvenkat (5 years ago)
I am glad these videos are helpful to you and thank you very much for taking time to give feedback. I don't have MSBI Stack(SSIS, SSAS & SSRS) videos at the moment, but will be recording and uploading them very soon. I upload training videos on a daily basis. If you want receive email alerts when new videos are uploaded, please feel free to subscribe to my youtube channel.
M Ali (5 years ago)
thank you veryyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy much i always learn something new from your videos. and thanks for taking some time out for these sub-queries videos :) you are a star :)
kudvenkat (5 years ago)
Hi Syed, thank you for taking time to feedback. Sure, I will whenever I get some time.
Syed Kasim (5 years ago)
As ususal very informative. I have seen all your videos, very nice. In one of the videos of triggers, you said that you are going to post DDL and login triggers. It will be useful if you post that videos. Thanks for you effort.
sujan pathak (5 years ago)
Thankyou For a Helpful Effort

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.