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.
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?
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!
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.
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.
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.
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?
@@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.
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
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.
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...
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 :)
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?
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.
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)
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 UA-cam.
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
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.
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.
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.
@aa
@@
I have been writing SQL for a long time and it's so refreshing to learn something new AND useful for work! Thank you!
I took, and passed, my 70-761 a few months ago; however, your tutorial would have made studying a lot easier. Great Job!
Thanks for sharing. I learned a lot. Been working with sql server since 2000.
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!
You're very welcome. Good luck with the exam!
ua-cam.com/video/4zZhooGL-Ew/v-deo.html
You teach incredibly great..... expert level but yet so simple and easy the way you teach. awesome James Oliver
Thank you kindly!
Greetings from Brazil!
Thanks for the videos. I'mportant information for sure.
U were at top notch in explanation and ur clarity throughout 🔥
Very Informative. Thanks.
Great Presentation, learned something new. Great Job Oliver. Thank you
Thank you Oliver for this great educative video
Your videos are a privilege. Your channel is a rich source of knowledge.
Thanks so much for the kind words. I really appreciate it. Keeps me motivated! :)
great work, expert explanation of expert level concepts.
clear and to the point explanations with easy to understand examples. Thanks!
so much admire , thank you
Thanks so much for this video. This is very useful and helpful
Awesome tutorials especially partition by, this will definitely help me my work.
Thanks James. It's a helpful video. Looking forward to more of them in your channel.
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.
Thank you so much 💓
Good video. I'm still processing how this works. Thanks
excellent video
Great videos, and very nicely explained. Please do more. TY!
This helped me a lot! Thanks for the explaining it so clearly!
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?
Thank you! Very nicely explained!
Great content! I know this is a older video but do you have a link to the datasets?
Excellent video! I was researching recursive queries for a future problem but now I will refactor another weird query using partitioning instead. 😄
github.com/sagittaracc/suql/blob/master/README.md
ua-cam.com/video/4zZhooGL-Ew/v-deo.html
So well explained! Thank you
Thanks, man. Great videos, I'm recycling my sql skills.
Amazing! 🤯
so helpful tutorial....thanks for share...if possible plz share data in SQL format to practice....
Great tutorial. Thanks.
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!
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.
ua-cam.com/video/4zZhooGL-Ew/v-deo.html
Awesome! I like the recursive stuff! Great! Thanks a lot for this!
ua-cam.com/video/4zZhooGL-Ew/v-deo.html
thanks for this!! II got it all ! you made my day!👍🙏
My pleasure. Appreciate the positive feedback.
Beautiful
Thank you Oliver! Would you mind to add more examples of the expert SQL level, please?
The Way sure!
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.
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.
what do mean by materialized at 12:29
Finally I get it!!
Top level content, thank you moneygrip.
Thanks! I really appreciate it.
EXCELLENT!!!!
Thanks a lot for this video
Thanks
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?
Thank you, very useful!
You're very welcome! :)
ua-cam.com/video/4zZhooGL-Ew/v-deo.html
Very good video, wish I saw this before the died interview LOL
ua-cam.com/video/4zZhooGL-Ew/v-deo.html
Nice
Thank you lot
Thank you James! Where can I find this table in order to practice myself?
You can download the AdventureWorks2012 SQL Server database for free from Microsoft.
@@JamesOliver can we use this SQL database for practice on mobile?
@@Hawkeye10000 what do you mean by mobile sql?
@@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.
@@Hawkeye10000 That would be really cool but I’m not sure that can be done.
this is great
Hi James, do you have any recommendation for courses (free or paid) on backend SQL Development?
Wish the videos are higher definition. Other than that the channel is awesome.
Where to get a database for drills?
Sir excelent. Can you share the database so that i can follow what you are doing. Thanks
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
If I couldn't find the top of the hierarchy, How do I write Anchor query in this case?
Why Level has [ ]? how to use eh. exactly? I need some help here
Thank yoj
Love you bro :)
Haha appreciate it :)
Thank you for this! I am still struggling with recursion and partition so this is very helpfull for me.
You're very welcome. Glad to help! :)
Can someone explain to me whats the advantage of SQL vs dplyr package from R?
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.
How about the fact that there are duplicate names of employees with different employeekey numbers??
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...
Thank you :)
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.
where can I download the datasets?
Do you do Oracle SQL lang instead? I dont see that
This is t-sql so will work on like a SQL Server.
Just wondering: is there an official list of detailed skills by level for SQL?
Thanks for the video btw :D
Good question. Not that I know of but if you find one let me know! :) Thanks for watching.
ua-cam.com/video/4zZhooGL-Ew/v-deo.html
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 :)
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?
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.
I m having a problem. Need help in SQL. Can anyone help me?
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)
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 UA-cam.
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
@@Ramreddy-kq5or select top 50 blah... offset 150
👍👍
great video! But I can hear the keyboard screaming after every stroke lol
loved these examples!! Many Thanks!! (sub #755 😊)
Thank you very much. I appreciate every sub! :)
Montana!
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.........
This was intentional and only to give context to the demonstration. The tables are not meant to reflect a realistic design.
That's expert level? Sooo, I must be God in SQL!
It is pretty advanced stuff. Although I am also hoping to see some stuff, union, except, intercepts and XML.
RATIO_TO_REPORT
I feel like Amy Albert actually is real.
This is Very Basic actually
I don't like you Ken Sanchez, James Oliver is the only NULL I want
:)
1) this isn’t expert level at all 2) if this is expert level I’m on god mode tier
This is expert? Seems level 4 of 10 difficulty
more like noob lvl sql
Tell me.. what do you consider advanced?
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.
Sorry about that. Will keep that in mind for future videos. I have a thing for mechanical keyboards but unfortunately they can be loud.
Actually, the noise keeps me focused as if I'm in the same room with James.
you dont explain the queries in detail. and the annoying noise of your keypress..
I thought I explained them enough. Sorry you feel that way.
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.