Cognizant Gen C SQL Mock Interview

Questions
Here is a comprehensive set of mock interview questions for a SQL-focused role, categorized by
difficulty. Each question includes a sample answer and keywords to look for in a candidate's response.

Beginner Level

1. What is SQL?
Answer: SQL, which stands for Structured Query Language, is a standard programming language used
to manage and manipulate relational databases. It is used to perform tasks such as querying data,
inserting, updating, and deleting records, as well as managing database schemas.
Keywords: Structured Query Language, relational databases, query, manipulate data, schema
management.

2. What are the different subsets of SQL?

Answer: SQL commands are divided into several subsets:

DDL (Data Definition Language): Defines or modifies the database structure. Commands include
CREATE , ALTER , DROP , TRUNCATE .
DML (Data Manipulation Language): Used for managing data within the tables. Commands
include INSERT , UPDATE , DELETE .
DQL (Data Query Language): Used to retrieve data. The primary command is SELECT .
DCL (Data Control Language): Manages user access and permissions. Commands include
GRANT , REVOKE .
TCL (Transaction Control Language): Manages database transactions. Commands include
COMMIT , ROLLBACK , SAVEPOINT .
Keywords: DDL, DML, DQL, DCL, TCL, CREATE , SELECT , GRANT , COMMIT .

3. What is a database table?

Answer: A database table is a collection of related data organized in a structured format of rows and
columns. A column represents an attribute (like 'FirstName'), and a row represents a single record or
entry (like a specific customer).
Keywords: Rows, columns, records, attributes, structured data.

4. What is a primary key?

Answer: A primary key is a constraint that uniquely identifies each record in a table. It must contain

Page 1 of 14
unique values and cannot contain NULL values. A table can have only one primary key.
Keywords: Unique identifier, no NULLs, unique values, one per table.

5. What is a foreign key?

Answer: A foreign key is a key used to link two tables together. It is a field (or collection of fields) in one
table that refers to the PRIMARY KEY in another table. The foreign key constraint is used to prevent
actions that would destroy links between tables, ensuring referential integrity.
Keywords: Links tables, refers to primary key, referential integrity.

6. What is the difference between DELETE , TRUNCATE , and DROP ?

Answer:

DELETE : A DML command that removes one or more rows from a table based on a WHERE
clause. It can be rolled back.
TRUNCATE : A DDL command that removes all rows from a table quickly. It cannot be rolled back
in most databases and does not fire DELETE triggers.
DROP : A DDL command that removes the entire table, including its structure, data, and indexes.
It cannot be rolled back.
Keywords: DELETE (DML, rows, rollback), TRUNCATE (DDL, all rows, no rollback), DROP (DDL,
table structure).

7. What is the purpose of the SELECT statement?

Answer: The SELECT statement is a DQL command used to query the database and retrieve data that
matches criteria that you specify. It is the most commonly used SQL command.
Keywords: Query, retrieve data, DQL.

8. What does the WHERE clause do?

Answer: The WHERE clause is used to filter records. It is used to extract only those records that fulfill a
specified condition. It can be used with SELECT , UPDATE , and DELETE statements.
Keywords: Filter records, condition, extract.

9. What is the purpose of the ORDER BY clause?

Answer: The ORDER BY clause is used to sort the result set of a query in ascending ( ASC ) or
descending ( DESC ) order based on one or more columns.
Keywords: Sort results, ASC , DESC .

10. What is an INNER JOIN ?

Answer: An INNER JOIN returns records that have matching values in both tables. It is the most
common type of join and is the default if no join type is specified.
Keywords: Matching values, both tables, common records.

Page 2 of 14
11. What is the difference between COUNT(*) and COUNT(column_name) ?
Answer: COUNT(*) counts all the rows in the result set, including NULLs and duplicates.
COUNT(column_name) counts only the non-NULL values in the specified column.
Keywords: COUNT(*) (all rows), COUNT(column_name) (non-NULL values).

