95+ SQL Server Interview Questions and Answers for All Levels

41 Technical SQL Server Interview Questions for Beginners

1. What Is SQL?

SQL stands for Structured Query Language. You use it to talk to relational database management
systems such as SQL Server. It lets you fetch rows, insert new data, update existing rows, and delete
data.

You also use SQL to define a schema, create tables, and control access through permissions. In
practical interviews, expect questions about select, insert, update, delet, create table, and simple
transaction control.

2. What Are SQL Dialects? Give Some Examples

SQL dialects are vendor or product variations of the language. Each offers core SQL plus extensions
and specific features. Examples include Microsoft SQL Server and its T-SQL extensions, PostgreSQL,
MySQL, SQLite, Oracle SQL, and nonrelational systems that use query languages such as MongoDB’s
query API. Knowing dialect differences helps when moving code between environments.

3. What Are the Main Applications of SQL?

With SQL, you create and alter tables, insert and delete rows, and update column values. You
retrieve and summarize data with SELECT, aggregate functions, grouping, and joins. You filter and
sort results, and you integrate SQL with languages like Python for reporting or ETL. In a junior role,
you’ll use these basics to run reports, debug queries, and build minor stored procedures.

4. What Is an SQL Statement? Give Some Examples

An SQL statement is a command sent to the database engine. The engine parses, plans, and executes
it. Common examples include SELECT for queries, CREATE TABLE to define a schema, INSERT to add
rows, DELETE to remove rows, DROP to remove objects, GRANT to grant permissions, and REVOKE to
revoke them. Interviewers often ask you to write or interpret these statements.

5. What Types of SQL Commands (or SQL Subsets) Do You Know?

SQL commands group by purpose:

 Data Definition Language (DDL) to define the schema

 Data Manipulation Language (DML) to change rows

 Data Control Language (DCL) to manage privileges

 Transaction Control Language (TCL) to manage transactions

 Data Query Language (DQL) focused on SELECT

Each category maps to common interview topics like schema design, CRUD operations, and
transaction handling.

6. Give Some Examples of Common SQL Commands of Each Type

 DDL: CREATE, ALTER TABLE, DROP, TRUNCATE, ADD COLUMN.

  DML: INSERT, UPDATE, DELETE.

 DCL: GRANT, REVOKE.

 TCL: COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION.

 DQL: SELECT

Interview tasks often combine these tasks, such as updating the schema and then running queries.

7. What Is a Database?

A database is a structured collection of data stored in tables, indexes, views, procedures, and
functions. It groups related objects and provides a controlled environment for storage, retrieval, and
security. In SQL Server, this also maps to files, filegroups, and logical databases you manage with
backups and restores.

8. What Is DBMS, and What Types of DBMS Do You Know?

DBMS stands for Database Management System. It’s the software that stores, retrieves, and manages
data. Types include relational, hierarchical, network, graph, and object databases. Relational DBMS,
such as SQL Server, MySQL, and PostgreSQL, are the ones interviewers expect you to know for
standard SQL roles.

9. What Is RDBMS? Give Some Examples of RDBMS

RDBMS means Relational Database Management System. It stores data in tables and uses keys to
relate tables. It enforces ACID properties for transactions.

Examples:

 Microsoft SQL Server

 MySQL

 PostgreSQL

 Oracle Database

 MariaDB

Expect questions about normalization, joins, and transactions in RDBMS interviews.

10. What Are Tables and Fields in SQL?

A table is a set of related records organized in rows and columns. A field is a column in that table and
defines the type of data stored. Tables hold data rows, and fields define the shape of each row,
including constraints like NOT NULL or DEFAULT.

11. What Is an SQL Query, and What Types of Queries Do You Know?

An SQL query is code that accesses or modifies database data. Select queries retrieve data and can
include grouping, ordering, and joins. Action queries change data or structure: CREATE, INSERT,
UPDATE, DELETE, and ALTER. During interviews, you may be asked to write both types and explain
the effects on data and transactions.

12. What Is a Subquery?

A subquery is an inner query placed inside another query’s clause, such as SELECT, FROM, or WHERE.
The database evaluates inner queries first and uses their result in the outer query. You will see
subqueries used to filter, compute aggregates per group, or provide derived tables for joins.

13. What Types of SQL Subqueries Do You Know?

Single-row subquery returns one row. Multi-row returns multiple rows. Multi-column returns
multiple columns. Correlated subquery references columns from the outer query and executes per
outer row. Nested subqueries are subqueries inside other subqueries. Knowing when to replace
correlated subqueries with joins improves performance.

