The ‘SQL and PostgreSQL: The Complete Developer’s Guide’ Udemy course will teach you the complete fundamental concepts of SQL and PostgreSQL. In this course, you will learn to store and fetch data, tune queries, and design efficient database structures.
By the end of this course, you’ll be able to design fast and efficient databases using the latest technologies. The course is usually available for INR 2,799 on Udemy but you can click on the link and get the ‘SQL and PostgreSQL: The Complete Developer’s Guide’ for INR 499.
Who all can opt for this course?
- Any developers looking to understand databases.
- Application engineers who want to expand their backend skillset.
Course Highlights
| Key Highlights | Details |
|---|---|
| Registration Link | Apply Now! |
| Price | INR 499 ( |
| Duration | 22 Hours |
| Rating | 4.7/5 |
| Student Enrollment | 45,043 students |
| Instructor | Stephen Grider https://www.linkedin.com/in/stephengrider |
| Topics Covered | SQL, PostgreSQL, Database Concepts, Database Structure |
| Course Level | Beginner |
| Total Student Reviews | 6,508 |
Learning Outcomes
- Understand relational database principles.
- Get the knowledge and abilities required to work as a database developer or administrator (DBA).
- Using the newest technologies, create databases that are quick and effective.
- With numerous tests, assignments, and built-in exercises, you may hone your talents.
- Many database design patterns should be learned and used.
- Improve query performance for both reading and writing.
- Recognize the hardware methods used by PostgreSQL to store data.
- Use an API to link PostgreSQL to front-end applications.
- Provide functionality for typical apps, such like and mention systems.
- Manage race situations and difficult concurrency problems.
- Schema migrations can help your database designs advance.
- Learn how to accelerate automated tests using PostgreSQL.
Course Content
| S.No. | Module (Duration) | Topics |
|---|---|---|
| 1. | Simple – But Powerful – SQL Statements (38 minutes) | Join Our Community! |
| Course Resources | ||
| What is PostgreSQL All About? | ||
| Database Design | ||
| Database Terminology | ||
| Creating Tables | ||
| Analyzing CREATE TABLE | ||
| Inserting Data Into a Table | ||
| Retrieving Data with Select | ||
| Create, Insert, and Select! | ||
| Calculated Columns | ||
| Calculating Phone Revenue | ||
| Using Calculated Columns | ||
| Exercise Solution | ||
| String Operators and Functions | ||
| 2. | Filtering Records (29 minutes) | Filtering Rows with “Where” |
| More on the “Where” Keyword | ||
| Compound “Where” Clauses | ||
| A “Where” Exercise Overview | ||
| Practicing Where Statements | ||
| A “Where” Solution | ||
| “Where” With Lists | ||
| A More Challenging ‘Where’ | ||
| A “Where” With Lists Solution | ||
| Calculations in “Where” Clauses | ||
| Trying Calculations in Where Clauses | ||
| Solving Calculations | ||
| Updating Rows | ||
| Deleting Rows | ||
| Try Updating Records In a Table! | ||
| A Solution for Updating Rows | ||
| Practice Deleting Records | ||
| Solution for Deleting Rows | ||
| 3. | Working with Tables (01 hour 18 minutes) | The Plan Moving Forward |
| Approaching Database Design | ||
| Let’s Design Some Schema | ||
| One-to-Many and Many-to-One Relationships | ||
| A ‘Has One’ or ‘Has Many’? | ||
| One-to-One and Many-to-Many Relationships | ||
| Identifying One-to-One and Many-to-Many Relationships | ||
| Primary Keys and Foreign Keys | ||
| Understanding Foreign Keys | ||
| Foreign Keys; How Do They Work? | ||
| Auto-Generated ID’s | ||
| Creating Foreign Key Columns | ||
| Running Queries on Associated Data | ||
| Exercise Overview | ||
| Creating and Using Foreign Keys | ||
| Foreign Key Creation Solution | ||
| Foreign Key Constraints Around Insertion | ||
| Constraints Around Deletion | ||
| Commands You’ll Need for the Next Video | ||
| Testing Deletion Constraints | ||
| Setting Foreign Keys to Null on Delete | ||
| What Happens On Delete? | ||
| Adding Some Complexity | ||
| 4. | Relating Records with Joins (01 hour 19 minutes) | Adding Some Data |
| Queries with Joins and Aggregations | ||
| Joining Data from Different Tables | ||
| Another Quick Join | ||
| Exercise Overview | ||
| Practice Joining Data | ||
| A Joinful Solution | ||
| Alternate Forms of Syntax | ||
| Missing Data in Joins | ||
| Why Wasn’t It Included | ||
| Four Kinds of Joins | ||
| Each Join in Practice | ||
| Does Order Matter? | ||
| Test Your Joining Knowledge | ||
| Exercise Overview | ||
| Joins, Joins, Join! | ||
| Two Possible Solutions | ||
| Where with Join | ||
| Three Way Joins | ||
| A Bit of Practice | ||
| Three Way Exercise | ||
| Exercise Solution | ||
| 5. | Aggregation of Records (45 minutes) | Aggregating and Grouping |
| Picturing Group By | ||
| Selecting Columns After Grouping | ||
| Aggregate Functions | ||
| Combining Group By and Aggregates | ||
| A Gotcha with Count | ||
| Visualizing More Grouping | ||
| Exercise Overview | ||
| Practice For Grouping and Aggregating | ||
| Grouping Solution | ||
| Adding a Layer of Difficulty | ||
| Grouping With a Join! | ||
| Solution | ||
| Filtering Groups with Having | ||
| Having In Action | ||
| More on Having! | ||
| A Having Exercise Overview | ||
| Practice Yourself Some Having | ||
| A Quick Solution | ||
| 6. | Working with Large Datasets (20 minutes) | A New Dataset |
| Investigating This Dataset | ||
| Some Group By Practice | ||
| Group By Review | ||
| Group By Review Solution | ||
| Remember Joins? | ||
| Inner Join Review | ||
| Of Course You Remember! | ||
| 7. | Sorting Records (13 minutes) | The Basics of Sorting |
| Two Variations on Sorting | ||
| Offset and Limit | ||
| Exercise Overview | ||
| Sorting, Offsetting, and Limiting | ||
| Exercise Solution | ||
| 8. | Unions and Intersections with Sets (21 minutes) | Handling Sets with Union |
| A Few Notes on Union | ||
| Commonalities with Intersect | ||
| Removing Commonalities with Except | ||
| Union Exercise Overview | ||
| Merging Results with Union | ||
| Exercise Solution | ||
| 9. | Assembling Queries with SubQueries (01 hour 52 minutes) | What’s a Subquery? |
| Thinking About the Structure of Data | ||
| What’s the Data Look Like? | ||
| Subqueries in a Select | ||
| Exercise Overview | ||
| Embedding in Select | ||
| Select Solution | ||
| Subqueries in a From | ||
| From Subqueries that Return a Value | ||
| Example of a Subquery in a From | ||
| Exercise Overview | ||
| Subquery From’s | ||
| Exercise Solution | ||
| Subqueries in a Join Clause | ||
| More Useful – Subqueries with Where | ||
| Data Structure with Where Subqueries | ||
| Exercise Overview | ||
| Subquery Where’s | ||
| Exercise Solution | ||
| The Not In Operator with a List | ||
| A New Where Operator | ||
| Finally Some! | ||
| Is It A Valid Subquery? | ||
| Exercise Overview | ||
| Practice Your Subqueries! | ||
| A Quick Solution | ||
| Probably Too Much About Correlated Subqueries | ||
| More on Correlated Subqueries | ||
| A Select Without a From? | ||
| Exercise Overview | ||
| From-less Selects | ||
| Exercise Solution | ||
| 10. | Selecting Distinct Records (05 minutes) | Selecting Distinct Values |
| Exercise Overview | ||
| Some Practice with Distinct | ||
| A Distinct Solution | ||
| 11. | Utility Operators, Keywords, and Functions (09 minutes) | The Greatest Value in a List |
| And the Least Value in a List! | ||
| The Case Keyword | ||
| 12. | Local PostgreSQL Installation (14 minutes) | PostgreSQL Installation on macOS |
| PGAdmin Setup on macOS | ||
| Postgres installation on Windows | ||
| 13. | PostgreSQL Complex Datatypes (37 minutes) | What’d We Just Do? |
| Data Types | ||
| Fast Rules on Numeric Data Types | ||
| More on Number Data Types | ||
| Reminder on Character Types | ||
| Boolean Data Types | ||
| Times, Dates, and Timestamps | ||
| Really Awesome Intervals | ||
| 14. | Database-Side Validation and Constraints (49 minutes) | Thinking About Validation |
| Creating and Viewing Tables in PGAdmin | ||
| Applying a Null Constraint | ||
| Solving a Gotcha with Null Constraints | ||
| Creating NULL Constraints | ||
| Default Column Values | ||
| Applying a Unique Constraint to One column | ||
| Multi-Column Uniqueness | ||
| Is It Unique? | ||
| Adding a Validation Check | ||
| Checks Over Multiple Columns | ||
| Does It Pass a Check? | ||
| So Where Are We Applying Validation? | ||
| 15. | Database Structure Design Patterns (25 minutes) | Approaching More Complicated Designs |
| Using a SQL Design Tool | ||
| A Config-based Schema Designer | ||
| Here’s the Plan | ||
| Rebuilding Some Schema | ||
| 16. | How to Build a ‘Like’ System (35 minutes) | Requirements of a Like System |
| How Not to Design a Like System | ||
| Designing a Like System | ||
| Building a Similar System | ||
| Making a Reaction System Instead | ||
| Polymorphic Associations | ||
| Polymorphic Association Alternative Implementation | ||
| The Simplest Alternative | ||
| Polymorphic Associations | ||
| So Which Approach? | ||
| 17. | How to Build a ‘Mention’ System (27 minutes) | Additional Features Around Posts |
| Adding Captions and Locations | ||
| Photo Mentions vs Caption Mentions | ||
| Considerations on Photo Tags vs Caption Tags | ||
| Update For Tags | ||
| 18. | How to Build a ‘Hashtag’ System (25 minutes) | Designing a Hashtag System |
| Tables for Hashtags | ||
| Including the Hashtag Table | ||
| A Few More User Columns | ||
| Why No Number of Followers or Posts? | ||
| 19. | How to Design a ‘Follower’ System (05 minutes) | Designing a Follower System |
| 20. | Implementing Database Design Patterns (44 minutes) | Back to Postgres |
| Creating Tables with Checks | ||
| Posts Creation | ||
| Comments Creation | ||
| Likes Creation | ||
| Photo Tags and Caption Tags | ||
| Creating Hashtags, Hashtag Posts, and Followers | ||
| 21. | Approaching and Writing Complex Queries (17 minutes) | Quick Note About Adding Some Data |
| Adding Some Data | ||
| Restoring from Scratch | ||
| Highest User ID’s Exercise | ||
| Solution for User ID’s | ||
| Posts by a Particular User | ||
| Solving for Posts by User | ||
| Likes Per User | ||
| Solution for Likes Per User | ||
| 22. | Understanding the Internals of PostgreSQL (47 minutes) | Thinking About Performance |
| Where Does Postgres Store Data? | ||
| Heaps, Blocks, and Tuples | ||
| Terminology Check | ||
| Block Data Layout | ||
| Heap File Layout | ||
| 23. | A Look at Indexes for Performance (01 hour 06 minutes) | Full Table Scans |
| What’s an Index | ||
| How an Index Works | ||
| Creating an Index | ||
| Benchmarking Queries | ||
| Downsides of Indexes | ||
| Index Types | ||
| Automatically Generated Indexes | ||
| Using Automatically Created Indexes | ||
| Behind the Scenes of Indexes | ||
| 24. | Basic Query Tuning (19 minutes) | The Query Processing Pipeline |
| Explain and Explain Analyze | ||
| Explain Vs Explain Analyze | ||
| Solving an Explain Mystery | ||
| 25. | Advanced Query Tuning (40 minutes) | Developing an Intuitive Understanding of Cost |
| Calculating Cost by Hand | ||
| A Touch More on Costs | ||
| Calculating Costs | ||
| Startup vs Total Costs | ||
| Costs Flow Up | ||
| Use My Index! | ||
| 26. | Simple Common Table Expressions (09 minutes) | Common Table Expressions |
| A Quick Solution | ||
| So What’s a CTE? | ||
| 27. | Recursive Common Table Expressions (36 minutes) | Recursive CTE’s |
| Recursive CTE’s Step by Step | ||
| Why Use Recursive CTE’s? | ||
| Writing the Query | ||
| Walking Through Recursion | ||
| 28. | Simplifying Queries with Views (21 minutes) | Most Popular Users |
| A Possible Solution for Merging Tables | ||
| Creating a View | ||
| When to Use a View? | ||
| Deleting and Changing Views | ||
| 29. | Optimizing Queries with Materialized Views (26 minutes) | Materialized Views |
| Grouping by Week | ||
| Reminder on Left Joins | ||
| Writing a Slow Query | ||
| Creating and Refreshing Materialized Views | ||
| Views vs Materialized Views | ||
| 30. | Handling Concurrency and Reversibility with Transactions (21 minutes) | What are Transactions Used For? |
| Some Sample Data | ||
| Opening and Closing Transactions | ||
| Transaction Cleanup on Crash | ||
| Closing Aborted Transactions | ||
| 31. | Managing Database Design with Schema Migrations (50 minutes) | A Story on Migrations |
| Migration Files | ||
| Issues Solved by Migrations | ||
| A Few Notes on Migrations Libraries | ||
| A Note About Node Installation | ||
| Project Creation | ||
| Generating and Writing Migrations | ||
| Applying and Reverting Migrations | ||
| Generating and Applying a Second Migration | ||
| 32. | Schema vs Data Migrations (01 hour 12 minutes) | Schema vs Data Migrations |
| Dangers Around Data Migrations | ||
| Properly Running Data and Schema Migrations | ||
| Creating a Posts Table | ||
| A Small Web Server | ||
| Web Server Setup Instructions | ||
| Adding the Loc Column | ||
| Writing Values to Both Columns | ||
| Transaction Locks | ||
| Updating Values | ||
| Migrations Setup Instructions | ||
| Updating the App Server | ||
| Dropping the Last Columns | ||
| 33. | Accessing PostgreSQL From API’s (27 minutes) | Section Goal |
| Initial Setup | ||
| One Fast Migration | ||
| Building the Users Router | ||
| Understanding Connection Pools | ||
| Validating Connection Credentials | ||
| Query and Close | ||
| 34. | Data Access Pattern – Repositories (32 minutes) | The Repository Pattern |
| Creating a Repository | ||
| Accessing the API | ||
| Casing Issues | ||
| Fixing Casing | ||
| Finding Particular Users | ||
| 35. | Security Around PostgreSQL (37 minutes) | SQL Injection Exploits |
| Handling SQL Injection with Prepared Statements | ||
| Preventing SQL Injection | ||
| Reminder on Post Requests | ||
| Inserting Users | ||
| Handling Updates | ||
| And, Finally, Delete | ||
| 36. | Fast Parallel Testing (01 hour 26 minutes) | A Note on Testing |
| Assertions Around User Count | ||
| Connecting to a DB For Tests | ||
| Disconnecting After Tests | ||
| Multi-DB Setup | ||
| Assumptions Around Content | ||
| Issues with Parallel Tests | ||
| Isolation with Schemas | ||
| Creating and Accessing Schemas | ||
| Controlling Schema Access with Search Paths | ||
| Routing Schema Access | ||
| Strategy for Isolation | ||
| Programmatic Schema Creation | ||
| Escaping Identifiers | ||
| Test Helpers | ||
| Cleaning up Schemas and Roles | ||
| Finally… Parallel Tests! (Final Lecture) | ||
| 37. | Bonus! (30 seconds) | Bonus! |
Resources Required
- A computer that is Mac, PC, or Linux-based
Featured Review
Marty Vue (5/5) : I’ve learned a lot and each section is very simple to follow along with. The teaching style is perfect to how I need to learn.
Pros
- Chuck Mai (5/5) : Excellent course, Stephen covers a lot of database topics in this course.
- Promise Sheggsmann (5/5) : One of the best courses you can find on PostgreSQL here on Udemy.
- Fuad Jamilov (5/5) : Just perfect course! I’m giving this rating after watching one section.
- Frank Mysirlakis (5/5) : But man this is one of the best courses ever I have seen.
Cons
- RandyMindy (1/5) : very misleading title and content overviews, at least be honest about these basics, your course is postgresql and APP with JAVA SCRIPT! where import database topics are discussed. Please have minimum integrity and respect to other people’s time, other than that your postgresql videos must be summarized at most in two hours.
- Thomas (1/5) : Most parts unnecessary long. I feel like wasting my time.
- Spiritual J. (1/5) : too basic
About the Author
The instructor of this course is Stephen Grider who is a Engineering Architect. With 4.7 Instructor Rating and 414,218 Reviews on Udemy, Stephen Grider offers 32 Courses and has taught 1,231,977 Students so far.
- In the San Francisco Bay Area, Stephen Grider has been developing intricate Javascript front ends for leading businesses.
- Stephen has been guiding engineers starting their careers in software development for years, and he has now expanded on that experience by creating the most highly rated React course on Udemy.
- He instructs on Udemy to impart his knowledge to other software engineers.
- Learning from Stephen’s published courses is an investment in oneself.
Comparison Table
| Parameters | SQL and PostgreSQL: The Complete Developer’s Guide | SQL – The Complete Developer’s Guide (MySQL, PostgreSQL) | Complete SQL and Databases Bootcamp: Zero to Mastery [2023] |
|---|---|---|---|
| Offers | INR 499 ( | INR 455 ( | INR 455 ( |
| Duration | 22 hours | 19.5 hours | 24.5 hours |
| Rating | 4.7/5 | 4.7/5 | 4.6/5 |
| Student Enrollments | 45,038 | 10,657 | 30,444 |
| Instructors | Stephen Grider | Academind by Maximilian Schwarzmüller | Andrei Neagoie |
| Register Here | Apply Now! | Apply Now! | Apply Now! |
Leave feedback about this