12. What is the use of the DISTINCT keyword?

Answer: The DISTINCT keyword is used with the SELECT statement to return only unique (different)
values from a column. It eliminates duplicate rows from the result set.
Keywords: Unique values, remove duplicates.

13. What is an alias in SQL?

Answer: An alias is a temporary name given to a table or a column in a query. It is used to make column
names more readable or to shorten table names in a join. It is specified with the AS keyword.
Keywords: Temporary name, AS keyword, readability.

14. What does the LIKE operator do?

Answer: The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. It
uses wildcard characters: % (represents zero or more characters) and _ (represents a single
character).
Keywords: Pattern matching, WHERE clause, wildcard, % , _ .

15. What is the difference between IN and BETWEEN ?

Answer:

IN : The IN operator allows you to specify multiple values in a WHERE clause. It is a shorthand
for multiple OR conditions.
BETWEEN : The BETWEEN operator selects values within a given range. The values can be
numbers, text, or dates. The range is inclusive (includes the start and end values).
Keywords: IN (multiple values, list), BETWEEN (range, inclusive).

16. What is a UNIQUE constraint?

Answer: A UNIQUE constraint ensures that all values in a column are different. Unlike a primary key, a
UNIQUE constraint can allow one NULL value.
Keywords: All values different, allows one NULL.

17. What is a NOT NULL constraint?

Answer: The NOT NULL constraint enforces that a column cannot accept NULL values. This means a
value must be entered for that column when a new record is inserted or an existing one is updated.
Keywords: Cannot be NULL, must have a value.

18. What does GROUP BY do?

Answer: The GROUP BY clause is used with aggregate functions (like COUNT , SUM , AVG ) to group

Page 3 of 14
rows that have the same values in specified columns into summary rows.
Keywords: Aggregate functions, group rows, summary.

19. What is the purpose of the HAVING clause?

Answer: The HAVING clause was added to SQL because the WHERE keyword cannot be used with
aggregate functions. HAVING is used to filter the results of a GROUP BY based on the result of an
aggregate function.
Keywords: Filter groups, aggregate functions, GROUP BY .

20. What is the difference between WHERE and HAVING ?

Answer: The WHERE clause filters rows before any groupings are made, while the HAVING clause
filters groups after the GROUP BY clause has been applied. WHERE works on individual rows;
HAVING works on aggregated groups.
Keywords: WHERE (before grouping, on rows), HAVING (after grouping, on groups).

21. How do you insert a new record into a table?

Answer: You use the INSERT INTO statement. The syntax is INSERT INTO table_name
(column1, column2) VALUES (value1, value2); .
Keywords: INSERT INTO , VALUES .

22. How do you update a record in a table?

Answer: You use the UPDATE statement, typically with a WHERE clause to specify which record(s) to
update. The syntax is UPDATE table_name SET column1 = value1 WHERE condition; .
Keywords: UPDATE , SET , WHERE .

23. What is an aggregate function? Give examples.

Answer: An aggregate function performs a calculation on a set of values and returns a single value.
Examples include COUNT() , SUM() , AVG() , MIN() , and MAX() .
Keywords: Single value, set of values, COUNT , SUM , AVG .

24. What is a scalar function? Give examples.

Answer: A scalar function returns a single value based on the input value. It operates on each row
individually. Examples include UCASE() (or UPPER() ), LCASE() (or LOWER() ), LEN() (or
LENGTH() ), and ROUND() .
Keywords: Single value, row-by-row, UPPER , ROUND .

25. What does the ALTER TABLE statement do?

Answer: The ALTER TABLE statement is a DDL command used to add, delete, or modify columns in
an existing table. It can also be used to add and drop various constraints on an existing table.
Keywords: DDL, add, delete, modify columns, constraints.

Page 4 of 14
26. What is a NULL value?
Answer: A NULL value in a table field represents a value that is unknown or missing. It is not the same
as zero or an empty string.
Keywords: Unknown, missing, not zero, not empty string.

