Basic and Advance SQL

The main purpose of this Data Analysis Fundamentals Using SQL is to give students the ability to add analysis capabilities to Excel spreadsheets. This course is intended for anyone responsible for analyzing data with SQL.

Each student in our Live Online classes receives a comprehensive set of materials, including course notes and all the class examples.
  • Batch size up to 1 to 3.
  • Access to the trainer any time for any questions and follow-ups.
  • Online in-person training.
  • Every session is followed by practical assignments.
  • Numerous assignments carefully designed on every topic by our professional trainers.
  • Guidance from experienced and professional people in software industry.
  • Assignments evaluation, feedbacks and encouragements to develop in better way.
  • Focus on hands-on training by students during sessions.

Prerequisites


Basic computer skills.


Syllabus


Duration: Min 20 hours of classroom training.
Min 25 assignments.

Introduction to DBMS
  • What is Data and Database?
  • Why electronics Database?
  • Information about various Database engines in Market.
  • Features of DBMS
Introduction to MS SQL Server
  • Introduction to Microsoft SQL Server
  • Components of SQL Server
  • Database Engine and Service
  • Management Studio
RDBMS Concepts
  • Overview of Relational Database Management System (RDBMS)
  • Basic database structures Database, Tables, Columns, and Rows
  • RDBMS Concepts- Entities, Attributes
  • Basic Data Types
Introduction to SQL
  • Introduction to SQL
  • Why SQL?
  • Characteristics of SQL
  • SQL concepts- DDL, DML and DCL
View data
  • Querying data using SELECT statement
  • Renaming Column and table - Alias
  • Sorting the Data - Order by
  • TOP Clause
Perform calculation
  • Arithmetic operations on data - Concate
  • Data manipulations using built in functions
  • Date Functions
Searching for information
  • Filtering the data - WHERE Clause
  • Logical operators
  • Logical expressions
Data Conversion
  • Data cleanup by handling NULLs
  • Data lookup using CASE
  • Data transformation using CAST and CONVERT
Analyzing bulk data
  • Aggregate functions
  • Elimination of duplicate records- DISTINCT
  • Groups records into summary rows - Group By
  • Filter data after aggregates – HAVING
Merge Data Sets
  • UNION
  • UNION ALL
  • INTERSECT
  • EXCEPT
Joining data together
  • Entity Relationships, Cardinalities
  • Inner Join
  • Outer joins - Left, Right, Full
  • Cross Join
Data Modeling
  • Normalized data model
  • De-normalized data model
  • Database constraints
Data Analysis - Comparative study
  • Subquery
  • Nested Subquery
  • Subquery in Case Statement
  • Correlated Subquery
Data Views
  • CTE
  • Purpose of View
  • Creating and using Database Views
  • Limitation and best practices about views
Advanced Data analysis
  • RANK
  • DENSE_RANK
  • NTILE
  • ROW_NUMBER
  • Using PIVOT to simplify a cross tab style
Data Processing
  • INSERT
  • UPDATE
  • DELETE
Data definitions
  • CREATE
  • ALTER
  • DROP
  • TRUNCATE
Performance
  • Identifying Query performance issues
  • Query performance tuning
  • Creating and managing indexes
This SQL training is designed for students new to writing SQL queries. You will gain a deeper knowledge and understanding of the SQL and how to write it.