SQL Complex Queries , Query Optimization and Interview Questions SQLServer 2016

[@nonym3829]

After 2~2.5 years.... This is the first time commenting on tutorial video on KZbin. THANK YOU, THANK YOU VERY MUCH!

[@techsapphire]

Glad you found it helpful :)

[@akrooma]

This video is not just a tutorial.. it is a comprehensive refrence! Good job really

[@techsapphire]

Thank you

[@PaIach]

Really cool stuff. Thanks a lot! Note for those interested (6:57): it is possible to have a group by over "Over" functions. In such case, the Over line would just need to have twice the aggregated function: SUM(SUM(Qty*UnitPrice)) OVER (ORDER BY ProdName) as RunningTotal .. + GROUP BY at the end

[@maximussamir2332]

I know it is kinda randomly asking but do anyone know a good place to stream new movies online?

[@alvaroarturo9345]

@Maximus Samir try flixzone. Just google for it :)

[@hughmarc9373]

@Maximus Samir lately I have been using flixzone. You can find it by googling =)

[@isaiahanderson1612]

@Maximus Samir I use Flixzone. Just search on google for it =)

[@splashoui3760]

No, partition by is doing the task of group by in this case. So, you should not use group by at all.

[@vijayvijayaratnam674]

Seriously this is the best channel about SQL Queries, at least for me, This is by far the best video I have ever seen on this topic, Thank you

[@techsapphire]

Thank you for your positive feedback.

[@onyekaekweh8681]

This is a detailed break down of pretty much windows function. I learn a lot in a short period of time with this video. Thanks so much for your generosity. One quick question, what is the dataset you used in building this query?

[@techsapphire]

Northwind is majorly used in my videos

[@ga7853]

Thank you so much for making the SQL script available that was really generous and it's appreciated. Thanks again.

[@techsapphire]

Thank you for your feedback. I am glad you find video helpful.

[@innominatesoloist1597]

aint it the best advanced scripting compilation, ever?

[@techsapphire]

Glad you liked it

[@vishalrode2194]

please upload some oracle database performance issue videos with real time example your teaching is very good :)

[@techsapphire]

Sure I will

[@srinivasprasad.b4479]

Very helpful and was concerned on making things easy giving the where and what situation can be asked and where query fits...👍👍

[@pratiharpooja]

you have given the best session on SQL queries.

[@techsapphire]

Thank you

[@BharatPatel-tk8dg]

Very helpful. I would suggest Inline temporary table work better than with clause. Thanks for sharing

[@dergimorka6126]

hello yogashi i like the way u explaided it is really perfect but i have one question for you so u only post SQL stuff or u do the SSRS ,SSIS ? LET ME KNOW PLS i subscribed already but wanted to know thank you

[@techsapphire]

yes I publish few videos on SSRS too, or local RDLC. Which one you are interested.

[@yulinnong4980]

Thank you for sharing the script! I really like the topics you covered in the tutorial. I feel these algorithms are not just for interview, they are for really production, very useful! Thank you again! Please continue to provide more useful tutorials like this!👍😊

[@techsapphire]

Glad you liked it, you can also check kzbin.info/www/bejne/qmO1YWCumLCqapY . If you find this solution helpful than donate comfortable amount on papal to my email ID or link www.paypal.me/bimlamehla.

[@suryanarayankhuntia1813]

Very helpful in terms of reporting, These things helped me a lot to clear the interviews.Thank you verymuch.

[@waleraji4517]

Pls i will like to ask u a question. Maybe you can email me : [e-mail]

[@supersql8406]

Super awesome training! Please make more of these type of videos!

[@techsapphire]

More to come!

[@acesprint9448]

Thank you for the crystal clear explanation! By any chance could i please get the database for this? i want to try running the query myself for exercise

[@techsapphire]

You can download from link provided www.techsapphire.in/index/sql_complex_queries_query_optimization_and_interview_questions_sql_server_2016/0-190

