Mastering SQL using Postgresql¶
This course is primarily designed to learn basic and advanced SQL using Postgresql Database.
Postgresql is one of the leading datatabase. It is an open source database and used for different types of applications.
Data Logging Applications
Even though it is relational database and best suited for transactional systems (OLTP), it’s flavors such as Redshift are extensively used for Analytical or Decision Support Systems.
This course is primarily designed to go through basic and advanced SQL using Postgres Database. You will be learning following aspects of SQL as well as Postgres Database.
Setup Postgres Database using Docker
Connect to Postgres using different interfaces such as psql, SQL Workbench, Jupyter with SQL magic etc.
Understand utilities to load the data
Overview of Normalization Principles and Relations (RDBMS Concepts)
Performing CRUD or DML Operations
Writing basic SQL Queries such as filtering, joins, aggregations, sorting etc
Creating tables, constraints and indexes
Different partitioning strategies while creating tables
Using pre-defined functions provided by Postgresql
Writing advanced SQL queries using analytic functions
Overview of query performance tuning with emphasis on explain plans and different tuning techniques
Difference between RDBMS and Data Warhousing with live examples.
Here are the desired audience for this course.
College students and entry level professionals to get hands on expertise with respect to SQL to be prepared for the interviews.
Experienced application developers to understand key aspects of Databases to improve their productivity.
Data Engineers and Data Warehouse Developers to understand the relevance of SQL and other key concepts.
Testers to improve their query writing abilities to validate data in the tables as part of running their test cases.
Business Analysts to write ad-hoc queries to understand data better or troubleshoot data quality issues.
Any other hands on IT Professional who want to improve their query writing and tuning capabilities.
Developers from non CS or IT background at times struggle in writing queries and this course will provide required database skills to take their overall application development skills to next level.
Here are the prerequisites before signing up for the course.
Computer with decent configuration
At least 4 GB RAM
8 GB RAM is highly desired
High Speed Internet
Engineering or Science Degree
Ability to use computer
Knowledge or working experience with databases is highly desired
The course is designed for the professionals to achieve these key objectives related to databases using Postgresql.
Ability to interpret data models.
Using database IDEs to interact with databases.
Data loading strategies to load data into database tables.
Write basic as well as advanced SQL queries.
Ability to create tables, partition tables, indexes etc.
Understand and use constraints effectively based up on the requirements.
Effective usage of functions provided by Postgresql.
Understand basic performance tuning strategies
Differences between RDBMS and Data Warehouse concepts by comparing Postgresql with Redshift.
This course is primarily designed to gain key database skills for application developers, data engineers, testers, business analysts etc.
Here are the details related to the training approach.
It is self paced with reference material, code snippets and videos.
One can either use environment provided by us or setup their own environment using Docker.
Modules will be published as and when they are ready. We would recommend to complete 2 modules every week by spending 4 to 5 hours per week.
It is highly recommended to take care of the exercises at the end to ensure that you are able to meet all the key objectives for each module.
Support will be provided either through chat or email.
For those who signed up, we will have weekly monitoring and review sessions to keep track of the progress.
Spend 4 to 5 hours per week up to 8 weeks and complete all the exercises to get best out of this course.
The course is designed in such a way that one can self evaluate through the course and confirm whether the skills are acquired.
Here is the approach we recommend you to take this course.
Go through the consolidated exercises and see if you are able to solve the problems or not.
Make sure to follow the order we have defined as part of the course.
After each and every section or module, make sure to solve the exercises. We have provided enough information to validate the output of your queries.
After the completion of the course try to solve the exercises using consolidated list.
Keep in mind that you will be reviewing the same exercises before the course, during the course as well as at the end of the course.
By the end of the course, if you are able to solve the problems, then you can come to a conclusion that you are able to master the key skill called as SQL.