SQL Server Database Course Syllabus

Course description

The objective of this course is providing learners with job-ready skills in SQ Server database development/administration. The course has has 11 modules. In Module 1 we will introduce you to information technology concepts like computer, computer hardware, computer software, the binary system and computer networks. In the remaining 10 modules students will learn about relational database concepts in line with practical demonstration of using SQL Server. SQL Server is one of the most popular DBMS used in business today. You will learn how to use SSMS and T-SQL to create and manage numerous database objects such as tables, views, stored procedures, functions, and indexes. There are a lot of practical demonstration and hands on exercise in every module. Students will also get the opportunity to work on a toy project on database development where you show case the skills you have learned in the course. Finally, you will learn the basics of business intelligence and related tools. Upon successful completion of the course students will be able to:

  1. Explain the various relational database concepts such as tables, primary key, foreign key, data types, views, procedures, functions, triggers, indexes
  2. Identify the components SQL Server Database including system database
  3. Explain the difference between DDL, DML and DCL SQL commands/statements
  4. Use T-SQL (DDL) to create database objects including tables, views, procedures, functions, and indexes
  5. Write various select (DML) statements to manipulate data: retrieve data from tables, sort data, aggregate data by using various data base functions.
  6. Write T-SQL statements to populate table with data.
  7. Create T-SQL statements to retrieve specific records from tables by using expressions
  8. Use database functions such as Date functions and string functions
  9. Write T-SQL code to retrieve data from multiple related tables by using join
  10. Create different types of subqueries and CTE
  11. Write accurate T-SQL code to analyze data.
  12. Create stored procedure to implement business logic
  13. Use database system functions
  14. Perform various database administration tasks such as backup, restore, managing security and user roles
  15. Use SQL Server Agent to manage jobs
  16. Explain BI concepts and tools

Module 1 – Introduction

Introduction to Information Technology

  • What is IT
  • IT uses in business
  • Computer
    • Definition
    • Components
      • Hardware
        • Processor, Memory, Storage, and I/O devices
        • Computer architecture
        • Data representation in computer
          • Binary system
          • ASCII
          • Bit and Byte
        • Software
          • Application software
            • Word processors, spreadsheets, DBMS, utility apps
          • System Software
            • Operating system
              • Functions
              • Types
              • File management
              • Customization
            • Computer system specifications
            • Virtualization
              • Download and Install VMWare Workstation Player
              • Create a Virtual Machine
              • Download and Install Windows Server
              • Virtual Machine Management
            • Computer network
              • Definition and use/purpose
              • Types of networks (LAN, WAN)
              • Internet, Intranet, VPN
              • OSI model
              • Networking hardware
                • Router, bridge, cables, modem, servers
              • IP address
                • Ipv4 vs IPv6
                • How to get your pc’s IP address
                • DNS, DHCP

Module 2 – Introduction to Relational Database and Database Management Systems (DBMS)

  • Introduction to Database systems and design
    • What is relational database?
    • What is Relational Database and Relational Database Management System (RDBMS)?
      • Popular DBMSs and vendors
    • Components of database
    • Components of a DBMS
    • DBMS features
    • Database systems vs file systems
    • What is relational data modeling
    • How is data stored?
    • What is a Table, COLUMN and ROW ?
    • What is a key? primary, foreign, unique keys etc.
    • What is a Transaction and ACID properties?
    • Database Normalization AND Different forms of Database Normalization

Module 3 Introduction to MS SQL Server

  • Download and install MSSQL and SQL Server Management Studio
    • System requirements
    • Install and Configure MSSQL
    • Install SQL Server Management Studio
    • Download and Install AdventureWorks sample Database
  • Exploring SQL Server Management Studio (SSMS)
    • GUI
    • T-SQL

Module 4 – Working with Database in SSMS

  • Data types in SQL Server
  • SQL Server Database identifiers
  • The three major types of SQL commands and how to use them
    • DDL (Data Definition Language) commands,
    • DML (Data Manipulation Language) commands, and
    • DCL (Data Control Language) commands
  • How to create a Database and Table: defining data types, identities, nulls, naming
  • How to populate your table with data using GUI and T-SQL
  • How to use user defined temp tables

Module 5 – Query and Manipulation of Data using SQL

  • SELECT Statement in detail
  • Components of SELECT statement
    • WHERE Clause, ORDER BY, HAVING BY, GROUP BY Clause
  • Operators, Expressions and Conditions
  • Select from two tables – JOINS
    • Different Types of JOINS
  • Sub Query
    • What is a Sub Query?
    • Types of subqueries
  • INSERT, UPDATE, DELETE and Truncate Statements
  • SQL database functions
    • Aggregate functions
      • AVG, Count, Sum, Max, MIN
    • Ranking functions
    • Mathematical functions
    • Scalar functions
      • String functions,
      • Date functions
      • Conversion functions
    • System functions
  • More Data base objects
    • Views
    • Store Procedure
    • Function, Trigger and INDEX
    • Clustered, Non-clustered Indexes and Index Design considerations
    • Index Fragmentation
    • Try…Catch T-SQL construct to implement error handling in SQL Server

Module 6 – Microsoft SQL Database Administration

  • Overview of MSSQL Management Tools
  • Exploring SQL Server Configuration Manager
  • MSSQL System Databases
    • What is master, tmpdb, msdb and other system databases?
  • MSSQL Backup and Restore
    • Backup of a Database, Importance for DBA and Different Media used for Backups
    • Recovery Models in MSSQL and how that impact backups an restore
    • Full and differential backups
    • Transaction Log Backups, Log Backup Chain and Tail-log Backup
    • Restore and Recovery Overview
    • Full, Differential and Transaction Log database restore
    • Point in time restore of a database
    • Complete Backup , Restore and Restore in Time Lab
    • MS SQL Server DBCC CHECKDB command
    • Page Level Restore / Recovery using Full Backups
    • Creating Maintenance Plan Backups/Re-indexing etc.

Module 7 – MSSQL Security and User Management

  • Authentication vs authorization
  • MSSQL Security Model
  • MSSQL Server Authentication Modes
  • Server Logins
  • Server Roles
  • Database Users
  • Database Roles
  • Permissions (GRANT , DENY, and REVOKE)

Module 8 – MSSQL Server Agent Management

  • SQL Server Agent Introduction
  • SQL Server Agent Jobs and Schedules
  • Agent Alerts
  • Operators
  • Database Mail
  • Activity Monitor

Module 9 – Advanced SQL Server Administration Topics

  • High Availability and its Types
  • What Is Replication and Transactional Replication
  • Log Shipping and Configuring Log Shipping
  • SQL Server Encryption and Encryption Types
  • Transparent Data Encryption (TDE) and configuration

Module 10 – Introduction to Business intelligence

  • What is BI?
  • Popular BI tools
  • ETL tools

Module 11 – Project, Summary and Looking forward

  • Database development project
  • Summary of what you have learned
  • Job hunting
    • Resume design
    • Interview preparation

Technical Requirements

This course is delivered 100% online, and students are required to have:

  • access to a computer or laptop
  • consistent access to the Internets