[@padmachowdarykothapalli891]

Thanks Yogesh for given good scenarios on sql

[@techsapphire]

Hi Padma I provide training in query optimization, if interested you can contact me on my email [e-mail]

[@padmachowdarykothapalli891]

techsapphire Yogesh please upload performance tuning scenarios also

[@techsapphire]

Padma performance lectures are part of paid training. First session is free if, you feel not to continue.

[@pavankumarvaka2265]

This is excellent video I ever found on SQL Thanks Bro

[@techsapphire]

Glad it was helpful!

[@kshathrugan5238]

Really helpful stuff. Can you explain the 2nd highest salary part inner query, its kind of unclear for me after the where part ... suppose 3rd highest... so it where 3-1 = distinct salary(suppose count is 5).. what will be the value after > e1.salary part?

[@techsapphire]

concept is, 3-1 means people with (3-1) position salary above him.

[@rajeshgoud7547]

Very Nice and simply super ...... Thanks for sharing your knowledge wit us.........

[@techsapphire]

Thank you for your feedback

[@sungod595]

Can you please create detailed course on Performance Tuning , Even if it is paid course on Udemy etc . i will be interested to take . Thankyou

[@techsapphire]

Soon I am publishing course on performance, data modelling and graph on SQL Server

[@sungod595]

@@techsapphire Following up on this, Hope the course that you planned is on tract and soon will be released. BTW where are you from ?

[@madhukotika5062]

Hi bro. Thank you for efforts. I have a suggestion, you need to slow down at times, you should clearly explain things. you are running around. If need make more videos.

[@manishdubey8646]

Very very informative for beginners. Keep it up yogesh ji

[@techsapphire]

Thank You Manish

[@nishantade2026]

Very useful videos do provide online training?

[@techsapphire]

yes I do provider commercial training and consultation.

[@sudeepkumarbehera4336]

these questions are helpful for 2+ yr experience people ? or should i go for more complex query please let me know . thanks for this video

[@techsapphire]

These are mainly for teaching components to build complex query. If you want more, you need to learn performance tuning.

[@learnnrelearn7553]

Very informative .... thanks a lot for sharing ...............

[@techsapphire]

So nice of you

[@ArihantJainIITKGP]

Thank you for the video, it's really exhaustive. Can you please provide the script link? The link in the description seems to be broken.

[@techsapphire]

Link will be working now

[@meenalnafde6216]

Very nice video.. Thanks for sharing👏👏👏👏👏

[@techsapphire]

So nice of you

[@vickyanand5898]

Can you tell me in Optimization of query why the query took more than 1 min ? what is the cardinality number ? Basically can you explain query ?

[@techsapphire]

Yes, I have provided complete performance tuning session. Kindly refer kzbin.info/www/bejne/qmO1YWCumLCqapY

[@vinayakjadhav4598]

It's complex but important ✌

[@techsapphire]

glad you found it helpful

[@ShyamRaithatha]

Gazab bhai, awesome

[@techsapphire]

glad you liked it

[@dnyaneshwarshinde4775]

Hi could u provide product table and category table information script... So we can do real time handsone.....

[@techsapphire]

You can find db backup on www.techsapphire.in/index/sql_complex_queries_query_optimization_and_interview_questions_sql_server_2016/0-190

[@madhusmitatripathy8767]

very well explained .. thank you for sharing

[@muralikumarkathirvel174]

Thank you for the presentation. It is so nice and very useful

[@anikethdeshpande8336]

Very nicely explained, thank you for sharing queries

[@techsapphire]

So nice of you

[@arpana20singh86]

Very nice n usefull. Liked it very much 😊👍 thank you.

[@techsapphire]

Glad you liked it, there is newer and advance sql optimisation video, if you feel interested please check kzbin.info/www/bejne/qmO1YWCumLCqapY

[@mrrosh4471]

Thanks for the class

[@techsapphire]

My pleasure

[@mohammadmamun5193]

Thanks for the video,Can I get the code and data to practice?