27. What is a subquery?

Answer: A subquery, or inner query, is a query nested inside another SQL query (like SELECT ,
INSERT , UPDATE , or DELETE ). The result of the subquery is used by the outer query.
Keywords: Nested query, inner query, outer query.

28. What is the UNION operator used for?

Answer: The UNION operator is used to combine the result sets of two or more SELECT statements.
It removes duplicate rows between the various SELECT statements.
Keywords: Combine results, SELECT statements, remove duplicates.

29. What is the default sort order for ORDER BY ?

Answer: The default sort order is ascending ( ASC ).
Keywords: Ascending, ASC .

30. What is referential integrity?

Answer: Referential integrity is a database concept that ensures relationships between tables remain
consistent. When enforced (usually with foreign keys), it prevents you from adding a record to a related
table if there's no associated record in the primary table, and it can also prevent you from deleting or
changing a primary table record that has related records.
Keywords: Consistency, relationships, foreign key.

Intermediate Level

1. What is the difference between UNION and UNION ALL ?

Answer: Both operators combine the result sets of two SELECT statements. UNION removes
duplicate rows from the combined result set. UNION ALL includes all rows, including duplicates.
Because it doesn't perform the extra step of removing duplicates, UNION ALL is faster.
Keywords: UNION (removes duplicates), UNION ALL (includes duplicates, faster).

2. Explain the different types of joins ( LEFT , RIGHT , FULL OUTER ).

Answer:

LEFT JOIN (or LEFT OUTER JOIN ): Returns all records from the left table, and the matched
records from the right table. The result is NULL from the right side if there is no match.

Page 5 of 14
 RIGHT JOIN (or RIGHT OUTER JOIN ): Returns all records from the right table, and the
matched records from the left table. The result is NULL from the left side if there is no match.
FULL OUTER JOIN : Returns all records when there is a match in either the left or the right
table. It combines the functionality of LEFT JOIN and RIGHT JOIN .
Keywords: LEFT JOIN (all from left), RIGHT JOIN (all from right), FULL OUTER JOIN (all
from both).

3. What is a self-join?
Answer: A self-join is a regular join, but the table is joined with itself. It is useful for querying
hierarchical data or comparing rows within the same table. You must use aliases for the table to perform
a self-join.
Keywords: Join with itself, hierarchical data, table alias.

4. What is a Common Table Expression (CTE)?

Answer: A CTE is a temporary, named result set that you can reference within a SELECT , INSERT ,
UPDATE , or DELETE statement. It is defined using the WITH clause and helps to simplify complex
queries, improve readability, and enable recursive queries.
Keywords: WITH clause, temporary result set, readability, recursive query.

5. What is normalization? Explain 1NF, 2NF, and 3NF.

Answer: Normalization is the process of organizing columns and tables in a relational database to
minimize data redundancy.

1NF (First Normal Form): Ensures that the table has a primary key and that each column contains
atomic (indivisible) values.
2NF (Second Normal Form): Must be in 1NF. All non-key attributes must be fully functionally
dependent on the entire primary key (this applies to tables with composite keys).
3NF (Third Normal Form): Must be in 2NF. There should be no transitive dependencies, meaning
non-key attributes cannot depend on other non-key attributes.
Keywords: Minimize redundancy, 1NF (atomic values), 2NF (full dependency), 3NF (no transitive
dependency).

6. What is an index?
Answer: An index is a special lookup table that the database search engine can use to speed up data
retrieval. An index is a pointer to data in a table. Creating an index on a column improves the speed of
queries that use that column in their WHERE clause but can slow down data modification operations
( INSERT , UPDATE ).
Keywords: Performance, speed up retrieval, lookup table, WHERE clause.

7. What is the difference between a clustered and a non-clustered index?

Answer:

