A tanfolyamról
Database systems are a key component to most modern applications. You can use a variety of such systems to store data efficiently and effectively. PostgreSQL is a diverse and powerful database system, designed to give you the tools required to manage and analyze your data. In this course, you will explore various methods of building databases in PostgreSQL.
You will get an understanding of PostgreSQL infrastructure, how SQL interacts with PostgreSQL objects, as well as how to secure and maintain your database systems. Once you have a grasp on the fundamentals of PostgreSQL, you will see how to create full stack applications using PERN stack and Java Spring, which interact with a PostgreSQL database. By the end of this course, you will feel comfortable working with PostgreSQL through psql, pgAdmin, and various tech stacks.
Who Should Attend This Course
- Software Developers
- Full-Stack Developers
- Data Analysts
- Database Administrators
What You Will Learn
-
Fundamentals of PostgreSQL
Learn how relational databases work, their fundamentals and how they are relevant in today’s times -
Understanding PostgreSQL
Learn everything about PostgreSQL, including the type of database management system it is. -
Installation
Build the necessary skills to understand how to install and setup PostgreSQL and run queries on it -
Create Applications
Understand how to create applications with PostgreSQL as a back-end database management system -
Configuration
Build skills to understand the Setting PostgreSQL Parameters, Connection Settings, and Security and Authentication -
PostgreSQL System Architecture
Understand the architectural summary of PostgreSQL system, including shared memory and statement processing -
PERN Stack
Build a full-stack web application using the PERN (PostgreSQL, Express, React and Node.js) Stack
Skills You Will Gain
- Running queries to create tables
- Updating and deleting data
- Performing calculations on data
- Constructing database objects
- Applying common table expressions
- Maintaining a database
- Analyzing query efficiency
- Building PERN stack applications
- Building Spring apps with PostgreSQL
During this 24-hour course, a variety of topics will be covered with the aim of making you a PostgreSQL expert.
The following are the practical skills you’ll gain on course completion:
- Understand how PostgreSQL functions as a database system
- Understand the fundamentals of object relational databases
- Create databases and tables
- Learn to manage data in tables
- Learn to analyze data in tables
- Create and manage database objects such as views, functions, indexes, user-defined types and rules
- Create advanced queries with joins, unions, common table expressions, and window functions
- Manage the security, access control, and maintenance of a PostgreSQL database
- Develop a basic PERN stack application
- Learn to use PostgreSQL with Java Spring applications
We provide the course in English.
Tematika
Curriculum
Module 1: Introduction to Database Systems
Learning Objectives:
There many types of databases and PostgreSQL is identified as an object-relational database. You will get an overall idea about various databases and the significance of PostgreSQL. In addition, you will learn to install PostgreSQL.
Topics
- Types of Databases
- Object-relational Database
- Introduction to PostgreSQL
- Installing PostgreSQL
Module 2: Fundamentals of SQL for Postgres
Learning Objectives:
Postgres enables you to access tables and databases through a SQL interface and a GUI called pgAdmin. You will learn to work through both these clients and perform various queries on tables.
Topics
- Creating Databases
- Creating Tables with psql
- Creating Tables with pgAdmin
- Inserting Data into the Table
- Updating and Removing Data
- Selecting and Retrieving Data
- Altering Tables
- Aggregations and Groups
- Types of Aggregates
- Boolean Operations and WHERE Clause
- In-line Operators
- Nested Queries
3.Module 3: Postgres Foundations
Learning Objectives:
You will learn to work with basic components and objects of PostgreSQL that enable you to build a robust database system. This includes various templates, schemas, views, indexes, functions, and many more utilities.
Topics
- Database Objects
- Roles and Tablespace
- Database Components
- Database Views
- Database Indexes
- Database Functions
- User-defined Data Types
- Triggers and Rule System
Module 4: Advanced Query Writing
Learning Objectives:
You will learn to write more queries which enable advanced operations on tables and databases. The queries include joins, unions, CTEs, window functions, etc.
Topics
- Inner Joins
- Left and Right Joins
- Full Joins
- Unions
- Common Table Expressions (CTE)
- Window Functions
Module 5: Postgres Security
Learning Objectives:
Learn the ways to implement security concepts such as, authentication and limiting connections to the database. Also, learn to create roles, encrypt data, clean the database, and generate query analysis.
Topics
- Authentication in PostgreSQL
- Access and Security Control
- Encrypting Data
- Routine Database Maintenance
- Evaluating Queries
Module 6: PERN Stack Implementation
Learning Objectives:
Create a simple Node.js application with a React front-end and a React-based API using Express. You will go through each step needed to create the application.
Topics
- Setting Up the PERN Stack
- Creating Connections with Prisma
- Understanding Prisma Models
- Defining Prisma Models
- Setting Up Express
- Creating POST Requests
- Testing POST Requests
- Creating GET Requests (Part 1)
- Creating GET Requests (Part 2)
- Creating PUT Requests
- Creating DELETE Requests
- Setting Up React
- Inputting Expense Data (Part 1)
- Inputting Expense Data (Part 2)
- Listing Expense Data (Part 1)
- Listing Expense Data (Part 2)
Module 7: Spring Boot Implementation
Learning Objectives:
Learn to create an application by integrating PostgreSQL into Java Spring. You will learn the required process steps to create this application.
Topics
- Setting Up a Spring Application
- Setting Up the PostgreSQL Connection
- Defining Schemas and Data
- Creating a Database Model (Part 1)
- Creating a Database Model (Part 2)
- Creating Repositories and Services
- Creating a Controller
- Creating a Frontend
Module 8: PostgreSQL Arrays, Keys, and Select Clauses
Learning Objectives:
Learn features that will enable you to establish and maintain relationship between multiple tables.
Topics
- PostgreSQL Arrays
- PostgreSQL 2D Arrays
- PostgreSQL Custom Data Types
- Key Relationships
- Defining Key Relationships
- Fetch and Limit
- Distinct and Duplicates
- Case Statements
Module 9: Capstone
Learning Objectives:
For the final capstone project, you will build an interactive application with a database system that includes various components and database objects.
Topics
- Build an interactive application with a DBMS
Kinek ajánljuk
Előfeltételek
Prerequisites
Fundamental knowledge of the following is required:
- Basics of SQL
- Database concepts