bandb

Menu
book appointment

SQL Development Course

Courses > SQL Development Course

Features of Our Courses

What does SQL entail?

SQL (Structured Query Language) is the universal standard for accessing, querying, and managing data within relational databases. It simplifies data manipulation by enabling users to retrieve, update, and manage records efficiently.

Why Choose SQL?

Proficiency in SQL is a valuable asset for anyone pursuing a career in data-centric fields, especially for web developers, data analysts, and software engineers. Its widespread adoption by major organizations, including Microsoft, Google, and Dell, underscores its significance. Mastering SQL not only enhances your career prospects but also equips you with critical skills applicable across industries.

Course Objectives:

This course focuses on Microsoft SQL Server, a robust relational database management system designed by Microsoft. Participants will learn how to store and retrieve data seamlessly for applications running on individual or networked systems. By the end of this course, learners will:

  • Master SQL commands and functions for database management.
  • Understand relational database principles and structures.
  • Develop skills in advanced querying and data visualization using Power BI.
  • Gain expertise in integrating SQL with other tools like SSIS, SSRS, and SSAS for business intelligence.

Pre-requisite / Target Audience:

Basic knowledge on SQL Server

Module 1: SQL Server Basics

In this module, the participants Get introduced to SQL Server and explore the foundational data types used in database design, including:

  • ExactNumeric
  • ApproximateNumeric
  • Dateand Time
  • CharacterStrings
  • UnicodeCharacter Strings
  • BinaryStrings
  • OtherData Types
  • TRYPARSE
  • TRYCONVERT
  • TRY CAST
  • Cast
  • Convert

Module 2: SQL Server Installation

In this module the participant delve in to step by step process on:

  • Download Software
  • Installation
  • Usage

Module 3: Keys

In this module the participant Understand key database concepts to maintain data integrity:

  • Download Software
  • Installation
  • Usage

Module 3: In this module the participant learn about Keys

  •  Super Key.
  • Candidate Key
  • Primary Key
  • Alternate key
  •  Composite/Compound Key
  • Unique Key
  • Foreign Key

Module 4: In this module the participants explore the core SQL command types:

  • Data Definition Language (DDL)
  • Data Manipulation Language (DML)
  • Data Query Language (DQL)
  • Transaction Control Language (TCL)
  • Data Control Language (DCL)

Module 5: In this module the participants learn about SQL Commands

  • INSERT / SELECT / UPDATE / DELETE: the basics of Data Manipulation Language
  • SELECT all rows and columns from a table
  • UPDATE Specific Row
  • DELETE All Rows
  • Comments in code
  • PRINT
  • Select rows that match a condition
  • UPDATE All Rows
  • TRUNCATE TABLE

Module 5: SQL INSERT Commands

  • INSERT multiple rows of data
  • Use OUTPUT to get the new Id
  • INSERT from SELECT Query Results
  • INSERT a single row of data
  • INSERT on specific columns
  • INSERT Hello World INTO table

Module 6: SQL INSERT Commands

In this module the participants master the essentials of data manipulation:

  • INSERT multiple rows of data
  • Use OUTPUT to get the new Id
  • INSERT from SELECT Query Results
  • INSERT a single row of data
  • INSERT on specific columns
  • INSERT Hello World INTO table

Module 7: SQL UPDATE Commands

In this module the participants dive into advanced commands, including:

  • Basic SELECT from table
  • Filter rows using WHERE clause
  • Sort results using ORDER BY
  • Group result using GROUP BY
  • Filter groups using HAVING clause
  • Returning only first N rows
  • Pagination using OFFSET FETCH
  • SELECT without FROM (no data source)

Module 8: SQL UPDATE Commands

In this module the participants learn to connect and retrieve data across multiple tables with joins:

  • Basic UPDATE
  • Update single/ multiple column in single query
  • Update using WHERE clause
  • Update using GROUP BY
  • Update using HAVING clause
  • Update using Case
  • Update using Join

Module 9: SQL ORDER BY & GROUP BY Commands