14. What Is a Constraint, and Why Use Constraints?

A constraint enforces rules on table columns to maintain integrity. Constraints ensure valid data,
prevent bad inserts, and keep relationships consistent. Typical constraints include NOT NULL,
UNIQUE, and FOREIGN KEY. Interviewers expect clear examples of when to use each constraint to
prevent data corruption.

15. What SQL Constraints Do You Know?

DEFAULT supplies a default value for a column. UNIQUE enforces unique values. NOT NULL forbids
nulls. PRIMARY KEY combines NOT NULL and UNIQUE to identify rows uniquely. FOREIGN KEY links a
column to another table’s primary key to enforce referential integrity.

16. What Is a Join?

A join combines rows from two or more tables using related columns. Joins let you merge data from
normalized tables for queries and reports. You’ll use joins daily for queries that pull customer
information, orders, or related records for analysis.

17. What Types of Joins Do You Know?

INNER JOIN returns rows that match join conditions in both tables. LEFT OUTER JOIN returns all rows
from the left table and matched rows from the right. RIGHT OUTER JOIN returns all rows from the
right table and matched rows from the left. FULL OUTER JOIN returns rows from both tables,
matched or not. Understanding each helps you control which rows appear in results.

18. What Is a Primary Key?

A primary key is one or more columns that uniquely identify each row and disallow NULLs. A table
can have only one primary key. Primary keys often drive clustered indexes in SQL Server and are
critical for joins and referential integrity.

19. What Is a Unique Key?

A unique key enforces uniqueness on a column or set of columns while allowing a single NULL in
many systems. It ensures no duplicates, but does not carry the same identity role as a primary key.
You use unique constraints to prevent duplicate business keys, such as email addresses.

20. What Is a Foreign Key?

A foreign key is a column or set of columns that references a primary key in another table. It ensures
that values in the child table are present in the parent table. Foreign keys are central to a normalized
schema and prevent orphaned rows.
21. What Is an Index?

An index is a data structure that accelerates row lookups, filter predicates, and ordering. Indexes
reduce full table scans by providing quick access paths. In SQL Server, indexes use B-tree structures
and can drastically alter query performance depending on design.

22. What Types of Indexes Do You Know?

Unique index prevents duplicates. A clustered index defines the physical order of table rows; a table
can have one clustered index. A nonclustered index stores key values separately from the data and
can be multiple per table. Knowing when to create included columns or composite keys affects
performance and storage.

23. What Is a Schema?

A schema is a namespace that groups tables, views, stored procedures, functions, and triggers.
Schemas help manage permissions and organize objects inside a database. For example, dbo is the
default schema in SQL Server.

24. What Is a SQL Comment?

A comment explains intent to future readers and doesn’t affect execution. Single-line comments start
with --. Multi-line comments use /* ... */. Use comments to clarify complicated joins, business rules,
or temporary workarounds.

25. What Is a SQL Operator?

An SQL operator is a symbol or keyword used to perform operations in expressions and conditions.
Operators appear in WHERE clauses, joins, and calculations. Expect questions about comparison,
logical, arithmetic, and set operators.

26. What Types of SQL Operators Do You Know?

 Arithmetic operators: +, -, *, /

 Comparison: =, <>, <, >, <=, >=

 Compound assignment appears in procedural extensions

 Logical operators: AND, OR, NOT, BETWEEN. String operators include LIKE with % and _

 Set operators: UNION, UNION ALL, INTERSECT, EXCEPT (or MINUS in some systems). Each has
rules for NULL behavior.

27. What Is an Alias?

An alias gives a temporary name to a table or column to simplify queries. You introduce it with AS,
though AS is optional for columns.

Example: SELECT [Link] AS CustomerName FROM Customers c. Aliases make complex joins and
derived columns easier to read.

28. What Is a Clause?

A clause is a logical part of a SQL statement that controls behavior or filters results. Examples include
WHERE to filter rows, ORDER BY to sort, GROUP BY to aggregate, HAVING to filter groups, and LIMIT
or TOP to restrict row counts. You chain clauses in queries to shape output.
29. What Are Some Common Statements Used With the SELECT Query?

Common statements with SELECT include FROM to indicate source tables, JOIN to combine tables,
WHERE to filter rows, GROUP BY to aggregate, HAVING to filter aggregated groups, ORDER BY to sort,
and LIMIT or TOP to restrict rows. Interview problems often require the right combination to get
correct results.

