Comprehensive SQL Server Interview Questions and Answers Guide

SQL Server Basic Selection and String Queries

  • Retrieve All Data: To get all employee details from the EmployeeDetail table:

    • MS SQL Server/Oracle/MySQL: SELECT * FROM EmployeeDetail
  • Column Selection: To retrieve only the FirstName column:

    • SELECT FirstName FROM EmployeeDetail
  • Case Specific Formatting:

    • Upper Case: SELECT UPPER(FirstName) AS [First Name] FROM EmployeeDetail
    • Lower Case: SELECT LOWER(FirstName) AS [First Name] FROM EmployeeDetail
  • String Concatenation (First and Last Name):

    • MS SQL Server: SELECT FirstName +' '+ LastName AS [Name] FROM EmployeeDetail
    • Oracle: SELECT FirstName ||' '|| LastName AS [Name] FROM EmployeeDetail
    • MySQL: SELECT CONCAT(FirstName ,' ', LastName) AS [Name] FROM EmployeeDetail
  • Filtering (WHERE Clause):

    • Specific Name: SELECT * FROM EmployeeDetail WHERE FirstName = 'Vikas'
    • Starts with 'a': SELECT * FROM EmployeeDetail WHERE FirstName like 'a%'
    • Contains 'k': SELECT * FROM EmployeeDetail WHERE FirstName like '%k%'
    • Ends with 'h': SELECT * FROM EmployeeDetail WHERE FirstName like '%h'
    • Starting character within a range (e.g., a to p): SELECT * FROM EmployeeDetail WHERE FirstName like '[a-p]%'
    • Starting character NOT within a range: SELECT * FROM [EmployeeDetail] WHERE FirstName like '[^a-p]%'
  • Wildcard Characters:

    • The underscore (_) represents exactly one single character.
    • Gender ending in 'le' and containing exactly 44 letters: SELECT * FROM [EmployeeDetail] WHERE Gender like '__le' (using two underscores).
    • First name starting with 'A' and containing exactly 55 letters: SELECT * FROM [EmployeeDetail] WHERE FirstName like 'A\_\_\_\_' (using four underscores).
    • Containing a literal percentage sign (%): SELECT * FROM [EmployeeDetail] WHERE FirstName like '%[%]%'
  • Unique Values and Aggregates:

    • Unique Departments: SELECT DISTINCT(Department) FROM [EmployeeDetail]
    • Highest Salary: SELECT MAX(Salary) FROM [EmployeeDetail]
    • Lowest Salary: SELECT MIN(Salary) FROM [EmployeeDetail]

SQL Server Date and Time Related Queries

  • Formatting Dates:

    • Format dd mmm yyyy (e.g., 1515 Feb 20132013): SELECT CONVERT(VARCHAR(20),JoiningDate,106) FROM [EmployeeDetail]
    • Format yyyy/mm/dd (e.g., 20132013/0202/1515): SELECT CONVERT(VARCHAR(20),JoiningDate,111) FROM [EmployeeDetail]
    • Extract Time part only: SELECT CONVERT(VARCHAR(20),JoiningDate,108) FROM [EmployeeDetail]
  • Extracting Date Parts:

    • Year: SELECT DATEPART(YEAR, JoiningDate) FROM [EmployeeDetail]
    • Month: SELECT DATEPART(MONTH,JoiningDate) FROM [EmployeeDetail]
  • System and UTC Dates:

    • Current System Date: SELECT GETDATE()
    • Current UTC Date: SELECT GETUTCDATE()
  • Calculating Differences:

    • Difference in months between current date and joining date: SELECT FirstName, GETDATE() [Current Date], JoiningDate, DATEDIFF(MM,JoiningDate,GETDATE()) AS [Total Months] FROM [EmployeeDetail]
    • Difference in days: SELECT FirstName, GETDATE() [Current Date], JoiningDate, DATEDIFF(DD,JoiningDate,GETDATE()) AS [Total Months] FROM [EmployeeDetail]
  • Filtering by Date:

    • Joining year 20132013: SELECT * FROM [EmployeeDetail] WHERE DATEPART(YYYY,JoiningDate) = '2013'
    • Joining month January (11): SELECT * FROM [EmployeeDetail] WHERE DATEPART(MM,JoiningDate) = '1'
    • Between specific dates: SELECT * FROM [EmployeeDetail] WHERE JoiningDate BETWEEN '2013-01-01' AND '2013-12-01'

