Library

Course: Learn SQL Server Step by Step

Learn SQL Server Step by Step

  • Life Time Access
  • Certificate on Completion
  • Access on Android and iOS App
About this Course

My name is Shivprasad Koirala and welcome to my Learn SQL Server step by step video series. If you have any technical issues you can send me message i am more than happy to reply to your queries. Happy learning.

Basic knowledge
  • No basic knowledge as such required, should have the zeal to learn :-)
What you will learn

These step by step video lessons will teach you SQL Server step by step from scratch.

SQL Server is a huge giant and how much ever we learn its not enough. As a part of this ongoing learning journey we have added course title "Learn SQL Server Step by Step" Tutorials which has currently 21 labs covering not all but the most required handfull skilled essentials to become SQL professional developer as required by today's software industry with the help of e below mentioned syllabus:

  • Lab 1:- Basic Fundamentals Database, Tables, rows, and columns.
  • Lab 2:- Primary key, foreign key, referential integrity, and constraints.
  • Lab 3:- Database Normalization (1st, 2nd, and 3rd normal forms).
  • Lab 4: - SQL basics(Select, Insert, Update and Delete)
  • Lab 5 :- DDL (Data definition language) Queries.
  • Lab 6: - Transactions, Locks and Isolation level in SQL Server.
  • Lab 7: - ISNULL and Coalesce functions.
  • Lab 8: - Row_Number, Partition, Rank, and DenseRank
  • Lab 9: - Triggers, inserted and deleted tables
  • Lab 10: - Instead of and after triggers.
  • Lab 11: - Denormalization, OLTP and OLAP.
  • Lab 12: - Understanding Star schema and Snowflake design.
  • Lab 13: - SQL Server 8 kb pages.
  • Lab 14:- Index and performances
  • Lab 15:- Page Split and indexes
  • Lab 16:- Clustered vs. non-clustered
  • Lab 17: - Stored procedures and their importance.
  • Lab 18: - Change Data Capture.
  • Lab 19: - Explain Columnstore Indexes?
  • Lab 20: - SQL Server agent
  • Lab 21: - How can we implement Pivot & Unpivot in SQL Server?
Curriculum
Number of Lectures: 21
Total Duration: 07:03:07
Lab 1:- Basic Fundamentals Database, Tables, rows and columns.
  • Lab 1:- Basic Fundamentals Database, Tables, rows and columns.  

    In this lab we see how to create databases , tables , rows , columns and also understand how to use SQL Server management studio.

Lab 2:- Primary key, foreign key, referential integrity and constraints.
  • Primary key, foreign key, referential integrity and constraints.  

    1:- NULLS

    2 :- Identity

    3 :- Unique key

    4 :- Candidates / Primary keys

    5 :- Refe Integrity and FK

    6 :- Database digrams  

Lab 3 :- Database Normalization (1st, 2nd and 3rd normal forms).
  • Database Normalization (1st, 2nd and 3rd normal forms).  

    1 :- First, second and third normal forms

    2 :- Database design problems

    3 :- Duplicate and Redundancy

    4 :- Atomic and repeating groups

    5 :- Design issues of partial dependency

    6 :- Transitional dependency

    7 :- Many to Many design 

Lab 4: - SQL basics(Select, Insert, Update and Delete)
  • Lab 4: - SQL basics(Select, Insert, Update and Delete)  

    1. SQL Practice 1:- Select all columns and all rows of the table

    2. SQL Practice 2:- Select only needed columns from table.

    3. SQL Practice 3:- Select using a numeric criterion.

    4. SQL Practice 4:- Select using a string criterion.

    5. SQL Practice 5:- Select using AND and OR.

    6. SQL Practice 6:- Sort data using ascending and descending.

    7. SQL Practice 7:- Provide user friendly ALIAS for column names.

    8. SQL Practice 8:- Display unique records from a table.

    9. SQL Practice 9:- Searching using pattern and wildcards.

    10. SQL Practice 10:- Create runtime calculated columns.

    11. SQL Practice 11:- CASE statements with SQL.

    12. SQL Practice 12:- Join data from two SELECTS using UNION and UNION ALL.

    13. SQL Practice 13:- Show matching data from two tables.( Inner Join)

    14. SQL Practice 14:- Show all records from one table and only matching record from other table. ( Left and Right)

    15. SQL Practice 15:- Show all records from matching or unmatching. (FULL outer join).

    16. SQL Practice 16:- Show cartersian of two tables( CROSS JOIN)

    17. SQL Practice 17:- Writing a complex SQL inner join statement.

    18. SQL Practice 18:- Display aggregate values from a table (GROUP BY)

    19. SQL Practice 19:- Filter on Aggregate values (HAVING CLAUSE)

    20. SQL Practice 20:- Self Join

    21. SQL Practice 21:- INSULL

    22. SQL Practice 22:- Sub Queries

    23. SQL Practice 23:- Co-related Queries

    24. SQL Practice 24:- Find Max, Min and Average.

    25. SQL Practice 25:- Find the between numeric values.

    26. SQL Practice 26:- Dump table data in to new table (SELECT INTO)

    27. SQL Practice 27:- Insert data in to table.

    28. SQL Practice 28:- Insert bulk data in existing table

    29. SQL Practice 29:- Update data in to table.

    30. SQL Practice 30:- Delete data from a table.

    31. SQL Practice 31:- The SQL designer window.

