Expert Level SQL Tutorial

In this video I will show you what it takes to be a SQL expert. I will cover unions, recursive/hierarchical queries and partitioning.

[@supersql8406]

I have been writing SQL for a long time and it's so refreshing to learn something new AND useful for work! Thank you!

[@jnmnza]

I took, and passed, my 70-761 a few months ago; however, your tutorial would have made studying a lot easier. Great Job!

[@opietwoep1247]

Thanks for sharing. I learned a lot. Been working with sql server since 2000.

[@anvesh12]

U were at top notch in explanation and ur clarity throughout 🔥

[@altareq24953]

You teach incredibly great..... expert level but yet so simple and easy the way you teach. awesome James Oliver

[@maximessina9999]

Your videos are a privilege. Your channel is a rich source of knowledge.

[@JamesOliver]

Thanks so much for the kind words. I really appreciate it. Keeps me motivated! :)

[@PawsAndKeys]

THANK YOU! Finally an "Expert Level" video that doesn't teach to a beginner. It's so hard to find actually advanced information amidst all the beginner's tutorials.

[@kanakbarua1122]

@aa

[@kanakbarua1122]

@@

[@ramm4012]

Great Presentation, learned something new. Great Job Oliver. Thank you

[@nandkumargaikwad9014]

so much admire , thank you

[@johnmichelotti5373]

Been using SQL as a functional ERP resource so I don’t know if I am an expert. This is very well done and IMHO not too far out of reach for someone who has the basics. Where have you been hiding?

[@MrFedX]

Excellent video! I was researching recursive queries for a future problem but now I will refactor another weird query using partitioning instead. 😄

[@lostnight4322]

github.com/sagittaracc/suql/blob/master/README.md

[@justinrocky6645]

kzbin.info/www/bejne/aqu9maKlfLFge9k

[@ellukayou]

Finally I get it!!

[@rodneyoganga6693]

Thank you Oliver for this great educative video

[@emmanuelaolaiya]

Thanks so much for this video. This is very useful and helpful

[@midnightrun335]

great work, expert explanation of expert level concepts.

[@official-DRFRE]

Awesome tutorials especially partition by, this will definitely help me my work.

[@adipai]

Thank you! Very nicely explained!

[@benjolin1623]

I like the video. Although everyone is at different levels. Id like to see some stuff tricks, ways to use xml to generate html documents, ect.

[@anawadtom6011]

Good video. I'm still processing how this works. Thanks

[@dawnhuttoe7204]

Thank you so much 💓

[@advancewebdevelopment9802]

Thanks

[@hhbbhvvbjhbbyjj]

excellent video

[@abcxyz6606]

Great videos, and very nicely explained. Please do more. TY!

[@DanielHernandezCa]

Thank yoj

[@hadireg]

thanks for this!! II got it all ! you made my day!👍🙏

[@JamesOliver]

My pleasure. Appreciate the positive feedback.

[@mattcoleman6780]

Beautiful

[@KgRu1eS]

Amazing! 🤯

[@kosmipologe]

Awesome! I like the recursive stuff! Great! Thanks a lot for this!

[@justinrocky6645]

kzbin.info/www/bejne/aqu9maKlfLFge9k

[@老王-d5d]

Very good video, wish I saw this before the died interview LOL

[@justinrocky6645]

kzbin.info/www/bejne/aqu9maKlfLFge9k

[@dalton_hfg]

Great tutorial. Thanks.

[@ruoxima3773]

EXCELLENT!!!!

[@hadibq]

👍👍

[@sriniwaasvemula9349]

so helpful tutorial....thanks for share...if possible plz share data in SQL format to practice....

[@mertcancoskun4658]

Top level content, thank you moneygrip.

[@JamesOliver]

Thanks! I really appreciate it.

[@snowglider400]

this is great

[@nilohernandez7508]

Sir excelent. Can you share the database so that i can follow what you are doing. Thanks

[@foysalmamun5106]

Nice Thank you lot

[@vipin_thegame]

Thank you, very useful!

[@JamesOliver]

You're very welcome! :)

[@justinrocky6645]

kzbin.info/www/bejne/aqu9maKlfLFge9k

[@basharjirjees8815]

I honestly don't get the recursive example as for it to work the EmployeeHierachy has to always change for it to check and find the next boss and their employees, and also how some employees are on same level but have different bosses?

