SQL Questions

Karan Bhardwaj

Score: 🔥 123

Intermediate

Took 2 months

Programming Language

SQL

46%

Questions Attempted

15 / 15

Correct Answers

Incorrect Answers

Points Earned

🔥 7

Question 1

You need to find all customers in your 'Customers' table who haven't provided a phone number. The 'PhoneNumber' column can contain NULL values. Which query achieves this?

SELECT * FROM Customers WHERE PhoneNumber = NULL;

SELECT * FROM Customers WHERE PhoneNumber <> '';

SELECT * FROM Customers WHERE PhoneNumber IS NULL;

SELECT * FROM Customers WHERE PhoneNumber NOT IN (NULL);

Explanation:

In SQL, NULL represents the absence of a value. To compare against NULL, you must use the 'IS NULL' operator. Using '=' with NULL will not yield the desired results.

Question 2

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 3

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 4

What SQL statement is used to apply the changes made within a transaction permanently to the database?

SAVE
COMMIT
UPDATE
EXECUTE

Explanation:

COMMIT is used to end a transaction and make all changes permanent in the database.

Question 5

You have a table of orders and want to calculate the running total of order amounts for each customer, ordered by order date. Which SQL feature would be most efficient for this task?

Self Join
Cross Join
Window Function
Recursive CTE

Explanation:

Window functions with a suitable ORDER BY clause within the OVER clause are specifically designed to perform calculations across a set of rows related to the current row, such as running totals, making them the most efficient way to calculate running totals.

Question 6

You are given a table 'Products' with columns 'ProductID', 'ProductName', and 'Price'. The Price column can contain NULL values for products not yet priced. Write a SQL query to retrieve all products, replacing NULL prices with the string 'Not Priced'.

SELECT ProductID, ProductName, 
CASE
    WHEN Price IS NULL THEN 0
    ELSE Price
END AS ModifiedPrice
FROM Products;

SELECT ProductID, ProductName, 
ISNULL(Price, 'Not Priced') AS ModifiedPrice
FROM Products;

SELECT ProductID, ProductName, 
COALESCE(Price, 'Not Priced') AS ModifiedPrice
FROM Products;

SELECT ProductID, ProductName, 
CASE
    WHEN Price = NULL THEN 'Not Priced'
    ELSE Price
END AS ModifiedPrice
FROM Products;

Explanation:

This query utilizes the COALESCE function, which efficiently handles NULL values. It replaces NULL prices with 'Not Priced' while retaining the original prices for other products.

Question 7

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 8

Which of the following SQL statements is used to definitively save changes made within a transaction?

SAVE
COMMIT
UPDATE
PERSIST

Explanation:

In SQL, the COMMIT command is the instruction used to finalize changes made within a transaction. It signifies that all operations within the transaction were successful, and their effects should be permanently written to the database.

Question 9

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 10

You are using a correlated subquery to compare values in each row of a table to aggregated data from another table. What is a potential performance concern with this approach?

Correlated subqueries are generally more efficient than joins.
Correlated subqueries can lead to faster data retrieval due to their targeted nature.
Correlated subqueries can be computationally expensive, especially with large datasets, as the subquery might be executed repeatedly for each row of the outer query.
Correlated subqueries are not recommended for use with aggregated data.

Explanation:

While correlated subqueries provide an elegant solution in some cases, their nested nature can lead to performance bottlenecks if not used carefully.

Question 11

You're asked to retrieve the names of all employees who earn more than the average salary of their respective departments. This necessitates calculating the average salary per department and then comparing it to individual employee salaries. Which type of subquery would be most appropriate for this scenario?

Non-Correlated Subquery
Correlated Subquery
Scalar Subquery
Inline View

Explanation:

A correlated subquery is executed once for each row in the outer query. In this case, for every employee, the subquery calculates the average salary of their department. This dynamic calculation, dependent on the outer query's row, makes correlated subqueries the right choice.

Question 12

You have a table named 'Employees' with columns 'EmployeeID', 'Department', and 'Salary'. Write a SQL query to fetch the top 3 highest-paid employees from each department, along with their rank within the department.

SELECT EmployeeID, Department, Salary, 
ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank
FROM Employees
WHERE Rank <= 3;

SELECT EmployeeID, Department, Salary
FROM Employees
ORDER BY Department, Salary DESC
LIMIT 3;

WITH RankedEmployees AS (
    SELECT EmployeeID, Department, Salary, 
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank
    FROM Employees
)
SELECT EmployeeID, Department, Salary, Rank
FROM RankedEmployees
WHERE Rank <= 3;

SELECT EmployeeID, Department, Salary, 
RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees
WHERE Rank <= 3;

Explanation:

This query correctly utilizes a CTE and the ROW_NUMBER() function to first rank employees within each department based on salary. Then, it filters the results to include only the top 3 ranked employees from each department.

Question 13

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 14

Which of these tools can be used to analyze query performance in SQL?

Query optimizer
Data definition language (DDL)
Transaction control language (TCL)
Query execution plan

Explanation:

A query execution plan is a visual representation of the steps a database takes to execute a query. Analyzing this plan can help identify bottlenecks and optimize query performance.

Question 15

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.