SQL Questions

Karan Bhardwaj

Score: 🔥 123

Intermediate

Took 8 days

Programming Language

SQL

60%

Questions Attempted

10 / 10

Correct Answers

Incorrect Answers

Points Earned

🔥 6

Question 1

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 2

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 3

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 4

In SQL, when a subquery is used in the FROM clause, it essentially acts as a:

Filter on the main query.
Temporary table.
Join condition.
Sorting mechanism.

Explanation:

Placing a subquery within the FROM clause effectively treats its result set as a virtual table. This temporary table can then be referenced, joined, or filtered just like any other physical table in your database.

Question 5

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.

Question 6

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 7

You are tasked with finding employees who do not manage any other employees. Which type of JOIN would be most suitable for this scenario?

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN

Explanation:

A LEFT JOIN will include all employees from the employee table. By joining on the manager ID and checking for NULL values in the manager's side, you can identify employees without any subordinates.

Question 8

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 9

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 10

You are tasked with finding the total sales for each month of the year. You have a table 'Orders' with columns 'OrderID', 'OrderDate', and 'SalesAmount'. Which query correctly calculates the monthly sales totals?

SELECT MONTH(OrderDate) AS Month, SUM(SalesAmount) AS TotalSales
FROM Orders
GROUP BY Month;

SELECT EXTRACT(MONTH FROM OrderDate) AS Month, SUM(SalesAmount) AS TotalSales
FROM Orders
ORDER BY Month;

SELECT MONTH(OrderDate) AS Month, SUM(SalesAmount) AS TotalSales
FROM Orders
ORDER BY MONTH(OrderDate);

SELECT STRFTIME('%Y-%m', OrderDate) AS Month, SUM(SalesAmount) AS TotalSales
FROM Orders
GROUP BY Month;

Explanation:

This query correctly extracts the month and year from the OrderDate using STRFTIME('%Y-%m', OrderDate) and groups the results by this formatted date, ensuring accurate monthly sales calculations.