In this module the participants master SQL aggregate functions like:

  • Simple Grouping
  • GROUP BY multiple columns
  • GROUP BY with ROLLUP and CUBE
  • Group by with multiple tables, multiple columns
  • HAVING Simple ORDER BY clause Section 24.2: ORDER BY multiple fields
  • Custom Ordering
  • ORDER BY with complex logic

Module 10: SQL Alias & NULL Commands

In this module the participants get hands-on experience with BI tools:

Giving alias after Derived table name

  • Using AS
  • Using =
  • Without using AS
  • COALESCE ()
  • ANSI NULLS
  • ISNULL()
  • Is null / Is not null
  • NULL comparison
  • NULL with NOT IN SubQuery

Module 10: SQL Operator

  • Arithmetic Operators
  • Logical Operators
  • Set Operators
  • Comparison Operators
  • Special Operators
  • Assignment Operators
  • String Concat Operator
  • LIKE

Module 11: SQL View

  • Create a view
  • Create or replace view
  • CREATE Indexed VIEW
  • CREATE VIEW With Encryption
  • CREATE VIEW With INNER JOIN
  • Grouped VIEWs
  • UNION-ed VIEWs
  • Create a view with schema binding

Module 12: SQL Merge & Union

  • MERGE to Insert / Update / Delete
  • Merge Using CTE Source
  • Merge Example – Synchronize Source And Target Table
  • MERGE using Derived Source Table
  • Merge using EXCEPT
  • Union and union all

Module 13: SQL Join

  • Inner Join
  • LEFT Outer Join
  • RIGHT Outer Join
  • Using Join in an Update
  • Join on a Subquery
  • Cross Join
  • Self Join
  • Accidentally turning an outer join into an inner join
  • Delete using Join

Module 14: SQL Common Table Expression

  • Generate a table of dates using CTE
  • Employee Hierarchy
  • Recursive CTE
  • Delete duplicate rows using CTE
  • CTE with multiple AS statements
  • Find nth highest salary using CTE

Module 15: SQL Aggregate Function

  •  SUM()
  • AVG()
  • MAX()
  • MIN()
  • COUNT()
  • COUNT(Column_Name) with GROUP BY Column_Name
  • Using STUFF for string aggregation
  • String_Agg for String Aggregation
  • RANK()
  • DANS_RANK()
  • PIVOT()
  • UNPIVOT()
  • PATITION BY ()

Module 16: SQL Stored Procedure

  • Creating and executing a basic stored procedure
  • Parameter Stored Procedure
  • Stored Procedure with If…Else and Insert Into operation
  • Dynamic SQL in stored procedure
  • STORED PROCEDURE with OUT parameters
  • Simple Looping

Module 17: SQL Trigger

  • Type of Trigger
  • DML Trigger
  • DDL Trigger
  • Logon Trigger
  • Create
  • Update
  • Delete
  • Disable/Enable
  • Drop

Module 18: SQL Function

  • Type of Function
  • Scalar Function
  • Aggregate Function
  • Table – Valued Function
  • Create
  • Update
  • Delete
  • Disable/Enable
  • Drop

Module 19: SQL Index

  • Create Clustered index
  • Drop index
  • Create Non-Clustered index
  • Show index info
  • Returns size and fragmentation indexes
  • Reorganize and rebuild index
  • Rebuild or reorganize all indexes on a table
  • Rebuild all index database
  • Index on view
  • Index investigations
  • Full-Text Indexing

Module 20: SQL Cursor

  • Introduction to SQL cursor
  • Cursor life cycle
  • Why and when use a cursor
  • Implement cursors
  • Limitation of SQL cursor
  • Replacement of SQL Cursor

Module 21: SQL Temp

  • What is
  • Local & Global Temp Table
  • Create
  • Update
  • Delete
  • Drop

Module 22: SQL Try Catch

  • Transaction in a TRY/CATCH
  • Raising errors in try-catch block
  • Raising info messages in try catch block
  • Re-throwing exception generated by RAISERROR
  • Throwing exception in TRY/CATCH blocks

