Develop practical database development skills with Database Development with Microsoft SQL Server Training, a hands-on course designed for developers, database professionals, and IT specialists who want to design, build, and manage reliable database solutions using Microsoft SQL Server.
Duration 5 Days – 35 hrs.
Overview
This course provides participants with the essential knowledge and practical skills required to design, develop, query, and maintain databases using Microsoft SQL Server and Transact-SQL (T-SQL).
Participants will learn how to create relational database structures, retrieve and manipulate data, combine data from multiple tables, build reusable database objects, implement data integrity, manage transactions, and improve query performance. The course also introduces stored procedures, functions, views, triggers, error handling, indexing, and database security fundamentals.
Hands-on exercises reinforce each topic and culminate in a practical database development project.
Learning Objectives
- Explain relational database and Microsoft SQL Server concepts.
- Use SQL Server Management Studio and related database development tools.
- Design normalized relational database structures.
- Create databases, schemas, tables, constraints, and relationships.
- Retrieve data using T-SQL SELECT statements.
- Filter, sort, group, summarize, and transform query results.
- Combine data using joins, subqueries, common table expressions, and set operators.
- Insert, update, delete, and merge database records.
- Create and use views, stored procedures, user-defined functions, and triggers.
- Implement transactions, error handling, and concurrency controls.
- Apply indexes and basic query optimization techniques.
- Implement fundamental database permissions and security practices.
- Develop and test a functional SQL Server database solution.
Target Audience
- Aspiring database developers
- Junior database developers
- Software and application developers
- Systems analysts and programmer analysts
- Data analysts and reporting professionals
- Database administrators who support development activities
- Quality assurance and test engineers working with databases
- IT professionals who need practical Microsoft SQL Server development skills
- Technical personnel responsible for database-driven applications
Prerequisites
- Basic computer and Windows navigation skills
- General understanding of data, records, tables, and business applications
- Basic programming or scripting knowledge, although not mandatory
- Familiarity with relational database concepts is helpful but not required
- Participants should have access to a computer with Microsoft SQL Server Developer Edition or an equivalent SQL Server environment and SQL Server Management Studio.
Course Outline
Day 1 — Microsoft SQL Server and Relational Database Foundations
Module 1: Introduction to Database Development
- Database systems and relational database concepts
- Role of a database developer
- Overview of the Microsoft SQL Server platform
- SQL Server editions and major components
- Databases, schemas, tables, rows, and columns
- Introduction to Transact-SQL
- Using SQL Server Management Studio
- Connecting to a SQL Server instance
- Creating and executing T-SQL scripts
Module 2: Relational Database Design
- Identifying entities, attributes, and relationships
- Primary keys and foreign keys
- One-to-one, one-to-many, and many-to-many relationships
- Entity-relationship diagrams
- Data redundancy and update anomalies
- First, second, and third normal forms
- Translating business requirements into database structures
- Naming conventions and design standards
Module 3: Creating Database Structures
- Creating and configuring databases
- Creating schemas and tables
- Selecting appropriate SQL Server data types
- Identity columns and sequences
- Defining primary and foreign keys
- Applying NOT NULL, UNIQUE, CHECK, and DEFAULT constraints
- Maintaining referential integrity
- Altering and removing database objects
Hands-on Laboratory: Design and create a normalized SQL Server database.
Day 2 — Data Retrieval and Querying
Module 4: Writing Basic T-SQL Queries
- Structure of the SELECT statement
- Selecting columns and creating aliases
- Expressions and calculated columns
- Removing duplicate results with DISTINCT
- Sorting results using ORDER BY
- Limiting returned rows
- Understanding logical query-processing order
- Working with NULL values
Module 5: Filtering and Transforming Data
- Filtering with the WHERE clause
- Comparison and logical operators
- Using IN, BETWEEN, and LIKE
- Pattern matching
- Data type conversion using CAST, CONVERT, and TRY_CONVERT
- Working with string functions
- Working with date and time functions
- Mathematical and system functions
- Conditional logic using CASE
- Handling missing values with ISNULL and COALESCE
Module 6: Aggregating Data
- Aggregate functions
- Grouping records using GROUP BY
- Filtering groups using HAVING
- Basic statistical and summary queries
- Grouping sets, rollups, and cubes
- Creating management and operational summaries
Hands-on Laboratory: Create filtered, calculated, and summarized business queries.
Day 3 — Advanced Querying and Data Manipulation
Module 7: Combining Data from Multiple Sources
- Understanding table aliases
- INNER JOIN
- LEFT, RIGHT, and FULL OUTER JOIN
- Self joins and cross joins
- Joining multiple tables
- Avoiding duplicate and incorrect join results
- Combining results using UNION, UNION ALL, INTERSECT, and EXCEPT
Module 8: Subqueries and Advanced Query Structures
- Scalar and multi-row subqueries
- Correlated subqueries
- Using EXISTS and NOT EXISTS
- Derived tables
- Common table expressions
- Recursive common table expressions
- Introduction to window functions
- Ranking functions
- Aggregate and analytical window functions
- Choosing between joins, subqueries, and CTEs
Module 9: Modifying Data
- Adding records using INSERT
- Updating records using UPDATE
- Removing records using DELETE
- Using MERGE appropriately
- Retrieving affected data using the OUTPUT clause
- Importing data with INSERT…SELECT
- Safely testing data-modification statements
- Maintaining data integrity during data changes
Hands-on Laboratory: Develop multi-table queries and perform controlled data modifications.
Day 4 — Developing Reusable Database Objects
Module 10: Views
- Purpose and benefits of views
- Creating and modifying views
- Querying data through views
- Updatable views
- Schema binding
- Using views for abstraction and security
- View design considerations
Module 11: Stored Procedures
- Purpose of stored procedures
- Creating and executing stored procedures
- Input and output parameters
- Returning result sets and status values
- Using conditional logic
- Local variables and control-of-flow statements
- Building dynamic SQL safely
- Avoiding SQL injection
- Stored procedure development practices
Module 12: User-Defined Functions and Triggers
- Scalar user-defined functions
- Inline table-valued functions
- Multi-statement table-valued functions
- Choosing between functions and stored procedures
- DML triggers
- Working with inserted and deleted logical tables
- AFTER and INSTEAD OF triggers
- Appropriate uses and limitations of triggers
- Auditing database changes
Hands-on Laboratory: Create views, parameterized stored procedures, functions, and an audit trigger.
Day 5 — Transactions, Performance, Security, and Practical Project
Module 13: Transactions and Error Handling
- Understanding ACID properties
- Autocommit and explicit transactions
- Using BEGIN TRANSACTION, COMMIT, and ROLLBACK
- Transaction scope and savepoints
- Implementing structured error handling with TRY…CATCH
- Using THROW
- Isolation levels and concurrency
- Locking, blocking, and deadlocks
- Designing reliable transactional operations
Module 14: Indexing and Query Performance
- Clustered and nonclustered indexes
- Primary-key and unique indexes
- Composite and covering indexes
- Included columns
- Index selectivity and appropriate index design
- Reading basic execution plans
- Table scans, index scans, and index seeks
- Identifying inefficient queries
- Statistics and query optimization
- Parameterization and parameter-sensitivity awareness
- Avoiding common SQL performance problems
Module 15: Database Security and Development Practices
- SQL Server authentication concepts
- Logins, users, roles, and schemas
- Granting, denying, and revoking permissions
- Principle of least privilege
- Ownership chaining
- Protecting sensitive data
- Parameterized queries and SQL injection prevention
- Source control and database change scripts
- Testing, documenting, and deploying database objects
Module 16: Capstone Database Development Project
- Analyze a business scenario.
- Design a normalized database.
- Create tables, keys, relationships, and constraints.
- Load and maintain sample data.
- Develop operational and summary queries.
- Create views, stored procedures, and functions.
- Implement transactions and error handling.
- Apply suitable indexes.
- Configure basic database permissions.
- Present and demonstrate the completed solution.