Advanced Filtering, Salary, and Formatting

  • Row Selection:

    • Select Top 11 record: SELECT TOP 1 * FROM [EmployeeDetail]
    • Multiple specific names: SELECT * FROM [EmployeeDetail] WHERE FirstName IN('Vikas','Ashish','Nikhil')
    • Excluding specific names: SELECT * FROM [EmployeeDetail] WHERE FirstName NOT IN('Vikas','Ashish','Nikhil')
  • Trimming and Prefixing:

    • Remove right white spaces: SELECT RTRIM(FirstName) AS [FirstName] FROM [EmployeeDetail]
    • Remove left white spaces: SELECT LTRIM(FirstName) AS [FirstName] FROM [EmployeeDetail]
    • Prefixing: SELECT 'Hello ' + FirstName FROM [EmployeeDetail]
  • Conditional Logic (CASE Statement):

    • To display Gender as M/F: SELECT FirstName, CASE WHEN Gender = 'Male' THEN 'M' WHEN Gender = 'Female' THEN 'F' END AS [Gender] FROM [EmployeeDetail]
  • Salary Queries:

    • Salary > 600,000600,000: SELECT * FROM [EmployeeDetail] WHERE Salary > 600000
    • Salary < 700,000700,000: SELECT * FROM [EmployeeDetail] WHERE Salary < 700000
    • Salary between 500,000500,000 and 600,000600,000: SELECT * FROM [EmployeeDetail] WHERE Salary BETWEEN 500000 AND 600000
    • Second Highest Salary: SELECT TOP 1 Salary FROM (SELECT TOP 2 Salary FROM [EmployeeDetail] ORDER BY Salary DESC) T ORDER BY Salary ASC

Grouping and Aggregation (GROUP BY and HAVING)

  • Sum and Ordering:

    • Department-wise total salary: SELECT Department, SUM(Salary) AS [Total Salary] FROM [EmployeeDetail] GROUP BY Department
    • Sorted by total salary ascending: SELECT Department, SUM(Salary) AS [Total Salary] FROM [EmployeeDetail] GROUP BY Department ORDER BY SUM(Salary) ASC
    • Sorted by total salary descending: SELECT Department, SUM(Salary) AS [Total Salary] FROM [EmployeeDetail] GROUP BY Department ORDER BY SUM(Salary) DESC
  • Counts and Statistics:

    • Department count and total salary: SELECT Department, COUNT(*) AS [Dept Counts], SUM(Salary) AS [Total Salary] FROM [EmployeeDetail] GROUP BY Department
    • Average Salary: SELECT Department, AVG(Salary) AS [Average Salary] FROM [EmployeeDetail] GROUP BY Department ORDER BY AVG(Salary) ASC
    • Max/Min Salary: Use MAX(Salary) or MIN(Salary) with GROUP BY Department and ORDER BY aggregate result.
  • HAVING Clause: Used to filter grouped results.

    • Example for projects assigned to more than one employee: SELECT ProjectName, Count(*) [NoofEmp] FROM [ProjectDetail] GROUP BY ProjectName HAVING COUNT(*) > 1

SQL Joins and Complex Relationships

  • Inner Join: Returns records with matching values in both tables.

    • SELECT FirstName, ProjectName FROM [EmployeeDetail] A INNER JOIN [ProjectDetail] B ON A.EmployeeID = B.EmployeeDetailID ORDER BY FirstName
  • Left Outer Join: Returns all records from the left table and matched records from the right; unmatched right side returns NULL.

    • SELECT FirstName, ProjectName FROM [EmployeeDetail] A LEFT OUTER JOIN [ProjectDetail] B ON A.EmployeeID = B.EmployeeDetailID
    • Handling NULLs: SELECT FirstName, ISNULL(ProjectName, '-No Project Assigned') FROM [EmployeeDetail] A LEFT OUTER JOIN [ProjectDetail] B ON A.EmployeeID = B.EmployeeDetailID
  • Right Outer Join: Returns all records from the right table and matched records from the left.

    • SELECT FirstName, ProjectName FROM [EmployeeDetail] A RIGHT OUTER JOIN [ProjectDetail] B ON A.EmployeeID = B.EmployeeDetailID
  • Full Outer Join: Returns all records when there is a match in either left or right table.

    • SELECT FirstName, ProjectName FROM [EmployeeDetail] A FULL OUTER JOIN [ProjectDetail] B ON A.EmployeeID = B.EmployeeDetailID
  • Advanced Join Logic:

    • Employees without projects: SELECT FirstName FROM [EmployeeDetail] A LEFT OUTER JOIN [ProjectDetail] B ON A.EmployeeID = B.EmployeeDetailID WHERE ProjectName IS NULL
    • Projects without employees: SELECT ProjectName FROM [EmployeeDetail] A RIGHT OUTER JOIN [ProjectDetail] B ON A.EmployeeID = B.EmployeeDetailID WHERE FirstName IS NULL
    • Employees with more than one project: SELECT EmployeeID, FirstName, ProjectName FROM [EmployeeDetail] E INNER JOIN [ProjectDetail] P ON E.EmployeeID = P.EmployeeDetailID WHERE EmployeeID IN (SELECT EmployeeDetailID FROM [ProjectDetail] GROUP BY EmployeeDetailID HAVING COUNT(*) > 1)