[@techsapphire]

Kindly share your email

[@devarajkr7651]

You have covered very good questions, thanks

[@techsapphire]

Thank you for your feedback

[@Latif127]

Very useful and helpful, thank you very very much.

[@techsapphire]

Glad it was helpful!

[@emilyf2641]

Very good video, thank you so much.

[@techsapphire]

You are welcome!

[@dergimorka6126]

i like to say thank you so much it is very helpful tips sir

[@techsapphire]

You are most welcome

[@zx-25r12]

Thank you for the video, learned a lot from it.

[@bharath.vkumar5941]

can you explain how to optimize sql queries with AI apllications

[@techsapphire]

please elaborate

[@atabongclement2020]

Good job performance guru.

[@mrrosh4471]

From where did you get the data. Can you share the link of the same data .

[@techsapphire]

You can find it on www.techsapphire.in/index/sql_complex_queries_query_optimization_and_interview_questions_sql_server_2016/0-190

[@mrrosh4471]

Thanks alot

[@vijayaramaraju2856]

Good Explanation, thank you

[@techsapphire]

Thank you

[@shivyogi806]

Superb

[@CevicheChessSalsa]

Very good stuff... Congratulations . keep up the good work !!

[@techsapphire]

Thank you for your feedback.

[@soumyaranjanbiswal632]

thanks for this video..it's helps a lot...

[@datarubik]

thank you so much, it is so helpful

[@techsapphire]

Glad it was helpful!

[@komanekaynchabeleng]

sum([TotalAmount]) over (partition by cat.categoryid order by productname) as runningtotal....?

[@techsapphire]

yes my friend

[@poshendrasarthi]

For team query I have written different query and it's working fine :- "SELECT A.City,B.City FROM #CITY A FULL OUTER JOIN #CITY B ON A.ID>B.ID WHERE A.City IS NOT NULL AND B.City IS NOT NULL"

[@techsapphire]

Your query is returning duplicate records. Please share query for putting data in #team, seems like my #team query is not same.

[@poshendrasarthi]

@@techsapphire Please check below query : SELECT * INTO #CITY FROM ( SELECT 1 AS ID,'ind' CITY UNION ALL SELECT 2 AS ID,'aus' CITY UNION ALL SELECT 3 AS ID,'sri' CITY UNION ALL SELECT 4 AS ID,'Eng' CITY )VW SELECT A.City,B.City FROM #CITY A FULL OUTER JOIN #CITY B ON A.ID>B.ID WHERE A.City IS NOT NULL AND B.City IS NOT NULL

[@chesterchaz135]

@@poshendrasarthi Correct no need for extra join. Even an inner join works. select a.id as FirstID, a.country as FirstC, b.id as SecondID, b.Country as SecondC into team from city a inner join city b on a.id b.id group by a.id,a.country,b.id,b.country select * from team where firstid > secondid order by firstc

[@Krishnagoje11]

Useful stuff

[@techsapphire]

Thank you for your feedback

[@jayshiljain7946]

Great session

[@techsapphire]

Thank you

[@nagatejasvi4376]

Sir one doubt is it applicable to only sql server or MySql ,Postgres etc

[@techsapphire]

General cases are common as these are, how wrong sql logic problem cause performance trouble? But you need to map it to execution plan of other database engine.

[@CrispinValdezOfficial]

This has been extremely helpful thank you so much!

[@techsapphire]

Glad it was helpful! You can learn performance tuning with this video kzbin.info/www/bejne/qmO1YWCumLCqapY

[@narendrakishore8526]

Hi, The link is not supporting as you provided in description

[@techsapphire]

Kindly try again, yesterday there was some domain problem

[@dhanspanicker]

This is a very good tutorial

[@techsapphire]

Thank you for your feedback :)

[@malikehsan147]

It was really helpful. Thank you!

[@techsapphire]

Glad it was helpful!

[@ImpressiveFall]

