We may earn an affiliate commission when you visit our partners.
Course image
Stephen Grider

Database structure design?  It's here.  Query tuning and optimization? You'll master it.  Complex queries? Yes indeed.

Read more

Database structure design?  It's here.  Query tuning and optimization? You'll master it.  Complex queries? Yes indeed.

This is the only course online that will teach you how to design a database, store complex data, optimize your queries, everything that is needed for operating a production, scalable database.

Every app in the world, whether it is a mobile app built with Flutter, a web app constructed with React, or a plain HTML page, needs to store information in a database.  Although there are many different databases you can use, PostgreSQL has been a number-one pick for decades, offering scalable performance, rock-solid uptime, and flexible design systems.  This course will teach you everything you need to know about PostgreSQL to use it on your next big project.

Don't know anything about databases at all? No problem. We begin with an overview of SQL, the language used to interact with PostgreSQL. Through an incredible number of exercises, you'll get practical, hands on experience working with realistic datasets.  You will understand how to store, fetch, and update information with a series of powerful commands. 

After gaining experience with elementary operations, you will move on to understanding database design patterns, offering multiple ways to structure your database for efficient data modeling. You'll see many different scenarios with different types of data, and understand the pros and cons to several approaches.

This course is packed with practice exercises and quizzes. You'll get immediate practice on all of the different topics and features that you learn.   In addition, you'll understand exactly where to use each of these features in real and practical projects.

Besides getting practical hands-on experience, you'll also get a behind-the-scenes look at how PostgreSQL works internally. We'll dive into raw data files, investigating how PostgreSQL stores information on your hard drive bit by bit.  This knowledge is invaluable when it comes time to start tuning your queries for performance.  By having a natural instinct of how PostgreSQL works, you can plan on being able to get every last bit of performance out of your database.

Here is a partial list of some of the topics that are covered in this course:

  • Apply powerful SQL commands to store, update, and retrieve information

  • Build relationships between records using foreign keys between tables

  • Understand PostgreSQL's numerous data types, and when to use each

  • Assemble reports of business data by using aggregation pipelines

  • Work with millions of records to simulate real production queries

  • Exercise your query logic skills through the use of sets and sorting operators

  • Compose queries out of smaller reusable units using subqueries

  • Use different design patterns to efficiently model your data

  • See how to divide database objects into different schemas to maintain them more easily

  • Validate your data using simple 'check' rules

  • Construct perfect designs for common features such as 'like', 'follow', and 'tag' systems

  • Speed up your queries with indexes - you'll see exactly how they work behind the scenes.

  • Dive into some of the most complex queries around with recursive common table expressions

  • Deal with concurrency issues easily by applying transactions

I made this course to be the perfect resource for engineers just getting started with their first database.  Master the database itself, and see how to apply it with real designs.  Sign up today and master PostgreSQL.

Enroll now

Good to know

Know what's good
, what to watch for
, and possible dealbreakers
Explores PostgreSQL, the most popular open-source database, which is highly relevant to data engineers and scientists
Taught by Stephen Grider, a renowned instructor in online courses
Covers a comprehensive range of topics, including advanced concepts like recursive queries and transactions
Provides numerous hands-on exercises and quizzes to reinforce learning
Develops practical skills in database design and optimization, which are valuable for job-seekers and career advancement
Advises students to have a basic understanding of SQL, which may require additional learning

Save this course

Save SQL and PostgreSQL: The Complete Developer's Guide to your list so you can find it easily later:
Save

Reviews summary

Postgresql and sql: deep dive

