SQL - Structured Query Language Sorting and Limiting Results Questions and Answers

Question 1

A developer needs to write a query that retrieves the top 10 most recently hired employees from an `employees` table.
Which query correctly accomplishes this, assuming `hire_date` is the column storing the hiring date?

Accepted Answer

SELECT * FROM employees ORDER BY hire_date DESC LIMIT 10;

Answer

SELECT * FROM employees ORDER BY hire_date ASC LIMIT 10;

Answer

SELECT TOP 10 * FROM employees ORDER BY hire_date;

Answer

SELECT * FROM employees FETCH FIRST 10 ROWS ONLY;

Question 2

You are implementing pagination for a list of products. You need to display the third page of results, with each page containing 20 products. The products should be sorted by `product_name`. Which query correctly fetches the records for the third page?

Accepted Answer

SELECT * FROM products ORDER BY product_name LIMIT 20 OFFSET 40;

Answer

SELECT * FROM products ORDER BY product_name LIMIT 20 OFFSET 20;

Answer

SELECT * FROM products ORDER BY product_name LIMIT 40, 20;

Answer

SELECT * FROM products ORDER BY product_name FETCH FIRST 20 ROWS ONLY OFFSET 40;

Question 3

Which of the following SQL clauses is a non-standard, database-specific way to limit results, commonly found in MySQL and PostgreSQL, while its equivalent in SQL Server is `TOP`?

Accepted Answer

LIMIT

Answer

FETCH FIRST

Answer

ROWNUM

Answer

STOP AFTER

Question 4

A data analyst wants to sort a `Customers` table first by `Country` in ascending order, and then by `TotalSales` in descending order for customers within the same country. Which `ORDER BY` clause is correct?

Accepted Answer

ORDER BY Country ASC, TotalSales DESC;

Answer

ORDER BY Country, TotalSales;

Answer

ORDER BY Country DESC, TotalSales ASC;

Answer

ORDER BY Country, TotalSales BOTH DESC;

Question 5

What is the primary reason to always use an `ORDER BY` clause when using a `LIMIT` or `FETCH` clause?

Accepted Answer

To guarantee consistent and predictable results.

Answer

To improve query execution speed.

Answer

To ensure the query syntax is valid.

Answer

To allow the use of aggregate functions on the limited set.

Question 6

In a database system that follows the SQL standard, how does the `ORDER BY` clause treat `NULL` values by default when sorting in ascending (`ASC`) order?

Accepted Answer

The behavior is not defined by the SQL standard, and it varies by database system.

Answer

They are always treated as the lowest possible values and appear first.

Answer

They are always treated as the highest possible values and appear last.

Answer

They are excluded from the result set automatically.

A developer needs to write a query that retrieves the top 10 most recently hired employees from an `employees` table.Which query correctly accomplishes this, assuming `hire_date` is the column storing the hiring date?

A developer needs to write a query that retrieves the top 10 most recently hired employees from an `employees` table.
Which query correctly accomplishes this, assuming `hire_date` is the column storing the hiring date?