Lab 5 :- DDL (Data definition language) Queries.
  • Lab 5 :- DDL (Data definition language) Queries.  

    SQL Practice Lab 31 :- Create Database Query

    SQL Practice Lab 32 :- Create Database with FileName , Size , MaxSize and Growth.

    SQL Practice Lab 33 :- Backup Database.

    SQL Practice Lab 34 :- Restore Database.

    SQL Practice Lab 35 :- Create table

    SQL Practice Lab 37 :- Alter a table structure drop a existing column.

    SQL Practice Lab 38 :- Alter a table structure change a existing column.

    SQL Practice Lab 39 :- Adding validation constraints NOT NULL and UNIQUE

    SQL Practice Lab 40 :- Check Constraints for data value validation.

    SQL Practice Lab 41 :- Provide Default values for a table.

    SQL Practice Lab 42 :- Creating Primary and Foreign key constraints

    Designer VS SQL Scripts

Lab 6: - Transactions, Locks and Isolation level in SQL Server.
  • Lab 6: - Transactions, Locks and Isolation level in SQL Server.  

    Chapter 1 :- Defining transactions.

    Chapter 2 :- Basic syntax Begin , commit & rollback tran

    Chapter 3:- Nested transaction ,check points & trancount.

    Chapter 4:- Understanding concurrency and issues associated with it.

    Chapter 5:- Understanding and demonstrating Exclusive (X) locking.

    Chapter 6:- Understanding row level locking concept.

    Chapter 7:- Read committed ,Read Uncommitted mode and NOLOCK .

    Chapter 8:- sp_lock and sp_who

    Chapter 9:- Dead lock and demonstration of the same.

    Chapter 10:- How to stop dead lock ?

    Chapter 11:- Update locks and how is it different from shared lock.

    Chapter 12:- Lock heirarchy

    Chapter 13:- Intent lock

    Chapter 14 :- ISOLATION LEVEL

    Chapter 15:- Repeatable read , Dirty reads and Phantom rows

    Chapter 16:- Locking compatibility matrix

    Chapter 17:- Conversion locks SIX,SIU and UIX

    Chapter 18:- ACID properties

    Revision note

Lab 7: - ISNULL and Coalesce functions.
  • Lab 7: - ISNULL and Coalesce functions.  

    ISNULL and Coalesce functions.

Lab 8: - Row_Number, Partition, Rank and DenseRank
  • Lab 8: - Row_Number, Partition, Rank and DenseRank  

    Row_Number, Partition, Rank and DenseRank

Lab 9: - Triggers, inserted and deleted tables
  • Lab 9: - Triggers, inserted and deleted tables  

    Triggers, inserted and deleted tables

Lab 10: - Instead of and after triggers.
  • Lab 10: - Instead of and after triggers.  

    Instead of and after triggers.

Lab 11: - Denormalization, OLTP and OLAP.
  • Lab 11: - Denormalization, OLTP and OLAP.  

    Denormalization, OLTP and OLAP.

Lab 12: - Understanding Star schema and Snow flake design.
  • Lab 12: - Understanding Star schema and Snow flake design.  

    Understanding Star schema and Snow flake design.

Lab 13: - SQL Server 8 kb pages.
  • Lab 13: - SQL Server 8 kb pages.  

    SQL Server 8 kb pages.

Lab 14 :- Index and performances
  • Lab 14 :- Index and performances  

     Index and performances

Lab 15 :- Page Split and indexes
  • Lab 15 :- Page Split and indexes  

    Page Split and indexes

Lab 16 :- Clustered vs non-clustered
  • Lab 16 :- Clustered vs non-clustered  

    Clustered vs non-clustered

Lab 17: - Stored procedures and their importance.
  • Lab 17: - Stored procedures and their importance.  

    Stored procedures and their importance.

Lab 18: - Change Data Capture.
  • Lab 18: - Change Data Capture.  

    Change Data Capture.

Lab 19: - Explain Columnstore Indexes?
  • Lab 19: - Explain Columnstore Indexes?  

    Explain Columnstore Indexes?

Lab 20: - SQL Server agent
  • Lab 20: - SQL Server agent  

    SQL Server agent

Lab 21: - How can we implement Pivot & Unpivot in SQL Server?
  • Lab 21: - How can we implement Pivot & Unpivot in SQL Server?  

    How can we implement Pivot & Unpivot in SQL Server?

Reviews (0)