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

11+ hours of Videos

Author
Trainer: Shivprasad Koirala

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.
  • 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.