could you plz share the link to vids on ‘cte’ and ‘pivot’ as mentioned at 35:20. Thanks!

[@techsapphire]

Kindly check link in desctiption

[@ramjadhav6942]

thanks, sir, really its helpful to understand concept

[@ananthasubramanian7355]

Excellent...thank you

[@deepakd1453]

Thankyou very usefull

[@techsapphire]

Welcome 😊

[@Nirvikaar-Nirvana]

Sir , i have one question suppose we have two table hotel by geography and another is booking like booking id, booking date, n all I have to find top 3 hotel by booking in x place in previous year of april,may and june. Please help met get this

[@techsapphire]

you can use dense rank to give ranking

[@karunahebsur5792]

It was to the point and awesome. I subscribed !! :-) Keep making videos .Good luck

[@techsapphire]

Thank you Karuna, for your feedback. I also provide performance tuning private sessions. If you find it interesting, you can opt for it.

[@karunahebsur5792]

@@techsapphire Yes I am interested !!

[@techsapphire]

Ok please ping me on [e-mail] . Will do further discussion on email.

[@techsapphire]

@@karunahebsur5792 If you want, first session on performance optimization on weekend, than please ping me on my email.

[@narendrannaren7416]

Very Useful information !! Thanks Keep it up sir :)

[@techsapphire]

Thanks for your comment

[@abhishekkumar-sl8ow]

Thank you for this video

[@techsapphire]

My pleasure

[@VuTuanThanh-z4k]

Can you give me some questions in the video?

[@techsapphire]

Kindly share your questions

[@VuTuanThanh-z4k]

@@techsapphire questions for SQL queries in the video

[@TheSoulamimukherjee]

Could you please tell me the difference btwn row_number() and rownum. All I know row_number() is analytical func and rownum is a pseudo column but when to use these both?

[@techsapphire]

rownum is just Alias name(column name) given to output so you can give any name like abc or def as Alias. Row_number() is actual function which do the job. If you need more information you can contact me on skype as mentioned in contacts detail of lecture.

[@vipincr189]

Thank for sharing this video..

[@Ram-Deep]

Sir is there any online course or tutorial for learning complete sql...

[@techsapphire]

I provide private sessions. But those are chargeable. Learning new things just read msdn updates.

[@coolquazi7515]

@@techsapphire what are the mode... Is it online or offline? Please share mode of communication so that we can discuss about the classes

[@techsapphire]

@@coolquazi7515 hi Quazi for performance tuning it take 4 sessions. I do session on zoom meeting, also I provide recording of session. For further discussion use my email [e-mail]

[@zezeandjr4110]

Fantastic video, way to go and thx for the link to the scripts.

[@austineanuforo7865]

Would understand better if you only spoke slower. Appreciate the material though.

[@techsapphire]

Thank you for your feedback Austine. I will try to be slow in my future videos.

[@peterbrown376]

Just click the gear icon and set the playback speed to 0.75. Sounds just about right then.

[@Shinoda4013]

Great Video , Thank you !

[@techsapphire]

Thank you for your feedback. I am glad you find video helpful.

[@weberrodrigues7072]

Hi, have, as you show me, how do I import data from mdb bank from acces to sql via code.

[@techsapphire]

You can use import wizard. I will try to create script which can help you with it. Thank you for your query.

[@weberrodrigues7072]

@@techsapphire You would help me a lot, but a lot, making a script, thank you very much.

[@shravankumarreddyreddy7174]

nice man..good job..

[@receps.8396]

Awesome!

[@techsapphire]

Glad you think so!

[@jeffmiller9742]

That was amazing!!! Thank you

[@techsapphire]

You're so welcome!

[@ervinurban7440]

Thanks for the video, it is great!

[@techsapphire]

Thank you for your feedback.

[@-Pheonix.On_PawZ]

I am getting an error while executing the running total query. Is someone facing the same issue?

[@techsapphire]

