MS SQL Server queries asking in interviews

 Here's a comprehensive list of SQL Server queries that are often asked in interviews. These queries cover a broad spectrum of SQL functionalities and scenarios:

Basic Queries

1. Select All Columns from a Table

   SELECT * FROM Employees;

2. Select Specific Columns from a Table

   SELECT FirstName, LastName FROM Employees;

3. Select with a WHERE Clause

   SELECT * FROM Employees WHERE Department = 'Sales';

4. Select with a LIKE Operator

   SELECT * FROM Employees WHERE LastName LIKE 'S%';

5. Select with IN Operator

   SELECT * FROM Employees WHERE Department IN ('Sales', 'HR');

6. Select with BETWEEN Operator

   SELECT * FROM Employees WHERE Salary BETWEEN 50000 AND 70000;

7. Select with IS NULL

   SELECT * FROM Employees WHERE ManagerID IS NULL;

8. Order by a Single Column

   SELECT * FROM Employees ORDER BY LastName ASC;

9. Order by Multiple Columns

   SELECT * FROM Employees ORDER BY Department ASC, LastName DESC;

10. Select Distinct Values

    SELECT DISTINCT Department FROM Employees;

11. Count Rows in a Table

    SELECT COUNT(*) FROM Employees;

12. Count Rows with a Condition

    SELECT COUNT(*) FROM Employees WHERE Department = 'Sales';

13. Sum Values in a Column

    SELECT SUM(Salary) FROM Employees;

14. Average Values in a Column

    SELECT AVG(Salary) FROM Employees;

15. Find the Minimum Value in a Column

    SELECT MIN(Salary) FROM Employees;

16. Find the Maximum Value in a Column

    SELECT MAX(Salary) FROM Employees;

17. Group By a Single Column

    SELECT Department, COUNT(*) FROM Employees GROUP BY Department;

18. Group By with Aggregates

    SELECT Department, AVG(Salary) FROM Employees GROUP BY Department;

19. Filter Groups with HAVING

    SELECT Department, COUNT(*) FROM Employees GROUP BY Department HAVING COUNT(*) > 5;

20. Select Top N Rows

    SELECT TOP 5 * FROM Employees ORDER BY Salary DESC;

Intermediate Queries

21. Inner Join Two Tables

    SELECT e.FirstName, d.DepartmentName
    FROM Employees e
    INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;
    

22. Left Join Two Tables

    SELECT e.FirstName, d.DepartmentName
    FROM Employees e
    LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID;
    

23. Right Join Two Tables

    SELECT e.FirstName, d.DepartmentName
    FROM Employees e
    RIGHT JOIN Departments d ON e.DepartmentID = d.DepartmentID;
    

24. Full Outer Join Two Tables

    SELECT e.FirstName, d.DepartmentName
    FROM Employees e
    FULL OUTER JOIN Departments d ON e.DepartmentID = d.DepartmentID;
    

25. Self Join

    SELECT e1.FirstName AS Employee, e2.FirstName AS Manager
    FROM Employees e1
    INNER JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;
    

26. Union of Two Queries

    SELECT FirstName FROM Employees
    UNION
    SELECT ManagerName FROM Managers;
    

27. Union All of Two Queries
    

    SELECT FirstName FROM Employees
    UNION ALL
    SELECT ManagerName FROM Managers;
    

28. Subquery in SELECT Clause

    SELECT FirstName, (SELECT AVG(Salary) FROM Employees) AS AvgSalary
    FROM Employees;
    

29. Subquery in WHERE Clause

    SELECT * FROM Employees
    WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'Sales');
    

30. Update Data in a Table

    UPDATE Employees
    SET Salary = Salary * 1.1
    WHERE Department = 'Sales';
    

31. Delete Data from a Table

    DELETE FROM Employees
    WHERE Department = 'Outsourcing';
    

32. Insert Data into a Table

    INSERT INTO Employees (FirstName, LastName, Department, Salary)
    VALUES ('John', 'Doe', 'Engineering', 60000);
    

33. Create a Table

    CREATE TABLE Employees (
        EmployeeID INT PRIMARY KEY,
        FirstName NVARCHAR(50),
        LastName NVARCHAR(50),
        Department NVARCHAR(50),
        Salary DECIMAL(18, 2)
    );
    

34. Drop a Table

    DROP TABLE Employees;

35. Alter a Table to Add a Column

    ALTER TABLE Employees
    ADD DateOfBirth DATE;
    

36. Alter a Table to Drop a Column

    ALTER TABLE Employees
    DROP COLUMN DateOfBirth;
    

37. Create an Index

    CREATE INDEX IX_Employees_Department
    ON Employees (Department);
    

38. Drop an Index

    DROP INDEX IX_Employees_Department ON Employees;

