Advanced SQL Tutorial
HTML-код
- Опубликовано: 20 окт 2019
- In this tutorial I will cover many advanced SQL concepts like full outer joins, coalesce, cross apply, outer apply, intersect, except and pivot.
NOTE: The database I use for this tutorial is MS SQL Server and the syntax can vary depending on the database.
What a legen! His restaurants went bankrupt but now he teaches us SQL. Thank you Jamie Oliver
LOL ;)
I find your vids real helpful as a full stack dev tweaking sprocs every now and then. Thanks for the content.
Great Video. Thanks for making your series on SQL... I've learned a ton from your clear and concise explanations.
Thanks for this. I finally have PIVOT working! I've converted two stored procedures so far and will be converting more now that I understand how it works.
>Thanks for watching
Thanks for creating
Amazing ! finally could understand coalesce and pivot. Please make videos on stored procedures, CTE and materialised views.
Great vid! Difficult topics explained in a very clear and easy way to understand! Thanks and keep them coming!
Thank you so much! Will do! :)
Loved the video!!! thank you for making this amazing video.
Thank you! I was looking for the SQL equivalent of the || operator, and had read about coalesce before. Your video really made it "click" for me.
Always like to know I'm helping people. That is what it is all about.
This is super useful stuff , thanks a lot mate
Brilliant! Well done!
Thank you! Great video!
Man you just rock thank you for sharing ❤
appreciate the vid brother
Great video!
loved it :)
Nice breakdown of pivot table
Thanks bro... Amazing video
Nice!!
Thank you
This was a helpful explanation of PIVOT
Glad you liked. :)
Excellent tutorial
Neo-1 I appreciate the kind words
Great video.
Appreciate it very much. :)
Thx
so at @2:10 you want to only select specific columns that they asked for which was the ecom quantity and store quantity don't need to see the color and all that
Great videos. Thank you. Only if you can control those juicy tong sounds.
Thanks for the video. Can you please make a video on how Enterprise manager reports can be leveraged.
Great suggestion! I will put that on my list.
@3:17 is there a is not null for the product id or is that a different concept?
SQL is supposed to be simple human readable. Always had issues with the Pivot, the fact that the Categories values had to be hard-coded in the code makes it static. There are solutions on the internet how to first read the list of categories values in a variable.
If I had u as my datasci professor I couldve been working at IBM by now
Midnight Run :)
Neat and concise. Great for familiarizing oneself with these features. If it included the UNPIVOT statement it would a make for a nice, complete tutorial.
Hi, We may also use Isnull(s.ProductID, e.ProductID) ProductID instead of coalesce this gives also same output. plz verify.
So what's the value in using OuterApply or CrossApply? Based on the demo, the result could be achieved with Table Joins. Anyway, I can Google for the answer. Was always curious when to use Cross Apply or Outer Apply, never knew the answer until this came up in a job interview.
Will I be able to apply this on mysql??
Cool, i have a doubt. Suppose i have a table with 3 columns say name,id,address and say 100 records. The table has some of the values in name,address filled with null or blank space. I Need to take report which shows me the count of records with blank spaces,null and this shud be achieved without using group by
at the first query, isn't it the same as union all? instead of left join+coalesce? if not, when do you use which?
Not sure if you will ever respond but.. why you didn't used the alias for ProductId in the COUNT function?
Why can't we just go for a simple subquery for the 1st requirement , Select * from table1 t1 where t1.id in (select id from table2)
how can you import csv file to an already existing table using sql query?
like suppose you have Customer table and now you want to add data from xyz.csv file and both Customer and csv file have same attribute ,then how to do such task?
hi your advanced sql session is really helpful...could you please upload some more advanced queries?
ADITYA ANAND sure will do
Thanks a lot....I am working on data analysis and i have to use sql with advance functions for transformations etc and so I thought that u could help me....thanks ...
How to enable viewing syntax and command definition [while typing any command] in MS-SQL as we see in Excel
In the pivot statement, would it have been possible to select distinct values rather than listing them?
You need to reference the actual column names. A select returns the values. Unless I'm misunderstanding the question.
@@JamesOliver i guess he meant making a subquery with (select distinct(c.name or category) from productcategory). can we do that in here?
Can you prepare basic to advanced series, so every one can learn SQL
how can i find to this database?
It's rubbish that you have to hard code the columns for the pivot... can you not do a select Distinct to populate that?
It would be hella cool if you add timestamps
Hello Mr. Oliver!
Could you take a doubt please?
Regarding the part of the query before the pivot?
I'm using the same base as you and your query, I achieved exactly the same results that you showed in the video.
select c.name Category,
count (p.productid) totalproducts
from production.product p
inner join production.productcategory c
on c.productcategoryid = p.productsubcategoryid
group by c.name
go
Accessories 8
Bikes 32
Clothing 22
Components 43
It's a simple query like you said then I tried to take the test and I couldn't reach the number that the query arrived
I'll take as an example the accessories category (productcategoryid = 4) that gave a total of 8:
select * from Production.ProductSubcategory
where ProductCategoryID = 4
go
productsubcategoryid productcategoryid
26 4
27 4
28 4
29 4
30 4
31 4
32 4
33 4
34 4
35 4
36 4
37 4
Ok, after this result I did this search:
select
count(productid) count_products_cat_accessories from Production.Product
where ProductSubcategoryID between 26 and 37
count_products_cat_accessories
29
If possible, can you show me why I didn't get the result of 8?
Thank you!!
I would have to go back and check but I use this database for other demos/purposes and it is possible I have modified the underlying tables. It has been a little while so would have to check to be sure but that is my guess. Thanks for watching.
@@JamesOliver I thank you! Your videos helped me a lot, they are very didactic!
Ok, I understood what you said about the db but
in this case, your query returned exactly the same results as the categories shown in your video.
I believe that the bases are hitting.
In the future, if you go back to the original DB AdventureWorks2012, I would appreciate it if you can check where I went wrong. Follow the test print link.
drive.google.com/file/d/1In0tEldKrakm8YM01qRo_5n5T1or1jXR/view?usp=sharing
My best regards
!
what application are you using sir ?
Sql server express and management studio. All free.
@2:45 I guess they don't like to see nulls in their data?
Yea just quickly handling the NULLs and converting to zero
How are you.
should be titled TSQL
How un earth is this considered advanced
lost me @4:30 what is this "function"
Ayejax just a function that returns rows