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

To find the most recently hired employees, the results must be sorted by `hire_date` in descending order (`DESC`), which places the latest dates first. The `LIMIT 10` clause then restricts the output to only the top 10 rows from this sorted result set.

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

To get the third page of 20 items, you must first skip the items from the first two pages (2 * 20 = 40). The `OFFSET 40` clause accomplishes this by skipping the first 40 records. The `LIMIT 20` clause then retrieves the next 20 records, which represent the third page. The `ORDER BY` clause is essential to ensure consistent pagination.

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

`LIMIT` is a clause used by MySQL, PostgreSQL, and SQLite to restrict the number of rows returned by a query. SQL Server uses the `TOP` keyword for the same purpose. `FETCH FIRST` is part of the SQL standard, and `ROWNUM` is specific to Oracle.

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

To sort by multiple columns, you list them in the `ORDER BY` clause in the desired order of precedence. The query first sorts by `Country` in ascending order (ASC is the default). Then, for rows with the same country, it sorts by `TotalSales` in descending order, as specified by the `DESC` keyword.

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

Without an `ORDER BY` clause, the database does not guarantee the order in which rows are returned. When using `LIMIT` or `FETCH`, this can lead to an unpredictable and inconsistent subset of rows being returned each time the query is run. `ORDER BY` provides a stable sorting order, ensuring the same subset is returned consistently.

SQL Practice Test

SQL Practice Test

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