SQL for Data Analytics - Learn SQL in 4 Hours
Вставка
- Опубліковано 31 лип 2024
- Course Links, Problems, & Notes 👉 lukebarousse.com/sql
⚠️ Fix Database Load Issue in Ch 2 👉 lukeb.co/sql_error
Thanks to Kelly Adams for her work in producing this course 🎉🙌
👩🏻💼 Kelly's LinkedIn: / kellyjianadams
🌐 Kelly's Website: www.kellyjadams.com/
Ch 1️⃣ - Basics
==============
00:00 - Welcome
03:43 - What is SQL
10:44 - Intro to Course
16:51 - The Basics
33:58 - Comparisons
41:16 - Practice Problem 1
45:21 - Wildcards
48:47 - Alias: AS
51:04 - Practice Problem 2
53:58 - Operations
59:34 - Aggregation
1:06:25 - Practice Problem 3
1:08:42 - NULL Values
Skipped - Practice Problem 4
1:10:03 - JOINS
1:20:11 - Order of Execution
1:22:31 - Practice Problem 5
Ch 2️⃣ - Advanced
==============
1:26:14 - Setup PostgreSQL
1:32:43 - IDE Install: VS Code
1:44:03 - Data Types
1:47:29 - Manipulate Tables
1:59:54 - Database Load
2:09:55 - Date Functions
2:20:18 - Problem Problem 6
2:24:54 - CASE Expression
2:30:16 - SubQueries and CTEs
2:42:12 - Practice Problem 7
2:50:02 - UNION Operators
2:54:19 - Practice Problem 8
Ch 3️⃣ - Project
==============
2:58:01 - About the Project
3:02:07 - Create the Repository
3:14:39 - Query 1 - Top Paying Jobs
3:20:31 - Query 2 - Top Paying Job's Skills
3:27:59 - Query 3 - In-Demand Skills
3:34:02 - Query 4 - Top Paying Skills
3:41:25 - Query 5 - Most Optimal Skills
3:53:01 - Share on GitHub
4:05:03 - Share on LinkedIn
Social Media / Contact Me
======================
📫Newsletter: www.lukebarousse.com/
👨🏼💼 Linkedin: / luke-b
🅧 X/Twitter: / lukebarousse
🌄 Instagram: / lukebarousse
⏰ TikTok: / lukebarousse
#sql #postgresql #sqlite - Наука та технологія
Updates for Video 👇
===========================================================================
2:06:55 - Database Load Issues
WATCH THIS VIDEO FOR A WALKTHROUGH OF THESE INSTRUCTIONS: lukeb.co/sql_error
NOTE: If you are having issues with permissions. And you get an error like: "could not open file
"C:\file_path\job_postings_fact.csv" for reading: Permission denied."
1. Open pgAdmin
2. In Object Explorer (left-hand pane), navigate to `sql_course` database
3. Right-click `sql_course` and select `PSQL Tool`
- This opens a terminal window to write the following code
4. Get the absolute file path of your csv files
a. Find path by right-clicking a CSV file in VS Code and selecting “Copy Path”
5. Paste the following into `PSQL Tool`, (with the CORRECT file path)
\copy company_dim FROM '[Insert File Path]/company_dim.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',', ENCODING 'UTF8');
\copy skills_dim FROM '[Insert File Path]/skills_dim.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',', ENCODING 'UTF8');
\copy job_postings_fact FROM '[Insert File Path]/job_postings_fact.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',', ENCODING 'UTF8');
\copy skills_job_dim FROM '[Insert File Path]/skills_job_dim.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',', ENCODING 'UTF8');
===========================================================================
2:06:55 - Duplicate Key Error
NOTE: If you are getting the following error (below) even after following the steps above you need to restart over and drop the database:
ERROR >> duplicate key value violates unique constraint "company_dim_pkey"
ERROR >> could not open file "C:\Users\...\company_dim.csv" for reading: Permission denied
1. Drop the Database
DROP DATABASE IF EXISTS sql_course;
2. Repeat steps to create database and load table schemas
3. Follow steps above for copying CSV files into the sql_course database
===========================================================================
3:08:15 Git Error - "Tell me who you are"
NOTE: Some users, setting up git for the first time are receiving the following error:
Please tell me who you are.
Run
git config --global user.email "you@example.com"
git config --global user.name "Your Name"
1. Open your terminal.
2. Set your Git identity by :
git config --global user.email "[insert email address]"
git config --global user.name "[insert your name]"
3. Continue video
Solved it! I found the solution on stack overflow.
'Assuming the psql command-line tool, you may use \copy instead of copy.
\copy opens the file and feeds the contents to the server, whereas copy tells the server the open the file itself and read it, which may be problematic permission-wise, or even impossible if client and server run on different machines with no file sharing in-between.
Under the hood, \copy is implemented as COPY FROM stdin and accepts the same options than the server-side COPY.'
I used SQL Shell (psql). Make use you typed \copy instead of COPY
@@LukeBarousseI've encountered the same issue with Mac but the solution you've posted also worked. Thank you very much!
I have the same problem on MAC. I'm a fresher, please help! @LukeBarousse
yea same
@@attiquetahir
@@mrbartuss1 Thanks for this solution I updated this as the new solution
i have waited for such course from you for my whole life. Thank you
Luke!!!! Just found this video and can hardly wait to get started this evening. Thanks in advance to you and Kelly Adams for putting this course together. Was wondering what I was going to explore next, now I know. Have a fantastic week! 😎
This is Absolute GOLD. Thank you Luke and Kelly for providing us with this for anyone. You Guys are awesome!!
Pure excelence! Started just now the full course and really looking forward to improving my SQL skills. Thanks once again Luke. 🙌
SELECT column1, column2 -- Silly
FROM table_name -- Frogs
WHERE column1 = 'value' -- Wear
GROUP BY column2 -- Green
HAVING COUNT(column2) > 1 -- Hats
ORDER BY column1 -- On
LIMIT 10; -- Lakes
Great job, I use alias with my Select statement.
I can’t really understand this, because no syntax is “ultimate”.
I have seen syntaxes such as :
WITH X as (
Var1 as defined..
Var2 as defined..
…)
Select Y FROM X
LIMIT Z
I have been trying to learn basics of SQL for quite sometime now and this comes at a right time as it's easy to understand and very resourceful.
Thanks Luke for breaking down every step and explaining everything about SQL to millions of people around the world.
by watching your different videos and the analysis done on your websites, learning SQL is essential in the world of data. Lots of tutorials on the web, I didn't find the motivation to start, but finally here is the SQL tutorial from my favorite channel. no more excuses, I can't wait to start 😀
Thank you Luke, I can say awesome tutorial before watching! appreciate it
Ive been working as an analyst for over a year now and I keep learning new things everyday thank you Luke.
Amazing content! This course is exactly what I was looking for, can't thank you enough !
Thank you Luke. You made video just in time that I’m looking for SQL full deep dive course. I used to learn Data science boot camp before, but the boot camp just provided SQL basic thing and course time limit
Thanks for doing this ! My SQL skillset was thouroughly refreshed 👍
Thank you Luke. Took me around a month to finish this but now I know SQL very well!
Gotta say, What i really appreciate about this is that you include when you mess up. its a great teaching point for others to know that someone like you still messes up. its not about being perfect with code, its about knowing how to troubleshoot and debug the code. to me, that matters more than always getting it right the first time.
completely agree with you!
4h of Luke? I can binge on that, but not today!
First few minutes... Not just clear as Cristal (& as tasty!) With real serious info but delivery with wit & humor..
Picture of favorite uncle in picture frame??
Holy cow! This is goes in depth of SQL to delve into. I have been trying to find a really good up to date course that is 4 hours long! You did an incredible job. My boyfriend and I are learning SQL together. We changed career paths and want to become a data analyst. THANK. YOU!! 🎉❤😊
Clear, Easy, Simple. You really made me feel confident about this. Thanks a bunch
A few minutes in, I am already excited. Can't believe how excited I am. Thanks for taking the time
I'm a college student trying to break into data analytics, really appreciate the time and effort put into this video. Excited to learn more about data, THANK YOU!!
You sir are a godsend. Will definitely work towards supporting the course.
This is the best video of sql that i found and i searched a lot. Thank you!!!
Absolutely spectacular video! Not finished yet but finding your SQL teaching very very comprehensive indeed. Just wanted to thank you from the bottom of my heart for demystifying this!!
This is incredible! Thank you so much!
Holy cow, need to set aside some time to walkthrough this beast! thanks Luke
Great content and so generous of you to make it free!!! Bought your notes & problem sets to support. Looking forward to more interesting contents🤩
Thank you for taking the time to Share. Wish I had this video years ago in my IT youth!
Thank you so much for this great video. Really enjoy the 4 hours ride. Much of beneficial knowledge to gain. Enjoy every single second of the video.
I was looking for a SQL tutorial from a professional data analyst and here it is..🎉
Will start soon..
YAAASSSSS!!!! Thank you so much for putting this together! I haven't watched it yet, but I assume it will be as good as the rest of your content 👍
I'm learning to be a data analyst and this course has been great. Thanks Luke.
Really very helpful resource for everyone out there! ❤
I just started my journey in data analytics to change career paths in early January. I'm almost done the IBM Data Analytics course on Coursera. I'm so glad this came out as the course has way more python than SQL and I planned to look for a course soon. THANK YOU!! 😄
I wish you success on your journey!
Same here, but i'm the beginning. But I started a SQL course from University Davis and I find it so complete.
Im halfway through the Google data analytics course on Coursera and it has more SQL than Python
Can u provide more info on Davis course?@@alicecooper2473
So the IBM one has Python
Starting to learn sql and this just comes very handy at the right time!!
Luke, THANKS FOR THAT!
You guys did such a great job.
We’ll never be able to express our gratitude enough 🎉🎉
Such an amazing way to hone my SQL skills 💪🏻
Note: extra_hours at 59:13 shows wrong data when a 16h work day is in question (shows 0, should be 8). Corrected code for the extra_hours column would be: (hours_spent % 8) + (FLOOR(hours_spent / 8) - 1) * 8 AS extra_hours. Great material. Cheers!
your content is fresh and pure gold love you man !
Got my Sunday covered for this week!!
This is by far one of the best and most complete SQL tutorial for Data Analytics, minus the course purchase option. Luke is one of my favorite data nerd, thank you. May the data be with you! 🤓
🖖🏼
Such a good course and project! Really appreciated!
This course has been really helpful to me. I am at a point where I need to take my data analysis skills to the next level and I would not have been able to learn without this course. I have reached the advanced level and I am really learning. Thank you so much. I encourage everyone to purchase this course and do the exercises ;)
One of the best course I met around😍 Thanks @Luke
2 mins in and i already love you! thank you brother Luke
I was thinking to learn sql today and you dropped this like what are the chances damn. Thank you
Hey! From Brazil here, please make more of those videos bro, they´re better than most expensive courses that we have here ! Thanks!
Amazing video! Thanks Luke
Only video on youtube for advance SQL queries. Really helpful
Thank you very much for your job! Love your content.
Thank you Luke. This is a very helpful video. I have learned a lot from it.
Thank you I was planing to learn SQL:) - you read my mind.
Thank you so much for this great video. I wanted to start learning SQL and it was kind of scary for me, but you explained everything with great detail and everything was perfect.
Wooow good timing bro, amazing!!
Thank You Luke, I really apperaciate it.
Probably one of the best 25$ i spent in my entire life. Problems are harder than capstone project itself and you also greatly outlined all the important topics and introduction to the github. Really appreciate Luke, thank you and Kelly for your amazing work!
Learned a lot. It's great! Thanks!
Great course - totally worth the upgrade for the exercises and the additional notes. Best training "bang-for-buck" that I have seen for a long time. The video is 4 hours long, but it took me 3 days to get through it with all of the additional exercises (because I am slow) and tangents (because I am too curious). Really opened my eyes to more than SQL and data analysis.
Heck yeah! Appreciate the feedback Jay! Glad to hear you'd recommend it!
Awesome work brother! Excellent!!
Dude! halfway through this course. I love the depth and detail you and Kelly have put into this course! I also like that you use VS Code for the second part of the course. I will be setting it up to learn Python when your Python course is out. Please continue making courses of similar quality and depth. I will be buying them as soon as they are available.
That's a very helpful content. SQL is easy as someone from an engineering background dealt with SQL for a few months I thought I'm still a beginner, but as I skimmed through this video I found I already know everything. All by practicing real case problem solving and self-learning.
Thank you so much! I appreciate you immensely. You are an awesome human
Thank you so so much ! It is such an informative and easy to understand video! It is literally so nicely explained!!!!!!!!
Thank you so much for sharing your knowledge with us.This course was really very helpful and again thank you
I'm in school for data analytics and this course has been FAR more helpful than any of my classes so far. Thanks a lot!
schools never helped in anything man.
they just want our money
Thank You for this Amazing video Luke.
Legend. Thanks for this. Such a helpful course.
Great job, thanks, Luke, so helpful. I am the PM and going to include SQL into my resume.
Amazing and useful video, thank you for your work and for sharing your knowledge
Just on time this vedeo appears thank you 🎉
Thank you so much for this!!!!
this is GOLD!
How is this FREE?!? Holy moly, this is fantastic. I've been looking into Data Analyst stuff this evening (looking for career change/expansion) and you and this course were referenced in two different places I ended up. And I can see why.
And your "paid" stuff is $25??? THANK YOU for what you're doing.
This is amazing, thank you!!
Thanks for sharing your knowledge ❤
Thanks for this content. I'm using this as a refresher course after learning SQL last summer as part of a data analyst bootcamp. The job market is difficult in my region (South of France) but I've got to keep "buggering on" 😅 if I'm going to land a job...
Thank you for posting this video, Luke. I appreciate your hard work! I bet it took you ages to edit this!
Ha! Actually had to hire an editor to help edit this; way more then I could handle
It’s only 25.usd to support, that’s a small contribution, happy to “help” but it’s you helping the world. Hoping you’ll find the time to do this also for Python, love to follow that one as well. Keep on doing what you are doing, thanks Luke!
Currently on Project 5. I really enjoyed watching and doing the projects. It wasn't all smooth but it came out nicely
Thank you so much for this video 🙏🙏
Thank you so much, so great!!!
Good job doing this. You are awesome 🎉🎉
It was an awesome project and the best part was editing the readme file and correcting all the hick-ups i found myself in along the way. I have almost completed the Google Data Analytics certificate on Coursera and they have SQL in it but this is so much more insightful. Thank you so much for this video.
Heck yeah, i'm glad it was able to help you go deep er with learning SQL
Amazing! Thank you!
Thank you luke for this video it really helpful and informative
more of this Luke! Im, gonna buy the resources! more power
you are my hero blue!!! thanks!!
Great job. Thank you
all of this gems for free, you're a legend, thank you is not enough , but really, thank you so much...
you are amazing..great work
Insane that this is free. Love it
Learning immediately
THANK YOU FOR THIS
woow. Nice one luke
Love it! ❤
This is much better than the boring 20 hour course i followed on Udemy.
gosh that mail notification got me a million times :D, thank you for the great content
🤣; and thanks!!
Just want to say that I think that your teaching and editing style for this SQL course was very effective.
Clear teaching and nice editing, transitions and course structure.
Might be the most effective course that I've gone through yet.
Thanks David!!
very cool thanks!!!
Thank you so muchhh man
Thank you very much 😊
I love this Guy!♥