CTE in SQL (Common Table Expression) | SQL WITH Clause | CTE Query Performance | Advanced SQL
HTML-код
- Опубликовано: 30 сен 2024
- 𝐖𝐚𝐧𝐭 𝐭𝐨 𝐌𝐚𝐬𝐭𝐞𝐫 𝐒𝐐𝐋? 𝐋𝐞𝐚𝐫𝐧 𝐒𝐐𝐋 𝐭𝐡𝐞 𝐫𝐢𝐠𝐡𝐭 𝐰𝐚𝐲 𝐭𝐡𝐫𝐨𝐮𝐠𝐡 𝐭𝐡𝐞 𝐦𝐨𝐬𝐭 𝐬𝐨𝐮𝐠𝐡𝐭 𝐚𝐟𝐭𝐞𝐫 𝐜𝐨𝐮𝐫𝐬𝐞 - 𝐒𝐐𝐋 𝐂𝐡𝐚𝐦𝐩𝐢𝐨𝐧𝐬 𝐏𝐫𝐨𝐠𝐫𝐚𝐦 𝐛𝐲 𝐒𝐮𝐦𝐢𝐭 𝐒𝐢𝐫!
"𝐀 8 𝐰𝐞𝐞𝐤 𝐏𝐫𝐨𝐠𝐫𝐚𝐦 𝐝𝐞𝐬𝐢𝐠𝐧𝐞𝐝 𝐭𝐨 𝐡𝐞𝐥𝐩 𝐲𝐨𝐮 𝐜𝐫𝐚𝐜𝐤 𝐭𝐡𝐞 𝐢𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰𝐬 𝐨𝐟 𝐭𝐨𝐩 𝐩𝐫𝐨𝐝𝐮𝐜𝐭 𝐛𝐚𝐬𝐞𝐝 𝐜𝐨𝐦𝐩𝐚𝐧𝐢𝐞𝐬 𝐛𝐲 𝐝𝐞𝐯𝐞𝐥𝐨𝐩𝐢𝐧𝐠 𝐚 𝐭𝐡𝐨𝐮𝐠𝐡𝐭 𝐩𝐫𝐨𝐜𝐞𝐬𝐬 𝐚𝐧𝐝 𝐚𝐧 𝐚𝐩𝐩𝐫𝐨𝐚𝐜𝐡 𝐭𝐨 𝐬𝐨𝐥𝐯𝐞 𝐚𝐧 𝐮𝐧𝐬𝐞𝐞𝐧 𝐏𝐫𝐨𝐛𝐥𝐞𝐦."
𝐇𝐞𝐫𝐞 𝐢𝐬 𝐡𝐨𝐰 𝐲𝐨𝐮 𝐜𝐚𝐧 𝐫𝐞𝐠𝐢𝐬𝐭𝐞𝐫 𝐟𝐨𝐫 𝐭𝐡𝐞 𝐏𝐫𝐨𝐠𝐫𝐚𝐦 -
𝐑𝐞𝐠𝐢𝐬𝐭𝐫𝐚𝐭𝐢𝐨𝐧 𝐋𝐢𝐧𝐤 (𝐂𝐨𝐮𝐫𝐬𝐞 𝐀𝐜𝐜𝐞𝐬𝐬 𝐟𝐫𝐨𝐦 𝐈𝐧𝐝𝐢𝐚) : rzp.io/l/SQLINR
𝐑𝐞𝐠𝐢𝐬𝐭𝐫𝐚𝐭𝐢𝐨𝐧 𝐋𝐢𝐧𝐤 (𝐂𝐨𝐮𝐫𝐬𝐞 𝐀𝐜𝐜𝐞𝐬𝐬 𝐟𝐫𝐨𝐦 𝐨𝐮𝐭𝐬𝐢𝐝𝐞 𝐈𝐧𝐝𝐢𝐚) : rzp.io/l/SQLUSD
𝐖𝐚𝐧𝐭 𝐭𝐨 𝐥𝐞𝐚𝐫𝐧 𝐁𝐢𝐠 𝐃𝐚𝐭𝐚 𝐛𝐲 𝐒𝐮𝐦𝐢𝐭 𝐒𝐢𝐫?
𝐜𝐡𝐞𝐜𝐤𝐨𝐮𝐭 𝐭𝐡𝐞 𝐛𝐢𝐠 𝐝𝐚𝐭𝐚 𝐜𝐨𝐮𝐫𝐬𝐞 𝐝𝐞𝐭𝐚𝐢𝐥𝐬
𝐖𝐞𝐛𝐬𝐢𝐭𝐞 : trendytech.in/...
I have trained over 20,000+ professionals in the field of Data Engineering in the last 5 years.
CTE in SQL | Common Table Expression | SQL WITH Clause | CTE Query Performance | Advanced SQL
In this video you will learn about CTE (Common table expressions) In SQL. The WITH clause really simplifies the complicated queries, and makes the SQL queries readable.
with Inner query we can see the code getting messy and there is a lot of redundancy.
Also we have talked about the performance of queries using CTE.
We even saw how CTE can degrade the performance.
CTE is very important from SQL Interview perspective, I hope this video should have given you a good idea on how to work with Common table expression in SQL.
J𝗼𝗶𝗻 𝗺𝗲 𝗼𝗻 𝗦𝗼𝗰𝗶𝗮𝗹 𝗠𝗲𝗱𝗶𝗮:🔥
🔅Sumit LinkedIn - / bigdatabysumit
🔅Sumit Instagram - / bigdatabysumit
If you haven't subscribed to the channel then please do subscribe for more insightful content
#CTE #AdvancedSQL #WITHClause #bigdata #dataengineering
Checkout the Big Data course details here: trendytech.in/?referrer=youtube_sqladv1
Just a suggestion please share the table and it's contents as a link in video. So that while learning we can practice side by side. Btw, course is very helpful and builds confidence in SQL.
i really like this suggestion, and would like to see this as well
@@rtecunit602, sir, you use any sales/product dataset. You can even create your own dammy/training dataset
"Fantastic explanation! Your detailed breakdown of the concepts and step-by-step walkthrough made it easy to follow along. The examples you provided really helped to clarify the more complex parts. Thank you for making such an informative and well-structured video!"
CTE looked like a mysterious concept initially after just looking into some of the articles from google search, this video has made it look easy to grasp its purpose, syntax, benefits, possible limitations & internals, explained in a way that makes learning interesting & joyful, thank you very much sir :)
Please create one vedio with the brief description of all the topics which u r going to cover in Advance SQL playlist.
first the video length discouraged me. but later I felt that its worthy.
haha so true , same with my case
short code of content is causing trouble for us 🙃
Thankyou for the video Sir. 3 months back I started preparing for interviews. I have watched almost all of your videos here. It helped me a lot in interviews. Today I have 7+ offer and all from top product companies. Invest in yourself, you will get best ROI... In my case 6x in 14 months. And Sumit sir played a very important role.
Hi, which profile vikash? Data engineer? I'm in Data analyst position and CTC is not that good (~4 LPA) Is it possible to increase my CTC to 8~9 LPA within 1 year?
@@abhimanyutiwari100 yeah i am a data engineer. u can definitely expect a good number provided u r prepared well
@@vikastiwari901 bro what imp topics we need to cover for data engineer profile?
Hi, can you please tell mi which topics are mostly asked in interviews and what is the level of questions asked for SQL ? Cause now I am feeling like that I am preparing everything and I am confusing myself.
We can use below subquery approach:
select order_customer_id,count(*) as total_count from orders group by order_customer_id having total_count >
(select avg(total_count) as average_count from
(select order_customer_id,count(*) as total_count from orders group by order_customer_id) derivedtable) ;
Can we use CTE on this ??
It is good to use CTE when there are Complex queries.
But its good, not to use CTE when single and recursive queries as CTE. Bcoz it may degrade the performance.
Correct me if I am wrong.
SQL tuning and optimization please sir!! Thank you and really great work.
This tutorial gave full clarity on CTE(With clause). Thank you sir.
Could you please make videos on hints and optimization tuning? It will help us a lot.
Thank you Sir, Please add some more topics like sql query optimization and all
Very thanks sir for your valuable time and energy...
WITHOUT SHARING THE TABLE AND WITHOUT DOING IT PARALLELY IT BECOMES DIFFICULT TO FOLLOW AFTER A WHILE.
Make more videos sir, we'd appreciate them if posted soon, thankyou
can you provide table script....?
Sir please upload content faster
btw the video is awesome
Happy that you like the content. Will try to keep up the pace.
It would have even better if data sources were also been shared.
Hi Sir,
Can we write the below fashion for the premium customers (36:29)
select order_customer_id
from total_orders
where total_orders_per_customer > (select avg_order_per_customer from average_orders)
NOTE: Only to get the customer_id not the rest of the columns.
unless they've given us total_orders and average_orders tables in the data, we can't use the query you've written.
Sir please continue the series where you used to solve leet code problems
Please upload pyspark course also sir
This Onecompiler doesnt work sir ...
You should always put down sql scripts in comment box for ppl to practice paralelly .
The way I write SQL queries is to capitalize the commands and use lower case for the tables. That way it stays really clean. Just an opinion.
Hello sir.Will you post videos on Data modelling concepts?
sir leet code series and sql optimization as well
Sure, noted.
I learn with clause on my own after referring bunch of codes in project that i am working on, took lot of time to understand it. Now that i know it, got addicted to it, its very useful.
Wish i would have seen this video earlier
Thanks Sumit Sir
explained very clearly!! hats off
Hi sir, could you please cover stored procedures , functions, triggers ad well
Else is their any paid course available let me know sir . Thanks in advance
Sumit Sir, really enjoyed this video a lot, I just know about CTE but not with CTE (column_1, column_2), this is super understandable. I would love to know how are you able to run the SQL query in VS code, please help me too. Thank you Sir. :)
Was eagerly waiting for it
Thank you Sumit
Hey dear, god bless your efforts in this channel.
I have a general enquiry as a new sql learner.
How could i create a pipeline to extract and load data from existing accounting program into our SQL server instances.
How can i know if the export mechanism in the software permits me to undertake this extraction process, and how can i know if an application have an api?
Thanks for taking care of my enquires.
Looking forward to gain more knowledge from you.
Well explained. Thank you sir :)
Where CTE is stored? which approach is better?
It is really advanced CTE concept 😊😊😊😊
please create a video on fact and dimension , row and columnar data, transactional and time series database
Thank you sir,
Can you do a video on TRANSACTION, COMMIT, and ROLLBACK Statements ?
Sir kindly provide the tables for practice it would help most of us for practice
My gap year is 5 years so can you help me to enter into it industry. My skill is oracle
Can you please share the create and insert syntax for this table?
I'm happy to see Deepak Kalal teaching us SQL. Nice video though ❤️
Thanks for the explanations. It has made CTE and subqueries clearer.👌
This video is very helpful sir, i have used CTE before but didn't know about having more than 1 table in WITH clause. Can you also explain about in your next video. Thankyou Sir
Thank you so much sir for this video, it was really helpful.
Please make video on Facts and Dimensions also, you explain so well and these are important topics as well..Please please
Can u make a video on Stored procedure
Thanks, very good explanation, please keep on providing right knowledge to all of us😊
Please do videos on optimizations...
The orders table is from the retail_db database
Sir aap se lecture join karna chahte h
Thank you for such a nice and informative tutorial on CTE. This cleared a lot of doubts.
It's one of the best tutorial with in depth explanations and clarity. You just made understanding & working of CTE so easy with your explanations sir. Hats off to you for this.
Thank you @ Sumit Mittal Sir for an amazing video on CTE.
excellent
Thank you so much for the detailed video for CTE
Hello Sumit Sir, Just Now I have watched CTE Video. Its totally Understand to me. Can you please Create One another video for CTE for us..
Topic is "Parent - Child Category Concept"
What I mean let say in a Category table there is 2 columns.
1. category_id (Primary key and Autoincrement)
2. parent_category_id (value of category_id)
that means recursive query (nested).
Example:
Category Id Parent_category_id
1 0
2 0
3 1
4 1
5 2
6 3
7 1
8 0
great.. thank you so much!!
Excellent in-depth video Sumit sir. Really liking it and will benefit us to a great extent.
Nested queries concept is it important
Very nicely explained , Maza aa gya :) !!
Thank you sir, Do more please sirrr...
It Is very helpful!
thank you sir...next video when?
Great explanation
Please share tables
normalisation concept 🌚
Great video sir . thanks for the effort
Great lecture sir........thank you 👏
Thanks sir
It helps alot
Pls take cursor classes
First one to like :)
Great 👍👍
The depth in your videos is incredible!
Awesome 😎👍
Marvellous💯
Amazing Lecture.
best video on cte
First view
love u sir
Sir... Your are killing it... 💯💯❤ ,Best sql series available on internet.
Thank you. Happy to know that you are liking the series.
@@sumitmittal07 request sir window functions and anaytical function , i mean with your series end result is cracking MANG . once series completed we should crack mang , refer Ankit Bansal youtube
@@sumitmittal07 cte , stored proc as one one query we dont fire , sql execution plan and correcting performance , recursive queries in sql topic
@@sumitmittal07 visit clever studies channel 6 concepts in one query mean prepare one doc where 6-7 concepts used together sql query like union + filter + recursive query + withclause + having
you can vsit data is good youtube channel , clever studies sql list , cloudyml youtube , Ankit bansal the level is increased
Really good
Glad that you are liking it.
Please launch more sessions for advance SQL as soon as possible sir..it's a request..I'm learning via your videos so desperately wait for your classes..