SQL for Business Analytics

·      Entity-Relationship Model            

·      Entity and Entity Set                    

·      Attributes and types of Attributes            

·      Entity Sets             

·      Relationship Sets               

·      Degree of Relationship                 

·      Mapping Cardinalities, One-to-One, One-to-Many, Many-to-one, Many-to-many

·      Symbols used in E-R Notation

·      Practical Exercise

·      Introduction to relational databases                      

·      Fundamental concepts of relational rows, tables, and columns                

·      Several operators (such as logical and relational), constraints, domains, indexes, stored procedures, primary and foreign keys                       

·      Understanding group functions                

·      The unique key

·      Practical Exercise

·      Join                        

·      Tables                    

·      Variables

·      Practical Exercise

·      SQL functions                   

·      Operators & queries                      

·      Table creation                    

·      Data retrieval from tables              

·      Combining rows from tables using inner, outer, cross, and self joins                   

·      Deploying operators such as ‘intersect,’ ‘except,’ ‘union,’           

·      Temporary table creation               

·      Set operator rules              

·      Table variables       

·      Practical Exercise

·      Understanding SQL functions                  

·      Scalar functions                 

·      Aggregate functions                      

·      Functions that can be used on different datasets, such as numbers, characters, strings, and dates

·      Inline SQL functions                    

·      General functions              

·      Duplicate functions

·      Practical Exercise

·      Understanding SQL subqueries, their rules          

·      Statements and operators with which subqueries can be used                 

·      Using the set clause to modify subqueries

·      Understanding different types of subqueries, such as where, select, insert, update, delete

·      Methods to create and view subqueries

·      Practical Exercise

·      Learning SQL views                     

·      Methods of creating, using, altering, renaming, dropping, and modifying views            

·      Understanding stored procedures and their key benefits 

·      Working with stored procedures               

·      Studying user-defined functions              

·      Error handling

·      Practical Exercise

·      User-defined functions                 

·      Types of UDFs, such as scalar                  

·      Inline table value               

·      Multi-statement table                    

·      Stored procedures and when to deploy them       

·      What is rank function?                  

·      Triggers, and when to execute triggers?   

·      Practical Exercise

·      SQL Server Management Studio              

·      Using pivot in MS Excel and MS SQL Server     

·      Differentiating between Char, Varchar, and NVarchar    

·      XL path, indexes and their creation                      

·      Records grouping, advantages, searching, sorting, modifying data                     

·      Clustered indexes creation            

·      Use of indexes to cover queries                

·      Common table expressions            

·      Index guidelines

·      Practical Exercise

·      Creating Transact-SQL queries                 

·      Querying multiple tables using joins                     

·      Implementing functions and aggregating data     

·      Modifying data                 

·      Determining the results of DDL statements on supplied tables and data            

·      Constructing DML statements using the output statement

·      Practical Exercise

·      Querying data using subqueries and APPLY       

·      Querying data using table expressions                  

·      Grouping and pivoting data using queries

·      Querying temporal data and non-relational data

·      Constructing recursive table expressions to meet business requirements              

·      Using windowing functions to group                   

·      Rank the results of a query           

·      Practical Exercise

·      Creating database programmability objects by using T-SQL       

·      Implementing error handling and transactions

·      Implementing transaction control in conjunction with error handling      

·      Implementing data types and NULL

·      Practical Exercise

·      Designing and implementing relational database schema 

·      Designing and implementing indexes                   

·      Learning to compare between indexed and included columns                 

·      Implementing clustered index                   

·      Designing and deploying views                

·      Column store views           

·      Practical Exercise

·      Explaining foreign key constraints            

·      Using T-SQL statements               

·      Usage of Data Manipulation Language (DML)

·      Designing the components of stored procedures

·      Implementing input and output parameters          

·      Applying error handling                

·      Executing control logic in stored procedures        

·      Designing trigger logic, DDL triggers

·      Practical Exercise

·      Applying transactions                   

·      Using the transaction behavior to identify DML statements        

·      Learning about implicit and explicit transactions

·      Isolation levels management                     

·      Understanding concurrency and locking behavior

·      Using memory-optimized tables   

·      Practical Exercise

·      Accuracy of statistics                    

·      Formulating statistics maintenance tasks  

·      Dynamic management objects management         

·      Identifying missing indexes                      

·      Examining and troubleshooting query plans         

·      Consolidating the overlapping indexes                 

·      The performance management of database instances

·      SQL server performance monitoring

·      Practical Exercise

·      Correlated Subquery, Grouping Sets, Rollup, Cube

·      Implementing Correlated Subqueries                    

·      Using EXISTS with a Correlated subquery          

·      Using Union Query                       

·      Using Grouping Set Query                       

·      Using Rollup                     

·      Using CUBE to generate four grouping sets        

·      Perform a partial CUBE

·      Practical Exercise