Page 6 of 14
 Clustered Index: Determines the physical order of data in a table. Because of this, a table can only
have one clustered index. The leaf nodes of the index contain the actual data pages.
Non-Clustered Index: Has a structure separate from the data rows. The leaf nodes of a non-
clustered index contain pointers to the data rows. A table can have multiple non-clustered
indexes.
Keywords: Clustered (physical order, one per table), Non-clustered (separate structure, multiple
per table, pointers).

8. What is a view in SQL?

Answer: A view is a virtual table based on the result-set of an SQL statement. It contains rows and
columns, just like a real table. Views can be used to simplify complex queries, provide a layer of security
by restricting access to data, and present data in a consistent way.
Keywords: Virtual table, stored query, security, simplification.

9. What are transactions and what are the ACID properties?

Answer: A transaction is a sequence of operations performed as a single logical unit of work. The ACID
properties guarantee data integrity during transactions:

Atomicity: All operations within a transaction are completed successfully, or none are.
Consistency: The database remains in a consistent state before and after the transaction.
Isolation: Concurrent transactions do not affect each other's execution.
Durability: The results of a committed transaction are permanent, even in the case of a system
failure.
Keywords: Atomicity, Consistency, Isolation, Durability (ACID), logical unit of work.

10. What is the purpose of the COALESCE function?

Answer: The COALESCE function returns the first non-NULL value in a list of expressions. It is a useful
way to substitute a default value for NULLs.
Keywords: First non-NULL, default value.

11. What is a CASE statement?

Answer: The CASE statement goes through conditions and returns a value when the first condition is
met (like an if-then-else statement). It allows you to add conditional logic to your SQL queries.
Keywords: Conditional logic, if-then-else , WHEN , THEN , ELSE .

12. What are the INTERSECT and EXCEPT operators?

Answer:

INTERSECT : Combines two SELECT statements and returns only the rows that appear in both
result sets.
EXCEPT (or MINUS in Oracle): Combines two SELECT statements and returns rows from the
first query that are not present in the second query's result set.
Keywords: INTERSECT (common rows), EXCEPT (rows in first, not in second).

Page 7 of 14
13. What is a correlated subquery?
Answer: A correlated subquery is an inner query that depends on the outer query for its values. It is
executed once for each row processed by the outer query. This can lead to poor performance if not used
carefully.
Keywords: Depends on outer query, row-by-row execution, performance.

14. What is a CHECK constraint?

Answer: A CHECK constraint is used to limit the value range that can be placed in a column. It enforces
domain integrity by ensuring that all values in a column satisfy a specific condition.
Keywords: Limit value range, condition, domain integrity.

15. What is a DEFAULT constraint?

Answer: The DEFAULT constraint is used to provide a default value for a column when no value is
specified during an INSERT operation.
Keywords: Default value, INSERT .

16. How can you get the current date and time in SQL?
Answer: This is database-specific. Common functions are GETDATE() (SQL Server), NOW() (MySQL),
and SYSDATE (Oracle).
Keywords: GETDATE() , NOW() , SYSDATE .

17. What is a stored procedure?

Answer: A stored procedure is a prepared SQL code that you can save, so the code can be reused over
and over again. It can accept input parameters and return results. Stored procedures can improve
performance, provide better security, and reduce network traffic.
Keywords: Reusable code, parameters, performance, security.

18. What is the difference between CHAR and VARCHAR ?

Answer:

CHAR : A fixed-length string data type. If you store a string shorter than the defined length, it will
be padded with spaces.
VARCHAR : A variable-length string data type. It only uses storage for the characters you actually
store, plus a small amount of overhead.
Use CHAR for strings of a known, fixed length (e.g., country codes). Use VARCHAR for strings
with variable lengths.
Keywords: CHAR (fixed-length), VARCHAR (variable-length).

19. What is the NULLIF function?

