SQL with Manoj

Basic Level

1. Introduction to SQL

2. History of SQL and Database tools

3. Download and Install SQL Server, download [video] & install [video]

4. Logical Query Processing with the SELECT statement

5. What are DDL, DML and DCL statements, with differences and examples? [video]
 

6. Working with SQL Server Databases:

    – Create a new Database [video]

    – Backup and Restore a Database [video]
 

7. SQL Server Data Types:

    – Basics [Video]

    – Decimal & Numeric

    – SQL_VARIANT data Type

    – XML Data Type
 

8. Working with Tables:

    – Create a new Table [video]

    – Truncate, Delete and Drop tables [video]

    – Working with Temporary Tables (#, ## tables) [video]

    – Working with Table variables (@ tables) [video]

    – Difference b/w Temporary Tables and Table Variables [video]
 

9. Working with Columns:

    – Add or Remove columns in Tables [video]

    – Rename or Change Data Type of a Column [video]

    – Computed Columns (Persisted and Non-Persisted) [video]
 

10. Using IDENTITY property with Columns in a Table:

    – IDENTITY property of a Column [video]

    – Check and Reseed IDENTITY value [video]

    – More about IDENTITY property [video]
 

11. Constraints in SQL Server:

    – Integrity Constraints in a SQL Database [video]

    – Primary Key (PK) Constraint [video]

    – Unique Key (UK) Constraint [video]

    – Foreign Key (FK) Constraint [video]

    – NOT NULL, CHECK and DEFAULT Constraints [video]
 

12. Querying a Table:

    – The SELECT statement [video]

    – The WHERE clause [video]

    – GROUP BY with Aggregate functions [video]

    – Having clause and difference with GROUP BY & WHERE clause [video]

    – UNION vs UNION ALL

    – EXCEPT, INTERSECT

    – INSERT, UPDATE, DELETE statements

    – MERGE statement
 

13. SQL Server JOINs and types

    – CROSS JOIN, Cartesian Product

    – INNER JOIN, Cartesian Product and Filter

    – OUTER JOIN, Cartesian Product, Filter, and Add Outer Rows (LEFT, RIGHT, FULL OUTER)

    – SELF JOIN
 

14. Working with Views

    – What are Views, vs Tables and their use

    – Indexed Views
 

15. Working with Stored Procedures

    – CREATE OR ALTER Stored Procedure

    – Stored Procedures Best Practices

    – Creating Stored Procedure with Dynamic Search, Paging and Sorting

    – Pass multiple values with a single Parameter in a Stored Procedure

    – Using OUTPUT Parameter

    – Native Compiled SPs (SQL Server 2014+)
 

16. Working with User Defined Functions

    – Types of UDFs (Scalar, Table valued, Multi Statement)

    – UDF vs SPs
 

17. Working with Triggers
 

Advance Level

18. Indexes in SQL Server

    – Types of Indexes

    – Clustered vs Non Clustered Indexes

    – Creating a Clustered or Non Clustered Index

    – Clustered Index and Row Storage, [Row Sorting]

    – Index Usage Stats

    – Reorganize Index vs Rebuild Index

    – ColumnStore Indexes Intro, [in SQL 2012/2014/2016], [in SQL 2017]

    – Hash and Range Indexes
 

19. Query Performance and Optimization

    – Using Execution Plans

    – Checking a bad preforming query Execution Plan

    – SET STATISTICS IO: Scan Count

    – SET STATISTICS IO/Time: Logical, Physical and Read-Ahead Reads?

    – Live Query Statistics
 

20.
 

YouTube “SQL Basics” series:

 

---