learners say this course is a largely positive and engaging introduction to databases for beginners, especially those with a programming background. It covers a wide range of topics and provides hands-on exercises to reinforce learning. The instructor is praised for his clear explanations, use of diagrams, and enthusiasm.
This course covers a comprehensive range of topics related to SQL and PostgreSQL, from basic concepts to advanced techniques.
"This might be one of the greatest courses on SQL I have taken. The instructor does a great job visualizing the SQL code operations, materialising all those abstract concepts."
"This is a very useful course. Stephen goes from talking about basic SQL, to writing advanced queries, to looking at the internals of Postgres, to integrating Postgres into a web app. Explanations are both clear and thorough, with lots of examples and helpful diagrams."
Learners appreciate the instructor's engaging and enthusiastic teaching style, which makes the learning process more enjoyable.
" Stephen Grider does an outstanding job of explaining Postgres VERY well. This is a beginner to intermediate course, so don't expect high-level DB administration info from this course. But, if you're new to Postgres, this course offers value in spades."
"Stephen teaches very well, love his style, I do feel this course could have touched on more PG specific areas like arrays, json, blob, geospatial data and text search, Gave it 5 stars because of the awesome teaching style, if only a bit more of these topics were included, but overall very solid."
This course is well-suited for beginners who are new to SQL and PostgreSQL, providing a comprehensive and easy-to-follow introduction.
"This course proved to be incredibly beginner-friendly."
"The way he puts things in his lectures are systematic and easy to digest."
This course features hands-on exercises and quizzes to help learners practice and reinforce their understanding of SQL and PostgreSQL.
"I really like the course so far. However, this last chapter where we are designing our database model is quite challenging. But it is maybe just me, I need to work a great deal further on this topic, as I am not familiar with Instagram at all. But I refuse to be discouraged and will stick to this course which overall is very good."
"Stephen is just synonym of excellence. Very well explained course where you gain a lot of valuable knowledge. Pay special attention to the use of graphs and charts, they really add to the learning experience. Not everything is hands on, the graphs and charts really help understand the big picture most of the time."
Stephen Grider provides in-depth explanations and insights into SQL and PostgreSQL, making complex concepts easy to understand.
"Stephen Grider adequately captures the nuances in all queries covered in this course (which is about all the important queries/keywords used in today's business world) giving an instance/problem which gives rise to the subsequent query."
"The instructor did an absolutely fantastic job at explaining the basics well, and the course moved from there to advanced topics with many solid practices & quizzes in between."

Activities

Be better prepared before your course. Deepen your understanding during and after it. Supplement your coursework and achieve mastery of the topics covered in SQL and PostgreSQL: The Complete Developer's Guide with these activities:
Review SQL language basics
Reinforce foundational knowledge in SQL to maximize engagement during lectures.
Browse courses on SQL
Show steps
  • Go through the online tutorials on SQL basics.
  • Set up a local SQL environment for practice.
  • Solve sample SQL exercises.
Follow a PostgreSQL tutorial series
Deepen understanding of PostgreSQL concepts through guided, step-by-step instructions.
Browse courses on Database Management
Show steps
  • Identify a comprehensive PostgreSQL tutorial series from a reputable source.
  • Follow each tutorial in sequence, completing the exercises and examples.
  • Take notes and summarize the key concepts covered in each tutorial.
Participate in a PostgreSQL study group
Engage with peers to discuss concepts, share knowledge, and work through challenges collaboratively.
Show steps
  • Find or form a study group with fellow PostgreSQL learners.
  • Meet regularly to discuss course topics, ask questions, and solve problems together.
  • Take turns presenting concepts and leading discussions.
One other activity
Expand to see all activities and additional details
Show all four activities
Design a database schema for a real-world scenario
Apply PostgreSQL concepts to a practical scenario, fostering critical thinking and problem-solving skills.
Show steps
  • Identify a real-world scenario that requires a database.
  • Analyze the scenario and identify the entities and relationships involved.
  • Design a logical data model using ER diagrams or other appropriate techniques.
  • Translate the logical model into a physical PostgreSQL schema.

Career center