Answer: The NULLIF function takes two arguments and returns NULL if the two arguments are
equal. Otherwise, it returns the first argument. It is often used to prevent division-by-zero errors.
Keywords: Returns NULL if equal, division-by-zero.

Page 8 of 14
20. Can you create a primary key on multiple columns?
Answer: Yes, this is called a composite primary key. It is created when a single column is not sufficient
to uniquely identify a record. All columns in the composite key together must be unique.
Keywords: Composite primary key, multiple columns.

21. What is the difference between a temporary table and a table variable?
Answer: Both are used to store data temporarily.

Temporary Table ( #temp ): A physical table created in the tempdb . It can be indexed and is
visible to the session that created it (or globally). It supports transactions.
Table Variable ( @table ): Stored in memory. It has a more limited scope (current batch or
procedure) and does not support transactions or explicit index creation (though it can have
primary keys). Generally better for small data sets.
Keywords: Temporary table ( tempdb , indexed), Table variable (memory, limited scope).

22. What is a trigger?

Answer: A trigger is a special type of stored procedure that automatically runs when a specific DML
event ( INSERT , UPDATE , DELETE ) occurs on a table or view. Triggers are often used to maintain
data integrity or enforce complex business rules.
Keywords: Automatic execution, DML events, stored procedure.

23. What is a schema?

Answer: A schema is a logical collection of database objects such as tables, views, and stored
procedures. It acts as a container or namespace to group related objects and helps in managing
security and organization.
Keywords: Logical container, namespace, database objects.

24. What is denormalization?

Answer: Denormalization is the process of intentionally adding redundant data to one or more tables.
This is done to improve query performance by reducing the need for complex joins. It is the opposite of
normalization.
Keywords: Redundant data, improve performance, reduce joins.

25. What is the difference between a function and a stored procedure?

Answer: A function must return a single value or a table, while a stored procedure is not required to
return anything. Functions can be called from within a SELECT statement, whereas stored procedures
cannot. Stored procedures can have both input and output parameters, while functions typically only
have input parameters.
Keywords: Function (returns value, in SELECT ), Stored Procedure (no required return).

26. What does the CAST function do?

Answer: The CAST function converts a value from one data type to another. For example,

Page 9 of 14
CAST('2023-01-01' AS DATE) .
Keywords: Convert data type.

27. What is a User-Defined Function (UDF)?

Answer: A UDF is a function created by the user to perform a specific task. UDFs can be scalar
(returning a single value), inline table-valued, or multi-statement table-valued (returning a table). They
promote code reusability.
Keywords: User-created, scalar, table-valued, reusable.

28. How would you find the Nth highest salary?

Answer: You can use a subquery with DISTINCT and TOP / LIMIT , or more modernly, a window
function like DENSE_RANK() . Using DENSE_RANK() , the query would be: WITH SalaryRank AS
(SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) as RankNum FROM
Employees) SELECT Salary FROM SalaryRank WHERE RankNum = N;
Keywords: DENSE_RANK , ROW_NUMBER , subquery, CTE.

29. What is a primary key vs a candidate key?

Answer: A candidate key is a column, or set of columns, that can uniquely identify a row in a table. A
table can have multiple candidate keys. The primary key is the one candidate key that is chosen to be
the main identifier for the table.
Keywords: Candidate key (potential primary key), Primary key (chosen identifier).

30. What is an entity-relationship (ER) diagram?

Answer: An ER diagram is a visual representation of a database schema. It shows the entities (tables),
their attributes (columns), and the relationships between them. It is a fundamental tool for database
design.
Keywords: Visual representation, entities, attributes, relationships, database design.

Advanced Level

1. What are window functions? Give some examples.

Answer: A window function performs a calculation across a set of table rows that are somehow related
to the current row. Unlike regular aggregate functions, which group rows into a single output row,
window functions produce a result for each row. Examples include ROW_NUMBER() , RANK() ,
DENSE_RANK() , LEAD() , LAG() , and aggregate functions used with an OVER() clause (e.g.,
SUM(Sales) OVER (PARTITION BY Region) ).
Keywords: OVER() clause, PARTITION BY , ROW_NUMBER , RANK , LEAD , LAG .