39. Create a View

    CREATE VIEW EmployeeView AS
    SELECT FirstName, LastName, Department
    FROM Employees
    WHERE Salary > 50000;
    

40. Drop a View

    DROP VIEW EmployeeView;

Advanced Queries

41. Common Table Expression (CTE)

    WITH DepartmentCTE AS (
        SELECT DepartmentID, DepartmentName
        FROM Departments
    )
    SELECT * FROM DepartmentCTE;
    

42. Recursive CTE

    WITH RecursiveCTE AS (
        SELECT EmployeeID, ManagerID, FirstName
        FROM Employees
        WHERE ManagerID IS NULL
        UNION ALL
        SELECT e.EmployeeID, e.ManagerID, e.FirstName
        FROM Employees e
        INNER JOIN RecursiveCTE r ON e.ManagerID = r.EmployeeID
    )
    SELECT * FROM RecursiveCTE;
    

43. Row Number Window Function

    SELECT FirstName, LastName,
           ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
    FROM Employees;
    

44. Rank Window Function

    SELECT FirstName, LastName,
           RANK() OVER (ORDER BY Salary DESC) AS Rank
    FROM Employees;
    

45. Dense Rank Window Function

    SELECT FirstName, LastName,
           DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
    FROM Employees;
    

46. NTILE Window Function

    SELECT FirstName, LastName,
           NTILE(4) OVER (ORDER BY Salary DESC) AS Quartile
    FROM Employees;
    

47. Lead and Lag Window Functions

    SELECT FirstName, LastName, Salary,
           LEAD(Salary, 1) OVER (ORDER BY Salary) AS NextSalary,
           LAG(Salary, 1) OVER (ORDER BY Salary) AS PreviousSalary
    FROM Employees;
    

48. Common Table Expression with Aggregates

    WITH DepartmentSalary AS (
        SELECT Department, SUM(Salary) AS TotalSalary
        FROM Employees
        GROUP BY Department
    )
    SELECT * FROM DepartmentSalary;
    

49. Pivot Data

    SELECT *
    FROM (SELECT Department, Salary
          FROM Employees) AS SourceTable
    PIVOT (SUM(Salary) FOR Department IN ([Sales], [HR], [Engineering])) AS PivotTable;
    

50. Unpivot Data

    SELECT Department, Salary
    FROM (SELECT EmployeeID, Sales, HR, Engineering
          FROM EmployeeSalaries) AS PivotTable
    UNPIVOT (Salary FOR Department IN (Sales, HR, Engineering)) AS UnpivotedTable;
    

51. Transaction Management

    BEGIN TRANSACTION;
    UPDATE Employees
    SET Salary = Salary * 1.1
    WHERE Department = 'Sales';
    
    IF @@ERROR <> 0
    BEGIN
        ROLLBACK TRANSACTION;
    END
    ELSE
    BEGIN
        COMMIT TRANSACTION;
    END
    

52. Using TRY...CATCH for Error Handling

    BEGIN TRY
        BEGIN TRANSACTION;
        UPDATE Employees
        SET Salary = Salary * 1.1
        WHERE Department = 'Sales';
    
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        -- Error handling code here
    END CATCH;
    

53. Create a Stored Procedure

    CREATE PROCEDURE GetEmployeeByDepartment
        @Department NVARCHAR(50)
    AS
    BEGIN
        SELECT * FROM Employees WHERE Department = @Department;
    END;
    

54. Execute a Stored Procedure

    EXEC GetEmployeeByDepartment @Department = 'Sales';

55. Create a Function

    CREATE FUNCTION CalculateBonus (@Salary DECIMAL(18, 2))
    RETURNS DECIMAL(18, 2)
    AS
    BEGIN
        RETURN @Salary * 0.1;
    END;
    

56. Use a Function

    SELECT FirstName, Salary, dbo.CalculateBonus(Salary) AS Bonus
    FROM Employees;
    

57. Create a Trigger

    CREATE TRIGGER trgAfterInsert
    ON Employees
    AFTER INSERT
    AS
    BEGIN
        PRINT 'New employee added';
    END;
    

58. Create an Index with Included Columns

    CREATE INDEX IX_Employees_Department
    ON Employees (Department)
    INCLUDE (FirstName, LastName);
    

59. Show Execution Plan

    SET SHOWPLAN_ALL ON;
    SELECT * FROM Employees;
    SET SHOWPLAN_ALL OFF;
    

60. Optimize a Query Using Hints

    SELECT * FROM Employees WITH (NOLOCK);

61. Check Table Schema

    EXEC sp_help 'Employees';

62. Find the Last Modified Date of a Table

    SELECT name AS TableName, modify_date
    FROM sys.tables
    WHERE name = 'Employees';
    

63. List All Tables in a Database

    SELECT * FROM INFORMATION_SCHEMA.TABLES;

64. List All Columns in a Table

    SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'Employees';
    