Learners who complete SQL and PostgreSQL: The Complete Developer's Guide will develop knowledge and skills that may be useful to these careers:
Database Administrator
Database Administrators are responsible for the maintenance and performance of databases. This course provides a comprehensive overview of PostgreSQL, one of the most popular database management systems. By learning how to design, optimize, and troubleshoot databases, you can gain the skills needed to succeed in this role.
Database Developer
Database Developers design and develop databases. This course can help build a foundation in SQL and PostgreSQL, which are essential tools for database developers. By learning how to design and optimize databases, you can gain valuable skills for this role.
Data Analyst
Data Analysts gather, clean, and analyze data to help businesses make informed decisions. This course can help build a foundation in SQL and PostgreSQL, which are essential tools for data analysts. By learning how to design and optimize databases, you can gain valuable skills for this role.
Data Engineer
Data Engineers build and maintain data pipelines. This course can help build a foundation in SQL and PostgreSQL, which are essential tools for data engineers. By learning how to design and optimize databases, you can gain valuable skills for this role.
Data Architect
Data Architects design and implement data management solutions. This course can help build a foundation in SQL and PostgreSQL, which are essential tools for data architects. By learning how to design and optimize databases, you can gain valuable skills for this role.
Data Scientist
Data Scientists use data to solve business problems and make predictions. This course can help build a foundation in SQL and PostgreSQL, which are essential tools for data scientists. By learning how to design and optimize databases, you can gain valuable skills for this role.
Software Engineer
Software Engineers design, develop, and maintain software applications. This course can help build a foundation in database design and optimization, which are essential skills for software engineers who work with data-driven applications. By learning how to store, retrieve, and manipulate data efficiently, you can gain valuable skills for this role.
Machine Learning Engineer
Machine Learning Engineers design and develop machine learning models. This course can help build a foundation in SQL and PostgreSQL, which are essential tools for machine learning engineers who work with data-driven models. By learning how to design and optimize databases, you can gain valuable skills for this role.
Information Architect
Information Architects design and organize information systems. This course can help build a foundation in SQL and PostgreSQL, which are essential tools for information architects. By learning how to design and optimize databases, you can gain valuable skills for this role.
Systems Analyst
Systems Analysts analyze and design business systems. This course can help build a foundation in SQL and PostgreSQL, which are essential tools for systems analysts. By learning how to design and optimize databases, you can gain valuable skills for this role.
Business Analyst
Business Analysts gather and analyze data to help businesses understand their customers and make better decisions. This course can help build a foundation in SQL and PostgreSQL, which are essential tools for business analysts. By learning how to design and optimize databases, you can gain valuable skills for this role.
Project Manager
Project Managers plan and execute projects. This course can help build a foundation in SQL and PostgreSQL, which are essential tools for project managers who work with data-driven projects. By learning how to design and optimize databases, you can gain valuable skills for this role.
Technical Writer
Technical Writers create documentation for software and other technical products. This course can help build a foundation in SQL and PostgreSQL, which are essential tools for technical writers who document database-driven applications. By learning how to design and optimize databases, you can gain valuable skills for this role.
Quality Assurance Analyst
Quality Assurance Analysts test and evaluate software products. This course can help build a foundation in SQL and PostgreSQL, which are essential tools for quality assurance analysts who test database-driven applications. By learning how to design and optimize databases, you can gain valuable skills for this role.

Reading list

We've selected eight books that we think will supplement your learning. Use these to develop background knowledge, enrich your coursework, and gain a deeper understanding of the topics covered in SQL and PostgreSQL: The Complete Developer's Guide.
While not specifically about SQL or PostgreSQL, this book provides a solid foundation in Python, which can be useful for automating database tasks.
A general-purpose book about PostgreSQL, which would be helpful for both beginners and those looking to go more in-depth.
Focuses on common mistakes in SQL programming, providing insights into how to avoid them. Useful for experienced developers looking to improve their coding practices.
Explores the inner workings of SQL performance, providing techniques for optimizing queries and improving database performance.
Covers a wide range of PostgreSQL topics, from basic SQL commands to advanced features such as stored procedures and triggers.

Share

Help others find this course page by sharing it with your friends and followers:
Our mission

OpenCourser helps millions of learners each year. People visit us to learn workspace skills, ace their exams, and nurture their curiosity.

Our extensive catalog contains over 50,000 courses and twice as many books. Browse by search, by topic, or even by career interests. We'll match you to the right resources quickly.

Find this site helpful? Tell a friend about us.

Affiliate disclosure

We're supported by our community of learners. When you purchase or subscribe to courses and programs or purchase books, we may earn a commission from our partners.

Your purchases help us maintain our catalog and keep our servers humming without ads.

Thank you for supporting OpenCourser.

© 2016 - 2024 OpenCourser