2. Explain the difference between RANK() , DENSE_RANK() , and ROW_NUMBER() .

Answer:

Page 10 of 14
 ROW_NUMBER() : Assigns a unique, sequential integer to each row within a partition, regardless
of ties.
RANK() : Assigns a rank to each row within a partition. If there are ties, tied rows get the same
rank, and the next rank is skipped (e.g., 1, 2, 2, 4).
DENSE_RANK() : Similar to RANK() , but if there are ties, the next rank is not skipped (e.g., 1, 2,
2, 3).
Keywords: ROW_NUMBER (unique, sequential), RANK (skips rank on tie), DENSE_RANK (no skip
on tie).

3. What is an execution plan?

Answer: An execution plan (or query plan) is a sequence of steps that the database query optimizer
generates to access data in the database. It shows how the database will execute a query, including
which tables are accessed, in what order, and what join algorithms and indexes are used. Analyzing the
execution plan is crucial for query performance tuning.
Keywords: Query optimizer, performance tuning, sequence of steps, index usage.

4. What are transaction isolation levels?

Answer: Transaction isolation levels define the degree to which a transaction must be isolated from
data modifications made by other concurrent transactions. The standard levels are:

Read Uncommitted: Lowest level. Allows dirty reads (reading uncommitted data).
Read Committed: Prevents dirty reads. A transaction can only read committed data.
Repeatable Read: Prevents dirty reads and non-repeatable reads (a row cannot be changed by
another transaction while the current one is running).
Serializable: Highest level. Prevents dirty reads, non-repeatable reads, and phantom reads (new
rows cannot be added that match the WHERE clause). It emulates serial transaction execution.
Keywords: Concurrency, dirty read, non-repeatable read, phantom read, locking.

5. What is a deadlock? How can it be prevented?

Answer: A deadlock is a situation where two or more transactions are waiting for each other to release
locks, resulting in a standstill where none of them can proceed. Prevention strategies include: always
accessing resources in the same order, keeping transactions short, and using a lower transaction
isolation level if possible. Most database systems have a deadlock detection mechanism that will
terminate one of the transactions (the "victim").
Keywords: Lock, transaction, victim, resource order.

6. How would you pivot data in SQL?

Answer: Pivoting data transforms rows into columns. Some database systems like SQL Server and
Oracle have a built-in PIVOT operator. For databases that don't, you can achieve the same result using
conditional aggregation with the CASE statement and an aggregate function like SUM() or MAX() .
For example, MAX(CASE WHEN Month = 'Jan' THEN Sales END) AS Jan_Sales .
Keywords: PIVOT , rows to columns, conditional aggregation, CASE .

Page 11 of 14
7. What is a recursive CTE?
Answer: A recursive Common Table Expression (CTE) is a CTE that references itself. It is used to query
hierarchical data, such as organizational charts or bill of materials. A recursive CTE consists of an
anchor member (the base case) and a recursive member that references the CTE itself, combined by a
UNION ALL .
Keywords: Hierarchical data, anchor member, recursive member, UNION ALL .

8. What is a covered index?

Answer: A covered index is an index that includes all the columns required to satisfy a query, including
those in the SELECT list, JOIN conditions, and WHERE clause. When a query is "covered" by an index,
the database can answer the query by only looking at the index, without having to access the table data
itself. This significantly improves performance.
Keywords: Index includes all columns, performance, no table access.

9. What is the difference between LEAD() and LAG() ?

Answer: Both are window functions used to access data from a different row within the same result set
without a self-join.

LAG() : Accesses data from a previous row in the result set.

LEAD() : Accesses data from a subsequent (following) row in the result set.
Keywords: Window function, previous row ( LAG ), subsequent row ( LEAD ).

