25 May 2024

#SQL_Server

#SQL_Server

Key Concepts


S.No Topic Sub-Topics
1 Introduction to Databases What is a database?, DBMS vs RDBMS, Types of databases, Use cases, Advantages
2 Database Architecture Three-tier architecture, Client-server model, Relational model, NoSQL model, Distributed databases
3 SQL Basics Data types, SELECT queries, WHERE clause, ORDER BY, GROUP BY
4 Data Definition Language (DDL) CREATE, ALTER, DROP, TRUNCATE, Constraints
5 Data Manipulation Language (DML) INSERT, UPDATE, DELETE, MERGE, Transactions
6 Data Control Language (DCL) GRANT, REVOKE, Privileges, Roles, Security
7 Transaction Management ACID properties, Commit, Rollback, Savepoints, Isolation levels
8 Indexes Primary index, Unique index, Composite index, Full-text index, Indexing strategies
9 Joins INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, Self join
10 Subqueries Single-row subqueries, Multi-row subqueries, Correlated subqueries, EXISTS, IN
11 Views Creating views, Updating views, Materialized views, Security, Use cases
12 Stored Procedures Creating procedures, Parameters, Control statements, Error handling, Best practices
13 Functions Scalar functions, Aggregate functions, User-defined functions, System functions, Usage examples
14 Triggers BEFORE triggers, AFTER triggers, INSTEAD OF triggers, Event types, Use cases
15 Normalization 1NF, 2NF, 3NF, BCNF, Denormalization
16 Database Design ER diagrams, Tables and relationships, Keys, Constraints, Schema design best practices
17 Backup & Recovery Full backup, Incremental backup, Point-in-time recovery, Recovery strategies, Tools
18 Performance Tuning Query optimization, Index tuning, Execution plans, Caching, Monitoring tools
19 Replication Master-slave replication, Multi-master replication, Synchronous vs asynchronous, Failover, Use cases
20 Sharding & Partitioning Horizontal partitioning, Vertical partitioning, Range-based sharding, Hash-based sharding, Load balancing
21 NoSQL Databases Document store, Key-value store, Column-family store, Graph database, Use cases
22 MongoDB Basics Documents, Collections, CRUD operations, Indexing, Aggregation framework
23 Redis Basics Key-value data, Data types, Persistence, Pub/Sub, Use cases
24 Database Security Authentication, Authorization, Roles, Encryption, Auditing
25 Concurrency Control Locks, Deadlocks, Optimistic concurrency, Pessimistic concurrency, Isolation levels
26 Data Warehousing OLAP vs OLTP, Star schema, Snowflake schema, ETL process, Tools
27 Big Data Integration Hadoop, Spark, Hive, Data lakes, ETL pipelines
28 Database Testing Unit testing, Integration testing, Performance testing, Security testing, Automation tools
29 Cloud Databases AWS RDS, Azure SQL Database, Google Cloud SQL, MongoDB Atlas, Benefits
30 Database Projects & Labs ER modeling project, CRUD project, Aggregation project, Replication/sharding lab, Cloud deployment lab

Interview question

Basic Level

  1. What is SQL Server and how does it differ from other RDBMS?
  2. What are the main editions of SQL Server?
  3. How do you install SQL Server?
  4. What is SQL Server Management Studio (SSMS)?
  5. What are databases, tables, and schemas in SQL Server?
  6. How do you create a database in SQL Server?
  7. How do you create a table in SQL Server?
  8. What are the different data types in SQL Server?
  9. What is a primary key? How do you define it in SQL Server?
  10. What is a foreign key? How do you define it in SQL Server?
  11. What is the difference between CHAR, VARCHAR, and NVARCHAR?
  12. How do you insert data into a table?
  13. How do you update and delete records in SQL Server?
  14. What is the difference between DELETE, TRUNCATE, and DROP?
  15. What are constraints in SQL Server? Name a few types.
  16. How do you create indexes in SQL Server?
  17. What is a clustered index? How does it differ from a non-clustered index?
  18. How do you check the structure of a table in SQL Server?
  19. How do you retrieve all databases on a SQL Server instance?
  20. What is the difference between UNION and UNION ALL?
  21. How do you filter data using the WHERE clause?
  22. Explain the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
  23. What is the purpose of the TOP keyword in SQL Server?
  24. What is IDENTITY in SQL Server? How is it used?
  25. How do you back up and restore a SQL Server database?