65. List All Indexes on a Table

    EXEC sp_helpindex 'Employees';

66. Check for Missing Indexes

    SELECT * 
    FROM sys.dm_db_missing_index_details;
    

67. Find Long-Running Queries

    SELECT * 
    FROM sys.dm_exec_requests
    WHERE status = 'running';
    

68. Get SQL Server Version

    SELECT @@VERSION;

69. Generate Database Schema Script

    EXEC sp_helpdb 'YourDatabaseName';

70. Backup a Database

    BACKUP DATABASE YourDatabaseName
    TO DISK = 'C:\Backup\YourDatabaseName.bak';
    

71. Restore a Database

    RESTORE DATABASE YourDatabaseName
    FROM DISK = 'C:\Backup\YourDatabaseName.bak';
    

72. Check Database Size

    EXEC sp_spaceused;

73. Find Active Sessions

    SELECT * FROM sys.dm_exec_sessions;

74. Find Active Connections

    SELECT * FROM sys.dm_exec_connections;

75. Check SQL Server Configuration

    EXEC sp_configure;

76. Monitor SQL Server Performance

    EXEC sp_monitor;

77. List All Views in a Database

    SELECT * FROM INFORMATION_SCHEMA.VIEWS;

78. List All Stored Procedures in a Database

    SELECT * FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE = 'PROCEDURE';
    

79. Find Orphaned Users

    EXEC sp_change_users_login 'Report';

80. Synchronize Orphaned Users

    EXEC sp_change_users_login 'Auto_Fix', 'username';

81. Get Database File Locations

    SELECT name, physical_name
    FROM sys.master_files
    WHERE database_id = DB_ID('YourDatabaseName');
    

82. List All Triggers in a Database

    SELECT * FROM sys.triggers;

83. Find Queries with High CPU Usage

    SELECT * 
    FROM sys.dm_exec_query_stats
    ORDER BY total_worker_time DESC;
    

84. Find Queries with High Disk I/O

    SELECT * 
    FROM sys.dm_exec_query_stats
    ORDER BY total_logical_reads DESC;
    

85. Find Slow-Running Queries

    SELECT * 
    FROM sys.dm_exec_query_stats
    WHERE total_elapsed_time > 1000;
    

86. Check for Blocking Sessions

    SELECT * 
    FROM sys.dm_exec_requests
    WHERE blocking_session_id <> 0;
    

87. Find Deadlocks

    SELECT * 
    FROM sys.dm_tran_locks
    WHERE request_status = 'WAIT';
    

88. List All SQL Server Agents Jobs

    EXEC sp_help_job;

89. Check SQL Server Agent Job Status

    EXEC msdb.dbo.sp_help_jobhistory @job_name = 'YourJobName';

90. Create a Full-Text Index

    CREATE FULLTEXT INDEX ON Employees (FirstName, LastName)
    KEY INDEX PK_Employees;
    

91. Perform a Full-Text Search

    SELECT * FROM Employees
    WHERE CONTAINS(FirstName, 'John');
    

92. Find Expired Indexes

    SELECT * 
    FROM sys.dm_db_index_usage_stats
    WHERE database_id = DB_ID('YourDatabaseName');
    

93. Monitor SQL Server Resource Usage

    SELECT * 
    FROM sys.dm_os_performance_counters;
    

94. Get Execution Plan for a Query

    SET SHOWPLAN_XML ON;
    SELECT * FROM Employees;
    SET SHOWPLAN_XML OFF;
    

95. Rebuild an Index

    ALTER INDEX IX_Employees_Department ON Employees REBUILD;

96. Reorganize an Index

    ALTER INDEX IX_Employees_Department ON Employees REORGANIZE;

97. Check Index Fragmentation

    SELECT * 
    FROM sys.dm_db_index_physical_stats(DB_ID('YourDatabaseName'), OBJECT_ID('Employees'), NULL, NULL, 'DETAILED');
    

98. Check for Long Transactions

    SELECT * 
    FROM sys.dm_tran_active_transactions;
    

99. Find Database Growth

    SELECT * 
    FROM sys.dm_db_log_space_usage;
    

100. Query Plan Cache Information
     
SELECT * FROM sys.dm_exec_query_plan_stats;

101. Query for SQL Server Wait Stats
     
SELECT * FROM sys.dm_os_wait_stats;

102. Query for SQL Server Locks
     
SELECT * FROM sys.dm_tran_locks;

103. Check SQL Server Service Accounts
     
EXEC xp_servicecontrol 'QUERYSTATE', 'MSSQLSERVER';

104. Get Current User in SQL Server
     
SELECT SYSTEM_USER;

105. List All Databases
     
SELECT name FROM sys.databases;

These queries cover a wide range of SQL Server functionalities, from basic data manipulation to advanced performance tuning and database management. Practicing these can help prepare for a variety of SQL Server interview questions.