SQL Course

3.1 Introduction to relational databases
3.2 Fundamental concepts of relational rows, tables, and columns
3.3 Several operators (such as logical and relational), constraints, domains, indexes, stored procedures, primary and foreign keys
3.4 Understanding group functions
3.5 The unique key

4.1 Advanced concepts of SQL tables
4.2 SQL functions
4.3 Operators & queries
4.4 Table creation
4.5 Data retrieval from tables
4.6 Combining rows from tables using the inner, outer, cross, and self joins
4.7 Deploying operators such as ‘intersect,’ ‘except,’ ‘union,’
4.8 Temporary table creation
4.9 Set operator rules
4.10 Table variables

5.1 Understanding SQL functions – what do they do?
5.2 Scalar functions
5.3 Aggregate functions
5.4 Functions that can be used on different datasets, such as numbers, characters, strings, and dates
5.5 Inline SQL functions
5.6 General functions
5.7 Duplicate functions

6.1 User-defined functions
6.2 Types of UDFs, such as scalar
6.3 Inline table value
6.4 Multi-statement table
6.5 Stored procedures and when to deploy them
6.6 What is the rank function?

7.1 Learning SQL views
7.2 Methods of creating, using, altering, renaming, dropping, and modifying views
7.3 Understanding stored procedures and their key benefits
7.4 Working with stored procedures
7.5 Error handling

8.1 Understanding SQL Triggers
8.2 Types of Triggers
8.3 Trigger Events
8.4 Trigger Syntax
8.5 Working with Trigger Actions
8.6 Trigger Timing
8.7 Nested and Recursive Trigger
8.8 Managing Trigger Execution
8.9 Common Use Cases for Triggers
8.10 Best Practices for Triggers
8.11 Trigger Debugging and Troubleshooting

9.1 Understanding SQL subqueries, their rules
9.2 Statements and operators with which subqueries can be used
9.3 Using the set clause to modify subqueries
9.4 Understanding different types of subqueries, such as where, select, insert, update, delete, etc.
9.5 Methods to create and view subqueries

10.1 Records grouping, advantages, searching, sorting, modifying data
10.2 Clustered indexes creation
10.3 Use of indexes to cover queries
10.4 Common table expressions
10.5 Index guidelines

11.1 Correlated Subquery, Grouping Sets, Rollup, Cube, Rank, CTE, Indexes, and Triggers
Correlated Subquery, Rollup, Grouping Set and Cubes

Hands-on Exercise

1. Implementing Correlated Subqueries
2. Using EXISTS with a Correlated subquery
3. Using Union Query
4. Using Grouping Set Query
5. Using Rollup
6. Using CUBE to generate four grouping sets
7. Perform a partial CUBE

12.1 Applying transactions
12.2 Using the transaction behavior to identify DML statements
12.3 Learning about implicit and explicit transactions
12.4 Isolation levels management
12.5 Understanding concurrency and locking behavior
12.6 Using memory-optimized tables

13.1 Creating Transact-SQL queries
13.2 Querying multiple tables using joins
13.3 Implementing functions and aggregating data
13.4 Modifying data
13.5 Determining the results of DDL statements on supplied tables and data
13.6 Constructing DML statements using the output statement
13.7 Querying data using subqueries and APPLY
13.8 Querying data using table expressions
13.9 Grouping and pivoting data using queries
13.10 Querying temporal data and non-relational data
13.11 Constructing recursive table expressions to meet business requirements
13.12 Using windowing functions to group
13.13 Rank the results of a query
13.14 Creating database programmability objects by using T-SQL
13.15 Implementing error handling and transactions
13.16 Implementing transaction control in conjunction with error handling in stored procedures
13.17 Implementing data types and NULL
13.18 Designing and implementing relational database schema
13.19 Designing and implementing indexes
13.20 Learning to compare between indexed and included columns
13.21 Implementing clustered index
13.22 Designing and deploying views
13.23 Column store views

13.24 Explaining foreign key constraints
13.25 Using T-SQL statements
13.26 Usage of Data Manipulation Language (DML)
13.27 Designing the components of stored procedures
13.28 Implementing input and output parameters
13.29 Applying error handling
13.30 Executing control logic in stored procedures
13.31 Designing trigger logic, DDL triggers, etc.
13.32 Accuracy of statistics
13.33 Formulating statistics maintenance tasks
13.34 Dynamic management objects management
13.35 Identifying missing indexes
13.36 Examining and troubleshooting query plans
13.37 Consolidating the overlapping indexes
13.38 The performance management of database instances
13.39 SQL server performance monitoring

14.1 Performance Tuning and Optimizing SQL Databases
14.2 Querying Data with Transact-SQL