SQL Server Step by Step + SQL Interview Q&A Tutorial

11+ hours of Videos (Subscription Validity For 8 Months)

Author
Trainer: Shivprasad Koirala

What you'll learn :-

The SQL Server Step by Step will teach you the basic and advance fundamentals of the SQL server. Furthermore, this also includes SQL server interview questionsand answers, which will help to crack the interview with ease.

Course Content

  • 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 Snow flake 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?
  • Basics:- Query plan, Logical operators and Logical reads
  • Point 1:- Unique keys improve table scan performance.
  • Point 2:- Choose Table scan for small & Seek scan for large records
  • Point 3:- Use Covering index to reduce RID (Row Identifier) lookup
  • Point 4:- Keep index size as small as possible.
  • Point 5:- use numeric as compared to text data type.
  • Point 6:- use indexed view for aggregated SQL Queries
  • Point 7:- Database Engine tuning advisor is your best friend.
  • Part 1 - Profiler, Tuning advisor, Clustered/Non-clustered indexes,DBCC command & Reading SQL Plan, OLTP & OLAP.
  • Part 2 - Estimated vs Actual plan/rows, SQL Plan revised, Table/Index scan, Indexes seek, Physical/Logical opertn, Execution mode, columnstore, Rebinds, Rewinds, Nested loop, Hash/Merge join
  • Part 3 - Interleaved Execution, Fragmentation, Performance counters, Page splits and Unused Indexes
  • Part 4 - Extended events, RID lookup and Covering indexes
  • Part 5 - Indexed views, Page splits & unused indexes, Dead locks, Query store Parameter sniffing Statistics
  • Part 6 - Inline vs Stored procedure SQL, Cache miss, Cache hit, Parameter sniffing and Query store.
  • Part 7 - Partitioning, Wait stats, Resource Governor, Inmemory Tables
  • Question 1 :- Explain normalization ?
  • Question 2 :- How to implement normalization ?
  • Question 3 :- What is denormalization ?
  • Question 4 :- Explain OLTP vs OLAP ?
  • Question 5 :- Explain 1st,2nd and 3rd Normal form ?
  • Question 6 :- Primary Key vs Unique key ?
  • Question 7 :- Differentiate between Char vs Varchar ?
  • Question 8 :- Differentiate between Char vs NChar ?
  • Question 9 :- Whats the size of Char vs NChar ?
  • Question 10 :- What is the use of Index ?
  • Question 11 :- How does it make search faster?
  • Question 12 :- What are the two types of Indexes ?
  • Question 13 :- Clustered vs Non-Clustered index?
  • Question 14 :- Function vs Stored Procedures?
  • Question 15 :- What are triggers and why do you need it ?
  • Question 16 :- What are types of triggers ?
  • Question 17 :- Differentiate between After trigger vs Instead Of ?
  • Question 18 :- What is need of Identity ?
  • Question 19 :- Explain transactions and how to implement it ?
  • Question 20 :- What are inner joins ?
  • Question 21 :- Explain Left join ?
  • Question 22 :- Explain Right join ?
  • Question 23 :- Explain Full outer joins ?
  • Question 24 :- Explain Cross joins ?
  • Question 25 :- Why do we need UNION ?
  • Question 26 :- Differentiate between Union vs Union All ?
  • Question 27 :- Can we have unequal columns in Union?
  • Question 28 :- Can column have different data types in Union ?
  • Question 29 :- Which Aggregate function have you used ?
  • Question 30 :- When to use Group by ?
  • Question 31 :- Can we select column which is not part of group by ?
  • Question 32 :- What is having clause ?
  • Question 33 :- Having clause vs Where clause?
  • Question 34 :- How can we sort records ?
  • Question 35 :- Whats the default sort ?
  • Question 36 :- How can we remove duplicates ?
  • Question 37 :- Select the first top X records ?
  • Question 38 :- How to handle NULLS ?
  • Question 39 :- What is use of wild cards ?
  • Question 40 :- What is the use of Alias ?
  • Question 41 :- How to write a case statement ?
  • Question 42 :- What is self reference tables ?
  • Question 43 :- What is self join ?
  • Question 44 :- Explain the between clause ?
  • Question 45 :- Explain SubQuery ?
  • Question 46 :- Can inner Subquery return multiple results ?
  • Question 47 :- What is Co-related Query ?
  • Question 48 :- Differentiate between Joins and SubQuery ?
  • Question 49 :- Performance Joins vs SubQuery?
  • Question 50 :- Find NTH Highest Salary in SQL.
  • Question 51 :- Select the top nth highest salary using correlated Queries?
  • Question 52 :- Select top nth using using TSQL
  • Question 53 :- Performance comparison of all the methods.
  • Question 54 :- What is CTE ?
  • Question 55 :- Can we execute CTE multiple times ?
  • Question 56 :- What is use of CTE ?
  • Question 57 :- How to write a recursive CTE ?
  • Question 58 :- Can we see some real world examples of CTE ?
  • Question 59 :- Can we perform insert updates on CTE?
  • Question 60 :- Does is update the tables physically ?
  • Question 61 :- What are temporary tables?
  • Question 62 :- Temp tables vs CTE
  • Question 63 :- Performance CTE vs Temp
  • SQL Server interview questions with answers PDF.

10 Lessons

03 Hours

World-class training and development programs developed by top teachers

Whats Included

  • World-class training teacher
  • Bench has zero learning curve
  • We handle the rest.