30. How to Create a Table?

Use CREATE TABLE to define columns and datatypes. Example:CREATE TABLE table_name ( col_1
datatype, col_2 datatype, col_3 datatype);

Define primary keys, defaults, and constraints at create time to enforce integrity from the start.

31. How to Update a Table?

Use UPDATE to change rows. Syntax:UPDATE table_nameSET col_1 = value_1, col_2 = value_2WHERE
condition;Always include a WHERE clause to avoid updating every row. In SQL Server, you may
combine UPDATE with JOINs or OUTPUT for auditing.

32. How to Delete a Table From a Database?

Use DROP TABLE table_name; to remove a table and its data. DROP is irreversible without backups.
In interviews, candidates should distinguish DROP TABLE from TRUNCATE TABLE; TRUNCATE removes
rows but keeps the table definition.

33. How to Get the Count of Records in a Table?

Use COUNT(*) to return the number of rows:SELECT COUNT(*) FROM table_name;Use

COUNT(column) to count non-null values in a column. For large tables, consider indexed views or
filtered counts for performance.

34. How to Sort Records in a Table?

Use ORDER BY to sort:SELECT * FROM table_nameORDER BY col_1;Add DESC for descending order.
You can sort by multiple columns and specify ascending or descending per column. Proper indexing
can help with ORDER BY performance when the index matches the sort.

35. How to Select All Columns From a Table?

Use SELECT * FROM table_name; to return every column. In production code, prefer explicit column
lists to avoid unexpected schema changes and to reduce data transfer.

36. How to Select Common Records From Two Tables?

You can use INTERSECT to return rows common to two SELECT results:SELECT * FROM
table_1INTERSECTSELECT * FROM table_2;

Alternatively, use INNER JOIN on matching keys when you want specific columns or better control
over joins and performance.

37. What Is the DISTINCT Statement and How Do You Use It?

DISTINCT filters duplicate rows from query results. Example:SELECT DISTINCT col_1FROM
table_name;Use DISTINCT only when you need unique values; overuse can slow queries, and
sometimes GROUP BY or proper keys are better solutions.
38. What Are Relationships? Give Some Examples

Relationships define how tables relate through keys. One-to-many is common; a customer table links
to many Orders via CustomerID as a foreign key. One-to-one links two tables by shared primary keys.
Many-to-many uses a junction table, for example, Products, Orders, and OrderDetails.

39. What Is a NULL Value? How Is It Different From Zero or a Blank Space?

NULL means unknown or missing data. Zero is a numeric value; an empty string is a valid text value
of length zero. NULL propagates in arithmetic and comparisons and requires IS NULL or IS NOT NULL
checks rather than equality operators.

40. What Is the Difference Between SQL and NoSQL?

SQL databases are relational and use predefined schemas with tables and joins. NoSQL systems are
nonrelational and often schema-less, using document, key-value, column family, or graph models.
SQL Server is a relational engine; choose NoSQL when you need flexible schema, horizontal scaling,
or specialized query models.

41. What Are Some Common Challenges When Working With SQL Databases?

Challenges include query performance tuning on large datasets, choosing and maintaining indexes,
preserving data integrity with constraints and transactions, handling concurrent access with locking
and preventing deadlocks, designing normalized schemas without over-normalizing, and optimizing
execution plans and statistics for stable performance. In interviews, expect scenario questions about
indexing strategies, execution plans, and basic troubleshooting.

44 Intermediate SQL Server Interview Questions

1. What Is a Function in SQL, and Why Use Functions?

A function is a named, encapsulated set of SQL statements that accepts parameters, performs
calculations or transformations, and returns a value. Use functions to standardize logic, avoid
repeated code, and keep business rules close to the data. In SQL Server, you’ll typically use scalar
functions for single values and inline table-valued functions for result sets, but be aware that scalar
UDFs can impact performance unless inlined.

2. What Types of SQL Functions Do You Know?

Functions are split into aggregate and scalar types, and into built-in versus user-defined. Aggregate
functions operate on groups and return one result per group. Scalar functions operate per value and
return a single result. The engine provides built-in functions; user-defined functions let you
implement custom logic, but in SQL Server, choose inline TVFs when you need performance.

3. What Aggregate Functions Do You Know?

AVG, SUM, MIN, MAX, and COUNT cover typical numeric and row-count aggregates. Some engines
offer FIRST and LAST but rely on ordering to be deterministic. Use COUNT(*) for row counts,
COUNT(column) to exclude nulls, and grouped aggregates with GROUP BY to compute per-group
summaries that feed reporting and business logic.