not exactly, check your SQL version. This feature is supported SQL 2012 and onwards. Check your version using select @@version. A lot of time, I find people taking sql management studio version as SQL version, which is totally wrong.

[@-Pheonix.On_PawZ]

@@techsapphire Thanks.

[@jaganmohanreddyk4234]

can you share complete file

[@techsapphire]

kindly follow link in description

[@techtransformers7464]

thanks for this

[@techsapphire]

You're welcome

[@klayverpaz]

Sensasional, vc é foda

[@techsapphire]

Glad you find it helpful

[@suryanarayankhuntia1813]

Please upload a tutorial on Normal forms .

[@techsapphire]

Surya I will upload it soon.

[@smastan3975]

@@techsapphire bro yr awesome explanation..I want to learn more techniques from you pls share yr mobile or Gmail at [e-mail]..so that I can call and discuss... between I have one doubt..one of my field in the table is decimal (5,10)...I need the records only more than 8 decimal points like 2345.123456789....it could be more than 8...pls answer

[@saireshmiv8863]

Could yu please post the link for backup file.Not able to access the link provided above

[@techsapphire]

try this link please www.techsapphire.in/index/sql_complex_queries_query_optimization_and_interview_questions_sql_server_2016/0-190

[@priyankasarkar6600]

Thank you sir!

[@techsapphire]

You are welcome!

[@amoghthali8399]

Why dont we know what question are you trying to address rather than what the code does? The video would be so informative if we knew what was asked, give it a thought and then look for how to go about it in your video.

[@techsapphire]

Sorry can you please elaborate.If you are talking about my speed than I am really sorry. As I am trying to improve in new lectures. If you are talking about, are these queries important for interviews, than yes. If you know these queries than only you will be able to build on demand complex queries. Please explain in detail, what I am missing.

[@ravikishore4423]

very usefull thanks

[@techsapphire]

Thanks for your comment.

[@trinaygangisetty4955]

the explanation should have been better for some questions. it is very fast and vauge.

[@techsapphire]

Agreed will try to improve

[@ashish21999]

Thanks

[@satyendersaini]

Great....:-)

[@hugokornelis6840]

Ou, I did not thought that so complex queries can be developed :(

[@techsapphire]

Hi Hugo, I am sorry if you didn't find this video as per your expectation. I give training on performance tuning, first session is totally free if you want to try. It will be different from this as it will be purely performance tuning. This video was meant for SQL developer so very less for performance. If you are interested, you can reach in my email [e-mail]

[@maryam4071]

You speak so fast, slower would be more understandable...thx

[@techsapphire]

Thank you for comment. I will try to improve.

[@sushmasushma5461]

queries are good but speed is so fast.

[@techsapphire]

Sorry for trouble, I am trying to improve in my new videos

[@ambarkdi9186]

Bhai mujhe SQL sikha do , I know the concept and have some working experience. Bhai need your help ..job is only my earning and four depends on me ..pls help me ...I will never forget your help and support ..I am writing these words in helpless situation pls help

[@techsapphire]

Practical advice, spend money if you want to learn or spend time.. anything free never stays with you

[@ambarkdi9186]

@@techsapphire Could you please teach me , I will pay the fees ,let me know how I contact u for the same.

[@techsapphire]

@@ambarkdi9186 you can reach me on skype [e-mail]

[@ambarkdi9186]

@@techsapphire thank you so much 🙏

[@sawseries2939]

Love

[@techsapphire]

Glad you liked it

[@kbor8491]

These are not SQL Complex Queries, this level for 1 year experience I cant find any appropriate information, only like this. I am very angry !!!!!! Aaaaaallllllll internet on that unusable information for a pro

[@techsapphire]

Hi Kbor, yes these are mainly to teach building blocks of complex queries. If you want, I do provide training in query optimization, which include actual product optimization. Example content covered is spooling operator, hash match, partition elimination, non sargable queries, bad views, etc.

[@ashschneizel7358]

Going way toooo fasssttt

[@deepanshurastogi007]

Great