Tricky SQL Query Sets and Behaviors

  • Identity and Syntax Quirks:

    • SELECT 15: Output is 1515.
    • SELECT $: Output is 0.000.00 (Money format).
    • SELECT COUNT(*) on an empty set result or constant: Can return 11.
    • SELECT 'VIKAS' + 1: Throws an error because string and integer cannot be implicitly added without conversion.
    • SELECT 'VIKAS' + '1': Output is VIKAS1.
    • SELECT NULL + 5: Output is NULL.
    • SELECT 1/0 or SELECT 0/0: Throws a divide-by-zero error.
    • SELECT 0/9: Output is 00.
    • SELECT 1 WHERE NULL = NULL: Returns 00 rows because NULL cannot be equated to NULL via =.
  • String Aggregation in a Single Cell (FOR XML PATH):

    • Concatenate all names with commas: SELECT STUFF((SELECT ', ' + E.FirstName FROM [EmployeeDetail] AS E FOR XML PATH('')), 1, 2, '') AS [All Emp Name]
  • Casing Logic:

    • Convert first letter to Upper and others to Lower: SELECT UPPER(SUBSTRING(FirstName,1,1)) + LOWER(SUBSTRING(FirstName,2,LEN(FirstName)-1)) AS [FirstName]

Data Definition Language (DDL) and Constraints

  • Table Creation with Identity and Primary Key: sql CREATE TABLE EmployeeDetail( [EmployeeID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY, [FirstName] NVARCHAR(50) NULL, [LastName] NVARCHAR(50) NULL, [Salary] DECIMAL(10, 2) NULL, [JoiningDate] DATETIME NULL, [Department] NVARCHAR(20) NULL, [Gender] VARCHAR(10) NULL ) &nbsp;&nbsp;&nbsp;&nbsp;

  • Altering Tables:

    • Add Primary Key: ALTER TABLE EmployeeDetail ADD PRIMARY KEY (P_EmployeeID)
    • Add Foreign Key: ALTER TABLE ProjectDetail ADD CONSTRAINT fk_EmployeeDetailID_Eid FOREIGN KEY(EmployeeDetailID) REFERENCES EmployeeDetail (EmployeeID)
  • Constraint Types:

    • NOT NULL: Every row must have a value in this field.
    • DEFAULT: Provides a default value if none is specified.
    • PRIMARY KEY: Ensures uniqueness and non-nullability (automatically creates a Clustered Index).
    • FOREIGN KEY: References a column (usually PK) in another table to enforce referential integrity.
    • UNIQUE: Ensures all values are different; allows exactly one NULL value.
    • CHECK: Ensures all values meet a specific requirement.

SQL Server Data Types

  • Integer Types:

    • bigint: −263-2^{63} to 263−12^{63}-1.
    • int: −2,147,483,648-2,147,483,648 to 2,147,483,6472,147,483,647.
    • smallint: −32,768-32,768 to 32,76732,767.
    • tinyint: 00 to 255255.
  • Character Types:

    • char: Fixed-length non-Unicode (8,0008,000 max).
    • varchar: Variable-length non-Unicode (8,0008,000 max, or max for 22 GB).
    • nchar: Fixed-length Unicode (4,0004,000 max).
    • nvarchar: Variable-length Unicode (4,0004,000 max).
  • Date/Time Types:

    • datetime: 1753−01−011753-01-01 to 9999−12−319999-12-31 (3.333.33 ms accuracy).
    • datetime2: More accurate (up to 100100 ns) and compact than datetime.
    • smalldatetime: 1900−01−011900-01-01 to 2079−06−062079-06-06 (11 min accuracy).
    • timestamp: Deprecated; replaced by rowversion. It is a database-wide unique number that updates when a row changes.
  • Other Types:

    • bit: 00 or 11.
    • decimal / numeric: Fixed precision/scale (e.g., decimal(9,2)).
    • uniqueidentifier: Globally Unique Identifier (GUID).

Indexes and Performance

  • Index definition: A database object created on one or more columns to help SQL Server retrieve data faster by forming a relevant data structure.

  • Types of Indexes:

    • Clustered Index: Sorts and stores the data rows in the table or view based on the key. There is only one per table because the data itself is physically stored in that order.
    • Non-Clustered Index: A separate structure from the data rows. Contains key values and row locators (pointing to the clustered index or heap). Allowed up to 249249 (SQL 20052005) or 999999 (SQL 2008+2008+) per table.
  • Fill Factor: A value (00-100100) that determines the percentage of space on each leaf-level page to be filled with data. Default is 100100 (or 00), meaning pages are filled completely. Lower values leave space for future expansion to prevent page splits.

Views

  • Definition: A virtual table defined by a stored SELECT statement. It does not store data physically unless it is a Clustered Indexed View.
  • Types: Indexed Views, Partitioned Views, System Views.
  • Column Limit: Max 1,0241,024 columns.
  • Encryption: Use WITH ENCRYPTION to hide the underlying code.
  • Schema Binding: Use WITH SCHEMABINDING to prevent changes to base tables that would break the view. Requires schemas for all objects and prohibits the use of SELECT *.

Stored Procedures (SPs)

  • Definition: A named group of SQL statements stored in the database.
  • Advantages: Reduces network traffic, improves security, improves performance via cached execution plans, encapsulates logic, prevents SQL injection.
  • Disadvantages: Specialized skills required for building/maintaining, lack of debuggers, hard to handle exceptions, tightly coupled to the database.
  • Recursion: SPs can call themselves up to 3232 levels deep.
  • Optimization Tips:
    • Include SET NOCOUNT ON.
    • Use schema names with object names.
    • Avoid the sp_ prefix.
    • Keep transactions as short as possible.
    • Use sp_executesql instead of EXECUTE for dynamic SQL.

Temporary Tables and Variables

  • Local Temp Table (#): Visible only to the current session; dropped when the session ends.
  • Global Temp Table (##): Visible to all connections; dropped when the creating session ends and no other sessions are referencing it.
  • Table Variables: Do not participate in transactions or locking, no statistics are maintained, and DML operations are not logged.
  • Storage: Created in the TEMPDB database.

SQL Server 2016 and 2017 New Features

  • SQL Server 2016:

    • Query Store: Captures query history and performance metrics.
    • Live Query Statistics (LQS): View active queries and associated stats during execution.
    • Always Encrypted: Protects data at rest and in motion with keys held by the application.
    • Row-Level Security (RLS): Restricts access to rows based on user identity.
    • JSON Support: Native support (lighter and faster than XML).
    • Temporal Tables: Built-in support for historical data tracking at any point in time.
    • PolyBase: Query relational and non-relational data together.
  • SQL Server 2017:

    • Adaptive Query Processing: Improvements like Batch mode memory grant feedback and adaptive joins.
    • CONCAT_WS: Concatenates with a separator (useful for CSV).
    • TRANSLATE: Replaces multiple characters respectively.
    • TRIM: Removes whitespace or characters from both ends of a string.
    • Python Support: Integrated into the database.

Storage Architecture

  • Fundamental Unit: A Page (88k in size).
  • Columns:
    • Non-wide table: Max 1,0241,024 columns.
    • Wide table: Max 30,00030,000 columns.
  • Rows: Limited by available storage, though IDENTITY column ranges (e.g., INT limit of 2,147,483,6472,147,483,647) or uniqueifier limits can act as ceilings.
  • Varchar(max): Can store up to 22 GB (231−12^{31}-1 bytes).