4. What Scalar Functions Do You Know?

Common scalar functions include LEN or LENGTH for string length, UPPER/LOWER for case
conversion, INITCAP for title casing, SUBSTR or SUBSTRING for extracting text, ROUND for numeric
rounding, and GETDATE or NOW for current timestamps. In SQL Server, prefer built-in, sargable
functions when used in WHERE clauses to allow index seeks.

5. What Are Case Manipulation Functions? Give Some Examples

Case manipulation functions change string case:

 UPPER/UPPERCASE

 LOWER

 Functions that title-case values

Use them when normalizing user input, preparing display names, or performing case-insensitive
comparisons when the collation does not already handle it.

6. What Are Character Manipulation Functions? Give Some Examples

Character functions include CONCAT, SUBSTRING, LEN, REPLACE, CHARINDEX or INSTR, LPAD/RPAD
(or RIGHT/LEFT padding equivalents), and TRIM. Use them to build identifiers, parse CSV-like fields,
remove unwanted characters before loading, or construct readable outputs from normalized data.

7. What Is the Difference Between Local and Global Variables?

Local variables exist only inside the stored procedure, batch, or function where you declare them.
Global variables exist outside those scopes and can be accessed more broadly; in many SQL Server
contexts, global variables are system-level (like @@IDENTITY) rather than user-declared. Scope
determines lifetime and concurrency behavior.

8. What Is the Difference Between SQL and PL/SQL?

SQL is the declarative language for querying and modifying relational data. PL/SQL is Oracle’s
procedural extension that adds loops, conditional logic, and exception handling for business rules
inside the database. For SQL Server, the equivalent procedural layer is T-SQL, which offers stored
procedures, control flow, and error handling.

9. What Is the Difference Between LEFT JOIN and LEFT OUTER JOIN?

LEFT JOIN and LEFT OUTER JOIN mean the same thing; SQL allows the OUTER keyword to be
optional. Both return every row from the left table and matching rows from the right table, with
NULLs for non-matching right-side columns.

10. What is indexing in SQL, and how does it improve performance?

An index is a data structure that speeds row retrieval by organizing key values and pointers to data
pages. Proper indexing reduces full table scans and lowers IO. Indexes incur storage costs and slow
down INSERT/UPDATE/DELETE operations because the engine must maintain them. In SQL Server,
consider clustered vs nonclustered, included columns, fillfactor, and statistics when tuning.

11. What Is Indexing in SQL, and How Does It Improve Performance?

A stored procedure is a compiled set of T-SQL statements that can accept parameters, perform
actions, return multiple result sets, and modify database state. Functions typically return a single
value or table and are intended to be side-effect-free. Use stored procedures for transactional work,
batching, and encapsulating complex workflows.

12. What Is the Default Data Ordering With the ORDER BY Statement, and How Do You Change It?

ORDER BY defaults to ascending order. Add DESC to sort descending. In SQL Server, you can also
specify multiple columns with ASC or DESC per column to control tie-breakers and deterministic
results.

13. What Set Operators Do You Know?

UNION combines results and removes duplicates. UNION ALL combines results, preserving
duplicates, and is faster. INTERSECT returns rows common to both queries. EXCEPT or MINUS returns
rows from the first query not in the second. Use UNION ALL for performance when duplicates are
acceptable.

14. What Operator Is Used in the Query for Pattern Matching?

The LIKE operator uses % for any sequence of characters and _ for a single character. Use LIKE for
simple patterns; use full-text search or PATINDEX when you need advanced linguistic matching or
performance on large text columns.

15. What Is the Difference Between a Primary Key and a Unique Key?

A primary key uniquely identifies each row and implicitly creates a unique index; it cannot be NULL. A
unique key enforces uniqueness but allows a NULL (depending on the engine) and can exist multiple
times per table. Primary keys are used for relationships and joins.

16. What Is a Composite Primary Key?

A composite primary key uses two or more columns together to identify a row uniquely. Use it when
no single column provides a unique identifier; otherwise, prefer a surrogate key for simpler joins and
indexing.

17. What Is the Order of Appearance of the Common Statements in the SELECT Query?

The standard writing order is SELECT – FROM – JOIN – ON – WHERE – GROUP BY – HAVING – ORDER
BY – LIMIT. Developers format queries this way to improve clarity.

18. In Which Order Does the Interpreter Execute the Common Statements in the SELECT Query?