10. How can you improve query performance?

Answer: Several strategies can be used:

Ensure proper indexing, especially on columns used in WHERE clauses and JOIN conditions.
Write efficient queries: avoid SELECT * , use WHERE clauses to limit data, and prefer JOIN s
over correlated subqueries.
Analyze the execution plan to identify bottlenecks.
Keep database statistics up to date.
Consider denormalization for read-heavy applications.
Keywords: Indexing, query optimization, execution plan, statistics, denormalization.

11. What is an INSTEAD OF trigger?

Answer: An INSTEAD OF trigger is a special type of trigger that is fired instead of the original DML
action ( INSERT , UPDATE , DELETE ). They are primarily used on views that are not directly updatable,
allowing you to define the logic for how to modify the underlying base tables.
Keywords: Trigger, view, non-updatable view.

12. What are filtered indexes?

Answer: A filtered index is a non-clustered index that includes a WHERE clause, so it only indexes a
subset of rows in a table. This can improve query performance, reduce index maintenance costs, and

Page 12 of 14
reduce index storage costs for queries that select from a well-defined subset of data (e.g., only active
orders).
Keywords: Non-clustered index, WHERE clause, subset of rows.

13. What is lock escalation?

Answer: Lock escalation is the process of converting many fine-grained locks (like row locks) into fewer
coarse-grained locks (like table locks). Database systems do this automatically to reduce memory
overhead when a single transaction acquires a large number of locks. However, it can reduce
concurrency.
Keywords: Concurrency, locks, row lock, table lock, memory overhead.

14. What is a materialized view?

Answer: A materialized view is a database object that contains the results of a query, similar to a
regular view. However, unlike a regular view, the result set is physically stored on disk and is periodically
refreshed from the base tables. They are used to improve performance for complex and expensive
queries.
Keywords: Physically stored, pre-computed, performance, refresh.

15. Explain SQL Injection.

Answer: SQL Injection is a code injection technique that might destroy your database. It occurs when
malicious SQL statements are inserted into an entry field for execution (e.g., to dump database content
to the attacker). It is one of the most common web hacking techniques. Using parameterized queries or
prepared statements is the primary way to prevent it.
Keywords: Security, malicious code, parameterized queries, prepared statements.

16. When would you use a CROSS JOIN ?

Answer: A CROSS JOIN produces a Cartesian product of two tables, meaning it returns every
possible combination of rows from the tables. It is used in scenarios where you need to generate all
possible pairings, such as creating a deck of cards (joining a 'ranks' table with a 'suits' table) or
generating test data.
Keywords: Cartesian product, all combinations.

17. What is the difference between GETDATE() and SYSDATETIME() in SQL Server?
Answer: Both return the current date and time of the database server. The difference is precision.
SYSDATETIME() has a higher fractional second precision (nanoseconds) compared to GETDATE()
(milliseconds). SYSDATETIME() is the more standard-compliant function.
Keywords: Precision, nanoseconds, milliseconds.

18. What is the purpose of sp_helpdb and sp_help ?

Answer: These are system stored procedures in SQL Server.

sp_helpdb : Provides information about a specific database or all databases.

Page 13 of 14
 sp_help : Provides information about a specific database object (like a table, view, or stored
procedure), including its columns, indexes, etc.
Keywords: System stored procedure, database information, object information.

19. What is a heap in the context of SQL tables?

Answer: A heap is a table that does not have a clustered index. Data rows in a heap are not stored in any
particular order. Without a clustered index, data is stored in the order it was inserted.
Keywords: No clustered index, unordered data.

20. What are Dynamic Management Views (DMVs)?

Answer: DMVs are built-in views in SQL Server that return server state information. They are used to
monitor the health of a server instance, diagnose problems, and tune performance. They provide
insights into active sessions, query plans, index usage, and memory pressure.
Keywords: Server state, monitoring, performance tuning, diagnostics.

Page 14 of 14