zorba

book appointment

SQL Development Course

Courses>> SQL Development Course

Features of Our Courses

SQL Development Course

This training aims to groom the database management skills of database administrators, SQL Developers, Database Engineers and so on.

Why SQL ?

SQL is most useful in data-focused careers and valuable for Web Developers and software Engineers. SQL has been accepted as a standard by many international governmental and organizational entities. Giant tech companies like Microsoft, Dell, and Google also use SQL to manage their databases. Having SQL skills can help you succeed in any industry or organization you work in. Upskilling to SQL could be a wonderful method to advance your current career and add to your technological toolkit.

Overview

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network.

Course Objectives:

The objective of a SQL Server Development course is to teach students how to write efficient and effective SQL code for managing and querying databases. This includes mastering T-SQL (Transact-SQL) for developing stored procedures, functions, and triggers. The course also focuses on performance optimization techniques and best practices for writing complex queries. Additionally, it aims to provide hands-on experience with real-world scenarios, ensuring students can apply their skills in practical situations. Ultimately, the goal is to prepare students to develop robust, high-performing database applications.

The prerequisites for a SQL Server Programming course tailored for a layman would be:

Basic Computer Literacy: Comfort with using a computer, including operating systems, file management, and basic software applications.

Basic Understanding of Databases: Familiarity with the concept of a database, such as knowing that databases store data in an organized manner.

Introductory Knowledge of SQL: Awareness of what SQL is and its basic purpose (e.g., retrieving and manipulating data), even if there is no hands-on experience.

Logical Thinking and Problem-Solving Skills: Ability to think logically and solve basic problems, as these skills are crucial for understanding programming concepts.

Willingness to Learn Technical Concepts: Openness to learning new technical concepts and terminologies.

These prerequisites ensure that even a beginner with no prior technical background can start the course and build a foundation in SQL Server programming.

Module 1: SQL Server Basics

In this module, we can learn about automation testing and when to automate the application. We can also look into different automation tools in the market, and we will discuss about advantages and disadvantages of automation tool.

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

Module 2: SQL Server Installation

  • Download Software
  • Installation
  • Usage

Module 3: Keys

  • Download Software
  • Installation
  • Usage

Module 3: Keys

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

Module 4: Basics of SQL Commands

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

Module 5: 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 SELECT Commands

  • 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 7: SQL UPDATE Commands

  • 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 8: SQL ORDER BY & GROUP BY Commands

  • 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 9: SQL Alias & NULL Commands

  • 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

  • Course Agenda:
  • SQL Server Integration Services (SSIS)
  • SQL Server Reporting Services (SSRS)
  • SQL Server Analysis Services (SSAS)

ETL concepts
BI Life cycle
SSIS Architecture
Features
Introduction to the tool
SQL Server Integration Services 


Introduction to SQL Server Integration Services
– SSIS Package Architecture Overview
– Development and Management Tools
– The Import and Export Wizard

Guided tour of Business Intelligence Development Studio

Introduction

Introduction to Power BI
Introduction to Excel
Relationship between Excel &Power BI
Understanding Excel BI add
Why power BI
Downloading and installing Power BI.
Power BI Desktop AND ;Power BI Services.
Building blocks of Power BI

Query Editor
Different external data source
Fetching data from different files
Working with import and direct query
Creating custom table in the Power BI
Merge Queries and Append Queries
Remove columns and split columns
Choosing required columns in the data
Working with different transformations
Applied steps in query editor
Working with hierarchies and measures
Working with Pivot & Unpivot columns
Creating index and custom columns
Group By functionality in Query Editor
Groups, Pivots, Filters
Creating and Working with groups
Managing the null values and errors
Managing data types in Query editor

Power BI Desktop
Connecting to Data sources
Working with different visualizations
Importing custom visualizations
Working with table and matrix visuals
Working with different level of filters
Data Visualizations with Power BI
Single row & multi row cards
Defining relationships between tables
Creating Dashboards
Configuring a Dashboard: Dashboard Tiles, Pinning Tiles

Introduction to Data Flow

  • Data Flow Overview
  • Data Sources
  • Data Destinations
  • Extract and Load example (db to ff)
  • Extract and Load example (ff to db)
  • Introduction to Data Flow Transformations

Data Sources

  • Excel Source
  • Flat File Source
  • OLE DB Source

Data Flow Destinations

  • Excel Destination
  • Flat File Destination
  • OLE DB Destination

Data Flow Transformations

  • Aggregate Transformation Example
  • Audit Transformation Example
  • Character Map Transformation Example
  • Derived Column Transformation Example
  • Data Conversion Transformation Example
  • Derived Column Transformation Example
  • Conditional Split Transformation Example
  • Merge Transformation
  • Union All Transformation
  • Merge Join Transformation
  • Multicast Transformation
  • Introduction to Variables
  • Row Count Transformation
  • OLE DB Command Transformation
  • Sort Transformation

Advanced Data Flow

  • Lookup Transformation
  • Cache Transformation

All Dataflow tasks overview with doubt clearance

Variables and Configurations

  • Variables Overview
  • Variable scope
  • SSIS system variables
  • Using variables in control flow
  • Using variables in data flow
  • Property expressions

Introduction to Control Flow

  • Control Flow Tasks
  • Sequence Container
  • The File System Task
  • The FTP Task
  • The Send Mail Task
  • Data Flow Task
  • Web Service Task
  • Script Task
  • Execute Package Task
  • Execute Process Task
  • The Execute SQL Task
  • Precedence Constraints

Advanced Control Flow

  • For Each Loop Container

Parameters (New feature)

  • Package level parameters
  • Project level parameters

Debugging, Error Handling and Logging

  • SSIS debugging overview
  • Breakpoints in SSIS
  • Control Flow: The On Error event handler
  • Data Flow: Error data flow

SSIS Package Deployment

  • This is will be delivered with new Features of SSIS i.e SSISDB

SSIS Package Management

  • Managing packages with SQL Server Management Studio
  • Scheduling packages with SQL Server Agent

SQL Server Analysis Services

DIMENSION
FACTS
SCHEMAS
Datawarehouse concepts
DATA SOURCE
DATA SOURCE VIEW
DIMENSIONS
CUBE
FACTS AND TYPE OF FACTS
DIMENSIONS AND TYPE OF DIMENSIONS
RELATIONSHIPS
Types of relationships
BUILD
DEPLOY
PROCESS
PARTITIONS
STORAGE MODES
MOLAP, ROLAP AND HOLAP
Partitions implementation
AGGREGATIONS
USAGE OF EXCEL TO READ CUBE DATA
MDX
SQL Server Reporting Services
BASICS REPORTS
TABLE
MATRIX
DRILLDOWN
PROPERTIES
PARAMETERS – IT ADDS INTERACTIVITY TO THE USER – V IMP
CASCADING REPORT
DRILLTHRU REPORT
EXPRESSIONS
FUNCTIONS
HEADER
FOOTER
CHART Reports
Chart Report AND PROPERTIES
GUAGE AND PROPERTIES
OTHER REPORTS
REPORTS BASED ON CUBE
DEPLOYMENTS
DATA VISUALIZATION USING POWER BI :