The engine evaluates FROM (including JOIN and ON), then WHERE filters rows, then GROUP BY
groups them, HAVING filters groups, SELECT projects columns and expressions, ORDER BY sorts, and
finally LIMIT or TOP narrows the result set. Understanding this helps reason about when aggregates
and aliases are available.

19. What Is a View, and Why Use It?

A view is a saved query that presents data as a virtual table. Use views to encapsulate joins and
filters, restrict column-level access, and provide a stable interface as underlying tables change. In SQL
Server, use indexed views carefully since they materialize data and require maintenance.

20. Can We Create a View Based on Another View?

Yes, nested views are allowed. Stacking multiple views increases complexity and can obscure
inefficient joins or non-sargable expressions, making execution plans difficult to optimize. Keep
nesting shallow and inspect execution plans when performance matters.

21. Can We Still Use a View if the Original Table Is Deleted?

No. If the base table is dropped, dependent views become invalid and queries against them fail.
Maintain object dependencies and use schema-bound opinions if you need protection from
accidental changes in SQL Server.

22. What Types of SQL Relationships Do You Know?

Common relationships are one-to-one, one-to-many, and many-to-many. Implement one-to-many

with a foreign key on the child table. Represent many-to-many with an associative join table. Enforce
referential integrity with foreign key constraints and consider cascading actions carefully.

23. What Are the Possible Values of a BOOLEAN Data Field?

PostgreSQL has a BOOLEAN with TRUE, FALSE, and NULL. SQL Server typically uses BIT with 1, 0, or
NULL to represent boolean states. Choose types that match query patterns and storage needs.

24. What Is Normalization in SQL, and Why Use It?

Normalization organizes tables to eliminate duplicate data and reduce update anomalies. Standard
forms guide decomposition so each table stores a single logical entity. Normalized schemas simplify
writes and enforce consistency, but may require more joins for reads.

25. What Is Denormalization in SQL, and Why Use It?

Denormalization intentionally duplicates data to avoid costly joins and improve read performance.
Use it when queries dominate the workload and latency is critical. Track update paths and accept the
need for extra maintenance and careful indexing.

26. What Is the Difference Between Renaming a Column and Giving an Alias to It?

Renaming a column changes the table schema and persists across sessions. An alias is a temporary
name for display or downstream processing in a query. Use aliases to make results readable without
altering production schemas.

27. What Is the Difference Between Nested and Correlated Subqueries?

A non-correlated (nested) subquery runs independently and returns a value set used by the outer
query. A correlated subquery references columns from the outer query and executes per outer row.
Correlated subqueries can be less efficient and often convert to joins or apply operators for better
performance.

28. What Is the Difference Between Clustered and Non-Clustered Indexes?

A clustered index defines the physical ordering of table rows and is limited to one per table; a non-
clustered index stores keys with pointers to the data rows. Clustered indexes benefit range scans on
the key. Non-clustered indexes with included columns can cover queries and avoid lookups.

29. What Is the CASE() Function?

CASE implements if-then-else logic inside SELECT, WHERE, or ORDER BY. It evaluates WHEN
conditions in order and returns the matching THEN value or ELSE if provided, else NULL. Use CASE to
bucket values, compute conditional aggregates, or create custom sort keys.

30. What Is the Difference Between the DELETE and TRUNCATE Statements?

DELETE removes rows one at a time and logs each deletion; you can specify WHERE, and the
operation is transactional and can fire triggers. TRUNCATE removes all rows by deallocating data
pages, is minimally logged, is faster, and cannot be used when foreign keys reference the table.
TRUNCATE resets identity metadata in many engines.

31. What Is the Difference Between the DROP and TRUNCATE Statements?

DROP removes a table and its metadata permanently, including constraints and permissions.
TRUNCATE removes all rows but preserves the table structure and related permissions. Both are
irreversible actions and require appropriate permissions.

32. What Is the Difference Between the HAVING and WHERE Statements?

WHERE filters raw rows before aggregation. HAVING filter groups after aggregation with GROUP BY.
When an expression references an aggregate, use HAVING; otherwise, prefer WHERE for earlier
pruning and better performance.

33. How Do You Add a Record to a Table?

Use INSERT INTO table_name VALUES (...) or INSERT INTO table_name (col1, col2) VALUES (...). For
SQL Server, you can also INSERT ... SELECT for bulk insert from another table, and use OUTPUT to
capture inserted rows.

34. How Do You Delete a Record From a Table?

