What does SQL entail?

SQL (Structured Query Language) serves as the standard language for accessing and manipulating databases. It enables users to execute queries, retrieve data, and manage records within a database, simplifying the process of interrogating databases. Why opt for SQL? SQL proficiency is highly advantageous for roles in data-centric careers and is particularly valuable for web developers and software engineers. Its widespread acceptance by international governmental and organizational bodies, along with its usage by tech giants like Microsoft, Dell, and Google, highlights its significance. Mastering SQL can significantly enhance one's career prospects across diverse industries, providing a valuable addition to one's technological skill set.

Course Objectives: Microsoft SQL Server, developed by Microsoft, serves as a relational database management system. This software product functions as a database server, primarily tasked with storing and fetching data as needed by various software applications. These applications can operate either on the same computer or on different computers connected via a network.
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 4: Keys
•Super Key.
• Candidate Key
• Primary Key
• Alternate key
• Composite/Compound Key
• Unique Key
• Foreign Key
Module 5: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 6: 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 7: 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 8: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 9: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 10: 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 11: SQL Alias & NULL Commands
• Giving alias after Derived table name
• Using AS
• Using =
• Without using AStables, multiple columns
• COALESCE ()
• ANSI NULLS
• ISNULL()
• Is null / Is not null
• NULL comparison
• NULL with NOT IN SubQuery
Module 12: SQL Operator
• Arithmetic Operators
• Logical Operators
• Set Operators
• Comparison Operators
• Special Operators
• Assignment Operators
• String Concat Operator
• LIKE
Module 13: 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 14: 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 15: 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 16: 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 17: 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 18: 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 18: 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 19: SQL Trigger
• Type of Trigger
• DML Trigger
• DDL Trigger
• Logon Trigger
• Create
• Update
• Delete
• Disable/Enable
• Drop
Module 20: SQL Function
• Type of Function
• Scalar Function
• Aggregate Function
• Table – Valued Function
• Create
• Update
• Delete
• Disable/Enable
• Drop
Module 21: 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 22: 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 23: SQL Temp
• What is
• Local & Global Temp Table
• Create
• Update
• Delete
• Drop
Module 24: 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 25: 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
Module 26: 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 :