[@50tigres79]

Wish the videos are higher definition. Other than that the channel is awesome.

[@drillcream]

great video! But I can hear the keyboard screaming after every stroke lol

[@biljam972]

Thank you for this! I am still struggling with recursion and partition so this is very helpfull for me.

[@JamesOliver]

You're very welcome. Glad to help! :)

[@granand]

Thank you very much. Please let me know if you have a full-fledged course? Looking to write user-defined functions with user input and create reports comparing databases from two different environments to show 2-way differences.

[@taweesakarchawamaitreekool4078]

Thank you :)

[@syednayyar]

what do mean by materialized at 12:29

[@standom2390]

Where to get a database for drills?

[@chriskeo392]

Montana!

[@walkermom100]

Why are there 2 different tables with the same type of data. Should be 1 table with a column identifying the order type. Online or store.

[@djp_video]

04:15 That should really be a NULL, not an empty string. And adding matching aliases to any columns without names or dissimilar names is best practice.

[@kaicheng7271]

Thanks for the great video! A quick question, arround 22:09 where are doing the PctTotal, can't we replace the 3rd line "TotalDue/Sum(TotalDue) OVER (PARTITION BY OrderDat) as PctTotal" with ""TotalDue/TotalSales as PctTotal" since we already aliased "Sum(TotalDue) OVER (PARTITION BY OrderDat)" as TotalSales? Thank you again!

[@JamesOliver]

I'm glad you enjoyed. In this video I'm using SQL Server and the alias cannot be reused, however, in certain databases you are absolutely right and this can be done.

[@justinrocky6645]

kzbin.info/www/bejne/aqu9maKlfLFge9k

[@siddharthseksaria]

If I couldn't find the top of the hierarchy, How do I write Anchor query in this case?

[@wenkexu6363]

Why Level has [ ]? how to use eh. exactly? I need some help here

[@zackjones8681]

Nice samples. It's a little confusing because you have the same employee name assigned to two different employeeKeys (David Bradley is 7 & 8 for example)

[@fun2badult]

How about the fact that there are duplicate names of employees with different employeekey numbers??

[@ajtaffy]

Looks like he used UNION ALL which will contain duplicates. If he'd had just used UNION there wouldn't have been duplicates. I'm not sure why he used UNION ALL...

[@hadireg]

loved these examples!! Many Thanks!! (sub #755 😊)

[@JamesOliver]

Thank you very much. I appreciate every sub! :)

[@anthonyrc6618]

Just wondering: is there an official list of detailed skills by level for SQL? Thanks for the video btw :D

[@JamesOliver]

Good question. Not that I know of but if you find one let me know! :) Thanks for watching.

[@justinrocky6645]

kzbin.info/www/bejne/aqu9maKlfLFge9k

[@ivanbzg8955]

Can someone explain to me whats the advantage of SQL vs dplyr package from R?

[@lennovo9525]

I'm just starting out, but my observations have been that R is data science research tool that offers great data transformation and visualization options, that Sql won't do or does very tediously. SQL can store large data very well, it's stable. Its an enterprise level product where R could not do, not very well any how. I think its not a question of which is better but how you can leverage both. Go easy on me, i am a Jr analyst.

[@tiagosilva856]

That's expert level? Sooo, I must be God in SQL!

[@benjolin1623]

It is pretty advanced stuff. Although I am also hoping to see some stuff, union, except, intercepts and XML.

[@TechMarketers]

Hi James, have you ever thought about creating sql intermediate/advanced course? I learn sql, use mysql workbench but I need new database and good examples, so It would be very useful for me :)

[@JamesOliver]

As a matter of fact recently I have thought about creating a class. I started putting together a course outline. Anything specific you would be interested in learning?

[@TheDataMaestro]

Great idea! I'm in the middle of each of 3 SQL courses and we never get to the meaty stuff. The instructors struggle with explaining self-joins, for instance.

[@findthetruth3021]

I have a question. I have 200 rows, and I want to delete from row 150 until 200. So, how can I choose from 150 to 200?. I will appreciate that if anyone can help me coz there is not such video on KZbin.

[@Ramreddy-kq5or]

This can be done in multiple ways. Apply row_numbers to each row, then write delete statement with where row_number>50, Im just a intermediate in in SQL. I guess this works for you

[@benjolin1623]