Use DELETE FROM table_name WHERE condition. Include a precise WHERE clause or use
transactions to avoid accidental full-table deletions. In SQL Server, wrap risky deletes in a transaction
and test with SELECT first.

35. How Do You Add a Column to a Table?

Run ALTER TABLE table_name ADD column_name datatype. Consider default values and nullability to
avoid expensive updates on large tables. In SQL Server, add nullable columns first or add with a
default using WITH VALUES when appropriate.

36. How Do You Rename a Column of a Table?

Use ALTER TABLE ... RENAME COLUMN ... TO ... in some engines. In SQL Server, use sp_rename
'[Link]', 'newname', 'COLUMN' or use proper migration scripts that update dependent code
and indexes.

37. How Do You Delete a Column From a Table?

Use ALTER TABLE table_name DROP COLUMN column_name. Check for dependencies such as
indexes, computed columns, or views before dropping to prevent runtime errors.

38. How Do You Select All Even or All Odd Records in a Table?

Test the remainder of the division by two. Use MOD(column, 2) = 0 in engines that support MOD, or
column % 2 = 0 in SQL Server to select even rows. Adjust the predicate for odd rows accordingly.
39. How to Prevent Duplicate Records When Making a Query?

Use SELECT DISTINCT to remove duplicates in the result set, for data integrity, and enforce UNIQUE
constraints or unique indexes on the table to prevent duplicates at the source.

40. How Do You Insert Many Rows in a Table?

Use a multi-row INSERT syntax with multiple VALUES lists, or INSERT ... SELECT for bulk loading. In
SQL Server, use bcp, BULK INSERT, or SqlBulkCopy for huge volumes to minimize logging and round-
trips.

41. How Do You Find the Nth Highest Value in a Column of a Table?

ORDER BY column DESC with OFFSET n-1 LIMIT 1 (or TOP 1 with OFFSET in SQL Server) returns the
nth highest by sorting. Alternatively, use window functions such as ROW_NUMBER, RANK, or
DENSE_RANK, partitioned as needed, and pick the row where rank = n for more apparent intent and
better control over ties.

42. How Do You Find the Values in a Text Column of a Table That Start With a Certain Letter?

Use WHERE column LIKE 'A%' to match strings starting with A. If you need performance for large
tables, add a left-anchored index or use full-text indexing for linguistic matching.

43. How Do You Find the Last ID in a Table?

Use MAX(id) to find the highest value, or ORDER BY id DESC with TOP 1 in SQL Server: SELECT TOP 1
id FROM table_name ORDER BY id DESC. For recently inserted identity values, use SCOPE_IDENTITY()
in the same scope.

44. How to Select Random Rows From a Table?

ORDER BY NEWID() in SQL Server or ORDER BY RAND() in MySQL returns random rows; then LIMIT or
TOP to restrict results. This approach can be expensive for large tables; prefer TABLESAMPLE or
reservoir sampling techniques when efficiency matters.

Experienced Questions

1. Write a SQL Query to Find the Nth Highest Salary From an Employee Table

SELECT DISTINCT TOP 1 Salary AS NthHighestSalaryFROM ( SELECT TOP 1 Salary FROM Employees
ORDER BY Salary DESC) AS NthHighestSalariesORDER BY Salary ASC;Output:nth highest salaryHighest
salary41

Explanation and Correction

The provided query is incorrect for the general nth case and will not return the nth highest salary as
expected. Use window functions for clarity and correctness. For example, use DENSE_RANK when
identical salaries should count once, or use ROW_NUMBER when each row is distinct.

Recommended pattern using parameter @n and DENSE_RANK:

WITH RankedSalaries AS ( SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk FROM
Employees WHERE Salary IS NOT NULL)SELECT Salary AS NthHighestSalaryFROM
RankedSalariesWHERE rnk = @n;
Performance and production notes:

 Add a nonclustered index on Salary if queries on salary ordering are frequent. A covering
index can reduce IO and allow index seek plus in order scan.

 For huge tables, sorts for ranking can be expensive and may spill to tempdb. Monitor sort
warnings in the execution plan and tune MAXDOP or memory grants if needed.

 Consider filtered statistics or filtered indexes for subsets. Update statistics frequently after
large data loads.

 Watch parameter sniffing when @n is constant in cached plans. Use OPTION (RECOMPILE) if
the plan shape varies.

2. Write a SQL Query to Find the Second Highest Salary From an Employee Table

SELECT MAX(Salary) AS salary FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM
Employees);Output:second highest salary from an Employee tableSecond highest salary

