PostgreSQL Tutorial Full Course 2022
HTML-код
- Опубликовано: 13 июл 2024
- I wrote a Techno-Thriller called Whispers if you're interested: amzn.to/4cfdtNW
I'd greatly appreciate a review for it if you have time.
I provide here in this PostgreSQL tutorial a full course you can use to master PostgreSQL. Postgres is an object relational database that is just as fast as MySQL that adheres more closely to SQL standards and excels at concurrency. Postgres is also superior at avoiding data corruption.
Postgres also provides more advanced data types and allows for the creation of custom types, operators and index types.
Postgres is normally the best option when extensibility, scalability and data integrity are most important to you. The table of contents below provides timestamps to everything I cover.
Code & Transcript Here : github.com/derekbanas/postgre...
Best Book on PostgreSQL : amzn.to/3rkNX3e
MY UDEMY COURSES ARE 87.5% OFF TIL May 1st ($9.99)
➡️ Python Data Science Series: Highest Rated & Largest Python Udemy Course + 56 Hrs + 200 Videos + Data Science bit.ly/Master_Python_52
➡️ C++ Programming Bootcamp Series: Over 23 Hrs + 53 Videos + Quizzes + Graded Assignments bit.ly/C_Course_52
➡️ Golang Course: 15 Hrs + Web Apps + Core Language + Create a Blog + PostgreSQL + Problem Solving bit.ly/go-tutorial10
#postgresql #postgres #fullcourse
TABLE OF CONTENTS
00:00 Intro
00:30 Why Use Postgres?
01:13 What is a Database
03:12 Change Database Theme
03:53 Create a Database
04:46 Design a Database
05:50 Turn Invoice into a Database
07:04 Make a Table
12:13 Data Types
16:36 Adding Data to Table
18:15 To See Data
18:25 SELECT
19:19 Create Custom Type
20:48 Change Column Data Type
22:58 Thinking About Tables
25:37 Breaking Up Tables
27:03 Primary & Foreign Keys
32:40 Foreign & Primary Keys
33:28 Altering Tables Many Examples
53:00 Getting Data from One Table
53:40 Where
54:30 Conditional Operators
55:48 Logical Operators
58:12 Order By
59:32 Limit
1:01:45 GROUP BY
1:03:11 Distinct
1:05:00 Getting Data from Multiple Tables
1:05:21 Inner Join
1:08:50 Join 3 Tables
1:13:15 Arithmetic Operators
1:13:45 Join with Where
1:14:55 Outer Joins
1:17:03 Cross Joins
1:18:16 Unions
1:19:27 Extract
1:21:05 IS NULL
1:22:03 SIMILAR LIKE & ~
1:29:25 GROUP BY
1:31:14 HAVING
1:32:18 AGGREGATE FUNCTIONS
1:34:22 WORKING WITH VIEWS
1:45:01 SQL Functions
1:49:00 Dollar Quotes
1:50:06 Functions that Return Void
1:52:38 Get Maximum Product Price
1:53:39 Get Total Value of Inventory
1:54:26 Get Number of Customers
1:56:15 Named Parameters
2:01:30 Return a Row / Composite
2:03:38 Get Multiple Rows
2:07:08 PL/pgSQL
2:11:35 Variables in Functions
2:15:55 Store Rows in Variables
2:19:17 IN INOUT and OUT
2:21:01 Using Multiple Outs
2:25:56 Return Query Results
2:33:42 IF ELSEIF and ELSE
2:38:48 CASE Statement
2:42:01 Loop Statement
2:45:20 FOR LOOP
2:48:34 Result Sets, Blocks & Raise Notice
2:51:11 For Each and Arrays
2:53:20 While Loop
2:54:54 Continue
3:01:34 Stored Procedures
3:09:35 Triggers
3:29:25 Cursors
3:39:45 Installation
This guy is one of the OG programming tutorial guy on YT. Seriously, he helped me out so much way back in 2016-17 when I was starting out with Java, and he's still pumping out videos. Legend.
Thank you for taking the time to tell me I helped :) I'm happy I could help
@@derekbanas the grind never ends
Facts!! He helped me get my head around programming when I was crazy enough to start a computer science degree 😂 good to see him still doing what he does. I am again here for seeking some help
@derekbanas is one of the GOATs when it comes to programming tutorials. He taught me PHP, MySQL, Yii platform and now POSTGRESS!
respect man!! from Nigeria
Agreed, king of the greybeards!
Hey derek. Just wanted to let you know that from my very first engineering class ten years ago to today where i am a full fledged software engineer you have really helped out.
Thank you for your contributions to society.
Another nice, thorough tutorial... A lot of material is covered over a short period of time. The pacing was good. Quick and to the point, using plausible scenarios to emphasize the concept. The thing I appreciate most is having a good explanation of Postgres broad capabilities, which are impressive. I will certainly use this video as a reference. I feel I have a good conceptual understanding of Postgres now. Well done. Thanks.
Long tutorial with many things covered. I referred the documentation once in awhile but your content covers quite a lot, especially the useful stuff. Keep it up!
hi, excellent tutorial! honestly i never seen an in depth tutorial on sql or postgres but this one is exactly that. Good job keep doing more advance videos on postgres! Thank you, regards!
ABSOLUTELY BRILLIANT! Excellent speed flow, excellent lesson flow, excellent explanation and excellent "PASSION" which can be felt in the humour/attitude and presentation tones! REALLY GRATEFUL Derek! As this is my first official introduction to SQL in general, I still have ways to go before I can be as comfortable with it as I am with other languages but you definitely set me up to follow a methodical route to finding points of reference as the need may arise. Looking forward to other lessons!
PS: for what its worth, it took me a solid 18 hours to complete your 3.5hr lesson with notes and practices. And your request to leave comment "came this far" was noted but I waited until the video was complete :)
Sometimes I feel like Derek must be following me around or something. Every time I start working with a new technology it seems I get a notification that Derek has made a new video about it lol. Thank you Derek. As always, your content is awesome.
That's funny :) I'm very happy to hear that my videos have helped
I kinda think same, was just looking for postgresql and found notification from Derek's channel.
Just perfect ! Thank's a lot for this comprehensive and detailed tuto !
Thanks !!! This is a great video... It's the SQL tutorial that I needed !!!
Hi uncle derek, just wanna say i started learning with your videos back when i was 14-15. Now im doing my bachelor degree in software engineering, and already a full stack dev with experience shipping few production app. Yet i still had one month more to finish my degree, and im already shipping app. Partially of that is a big thanks to you and your tutorial in one video.
Incredibly educational and useful. Thank you very much for all of your efforts and sharing it ! Highly appreciated! Wish you a happy year :)
Thank you :) I’m happy I could help Happy New Year
@Derek Banas - I Started watching your code tutorials back in 2016 with the python series you made, hoping to get a job/career in programming and away from security. Haven't got a spot yet, but haven't stopped watching your stuff yet. Thank you for making these videos, it helps me feel smarter every time I watch them as so little else keeps my brain preoccupied.
Thank you for watching for so long! I wish you all the best in your job search and your future :)
Never give up mate! 👊🏻💪🏻 But you have to DO and Type and not just WATCH!
BIG BIG DIFFERENCE!!
Great Tutorial Derek! Congrats!
Thanks foor this. Likely the longest viedo on YoutTube I have watched. Time to implement and learn further!
Perfect Videoto get up and running with plenty of examples to use as reference ... thanks!
Hi sir, I made it until the point you mentioned in the tutorial. And I am going to finish the whole video today. I take note of everything you said because I find them all very important. Thank you for producing an amazing video.
Wow!.... this is very comprehensive lecture... Thank you Sir, am beginning to understand the basics of SQL... More power to you....
This has been super helpful! I watch on 1.5 speed and try to follow along. Thanks so much.
Great tutorial, voice pleasant to the ear, can't recommend enough. Thank you sir.
WOW THIS COURSE IS MAGNIFICENT, thank you very much appreciated
i love PostgreSQL since it is a powerful, open-source Object-relational database system. It provides good performance with low maintenance efforts because of its high stability.PostgreSQL was the first DBMS that implemented the multi-version concurrency control (MVCC) feature. Some of the highlights of PostgreSQL are:
Support for the vast amount of languages.
It provides advanced security features.
It has geo-tagging support.
I agree! It is the DB I use for all my stores
What is MVCC?
Derek, thank you so much for all your videos. People are immensely benefited from your videos.
Thank you for taking the time to tell me they help :) I greatly appreciate that
Incredible course that has to many information that you can search afterwards or during the course! Thanks Darek for making this video to all of us!!! Wish you all the best
Edit my comment after one month:
- This course is just so complete and with huge amount of work. You can spend at least 10 hours by yourself in order to understand each topic and search more information.
Question: Do you know how we can produce sample data automatically in order to work more with SQL and try different examples ?
Thank you!
Thank you very much for all your hard work. You really helped a lot.
completed it in 4 days!. Thankyou so much
really wonderful and practical tutorial! helps a lot!
Hi, I greatly appreciate the quality content, truly amazing stuff. thank you :)
A#1 your tutorials have saved me so much time!!!!
Wow I have a coding test for a grad scheme in a few days where I have to use postgres and you posted this, what a legend!
Thanks :) I hope it helps
This guy is GIGACHAD, he's a good person who likes to teach people, he's so smart, he explains so good. Thank you for your tutorials.
Love the video, learning a lot. Thanks
Aww so unexpected and good news to see you cover PostgreSQL. Good job!
Thanks Stan! I couldn’t believe there were so few tutorials on Postgres so I had to fix that
@@derekbanas well it ain't on no hype train I guess.
Good tutorial.
Thanks for video!
Made it! Very well done video
OMG! This is incredible Derek 🔥
Thank you so much
Thank you :) I hope it helps
Great stuff, sir!
His Video helped me with C++ years back when I was in college, Now I hope this video will help me change my career but this man is absolute legend for putting so much videos for free to help people all over the world for free.
I just love your tutorials, and this is an awesome one as well, and as you requested: "Hey I made it!"
Hey I made it! You're changing lives. Thank you.
I made it to 2:49:36 and will watch it all, I've watched many videos from you and have tried many of the SQL videos, I'm still learning but it helps a lot to understand how PostgreSQL works in a very compact and easy to understand way, I think I can do a lot of work with it as a data and reporting analyst which I have been working as for the last 7 years but haven't taken proper training in SQL until now, I'm also learning R and its data.table package and I have worked with regex in Javascript and google sheets and plan to use it in R and SQL.
Hi Derek, I'm back!
After some quite long time, because now I need to learn something BLASTING new, i.e. PostgreSQL for this time because one of my job interviews requires it and it is always something new for me and my CV!! ^^ :P
Please don't hesitate to upload your materials because your tutorials quality is best on RUclips and generally anywhere! ;]
Udemy is just a joke sometimes ..
I just love to be back on your channel and I really missed that! ^^
Hopefully you will still find some time for us and you're going to provide more really useful materials about more newest and most important technologies! :)
Keep us up to date and keep up the good work Derek!
I really appreciate your tutorials, great job! :)
I made it to 2:49:28. Thanks for your help. I first used PostgreSQL when it was just Postgres and needed a refresher since I've been doing nothing in management for a long time. lol
Thanks!
WOW!!! Thank you so much for your support! I greatly appreciate it! You are the largest donator of all time!
One of the best tutorials I have come across. I have learnt a lot in 5 min. Thank you very much
Thank you :) I'm happy I could help
Incredible tutorial!
Not confusing but fast tutorial. Ty for your efforts.
Made it Sir !.. Wish you come up with more useful videos for us...like C++ in future💯
Thanks Derek, this is awesome. Saving this for later.
Thank you :)
Thank you for making this.
I've made it - great tutorial as always, Derek
Thank you very much for taking the time and effort to create these videos. I am starting with Postgresql and will certainly learn a lot from your course.
One thing I noticed at the start is that you say "you will use varchar a lot" - which I did indeed until recently. Then someone pointed me to the "Don't do this" page on the official Postgresql wiki which says we should not use varchar(n) but text instead. I found it a bit counterintuitive but apparently, the database will handle text at least as efficiently as varchar. The same goes for or char(n). Do you have an opinion on that?
I made it. Excellent video my guy. keep it up.
and once again after 2 years I'm back on legendary tutor's tutorial
3rd time watching. it gets better and better. thank you
This is an excellent tutorial, I'll deduct 2points for not explaining the different JOIN types better, and for suddenly just using NATURAL JOIN out of the blue. But over all it was a 98 out of 100 score from me - tank you sir!
... yup I too got stuck a few times with 'lack of explanation' ... but no biggy ... i had CHATGPT ready for quick reference ... great tut!
Looking forward to this!
I hope you find it useful
Hey, I made it, it wook me a while but I really appreciate the video!
Always creating unparalleled content. Keep the Good Work, Derek Bhau ( Brother )
Thank you :) trying to do my best
Made it!!
Thanks again for a great course
Thank you bro for great course
Derek I have no words this is gold i am student from India this video give me a precious knowledge thank you Derek.
This video should have million views.
Thank you
Thank you for taking the time to tell me it helped :) I wish you all the best!
Thank you!
I think there is a logical mistake at 3:01:15 where the price sum is the sum of all item price instead of Nike’s specifically. I changed the code a bit and it worked out. I am thankful for the opportunity for me to actually think through the problem haha. Great video really helped! Thank you!
no, way more than that, without join condition there will exist duplicate values
This is an incredible tutorial
Amazing, I wanted to see course about this database.
PostGreSql and the TimeScale add-on (free) make this one of the best for financial time series and IoT time-ordered data. Just smoking fast because of the automatic partitioning.
I've made it, great stuff !!
Thank you for this tutorial
Made it to the 2:49:25 mark! Thanks for this tutorial, I will definitely be returning for tips and reminders in the future
Thank you :) I'm happy I could help
in the functions part, following the example in current versions will give an error if you just run the query on the whole thing and it requires more steps to get the result, but if you add a semi-colon at the end of the block for declaring and defining the function (LANGUAGE SQL ;) it will still work with a single click
if you have trouble with ids matching what I ended up doing was downloading the data from github and then copy pasting it in. The first time the ids still wouldn't behave but the 3rd try after typing manually the first run and then trying to copy paste 2nd, the third time was indeed the charm. I probably added something to a column by accident which really messed up the whole process. On a different note, I did learn there is a way to reset the IDs from a comment below, which may come in handy later in life. Thank you very much for the quality material and especially the downloadable data files.
Thx for this, I needed a refresher on Postgres (and normalization for that matter). Question on the product table around the 28:00 min mark: Wouldn't the supplier normally be its own table? Just double checking my thought process. You might address this later (in which case I'll find out) and/or have omitted it for brevity/reduced complexity; or I might I just be wrong. Thanks, great stuff.
Well I pretty much LOVE your tutorials, and have kept watching for years. But actually as to postgresql, I feel that if you could paste pictures of some full tables you've been selecting from, it might make some commands more clear and explicit.😘
HEY I MADE IT (barely
You truly are amazing, man. Respect
You're very kind :) I'm very lucky to be able to do this.
As requested, I've made it to 2:49 so far :-) This is very well done,.
There is a logical mistake at 2:59:15. By writing "FROM table1, table2" you get a cross join of these tables, which was not the intent. This is why the sum is so large.
This video came just in time!
I'm happy I could help :)
GOOD WORK 👍
"Hey I made it!" Thanks for such a great walkthrough
Thank you :) I’m glad you found it useful
amazing
Great job Thanks very helpfull
Thank you :) I'm happy I could help
Thank you
heyy can you create video on how to implement clean architecture and tdd? today i was checking my old projects and tried to do some changes but it broke 😢. I felt my old projects are just useless now because i couldn't make any changes. it would help if you could make a video on that. Your postgres video is amazing! It helped me in exams.. thank you so much for amazing content!
you are kidding me, this is exaaactly what I have been wanting to find recently
Awesome! I’ve been going through RUclips and making tutorials on subjects that haven’t been made or that need updated
Would be great to have a video about Git and CI/CD
Perfect voice and pace for an instructor. Thank you for sharing your knowledge in these great videos.
@derekbanas you are joining two primary keys @1:07:00. That is not correct. But i noticed you spoke the right thing after you wrote the wrong query. I hope that I'm not the only one noticed that. So, as you said, we join a fk from one table with the pk in the other table. One more mention - I don't know if you covered that later, to use transactions or to defer serial pk autoincrement on insert errors where serial keys are messed up for other related tables that depend on them. Good content so far, thanks.
made it to 3h, great tutorial. Hopefully I will come back to finish the remaining 40 min
Just opened the video to hear you say "Well hello internet!". Will learn Postgres later :)
That’s funny :) I hope you find it useful
Thanks man
It is my pleasure to be able to help :)
this is beyond amazing
Thank you :) Happy I could help
merece uma boa compania !
There is a mistake at 1:06:25.
It should not be: item id = sales_item id.
It should be: item id = sales_item item_id.
I came here to point out the same mistake. Thank you for pointing it out first!
i thought it was only me lol, finally someone else pointed that out. but even with the wrong code, it worked for him, how come??
@@dirtysouth3291 I spotted that immediately. It worked because it is not mandatory for two ids to be in pk vs. fk relationship. If there is no matching key the result row is discarded. But the result table is wrong, just inspect prices in the `item` table.
Is there ever a point in creating the DB before the front end? I thought you need a ORM to talk to the DB from a framework or am I wrong?
谢谢！
太感谢了!
Awesome course and thank you. I ran your 3 table join line for line and it returns nothing. Not getting an error just 4 columns with no data??
You made a small mistake in drop trigger as it generated an error at 3:29 it should be “drop trigger tr_block_weekend_changes ON distributor”. Brilliance video by the way too.
Video Request: Can you please make a PostgreSQL part2 showing us how to turn a small schema into a physical DB implementation on Azure Database for PostgreSQL and do API requests to/front it? Even if it's a simple temperature data app where you can fetch the daily temperature via a API query it would be super helpful.
That’s an oddly specific request haha
@@deificGashsaucer Haha maybe, but you can't argue that it wouldn't be useful to know!!
nice... can you give tutor for pgsql maps too