Module 23: SQL variable

  • Declare a Table Variable
  • Updating variables using SELECT
  • Declare multiple variables at once, with initial values
  • Updating a variable using SET
  • Updating variables by selecting from a table
  • Compound assignment operators

Module 23: SQL IF – Else & Loop

  • Single IF statement
  • Multiple IF Statements
  • Single IF..ELSE statement
  • Multiple IF… ELSE with final ELSE Statements
  • Multiple IF…ELSE Statements
  • Using While Loop
  • While Loop with join

  • Data warehousing & Microsoft Business Intelligence

Introduction to Data warehousing & Microsoft Business Intelligence

This course offers a comprehensive exploration of SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), SQL Server Analysis Services (SSAS), and Power BI. It is designed to equip you with the skills necessary to manage data, create visualizations, and develop BI solutions. Below is a detailed breakdown of the course content:

SQL Server Integration Services (SSIS)

Introduction to SSIS:

  • Overview of SSIS architecture
  • Development tools and the Import/Export Wizard
  • Guided tour of Business Intelligence Development Studio

Core Concepts & Features:

  • SSIS package architecture and features
  • ETL (Extract, Transform, Load) concepts and processes
  • Data flow and control flow tasks

Data Flow Management:

  • Working with external data sources (Excel, flat files, OLE DB)
  • Data transformation techniques: merging, appending, splitting, and removing columns
  • Creating custom tables and columns
  • Using transformations such as Derived Column, Conditional Split, and Lookup

Advanced Data Flow:

  • Managing complex data flows with Lookup, Cache, and Multicast transformations
  • Utilizing Row Count, Sort, and Merge transformations

Variables & Control Flow:

  • Overview of variables and scope
  • Using variables in control flow and data flow
  • Introduction to property expressions

Control Flow Tasks:

  • Key task types: Execute SQL Task, File System Task, Web Service Task, and more
  • Implementing Precedence Constraints and Sequence Containers

Debugging & Logging:

  • Debugging SSIS packages with breakpoints
  • Error handling techniques and logging

Package Deployment & Management:

  • Deploying SSIS packages using SSISDB
  • Scheduling and managing packages with SQL Server Agent

Power BI

Introduction to Power BI:

  • Overview of Power BI Desktop and Power BI Services
  • Understanding Power BI components and architecture

Data Connectivity & Transformation:

  • Connecting to various data sources (Excel, flat files, databases)
  • Using Query Editor for data import and transformations
  • Handling null values, errors, and data types

Data Transformation Techniques:

  • Merging, appending, and splitting queries
  • Creating custom tables, index columns, and calculated columns
  • Grouping and pivoting data in Query Editor

Data Visualization:

  • Creating visualizations with tables, matrices, and cards
  • Defining relationships between tables and building interactive dashboards
  • Working with custom visualizations and filters

Advanced Power BI Features:

  • Implementing calculated measures, hierarchies, and custom visuals
  • Using advanced data flow tools for transformation

SQL Server Reporting Services (SSRS)

Basic Reporting:

  • Designing table, matrix, and chart reports
  • Utilizing parameters for user interactivity

Advanced Reporting Techniques:

  • Implementing drillthrough, cascading reports, and custom expressions
  • Creating and configuring charts and gauges

Cube-based Reporting:

  • Building reports based on data cubes, including aggregations and summarizations

SQL Server Analysis Services (SSAS)

Data Warehousing Concepts:

  • Working with dimensions, facts, and schemas
  • Understanding relationships and data source views

Cube Design & Management:

  • Designing and deploying OLAP cubes
  • Processing cubes and implementing partitions

Advanced SSAS Features:

  • Aggregations and storage modes (MOLAP, ROLAP, HOLAP)
  • Writing MDX queries to analyze cube data

Additional Topics

Business Intelligence (BI) Life Cycle:

  • Understanding the BI life cycle and its impact on decision-making processes

Data Visualization with Power BI:

  • Creating interactive dashboards and effective data visualizations