Explanation and Improvements

The provided query works if the top salary is unique and the Salary is not NULL. For duplicate top
wages, it gives the correct next lower salary. For robust behavior, use DENSE_RANK or handle NULLs.

Robust approach using DENSE_RANK:

WITH Ranked AS ( SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk FROM
Employees WHERE Salary IS NOT NULL)SELECT SalaryFROM RankedWHERE rnk = 2;

Operational considerations:

 If the Salary is volatile, ensure accurate stats before running analytics.

 If Salary has high cardinality, the DENSE_RANK window can be costly; an index on Salary
helps.

3. Write a Query to Calculate the Total Number of Orders Placed by Each Customer (Order Table)

SELECT cust_ID, COUNT(*) AS TotalOrders FROM Orders GROUP BY

cust_ID;Output:OrderPlacedbyEachCustomer

Explanation, Scaling, and Accuracy

This GROUP BY is straightforward and efficient with an index on cust_ID. For huge datasets, consider
incremental aggregates, partitioning, or precomputed summary [Link] patterns and
performance tips:

 If Orders is partitioned by order date, aggregate across partitions using partition elimination
where possible.

 Maintain a summary table or use an indexed view for near real-time counts if reporting
latency must be low. Ensure the indexed view meets schema and determinism requirements.

 Use COUNT_BIG when counts may exceed 2^31 1 to avoid overflow.

  For concurrency, use snapshot isolation to avoid long locking during heavy reporting.

4. Write a Query to Find Employees Who Have the Same Job Title but Different Salaries

SELECT e1.emp_id , e1.emp_name, [Link], [Link] FROM Employees e1 JOIN Employees e2

ON [Link]= [Link] e1.emp_id <> e2.emp_id AND [Link] <> [Link] ORDER BY
e1.job_title, e1.emp_id;Output:find employees who have the same job title

Explanation and Refinement

The self-join works but duplicates rows and can be inefficient. Use analytic functions or EXISTS to
reduce work and return distinct [Link] query using EXISTS:SELECT DISTINCT e.emp_id,
e.emp_name, [Link], [Link] Employees eWHERE EXISTS ( SELECT 1 FROM Employees e2
WHERE [Link] = [Link] AND e2.emp_id <> e.emp_id AND [Link] <> [Link]);Index and
execution advice:

 Create an index on JobTitle and include Salary where selective. That helps index seeks for the
EXISTS predicate.

 Look at execution plans to avoid nested loops on large populations. Hash joins may be
preferable, but check memory grants.

5. Write a SQL Query to Get the Top 5 Highest-Paid Employees

SELECT TOP 5 * FROM Employees ORDER BY Salary DESC;Output:top 5 highest paid employees

Explanation and Production Considerations

TOP with ORDER BY is fine for small sets. For repeated dashboard queries, add a supporting index
and avoid SELECT *. Use projection to reduce [Link] practice:SELECT TOP (5) emp_id, emp_name,
Salary, JobTitleFROM EmployeesORDER BY Salary DESC;Advanced notes:

 If queries run under heavy concurrency, consider cached result sets refreshed periodically.

 Columnstore indexes or compressed indexes help when scanning large rowstore tables to
fetch the top N by aggregation.

6. Write a Query to Find All Employees Who Joined in the Last Month

SELECT * FROM EmployeesWHERE JoinDate>= DATEADD(MONTH, -1, DATEADD(MONTH,

DATEDIFF(MONTH, 0, GETDATE()), 0)) AND JoinDate< DATEADD(MONTH, DATEDIFF(MONTH, 0,
GETDATE()), 0);Output:No employees are there who joined in the last [Link] all employees who
joined in the last month

Explanation and Robustness

The provided expression finds rows in the previous calendar month, but it is hard to read. Use
explicit boundaries and handle time zones and datetime2 precision.

Clearer pattern for the previous calendar month:

DECLARE @Start DATETIME2 = DATEADD(MONTH, -1, DATEFROMPARTS(YEAR(GETDATE()),

MONTH(GETDATE()), 1));DECLARE @End DATETIME2 = DATEFROMPARTS(YEAR(GETDATE()),
MONTH(GETDATE()), 1);SELECT emp_id, emp_name, JoinDateFROM EmployeesWHERE JoinDate >=
@Start AND JoinDate < @End;
Operational points:

 Ensure JoinDate uses a consistent timezone or is stored in UTC; convert at presentation time.

 Add an index on JoinDate for fast range seeks.