​@@Ramreddy-kq5or select top 50 blah... offset 150

[@adventureswithfrodo2721]

The two sales tables are poor. It should all be in one table with a sales type flag. The you really need to have a star table structure etc.........

[@JamesOliver]

This was intentional and only to give context to the demonstration. The tables are not meant to reflect a realistic design.

[@pdro283]

This is Very Basic actually

[@ConcernedCitizenFiveTowns]

This is expert? Seems level 4 of 10 difficulty

[@joyo2122]

more like noob lvl sql

[@JamesOliver]

Tell me.. what do you consider advanced?

[@MrCoder101]

Please get microphone away from keyboard or use a quieter keyboard or don't hit the keys so hard. I want to hear you and I turn it up and then I have to turn it down when you start hitting enter.

[@JamesOliver]

Sorry about that. Will keep that in mind for future videos. I have a thing for mechanical keyboards but unfortunately they can be loud.

[@TheDataMaestro]

Actually, the noise keeps me focused as if I'm in the same room with James.

[@danicarovo8818]

I'm binging your videos at 1.5 speed 1 week before my database exam.:D Thanks a lot, great explanation, esp about the recursion, I needed that one!

[@JamesOliver]

You're very welcome. Good luck with the exam!

[@justinrocky6645]

kzbin.info/www/bejne/aqu9maKlfLFge9k

[@theway5258]

Thank you Oliver! Would you mind to add more examples of the expert SQL level, please?

[@JamesOliver]

The Way sure!

[@leodigiacomo]

Hi James, do you have any recommendation for courses (free or paid) on backend SQL Development?

[@Helloimtheshiieet]

1) this isn’t expert level at all 2) if this is expert level I’m on god mode tier

[@codyhayes2121]

I feel like Amy Albert actually is real.

[@eb_reviews]

This helped me a lot! Thanks for the explaining it so clearly!

[@Irina_M1989]

clear and to the point explanations with easy to understand examples. Thanks!

[@chandramouli1993]

Could you help with writing a query to find the total sales of employees reporting to each employee? Emp_Id Total sales of employees reporting to Emp_Id

[@sinvalfelisberto]

Thank you kindly! Greetings from Brazil!

[@MrTxematrix]

RATIO_TO_REPORT

[@johndimartino4453]

Thanks for the videos. I'mportant information for sure.

[@sunilsingh-ji8iz]

Thanks a lot for this video

[@sohailalam499]

I m having a problem. Need help in SQL. Can anyone help me?

[@piyushrajmishra]

Very Informative. Thanks.

[@AnthonyDev]

Thanks, man. Great videos, I'm recycling my sql skills.

[@gracehe9792]

Thanks James. It's a helpful video. Looking forward to more of them in your channel.

[@medhannioui1167]

where can I download the datasets?

[@michellefitzpatrick8296]

So well explained! Thank you

[@DatzAdam]

I don't like you Ken Sanchez, James Oliver is the only NULL I want

[@JamesOliver]

:)

[@Ramreddy-kq5or]

Love you bro :)

[@JamesOliver]

Haha appreciate it :)

[@anastasiosvitsios6311]

Thank you James! Where can I find this table in order to practice myself?

[@JamesOliver]

You can download the AdventureWorks2012 SQL Server database for free from Microsoft.

[@Hawkeye10000]

@@JamesOliver can we use this SQL database for practice on mobile?

[@JamesOliver]

@@Hawkeye10000 what do you mean by mobile sql?

[@Hawkeye10000]

@@JamesOliver what I meant to ask was "Can I download this sql database practice application adventureworks2012 on mobile for sql practice?" The thing is: my laptop has gone for repairs and it's gonna take A WHILE. So can we download this Microsoft sql application on mobile for practice? Thanks. Regards.

[@JamesOliver]

@@Hawkeye10000 That would be really cool but I’m not sure that can be done.

[@j.maginnenu6291]

Do you do Oracle SQL lang instead? I dont see that

[@JamesOliver]

This is t-sql so will work on like a SQL Server.

[@abhidhatrak1]

you dont explain the queries in detail. and the annoying noise of your keypress..

[@JamesOliver]

I thought I explained them enough. Sorry you feel that way.

[@TheDataMaestro]

There is a lot going on. I struggle with understanding it also, but I would suggest watching the video a few times and write your own similar queries against your own database.