Duration 5 Days – 35 hrs.

 

Overview

 

The Database Development (Microsoft SQL Training or PostgreSQL Fundamentals) Training Course is a comprehensive, hands-on program designed for IT professionals, software developers, database administrators, system analysts, business intelligence practitioners, and technical staff who need to develop, manage, and query relational databases using either Microsoft SQL Server or PostgreSQL.

 

This course provides participants with a strong foundation in relational database concepts, Structured Query Language (SQL), database design principles, data manipulation, indexing, views, stored procedures, functions, transactions, performance optimization, backup and recovery, and security best practices. Practical laboratory exercises enable participants to build real-world database applications while learning industry-standard database development techniques.

 

Objectives

 

• Understand relational database concepts and architecture.
• Install and configure Microsoft SQL Server or PostgreSQL.
• Design normalized relational databases.
• Create and manage databases, tables, and relationships.
• Write SQL queries for data retrieval and reporting.
• Perform data manipulation using INSERT, UPDATE, DELETE, and MERGE statements.
• Build joins, subqueries, Common Table Expressions (CTEs), and set operations.
• Develop stored procedures, functions, and database views.
• Implement indexes and optimize SQL query performance.
• Manage transactions and concurrency.
• Perform database backup and restoration.
• Apply database security and user management best practices.
• Troubleshoot common database performance and development issues.

 

Target Audience

 

• Software Developers
• Database Developers
• Database Administrators (Junior to Intermediate)
• Application Developers
• Business Intelligence Developers
• Data Analysts
• System Analysts
• Backend Developers
• IT Support Engineers
• DevOps Engineers
• Technical Consultants
• IT Professionals responsible for database development

 

Prerequisites

 

• Basic computer proficiency
• Familiarity with Windows or Linux operating systems
• Basic understanding of programming concepts is beneficial but not mandatory
• Basic knowledge of information systems is recommended

 

Course Outline

 

Day 1 – Database Fundamentals and SQL Environment

 

Module 1: Introduction to Relational Databases

 

• Database concepts
• DBMS overview
• Relational database principles
• ACID properties
• Database architecture
• Microsoft SQL Server vs PostgreSQL overview

 

 

 

Module 2: Database Installation and Configuration

 

• Installing SQL Server or PostgreSQL
• Database tools
• SQL Server Management Studio (SSMS) or pgAdmin
• Database configuration
• Creating databases
• Database objects

 

Module 3: Database Design Fundamentals

 

• Tables
• Columns
• Data types
• Primary keys
• Foreign keys
• Relationships
• Constraints

Hands-on Lab

• Install database platform
• Create databases
• Create tables
• Define relationships

 

Day 2 – SQL Query Fundamentals

 

Module 4: SQL Language Basics

 

• SELECT statements
• Filtering data
• Sorting
• DISTINCT
• TOP/LIMIT
• Aliases

 

Module 5: Data Manipulation Language (DML)

 

• INSERT
• UPDATE
• DELETE
• MERGE/UPSERT concepts
• Transactions

 

Module 6: Query Functions

 

• Aggregate functions
• GROUP BY
• HAVING
• Date functions
• String functions
• Numeric functions
• NULL handling

Hands-on Lab

• CRUD operations
• Aggregate reporting
• Data filtering
• Transaction exercises

 

Day 3 – Advanced SQL Development

 

Module 7: Advanced Queries

 

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
• CROSS JOIN
• SELF JOIN

 

Module 8: Subqueries and Common Table Expressions

 

• Nested queries
• Correlated subqueries
• EXISTS
• IN
• CTEs
• Recursive CTEs

 

Module 9: Views and Temporary Objects

 

• Views
• Materialized views (PostgreSQL)
• Temporary tables
• Table variables (SQL Server)
• Common use cases

Hands-on Lab

• Multi-table reporting
• Complex joins
• Recursive queries
• View creation

 

Day 4 – Database Programming

 

Module 10: Stored Procedures

 

• Procedure creation
• Parameters
• Error handling
• Output parameters

 

Module 11: Functions

 

• Scalar functions
• Table-valued functions
• User-defined functions

 

Module 12: Triggers and Transactions

 

• INSERT triggers
• UPDATE triggers
• DELETE triggers
• Transaction management
• Rollback
• Commit
• Exception handling

Hands-on Lab

• Develop stored procedures
• Build functions
• Create triggers
• Transaction testing

 

Day 5 – Performance, Administration and Best Practices

 

Module 13: Database Performance Optimization

 

• Indexes
• Clustered indexes
• Non-clustered indexes
• Query execution plans
• Query optimization
• Statistics

 

Module 14: Backup and Recovery

 

• Full backup
• Differential backup
• Transaction log backup (SQL Server)
• Point-in-time recovery
• Restore procedures

 

Module 15: Security and User Management

 

• Authentication
• Authorization
• Roles
• Permissions
• Encryption overview
• Auditing

 

Module 16: Database Development Best Practices

 

• Naming conventions
• Version control
• Source code management
• Database deployment
• Documentation
• Performance tuning checklist

Hands-on Lab

• Performance tuning
• Index optimization
• Backup and restore
• Security configuration
• Final integrated database development exercise