7. Write a SQL Query to Find Duplicate Records in a Table

SELECT emp_name, COUNT(*) AS DuplicateCount FROM Employees GROUP BY emp_name HAVING

COUNT(*) > 1;Output:find duplicate records in a table

Explanation and Alternatives

Grouping by a single column shows duplicates for that column. For actual duplicate rows, include all
columns or a hash of columns to identify identical rows.

Detect full row duplicates:

SELECT emp_name, emp_dob, dept_id, COUNT(*) AS DuplicateCountFROM EmployeesGROUP BY

emp_name, emp_dob, dept_idHAVING COUNT(*) > 1;

Large-scale techniques:

 For vast rows, compute a HASHBYTES of concatenated columns to speed grouping. Watch for
collisions and use sufficiently large algorithms like SHA2_256.

 Use window functions with ROW_NUMBER to mark duplicates for deletion.

 Use minimal logging and batch deletes to avoid long blocking when cleaning duplicates.

8. Write a Query to Calculate the Average Salary of Employees in Each Department

SELECT d.dept_name, AVG([Link]) AS AvgSalary FROM Employees e JOIN Departments d ON

e.dept_id = d.dept_idGROUP BY d.dept_name;Output:averageSalaryinEachDept

Explanation, Accuracy, and Storage

AVG returns approximate values; cast to decimal for precise rounding. Joining to Departments is fine
if dept_id is indexed and referential integrity is enforced.

Improved query with formatting:

SELECT d.dept_name, CAST(AVG(CAST([Link] AS DECIMAL(18,2))) AS DECIMAL(18,2)) AS

AvgSalaryFROM Employees eJOIN Departments d ON e.dept_id = d.dept_idGROUP BY d.dept_name;

Performance and scale:

 Use statistics on dept_id to get good plans for aggregation.

 For large dimension tables, use smaller lookup tables to reduce join costs.

 Consider columnstore indexes for analytical aggregation over many rows.

9. Write a SQL Query to Find the Total Number of Products Sold Each Month
SELECT YEAR, MONTH, COUNT(*) AS TotalProductsSold FROM Sales GROUP BY YEAR, MONTH ORDER
BY YEAR, MONTH;Output:TotalProductsSolds

Explanation, Corrections, and Best Practices

Grouping by computed YEAR and MONTH may prevent index seeks. Instead, compute a date key or
use date dimension keys for efficient grouping, and avoid using reserved words YEAR or MONTH as
column names.

Partition-aware pattern:

SELECT DATEPART(YEAR, SaleDate) AS SaleYear, DATEPART(MONTH, SaleDate) AS SaleMonth,

COUNT(*) AS TotalProductsSoldFROM SalesGROUP BY DATEPART(YEAR, SaleDate),
DATEPART(MONTH, SaleDate)ORDER BY SaleYear, SaleMonth;

Advanced techniques:

 Partition Sales by SaleDate to prune partitions for date ranges and improve concurrency.

 Use columnstore indexes for high-throughput analytical workloads.

 Preaggregate into monthly summary tables for dashboards with low latency

10. Write a SQL Query to Identify Employees Who Have Duplicate Manager Names

SELECT e1.emp_ID, e1.emp_name, e1.manager_name FROM Employees e1JOIN Employees e2 ON

e1.manager_name = e2.manager_nameWHERE e1.emp_ID <> e2.emp_IDAND e1.emp_name <>
e2.emp_nameORDER BY e1.manager_name, e1.emp_ID;Output:identify employees who have
duplicate manager names

Explanation and Improvements

The self-join can produce multiple duplicates. Use GROUP BY on manager_name to find managers
with multiple reports and then join back to the list of direct reports.

Cleaner approach:WITH Managers AS ( SELECT manager_name FROM Employees GROUP BY

manager_name HAVING COUNT(*) > 1)SELECT e.emp_ID, e.emp_name, e.manager_nameFROM
Employees eJOIN Managers m ON e.manager_name = m.manager_nameORDER BY
e.manager_name, e.emp_ID;

Security and data quality notes:

 Manager names are free text and can collide. Prefer a manager_id foreign key to a People
table to avoid ambiguity and to support referential integrity.

 Mask personally identifiable information if exposing results to broad audiences.

11. Write a Query to Fetch the Names of Employees Along With Their Department Names

SELECT e.emp_name, d.dept_name FROM Employees e JOIN Departments d ON e.dept_ID =

d.dept_ID;Output:(none provided)