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
EmployeeDetailtable:- MS SQL Server/Oracle/MySQL:
SELECT * FROM EmployeeDetail
- MS SQL Server/Oracle/MySQL:
Column Selection: To retrieve only the
FirstNamecolumn: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
- Upper Case:
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
- MS SQL Server:
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]%'
- Specific Name:
Wildcard Characters:
- The underscore (
_) represents exactly one single character. - Gender ending in 'le' and containing exactly letters:
SELECT * FROM [EmployeeDetail] WHERE Gender like '__le'(using two underscores). - First name starting with 'A' and containing exactly letters:
SELECT * FROM [EmployeeDetail] WHERE FirstName like 'A\_\_\_\_'(using four underscores). - Containing a literal percentage sign (
%):SELECT * FROM [EmployeeDetail] WHERE FirstName like '%[%]%'
- The underscore (
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]
- Unique Departments:
SQL Server Date and Time Related Queries
Formatting Dates:
- Format
dd mmm yyyy(e.g., Feb ):SELECT CONVERT(VARCHAR(20),JoiningDate,106) FROM [EmployeeDetail] - Format
yyyy/mm/dd(e.g., //):SELECT CONVERT(VARCHAR(20),JoiningDate,111) FROM [EmployeeDetail] - Extract Time part only:
SELECT CONVERT(VARCHAR(20),JoiningDate,108) FROM [EmployeeDetail]
- Format
Extracting Date Parts:
- Year:
SELECT DATEPART(YEAR, JoiningDate) FROM [EmployeeDetail] - Month:
SELECT DATEPART(MONTH,JoiningDate) FROM [EmployeeDetail]
- Year:
System and UTC Dates:
- Current System Date:
SELECT GETDATE() - Current UTC Date:
SELECT GETUTCDATE()
- Current System Date:
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]
- Difference in months between current date and joining date:
Filtering by Date:
- Joining year :
SELECT * FROM [EmployeeDetail] WHERE DATEPART(YYYY,JoiningDate) = '2013' - Joining month January ():
SELECT * FROM [EmployeeDetail] WHERE DATEPART(MM,JoiningDate) = '1' - Between specific dates:
SELECT * FROM [EmployeeDetail] WHERE JoiningDate BETWEEN '2013-01-01' AND '2013-12-01'
- Joining year :
Advanced Filtering, Salary, and Formatting
Row Selection:
- Select Top 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')
- Select Top record:
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]
- Remove right white spaces:
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]
- To display Gender as M/F:
Salary Queries:
- Salary > :
SELECT * FROM [EmployeeDetail] WHERE Salary > 600000 - Salary < :
SELECT * FROM [EmployeeDetail] WHERE Salary < 700000 - Salary between and :
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
- Salary > :
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
- Department-wise total salary:
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)orMIN(Salary)withGROUP BY DepartmentandORDER BYaggregate result.
- Department count and total salary:
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
- Example for projects assigned to more than one employee:
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)
- Employees without projects:
Tricky SQL Query Sets and Behaviors
Identity and Syntax Quirks:
SELECT 15: Output is .SELECT $: Output is (Money format).SELECT COUNT(*)on an empty set result or constant: Can return .SELECT 'VIKAS' + 1: Throws an error because string and integer cannot be implicitly added without conversion.SELECT 'VIKAS' + '1': Output isVIKAS1.SELECT NULL + 5: Output isNULL.SELECT 1/0orSELECT 0/0: Throws a divide-by-zero error.SELECT 0/9: Output is .SELECT 1 WHERE NULL = NULL: Returns rows becauseNULLcannot be equated toNULLvia=.
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]
- Concatenate all names with commas:
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]
- Convert first letter to Upper and others to Lower:
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 ) 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)
- Add Primary Key:
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
NULLvalue. - CHECK: Ensures all values meet a specific requirement.
SQL Server Data Types
Integer Types:
bigint: to .int: to .smallint: to .tinyint: to .
Character Types:
char: Fixed-length non-Unicode ( max).varchar: Variable-length non-Unicode ( max, ormaxfor GB).nchar: Fixed-length Unicode ( max).nvarchar: Variable-length Unicode ( max).
Date/Time Types:
datetime: to ( ms accuracy).datetime2: More accurate (up to ns) and compact thandatetime.smalldatetime: to ( min accuracy).timestamp: Deprecated; replaced byrowversion. It is a database-wide unique number that updates when a row changes.
Other Types:
bit: or .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 (SQL ) or (SQL ) per table.
Fill Factor: A value (-) that determines the percentage of space on each leaf-level page to be filled with data. Default is (or ), 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
SELECTstatement. It does not store data physically unless it is a Clustered Indexed View. - Types: Indexed Views, Partitioned Views, System Views.
- Column Limit: Max columns.
- Encryption: Use
WITH ENCRYPTIONto hide the underlying code. - Schema Binding: Use
WITH SCHEMABINDINGto prevent changes to base tables that would break the view. Requires schemas for all objects and prohibits the use ofSELECT *.
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 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_executesqlinstead ofEXECUTEfor dynamic SQL.
- Include
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
TEMPDBdatabase.
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 (k in size).
- Columns:
- Non-wide table: Max columns.
- Wide table: Max columns.
- Rows: Limited by available storage, though
IDENTITYcolumn ranges (e.g.,INTlimit of ) or uniqueifier limits can act as ceilings. - Varchar(max): Can store up to GB ( bytes).