SQL Questions

Mahesh pal

Score: 🔥 44

Intermediate

Took 2 months

Programming Language

SQL

53%

Questions Attempted

15 / 15

Correct Answers

Incorrect Answers

Points Earned

🔥 8

Question 1

What is the primary difference between a FULL OUTER JOIN and a UNION ALL operation in SQL?

FULL OUTER JOIN combines rows based on a join condition, UNION ALL appends all rows.
FULL OUTER JOIN only returns matching rows, UNION ALL returns all rows from both tables.
FULL OUTER JOIN eliminates duplicates, UNION ALL retains them.
There is no difference; they achieve the same outcome.

Explanation:

The core distinction lies in how they combine data. FULL OUTER JOIN merges rows from tables based on a specified join condition, while UNION ALL directly appends all rows from the involved tables without considering any condition.

Question 2

In SQL, a self-join is used to:

Join two tables with different schemas.
Join a table to itself using an alias.
Combine data from multiple tables based on a common column.
Improve query performance by reducing the number of joins.

Explanation:

A self-join in SQL involves joining a table to itself, typically using aliases to differentiate between instances of the same table. This is useful for comparing rows within the same table based on specific criteria.

Question 3

Which of these is NOT a common SQL performance tuning technique?

Using wildcard characters (%) at the beginning of a search pattern.
Selecting only the required columns instead of using SELECT *.
Filtering data on indexed columns whenever possible.
Using appropriate data types for columns.

Explanation:

Using wildcard characters at the beginning of a search pattern often leads to full table scans, negating the benefits of indexes. It's more efficient to use other filtering methods or use wildcards at the end of the pattern if possible.

Question 4

Which of the following is a valid use case for a recursive CTE?

Finding the average salary of employees in a department.
Retrieving all employees and their managers in a hierarchical structure.
Updating multiple rows in a table based on a condition.
Creating a new table based on the results of a query.

Explanation:

Recursive CTEs excel at traversing hierarchical or tree-like structures within data. They are particularly well-suited for scenarios like retrieving all employees and their managers in a hierarchy, where each level of management is connected.

Question 5

Which window function would you use to assign a unique, sequential number to each row within a partition of a result set?

ROW_NUMBER()
RANK()
DENSE_RANK()
NTILE()

Explanation:

ROW_NUMBER() assigns a unique sequential integer to each row within the partition of a result set. It's used when you need a simple, unbroken sequence.

Question 6

You have a large table with millions of records. You frequently query the table based on a specific column 'customer_city'. What is the most effective way to improve the performance of these queries?

Add an index to the 'customer_city' column.
Increase the memory allocated to the database server.
Use a stored procedure instead of ad-hoc queries.
Optimize the database server's configuration parameters.

Explanation:

Adding an index to the frequently queried column 'customer_city' will significantly speed up data retrieval by allowing the database to quickly locate the relevant rows without scanning the entire table.

Question 7

What is a CTE (Common Table Expression) in SQL?

A permanent table structure stored in the database.
A temporary, named result set that can be referenced within a single query.
A type of index used for optimizing query performance.
A stored procedure used to encapsulate and reuse SQL code.

Explanation:

A CTE is like a temporary view that exists only within the scope of a single query. It helps break down complex queries into more manageable parts.

Question 8

What is a disadvantage of having too many indexes on a table?

Increased storage space required for the indexes.
Slower query execution due to the overhead of maintaining the indexes.
Reduced data integrity because of potential index corruption.
Increased complexity in managing the database schema.

Explanation:

While indexes speed up data retrieval, they also introduce overhead for data modification operations (insert, update, delete). Too many indexes can slow down these operations as the database needs to update all relevant indexes.

Question 9

What is the primary purpose of using a Common Table Expression (CTE) in SQL?

To create a temporary, named result set that exists only within the scope of the current query.
To define a recursive relationship within a table.
To improve the performance of complex queries by storing intermediate results.
To replace the need for views in a database.

Explanation:

CTEs provide a way to organize and break down complex queries into smaller, more manageable parts. They are not persistent like views and are primarily used for readability and modularity within a single query.

Question 10

Imagine you need to find pairs of employees who share the same job title without displaying the same employee twice (e.g., Employee A paired with Employee B is the same as Employee B with Employee A). Which SQL concept would be most efficient for this task?

UNION
INTERSECT
Self-Join
Cross Join

Explanation:

A self-join allows you to join a table to itself, effectively treating it as two separate tables. This is ideal for comparing rows within the same table, such as finding employees with matching job titles without redundant pairings.

Question 11

What is the result of a CROSS JOIN between a table with 5 rows and a table with 3 rows?

5 rows
3 rows
8 rows
15 rows

Explanation:

A CROSS JOIN produces a Cartesian product of the two tables. This means every row in the first table is combined with every row in the second table, resulting in 5 * 3 = 15 rows.

Question 12

Which ACID property ensures that any changes made within a transaction are applied to the database in a manner that prevents partial updates, preserving data consistency?

Atomicity
Consistency
Isolation
Durability

Explanation:

Atomicity in ACID properties mandates that a transaction is treated as a single, indivisible unit of work. Either all changes within the transaction are applied successfully, or none are, preventing partial updates and maintaining data integrity.

Question 13

You have a table 'Customers' with a column 'LastPurchaseDate' that can contain NULL values. Write a query to retrieve all customers who have NOT made a purchase in the last 90 days.

SELECT *
FROM Customers
WHERE LastPurchaseDate IS NULL;

SELECT *
FROM Customers
WHERE LastPurchaseDate < DATEADD(day, -90, GETDATE());

SELECT *
FROM Customers
WHERE LastPurchaseDate IS NOT NULL AND LastPurchaseDate < DATEADD(day, -90, GETDATE());

SELECT *
FROM Customers
WHERE LastPurchaseDate < GETDATE() - 90;

Explanation:

This query correctly identifies customers who haven't made a purchase in the last 90 days by considering both NULL values (meaning they've never purchased) and dates older than 90 days ago. It uses IS NULL to check for null values and DATEADD function to subtract 90 days from the current date.

Question 14

You have a table 'Orders' with columns 'OrderID', 'CustomerID', and 'OrderDate'. You want to rank customers within each order date based on the order they placed (earliest to latest). Which window function should you use?

ROW_NUMBER() OVER (PARTITION BY OrderDate ORDER BY OrderID)

RANK() OVER (ORDER BY OrderDate, OrderID)

DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY OrderDate)

NTILE(4) OVER (PARTITION BY OrderDate ORDER BY OrderID)

Explanation:

ROW_NUMBER() assigns a unique sequential number to each row within the partition defined by 'OrderDate', ordered chronologically by 'OrderID', thus ranking customers by their order placement within each date.

Question 15

You need to represent a hierarchical organization structure in a relational database table. Which approach is most suitable for querying and traversing this hierarchical data?

Storing the entire hierarchy as a single string in a column
Using multiple tables with foreign key relationships to represent different levels
Implementing a Recursive CTE to query and navigate the hierarchy
Creating a separate table for each level of the hierarchy

Explanation:

Recursive CTEs are ideal for representing and querying hierarchical data. They allow you to traverse the hierarchy recursively, retrieving data at different levels efficiently.