RKCP Information Technology




MSSQL
Home » Courses   » MSSQL

MSSQL

This course provides the technical skills needed to write basic Transact-SQL queries for Microsoft SQL Server 2014.

Overview

MSSQL is the foundation for all SQL Server-related disciplines: namely, Database Administration, Database Development and Business Intelligence. It covers new features in SQL Server 2014, but also describes the important capabilities across the SQL Server data platform.


Objective

At the end of this MSSQL training, the participants will be able to:

  • Write SELECT queries
  • Query multiple tables
  • Sort and filter data - Describe the uses of data types in SQL Server
  • Modify data using Transact-SQL
  • Use built-in functions
  • Group and aggregate data
  • Use subqueries
  • Use table expressions
  • Use set operators
  • Use window ranking, offset and aggregate functions
  • Implement pivoting and grouping sets
  • Execute stored procedures
  • Program with T-SQL
  • Implement error handling
  • Implement transactions

Prerequisites

Need to Have

  • Basic knowledge of Microsoft Windows operations systems and its core functionality
  • Knowledge of working processes of Transact-SQL and relational databases
  • Experience in database design


Syllabus

Expand All
  • 1. Introduction to Microsoft SQL Server 2014
    • The Basic Architecture of SQL Server
    • SQL Server Editions and Versions
    • Getting Started with SQL Server Management Studio
    • Lab : Working with SQL Server 2014 Tools
  • 2. Introduction to T-SQL Querying
    • Introducing T-SQL
    • Understand Sets
    • Understand Predicate Logic
    • Understand the Logical Order of Operations in SELECT statements
    • Lab : Introduction to Transact-SQL Querying
  • 3. Writing SELECT Queries
    • Write Simple SELECT Statements
    • Eliminate Duplicates with DISTINCT
    • Use Column and Table Aliases
    • Write Simple CASE Expressions
    • Lab : Write Basic SELECT Statements
  • 4. Querying Multiple Tables
    • Understand Joins
    • Query with Inner Joins
    • Query with Outer Joins
    • Query with Cross Joins and Self Joins
    • Lab : Query Multiple Tables
  • 5. Sorting and Filtering Data
    • Sort Data
    • Filter Data with a WHERE Clause
    • Filter with the TOP and OFFSET-FETCH Options
    • Work with Unknown and Missing Values
    • Lab : Sort and Filter Data
  • 6. Working with SQL Server 2014 Data Types
    • Introduction to SQL Server 2014 Data Types
    • Work with Character Data
    • Work with Date and Time Data
    • Lab : Work with SQL Server 2014 Data Types
  • 7. Using DML to Modify Data
    • Insert Data
    • Modify and Delete Data
    • Lab : Use DML to Modify Data
  • 8. Using Built-In Functions
    • Write Queries with Built-In Functions
    • Use Conversion Functions
    • Use Logical Functions
    • Use Functions to Work with NULL
    • Lab : Use Built-In Functions
  • 9. Grouping and Aggregating Data
    • Use Aggregate Functions
    • Use GROUP BY Clause
    • Filter Groups with HAVING
    • Lab : Grouping and Aggregating Data
  • 10. Using Subqueries
    • Write Self-Contained Subqueries
    • Write Correlated Subqueries
    • Use the EXISTS Predicate with Subqueries
    • Lab : Use Subqueries
  • 11. Using Table Expressions
    • Use Derived Tables
    • Use Common Table Expressions
    • Use Views
    • Use Inline Table-Valued Functions
    • Lab : Use Table Expressions
  • 12. Using Set Operators
    • Write Queries with the UNION Operator
    • Use EXCEPT and INTERSECT
    • Use APPLY
    • Lab : Use Set Operators
  • 13. Using Window Ranking, Offset, and Aggregate Functions
    • Create Windows with OVER
    • Explore Window Functions
    • Lab : Use Window Ranking, Offset and Aggregate Functions
  • 14. Pivoting and Grouping Sets
    • Write Queries with PIVOT and UNPIVOT
    • Work with Grouping Sets
    • Lab : Pivoting and Grouping Sets
  • 15. Executing Stored Procedures
    • Query Data with Stored Procedures
    • Pass Parameters to Stored Procedures
    • Create Simple Stored Procedures
    • Work with Dynamic SQL
    • Lab : Execute Stored Procedures
  • 16. Programming with T-SQL
    • T-SQL Programming Elements
    • Control Program Flow
    • Lab : Programming with T-SQL
  • 17. Implementing Error Handling
    • Use TRY / CATCH Blocks
    • Work with Error Information
    • Lab : Implement Error Handling
  • 18. Implementing Transactions
    • Transactions and the Database Engine
    • Control Transactions
    • Isolation Levels
    • Lab : Implement Transactions