Database Development introduces learners to the fundamentals of designing databases, writing SQL queries, managing relational data, and developing database applications using Microsoft SQL Server or PostgreSQL.
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
