Course
Advanced Databases
Advanced database management with SQL, integrity, and practical applications
- of video
- 8.3 h
- lectures
- 136
- sections
- 22
$99.97one payment
Lifetime access. 15-day money-back guarantee.
Want a mentor as well? The Academy gives you every course on Amigoscode plus code reviews, daily live calls and interview prep, for $129/month. Compare →
What you'll learn
By the end of this course
- Master Advanced Data Types - Work with JSONB, UUIDs, arrays, enums, and other data types to design versatile and modern databases.
- Optimize Query Performance - Dive deep into indexes, query plans, and explain-analyze techniques to write efficient and lightning-fast queries.
- Harness the Power of Transactions - Understand ACID properties, isolation levels, savepoints, and how to manage transactions for robust data consistency.
- Unlock Subqueries and Common Table Expressions (CTEs) - Simplify complex queries and enhance maintainability using advanced SQL features.
- Leverage Functions, Stored Procedures, and Triggers - Automate tasks and implement business logic directly within the database for seamless operations.
- Manage Roles, Privileges, and Security - Explore advanced techniques for access control, row-level security, and safeguarding sensitive data.
- Work with Views and Materialized Views - Gain insights into simplifying data access and improving performance for complex queries.
- Backup and Restore Strategies - Implement reliable database backup and restore practices to protect your data from unforeseen events.
Curriculum
22 sections, 136 lectures
8.3 h of video. Open any section to see its lectures.
1Intro3 lectures · 1 min
- 1Welcome1 min
- 2Recommended Courses
- 3Slides
2Schemas6 lectures · 19 min
- 1What Are Schemas4 min
- 2Understanding Schemas And Search Path4 min
- 3Create Schema3 min
- 4Set Search Path7 min
- 5How I Am Using Schemas2 min
- 6Schemas Quiz
3Advanced data types11 lectures · 51 min
- 1Adavanced Data Types1 min
- 2Casting
- 3Quiz
- 4Working With Sequences7 min
- 5Arrays11 min
- 6Serial And Bigserial7 min
- 7Jsonb7 min
- 8Enum7 min
- 9UUID (Universally Unique Identifier)7 min
- 10Other Data Types1 min
- 11Extensions And Hstore3 min
4Working with dates5 lectures · 15 min
- 1Dates And Date Data Types5 min
- 2Adding Subtracting Dates4 min
- 3Age And Extract Functions2 min
- 4Date and Time Documentation3 min
- 5Quiz
5Constraints9 lectures · 42 min
- 1Contraints And Types4 min
- 2The Problem Without Constraints4 min
- 3Alter Table And Add Primary Key Connstraint6 min
- 4Alter Table And Add Pk, Fk And Check Constraints8 min
- 5Remove Constraints4 min
- 6Clean Up Data Your Data First4 min
- 7Create Table With Constraints7 min
- 8Name Constraints6 min
- 9Quiz
6Foreign keys3 lectures · 19 min
- 1Intro To Foreign Keys2 min
- 2Updating Foreign Keys8 min
- 3On Cascade Delete9 min
7Manage tables2 lectures · 1 min
- 1Manage Tables
- 2Quiz
8Indexes12 lectures · 1 h 0 min
- 1Intro To Indexes4 min
- 2Create Index Cuncurrently7 min
- 3Visual Representation Of Indexes5 min
- 4Quiz
- 5Employee Table With 1M Records2 min
- 6Indexes In Action5 min
- 7Index Size, Create And Drop Index7 min
- 8Partial Indexes6 min
- 9Multi Column Index5 min
- 10Unique Indexes11 min
- 11Index Types4 min
- 12Index Data Structures4 min
9Analyse queries7 lectures · 15 min
- 1Explain Analyse2 min
- 2Query Plans6 min
- 3Reading Query Plans3 min
- 4Why Explain Analyse Is Important1 min
- 5Why Explain Analyse Is Important1 min
- 6Explain Analyse Cheatsheet1 min
- 7Quiz
10Conditional expressions5 lectures · 13 min
- 1Conditional Expressions1 min
- 2Coalesce5 min
- 3Nullif3 min
- 4Case4 min
- 5Quiz
11Dealing with conflicts5 lectures · 13 min
- 1Section SQL
- 2Dealing With Conflicts4 min
- 3On Coflict Do Nothing4 min
- 4On Conflict Do Update Aka Upsert6 min
- 5Quiz
12Sub queries8 lectures · 18 min
- 1Section SQL
- 2What Are Sub Queries3 min
- 3Using Sub Queries5 min
- 4Where Exists6 min
- 5Any5 min
- 6Section SQL
- 7Exercises
- 8Quiz
13Common table expressions5 lectures · 15 min
- 1Common Table Expressions2 min
- 2Cte In Action6 min
- 3Multiple Cte7 min
- 4Complex CTE Example
- 5Quiz
14Window functions7 lectures · 31 min
- 1Window Functions3 min
- 2Quiz
- 3Row_number()6 min
- 4Rank Dense Rank And Ntile9 min
- 5Aggreate Functions7 min
- 6Value Functions7 min
- 7Real World Example
15Views and materialized views5 lectures · 17 min
- 1Intro To Views2 min
- 2Working With Views8 min
- 3Intro To Marerialised Views2 min
- 4Working With Materialized Views4 min
- 5Quiz
16Transactions8 lectures · 23 min
- 1Intro To Transactions1 min
- 2Acid Properties2 min
- 3Begin Commit And Rollback4 min
- 4Working With Transacitons Part 14 min
- 5Begin Commit And Rollback In Action6 min
- 6Isolation Levels5 min
- 7Transaction Save Points1 min
- 8Quiz
17Functions stored procedures and triggers7 lectures · 33 min
- 1Intro To Functions2 min
- 2Your First Function7 min
- 3Intro Stored Procedures2 min
- 4Your First Stored Procedure5 min
- 5More Complex Procedure12 min
- 6Triggers5 min
- 7Quiz
18Roles groups and privileges5 lectures · 23 min
- 1Intro To Roles Groups And Privileges4 min
- 2Working With Roles8 min
- 3Privileges Aka Permissions6 min
- 4Groups5 min
- 5Quiz
19Backup and restore5 lectures · 27 min
- 1Intro To Database Backups2 min
- 2Backup Databases12 min
- 3Restore Databases7 min
- 4Copy And CSV5 min
- 5Quiz
20Full text search7 lectures · 17 min
- 1Intro To Full Text Seach2 min
- 2Tsvector5 min
- 3Tsquery3 min
- 4Searching Vectors5 min
- 5Ranking2 min
- 6Exercise
- 7Quiz
21Row level security8 lectures · 47 min
- 1Intro To Rls7 min
- 2Db Roles And Table Setup6 min
- 3Enabling Rsl And Policies9 min
- 4Insert Policy9 min
- 5Real World Examples4 min
- 6Dealing With Recursion7 min
- 7Update Rls6 min
- 8Quiz
22Next steps3 lectures · 1 min
- 1Next Steps1 min
- 2Feedback
- 3Next Steps
Choose how you learn
Learn on your own, or with people who check your work
The course is yours to keep. The Academy is this course and every other one, plus the people who get you hired.
Advanced Databases
The course, on your own
$99.97one payment
- 136 lectures, 8.3 h of video
- Subtitles on every video
- 6 months of IntelliJ IDEA Ultimate
- Lifetime access to the course and its updates
15-day money-back guarantee
Recommended
The Academy
The courses, plus the people
$129/monthcancel anytime
This course, plus
- Every course on Amigoscode 50+ in the Zero to Hired Roadmap
- Daily live calls and 1:1 instructor time
- Code reviews and pair programming on what you build
- Interview prep mock interviews, System Design Lab, CV and LinkedIn review
- Land an interview in 90 days or we coach you 1:1 free until you do.
Join the Academy
7-day money-back guarantee · Secure checkout on Skool
“Nelson was teaching 90% of my job. I was already implementing everything I learnt from it.”
“I was a totally different person. More confident as a developer, more prepared for my job.”
GabrielaSoftware Engineer at Mercado Livre“I studied biomedicine and psychology. I found Nelson's Java tutorials while looking for a course for my brother. I was hooked and now here I am, a software engineer at IKEA.”
NajmaSoftware Engineer at IKEAAbout this course
Who is this course for?
Level: Mid and Senior Engineers.
How long will it take?
There are about 8 hours of video. Most people finish in 2 weeks at 5 hours a week, plus the time you spend building along.
Do I keep access?
Yes. You pay once and keep the course for life, including updates.
What if it isn't for me?
Email us within 15 days of buying and we'll refund you in full.
Should I buy the course or join the Academy?
Buy the course if you only want this topic and you learn well on your own. Join the Academy if you want to get hired or move up: you get every course on Amigoscode, this one included, plus daily live calls, code reviews and interview prep, for $129/month. The course is $99.97 once.