Intermediate Level

  1. What are SQL Server system databases?
  2. Explain the purpose of master, msdb, model, and tempdb.
  3. What is tempdb used for?
  4. Explain the difference between a view and a materialized view.
  5. What are stored procedures in SQL Server?
  6. How do you create and execute a stored procedure?
  7. What are triggers in SQL Server?
  8. Explain the difference between AFTER and INSTEAD OF triggers.
  9. What are functions in SQL Server? Differentiate between scalar and table-valued functions.
  10. What is a common table expression (CTE)?
  11. How do you implement recursive queries in SQL Server?
  12. What are transactions in SQL Server?
  13. Explain ACID properties in SQL Server.
  14. What is the difference between COMMIT and ROLLBACK?
  15. How does SQL Server handle concurrency?
  16. What are isolation levels in SQL Server? List them.
  17. What is the difference between optimistic and pessimistic concurrency control?
  18. How do you use the EXCEPT and INTERSECT operators?
  19. What are SQL Server Operators like LIKE, BETWEEN, IN, and EXISTS?
  20. What is the CASE statement in SQL Server?
  21. How do you handle NULL values in SQL Server?
  22. What is the difference between ISNULL() and COALESCE()?
  23. How do you grant and revoke permissions in SQL Server?
  24. What are SQL Server logins and users? How do they differ?
  25. What is the SQL Server Agent? What is it used for?

Advanced Level

  1. What is indexing strategy in SQL Server?
  2. What are included columns in a non-clustered index?
  3. What is a filtered index in SQL Server?
  4. What are indexed views in SQL Server?
  5. What are statistics in SQL Server? Why are they important?
  6. How does SQL Server query optimizer work?
  7. What is the execution plan in SQL Server? How do you read it?
  8. What is parameter sniffing in SQL Server?
  9. How do you avoid parameter sniffing issues?
  10. What is a deadlock in SQL Server? How do you resolve it?
  11. How do you detect blocking in SQL Server?
  12. Explain difference between NOLOCK and other table hints.
  13. What is sp_who2 used for?
  14. How do you find the longest-running queries in SQL Server?
  15. What are Dynamic Management Views (DMVs)?
  16. How do you monitor performance using DMVs?
  17. What is partitioning in SQL Server?
  18. What are partitioned tables and partitioned indexes?
  19. What is replication in SQL Server? Types?
  20. What is the difference between snapshot, transactional, and merge replication?
  21. What is log shipping in SQL Server?
  22. What is database mirroring in SQL Server?
  23. What is AlwaysOn Availability Groups in SQL Server?
  24. How do you configure failover clustering in SQL Server?
  25. What is database sharding? Is it supported in SQL Server?

Expert Level

  1. Explain SQL Server architecture (SQL OS, storage engine, relational engine).
  2. What is the difference between SQL Server on-premises and Azure SQL Database?
  3. What are extended events in SQL Server?
  4. What is SQL Profiler? Why is it deprecated?
  5. How do you capture deadlock graphs in SQL Server?
  6. Explain tempdb contention and how to resolve it.
  7. How do you tune queries in SQL Server?
  8. What is Query Store in SQL Server?
  9. How do you use Query Store for performance troubleshooting?
  10. What are columnstore indexes? When should you use them?
  11. What is in-memory OLTP in SQL Server?
  12. How does SQL Server handle encryption (TDE, Always Encrypted)?
  13. What is row-level security (RLS) in SQL Server?
  14. What are dynamic data masking techniques in SQL Server?
  15. Explain PolyBase in SQL Server.
  16. How do you connect SQL Server to external data sources (linked servers)?
  17. What is Service Broker in SQL Server?
  18. What is Change Data Capture (CDC)?
  19. What is Change Tracking in SQL Server?
  20. What is the difference between CDC and Change Tracking?
  21. How do you implement auditing in SQL Server?
  22. What are best practices for SQL Server index maintenance?
  23. How do you configure high availability and disaster recovery (HADR)?
  24. What is the difference between synchronous and asynchronous replication in SQL Server?
  25. Compare SQL Server with PostgreSQL and Oracle in terms of scalability, features, and use cases.

Related Topics


   Index   
   Views   
   Stored Procedures   
-
Labels: , ,